Hi,
I am experiencing incorrect query result with SQLite 3.25.2 and 3.28.
The database and the queries have been reduced to the minimum, so they
are not really relevant, but demonstrates better where is the problem.
Also note that the result is correct using SQLite 3.15.2.
Using the following table:
CREATE TABLE generated_ITI (
id INTEGER PRIMARY KEY,
nom VARCHAR,
sens INTEGER
);
INSERT INTO generated_ITI VALUES
(1, '6001_6023', 1),
(2, '6001_6025', 1),
(3, '6018_5934', 0)
The following query returns, for each "ITI", others "ITI" on the opposite:
WITH ITI_cmd AS (
SELECT id, nom, sens
FROM generated_ITI
GROUP BY id)
SELECT *
FROM ITI_cmd AS ITI1
JOIN ITI_cmd AS ITI2
WHERE ITI1.sens <> ITI2.sens
Running the same query, but adding "ITI1.sens = 1" (or "ITI1.sens =
1") gives no result:
WITH ITI_cmd AS (
SELECT id, nom, sens
FROM generated_ITI
GROUP BY id)
SELECT *
FROM ITI_cmd AS ITI1
JOIN ITI_cmd AS ITI2
WHERE ITI1.sens <> ITI2.sens
AND ITI1.sens = 1
Running the same query as above, but without the GROUP BY in the WITH
clause make the query work again:
WITH ITI_cmd AS (
SELECT id, nom, sens
FROM generated_ITI)
SELECT ITI1.id, ITI2.nom, ITI1.sens
FROM ITI_cmd AS ITI1
JOIN ITI_cmd AS ITI2
WHERE ITI1.sens <> ITI2.sens
AND ITI1.sens = 1
Thank you and kind regards,
Eric
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
