Problem description: I use a self defined query as basis to generate a report. In this query I calculate an own column in the result of the select statement. This self defined column is used as grouping member in the reportbuilder. when executing the report, I get a syntax error regarding the used select statement. Steps to reproduce: 1. Used Query: SELECT LEFT( UPPER ( "Name" ), 1 ) AS "UName", "Adressliste komplett".* FROM "Adressliste komplett" WHERE "Kennung" IN ( '-', 'n' ) AND "MitglNr" NOT IN ( SELECT "Ehepartner" FROM "Adressliste komplett" WHERE "Kennung" IN ( 'u', 'n' ) ) 2. Used Grouping in the Report: - UName - Name - Vorname - MitglNr 3. Execute report generation Current behavior: SQL-State: S1000 An exception of type com.sun.star.lang.WrappedTargetException was detected Column not found: Adressliste komplett.UName in statement [SELECT LEFT( UPPER ( "Name" ), 1 ) AS "UName", "Adressliste komplett".* FROM ( SELECT "adr".*, "schule"."Schulname", "schule"."Jahrgang", "kennung"."Beschreibung" FROM ( SELECT "adr".*, "heimat"."Heimatort", "heimat"."Heimatanschrift" FROM "Adressen" AS "adr" LEFT OUTER JOIN "Heimatanschrift" AS "heimat" ON "adr"."MitglNr" = "heimat"."MitglNr" ) AS "adr" LEFT OUTER JOIN ( SELECT "jahrgang".*, "schule"."Schulname" FROM "Schuljahrgang" AS "jahrgang", "Schulen" AS "schule" WHERE "jahrgang"."SchulID" = "schule"."SchulID" ) AS "schule" ON "adr"."MitglNr" = "schule"."MitglNr", "Kennung" AS "kennung" WHERE "adr"."Kennung" = "kennung"."Kennung" ) AS "Adressliste komplett" WHERE "Kennung" IN ( '-', 'n' ) AND "MitglNr" NOT IN ( SELECT "Ehepartner" FROM ( SELECT "adr".*, "schule"."Schulname", "schule"."Jahrgang", "kennung"."Beschreibung" FROM ( SELECT "adr".*, "heimat"."Heimatort", "heimat"."Heimatanschrift" FROM "Adressen" AS "adr" LEFT OUTER JOIN "Heimatanschrift" AS "heimat" ON "adr"."MitglNr" = "heimat"."MitglNr" ) AS "adr" LEFT OUTER JOIN ( SELECT "jahrgang".*, "schule"."Schulname" FROM "Schuljahrgang" AS "jahrgang", "Schulen" AS "schule" WHERE "jahrgang"."SchulID" = "schule"."SchulID" ) AS "schule" ON "adr"."MitglNr" = "schule"."MitglNr", "Kennung" AS "kennung" WHERE "adr"."Kennung" = "kennung"."Kennung" ) AS "Adressliste komplett" WHERE "Kennung" IN ( 'u', 'n' ) ) ORDER BY "Adressliste komplett"."UName", "Adressliste komplett"."Name", "Adressliste komplett"."Vorname", "Adressliste komplett"."MitglNr"] The problem is the by the reportbuilder generated ORDER BY-clause. There the tablename given in the FROM clause is set before each column name, even before the self defined column UName. Expected behavior: In LibreOffice 4.0.5 the report worked fine. Operating System: Windows 7 Version: 4.1.1.2 rc
Could you please add an attachment to test, where this query worked and where not? Better idea for the reportbuilder is to create the reports from a view. Problems with the order-by-clause are not new in LO 4.1.* See https://bugs.freedesktop.org/show_bug.cgi?id=55385
Created attachment 85659 [details] Start the report with LO 4.0.* - works. With LO 4.1.* it's wrong SQL-code I could confirm the bug. Have created a little attachment: The data of the report come from the query SELECT UPPER ( "Name" ) AS "Uname", "Table1".* FROM "Table1" This query should be grouped by "Uname". Report-Builder changes the SQL-code to SELECT UPPER ( "Name" ) AS "Uname", "Table1".* FROM "Table1" ORDER BY "Table1"."Uname" and could not find "Table1"."Uname" anywhere. This bug has been introduced with LO 4.1. First version I could test is 4.1.0.0 beta2 (beta2, because beta1 didn't open any report and crashes immediately ...)
According to my testing, this happens only when using a saved query. Work-around: - set the report's "Content Type" property to "SQL Command" - copy/paste the query's SQL code (from "edit as SQL" on the query) into the report's "Content" property
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=6a1ad344a8100b7fe807383356128aa8ee82b6d5 fdo#69147 report sort columns are RESULT columns The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
*** Bug 68656 has been marked as a duplicate of this bug. ***
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=952c5fe20d5975c8a2c9f5093d05472ca0fc78fd&h=libreoffice-4-1 fdo#69147 report sort columns are RESULT columns It will be available in LibreOffice 4.1.3. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
I installed the daily build 2013-09-15_15.03.10 of 4.1.3 and tested it with my database. I can confirm that the bugfix works fine and I am now able to generate all of my reports again. THANX!