Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -5464,18 +5464,23 @@ SrcList *pTabList, /* Search for self-joins in this FROM clause */ struct SrcList_item *pThis /* Search for prior reference to this subquery */ ){ struct SrcList_item *pItem; for(pItem = pTabList->a; pItempSelect==0 ) continue; if( pItem->fg.viaCoroutine ) continue; if( pItem->zName==0 ) continue; if( sqlite3_stricmp(pItem->zDatabase, pThis->zDatabase)!=0 ) continue; if( sqlite3_stricmp(pItem->zName, pThis->zName)!=0 ) continue; - if( sqlite3ExprCompare(0, - pThis->pSelect->pWhere, pItem->pSelect->pWhere, -1) - ){ + pS1 = pItem->pSelect; + if( pThis->pSelect->selId!=pS1->selId ){ + /* The query flattener left two different CTE tables with identical + ** names in the same FROM clause. */ + continue; + } + if( sqlite3ExprCompare(0, pThis->pSelect->pWhere, pS1->pWhere, -1) ){ /* The view was modified by some other optimization such as ** pushDownWhereTerms() */ continue; } return pItem; Index: test/with1.test ================================================================== --- test/with1.test +++ test/with1.test @@ -1041,7 +1041,26 @@ WITH c(i)AS(VALUES(9)UNION SELECT~i FROM c)SELECT max(5)>i fROM c; } {0} do_execsql_test 20.2 { WITH c(i)AS(VALUES(5)UNIoN SELECT 0)SELECT min(1)-i fROM c; } {1} + +# 2018-12-26 +# Two different CTE tables with the same name appear in within a single FROM +# clause due to the query-flattener optimization. make sure this does not cause +# problems. This problem was discovered by Matt Denton. +# +do_execsql_test 21.1 { + WITH RECURSIVE t21(a,b) AS ( + WITH t21(x) AS (VALUES(1)) + SELECT x, x FROM t21 ORDER BY 1 + ) + SELECT * FROM t21 AS tA, t21 AS tB +} {1 1 1 1} +do_execsql_test 21.2 { + SELECT printf('', + EXISTS (WITH RECURSIVE Table0 AS (WITH Table0 AS (SELECT DISTINCT 1) + SELECT *, * FROM Table0 ORDER BY 1 DESC) + SELECT * FROM Table0 NATURAL JOIN Table0)); +} {{}} finish_test