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.
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.
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.
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.
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.
For my CRASH problem I submitted "Bug 44823 - EDITING: CRASH opening query with Join in particular document"
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.
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
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.
Also: HSQLDB 2.x supports "IS [NOT] DISTINCT FROM"
Created attachment 55674 [details] patch for bug 3)
Created attachment 55675 [details] Use "IS DISTINCT FROM" in refresh query
Created attachment 55676 [details] support "IS DISTINCT FROM" in LibreOffice SQL parser
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