Issue 118121

Summary:	Sorting function doesn't work properly on concationation fields
Product:	Base	Reporter:	stanislav
Component:	code	Assignee:	AOO issues mailing list <issues>
Status:	UNCONFIRMED ---	QA Contact:
Severity:	Normal
Priority:	P3	CC:	issues, r4zoli
Version:	OOo 3.3
Target Milestone:	---
Hardware:	PC
OS:	Windows XP
Issue Type:	DEFECT	Latest Confirmation in:	---
Developer Difficulty:	---

Description stanislav 2011-06-01 14:45:54 UTC

In all higher versions than 3.1.1 sorting function doesnt seem to work properly on concation fields, when a form element is filled using a corresponding SQL command.

Details:
- a table control element is used in a form
- the form uses a ODBC-connection as a data source (the problem remains also with the native MYSQL connector)
- one of the fields in the SQL command (as a content type)is defined as following:

<...> CONCAT( IFNULL(`firma`.`firma`,''), ' ', IFNULL(`firma`.`firma1`,''), ' ', IFNULL(`firma`.`firma2`,'')) AS `firmenname` <...>

Description:
When using the sorting buttons (ascending or descending) in the form navigation pane on the above mentioned field, all results disappear from the table control element. When sorting on any other field the data are displayed and sorted in correct order. This behaviour is only observed on any versions higher than 3.1.1. Sorting on the same field using the sorting buttons from the navigation pane using the 3.1.1 version functions properly.

Comment 1 r4zoli 2011-06-01 19:26:52 UTC

Please test it with OOo 3.4Beta downloadable from OOo site, which has lot of bugfixes in.

Or attach example document with form, and describe table structure.

Comment 2 stanislav 2011-06-09 15:14:55 UTC

(In reply to comment #1)
> Please test it with OOo 3.4Beta downloadable from OOo site, which has lot of
> bugfixes in.
> 
> Or attach example document with form, and describe table structure.

So far I managed to find out that changing the SQL-Query from
 
SELECT `person_firma`.*, `person`.*, `firma`.*, CONCAT( IFNULL(`firma`.`firma`,''), ' ', IFNULL(`firma`.`firma1`,''), ' ', IFNULL(`firma`.`firma2`,'')) AS `firmenname` FROM `openmysql`.`person_firma`, `openmysql`.`person`, `openmysql`.`firma` WHERE ( `person_firma`.`person_id` = `person`.`id` AND `person_firma`.`firma_id` = `firma`.`id` )

to

SELECT `person_firma`.*, `person`.*, `firma`.*, CONCAT( IFNULL(`firma`.`firma`,''), ' ', IFNULL(`firma`.`firma1`,''), ' ', IFNULL(`firma`.`firma2`,'')) AS `firmenname` FROM `person_firma`, `person`, `firma` WHERE ( `person_firma`.`person_id` = `person`.`id` AND `person_firma`.`firma_id` = `firma`.`id` )

It seems like while generating sorting-conditions using the sorting buttons (which later appear in the sort-field in the formular options) the name of the database ("openmysql") is attached before the name of the column in the first case. As a sorting condition "openmysql.firmenname" is then placed into the sort-field in the formular options, which cannot be resolved.

So now I am not quite sure if this is a bug, but still it is not clear, why OO 3.1.1. can handle the first query, whereas all higher versions can not and which implementations changes might have caused these changes.

The form is unfortunatelly not functionable without the underlying database.

Thank you

Comment 3 Oliver-Rainer Wittmann 2012-06-13 12:17:42 UTC

getting rid of value "enhancement" for field "severity".
For enhancement the field "issue type" shall be used.