SQLite Forum

Possible inconsistent result when use EXISTS ( SELECT...) expression in a WHERE clause
Login

Possible inconsistent result when use EXISTS ( SELECT...) expression in a WHERE clause

(1) By Yu Liang (LY1598773890) on 2021-05-04 04:01:35 updated by 1.1 [source]

For query:

--------------------------

```SQL
CREATE TABLE v0 ( v1 INTEGER);
INSERT INTO v0 VALUES ( 10 ),  ( 7 );
CREATE TABLE v4 ( v5 INTEGER PRIMARY KEY );
INSERT INTO v4 VALUES ( 10 );

/* First SELECT stmt */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = v1 )) as BOOL) != 0   FROM  v0 ;
/* Output: 1 */

/* Second SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = v1 )) as BOOL) != 0)   FROM  v0 ;
/* Output: 2.0 */

/* Third SELECT stmt, replace v1 with 10, matching the first component in v1. */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 10 )) as BOOL) != 0   FROM  v0 ; 
/* Output: 1 1 */

/* Fourth SELECT stmt, as validation for the third SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 10 )) as BOOL) != 0)   FROM  v0 ;
/* Output: 2.0 */

/* Fifth SELECT stmt, replace v1 with 7, matching the second component in v1. */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 7 )) as BOOL) != 0   FROM  v0 ;
/* Output EMPTY */

/* Sixth SELECT stmt, as validation for the fifth SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 7 )) as BOOL) != 0)   FROM  v0 ;
/* Output 0.0 */

```

-----------------------------------------

The first and the second SELECT stmts contains a correlated subquery in the select clause. We expect the subquery would be evaluated each time when iterating through column v1 from v0. These steps are being shown in the third and the fifth SELECT stmt. However, the second SELECT stmt result is mismatched with the result of the first SELECT stmt. We expect the second result to be 1.0, rather than 2.0 in the reality. We wonder whether it is a mistake in the code or an expected behavior.

This inconsistent behavior from the second SELECT stmt, seems to be introduced in Fossil: 9f90a88221d0694951c353e58efce342eb0b868b8ca6a4469c8205e5c7855b24. 

Looking forward to your reply.

Possible inconsistent result when use EXISTS ( SELECT...) expression in a WHERE clause

(1.1) By Yu Liang (LY1598773890) on 2021-05-04 04:06:22 edited from 1.0 [link] [source]

For query:


CREATE TABLE v0 ( v1 INTEGER);
INSERT INTO v0 VALUES ( 10 ),  ( 7 );
CREATE TABLE v4 ( v5 INTEGER PRIMARY KEY );
INSERT INTO v4 VALUES ( 10 );

/* First SELECT stmt */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = v1 )) as BOOL) != 0   FROM  v0 ;
/* Output: 1 */

/* Second SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = v1 )) as BOOL) != 0)   FROM  v0 ;
/* Output: 2.0 */

/* Third SELECT stmt, replace v1 with 10, matching the first component in v1. */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 10 )) as BOOL) != 0   FROM  v0 ; 
/* Output: 1 1 */

/* Fourth SELECT stmt, as validation for the third SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 10 )) as BOOL) != 0)   FROM  v0 ;
/* Output: 2.0 */

/* Fifth SELECT stmt, replace v1 with 7, matching the second component in v1. */
SELECT cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 7 )) as BOOL) != 0   FROM  v0 ;
/* Output EMPTY */

/* Sixth SELECT stmt, as validation for the fifth SELECT stmt */
SELECT TOTAL(cast ( (SELECT v5 FROM v4 WHERE EXISTS ( SELECT v5 WHERE v5 = 7 )) as BOOL) != 0)   FROM  v0 ;
/* Output 0.0 */


The first and the second SELECT stmts contains a correlated subquery in the select clause. We expect the subquery would be evaluated each time when iterating through column v1. These steps are being shown in the third and the fifth SELECT stmts.

However, the second SELECT stmt result is mismatched with the result of the first SELECT stmt. Because they have a similar correlated subquery inside the select clause, we expect the second result to be 1.0, rather than 2.0 in the reality. We wonder whether it is a mistake in the code or an expected behavior.

This inconsistent behavior from the second SELECT stmt, seems to be introduced in Fossil: 9f90a88221d0694951c353e58efce342eb0b868b8ca6a4469c8205e5c7855b24.

Looking forward to your reply.

(2) By Richard Hipp (drh) on 2021-05-04 12:12:42 in reply to 1.1 [link] [source]

Fixed by backing out the EXISTS-to-IN optimization. The EXISTS-to-IN optimization attempts to convert EXISTS operators into IN operators. It was described by bullet 8b of the change log. We may come back and revisit that optimization opportunity later. But as it was causing other problems besides this bug, it seems best to just back it out for now.

(3) By Yu Liang (LY1598773890) on 2021-05-04 16:45:17 in reply to 2 [link] [source]

Thank you for the explanation.