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
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')
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?
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
OK, resolving as FIXED then...
It's also OK for Windows XP SP3.
Closing - Sophie