I reproduced this bug in different circumstances:
 - MySQL, not only PostgreSQL
 - "analyze SQL command" set to "Yes"
 - The data source (property "Content") set to e.g. "SELECT foo, bar FROM qux WHERE baz=0" (the presence of a WHERE clause is of essence)

Then, when one executes the report, it fails with MySQL error message "Every derived table must have its own alias". That is because LibreOffice sends the following request to MySQL:

SELECT * FROM (SELECT foo, bar FROM qux WHERE baz=0) WHERE 0=1

Again, this should be:

SELECT * FROM (SELECT foo, bar FROM qux WHERE baz=0) AS SOME_NAME WHERE 0=1

As this bug happens not only with PostgreSQL, but also with MySQL, and more importantly since it breaks also the "analyze SQL command" case as soon as there is a WHERE clause in the query, I raised its severity.

Reconfirmed with RC3 (package from "experimental" section of Debian)

I get this bug only with gcj, not with OpenJDK. The cause for the different behaviour is in function fillOrderStatement of reportbuilder/java/com/sun/star/report/SDBCReportDataFactory.java:

            final XSingleSelectQueryComposer composer = getComposer(tools, command, commandType);
            if (composer != null)
            {

I get this bug only with gcj, not with OpenJDK. The cause for the different
behaviour is in function fillOrderStatement of reportbuilder/java/com/sun/star/report/SDBCReportDataFactory.java:

  final XSingleSelectQueryComposer composer = getComposer(tools, command, commandType);
  if (composer != null)
  {
    // This branch is taken by OpenJDK
    (...)
  }
  else
  {
    // This branch is taken by gcj
    (...)
    statement = "SELECT * FROM (" + command + ")"
    (...)
  }

This probably means:

 - It should have lower priority / severity
 - This is not a regression: I probably tested OO.org 3.2 with OpenJDK...
 - Does not fit the release blocker criteria.

After this bug and bug#32964, are corrected, IMHO thee "analyze SQL: no" case (EscapeProcessing = false) should _always_ take the second branch (or do a similar thing from within composer): this allows to add an "order by" clause to an SQL command without parsing it, and thus to use a EscapeProcessing=false SQL command in a report that uses grouping. An alternative would be _not_ to add the ORDER BY clause at all, thus having the query still go through successfully, and rely on the user to put the ORDER BY clause that fits the grouping done by the report himself in the SQL command.

ok - thanks for the analysis ! it is encouraging that this is gcj related. If the functionality changes depending on Java run-time, this could well be a gcj bug unrelated to LibreOffice anyway :-)

So - un-marking as a blocker; great job debugging it though, any chance you can roll the debugging a few frames further back to see why getComposer is returning NULL ?

Also - if this branch produces broken output, would we be better off with some sort of thrown exception there ? and/or is there a clean code fix for that branch ?

Thanks !

(In reply to comment #5)

> So - un-marking as a blocker; great job debugging it though, any chance you can
> roll the debugging a few frames further back to see why getComposer is
> returning NULL ?

I haven't managed to get gdb to give me anything meaningful as far as Java code is concerned, nor to set a breakpoint in java code. I'd appreciate a pointer to a tutorial on how to debug Java code, generally or in OpenOffice.org/LibreOffice if that's any different.

http://gcc.gnu.org/java/gdb.html says gdb supports Java since 5.x days, but the documentation of 7.x is mum about Java; it is not listed as a supported language.

Also, my dev build suddenly refuses to use gcj for some reason, I have to investigate why.

> Also - if this branch produces broken output, would we be better off with some
> sort of thrown exception there ? and/or is there a clean code fix for that
> branch ?

This branch (or the same behaviour implemented within composer or somewhere else) has the potential, once bug #32964 is fixed, to allow a fix for bug #33240, so I wouldn't like to see it dropped completely.

Created attachment 42160 [details] [review]
patch for "invalid SQL" issue

So, there are two ways to fix this:
 1) Fix the fact that "composer" is NULL with gcj (which might be a gcj bug)
 2) Give the derived table (subquery) an alias (name).

IMHO the fact that we do the first should not keep us from doing the second, for added robustness.

The attached patch does the second.

Thanks for the patch, pushed to master. Resolving bug, please reopen if you find that the patch is not enough after all.