All, I encountered a likely bug during development with latest SQL versions (3.7.17)... It appears to be affecting the result of queries with GROUP BY clause with partial join over two primary keys.
SQL Version 3.7.14 does not have this behavior, and SQL Version 3.7.15 - 3.7.17 are all affected. This may have been fixed since 2013-05-30 builds... On the timeline builds, This version shows still having the bug http://www.sqlite.org/src/info/aebe1f2603 But this version and above does not: http://www.sqlite.org/src/info/001539df4b To recreate the scenario, run the following statements in the P.S. Section. I'm not familiar with the active development of NGQP, but it seems to be very exciting and I hope maybe this can be helpful for the test procedure before the release. Thanks in advance for identifying and fixing the bug. P.S.: Script to recreate the scenario (please see "--This Fails" section for detailed error output). CREATE TABLE T1 (B INTEGER NOT NULL, C INTEGER NOT NULL, D INTEGER NOT NULL, E INTEGER NOT NULL, F INTEGER NOT NULL, G INTEGER NOT NULL, H INTEGER NOT NULL, PRIMARY KEY (B, C, D)); CREATE TABLE T2 (A INTEGER NOT NULL, B INTEGER NOT NULL, C INTEGER NOT NULL, PRIMARY KEY (A, B, C)); INSERT INTO T2(A, B, C) VALUES(702118,16183,15527); INSERT INTO T2(A, B, C) VALUES(702118,16183,15560); INSERT INTO T2(A, B, C) VALUES(702118,16183,15561); INSERT INTO T2(A, B, C) VALUES(702118,16183,15563); INSERT INTO T2(A, B, C) VALUES(702118,16183,15564); INSERT INTO T2(A, B, C) VALUES(702118,16183,15566); INSERT INTO T2(A, B, C) VALUES(702118,16183,15567); INSERT INTO T2(A, B, C) VALUES(702118,16183,15569); INSERT INTO T2(A, B, C) VALUES(702118,16183,15612); INSERT INTO T2(A, B, C) VALUES(702118,16183,15613); INSERT INTO T2(A, B, C) VALUES(702118,16183,15638); INSERT INTO T2(A, B, C) VALUES(702118,16183,15681); INSERT INTO T2(A, B, C) VALUES(702118,16183,15682); INSERT INTO T1(B, C, D, E, F, G, H) INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15527,6,0,5,5,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15560,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15561,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15563,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15564,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15566,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15567,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15569,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15612,6,0,5,5,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15613,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15638,6,0,5,2,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15681,6,0,5,5,0); INSERT INTO T1(B, C, D, E, F, G, H) VALUES(16183,15682,6,0,5,2,0); -- This fails as it does not give me unique results grouped by the criteria SELECT T2.A, T2.B, T1.D, T1.E, T1.F, T1.G, T1.H, MAX(T1.C) FROM T1, T2 WHERE T1.B = T2.B AND T1.C = T2.C GROUP BY T2.A, T2.B, T1.D, T1.E, T1.F, T1.G, T1.H; -- Results: -- 702118|16183|6|0|5|2|0|15569 -- 702118|16183|6|0|5|5|0|15612 -- 702118|16183|6|0|5|2|0|15638 -- 702118|16183|6|0|5|5|0|15681 -- 702118|16183|6|0|5|2|0|15682 -- Somehow this always Succeeds, I changed the group-by order so it is more sensitive to the data... SELECT T2.A, T2.B, T1.D, T1.E, T1.F, T1.G, T1.H, MAX(T1.C) FROM T1, T2 WHERE T1.B = T2.B AND T1.C = T2.C GROUP BY T2.A, T2.B, T1.F, T1.D, T1.E, T1.G, T1.H; -- Results: -- 702118|16183|6|0|5|2|0|15682 -- 702118|16183|6|0|5|5|0|15681 Mi Chen mi.c...@echostar.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
