Bug 32964 - with gcj, report uses derived table (subquery), but automatic ORDER BY clause uses table names from within the subquery; breaks PostgreSQL, HSQLDB, MySQL
Summary: with gcj, report uses derived table (subquery), but automatic ORDER BY clause...
Status: RESOLVED WONTFIX
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.3.0 RC2
Hardware: Other All
: high critical
Assignee: Not Assigned
URL:
Whiteboard: testing after fixing Bug 32960 necessary
Keywords: regression
Depends on: 32960
Blocks:
  Show dependency treegraph
 
Reported: 2011-01-10 06:11 UTC by Lionel Elie Mamane
Modified: 2015-01-12 10:12 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
odb file showing problem in HSQLDB case (35.32 KB, application/vnd.oasis.opendocument.database)
2011-01-12 23:30 UTC, Lionel Elie Mamane
Details
backtrace with master sources (14.21 KB, text/plain)
2014-07-26 07:25 UTC, Julien Nabet
Details
backtrace from breakpoint (8.66 KB, text/plain)
2014-07-26 07:27 UTC, Julien Nabet
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Lionel Elie Mamane 2011-01-10 06:11:09 UTC
Create a report, e.g. based on the following query:
 SELECT "T"."A", "T"."B", "T"."C", "T"."D" FROM "S"."T" AS "T"
The report has "analyze SQL command" set to "yes".
Edit the report.
Menu View, Sorting.
Add a grouping level, for example on column B.
Try to execute the report: it does not work, one gets SQL error from PostgreSQL:
 missing FROM-clause entry for table "S.T"

That's because the SQL command sent to PostgreSQL looks like:

 SELECT "T"."A", "T"."B", "T"."C", "T"."D" FROM "S"."T" AS "T" ORDER BY "S.T"."B"

There are two layered errors here:
 1) The fully qualified name for that table is "S"."T", not "S.T": the escaping is wrong.
 2) At that point (ORDER BY clause), this table is not availble under its fully qualified name, only under its "local" name in the SELECT, that is just "T".

Consider the equivalent query:

 SELECT "T"."A", "T"."B", "T"."C", "T"."D" FROM "S"."V" AS "T"

Then the "ORDER BY" clause has to call that table "T", not "V", nor "S.V", nor "S"."V", but LibreOffice creates clause like:
 ORDER BY "S.V"."B"

I'm using Debian version 1:3.3.0~rc2-3 of LibreOffice, and the sdbc postgresql driver (version 1:0.7.6+LibO3.3.0~rc2-3).
Comment 1 Lionel Elie Mamane 2011-01-12 23:28:39 UTC
HSQLDB suffers from a similar, yet subtly different, problem. The generated query looks like:

 SELECT * FROM (SELECT "T"."A", "T"."B", "T"."C", "T"."D" FROM "T" "T") ORDER BY "T"."B"

should be
 SELECT * FROM (SELECT "T"."A", "T"."B", "T"."C", "T"."D" FROM "T" "T") ORDER BY "B"
Comment 2 Lionel Elie Mamane 2011-01-12 23:30:53 UTC
Created attachment 41951 [details]
odb file showing problem in HSQLDB case
Comment 3 Lionel Elie Mamane 2011-01-13 15:40:53 UTC
Reconfirmed HSQLDB case with RC3
Comment 4 Lionel Elie Mamane 2011-01-16 00:03:03 UTC
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.
Comment 5 Lionel Elie Mamane 2011-01-16 00:03:44 UTC
I meant this bug and bug #32960.
Comment 6 Lionel Elie Mamane 2011-01-16 00:49:57 UTC
I was a bit quick with my previous comment, sorry.
 - I get the _HSQLDB_ case problem only with GCJ. The PostgreSQL problem happens with both OpenJDK and GCJ.
 - The HSQLDB case is _still_ a regression, I don't get it with OpenOffice.org 3.2.
 - The PostgreSQL case is also a regression (with OpenJDK).
Comment 7 Michael Meeks 2011-01-17 02:52:43 UTC
Lionel, thanks again for your testing; unfortunately - I couldn't easily repeat the issue with my developer build; I got endless moaning about Sun report builder extensions that stopped me being able to access the report at all. It would be good to fix our developer build flags so that that gets built and included I suppose (any chance of a patch for bootstrap/ to fix that on master ?).

Are we certain that this is not a gcj bug ? have you run:

strace -f -o /tmp/slog -e file soffice

and checked that gcj doesn't show up in the /tmp/slog file ?

Thanks !
Comment 8 Thorsten Behrens (CIB) 2011-01-17 11:26:18 UTC
Given the limited scope, removed from 3.3 blocker list
Comment 9 Lionel Elie Mamane 2011-01-18 04:52:59 UTC
(In reply to comment #7)

> I couldn't easily repeat the issue with my developer build; I got
> endless moaning about Sun report builder extensions that stopped me
> being able to access the report at all.

I see.

> Are we certain that this is not a gcj bug ? have you run:

> strace -f -o /tmp/slog -e file soffice

> and checked that gcj doesn't show up in the /tmp/slog file ?

Well, my developer build suddenly refuses to use gcj. It says that
there isn't a Java JRE there. Debian package RC3 is happy to use
gcj. I have to find out why (maybe a patch in the Debian package? A
fix that went into 3.3, but not into master?) before I can do more
tests :-|

There are really two bugs here; if we don't separate them, we'll just end up hopelessly confused, so I separated them. This bug is about the fact that SQL does not allow ORDER BY *outside* of a subquery with names from *within* a subquery, but (with gcj), that's what is being constructed. The other bug is Bug# 33236, and is about the PostgreSQL-specific regression that the table is called "schema.table" instead of "schema"."table".

*This* bug is highly unlikely to be a gcj bug; the fact that the
codepath that triggers this bug is taken *might* be a gcj bug, but
that's bug #32960.

Testing on this bug with MySQL or PostgreSQL necessarily happens with my own build of the reportbuilder extension, where I fixed / worked around bug#32960 by just adding "AS constant" to the "SELECT * FROM (subquery)"; else one does not even come to that issue, one is blocked by bug#32960. But with HSQLDB, pristine reportbuilder also shows this bug.

So this situation of *this* bug is:
 - specific to gcj, as a consequence of the different codepath taken (composer being NULL), related to bug #32960.
 - regression status stricto sensu unclear, as I cannot reproduce bug #32960 in OpenOffice 3.2.1. In a larger understanding of "regression", it is one, as this bug is not triggered in OpenOffice 3.2.1.
Comment 10 Björn Michaelsen 2011-12-23 11:33:46 UTC
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:
http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Comment 11 Jochen 2012-07-25 16:15:00 UTC
Hi Lionel,

is bug still (= for LO 3.6) relevant?
Comment 12 Jochen 2012-08-03 19:03:47 UTC
@Robert:
What is your opinion: underlying bug (32960) "RESOLVED FIXED". This bug also "RESOLVED FIXED"?
Comment 13 Lionel Elie Mamane 2012-08-04 22:48:19 UTC
(In reply to comment #12)
> @Robert:
> What is your opinion: underlying bug (32960) "RESOLVED FIXED".

Bug 32960 just needed to be fixed before one could even reproduce this bug.

> This bug also "RESOLVED FIXED"?

Would need to be retested before we make that judgement.
Comment 14 Jochen 2012-08-05 09:04:43 UTC
(In reply to comment #13)
> Bug 32960 just needed to be fixed before one could even reproduce this bug.

Hi Lionel,

comprehension question: Bug 32960 is marked as "RESOLVED FIXED". Why do you write "Bug 32960 just needed to be fixed"?
Comment 15 Lionel Elie Mamane 2012-08-05 10:23:19 UTC
(In reply to comment #14)
> (In reply to comment #13)
>> Bug 32960 just needed to be fixed before one could even reproduce this bug.

> comprehension question: Bug 32960 is marked as "RESOLVED FIXED". Why do you
> write "Bug 32960 just needed to be fixed"?

On 10 January 2011, bug 32960 was not "RESOLVED FIXED". So at that time, before one could see this bug (bug 32964), one first needed to fix bug 32960 and then only one could reproduce bug 32964. That is because when doing the steps to reproduce bug 32964, one would get an error message pertaining to bug 32960 before one could even get to the error message pertaining to bug 32964.
Comment 16 Jochen 2012-08-17 15:44:45 UTC
Hi Robert,

have you time/capability to reproduce the reported behavior for the newest LO-version 3.6.1 RC1?
Comment 17 Robert Großkopf 2012-08-17 16:20:07 UTC
(In reply to comment #16)
> Hi Robert,
> 
> have you time/capability to reproduce the reported behavior for the newest
> LO-version 3.6.1 RC1?

I have tested it with the attachment and LO 3.3.4 under OpenSuSE 32bit rpm - could not reproduce any error with the report.

Then I tried to start the report with LO 3.6.1 RC1. Every test crashes the whole LO. But this has nothing to do with "analyze SQL command" set to "yes" or set to "no". Must be another problem with that file ...
Comment 18 Robert Großkopf 2012-08-17 16:26:32 UTC
@jochen

This bug is a problem of some jaya-versions with the integrated database and external databases like PostgreSQL and MySQL.
When I tested with LO 3.3.4, I took Java 1.6.0_22;
with LO 3.6.1 RC1 I took Java 1.6.0_29 and Openjdk 1.6.0_24
Comment 19 Jochen 2012-08-17 16:46:29 UTC
set importance to "high critical" (reason: LO crash)
Comment 20 Julien Nabet 2012-09-22 22:31:22 UTC
On pc Debian x86-64 with 3.5, 3.6 and master sources updated today (and a brand new LO directory profile for each test), I didn't reproduce the crash.

Each test included:
- double click on the report
- right click on the report then Edit

java version "1.7.0_03"
OpenJDK Runtime Environment (IcedTea7 2.1.2) (7u3-2.1.2-2)
OpenJDK 64-Bit Server VM (build 22.0-b10, mixed mode)
Comment 21 Robert Großkopf 2012-09-23 07:01:45 UTC
I have tried to reproduce the behaviour with gcj and the internal HSQLDB (Open SUSE 11.4, gcj 1.5.0). 
I could not start any database with LO 3.3.4 and OOo 3.3, when I choose gcj. LO and OOo crash immediately.
I could start LO 3.6.2.1 with the example database tst.odb and gcj. I could open the table. But when I try to open the report LO freezes.

No problems with any other java-version on my system ...
I haven't worked with gcj before. I don't know, who would need it, when there is a free OpenJDK.

Main-problem seem to be the external databases.
Comment 22 Julien Nabet 2012-09-23 07:44:55 UTC
By rereading the bug title, I've just noticed it contained gcj.
Sorry for the noise then since I use OpenJdk.
Comment 23 Julien Nabet 2012-11-16 13:01:46 UTC
Lionel/Michael/Thorsten: could http://cgit.freedesktop.org/libreoffice/core/commit/?id=60d2248aef20aa1363ef8624ff303c854c68dafe ("fixed java classpath parameter (required for gcj)") helped a bit here? If yes, could it be interesting to cherry-pick this commit to 3.6 branch?
Comment 24 Lionel Elie Mamane 2012-11-16 14:26:55 UTC
(In reply to comment #23)
> Lionel/Michael/Thorsten: could
> http://cgit.freedesktop.org/libreoffice/core/commit/
> ?id=60d2248aef20aa1363ef8624ff303c854c68dafe ("fixed java classpath
> parameter (required for gcj)") helped a bit here?

Possibly. Again, the codepath taken by gcj was different at the time I reported the bug:


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 + ")"
    (...)
  }


So, if above patch causes gcj to take the "if" branch instead of the "else" branch, it could fix this bug. But this needs to be tested. A few weeks ago, LibreOffice Base / embedded HSQLDB would not run with gcj at all (see e.g. http://lists.freedesktop.org/archives/libreoffice/2012-July/034921.html)...
Comment 25 Terrence Enger 2013-09-12 20:07:28 UTC
Bug 69147 "REPORTBUILDER: Wrong order by-clause generated by reportbuilder" looks very similar to this.  I have refrained from marking it a DUP of this one because the report says that his report worked in 4.0.5.
Comment 26 Julien Nabet 2014-07-26 07:25:24 UTC
Created attachment 103490 [details]
backtrace with master sources

Just to give an updated, I could reproduce the problem with master sources updated yesterday + gcj 4.9
Comment 27 Julien Nabet 2014-07-26 07:27:49 UTC
Created attachment 103491 [details]
backtrace from breakpoint

I put a breakpoint at the location where it triggers the popup:
Impossible to open the document "Assets".

[jni_uno bridge error] UNO calling Java method activate:
java.lang.NullPointerException
java stack trace:
java.lang.NullPointerException
  at java.lang.reflect.Method.getType(libgcj.so.15)
  at java.lang.reflect.Method.toString(libgcj.so.15)
Comment 28 Julien Nabet 2014-07-26 07:28:52 UTC
As a noteside, trying to open the table in gdb session (still with gcj), I've got a lot of these:
Program received signal SIGPWR, Power fail/restart.
[Switching to Thread 0x2aaad42c5700 (LWP 6549)]
pthread_cond_wait@@GLIBC_2.3.2 () at ../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_cond_wait.S:185
185	../nptl/sysdeps/unix/sysv/linux/x86_64/pthread_cond_wait.S: Aucun fichier ou dossier de ce type.
(gdb) c
Continuing.

Program received signal SIGXCPU, CPU time limit exceeded.
0x00002aaaab29a782 in do_sigsuspend (set=0x2aaad4045680) at ../sysdeps/unix/sysv/linux/sigsuspend.c:31
31	../sysdeps/unix/sysv/linux/sigsuspend.c: Aucun fichier ou dossier de ce type.
(gdb) c
Continuing.
Comment 29 Alex Thurgood 2015-01-03 17:39:57 UTC
Adding self to CC if not already on
Comment 30 Julien Nabet 2015-01-10 09:54:00 UTC
Just for the record, "This branch is taken by gcj" and "This branch is taken by OpenJdk" is nowhere to been seen now in master sources.
So perhaps it's ok now, I can't succeed in using gcj in LO so can't give it a try again.
Comment 31 Lionel Elie Mamane 2015-01-10 10:13:36 UTC
(In reply to Julien Nabet from comment #30)
> Just for the record, "This branch is taken by gcj" and "This branch is taken
> by OpenJdk" is nowhere to been seen now in master sources.
> So perhaps it's ok now, I can't succeed in using gcj in LO so can't give it
> a try again.

These two comments were "comments in the bug about the code", not "comments in the actual code". That code is still there, file reportbuilder/java/org/libreoffice/report/SDBCReportDataFactory.java method "private void fillOrderStatement".

Anyway, it seems that now LibreOffice-with-gcj is again that deeply broken that one does not even get to this issue. As in comment 26 and comment 27, one gets an even earlier error...

I'm somewhat tempted to just abandon and say "LibreOffice does not work with GCJ at all and will not".
Comment 32 Julien Nabet 2015-01-11 22:26:50 UTC
(In reply to Lionel Elie Mamane from comment #31)
...
> 
> Anyway, it seems that now LibreOffice-with-gcj is again that deeply broken
> that one does not even get to this issue. As in comment 26 and comment 27,
> one gets an even earlier error...
> 
> I'm somewhat tempted to just abandon and say "LibreOffice does not work with
> GCJ at all and will not".
I'm wondering about the future of gcj reading this https://gcc.gnu.org/java/index.html (last news are from 2009!)

BTOH, I read this too http://www.phoronix.com/scan.php?page=news_item&px=MTU3OTI

So I don't know what to think about the interest to fix "gcj only" bugs?

Stephan: perhaps you may have some opinion about this?
Comment 33 Stephan Bergmann 2015-01-12 10:08:19 UTC
I guess it would be acceptable to close this as wontfix.
Comment 34 Julien Nabet 2015-01-12 10:12:35 UTC
Thank you Stephan for your feedback.
So following Lionel's comment and yours, let's put this one to WONTFIX.