Bug Hunting Session
Bug 44813 - UI: Scrolling in queries with join ends with only one empty row
Summary: UI: Scrolling in queries with join ends with only one empty row
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
Master old -3.6
Hardware: x86 (IA32) All
: medium major
Assignee: Lionel Elie Mamane
URL:
Whiteboard: target:3.6.0 target:3.5.0
Keywords:
Depends on:
Blocks:
 
Reported: 2012-01-15 11:40 UTC by Robert Großkopf
Modified: 2012-02-08 09:21 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Query "With_Join" - with not so high windows you have to scroll. (5.75 KB, application/vnd.sun.xml.base)
2012-01-15 11:40 UTC, Robert Großkopf
Details
patch for bug 3) (5.75 KB, patch)
2012-01-17 06:07 UTC, Lionel Elie Mamane
Details
Use "IS DISTINCT FROM" in refresh query (1.76 KB, patch)
2012-01-17 06:09 UTC, Lionel Elie Mamane
Details
support "IS DISTINCT FROM" in LibreOffice SQL parser (1.02 KB, patch)
2012-01-17 06:10 UTC, Lionel Elie Mamane
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2012-01-15 11:40:37 UTC
Created attachment 55610 [details]
Query "With_Join" - with not so high windows you have to scroll.

A query with two tables, linked with a "left join". All data of the first table should be shown.
When opening the query it looks nice. But when the window, which shows data, isn't high enough for all data you have to scroll. Then all data disappear.
Comment 1 Jochen 2012-01-15 12:06:44 UTC
Not report directly to the. But: TDF needs to make sure a stable database. The problem is that LO 3.5 can only conditionally be used, because Base does not work properly. Some people still use 3.3 to have a robust database.

Robert has reported some bugs in Bugzilla.

Sorry for the harsh words. Please fix the most important problems concerning Base.
Comment 2 Rainer Bielefeld Retired 2012-01-15 22:31:18 UTC
If you test with 3.4, LibO might crash.

[Reproducible] with Parallel Dev-Installation of  "LibreOffice 3.5.0 Beta3 - WIN7 Home Premium (64bit) English UI [Build-ID: e40af8c-10029e3-615e522-88673a2-727f724]:

0. Start LibO from WIN programs start center
1. Open attached "Join_Test.odb" from LibP Start Center File Menu
2. Click Queries
3. Double click "With_Join" 
   > Result table opens, looks fine
4. Scroll down slowly using scroll slider
   Expected: resting rows until last one (41) appear
   Actual: as expected until row 40, then Row "<Auto Field> appears,
           scroll slider disappears, no way back (except 'Refresh')

Works fine for query "Without_Join"

Results with older versions:
3.3.0 Portable can not read the query "With_Join"
3.4.5 will crash (I will submit an additional Bug)

Other Master versions I checked (I tested back until July 2011) all showed the same behavior as 3.5.0Beta

@Lionel:
Please feel free to reassign (or reset Assignee to default) if it’s not your area or if provided information is not sufficient. Please set Status to ASSIGNED if you accept this Bug.
Comment 3 Lionel Elie Mamane 2012-01-16 00:37:55 UTC
Testing with 3.4.4 (Debian package), I get error message:

 The data content could not be loaded.

 No data is available.

This seems linked to having in the result two columns with the same name; if one renames one of the "ID" columns in the example, it goes through OK.
Comment 4 Lionel Elie Mamane 2012-01-16 03:05:31 UTC
I'm working on it; the root cause is that the seek cursor thinks it is on the one-after-last row, while it is not; it is on the first row. This leads it to refusing to give a bookmark, and having a dummy row number of 0, which explains why no row is displayed.
Comment 5 Rainer Bielefeld Retired 2012-01-16 04:22:28 UTC
For my CRASH problem I submitted "Bug 44823 - EDITING: CRASH opening query with Join in particular document"
Comment 6 Robert Großkopf 2012-01-17 00:02:05 UTC
When changing the query and setting one filed with an alias it's the same:
"Name".*, "Adresse"."ID" AS "ADI"
Older versions of LO say: No data is available. 3.5 shows data and after scrolling there is only the position "0".
Direct SQL works with this query in older LO-versions and in 3.5. But I need the GUI, because I want to edit data. And I need the *, because it should show all fields.
Could be the query works when all fields are written in it.
Comment 7 Lionel Elie Mamane 2012-01-17 05:27:22 UTC
The symptoms described here are a chain consequence of a whole pile of bugs:

1) When showing the blank (insert new) row at the end, Base refreshes (rereads from the database) the last data row.

2) It does so by issuing "SELECT ... WHERE pKey1=Value1 AND pKey2=Value2", where pKey1, pKey2, ... are the columns of the primary key of all tables involved in the join. Value1, Value2, ... are the va
Comment 8 Lionel Elie Mamane 2012-01-17 06:04:57 UTC
The symptoms described here are a chain consequence of a whole pile of bugs:

1) When showing the blank (insert new) row at the end, Base refreshes (rereads
from the database) the last data row.

2) It does so by issuing "SELECT ... WHERE pKey1=Value1 AND pKey2=Value2",
where pKey1, pKey2, ... are the columns of the primary key of all tables
involved in the join. Value1, Value2, ... are the values previously read for this row.

3) When reading rows, if a non-NULLable column (as per metadata) column has value NULL, then Base replaces that by a type-default value. For integer values, this is 0.

Analysis:

1) is not hugely problematic per se; it just does some not strictly necessary work, and gateways us into the next bug.

2) leads to the row not being found again, since pKey2=NULL evaluates to NULL, which coerces to falsity in test: no row will match. This happens in our example (and will happen in LEFT/RIGHT/CROSS joins in general) for rows where the table of pKey2 has no matching row.

3) has the same effect as 2): the value used in the test is not even the right one (0 instead of NULL).

So, now the last data row is removed from the display (since it is assumed to be deleted, since the refresh query returns an empty set), Base refreshed the new "last row", and we cycle until no row is left.


Fixing:

3) is rather easy to fix; I attach the patch and will commit it to libreoffice-3-5 and master (LibreOffice 3.6) branches. What worries me a bit, is that this change may have been made to fix some other bug which I may now be reintroducing. This change was made in:

d4420993 (Ivo Hinkelmann        2009-04-23 10:42:05 +0000)

The commit message (a mercurial merge message) gives me no inspiration... If someone can do code archeology in oo.org and find a the non-merge commit message that did this, maybe we can get a better idea on this.


2) is more problematic, but not impossible. The refresh query is a static parametric query, where the Value1, Value2, ... above are parameters that are filled in later. ANSI SQL has an easy fix for this, in the form of the "IS [NOT] DISTINCT FROM" operator, which has the desired behaviour on NULL values (namely "pKey IS NOT DISTINCT FROM NULL" is TRUE when pKey is NULL). I attach the patch to change the refresh query to that operator. However, not all databases support that operator, and in particular HSQL 1.8 (that is bundled with LibreOffice) does not. Another datapoint: SQLite supports that feature, but under a different syntax, a rather natural extension to the ANSI SQL "IS [NOT] NULL" construct, namely "pKey IS [NOT] Value1". As such these patches are not OK for inclusion into LibreOffice.

The only portable solution I see is to make the query dynamic, and use "pKey1 = Value1" or "pKey1 IS NULL" as the case may be. That's more work, but I'll do it sooner or later.
Comment 9 Lionel Elie Mamane 2012-01-17 06:05:30 UTC
Also: HSQLDB 2.x supports "IS [NOT] DISTINCT FROM"
Comment 10 Lionel Elie Mamane 2012-01-17 06:07:29 UTC
Created attachment 55674 [details]
patch for bug 3)
Comment 11 Lionel Elie Mamane 2012-01-17 06:09:07 UTC
Created attachment 55675 [details]
Use "IS DISTINCT FROM" in refresh query
Comment 12 Lionel Elie Mamane 2012-01-17 06:10:06 UTC
Created attachment 55676 [details]
support "IS DISTINCT FROM" in LibreOffice SQL parser
Comment 13 Lionel Elie Mamane 2012-01-18 04:26:10 UTC
Fixed in libreoffice-3-5 and master (3.6).

I found a way to (hopefully) portably do a NULL-safe sameness test without making the query dynamic. A bit hackish, but works well in tests.

See http://cgit.freedesktop.org/libreoffice/core/commit/?id=3623701d65f92017da905f4debf5514045f502c8