/ Check-in [005d5b87]
Login

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

Overview
Comment:Disable the query flattener optimization for SELECT statements that are on the RHS of vector IN operators. This is a hack that fixes the bug described in ticket [da7841375186386c]. A better solution that does not disable the query flattener is needed, but this will server for the time being.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 005d5b870625d175fdf3c0e87d974006c569d9e1
User & Date: drh 2016-11-17 13:13:25
References
2017-11-17
21:01
Improved fix for ticket [da78413751863] that does not require disabling the query flattener as was done in [005d5b870625]. This also makes the code generator for vector IN operators a little easier to understand. check-in: 723f1be3 user: drh tags: trunk
Context
2016-11-23
21:01
Disable the query flattener optimization for SELECT statements that are on the RHS of vector IN operators. This is a hack that fixes the bug described in ticket [da7841375186386c]. A better solution that does not disable the query flattener is needed, but this will server for the time being. check-in: 27438fb4 user: drh tags: branch-3.15
2016-11-17
13:54
Change the --enable-debug option on configure so that it enables the ".selecttrace" and ".wheretrace" commands in the command-line shell. check-in: 2331192b user: drh tags: trunk
13:13
Disable the query flattener optimization for SELECT statements that are on the RHS of vector IN operators. This is a hack that fixes the bug described in ticket [da7841375186386c]. A better solution that does not disable the query flattener is needed, but this will server for the time being. check-in: 005d5b87 user: drh tags: trunk
2016-11-15
21:17
Add a needed #include to speedtest1.c. check-in: 77aeb705 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

   442    442       }
   443    443   
   444    444       if( (pX->flags & EP_xIsSelect)==0 || pX->x.pSelect->pEList->nExpr==1 ){
   445    445         eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, 0);
   446    446       }else{
   447    447         Select *pSelect = pX->x.pSelect;
   448    448         sqlite3 *db = pParse->db;
          449  +      u16 savedDbOptFlags = db->dbOptFlags;
   449    450         ExprList *pOrigRhs = pSelect->pEList;
   450    451         ExprList *pOrigLhs = pX->pLeft->x.pList;
   451    452         ExprList *pRhs = 0;         /* New Select.pEList for RHS */
   452    453         ExprList *pLhs = 0;         /* New pX->pLeft vector */
   453    454   
   454    455         for(i=iEq;i<pLoop->nLTerm; i++){
   455    456           if( pLoop->aLTerm[i]->pExpr==pX ){
................................................................................
   485    486             pX->pLeft = pLhs->a[0].pExpr;
   486    487           }else{
   487    488             pLeft->x.pList = pLhs;
   488    489             aiMap = (int*)sqlite3DbMallocZero(pParse->db, sizeof(int) * nEq);
   489    490             testcase( aiMap==0 );
   490    491           }
   491    492           pSelect->pEList = pRhs;
          493  +        db->dbOptFlags |= SQLITE_QueryFlattener;
   492    494           eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, aiMap);
          495  +        db->dbOptFlags = savedDbOptFlags;
   493    496           testcase( aiMap!=0 && aiMap[0]!=0 );
   494    497           pSelect->pEList = pOrigRhs;
   495    498           pLeft->x.pList = pOrigLhs;
   496    499           pX->pLeft = pLeft;
   497    500         }
   498    501         sqlite3ExprListDelete(pParse->db, pLhs);
   499    502         sqlite3ExprListDelete(pParse->db, pRhs);

Changes to test/rowvalue3.test.

   197    197       do_execsql_test 4.$tn.$tn2 "
   198    198         SELECT c FROM hh WHERE (a, b) in (SELECT x, y FROM k1) ORDER BY $orderby
   199    199       " $res
   200    200     }
   201    201   }
   202    202   
   203    203   #-------------------------------------------------------------------------
          204  +
          205  +# 2016-11-17.  Query flattening in a vector SELECT on the RHS of an IN
          206  +# operator.  Ticket https://www.sqlite.org/src/info/da7841375186386c
          207  +#
          208  +do_execsql_test 5.0 {
          209  +  DROP TABLE IF EXISTS t1;
          210  +  DROP TABLE IF EXISTS t2;
          211  +  CREATE TABLE T1(a TEXT);
          212  +  INSERT INTO T1(a) VALUES ('aaa');
          213  +  CREATE TABLE T2(a TEXT PRIMARY KEY,n INT);
          214  +  INSERT INTO T2(a, n) VALUES('aaa',0);
          215  +  SELECT * FROM T2
          216  +  WHERE (a,n) IN (SELECT T1.a, V.n FROM T1, (SELECT * FROM (SELECT 0 n)) V);
          217  +} {aaa 0}
   204    218   
   205    219   
   206    220   finish_test