/ Check-in [202dd033]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix a problem in which nested CTEs with the same table name trick the code generator into thinking they are the same CTE, which then tries to use the manifest them both into the same transient table.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 202dd033019dd27428e3cc5f6e164c95b37efe39e2753515112b201ddefca67b
User & Date: drh 2018-12-27 02:16:01
Context
2018-12-27
07:46
Fix a null-pointer dereference that could occur in fts5 with a corrupt database. check-in: b0a49d5e user: dan tags: trunk
02:16
Fix a problem in which nested CTEs with the same table name trick the code generator into thinking they are the same CTE, which then tries to use the manifest them both into the same transient table. check-in: 202dd033 user: drh tags: trunk
00:30
In the treeview debugging output, provide additional details for the Table object associated with each FROM clause term. check-in: 11d4682d user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/select.c.

  5462   5462   */
  5463   5463   static struct SrcList_item *isSelfJoinView(
  5464   5464     SrcList *pTabList,           /* Search for self-joins in this FROM clause */
  5465   5465     struct SrcList_item *pThis   /* Search for prior reference to this subquery */
  5466   5466   ){
  5467   5467     struct SrcList_item *pItem;
  5468   5468     for(pItem = pTabList->a; pItem<pThis; pItem++){
         5469  +    Select *pS1;
  5469   5470       if( pItem->pSelect==0 ) continue;
  5470   5471       if( pItem->fg.viaCoroutine ) continue;
  5471   5472       if( pItem->zName==0 ) continue;
  5472   5473       if( sqlite3_stricmp(pItem->zDatabase, pThis->zDatabase)!=0 ) continue;
  5473   5474       if( sqlite3_stricmp(pItem->zName, pThis->zName)!=0 ) continue;
  5474         -    if( sqlite3ExprCompare(0, 
  5475         -          pThis->pSelect->pWhere, pItem->pSelect->pWhere, -1) 
  5476         -    ){
         5475  +    pS1 = pItem->pSelect;
         5476  +    if( pThis->pSelect->selId!=pS1->selId ){
         5477  +      /* The query flattener left two different CTE tables with identical
         5478  +      ** names in the same FROM clause. */
         5479  +      continue;
         5480  +    }
         5481  +    if( sqlite3ExprCompare(0, pThis->pSelect->pWhere, pS1->pWhere, -1) ){
  5477   5482         /* The view was modified by some other optimization such as
  5478   5483         ** pushDownWhereTerms() */
  5479   5484         continue;
  5480   5485       }
  5481   5486       return pItem;
  5482   5487     }
  5483   5488     return 0;

Changes to test/with1.test.

  1039   1039   #
  1040   1040   do_execsql_test 20.1 {
  1041   1041     WITH c(i)AS(VALUES(9)UNION SELECT~i FROM c)SELECT max(5)>i fROM c;
  1042   1042   } {0}
  1043   1043   do_execsql_test 20.2 {
  1044   1044     WITH c(i)AS(VALUES(5)UNIoN SELECT 0)SELECT min(1)-i fROM c;
  1045   1045   } {1}
         1046  +
         1047  +# 2018-12-26
         1048  +# Two different CTE tables with the same name appear in within a single FROM
         1049  +# clause due to the query-flattener optimization.  make sure this does not cause
         1050  +# problems.  This problem was discovered by Matt Denton.
         1051  +#
         1052  +do_execsql_test 21.1 {
         1053  +   WITH RECURSIVE t21(a,b) AS (
         1054  +    WITH t21(x) AS (VALUES(1))
         1055  +    SELECT x, x FROM t21 ORDER BY 1
         1056  +  )
         1057  +  SELECT * FROM t21 AS tA, t21 AS tB
         1058  +} {1 1 1 1}
         1059  +do_execsql_test 21.2 {
         1060  +  SELECT printf('',
         1061  +     EXISTS (WITH RECURSIVE Table0 AS (WITH Table0 AS (SELECT DISTINCT 1)
         1062  +                                       SELECT *, * FROM Table0 ORDER BY 1 DESC)
         1063  +             SELECT * FROM Table0  NATURAL JOIN  Table0));
         1064  +} {{}}
  1046   1065   
  1047   1066   finish_test