/ Check-in [7d91f688]
Login

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

Overview
Comment:Virtual tables now always report 25 rows instead of 0 rows in the EXPLAIN QUERY PLAN output. Adjust tests accordingly.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | nextgen-query-plan-exp
Files: files | file ages | folders
SHA1: 7d91f688815597ff65ec04b7daa21cb9e5d6bf98
User & Date: drh 2013-06-03 15:24:11
Context
2013-06-03
15:34
Fix an issue that was causing ORDER BY DESC to come out in ascending order. check-in: 02984012 user: drh tags: nextgen-query-plan-exp
15:24
Virtual tables now always report 25 rows instead of 0 rows in the EXPLAIN QUERY PLAN output. Adjust tests accordingly. check-in: 7d91f688 user: drh tags: nextgen-query-plan-exp
15:07
Set the WHERE_UNIQUE flag on loops that can only run once. check-in: 510f4d8e user: drh tags: nextgen-query-plan-exp
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to test/fts3aux1.test.

   101    101   db func rec rec
   102    102   
   103    103   # Use EQP to show that the WHERE expression "term='braid'" uses a different
   104    104   # index number (1) than "+term='braid'" (0).
   105    105   #
   106    106   do_execsql_test 2.1.1.1 {
   107    107     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid'
   108         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} }
          108  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} }
   109    109   do_execsql_test 2.1.1.2 {
   110    110     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid'
   111         -} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}}
          111  +} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)}}
   112    112   
   113    113   # Now show that using "term='braid'" means the virtual table returns
   114    114   # only 1 row to SQLite, but "+term='braid'" means all 19 are returned.
   115    115   #
   116    116   do_test 2.1.2.1 {
   117    117     set cnt 0
   118    118     execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' }
................................................................................
   150    150   
   151    151   # Special case: term=NULL
   152    152   #
   153    153   do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {}
   154    154   
   155    155   do_execsql_test 2.2.1.1 {
   156    156     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'
   157         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2: (~0 rows)} }
          157  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2: (~25 rows)} }
   158    158   do_execsql_test 2.2.1.2 {
   159    159     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain'
   160         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
          160  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} }
   161    161   
   162    162   do_execsql_test 2.2.1.3 {
   163    163     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain'
   164         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4: (~0 rows)} }
          164  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4: (~25 rows)} }
   165    165   do_execsql_test 2.2.1.4 {
   166    166     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain'
   167         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
          167  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} }
   168    168   
   169    169   do_execsql_test 2.2.1.5 {
   170    170     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain'
   171         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6: (~0 rows)} }
          171  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6: (~25 rows)} }
   172    172   do_execsql_test 2.2.1.6 {
   173    173     EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain'
   174         -} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
          174  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} }
   175    175   
   176    176   do_test 2.2.2.1 {
   177    177     set cnt 0
   178    178     execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' }
   179    179     set cnt
   180    180   } {18}
   181    181   do_test 2.2.2.2 {
................................................................................
   331    331     5    1    "ORDER BY documents"
   332    332     6    1    "ORDER BY documents DESC"
   333    333     7    1    "ORDER BY occurrences ASC"
   334    334     8    1    "ORDER BY occurrences"
   335    335     9    1    "ORDER BY occurrences DESC"
   336    336   } {
   337    337   
   338         -  set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}]
          338  +  set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)}]
   339    339     if {$sort} { lappend res 0 0 0 {USE TEMP B-TREE FOR ORDER BY} }
   340    340   
   341    341     set sql "SELECT * FROM terms $orderby"
   342    342     do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res
   343    343   }
   344    344   
   345    345   #-------------------------------------------------------------------------
................................................................................
   407    407     uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r]
   408    408   }
   409    409   
   410    410   do_plansql_test 4.2 {
   411    411     SELECT y FROM x2, terms WHERE y = term AND col = '*'
   412    412   } {
   413    413     0 0 0 {SCAN TABLE x2 (~1000000 rows)} 
   414         -  0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} 
          414  +  0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} 
   415    415     a b c d e f g h i j k l
   416    416   }
   417    417   
   418    418   do_plansql_test 4.3 {
   419    419     SELECT y FROM terms, x2 WHERE y = term AND col = '*'
   420    420   } {
   421    421     0 0 1 {SCAN TABLE x2 (~1000000 rows)} 
   422         -  0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} 
          422  +  0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~25 rows)} 
   423    423     a b c d e f g h i j k l
   424    424   }
   425    425   
   426    426   do_plansql_test 4.4 {
   427    427     SELECT y FROM x3, terms WHERE y = term AND col = '*'
   428    428   } {
   429         -  0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 
          429  +  0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} 
   430    430     0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
   431    431     a b c d e f g h i j k l
   432    432   }
   433    433   
   434    434   do_plansql_test 4.5 {
   435    435     SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*'
   436    436   } {
   437         -  0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 
          437  +  0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~25 rows)} 
   438    438     0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
   439    439     a k l
   440    440   }
   441    441   
   442    442   #-------------------------------------------------------------------------
   443    443   # The following tests check that fts4aux can handle an fts table with an
   444    444   # odd name (one that requires quoting for use in SQL statements). And that
................................................................................
   515    515   
   516    516   do_test 8.2 {
   517    517     execsql {DETACH att}
   518    518     catchsql { SELECT * FROM aux2 }
   519    519   } {1 {SQL logic error or missing database}}
   520    520   
   521    521   finish_test
   522         -

Changes to test/fts3query.test.

   115    115       CREATE TABLE bt(title);
   116    116     }
   117    117   } {}
   118    118   do_eqp_test fts3query-4.2 {
   119    119     SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date
   120    120   } {
   121    121     0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
   122         -  0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~0 rows)}
          122  +  0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~25 rows)}
   123    123   }
   124    124   do_eqp_test fts3query-4.3 {
   125    125     SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date
   126    126   } {
   127    127     0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
   128         -  0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~0 rows)}
          128  +  0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1: (~25 rows)}
   129    129   }
   130    130   do_eqp_test fts3query-4.4 {
   131    131     SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date
   132    132   } {
   133    133     0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 
   134    134     0 1 1 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
   135    135   }
................................................................................
   206    206   
   207    207     7 "SELECT snippet(t3, 'XXX', 'YYY', 'ZZZ', 1, 5) FROM t3 WHERE t3 MATCH 'gestures'" 
   208    208     {{ZZZthe hand XXXgesturesYYY (called beatsZZZ}}
   209    209   }
   210    210   
   211    211   
   212    212   finish_test
   213         -