/ Check-in [28643b85]
Login

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

Overview
Comment:Use "COMPOUND" instead of "COMPOSITE" in the EXPLAIN QUERY PLAN output to describe UNION, UNION ALL, EXCEPT and INTERSECT operations.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | experimental
Files: files | file ages | folders
SHA1: 28643b85d93d27a44b9370e4087efa8fa2af7f8e
User & Date: dan 2010-11-11 17:48:51
Context
2010-11-12
15:36
Reduce the number of branches that need to be tested in the explainIndexRange() function of where.c. check-in: 6fdae9a6 user: drh tags: experimental
2010-11-11
17:48
Use "COMPOUND" instead of "COMPOSITE" in the EXPLAIN QUERY PLAN output to describe UNION, UNION ALL, EXCEPT and INTERSECT operations. check-in: 28643b85 user: dan tags: experimental
16:46
Add a row of EXPLAIN QUERY PLAN output for each composite select operation (UNION, EXCEPT etc.) in the query. check-in: 00fb8468 user: dan tags: experimental
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

   827    827     int iSub2,                      /* Subquery id 2 */
   828    828     int bUseTmp                     /* True if a temp table was used */
   829    829   ){
   830    830     assert( op==TK_UNION || op==TK_EXCEPT || op==TK_INTERSECT || op==TK_ALL );
   831    831     if( pParse->explain==2 ){
   832    832       Vdbe *v = pParse->pVdbe;
   833    833       char *zMsg = sqlite3MPrintf(
   834         -        pParse->db, "COMPOSITE SUBQUERIES %d AND %d %s(%s)", iSub1, iSub2,
          834  +        pParse->db, "COMPOUND SUBQUERIES %d AND %d %s(%s)", iSub1, iSub2,
   835    835           bUseTmp?"USING TEMP B-TREE ":"", selectOpName(op)
   836    836       );
   837    837       sqlite3VdbeAddOp4(v, OP_Explain, pParse->iSelectId, 0, 0, zMsg, P4_DYNAMIC);
   838    838     }
   839    839   }
   840    840   
   841    841   /*

Changes to test/eqp.test.

   213    213   # Test cases eqp-4.* - tests for composite select statements.
   214    214   #
   215    215   do_eqp_test 4.1.1 {
   216    216     SELECT * FROM t1 UNION ALL SELECT * FROM t2
   217    217   } {
   218    218     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   219    219     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   220         -  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION ALL)} 
          220  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 
   221    221   }
   222    222   do_eqp_test 4.1.2 {
   223    223     SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
   224    224   } {
   225    225     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   226    226     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   227    227     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   228    228     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   229         -  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION ALL)} 
          229  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 
   230    230   }
   231    231   do_eqp_test 4.1.3 {
   232    232     SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
   233    233   } {
   234    234     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   235    235     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   236    236     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   237    237     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   238         -  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION)} 
          238  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} 
   239    239   }
   240    240   do_eqp_test 4.1.4 {
   241    241     SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
   242    242   } {
   243    243     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   244    244     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   245    245     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   246    246     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   247         -  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (INTERSECT)} 
          247  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} 
   248    248   }
   249    249   do_eqp_test 4.1.5 {
   250    250     SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
   251    251   } {
   252    252     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   253    253     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   254    254     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   255    255     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   256         -  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (EXCEPT)} 
          256  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 
   257    257   }
   258    258   
   259    259   do_eqp_test 4.2.2 {
   260    260     SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
   261    261   } {
   262    262     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   263    263     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   264    264     2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 
   265         -  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION ALL)} 
          265  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 
   266    266   }
   267    267   do_eqp_test 4.2.3 {
   268    268     SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
   269    269   } {
   270    270     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   271    271     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   272    272     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   273    273     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   274         -  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (UNION)} 
          274  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} 
   275    275   }
   276    276   do_eqp_test 4.2.4 {
   277    277     SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
   278    278   } {
   279    279     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   280    280     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   281    281     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   282    282     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   283         -  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (INTERSECT)} 
          283  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} 
   284    284   }
   285    285   do_eqp_test 4.2.5 {
   286    286     SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
   287    287   } {
   288    288     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   289    289     1 0 0 {USE TEMP B-TREE FOR ORDER BY}
   290    290     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   291    291     2 0 0 {USE TEMP B-TREE FOR ORDER BY}
   292         -  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 (EXCEPT)} 
          292  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 
   293    293   }
   294    294   
   295    295   do_eqp_test 4.3.1 {
   296    296     SELECT x FROM t1 UNION SELECT x FROM t2
   297    297   } {
   298    298     1 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   299    299     2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   300         -  0 0 0 {COMPOSITE SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} 
          300  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} 
   301    301   }
   302    302   
   303    303   do_eqp_test 4.3.2 {
   304    304     SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
   305    305   } {
   306    306     2 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   307    307     3 0 0 {SCAN TABLE t2 (~1000000 rows)} 
   308         -  1 0 0 {COMPOSITE SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
          308  +  1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
   309    309     4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   310         -  0 0 0 {COMPOSITE SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
          310  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
   311    311   }
   312    312   do_eqp_test 4.3.3 {
   313    313     SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
   314    314   } {
   315    315     2 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   316    316     2 0 0 {USE TEMP B-TREE FOR ORDER BY} 
   317    317     3 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)} 
   318         -  1 0 0 {COMPOSITE SUBQUERIES 2 AND 3 (UNION)} 
          318  +  1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)} 
   319    319     4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
   320    320     4 0 0 {USE TEMP B-TREE FOR ORDER BY} 
   321         -  0 0 0 {COMPOSITE SUBQUERIES 1 AND 4 (UNION)}
          321  +  0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)}
   322    322   }
   323    323   
   324    324   finish_test
   325    325