wrong inner join result involving subquery containing PERCENT_RANK()
(1.2) By Zhaokun Xiang (silva9988) on 2025-06-10 05:37:12 edited from 1.1 [source]
Hi, SQLite developers
Please see the following cases. I meet an unexpected inner join result. I can reproduce on the SQLite version 3.49.1 2025-02-18 13:38:58 as well as SQLite version 3.25.0 2018-09-15 04:01:47 in which window functions have been introduced.
CREATE TABLE vt0(c0);
INSERT INTO vt0 VALUES(1);
select * from (select PERCENT_RANK() OVER () AS col0) inner join vt0 on (0 LIKE(col0)) and (0 = col0);
-- 0, 1 Wrong result
-- Expected Result: should be empty result
(2) By Gunter Hick (gunter_hick) on 2025-04-23 05:43:21 in reply to 1.1 [link] [source]
The same effect is achieved by replacing 'PERCENT_RANK() OVER ()' by a constant 0.0. The difference is that SQLite puts the like() inside the coroutine in case of left join: 53 InitCoroutine 1 0 2 NULL 0 NULL 54 Yield 1 73 0 NULL 0 NULL 55 Integer 0 12 0 NULL 0 NULL 56 Rewind 1 69 0 NULL 0 NULL 57 Copy 11 14 0 NULL 2 NULL 58 Integer 0 15 0 NULL 0 NULL 59 Function 2 14 13 like(2) 0 NULL 60 IfNot 13 68 1 NULL 0 NULL 61 Copy 11 13 0 NULL 2 NULL 62 Integer 0 16 0 NULL 0 NULL 63 Ne 16 68 13 BINARY-8 80 NULL 64 Integer 1 12 0 NULL 0 NULL 65 Copy 11 17 0 NULL 2 NULL 66 Column 1 0 18 NULL 0 NULL 67 ResultRow 17 2 0 NULL 0 NULL 68 Next 1 57 0 NULL 1 NULL 69 IfPos 12 72 0 NULL 0 NULL 70 NullRow 1 0 0 NULL 0 NULL 71 Goto 0 64 0 NULL 0 NULL 72 Goto 0 54 0 NULL 0 NULL but places it outside the coroutine for an inner join, leaving 0=0.0 which is TRUE 52 Integer 0 13 0 NULL 0 NULL 53 Function 3 13 12 like(2) 0 NULL 54 IfNot 12 66 1 NULL 0 NULL 55 OpenRead 1 72 0 1 0 NULL 56 InitCoroutine 1 0 2 NULL 0 NULL 57 Yield 1 66 0 NULL 0 NULL 58 Copy 11 12 0 NULL 2 NULL 59 Ne 15 65 12 BINARY-8 80 NULL 60 Rewind 1 66 0 NULL 0 NULL 61 Copy 11 16 0 NULL 2 NULL 62 Column 1 0 17 NULL 0 NULL 63 ResultRow 16 2 0 NULL 0 NULL 64 Next 1 61 0 NULL 1 NULL
(3.1) By Gunter Hick (gunter_hick) on 2025-04-23 06:07:07 edited from 3.0 in reply to 2 [link] [source]
On closer examination, SQLite determines that col0 in the term 0 LIKE col0 is NOT a constant value in the outer join; this evaluates to 0 LIKE 0.0, which is evaluated as '0' LIKE '0.0', which is FALSE. However, in the case of the inner join, SQLite determines that col0 in the term 0 LIKE col0 IS a constant value; this evaluates to 0 LIKE 0, which is evaluated as '0' LIKE '0', which is TRUE.
(4) By Zhaokun Xiang (silva9988) on 2025-04-23 08:23:44 in reply to 3.1 [link] [source]
Thanks for your analysis. I try this case in the latest check in. The discrepancy disappears. Do you think it is a bug?
(5) By Richard Hipp (drh) on 2025-04-23 15:52:39 in reply to 1.1 [link] [source]
This is the same problem as your previous post and has the same fix.