SQLite: View Ticket

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