Download it now!
Bug 31240 (Ribot) - SQL queries involving a join make incorrect results.
Summary: SQL queries involving a join make incorrect results.
Status: CLOSED FIXED
Alias: Ribot
Product: LibreOffice
Classification: Unclassified
Component: LibreOffice (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: x86 (IA32) Windows (All)
: high major
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: regression
Depends on:
Blocks:
 
Reported: 2010-10-30 10:33 UTC by ribotb
Modified: 2011-01-12 07:55 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
zip file containing database and results with OOo321 and LibO330b2 (99.78 KB, application/x-zip)
2010-10-30 10:33 UTC, ribotb
Details

Note You need to log in before you can comment on or make changes to this bug.
Description ribotb 2010-10-30 10:33:05 UTC
Created attachment 39910 [details]
zip file containing database and results with OOo321 and LibO330b2

Queries are created by clicking the icon in the left pane application window's databases.
The following cases were tested (on Windows XP SP3 and Windows 7) :
- "equi jointure" : a simple join that displays the label of sex for each student 
- innerjoin : same result as equi-jointure
- self join : displays the number of students who have a note in the subject number 1 less than the note obtained in the subject number 2.
- left outer join : displays all students having whether or not, a note. If a student does not
have a note, the field Numetu of the corresponding table (NOTES) will be NULL.
- right outer join : displays all students who have at least one note
- "théta-jointure" : displays for each student, how many are before him on the alphabetical list names.

Queries with union (UNION, UNION ALL) and intersection (INTERSECT) of tables  gave correct results.

Attacment : a zip file named containing a small database containing the test queries, the results obtained with OOo 3.2.1 and those obtained with Lino 3.3.0 beta 2
Comment 1 William Whalley 2010-11-25 10:50:12 UTC
I confirm this bug with LibreOffice (ooo330m9, libreoffice-build 3.299.2) with some added information.

Using HSQL embedded database on a simple joined table (see below)

This query fails. It gives multiple instances of the first returned record
SELECT “LNAME”,”ADDRESS” FROM NAMES INNER JOIN ADDR ON NAMES.ADDR_ID=ADDR.ADDR_ID

This query succeeds
SELECT * FROM NAMES INNER JOIN ADDR ON NAMES.ADDR_ID=ADDR.ADDR_ID

Both queries succeed if the “Run SQL command directly” option is used.

Example database

SET DATABASE COLLATION "Latin1_General"
CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE CACHED TABLE "NAMES"("NAME_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"LNAME" VARCHAR(50),"ADDR_ID" INTEGER,"TYPE" VARCHAR(50))
CREATE CACHED TABLE "ADDR"("ADDR_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"ADDRESS" VARCHAR(50),"EXPDATE" DATE)
ALTER TABLE "NAMES" ADD CONSTRAINT SYS_FK_64 FOREIGN KEY("ADDR_ID") REFERENCES "ADDR"("ADDR_ID")
ALTER TABLE "NAMES" ALTER COLUMN "NAME_ID" RESTART WITH 4
ALTER TABLE "ADDR" ALTER COLUMN "ADDR_ID" RESTART WITH 2
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 60
SET SCHEMA PUBLIC
INSERT INTO "NAMES" VALUES(0,'Jones',0,'I')
INSERT INTO "NAMES" VALUES(1,'Smith',0,'I')
INSERT INTO "NAMES" VALUES(2,'Johnson',1,'J')
INSERT INTO "NAMES" VALUES(3,'Gates',1,'J')
INSERT INTO "ADDR" VALUES(0,'Oak Lane','2010-01-01')
INSERT INTO "ADDR" VALUES(1,'Elm Lane','2010-02-01')
Comment 2 Drew Jensen 2010-11-30 21:52:54 UTC
Checked with Ubuntu 10.10 (64bit) Gnome, LibO 3.3 Beta 3, SUN Java 1.6.0_22
First created the DB per the second comment - both queries run as expected.
Ran the queries in the ODB file in the attached zip file - all queries appear to return correct results.

Can double check this on XP - Win 7 tomorrow if needed.

@ribotb Have you had a chance to run these using beta 3?
Comment 3 ribotb 2010-12-01 02:32:46 UTC
I just installed beta 3 on Windows 7. It seems that the bug has been corrected. All my test cases give a correct result. 
I'll install the beta 3 on Windows XP SP3 and I give you the result as soon as possible.
Bernard Ribot
Comment 4 Don't use this account, use tml@iki.fi 2010-12-01 02:58:41 UTC
OK, resolving as FIXED then...
Comment 5 ribotb 2010-12-01 03:45:16 UTC
It's also OK for Windows XP SP3.
Comment 6 sophie 2011-01-12 07:55:56 UTC
Closing - Sophie