Bug Hunting Session
Bug 72163 - Report of query with two primarykeys and over 50 rows fails
Summary: Report of query with two primarykeys and over 50 rows fails
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.1.0.0.beta2
Hardware: x86-64 (AMD64) Linux (All)
: medium major
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:4.3.0 target:4.2.0.0.beta2 tar...
Keywords: regression
Depends on:
Blocks:
 
Reported: 2013-11-29 23:06 UTC by B Mikowski
Modified: 2013-12-17 11:23 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
report, starting with date-parameters of a query. (21.38 KB, application/vnd.sun.xml.base)
2013-11-30 20:10 UTC, Robert Großkopf
Details
Database that demonstrates issue (168.64 KB, application/vnd.oasis.opendocument.database)
2013-11-30 22:43 UTC, B Mikowski
Details
Open the different reports. Doesn't work with two primarykeys and limit > 50 (24.29 KB, application/vnd.sun.xml.base)
2013-12-02 17:03 UTC, Robert Großkopf
Details
console logs (5.17 KB, text/plain)
2013-12-02 21:20 UTC, Julien Nabet
Details

Note You need to log in before you can comment on or make changes to this bug.
Description B Mikowski 2013-11-29 23:06:39 UTC
I have a report with an embedded SQL statement that prompts for parameters. For example "PaymentDate BETWEEN :EnterStartDate END :EnterEndDate".  When I run it in LibreOffice 3.6, it functions correctly.  When running the DB in LO 4.1.3.2, it gives the following error: 'The Document "Report_W_Payments" could not be opened.  Failed to process the report.'  I tried editing the report in the report builder, and I get a little more detail there when executing it.  It produces a few more error lines: 

SQL Status: S1000
An error occurred while creating the report.

An exception of type com.sun.star.lang.WrappedTargetException was caught.

Failed to process the report.

Failed to process the report.

I then went into the SQL builder, and ran the same code interactively, supplying the same parameters, and it returns Zero rows, but does execute.  If I change the criteria to for payment date to ">= :EnterStartDate" and an additional line of "<=:EnterEndDate" and run it interactively, I get the 62 rows that I would expect.

So, it appears as if there may be a few things going on here:  1.  Issues with parameter passing to the SQL engine, and 2. That the newer version of HSQL is improperly handling the between statement for date fields.
Comment 1 Julien Nabet 2013-11-30 18:25:35 UTC
Could you attach the odb file so we can try to reproduce this?
Comment 2 Robert Großkopf 2013-11-30 20:10:14 UTC
Created attachment 90041 [details]
report, starting with date-parameters of a query.

Couldn't confirm the described behavior.
I have tested the attached database, created with LO 3.3.4, with LO 4.1.3.2 and other versions. works with all versions here.
The report reads its data from a query, which will ask for date-parameters.
Comment 3 Robert Großkopf 2013-11-30 20:12:52 UTC
Have forgotten to write down my system:
OpenSUSE 12.3 64bit rpm Linux.
Comment 4 B Mikowski 2013-11-30 22:43:11 UTC
Created attachment 90045 [details]
Database that demonstrates issue

OpenSuse 13.1 x64, LibreOffice Version: 4.1.3.2, Oracle Corp JRE v1.7.0_45
Comment 5 B Mikowski 2013-11-30 22:45:28 UTC
Let me know if you need any more details on system or settings. 
Thanks, 
B
Comment 6 Julien Nabet 2013-12-01 08:13:05 UTC
B Mikowski: thank you for your feedback, I could reproduce the problem.
I put it at NEW.
Comment 7 Robert Großkopf 2013-12-01 18:11:16 UTC
(In reply to comment #5)
> Let me know if you need any more details on system or settings. 
> Thanks, 
> B

Gives also an error on my system.

In the query for the error are two fields, which had a doulbe.
Also the date-parameter doesn't work with "<= or >=" - shows all data.

Could you try to get an example which shows the right hint for the error? When you open https://bugs.freedesktop.org/attachment.cgi?id=90041 you could see a query with a date-parameter that doesn't fail. There must be something else in your report, that let it work before (have tested it in LO 3.3.4, works without showing the startdate and enddate), and gives an error now - and that we have to find.

Regards

Robert
Comment 8 Robert Großkopf 2013-12-01 20:23:48 UTC
Have tried a little bit more with the attached database. The query, which is the start for the report, is a query for two tables. One table has a foreignkey "MemberID", the other the primarykey with the same name. Both are fields of the query. If you delete the second field in the query the report works.

I will try to create a database which shows only this behavior. The date-parameters have nothing to do with the error, which doesn't let start the creation of the database-report.
Comment 9 Robert Großkopf 2013-12-02 16:24:48 UTC
I have reduced the database to two fields in "AssociationMembers" and three fields in "Payments". When I start a report, which runs with a query like
SELECT "AssociationMembers"."MemberID", "Payments"."PaymentDate", "Payments"."PaymentID" FROM "Payments", "AssociationMembers" WHERE "Payments"."MemberID" = "AssociationMembers"."MemberID"
The report could not be executed. If you choose only one primarykey in the query the report could be executed.
So I thought: 2 primarykeys in one query - that's the reason for the bug. Tested it with another database, also primarykeys as Integer - AutoValue. But the report in my database works with such a relation. Don't know if I could find the difference between the two databases ...
Comment 10 Robert Großkopf 2013-12-02 17:03:40 UTC
Created attachment 90113 [details]
Open the different reports. Doesn't work with two primarykeys and limit > 50

I have found the reason for the bug. Seem to be two reasons why it fails with com.sun.star.lang.WrappedTargetException.
First reason: in the query for the report are two primarykeys. 
Second reason: the result of the query has over 50 rows. If it is smaller or the result is limited (LIMIT 50) the report works. If it is bigger than 50 it doesn't work.
Only both reasons together let the report fail.
Comment 11 Robert Großkopf 2013-12-02 17:50:34 UTC
Set Version to 4.1.0.0.beta2, because the bug doesn't appear in LO 4.0.5.2 and couldn't be reproduced with 4.1.0.0.beta1 (reportbuilder chrashes LO in this version)9. So the bug has been introduced with version 4.1.*
Hint: There had been added the creation of LIMIT in the GUI of the queries...
Comment 12 Julien Nabet 2013-12-02 18:04:50 UTC
Robert: great investigation!

Lionel: you might be interested in this one.
Comment 13 Julien Nabet 2013-12-02 21:20:10 UTC
Created attachment 90122 [details]
console logs

I added some logs:
diff --git a/bridges/source/jni_uno/java/com/sun/star/bridges/jni_uno/JNI_proxy.java b/bridges/source/jni_uno/java/com/sun/star/bridges/jni_uno/JNI_proxy.java
index 31861d6..6a99b15 100644
--- a/bridges/source/jni_uno/java/com/sun/star/bridges/jni_uno/JNI_proxy.java
+++ b/bridges/source/jni_uno/java/com/sun/star/bridges/jni_uno/JNI_proxy.java
@@ -156,6 +156,13 @@ public final class JNI_proxy implements java.lang.reflect.InvocationHandler
     {
         Class decl_class = method.getDeclaringClass();
         String method_name = method.getName();
+if (method_name.equals("last")) {
+System.out.println("TODO JUL decl_class " + decl_class.getName());
+System.out.println("TODO JUL m_class " + m_class.getName());
+System.out.println("TODO JUL method_name " + method_name);
+System.out.println("TODO JUL Object.class " + Object.class.getName());
+System.out.println("TODO JUL args " + args);
+}
 
         if (Object.class.equals( decl_class ))
         {

and launched the report OK then the KO one. I attached the result in the file.
But I don't know where to find this method last.
Comment 14 Julien Nabet 2013-12-02 22:17:38 UTC
Just found the last method in dbaccess/source/core/api/RowSetCache.cxx line 1126

I'll keep on digging on it but it seems the problem is more precisely in moveWindow function (same file).
Comment 15 Commit Notification 2013-12-02 22:57:54 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d4b7e3d12a02d27e9ea569e4828370cf00e17540

fdo#72163 after updating m_xComposer, command facets are not dirty anymore



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.
Comment 16 Commit Notification 2013-12-02 22:58:30 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=56cdaa186971eedbac1b494b46c2622862386e1a

fdo#72163 belts and suspenders



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.
Comment 17 Commit Notification 2013-12-02 22:59:54 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=938f0e7fe4216d135815b98fdb918f97e747c6bd&h=libreoffice-4-2

fdo#72163 after updating m_xComposer, command facets are not dirty anymore


It will be available in LibreOffice 4.2.

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.
Comment 18 Commit Notification 2013-12-02 23:00:10 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-2":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=5bfdb6cd5f80f87c5bc6b0bccdad5aad00a45ba3&h=libreoffice-4-2

fdo#72163 belts and suspenders


It will be available in LibreOffice 4.2.

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.
Comment 19 Commit Notification 2013-12-04 10:53:26 UTC
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=d4a709cbf4710555b1518816f1eaa9b43dc5cae7&h=libreoffice-4-1

fdo#72163 after updating m_xComposer, command facets are not dirty anymore


It will be available in LibreOffice 4.1.5.

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.
Comment 20 Commit Notification 2013-12-16 08:52:42 UTC
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=4f93ef75127d32da84bbf0275e679a0c9ce7b98d&h=libreoffice-4-1

fdo#72163: fix safer way


It will be available in LibreOffice 4.1.5.

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.
Comment 21 Commit Notification 2013-12-16 08:54:02 UTC
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=5f9d293732bfbf39c2663f28d7f59a2a94339f5a&h=libreoffice-4-1

Revert "fdo#72163 after updating m_xComposer, command facets are not dirty"


It will be available in LibreOffice 4.1.5.

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.