SQLite

View Ticket
Login
Plaintext
2021-03-30
19:18 • Fixed ticket [de7db147]: subquery with limit clause fails as EXISTS operand plus 6 other changes (artifact: 5cfeb90d user: drh)
2021-03-29
23:11 • New ticket [de7db147]. (artifact: 794b3b38 user: larrybr)
21:16
Do not do the EXISTS-to-IN transformation if the sub-select has LIMIT clause. Ticket [de7db14784a08053] (check-in: 3bf8a357 user: dan tags: branch-3.35)
Ticket Hash: de7db14784a080535bcb85b46d39c32e8aff78f8
Title: subquery with limit clause fails as EXISTS operand
Status: Fixed Type: Code_Defect
Severity: Severe Priority: High
Subsystem: Unknown Resolution: Fixed
Last Modified: 2021-03-30 19:18:45
Version Found In: 3.35.3
User Comments:
larrybr added on 2021-03-29 23:11:30:

This table setup and query, -- From test/exists2.test CREATE TABLE t3(a TEXT PRIMARY KEY, b TEXT, x INT) WITHOUT ROWID; CREATE TABLE t4(c TEXT COLLATE nocase, y INT); INSERT INTO t3 VALUES('one', 'i', 1); INSERT INTO t3 VALUES('two', 'ii', 2); INSERT INTO t3 VALUES('three', 'iii', 3); INSERT INTO t3 VALUES('four', 'iv', 4); INSERT INTO t3 VALUES('five', 'v', 5); INSERT INTO t4 VALUES('FIVE',5), ('four',4), ('TWO',2), ('one',1); -- new test case: SELECT a FROM t3 WHERE EXISTS (SELECT 1 FROM t4 WHERE (a,x)=(c,y) LIMIT 1); returns nothing when, without the limit clause, it returns more (as it should.)

Reported via email by Marco Bonardo mbonardo@mozilla.com Date: Mon, 29 Mar 2021 22:00:52 +0200