Hi All, We have been using SQLite 3.4.2 for some time. On investigating upgrading to 3.6.2, we found that different results were produced for one query. The following illustrates:
CREATE TABLE tst1( tst1Id INTEGER, width REAL, thickness REAL ); CREATE TABLE tst2( tst2Id INTEGER, tst3Id INTEGER, setType INTEGER, length REAL, number INTEGER ); CREATE TABLE tst3( tst3Id INTEGER, setIdA INTEGER, setIdB INTEGER, setIdC INTEGER, setIdD INTEGER ); INSERT INTO tst1 VALUES( 1, 1.0, 1.1 ); INSERT INTO tst1 VALUES( 2, 2.0, 2.1 ); INSERT INTO tst1 VALUES( 3, 3.0, 3.1 ); INSERT INTO tst1 VALUES( 4, 4.0, 4.1 ); INSERT INTO tst1 VALUES( 5, 5.0, 5.1 ); INSERT INTO tst1 VALUES( 6, 6.0, 6.1 ); INSERT INTO tst1 VALUES( 9, 7.0, 7.1 ); INSERT INTO tst2 VALUES( 1, 1, 1, 101.1, 1 ); INSERT INTO tst2 VALUES( 2, 1, 2, 102.1, 2 ); INSERT INTO tst2 VALUES( 4, 3, 4, 104.1, 2 ); INSERT INTO tst2 VALUES( 5, 4, 3, 105.1, 1 ); INSERT INTO tst2 VALUES( 6, 4, 1, 106.1, 6 ); INSERT INTO tst3 VALUES( 1, 1, 1, 2, 1 ); INSERT INTO tst3 VALUES( 2, 6, 2, 9, 4 ); INSERT INTO tst3 VALUES( 3, 2, 1, 3, 6 ); INSERT INTO tst3 VALUES( 4, 3, 5, 9, 9 ); SELECT tst1.width, SUM( tst2.length * tst2.number ) AS totLength, CASE tst2.setType WHEN 1 THEN tst3.setIdA WHEN 2 THEN tst3.setIdB WHEN 3 THEN tst3.setIdC ELSE tst3.setIdA END AS theSetId FROM tst2 LEFT OUTER JOIN tst3 ON tst2.tst3Id=tst2.tst3Id LEFT OUTER JOIN tst1 ON tst1.tst1Id=theSetId GROUP BY tst1.width>4; On 3.4.2 we get: 3.0|3660.5|3 6.0|1360.3|6 On 3.6.2 we get: |5020.8|3 We have determined that the query above can be rewritten as: SELECT tst1.width, SUM( tst2.length * tst2.number ) AS totLength FROM tst2 LEFT OUTER JOIN tst3 ON tst2.tst3Id=tst2.tst3Id LEFT OUTER JOIN tst1 ON tst1.tst1Id= CASE tst2.setType WHEN 1 THEN tst3.setIdA WHEN 2 THEN tst3.setIdB WHEN 3 THEN tst3.setIdC ELSE tst3.setIdA END GROUP BY tst1.width>4; and both versions then give the same result. However, the SQL from which this is adapted joins several more tables on the 'theSetId' value, and we wanted only one place to perform updates. Anyway, is the behaviour in 3.6.2 correct, or was the behaviour in 3.4.2 incorrect? Thanking you in advance, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users