Re: [sqlite] Incorrect query result

2019-09-27 Thread Richard Hipp
On 9/27/19, Eric Boudaillier  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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Incorrect query result

2019-09-27 Thread 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Re: [sqlite] Incorrect query result

2019-08-14 Thread Jay Kreibich

Alas, the mailing list does not allow attachments.

 -j


> On Aug 14, 2019, at 8:24 AM, Eric Boudaillier  
> wrote:
> 
> Hi,
> 
> I am experiencing incorrect query result with SQLite 3.25.2 and 3.28.
> Attached are the database and a Tcl script running 3 queries.
> 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.
> 
> The first query fills an array with number of rows returned per "sens"
> attribute.
> The second query is the same, but using "WHERE ITI1.sens = 1".
> The third query is the same as the second, but without the GROUP BY in the
> WITH clause.
> 
> The second query returns nothing, which is not expected, as shown in the
> result of the first query.
> 
> 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

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


[sqlite] Incorrect query result

2019-08-14 Thread Eric Boudaillier
Hi,

I am experiencing incorrect query result with SQLite 3.25.2 and 3.28.
Attached are the database and a Tcl script running 3 queries.
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.

The first query fills an array with number of rows returned per "sens"
attribute.
The second query is the same, but using "WHERE ITI1.sens = 1".
The third query is the same as the second, but without the GROUP BY in the
WITH clause.

The second query returns nothing, which is not expected, as shown in the
result of the first query.

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