Bug Hunting Session
Bug 32958 - query design: once a join has two fields, cannot go back to one field
Summary: query design: once a join has two fields, cannot go back to one field
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.3.0 RC2
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:4.0.0.0.beta0 target:4.1.0
Keywords:
Depends on:
Blocks:
 
Reported: 2011-01-10 05:29 UTC by Lionel Elie Mamane
Modified: 2012-12-12 15:45 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Step by step to reproduce the bug. (216.99 KB, application/x-download)
2012-07-31 08:07 UTC, Robert Großkopf
Details
Database for reproducing the bug. Query with 2 joins. (5.79 KB, application/vnd.sun.xml.base)
2012-07-31 08:13 UTC, Robert Großkopf
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 05:29:14 UTC

    
Comment 1 Lionel Elie Mamane 2011-01-10 05:41:52 UTC
Create a new query, using a join on two (or more) fields on _each_ side of the join.
For example:
 SELECT A.foo, B.bar FROM A INNER JOIN B on A.f1=B.f1 AND A.f2=B.f2
Edit the query in Design View.
Double-click on one of the two lines representing the join (making the connection between two tables).
Remove one field from the join, by selecting "blank" instead of a field name in one of the lines describing the join (on both sides of the join).
Notice that the line does not go away but stays blank.
Save, try to execute the query, error because LibreOffice tries to use an empty column name.
For example, with MySQL one gets:
 Unknown column 'A.' in 'where clause".

View the query in SQL, it looks like:
 SELECT "A"."foo", "B"."bar" FROM "db.A" AS "A", "db.B" AS "B" WHERE "A"."f1"="B"."f1" AND "A".""="B".""
Notice the empty column name

Go back to design view. The query now works.

Go back to SQL view. The query now looks like:

 SELECT "A"."foo", "B"."bar" FROM "db.A" AS "A", "db.B" AS "B" WHERE "A"."f1"="B"."f1" AND "A"."f1"="A"."f1"
Notice the second clause has become a tautology.

In the design view, it is shown as a join of the table A upon itself.
Comment 2 Lionel Elie Mamane 2011-01-13 15:44:14 UTC
Reconfirmed with RC3
Comment 3 Björn Michaelsen 2011-12-23 11:33:41 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 4 Jochen 2012-07-25 16:13:33 UTC
(In reply to comment #2)
> Reconfirmed with RC3

Hi Lionel,

is bug still (= for LO 3.6) relevant?
Comment 5 Lionel Elie Mamane 2012-07-30 17:36:34 UTC
Reconfirmed with libreoffice-3-6
Comment 6 Jochen 2012-07-31 06:45:54 UTC
Hi Lionel,

On German discuss-ML we have discussed this bug. Our opinion is:  we lack the sense for such a query.
You link two tables with 2 connections. Normally a table is connected to another by the following way: primary key of the second table is a foreign key of the first table. Example:
Table 1: "Name" - contains ID, first name, last name
Table 2: "Address" - contains ID, Street, City
To link the tables together in Table 1 is added the field "AnschriftID". This field contents  the field "id" of table "address".
Based on this construction You make following:
Table 1: "Name" - contains ID, first name, last name
Table 2: "Address" - contains ID, street address, city, first name, last name
No key field of a first table is a foreign key in the second table. A connection concerning unique values is only possible by as many links to the second table.
You have to link "first name" with "first name" and "last Name" with "last Name" and so on. This design requires double entries in the tables and is of course subject to error, even if the entries correctly in the fields be made.
Finally, it may exist for example more people called "Peter Smith". Then you produce existing two equal persons four records and existing three equal persons nine records and so on.
May be Your problem is not a bug but a not correct using of the database.
Comment 7 Robert Großkopf 2012-07-31 08:07:42 UTC
Created attachment 64982 [details]
Step by step to reproduce the bug.
Comment 8 Robert Großkopf 2012-07-31 08:13:55 UTC
Created attachment 64983 [details]
Database for reproducing the bug. Query with 2 joins.

I have added a description and a database to reproduce the bug. I didn't have used the GUI-links before to link 2 tables with 2 links - only have used foreign keys in a table - so it has to be only one field.

Could be that the example-database isn't a good example - but it makes the bug reproducable for unexperienced database-user.
Comment 9 Not Assigned 2012-12-12 15:38:01 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8735769e6ab926efff117abf731fa769b90bc563&g=libreoffice-4-0

fdo#32958 join editor: remove empty lines


It will be available in LibreOffice 4.0.

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 10 Not Assigned 2012-12-12 15:45:27 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=058deb4a4c422dea78b9a39bd294b7b50965d10d

fdo#32958 join editor: remove empty lines



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.