/ Check-in [eb27086e]
Login

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

Overview
Comment:Remove more vestiges of sqlite_query_plan from the test cases.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: eb27086e8a8a4d5fcb2ea358256a555e34339423
User & Date: drh 2013-06-03 22:08:20
Context
2013-06-04
12:42
Refactor the ORDER BY optimizer in the NGQP so that it is easier to maintain and so that it can support optimizing out GROUP BY and DISTINCT clauses. check-in: e605c468 user: drh tags: nextgen-query-plan-exp
2013-06-03
22:08
Remove more vestiges of sqlite_query_plan from the test cases. check-in: eb27086e user: drh tags: nextgen-query-plan-exp
21:25
Adjust the xBestIndex methods on both the fuzzer and transitive_closure virtual tables so that an unused MATCH operator gets a really large cost. Remove ambiguities from the fuzzer test cases. check-in: e2c1af78 user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/between.test.

    44     44       CREATE INDEX i1zyx ON t1(z,y,x);
    45     45       COMMIT;
    46     46     }
    47     47   } {}
    48     48   
    49     49   # This procedure executes the SQL.  Then it appends to the result the
    50     50   # "sort" or "nosort" keyword depending on whether or not any sorting
    51         -# is done.  Then it appends the ::sqlite_query_plan variable.
           51  +# is done.  Then it appends the names of the table and index used.
    52     52   #
    53     53   proc queryplan {sql} {
    54     54     set ::sqlite_sort_count 0
    55     55     set data [execsql $sql]
    56     56     if {$::sqlite_sort_count} {set x sort} {set x nosort}
    57     57     lappend data $x
    58     58     set eqp [execsql "EXPLAIN QUERY PLAN $sql"]

Changes to test/intpkey.test.

   121    121   #
   122    122   do_test intpkey-1.12.1 {
   123    123     execsql {
   124    124       SELECT * FROM t1 WHERE a==4;
   125    125     }
   126    126   } {4 one two}
   127    127   do_test intpkey-1.12.2 {
   128         -  set sqlite_query_plan
   129         -} {t1 *}
          128  +  execsql {
          129  +    EXPLAIN QUERY PLAN
          130  +    SELECT * FROM t1 WHERE a==4;
          131  +  }
          132  +} {/SEARCH TABLE t1 /}
   130    133   
   131    134   # Try to insert a non-integer value into the primary key field.  This
   132    135   # should result in a data type mismatch.
   133    136   #
   134    137   do_test intpkey-1.13.1 {
   135    138     set r [catch {execsql {
   136    139       INSERT INTO t1 VALUES('x','y','z');

Changes to test/like.test.

   152    152   ifcapable !like_opt {
   153    153     finish_test
   154    154     return
   155    155   } 
   156    156   
   157    157   # This procedure executes the SQL.  Then it appends to the result the
   158    158   # "sort" or "nosort" keyword (as in the cksort procedure above) then
   159         -# it appends the ::sqlite_query_plan variable.
          159  +# it appends the names of the table and index used.
   160    160   #
   161    161   proc queryplan {sql} {
   162    162     set ::sqlite_sort_count 0
   163    163     set data [execsql $sql]
   164    164     if {$::sqlite_sort_count} {set x sort} {set x nosort}
   165    165     lappend data $x
   166         -  return [concat $data $::sqlite_query_plan]
          166  +  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
          167  +  # puts eqp=$eqp
          168  +  foreach {a b c x} $eqp {
          169  +    if {[regexp { TABLE (\w+ AS )?(\w+) USING COVERING INDEX (\w+)\W} \
          170  +        $x all as tab idx]} {
          171  +      lappend data {} $idx
          172  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
          173  +        $x all as tab idx]} {
          174  +      lappend data $tab $idx
          175  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
          176  +      lappend data $tab *
          177  +    }
          178  +  }
          179  +  return $data   
   167    180   }
   168    181   
   169    182   # Perform tests on the like optimization.
   170    183   #
   171    184   # With no index on t1.x and with case sensitivity turned off, no optimization
   172    185   # is performed.
   173    186   #
   174    187   do_test like-3.1 {
   175    188     set sqlite_like_count 0
   176    189     queryplan {
   177    190       SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
   178    191     }
   179         -} {ABC {ABC abc xyz} abc abcd sort t1 {}}
          192  +} {ABC {ABC abc xyz} abc abcd sort t1 *}
   180    193   do_test like-3.2 {
   181    194     set sqlite_like_count
   182    195   } {12}
   183    196   
   184    197   # With an index on t1.x and case sensitivity on, optimize completely.
   185    198   #
   186    199   do_test like-3.3 {
................................................................................
   265    278     set sqlite_like_count
   266    279   } 12
   267    280   
   268    281   # No optimization for case insensitive LIKE
   269    282   #
   270    283   do_test like-3.13 {
   271    284     set sqlite_like_count 0
          285  +  db eval {PRAGMA case_sensitive_like=off;}
   272    286     queryplan {
   273         -    PRAGMA case_sensitive_like=off;
   274    287       SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
   275    288     }
   276    289   } {ABC {ABC abc xyz} abc abcd nosort {} i1}
   277    290   do_test like-3.14 {
   278    291     set sqlite_like_count
   279    292   } 12
   280    293   
   281    294   # No optimization without an index.
   282    295   #
   283    296   do_test like-3.15 {
   284    297     set sqlite_like_count 0
   285         -  queryplan {
          298  +  db eval {
   286    299       PRAGMA case_sensitive_like=on;
   287    300       DROP INDEX i1;
          301  +  }
          302  +  queryplan {
   288    303       SELECT x FROM t1 WHERE x LIKE 'abc%' ORDER BY 1;
   289    304     }
   290         -} {abc abcd sort t1 {}}
          305  +} {abc abcd sort t1 *}
   291    306   do_test like-3.16 {
   292    307     set sqlite_like_count
   293    308   } 12
   294    309   
   295    310   # No GLOB optimization without an index.
   296    311   #
   297    312   do_test like-3.17 {
   298    313     set sqlite_like_count 0
   299    314     queryplan {
   300    315       SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
   301    316     }
   302         -} {abc abcd sort t1 {}}
          317  +} {abc abcd sort t1 *}
   303    318   do_test like-3.18 {
   304    319     set sqlite_like_count
   305    320   } 12
   306    321   
   307    322   # GLOB is optimized regardless of the case_sensitive_like setting.
   308    323   #
   309    324   do_test like-3.19 {
................................................................................
   314    329     }
   315    330   } {abc abcd nosort {} i1}
   316    331   do_test like-3.20 {
   317    332     set sqlite_like_count
   318    333   } 0
   319    334   do_test like-3.21 {
   320    335     set sqlite_like_count 0
          336  +  db eval {PRAGMA case_sensitive_like=on;}
   321    337     queryplan {
   322         -    PRAGMA case_sensitive_like=on;
   323    338       SELECT x FROM t1 WHERE x GLOB 'abc*' ORDER BY 1;
   324    339     }
   325    340   } {abc abcd nosort {} i1}
   326    341   do_test like-3.22 {
   327    342     set sqlite_like_count
   328    343   } 0
   329    344   do_test like-3.23 {
   330    345     set sqlite_like_count 0
          346  +  db eval {PRAGMA case_sensitive_like=off;}
   331    347     queryplan {
   332         -    PRAGMA case_sensitive_like=off;
   333    348       SELECT x FROM t1 WHERE x GLOB 'a[bc]d' ORDER BY 1;
   334    349     }
   335    350   } {abd acd nosort {} i1}
   336    351   do_test like-3.24 {
   337    352     set sqlite_like_count
   338    353   } 6
   339    354   
................................................................................
   805    820       INSERT INTO t11 VALUES(10, 'yz','yz');
   806    821       INSERT INTO t11 VALUES(11, 'X','X');
   807    822       INSERT INTO t11 VALUES(12, 'YZ','YZ');
   808    823       SELECT count(*) FROM t11;
   809    824     }
   810    825   } {12}
   811    826   do_test like-11.1 {
          827  +  db eval {PRAGMA case_sensitive_like=OFF;}
   812    828     queryplan {
   813         -    PRAGMA case_sensitive_like=OFF;
   814    829       SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
   815    830     }
   816    831   } {abc abcd ABC ABCD nosort t11 *}
   817    832   do_test like-11.2 {
          833  +  db eval {PRAGMA case_sensitive_like=ON;}
   818    834     queryplan {
   819         -    PRAGMA case_sensitive_like=ON;
   820    835       SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
   821    836     }
   822    837   } {abc abcd nosort t11 *}
   823    838   do_test like-11.3 {
   824         -  queryplan {
          839  +  db eval {
   825    840       PRAGMA case_sensitive_like=OFF;
   826    841       CREATE INDEX t11b ON t11(b);
          842  +  }
          843  +  queryplan {
   827    844       SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
   828    845     }
   829    846   } {abc abcd ABC ABCD sort {} t11b}
   830    847   do_test like-11.4 {
          848  +  db eval {PRAGMA case_sensitive_like=ON;}
   831    849     queryplan {
   832         -    PRAGMA case_sensitive_like=ON;
   833    850       SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY a;
   834    851     }
   835    852   } {abc abcd nosort t11 *}
   836    853   do_test like-11.5 {
   837         -  queryplan {
          854  +  db eval {
   838    855       PRAGMA case_sensitive_like=OFF;
   839    856       DROP INDEX t11b;
   840    857       CREATE INDEX t11bnc ON t11(b COLLATE nocase);
          858  +  }
          859  +  queryplan {
   841    860       SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
   842    861     }
   843    862   } {abc abcd ABC ABCD sort {} t11bnc}
   844    863   do_test like-11.6 {
          864  +  db eval {CREATE INDEX t11bb ON t11(b COLLATE binary);}
   845    865     queryplan {
   846         -    CREATE INDEX t11bb ON t11(b COLLATE binary);
   847    866       SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
   848    867     }
   849    868   } {abc abcd ABC ABCD sort {} t11bnc}
   850    869   do_test like-11.7 {
          870  +  db eval {PRAGMA case_sensitive_like=ON;}
   851    871     queryplan {
   852         -    PRAGMA case_sensitive_like=ON;
   853    872       SELECT b FROM t11 WHERE b LIKE 'abc%' ORDER BY +a;
   854    873     }
   855    874   } {abc abcd sort {} t11bb}
   856    875   do_test like-11.8 {
          876  +  db eval {PRAGMA case_sensitive_like=OFF;}
   857    877     queryplan {
   858         -    PRAGMA case_sensitive_like=OFF;
   859    878       SELECT b FROM t11 WHERE b GLOB 'abc*' ORDER BY +a;
   860    879     }
   861    880   } {abc abcd sort {} t11bb}
   862    881   do_test like-11.9 {
   863         -  queryplan {
          882  +  db eval {
   864    883       CREATE INDEX t11cnc ON t11(c COLLATE nocase);
   865    884       CREATE INDEX t11cb ON t11(c COLLATE binary);
          885  +  }
          886  +  queryplan {
   866    887       SELECT c FROM t11 WHERE c LIKE 'abc%' ORDER BY +a;
   867    888     }
   868    889   } {abc abcd ABC ABCD sort {} t11cnc}
   869    890   do_test like-11.10 {
   870    891     queryplan {
   871    892       SELECT c FROM t11 WHERE c GLOB 'abc*' ORDER BY +a;
   872    893     }
   873    894   } {abc abcd sort {} t11cb}
   874    895   
   875    896   
   876    897   finish_test

Changes to test/subquery.test.

   237    237     execsql {
   238    238       CREATE INDEX t4i ON t4(x);
   239    239       SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
   240    240     }
   241    241   } {10.0}
   242    242   do_test subquery-2.5.3.2 {
   243    243     # Verify that the t4i index was not used in the previous query
   244         -  set ::sqlite_query_plan
   245         -} {t4 {}}
          244  +  execsql {
          245  +    EXPLAIN QUERY PLAN
          246  +    SELECT * FROM t4 WHERE x IN (SELECT a FROM t3);
          247  +  }
          248  +} {/SCAN TABLE t4 /}
   246    249   do_test subquery-2.5.4 {
   247    250     execsql {
   248    251       DROP TABLE t3;
   249    252       DROP TABLE t4;
   250    253     }
   251    254   } {}
   252    255   

Changes to test/where2.test.

    62     62     if {[db status sort]} {set x sort} {set x nosort}
    63     63     lappend data $x
    64     64     return $data
    65     65   }
    66     66   
    67     67   # This procedure executes the SQL.  Then it appends to the result the
    68     68   # "sort" or "nosort" keyword (as in the cksort procedure above) then
    69         -# it appends the ::sqlite_query_plan variable.
           69  +# it appends the name of the table and index used.
    70     70   #
    71     71   proc queryplan {sql} {
    72     72     set ::sqlite_sort_count 0
    73     73     set data [execsql $sql]
    74     74     if {$::sqlite_sort_count} {set x sort} {set x nosort}
    75     75     lappend data $x
    76     76     set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
................................................................................
    80     80           $x all as tab idx]} {
    81     81         lappend data $tab $idx
    82     82       } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
    83     83         lappend data $tab *
    84     84       }
    85     85     }
    86     86     return $data   
    87         -  # return [concat $data $::sqlite_query_plan]
    88     87   }
    89     88   
    90     89   
    91     90   # Prefer a UNIQUE index over another index.
    92     91   #
    93     92   do_test where2-1.1 {
    94     93     queryplan {

Changes to test/where3.test.

    99     99          FROM parent1
   100    100          LEFT OUTER JOIN child1 ON parent1.child1key = child1.child1key 
   101    101          INNER JOIN child2 ON child2.child2key = parent1.child2key;
   102    102        }]
   103    103   }
   104    104   
   105    105   # This procedure executes the SQL.  Then it appends 
   106         -# the ::sqlite_query_plan variable.
          106  +# the names of the table and index used
   107    107   #
   108    108   proc queryplan {sql} {
   109    109     set ::sqlite_sort_count 0
   110    110     set data [execsql $sql]
   111         -  return [concat $data $::sqlite_query_plan]
          111  +  set eqp [execsql "EXPLAIN QUERY PLAN $sql"]
          112  +  # puts eqp=$eqp
          113  +  foreach {a b c x} $eqp {
          114  +    if {[regexp { TABLE (\w+ AS )?(\w+) USING.* INDEX (\w+)\W} \
          115  +        $x all as tab idx]} {
          116  +      lappend data $tab $idx
          117  +    } elseif {[regexp { TABLE (\w+ AS )?(\w+)\W} $x all as tab]} {
          118  +      lappend data $tab *
          119  +    }
          120  +  }
          121  +  return $data   
   112    122   }
   113    123   
   114    124   
   115    125   # If you have a from clause of the form:   A B C left join D
   116    126   # then make sure the query optimizer is able to reorder the 
   117    127   # A B C part anyway it wants. 
   118    128   #
................................................................................
   140    150       CREATE TABLE tC(cpk integer primary key, cx);
   141    151       CREATE TABLE tD(dpk integer primary key, dx);
   142    152     }
   143    153     queryplan {
   144    154       SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
   145    155        WHERE cpk=bx AND bpk=ax
   146    156     }
   147         -} {tA {} tB * tC * tD *}
          157  +} {tA * tB * tC * tD *}
   148    158   do_test where3-2.1.1 {
   149    159     queryplan {
   150    160       SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
   151    161        WHERE cpk=bx AND bpk=ax
   152    162     }
   153         -} {tA {} tB * tC * tD *}
          163  +} {tA * tB * tC * tD *}
   154    164   do_test where3-2.1.2 {
   155    165     queryplan {
   156    166       SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
   157    167        WHERE bx=cpk AND bpk=ax
   158    168     }
   159         -} {tA {} tB * tC * tD *}
          169  +} {tA * tB * tC * tD *}
   160    170   do_test where3-2.1.3 {
   161    171     queryplan {
   162    172       SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
   163    173        WHERE bx=cpk AND ax=bpk
   164    174     }
   165         -} {tA {} tB * tC * tD *}
          175  +} {tA * tB * tC * tD *}
   166    176   do_test where3-2.1.4 {
   167    177     queryplan {
   168    178       SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
   169    179        WHERE bx=cpk AND ax=bpk
   170    180     }
   171         -} {tA {} tB * tC * tD *}
          181  +} {tA * tB * tC * tD *}
   172    182   do_test where3-2.1.5 {
   173    183     queryplan {
   174    184       SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
   175    185        WHERE cpk=bx AND ax=bpk
   176    186     }
   177         -} {tA {} tB * tC * tD *}
          187  +} {tA * tB * tC * tD *}
   178    188   do_test where3-2.2 {
   179    189     queryplan {
   180    190       SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
   181    191        WHERE cpk=bx AND apk=bx
   182    192     }
   183    193   } {tB {} tA * tC * tD *}
   184    194   do_test where3-2.3 {