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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
