/ Check-in [c75eee69]
Login

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

Overview
Comment:Enhance EXPLAIN QUERY PLAN to report the generation of constant rows using VALUES or just a SELECT without FROM.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rework-EQP
Files: files | file ages | folders
SHA3-256:c75eee69fa8a9b56ee58a4cc539e80cc982f43390dc3a357344d58479dd89a41
User & Date: drh 2018-05-03 01:37:13
Context
2018-05-03
19:47
Optimizations to the new EQP framework. Closed-Leaf check-in: 956fef36 user: drh tags: rework-EQP
01:37
Enhance EXPLAIN QUERY PLAN to report the generation of constant rows using VALUES or just a SELECT without FROM. check-in: c75eee69 user: drh tags: rework-EQP
2018-05-02
19:42
More test case updates. Tests are all running now. check-in: dab5e529 user: drh tags: rework-EQP
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  2369   2369   ** Since the limit is exactly 1, we only need to evalutes the left-most VALUES.
  2370   2370   */
  2371   2371   static int multiSelectValues(
  2372   2372     Parse *pParse,        /* Parsing context */
  2373   2373     Select *p,            /* The right-most of SELECTs to be coded */
  2374   2374     SelectDest *pDest     /* What to do with query results */
  2375   2375   ){
  2376         -  Select *pPrior;
  2377         -  Select *pRightmost = p;
  2378   2376     int nRow = 1;
  2379   2377     int rc = 0;
         2378  +  int bShowAll = p->pLimit==0;
  2380   2379     assert( p->selFlags & SF_MultiValue );
  2381   2380     do{
  2382   2381       assert( p->selFlags & SF_Values );
  2383   2382       assert( p->op==TK_ALL || (p->op==TK_SELECT && p->pPrior==0) );
  2384   2383       assert( p->pNext==0 || p->pEList->nExpr==p->pNext->pEList->nExpr );
  2385   2384       if( p->pPrior==0 ) break;
  2386   2385       assert( p->pPrior->pNext==p );
  2387   2386       p = p->pPrior;
  2388         -    nRow++;
         2387  +    nRow += bShowAll;
  2389   2388     }while(1);
  2390         -  ExplainQueryPlan((pParse, 0, "SCAN %d CONSTANT ROWS", nRow));
         2389  +  ExplainQueryPlan((pParse, 0, "SCAN %d CONSTANT ROW%s", nRow,
         2390  +                    nRow==1 ? "" : "S"));
  2391   2391     while( p ){
  2392         -    pPrior = p->pPrior;
  2393         -    p->pPrior = 0;
  2394         -    rc = sqlite3Select(pParse, p, pDest);
  2395         -    p->pPrior = pPrior;
  2396         -    if( rc || pRightmost->pLimit ) break;
         2392  +    selectInnerLoop(pParse, p, -1, 0, 0, pDest, 1, 1);
         2393  +    if( !bShowAll ) break;
  2397   2394       p->nSelectRow = nRow;
  2398   2395       p = p->pNext;
  2399   2396     }
  2400   2397     return rc;
  2401   2398   }
  2402   2399   
  2403   2400   /*

Changes to src/where.c.

  4588   4588     /* Special case: No FROM clause
  4589   4589     */
  4590   4590     if( nTabList==0 ){
  4591   4591       if( pOrderBy ) pWInfo->nOBSat = pOrderBy->nExpr;
  4592   4592       if( wctrlFlags & WHERE_WANT_DISTINCT ){
  4593   4593         pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
  4594   4594       }
  4595         -    /* ExplainQueryPlan((pParse, 0, "SCAN CONSTANT ROW")); */
         4595  +    ExplainQueryPlan((pParse, 0, "SCAN CONSTANT ROW"));
  4596   4596     }else{
  4597   4597       /* Assign a bit from the bitmask to every term in the FROM clause.
  4598   4598       **
  4599   4599       ** The N-th term of the FROM clause is assigned a bitmask of 1<<N.
  4600   4600       **
  4601   4601       ** The rule of the previous sentence ensures thta if X is the bitmask for
  4602   4602       ** a table T, then X-1 is the bitmask for all other tables to the left of T.

Changes to test/eqp.test.

    85     85   }
    86     86   
    87     87   do_eqp_test 1.7 {
    88     88     SELECT * FROM t3 JOIN (SELECT 1)
    89     89   } {
    90     90     QUERY PLAN
    91     91     |--MATERIALIZE xxxxxx
           92  +  |  `--SCAN CONSTANT ROW
    92     93     |--SCAN SUBQUERY xxxxxx
    93     94     `--SCAN TABLE t3
    94     95   }
    95     96   do_eqp_test 1.8 {
    96     97     SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2)
    97     98   } {
    98     99     QUERY PLAN
    99    100     |--MATERIALIZE xxxxxx
   100    101     |  `--COMPOUND QUERY
   101    102     |     |--LEFT-MOST SUBQUERY
          103  +  |     |  `--SCAN CONSTANT ROW
   102    104     |     `--UNION USING TEMP B-TREE
          105  +  |        `--SCAN CONSTANT ROW
   103    106     |--SCAN SUBQUERY xxxxxx
   104    107     `--SCAN TABLE t3
   105    108   }
   106    109   do_eqp_test 1.9 {
   107    110     SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17)
   108    111   } {
   109    112     QUERY PLAN
   110    113     |--MATERIALIZE xxxxxx
   111    114     |  `--COMPOUND QUERY
   112    115     |     |--LEFT-MOST SUBQUERY
          116  +  |     |  `--SCAN CONSTANT ROW
   113    117     |     `--EXCEPT USING TEMP B-TREE
   114    118     |        `--SCAN TABLE t3
   115    119     |--SCAN SUBQUERY xxxxxx
   116    120     `--SCAN TABLE t3
   117    121   }
   118    122   do_eqp_test 1.10 {
   119    123     SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17)
   120    124   } {
   121    125     QUERY PLAN
   122    126     |--MATERIALIZE xxxxxx
   123    127     |  `--COMPOUND QUERY
   124    128     |     |--LEFT-MOST SUBQUERY
          129  +  |     |  `--SCAN CONSTANT ROW
   125    130     |     `--INTERSECT USING TEMP B-TREE
   126    131     |        `--SCAN TABLE t3
   127    132     |--SCAN SUBQUERY xxxxxx
   128    133     `--SCAN TABLE t3
   129    134   }
   130    135   
   131    136   do_eqp_test 1.11 {
   132    137     SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17)
   133    138   } {
   134    139     QUERY PLAN
   135    140     |--MATERIALIZE xxxxxx
   136    141     |  `--COMPOUND QUERY
   137    142     |     |--LEFT-MOST SUBQUERY
          143  +  |     |  `--SCAN CONSTANT ROW
   138    144     |     `--UNION ALL
   139    145     |        `--SCAN TABLE t3
   140    146     |--SCAN SUBQUERY xxxxxx
   141    147     `--SCAN TABLE t3
   142    148   }
   143    149   
   144    150   #-------------------------------------------------------------------------

Changes to test/orderby1.test.

   450    450       
   451    451       SELECT b, y FROM t41 CROSS JOIN t42 ON x=a ORDER BY b, y;
   452    452     }
   453    453   } {1 13 1 14 1 15 1 16}
   454    454   
   455    455   # No sorting of queries that omit the FROM clause.
   456    456   #
   457         -do_execsql_test 5.0 {
   458         -  EXPLAIN QUERY PLAN SELECT 5 ORDER BY 1
   459         -} {}
          457  +do_eqp_test 5.0 {
          458  +  SELECT 5 ORDER BY 1
          459  +} {
          460  +  QUERY PLAN
          461  +  `--SCAN CONSTANT ROW
          462  +}
   460    463   do_execsql_test 5.1 {
   461    464     EXPLAIN QUERY PLAN SELECT 5 UNION ALL SELECT 3 ORDER BY 1
   462    465   } {~/B-TREE/}
   463    466   do_execsql_test 5.2 {
   464    467     SELECT 5 UNION ALL SELECT 3 ORDER BY 1
   465    468   } {3 5}
   466    469   do_execsql_test 5.3 {

Changes to test/with1.test.

  1001   1001       x1(a) AS (values(100))
  1002   1002     INSERT INTO t1(x)
  1003   1003       SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2);
  1004   1004     SELECT * FROM t1;
  1005   1005   } {
  1006   1006     QUERY PLAN
  1007   1007     |--MATERIALIZE xxxxxx
         1008  +  |  `--SCAN CONSTANT ROW
  1008   1009     |--SCAN SUBQUERY xxxxxx
  1009   1010     `--SCAN SUBQUERY xxxxxx
  1010   1011   }
  1011   1012   
  1012   1013   # 2017-10-28.
  1013   1014   # See check-in https://sqlite.org/src/info/0926df095faf72c2
  1014   1015   # Tried to optimize co-routine processing by changing a Copy opcode

Changes to test/with3.test.

    79     79     do_eqp_test 3.1.2 {
    80     80       WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1)
    81     81       SELECT * FROM cnt, y1 WHERE i=a
    82     82     } [string map {"\n  " \n} {
    83     83       QUERY PLAN
    84     84       |--MATERIALIZE xxxxxx
    85     85       |  |--SETUP
           86  +    |  |  `--SCAN CONSTANT ROW
    86     87       |  `--RECURSIVE STEP
    87     88       |     `--SCAN TABLE cnt
    88     89       |--SCAN SUBQUERY xxxxxx
    89     90       `--SEARCH TABLE y1 USING INDEX y1a (a=?)
    90     91     }]
    91     92   
    92     93     do_eqp_test 3.1.3 {
    93     94       WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000)
    94     95       SELECT * FROM cnt, y1 WHERE i=a
    95     96     } [string map {"\n  " \n} {
    96     97       QUERY PLAN
    97     98       |--MATERIALIZE xxxxxx
    98     99       |  |--SETUP
          100  +    |  |  `--SCAN CONSTANT ROW
    99    101       |  `--RECURSIVE STEP
   100    102       |     `--SCAN TABLE cnt
   101    103       |--SCAN TABLE y1
   102    104       `--SEARCH SUBQUERY xxxxxx USING AUTOMATIC COVERING INDEX (i=?)
   103    105     }]
   104    106   }
   105    107   
................................................................................
   113    115        UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1)
   114    116        SELECT * FROM c, w2, w1
   115    117        WHERE c.id=w2.pk AND c.id=w1.pk;
   116    118   } {
   117    119     QUERY PLAN
   118    120     |--MATERIALIZE xxxxxx
   119    121     |  |--SETUP
          122  +  |  |  |--SCAN CONSTANT ROW
   120    123     |  |  `--SCALAR SUBQUERY
   121    124     |  |     `--SCAN TABLE w2
   122    125     |  `--RECURSIVE STEP
   123    126     |     |--SCAN TABLE w1
   124    127     |     `--SCAN TABLE c
   125    128     |--SCAN SUBQUERY xxxxxx
   126    129     |--SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)
   127    130     `--SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)
   128    131   }
   129    132   
   130    133   finish_test