/ Check-in [21235d9a]
Login

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

Overview
Comment:Fix the handling of sub-queries with LIMIT clauses by the optimization activated by compile-time symbol SQLITE_COUNTOFVIEW_OPTIMIZATION.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 21235d9a41567897418aa12f7bd6dd8d6ee363147527e1d8fbca14fc83e0f2c9
User & Date: dan 2018-08-03 20:19:52
Context
2018-08-04
15:16
Ensure that all expressions that are to be evaluated once at the start of a prepared statement (the Parse.pConstExpr expressions) pass the sqlite3ExprIsConstantNotJoin() test. It is not sufficient to pass just the sqlite3ExprIsConstant() test as that would allow through column references that are bound to constants by the WHERE clause in the constant propagation optimization. This fixes a problem discovered by OSSFuzz. check-in: 8bc7f84c user: drh tags: trunk
2018-08-03
23:04
Completely remove the column cache logic, which has been a persistent source of bugs for many years. Due to recent enhancements to the performance of the OP_Column opcode, removing the column cache actually makes speed-check.sh run faster. Removing the column cache also saves about 1,800 bytes of code space. check-in: 3f5f60cd user: drh tags: omit-column-cache
20:19
Fix the handling of sub-queries with LIMIT clauses by the optimization activated by compile-time symbol SQLITE_COUNTOFVIEW_OPTIMIZATION. check-in: 21235d9a user: dan tags: trunk
15:58
Fix the OP_SeekRowid opcode so that it has no type-change side-effects on the key register in P3. This fixes an obcure problem that arises when doing equi-joins between a table with a TEXT column against another table with an INTEGER PRIMARY KEY. The original problem was discovered when OSSFuzz created such a query and hit an assert() in OP_VerifyTabCol that was specifically designed to catch these kinds of errors at run-time. Test cases for this fix are in TH3. check-in: fa94b49e user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  5491   5491   ** Into this:
  5492   5492   **
  5493   5493   **    SELECT (SELECT count(*) FROM t1)+(SELECT count(*) FROM t2)
  5494   5494   **
  5495   5495   ** The transformation only works if all of the following are true:
  5496   5496   **
  5497   5497   **   *  The subquery is a UNION ALL of two or more terms
         5498  +**   *  The subquery does not have a LIMIT clause
  5498   5499   **   *  There is no WHERE or GROUP BY or HAVING clauses on the subqueries
  5499   5500   **   *  The outer query is a simple count(*)
  5500   5501   **
  5501   5502   ** Return TRUE if the optimization is undertaken.
  5502   5503   */
  5503   5504   static int countOfViewOptimization(Parse *pParse, Select *p){
  5504   5505     Select *pSub, *pPrior;
................................................................................
  5514   5515     if( p->pSrc->nSrc!=1 ) return 0;                  /* One table in FROM  */
  5515   5516     pSub = p->pSrc->a[0].pSelect;
  5516   5517     if( pSub==0 ) return 0;                           /* The FROM is a subquery */
  5517   5518     if( pSub->pPrior==0 ) return 0;                   /* Must be a compound ry */
  5518   5519     do{
  5519   5520       if( pSub->op!=TK_ALL && pSub->pPrior ) return 0;  /* Must be UNION ALL */
  5520   5521       if( pSub->pWhere ) return 0;                      /* No WHERE clause */
         5522  +    if( pSub->pLimit ) return 0;                      /* No LIMIT clause */
  5521   5523       if( pSub->selFlags & SF_Aggregate ) return 0;     /* Not an aggregate */
  5522   5524       pSub = pSub->pPrior;                              /* Repeat over compound */
  5523   5525     }while( pSub );
  5524   5526   
  5525   5527     /* If we reach this point then it is OK to perform the transformation */
  5526   5528   
  5527   5529     db = pParse->db;
................................................................................
  5774   5776         SELECTTRACE(0x100,pParse,p,("After constant propagation:\n"));
  5775   5777         sqlite3TreeViewSelect(0, p, 0);
  5776   5778       }
  5777   5779   #endif
  5778   5780     }else{
  5779   5781       SELECTTRACE(0x100,pParse,p,("Constant propagation not helpful\n"));
  5780   5782     }
         5783  +
         5784  +#ifdef SQLITE_COUNTOFVIEW_OPTIMIZATION
         5785  +  if( OptimizationEnabled(db, SQLITE_QueryFlattener|SQLITE_CountOfView)
         5786  +   && countOfViewOptimization(pParse, p)
         5787  +  ){
         5788  +    if( db->mallocFailed ) goto select_end;
         5789  +    pEList = p->pEList;
         5790  +    pTabList = p->pSrc;
         5791  +  }
         5792  +#endif
  5781   5793   
  5782   5794     /* For each term in the FROM clause, do two things:
  5783   5795     ** (1) Authorized unreferenced tables
  5784   5796     ** (2) Generate code for all sub-queries
  5785   5797     */
  5786   5798     for(i=0; i<pTabList->nSrc; i++){
  5787   5799       struct SrcList_item *pItem = &pTabList->a[i];
................................................................................
  5951   5963     sDistinct.isTnct = (p->selFlags & SF_Distinct)!=0;
  5952   5964   
  5953   5965   #if SELECTTRACE_ENABLED
  5954   5966     if( sqlite3SelectTrace & 0x400 ){
  5955   5967       SELECTTRACE(0x400,pParse,p,("After all FROM-clause analysis:\n"));
  5956   5968       sqlite3TreeViewSelect(0, p, 0);
  5957   5969     }
  5958         -#endif
  5959         -
  5960         -#ifdef SQLITE_COUNTOFVIEW_OPTIMIZATION
  5961         -  if( OptimizationEnabled(db, SQLITE_QueryFlattener|SQLITE_CountOfView)
  5962         -   && countOfViewOptimization(pParse, p)
  5963         -  ){
  5964         -    if( db->mallocFailed ) goto select_end;
  5965         -    pEList = p->pEList;
  5966         -    pTabList = p->pSrc;
  5967         -  }
  5968   5970   #endif
  5969   5971   
  5970   5972     /* If the query is DISTINCT with an ORDER BY but is not an aggregate, and 
  5971   5973     ** if the select-list is the same as the ORDER BY list, then this query
  5972   5974     ** can be rewritten as a GROUP BY. In other words, this:
  5973   5975     **
  5974   5976     **     SELECT DISTINCT xyz FROM ... ORDER BY xyz

Added test/countofview.test.

            1  +# 2018-08-04
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# 
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +source $testdir/malloc_common.tcl
           17  +set testprefix countofview
           18  +
           19  +do_execsql_test 1.0 {
           20  +  CREATE TABLE t2(c);
           21  +  CREATE TABLE t3(f);
           22  +
           23  +  INSERT INTO t2 VALUES(1), (2);
           24  +  INSERT INTO t3 VALUES(3);
           25  +}
           26  +
           27  +do_execsql_test 1.1 {
           28  +  select c from t2 union all select f from t3 limit 1 offset 1
           29  +} {2}
           30  +
           31  +do_execsql_test 1.2 {
           32  +  select count(*) from (
           33  +    select c from t2 union all select f from t3 limit 1 offset 1
           34  +  )
           35  +} {1}
           36  +
           37  +do_execsql_test 1.3 {
           38  +  select count(*) from (
           39  +    select c from t2 union all select f from t3
           40  +  )
           41  +} {3}
           42  +
           43  +finish_test
           44  +