Incorrect query result

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Incorrect query result

Eric Boudaillier
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Incorrect query result

Richard Hipp-3
On 9/27/19, Eric Boudaillier <[hidden email]> wrote:
>
> I am experiencing incorrect query result with SQLite 3.25.2 and 3.28.

This problem was resolved by check-in
https://sqlite.org/src/info/74ef97bf51dd531a which was included in
release 3.29.0.  The same fix is also found in on branch-3.28
(https://sqlite.org/src/timeline?r=branch-3.28), in case you want to
fix your 3.28 version with a minimal amount of change.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users