/ Check-in [5f0e803e]
Login

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

Overview
Comment:Fix test cases so that they work with the new EXPLAIN QUERY PLAN output format. Only some of the cases have been fixed. This is an incremental check-in.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | rework-EQP
Files: files | file ages | folders
SHA3-256: 5f0e803e33aa557865d5fc830d9202d628de9a94c9757058ca48f1a560702cd3
User & Date: drh 2018-05-02 18:00:17
Context
2018-05-02
19:42
More test case updates. Tests are all running now. check-in: dab5e529 user: drh tags: rework-EQP
18:00
Fix test cases so that they work with the new EXPLAIN QUERY PLAN output format. Only some of the cases have been fixed. This is an incremental check-in. check-in: 5f0e803e user: drh tags: rework-EQP
16:13
Improved EQP output for recursive CTEs and multi-value VALUES clauses. check-in: f2f52554 user: drh tags: rework-EQP
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts5/test/fts5plan.test.

    25     25     CREATE TABLE t1(x, y);
    26     26     CREATE VIRTUAL TABLE f1 USING fts5(ff);
    27     27   }
    28     28   
    29     29   do_eqp_test 1.1 {
    30     30     SELECT * FROM t1, f1 WHERE f1 MATCH t1.x
    31     31   } {
    32         -  0 0 0 {SCAN TABLE t1} 
    33         -  0 1 1 {SCAN TABLE f1 VIRTUAL TABLE INDEX 65537:}
           32  +  QUERY PLAN
           33  +  |--SCAN TABLE t1
           34  +  `--SCAN TABLE f1 VIRTUAL TABLE INDEX 65537:
    34     35   }
    35     36   
    36     37   do_eqp_test 1.2 {
    37     38     SELECT * FROM t1, f1 WHERE f1 > t1.x
    38     39   } {
    39         -  0 0 1 {SCAN TABLE f1 VIRTUAL TABLE INDEX 0:}
    40         -  0 1 0 {SCAN TABLE t1} 
           40  +  QUERY PLAN
           41  +  |--SCAN TABLE f1 VIRTUAL TABLE INDEX 0:
           42  +  `--SCAN TABLE t1
    41     43   }
    42     44   
    43     45   do_eqp_test 1.3 {
    44     46     SELECT * FROM f1 WHERE f1 MATCH ? ORDER BY ff
    45     47   } {
    46         -  0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 65537:}
    47         -  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
           48  +  QUERY PLAN
           49  +  |--SCAN TABLE f1 VIRTUAL TABLE INDEX 65537:
           50  +  `--USE TEMP B-TREE FOR ORDER BY
    48     51   }
    49     52   
    50     53   do_eqp_test 1.4 {
    51     54     SELECT * FROM f1 ORDER BY rank
    52     55   } {
    53         -  0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 0:}
    54         -  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
           56  +  QUERY PLAN
           57  +  |--SCAN TABLE f1 VIRTUAL TABLE INDEX 0:
           58  +  `--USE TEMP B-TREE FOR ORDER BY
    55     59   }
    56     60   
    57     61   do_eqp_test 1.5 {
    58     62     SELECT * FROM f1 WHERE rank MATCH ?
    59         -} {
    60         -  0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 2:}
    61         -}
    62         -
    63         -
    64         -
           63  +} {SCAN TABLE f1 VIRTUAL TABLE INDEX 2:
    65     64   
    66     65   finish_test

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/analyzeA.test.

   132    132     do_execsql_test 1.$tn.2.1 { SELECT count(*) FROM t1 WHERE b=31 } 1
   133    133     do_execsql_test 1.$tn.2.2 { SELECT count(*) FROM t1 WHERE c=0  } 49
   134    134     do_execsql_test 1.$tn.2.3 { SELECT count(*) FROM t1 WHERE b=125  } 49
   135    135     do_execsql_test 1.$tn.2.4 { SELECT count(*) FROM t1 WHERE c=16  } 1
   136    136   
   137    137     do_eqp_test 1.$tn.2.5 {
   138    138       SELECT * FROM t1 WHERE b = 31 AND c = 0;
   139         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}}
          139  +  } {SEARCH TABLE t1 USING INDEX t1b (b=?)}
   140    140     do_eqp_test 1.$tn.2.6 {
   141    141       SELECT * FROM t1 WHERE b = 125 AND c = 16;
   142         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)}}
          142  +  } {SEARCH TABLE t1 USING INDEX t1c (c=?)}
   143    143   
   144    144     do_execsql_test 1.$tn.3.1 { 
   145    145       SELECT count(*) FROM t1 WHERE b BETWEEN 0 AND 50
   146    146     } {6}
   147    147     do_execsql_test 1.$tn.3.2 { 
   148    148       SELECT count(*) FROM t1 WHERE c BETWEEN 0 AND 50
   149    149     } {90}
................................................................................
   152    152     } {90}
   153    153     do_execsql_test 1.$tn.3.4 { 
   154    154       SELECT count(*) FROM t1 WHERE c BETWEEN 75 AND 125
   155    155     } {6}
   156    156   
   157    157     do_eqp_test 1.$tn.3.5 {
   158    158       SELECT * FROM t1 WHERE b BETWEEN 0 AND 50 AND c BETWEEN 0 AND 50
   159         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}
          159  +  } {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}
   160    160   
   161    161     do_eqp_test 1.$tn.3.6 {
   162    162       SELECT * FROM t1 WHERE b BETWEEN 75 AND 125 AND c BETWEEN 75 AND 125
   163         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}
          163  +  } {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}
   164    164   
   165    165     do_eqp_test 1.$tn.3.7 {
   166    166       SELECT * FROM t1 WHERE b BETWEEN +0 AND +50 AND c BETWEEN +0 AND +50
   167         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}
          167  +  } {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}
   168    168   
   169    169     do_eqp_test 1.$tn.3.8 {
   170    170       SELECT * FROM t1
   171    171        WHERE b BETWEEN cast('0' AS int) AND cast('50.0' AS real)
   172    172          AND c BETWEEN cast('0' AS numeric) AND cast('50.0' AS real)
   173         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}}
          173  +  } {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}
   174    174   
   175    175     do_eqp_test 1.$tn.3.9 {
   176    176       SELECT * FROM t1 WHERE b BETWEEN +75 AND +125 AND c BETWEEN +75 AND +125
   177         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}
          177  +  } {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}
   178    178   
   179    179     do_eqp_test 1.$tn.3.10 {
   180    180       SELECT * FROM t1
   181    181        WHERE b BETWEEN cast('75' AS int) AND cast('125.0' AS real)
   182    182          AND c BETWEEN cast('75' AS numeric) AND cast('125.0' AS real)
   183         -  } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}}
          183  +  } {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}
   184    184   }
   185    185   
   186    186   finish_test

Changes to test/analyzeD.test.

    59     59   } {}
    60     60   
    61     61   # With full ANALYZE data, SQLite sees that c=150 (5 rows) is better than
    62     62   # a=3001 (7 rows).
    63     63   #
    64     64   do_eqp_test 1.2 {
    65     65     SELECT * FROM t1 WHERE a=3001 AND c=150;
    66         -} {
    67         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_c (c=?)}
    68         -}
           66  +} {SEARCH TABLE t1 USING INDEX t1_c (c=?)}
    69     67   
    70     68   do_test 1.3 {
    71     69     execsql { DELETE FROM sqlite_stat1 }
    72     70     db close
    73     71     sqlite3 db test.db
    74     72   } {}
    75     73   
................................................................................
    76     74   # Without stat1, because 3001 is larger than all samples in the stat4
    77     75   # table, SQLite thinks that a=3001 matches just 1 row. So it (incorrectly)
    78     76   # chooses it over the c=150 index (5 rows). Even with stat1 data, things
    79     77   # worked this way before commit [e6f7f97dbc].
    80     78   #
    81     79   do_eqp_test 1.4 {
    82     80     SELECT * FROM t1 WHERE a=3001 AND c=150;
    83         -} {
    84         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_ab (a=?)}
    85         -}
           81  +} {SEARCH TABLE t1 USING INDEX t1_ab (a=?)}
    86     82   
    87     83   do_test 1.5 {
    88     84     execsql { 
    89     85       UPDATE t1 SET a=13 WHERE a = 3001;
    90     86       ANALYZE;
    91     87     }
    92     88   } {}
    93     89   
    94     90   do_eqp_test 1.6 {
    95     91     SELECT * FROM t1 WHERE a=13 AND c=150;
    96         -} {
    97         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_c (c=?)}
    98         -}
           92  +} {SEARCH TABLE t1 USING INDEX t1_c (c=?)}
    99     93   
   100     94   do_test 1.7 {
   101     95     execsql { DELETE FROM sqlite_stat1 }
   102     96     db close
   103     97     sqlite3 db test.db
   104     98   } {}
   105     99   
   106    100   # Same test as 1.4, except this time the 7 rows that match the a=? condition 
   107    101   # do not feature larger values than all rows in the stat4 table. So SQLite
   108    102   # gets this right, even without stat1 data.
   109    103   do_eqp_test 1.8 {
   110    104     SELECT * FROM t1 WHERE a=13 AND c=150;
   111         -} {
   112         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1_c (c=?)}
   113         -}
          105  +} {SEARCH TABLE t1 USING INDEX t1_c (c=?)}
   114    106   
   115    107   finish_test

Changes to test/analyzeF.test.

    58     58   
    59     59     9  "x = str('19') AND y = str('4')" {t1y (y=?)}
    60     60     10 "x = str('4') AND y = str('19')" {t1y (y=?)}
    61     61   
    62     62     11 "x = nullif('19', 0) AND y = nullif('4', 0)" {t1y (y=?)}
    63     63     12 "x = nullif('4', 0) AND y = nullif('19', 0)" {t1y (y=?)}
    64     64   } {
    65         -  set res "0 0 0 {SEARCH TABLE t1 USING INDEX $idx}"
           65  +  set res "SEARCH TABLE t1 USING INDEX $idx"
    66     66     do_eqp_test 1.$tn "SELECT * FROM t1 WHERE $where" $res
    67     67   }
    68     68   
    69     69   # Test that functions that do not exist - "func()" - do not cause an error.
    70     70   #
    71     71   do_catchsql_test 2.1 {
    72     72     SELECT * FROM t1 WHERE x = substr('145', 2, 1) AND y = func(1, 2, 3)
................................................................................
    88     88   foreach {tn where idx} {
    89     89     1 "x = det4() AND y = det19()"     {t1x (x=?)}
    90     90     2 "x = det19() AND y = det4()"     {t1y (y=?)}
    91     91   
    92     92     3 "x = nondet4() AND y = nondet19()"     {t1y (y=?)}
    93     93     4 "x = nondet19() AND y = nondet4()"     {t1y (y=?)}
    94     94   } {
    95         -  set res "0 0 0 {SEARCH TABLE t1 USING INDEX $idx}"
           95  +  set res "SEARCH TABLE t1 USING INDEX $idx"
    96     96     do_eqp_test 3.$tn "SELECT * FROM t1 WHERE $where" $res
    97     97   }
    98     98   
    99     99   
   100    100   execsql { DELETE FROM t1 }
   101    101   
   102    102   proc throw_error {err} { error $err }

Changes to test/autoindex3.test.

    80     80   # on the basis that the real index "uab" must be better than the automatic
    81     81   # index. This is not right - a skip-scan is not necessarily better than an
    82     82   # automatic index scan.
    83     83   #
    84     84   do_eqp_test 220 {
    85     85     select count(*) from u, v where u.b = v.b and v.e > 34;
    86     86   } {
    87         -  0 0 1 {SEARCH TABLE v USING INDEX ve (e>?)} 
    88         -  0 1 0 {SEARCH TABLE u USING AUTOMATIC COVERING INDEX (b=?)}
           87  +  QUERY PLAN
           88  +  |--SEARCH TABLE v USING INDEX ve (e>?)
           89  +  `--SEARCH TABLE u USING AUTOMATIC COVERING INDEX (b=?)
    89     90   }
    90     91   
    91     92   
    92     93   finish_test

Changes to test/bestindex1.test.

    47     47   
    48     48   do_execsql_test 1.0 {
    49     49     CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);
    50     50   } {}
    51     51   
    52     52   do_eqp_test 1.1 {
    53     53     SELECT * FROM x1 WHERE a = 'abc'
    54         -} {
    55         -  0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!}
    56         -}
           54  +} {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!}
    57     55   
    58     56   do_eqp_test 1.2 {
    59     57     SELECT * FROM x1 WHERE a IN ('abc', 'def');
    60         -} {
    61         -  0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!}
    62         -}
           58  +} {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!}
    63     59   
    64     60   #-------------------------------------------------------------------------
    65     61   #
    66     62   reset_db
    67     63   register_tcl_module db
    68     64   
    69     65   # Parameter $mode may be one of:
................................................................................
   140    136     do_execsql_test 2.2.$mode.4 {SELECT rowid FROM t1 WHERE a='two'} {2} 
   141    137   
   142    138     do_execsql_test 2.2.$mode.5 {
   143    139       SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
   144    140     } {1 4} 
   145    141   
   146    142     set plan(use) {
   147         -    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'}
   148         -    0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          143  +    QUERY PLAN
          144  +    |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'
          145  +    `--USE TEMP B-TREE FOR ORDER BY
   149    146     }
   150    147     set plan(omit) {
   151         -    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'}
   152         -    0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          148  +    QUERY PLAN
          149  +    |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'
          150  +    `--USE TEMP B-TREE FOR ORDER BY
   153    151     }
   154    152     set plan(use2) {
   155         -    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x}
   156         -    0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          153  +    QUERY PLAN
          154  +    |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x
          155  +    `--USE TEMP B-TREE FOR ORDER BY
   157    156     }
   158    157   
   159    158     do_eqp_test 2.2.$mode.6 { 
   160    159       SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
   161         -  } $plan($mode)
          160  +  } [string map {"\n  " "\n"} $plan($mode)]
   162    161   }
   163    162   
   164    163   # 2016-04-09.
   165    164   # Demonstrate a register overwrite problem when using two virtual
   166    165   # tables where the outer loop uses the IN operator.
   167    166   #
   168    167   set G(collist) [list PrimaryKey flagA columnA]

Changes to test/bestindex2.test.

    85     85     CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd t1 {a b}");
    86     86     CREATE VIRTUAL TABLE t2 USING tcl("vtab_cmd t2 {c d}");
    87     87     CREATE VIRTUAL TABLE t3 USING tcl("vtab_cmd t3 {e f}");
    88     88   }
    89     89   
    90     90   do_eqp_test 1.1 {
    91     91     SELECT * FROM t1 WHERE a='abc'
    92         -} {
    93         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
    94         -}
           92  +} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
           93  +
    95     94   do_eqp_test 1.2 {
    96     95     SELECT * FROM t1 WHERE a='abc' AND b='def'
    97         -} {
    98         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=? AND b=?)}
    99         -}
           96  +} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=? AND b=?)}
           97  +
   100     98   do_eqp_test 1.3 {
   101     99     SELECT * FROM t1 WHERE a='abc' AND a='def'
   102         -} {
   103         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
   104         -}
          100  +} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
          101  +
   105    102   do_eqp_test 1.4 {
   106    103     SELECT * FROM t1,t2 WHERE c=a
   107    104   } {
   108         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
   109         -  0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)}
          105  +  QUERY PLAN
          106  +  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:
          107  +  `--SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
   110    108   }
   111    109   
   112    110   do_eqp_test 1.5 {
   113    111     SELECT * FROM t1, t2 CROSS JOIN t3 WHERE t2.c = +t1.b AND t3.e=t2.d
   114    112   } {
   115         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
   116         -  0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
   117         -  0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
          113  +  QUERY PLAN
          114  +  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:
          115  +  |--SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
          116  +  `--SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)
   118    117   }
   119    118   
   120    119   do_eqp_test 1.6 {
   121    120     SELECT * FROM t1, t2, t3 WHERE t2.c = +t1.b AND t3.e = t2.d
   122    121   } {
   123         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} 
   124         -  0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
   125         -  0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
          122  +  QUERY PLAN
          123  +  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:
          124  +  |--SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
          125  +  `--SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)
   126    126   }
   127    127   
   128    128   do_execsql_test 1.7.1 {
   129    129     CREATE TABLE x1(a, b);
   130    130   }
   131    131   do_eqp_test 1.7.2 {
   132    132     SELECT * FROM x1 CROSS JOIN t1, t2, t3 
   133    133       WHERE t1.a = t2.c AND t1.b = t3.e
   134    134   } {
   135         -  0 0 0 {SCAN TABLE x1} 
   136         -  0 1 1 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:}
   137         -  0 2 2 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} 
   138         -  0 3 3 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)}
          135  +  QUERY PLAN
          136  +  |--SCAN TABLE x1
          137  +  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:
          138  +  |--SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
          139  +  `--SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)
   139    140   }
   140    141   
   141    142   finish_test

Changes to test/bestindex3.test.

    75     75   
    76     76   do_execsql_test 1.0 {
    77     77     CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd 0");
    78     78   }
    79     79   
    80     80   do_eqp_test 1.1 {
    81     81     SELECT * FROM t1 WHERE a LIKE 'abc';
    82         -} {
    83         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?}
    84         -}
           82  +} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?}
    85     83   
    86     84   do_eqp_test 1.2 {
    87     85     SELECT * FROM t1 WHERE a = 'abc';
    88         -} {
    89         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?}
    90         -}
           86  +} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?}
    91     87   
    92     88   do_eqp_test 1.3 {
    93     89     SELECT * FROM t1 WHERE a = 'abc' OR b = 'def';
    94     90   } {
    95         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?}
    96         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?}
           91  +  QUERY PLAN
           92  +  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?
           93  +  `--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?
    97     94   }
    98     95   
    99     96   do_eqp_test 1.4 {
   100     97     SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def';
   101     98   } {
   102         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?}
   103         -  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?}
           99  +  QUERY PLAN
          100  +  |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?
          101  +  `--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?
   104    102   }
   105    103   
   106    104   do_execsql_test 1.5 {
   107    105     CREATE TABLE ttt(a, b, c);
   108    106   
   109    107     INSERT INTO ttt VALUES(1, 'two',   'three');
   110    108     INSERT INTO ttt VALUES(2, 'one',   'two');
................................................................................
   144    142       CREATE INDEX t2x ON t2(x COLLATE nocase);
   145    143       CREATE INDEX t2y ON t2(y);
   146    144     }
   147    145   
   148    146     do_eqp_test 2.2 {
   149    147       SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'
   150    148     } {
   151         -    0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?)}
   152         -    0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?)}
          149  +  QUERY PLAN
          150  +  |--SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?)
          151  +  `--SEARCH TABLE t2 USING INDEX t2y (y=?)
   153    152     }
   154    153   }
   155    154   
   156    155   #-------------------------------------------------------------------------
   157    156   # Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE 
   158    157   # statement is currently ignored.
   159    158   #

Changes to test/bigmmap.test.

    88     88         ORDER BY b, c;
    89     89       " {}
    90     90       
    91     91       do_eqp_test 2.$i.$t.3 "
    92     92         SELECT * FROM t$t AS o WHERE 
    93     93           NOT EXISTS( SELECT * FROM t$t AS i WHERE a=o.a AND +b=o.b AND +c=o.c )
    94     94         ORDER BY b, c;
    95         -    " "
    96         -      0 0 0 {SCAN TABLE t$t AS o USING COVERING INDEX sqlite_autoindex_t${t}_1}
    97         -      0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1}
    98         -      1 0 0 {SEARCH TABLE t$t AS i USING INTEGER PRIMARY KEY (rowid=?)}
    99         -    "
           95  +    " [string map {"\n    " "\n"} "
           96  +      QUERY PLAN
           97  +      |--SCAN TABLE t$t AS o USING COVERING INDEX sqlite_autoindex_t${t}_1
           98  +      `--CORRELATED SCALAR SUBQUERY
           99  +         `--SEARCH TABLE t$t AS i USING INTEGER PRIMARY KEY (rowid=?)
          100  +    "]
   100    101     }
   101    102   }
   102    103   
   103    104   finish_test
   104         -

Changes to test/cost.test.

    20     20     CREATE TABLE t4(c, d, e);
    21     21     CREATE UNIQUE INDEX i3 ON t3(b);
    22     22     CREATE UNIQUE INDEX i4 ON t4(c, d);
    23     23   }
    24     24   do_eqp_test 1.2 {
    25     25     SELECT e FROM t3, t4 WHERE b=c ORDER BY b, d;
    26     26   } {
    27         -  0 0 0 {SCAN TABLE t3 USING COVERING INDEX i3} 
    28         -  0 1 1 {SEARCH TABLE t4 USING INDEX i4 (c=?)}
           27  +  QUERY PLAN
           28  +  |--SCAN TABLE t3 USING COVERING INDEX i3
           29  +  `--SEARCH TABLE t4 USING INDEX i4 (c=?)
    29     30   }
    30     31   
    31     32   
    32     33   do_execsql_test 2.1 {
    33     34     CREATE TABLE t1(a, b);
    34     35     CREATE INDEX i1 ON t1(a);
    35     36   }
    36     37   
    37     38   # It is better to use an index for ORDER BY than sort externally, even 
    38     39   # if the index is a non-covering index.
    39     40   do_eqp_test 2.2 {
    40     41     SELECT * FROM t1 ORDER BY a;
    41         -} {
    42         -  0 0 0 {SCAN TABLE t1 USING INDEX i1}
    43         -}
           42  +} {SCAN TABLE t1 USING INDEX i1}
    44     43   
    45     44   do_execsql_test 3.1 {
    46     45     CREATE TABLE t5(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
    47     46     CREATE INDEX t5b ON t5(b);
    48     47     CREATE INDEX t5c ON t5(c);
    49     48     CREATE INDEX t5d ON t5(d);
    50     49     CREATE INDEX t5e ON t5(e);
................................................................................
    53     52   }
    54     53   
    55     54   do_eqp_test 3.2 {
    56     55     SELECT a FROM t5 
    57     56     WHERE b IS NULL OR c IS NULL OR d IS NULL 
    58     57     ORDER BY a;
    59     58   } {
    60         -  0 0 0 {SEARCH TABLE t5 USING INDEX t5b (b=?)} 
    61         -  0 0 0 {SEARCH TABLE t5 USING INDEX t5c (c=?)} 
    62         -  0 0 0 {SEARCH TABLE t5 USING INDEX t5d (d=?)} 
    63         -  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
           59  +  QUERY PLAN
           60  +  |--SEARCH TABLE t5 USING INDEX t5b (b=?)
           61  +  |--SEARCH TABLE t5 USING INDEX t5c (c=?)
           62  +  |--SEARCH TABLE t5 USING INDEX t5d (d=?)
           63  +  `--USE TEMP B-TREE FOR ORDER BY
    64     64   }
    65     65   
    66     66   #-------------------------------------------------------------------------
    67     67   # If there is no likelihood() or stat3 data, SQLite assumes that a closed
    68     68   # range scan (e.g. one constrained by "col BETWEEN ? AND ?" constraint)
    69     69   # visits 1/64 of the rows in a table.
    70     70   #
................................................................................
    75     75   do_execsql_test 4.1 {
    76     76     CREATE TABLE t1(a, b);
    77     77     CREATE INDEX i1 ON t1(a);
    78     78     CREATE INDEX i2 ON t1(b);
    79     79   }
    80     80   do_eqp_test 4.2 {
    81     81     SELECT * FROM t1 WHERE likelihood(a=?, 0.014) AND b BETWEEN ? AND ?;
    82         -} {
    83         -  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}
    84         -}
           82  +} {SEARCH TABLE t1 USING INDEX i1 (a=?)}
           83  +
    85     84   do_eqp_test 4.3 {
    86     85     SELECT * FROM t1 WHERE likelihood(a=?, 0.016) AND b BETWEEN ? AND ?;
    87         -} {
    88         -  0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b>? AND b<?)}
    89         -}
           86  +} {SEARCH TABLE t1 USING INDEX i2 (b>? AND b<?)}
    90     87   
    91     88   
    92     89   #-------------------------------------------------------------------------
    93     90   #
    94     91   reset_db
    95     92   do_execsql_test 5.1 {
    96     93     CREATE TABLE t2(x, y);
    97     94     CREATE INDEX t2i1 ON t2(x);
    98     95   }
    99     96   
   100     97   do_eqp_test 5.2 {
   101     98     SELECT * FROM t2 ORDER BY x, y;
   102     99   } {
   103         -  0 0 0 {SCAN TABLE t2 USING INDEX t2i1} 
   104         -  0 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY}
          100  +  QUERY PLAN
          101  +  |--SCAN TABLE t2 USING INDEX t2i1
          102  +  `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
   105    103   }
   106    104   
   107    105   do_eqp_test 5.3 {
   108    106     SELECT * FROM t2 WHERE x BETWEEN ? AND ? ORDER BY rowid;
   109    107   } {
   110         -  0 0 0 {SEARCH TABLE t2 USING INDEX t2i1 (x>? AND x<?)} 
   111         -  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          108  +  QUERY PLAN
          109  +  |--SEARCH TABLE t2 USING INDEX t2i1 (x>? AND x<?)
          110  +  `--USE TEMP B-TREE FOR ORDER BY
   112    111   }
   113    112   
   114    113   # where7.test, where8.test:
   115    114   #
   116    115   do_execsql_test 6.1 {
   117    116     CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c);
   118    117     CREATE INDEX t3i1 ON t3(b);
   119    118     CREATE INDEX t3i2 ON t3(c);
   120    119   }
   121    120   
   122    121   do_eqp_test 6.2 {
   123    122     SELECT a FROM t3 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a
   124    123   } {
   125         -  0 0 0 {SEARCH TABLE t3 USING INDEX t3i1 (b>? AND b<?)} 
   126         -  0 0 0 {SEARCH TABLE t3 USING INDEX t3i2 (c=?)}
   127         -  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          124  +  QUERY PLAN
          125  +  |--SEARCH TABLE t3 USING INDEX t3i1 (b>? AND b<?)
          126  +  |--SEARCH TABLE t3 USING INDEX t3i2 (c=?)
          127  +  `--USE TEMP B-TREE FOR ORDER BY
   128    128   }
   129    129   
   130    130   #-------------------------------------------------------------------------
   131    131   #
   132    132   reset_db
   133    133   do_execsql_test 7.1 {
   134    134     CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
................................................................................
   141    141   }
   142    142   
   143    143   do_eqp_test 7.2 {
   144    144     SELECT a FROM t1
   145    145        WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
   146    146     ORDER BY a
   147    147   } {
   148         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)} 
   149         -  0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)} 
   150         -  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          148  +  QUERY PLAN
          149  +  |--SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)
          150  +  |--SEARCH TABLE t1 USING INDEX t1b (b=?)
          151  +  `--USE TEMP B-TREE FOR ORDER BY
   151    152   }
   152    153   
   153    154   do_eqp_test 7.3 {
   154    155     SELECT rowid FROM t1
   155    156     WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
   156    157           OR (b NOT NULL AND c IS NULL AND d NOT NULL)
   157    158           OR (b NOT NULL AND c NOT NULL AND d IS NULL)
   158         -} {
   159         -  0 0 0 {SCAN TABLE t1}
   160         -}
          159  +} {SCAN TABLE t1}
   161    160   
   162    161   do_eqp_test 7.4 {
   163    162     SELECT rowid FROM t1 WHERE (+b IS NULL AND c NOT NULL) OR c IS NULL
   164         -} {
   165         -  0 0 0 {SCAN TABLE t1}
   166         -}
          163  +} {SCAN TABLE t1}
   167    164   
   168    165   #-------------------------------------------------------------------------
   169    166   #
   170    167   reset_db
   171    168   do_execsql_test 8.1 {
   172    169     CREATE TABLE composer(
   173    170       cid INTEGER PRIMARY KEY,
................................................................................
   190    187   do_eqp_test 8.2 {
   191    188     SELECT DISTINCT aname
   192    189       FROM album, composer, track
   193    190      WHERE cname LIKE '%bach%'
   194    191        AND unlikely(composer.cid=track.cid)
   195    192        AND unlikely(album.aid=track.aid);
   196    193   } {
   197         -  0 0 2 {SCAN TABLE track} 
   198         -  0 1 0 {SEARCH TABLE album USING INTEGER PRIMARY KEY (rowid=?)}
   199         -  0 2 1 {SEARCH TABLE composer USING INTEGER PRIMARY KEY (rowid=?)}
   200         -  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
          194  +  QUERY PLAN
          195  +  |--SCAN TABLE track
          196  +  |--SEARCH TABLE album USING INTEGER PRIMARY KEY (rowid=?)
          197  +  |--SEARCH TABLE composer USING INTEGER PRIMARY KEY (rowid=?)
          198  +  `--USE TEMP B-TREE FOR DISTINCT
   201    199   }
   202    200   
   203    201   #-------------------------------------------------------------------------
   204    202   #
   205    203   do_execsql_test 9.1 {
   206    204     CREATE TABLE t1(
   207    205       a,b,c,d,e, f,g,h,i,j,
................................................................................
   259    257         execsql { INSERT INTO t6 VALUES($i%4, 'xyz', $i%8) }
   260    258       }
   261    259       execsql ANALYZE
   262    260     } {}
   263    261   
   264    262     do_eqp_test 10.3 {
   265    263       SELECT rowid FROM t6 WHERE a=0 AND c=0
   266         -  } {
   267         -    0 0 0 {SEARCH TABLE t6 USING INDEX t6i2 (c=?)}
   268         -  }
          264  +  } {SEARCH TABLE t6 USING INDEX t6i2 (c=?)}
   269    265   
   270    266     do_eqp_test 10.4 {
   271    267       SELECT rowid FROM t6 WHERE a=0 AND b='xyz' AND c=0
   272         -  } {
   273         -    0 0 0 {SEARCH TABLE t6 USING INDEX t6i2 (c=?)}
   274         -  }
          268  +  } {SEARCH TABLE t6 USING INDEX t6i2 (c=?)}
   275    269   
   276    270     do_eqp_test 10.5 {
   277    271       SELECT rowid FROM t6 WHERE likelihood(a=0, 0.1) AND c=0
   278         -  } {
   279         -    0 0 0 {SEARCH TABLE t6 USING INDEX t6i1 (a=?)}
   280         -  }
          272  +  } {SEARCH TABLE t6 USING INDEX t6i1 (a=?)}
   281    273   
   282    274     do_eqp_test 10.6 {
   283    275       SELECT rowid FROM t6 WHERE likelihood(a=0, 0.1) AND b='xyz' AND c=0
   284         -  } {
   285         -    0 0 0 {SEARCH TABLE t6 USING INDEX t6i1 (a=? AND b=?)}
   286         -  }
          276  +  } {SEARCH TABLE t6 USING INDEX t6i1 (a=? AND b=?)}
   287    277   }
   288    278   
   289    279   finish_test

Changes to test/coveridxscan.test.

   105    105   
   106    106     CREATE TABLE t2(i INTEGER PRIMARY KEY, $cols);
   107    107     CREATE INDEX i2 ON t2($cols);
   108    108   "
   109    109   
   110    110   do_eqp_test 5.1.1 {
   111    111     SELECT * FROM t1 ORDER BY c1, c2;
   112         -} {
   113         -  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}
   114         -}
          112  +} {SCAN TABLE t1 USING COVERING INDEX i1}
   115    113   
   116    114   do_eqp_test 5.1.2 {
   117    115     SELECT * FROM t2 ORDER BY c1, c2;
   118         -} {
   119         -  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i2}
   120         -}
   121         -
          116  +} {SCAN TABLE t2 USING COVERING INDEX i2}
   122    117   
   123    118   
   124    119   finish_test

Changes to test/fts3join.test.

    92     92   
    93     93   do_eqp_test 4.2 {
    94     94     SELECT * FROM t4 LEFT JOIN (
    95     95         SELECT docid, * FROM ft4 WHERE ft4 MATCH ?
    96     96     ) AS rr ON t4.rowid=rr.docid 
    97     97     WHERE t4.y = ?;
    98     98   } {
    99         -  1 0 0 {SCAN TABLE ft4 VIRTUAL TABLE INDEX 3:} 
   100         -  0 0 0 {SCAN TABLE t4}
   101         -  0 1 1 {SEARCH SUBQUERY 1 AS rr USING AUTOMATIC COVERING INDEX (docid=?)}
           99  +  QUERY PLAN
          100  +  |--MATERIALIZE xxxxxx
          101  +  |  `--SCAN TABLE ft4 VIRTUAL TABLE INDEX 3:
          102  +  |--SCAN TABLE t4
          103  +  `--SEARCH SUBQUERY xxxxxx AS rr USING AUTOMATIC COVERING INDEX (docid=?)
   102    104   }
   103    105   
   104    106   finish_test

Changes to test/fts3query.test.

   114    114       CREATE VIRTUAL TABLE ft USING fts3(title);
   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         -  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 
   122         -  0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1:}
          121  +  QUERY PLAN
          122  +  |--SCAN TABLE t1 USING COVERING INDEX i1
          123  +  `--SCAN TABLE ft VIRTUAL TABLE INDEX 1:
   123    124   }
   124    125   do_eqp_test fts3query-4.3 {
   125    126     SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date
   126    127   } {
   127         -  0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1} 
   128         -  0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1:}
          128  +  QUERY PLAN
          129  +  |--SCAN TABLE t1 USING COVERING INDEX i1
          130  +  `--SCAN TABLE ft VIRTUAL TABLE INDEX 1:
   129    131   }
   130    132   do_eqp_test fts3query-4.4 {
   131    133     SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date
   132    134   } {
   133         -  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 
   134         -  0 1 1 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?)}
          135  +  QUERY PLAN
          136  +  |--SCAN TABLE t1 USING COVERING INDEX i1
          137  +  `--SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?)
   135    138   }
   136    139   do_eqp_test fts3query-4.5 {
   137    140     SELECT t1.number FROM bt, t1 WHERE t1.number=bt.rowid ORDER BY t1.date
   138    141   } {
   139         -  0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1} 
   140         -  0 1 0 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?)}
          142  +  QUERY PLAN
          143  +  |--SCAN TABLE t1 USING COVERING INDEX i1
          144  +  `--SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?)
   141    145   }
   142    146   
   143    147   
   144    148   # Test that calling matchinfo() with the wrong number of arguments, or with
   145    149   # an invalid argument returns an error.
   146    150   #
   147    151   do_execsql_test 5.1 {

Changes to test/index6.test.

   314    314     INSERT INTO t8b VALUES('value', 3);
   315    315     INSERT INTO t8b VALUES('dummy', 4);
   316    316   } {}
   317    317   
   318    318   do_eqp_test index6-8.1 {
   319    319     SELECT * FROM t8a LEFT JOIN t8b ON (x = 'value' AND y = a)
   320    320   } {
   321         -  0 0 0 {SCAN TABLE t8a} 
   322         -  0 1 1 {SEARCH TABLE t8b USING INDEX i8c (y=?)}
          321  +  QUERY PLAN
          322  +  |--SCAN TABLE t8a
          323  +  `--SEARCH TABLE t8b USING INDEX i8c (y=?)
   323    324   }
   324    325   
   325    326   do_execsql_test index6-8.2 {
   326    327     SELECT * FROM t8a LEFT JOIN t8b ON (x = 'value' AND y = a)
   327    328   } {
   328    329     1 one value 1 
   329    330     2 two {} {} 

Changes to test/index7.test.

   317    317     INSERT INTO t4 VALUES('def', 'xyz');
   318    318     SELECT * FROM v4 WHERE d='xyz' AND c='def'
   319    319   } {
   320    320     def xyz
   321    321   }
   322    322   do_eqp_test index7-6.4 {
   323    323     SELECT * FROM v4 WHERE d='xyz' AND c='def'
   324         -} {
   325         -  0 0 0 {SEARCH TABLE t4 USING INDEX i4 (c=?)}
   326         -}
          324  +} {SEARCH TABLE t4 USING INDEX i4 (c=?)}
          325  +
   327    326   do_catchsql_test index7-6.5 {
   328    327     CREATE INDEX t5a ON t5(a) WHERE a=#1;
   329    328   } {1 {near "#1": syntax error}}
   330    329   
   331    330   
   332    331   finish_test

Changes to test/indexedby.test.

    36     36   #
    37     37   proc EQP {sql} {
    38     38     uplevel "execsql {EXPLAIN QUERY PLAN $sql}"
    39     39   }
    40     40   
    41     41   # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected.
    42     42   #
    43         -do_execsql_test indexedby-1.2 {
    44         -  EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; 
    45         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
    46         -do_execsql_test indexedby-1.3 {
    47         -  EXPLAIN QUERY PLAN select * from t1 ; 
    48         -} {0 0 0 {SCAN TABLE t1}}
    49         -do_execsql_test indexedby-1.4 {
    50         -  EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; 
           43  +do_eqp_test indexedby-1.2 {
           44  +  select * from t1 WHERE a = 10; 
           45  +} {SEARCH TABLE t1 USING INDEX i1 (a=?)}
           46  +do_eqp_test indexedby-1.3 {
           47  +  select * from t1 ; 
           48  +} {SCAN TABLE t1}
           49  +do_eqp_test indexedby-1.4 {
           50  +  select * from t1, t2 WHERE c = 10; 
    51     51   } {
    52         -  0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)} 
    53         -  0 1 0 {SCAN TABLE t1}
           52  +  QUERY PLAN
           53  +  |--SEARCH TABLE t2 USING INDEX i3 (c=?)
           54  +  `--SCAN TABLE t1
    54     55   }
    55     56   
    56     57   # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 
    57     58   # attached to a table in the FROM clause, but not to a sub-select or
    58     59   # SQL view. Also test that specifying an index that does not exist or
    59     60   # is attached to a different table is detected as an error.
    60     61   #
................................................................................
   111    112   #
   112    113   # EVIDENCE-OF: R-37002-28871 The "NOT INDEXED" clause specifies that no
   113    114   # index shall be used when accessing the preceding table, including
   114    115   # implied indices create by UNIQUE and PRIMARY KEY constraints. However,
   115    116   # the rowid can still be used to look up entries even when "NOT INDEXED"
   116    117   # is specified.
   117    118   #
   118         -do_execsql_test indexedby-3.1 {
   119         -  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a = 'one' AND b = 'two'
          119  +do_eqp_test indexedby-3.1 {
          120  +  SELECT * FROM t1 WHERE a = 'one' AND b = 'two'
   120    121   } {/SEARCH TABLE t1 USING INDEX/}
   121         -do_execsql_test indexedby-3.1.1 {
   122         -  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
   123         -} {0 0 0 {SCAN TABLE t1}}
   124         -do_execsql_test indexedby-3.1.2 {
   125         -  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE rowid=1
          122  +do_eqp_test indexedby-3.1.1 {
          123  +  SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
          124  +} {SCAN TABLE t1}
          125  +do_eqp_test indexedby-3.1.2 {
          126  +  SELECT * FROM t1 NOT INDEXED WHERE rowid=1
   126    127   } {/SEARCH TABLE t1 USING INTEGER PRIMARY KEY .rowid=/}
   127    128   
   128    129   
   129         -do_execsql_test indexedby-3.2 {
   130         -  EXPLAIN QUERY PLAN 
          130  +do_eqp_test indexedby-3.2 {
   131    131     SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'
   132         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
   133         -do_execsql_test indexedby-3.3 {
   134         -  EXPLAIN QUERY PLAN 
          132  +} {SEARCH TABLE t1 USING INDEX i1 (a=?)}
          133  +do_eqp_test indexedby-3.3 {
   135    134     SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'
   136         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
          135  +} {SEARCH TABLE t1 USING INDEX i2 (b=?)}
   137    136   do_test indexedby-3.4 {
   138    137     catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
   139    138   } {1 {no query solution}}
   140    139   do_test indexedby-3.5 {
   141    140     catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }
   142    141   } {1 {no query solution}}
   143    142   do_test indexedby-3.6 {
   144    143     catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
   145    144   } {0 {}}
   146    145   do_test indexedby-3.7 {
   147    146     catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
   148    147   } {0 {}}
   149    148   
   150         -do_execsql_test indexedby-3.8 {
   151         -  EXPLAIN QUERY PLAN 
          149  +do_eqp_test indexedby-3.8 {
   152    150     SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e 
   153         -} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1}}
   154         -do_execsql_test indexedby-3.9 {
   155         -  EXPLAIN QUERY PLAN 
          151  +} {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1}
          152  +do_eqp_test indexedby-3.9 {
   156    153     SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 
   157         -} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?)}}
          154  +} {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?)}
   158    155   do_test indexedby-3.10 {
   159    156     catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
   160    157   } {1 {no query solution}}
   161    158   do_test indexedby-3.11 {
   162    159     catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
   163    160   } {1 {no such index: sqlite_autoindex_t3_2}}
   164    161   
   165    162   # Tests for multiple table cases.
   166    163   #
   167         -do_execsql_test indexedby-4.1 {
   168         -  EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c 
          164  +do_eqp_test indexedby-4.1 {
          165  +  SELECT * FROM t1, t2 WHERE a = c 
   169    166   } {
   170         -  0 0 0 {SCAN TABLE t1} 
   171         -  0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)}
          167  +  QUERY PLAN
          168  +  |--SCAN TABLE t1
          169  +  `--SEARCH TABLE t2 USING INDEX i3 (c=?)
   172    170   }
   173         -do_execsql_test indexedby-4.2 {
   174         -  EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 
          171  +do_eqp_test indexedby-4.2 {
          172  +  SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 
   175    173   } {
   176         -  0 0 1 {SCAN TABLE t2} 
   177         -  0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}
          174  +  QUERY PLAN
          175  +  |--SCAN TABLE t2
          176  +  `--SEARCH TABLE t1 USING INDEX i1 (a=?)
   178    177   }
   179    178   do_test indexedby-4.3 {
   180    179     catchsql {
   181    180       SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c
   182    181     }
   183    182   } {1 {no query solution}}
   184    183   do_test indexedby-4.4 {
................................................................................
   190    189   # Test embedding an INDEXED BY in a CREATE VIEW statement. This block
   191    190   # also tests that nothing bad happens if an index refered to by
   192    191   # a CREATE VIEW statement is dropped and recreated.
   193    192   #
   194    193   do_execsql_test indexedby-5.1 {
   195    194     CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;
   196    195     EXPLAIN QUERY PLAN SELECT * FROM v2 
   197         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
          196  +} {/*SEARCH TABLE t1 USING INDEX i1 (a>?)*/}
   198    197   do_execsql_test indexedby-5.2 {
   199    198     EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 
   200         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
          199  +} {/*SEARCH TABLE t1 USING INDEX i1 (a>?)*/}
   201    200   do_test indexedby-5.3 {
   202    201     execsql { DROP INDEX i1 }
   203    202     catchsql { SELECT * FROM v2 }
   204    203   } {1 {no such index: i1}}
   205    204   do_test indexedby-5.4 {
   206    205     # Recreate index i1 in such a way as it cannot be used by the view query.
   207    206     execsql { CREATE INDEX i1 ON t1(b) }
................................................................................
   212    211     # be used by the query.
   213    212     execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) }
   214    213     catchsql { SELECT * FROM v2 }
   215    214   } {0 {}}
   216    215   
   217    216   # Test that "NOT INDEXED" may use the rowid index, but not others.
   218    217   # 
   219         -do_execsql_test indexedby-6.1 {
   220         -  EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 
   221         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
   222         -do_execsql_test indexedby-6.2 {
   223         -  EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 
   224         -} {0 0 0 {SCAN TABLE t1}}
          218  +do_eqp_test indexedby-6.1 {
          219  +  SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 
          220  +} {SEARCH TABLE t1 USING INDEX i2 (b=?)}
          221  +do_eqp_test indexedby-6.2 {
          222  +  SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 
          223  +} {SCAN TABLE t1}
   225    224   
   226    225   # EVIDENCE-OF: R-40297-14464 The INDEXED BY phrase forces the SQLite
   227    226   # query planner to use a particular named index on a DELETE, SELECT, or
   228    227   # UPDATE statement.
   229    228   #
   230    229   # Test that "INDEXED BY" can be used in a DELETE statement.
   231    230   # 
   232         -do_execsql_test indexedby-7.1 {
   233         -  EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 
   234         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
   235         -do_execsql_test indexedby-7.2 {
   236         -  EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 
   237         -} {0 0 0 {SCAN TABLE t1}}
   238         -do_execsql_test indexedby-7.3 {
   239         -  EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 
   240         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
   241         -do_execsql_test indexedby-7.4 {
   242         -  EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10
   243         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
   244         -do_execsql_test indexedby-7.5 {
   245         -  EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10
   246         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
          231  +do_eqp_test indexedby-7.1 {
          232  +  DELETE FROM t1 WHERE a = 5 
          233  +} {SEARCH TABLE t1 USING INDEX i1 (a=?)}
          234  +do_eqp_test indexedby-7.2 {
          235  +  DELETE FROM t1 NOT INDEXED WHERE a = 5 
          236  +} {SCAN TABLE t1}
          237  +do_eqp_test indexedby-7.3 {
          238  +  DELETE FROM t1 INDEXED BY i1 WHERE a = 5 
          239  +} {SEARCH TABLE t1 USING INDEX i1 (a=?)}
          240  +do_eqp_test indexedby-7.4 {
          241  +  DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10
          242  +} {SEARCH TABLE t1 USING INDEX i1 (a=?)}
          243  +do_eqp_test indexedby-7.5 {
          244  +  DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10
          245  +} {SEARCH TABLE t1 USING INDEX i2 (b=?)}
   247    246   do_test indexedby-7.6 {
   248    247     catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5}
   249    248   } {1 {no query solution}}
   250    249   
   251    250   # Test that "INDEXED BY" can be used in an UPDATE statement.
   252    251   # 
   253         -do_execsql_test indexedby-8.1 {
   254         -  EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 
   255         -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
   256         -do_execsql_test indexedby-8.2 {
   257         -  EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 
   258         -} {0 0 0 {SCAN TABLE t1}}
   259         -do_execsql_test indexedby-8.3 {
   260         -  EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 
   261         -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}}
   262         -do_execsql_test indexedby-8.4 {
   263         -  EXPLAIN QUERY PLAN 
          252  +do_eqp_test indexedby-8.1 {
          253  +  UPDATE t1 SET rowid=rowid+1 WHERE a = 5 
          254  +} {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}
          255  +do_eqp_test indexedby-8.2 {
          256  +  UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 
          257  +} {SCAN TABLE t1}
          258  +do_eqp_test indexedby-8.3 {
          259  +  UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 
          260  +} {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}
          261  +do_eqp_test indexedby-8.4 {
   264    262     UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10
   265         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
   266         -do_execsql_test indexedby-8.5 {
   267         -  EXPLAIN QUERY PLAN 
          263  +} {SEARCH TABLE t1 USING INDEX i1 (a=?)}
          264  +do_eqp_test indexedby-8.5 {
   268    265     UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10
   269         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}}
          266  +} {SEARCH TABLE t1 USING INDEX i2 (b=?)}
   270    267   do_test indexedby-8.6 {
   271    268     catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
   272    269   } {1 {no query solution}}
   273    270   
   274    271   # Test that bug #3560 is fixed.
   275    272   #
   276    273   do_test indexedby-9.1 {
................................................................................
   337    334     SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3';
   338    335   } {1 1 3}
   339    336   do_execsql_test 11.4 {
   340    337     SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0';
   341    338   } {1 1 3}
   342    339   do_eqp_test 11.5 {
   343    340     SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0';
   344         -} {0 0 0 {SEARCH TABLE x1 USING COVERING INDEX x1i (a=? AND b=? AND rowid=?)}}
          341  +} {SEARCH TABLE x1 USING COVERING INDEX x1i (a=? AND b=? AND rowid=?)}
   345    342   
   346    343   do_execsql_test 11.6 {
   347    344     CREATE TABLE x2(c INTEGER PRIMARY KEY, a, b TEXT);
   348    345     CREATE INDEX x2i ON x2(a, b);
   349    346     INSERT INTO x2 VALUES(1, 1, 1);
   350    347     INSERT INTO x2 VALUES(2, 1, 1);
   351    348     INSERT INTO x2 VALUES(3, 1, 1);
................................................................................
   358    355     SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3';
   359    356   } {1 1 3}
   360    357   do_execsql_test 11.9 {
   361    358     SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0';
   362    359   } {1 1 3}
   363    360   do_eqp_test 11.10 {
   364    361     SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0';
   365         -} {0 0 0 {SEARCH TABLE x2 USING COVERING INDEX x2i (a=? AND b=? AND rowid=?)}}
          362  +} {SEARCH TABLE x2 USING COVERING INDEX x2i (a=? AND b=? AND rowid=?)}
   366    363   
   367    364   #-------------------------------------------------------------------------
   368    365   # Check INDEXED BY works (throws an exception) with partial indexes that 
   369    366   # cannot be used.
   370    367   do_execsql_test 12.1 {
   371    368     CREATE TABLE o1(x INTEGER PRIMARY KEY, y, z);
   372    369     CREATE INDEX p1 ON o1(z);

Changes to test/indexexpr2.test.

    87     87   
    88     88   ifcapable json1 {
    89     89     do_eqp_test 3.3.1 {
    90     90       SELECT json_extract(x, '$.b') FROM t2 
    91     91       WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL 
    92     92       GROUP BY json_extract(x, '$.b') COLLATE nocase
    93     93       ORDER BY json_extract(x, '$.b') COLLATE nocase;
    94         -  } {
    95         -    0 0 0 {SCAN TABLE t2} 
    96         -    0 0 0 {USE TEMP B-TREE FOR GROUP BY}
    97         -  }
           94  +  } [string map {"\n  " \n} {
           95  +    QUERY PLAN
           96  +    |--SCAN TABLE t2
           97  +    `--USE TEMP B-TREE FOR GROUP BY
           98  +  }]
    98     99     
    99    100     do_execsql_test 3.3.2 {
   100    101       CREATE INDEX i3 ON t3(json_extract(x, '$.a'), json_extract(x, '$.b'));
   101    102     } {}
   102    103     
   103    104     do_eqp_test 3.3.3 {
   104    105       SELECT json_extract(x, '$.b') FROM t3 
   105    106       WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL 
   106    107       GROUP BY json_extract(x, '$.b') COLLATE nocase
   107    108       ORDER BY json_extract(x, '$.b') COLLATE nocase;
   108         -  } {
   109         -    0 0 0 {SEARCH TABLE t3 USING INDEX i3 (<expr>=?)} 
   110         -    0 0 0 {USE TEMP B-TREE FOR GROUP BY}
   111         -  }
          109  +  } [string map {"\n  " \n} {
          110  +    QUERY PLAN
          111  +    |--SEARCH TABLE t3 USING INDEX i3 (<expr>=?)
          112  +    `--USE TEMP B-TREE FOR GROUP BY
          113  +  }]
   112    114   }
   113    115   
   114    116   do_execsql_test 3.4.0 {
   115    117     CREATE TABLE t4(a, b);
   116    118     INSERT INTO t4 VALUES('.ABC', 1);
   117    119     INSERT INTO t4 VALUES('.abc', 2);
   118    120     INSERT INTO t4 VALUES('.ABC', 3);

Changes to test/join2.test.

   108    108     CREATE TABLE t3_1(k3 PRIMARY KEY, v3) WITHOUT ROWID;
   109    109     CREATE TABLE t3_2(v3, k3 PRIMARY KEY) WITHOUT ROWID;
   110    110   }
   111    111   
   112    112   do_eqp_test 3.1 {
   113    113     SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_1 USING (k3);
   114    114   } {
   115         -  0 0 0 {SCAN TABLE t1} 
   116         -  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
          115  +  QUERY PLAN
          116  +  |--SCAN TABLE t1
          117  +  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
   117    118   }
   118    119   
   119    120   do_eqp_test 3.2 {
   120    121     SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_2 USING (k3);
   121    122   } {
   122         -  0 0 0 {SCAN TABLE t1} 
   123         -  0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)}
          123  +  QUERY PLAN
          124  +  |--SCAN TABLE t1
          125  +  `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
   124    126   }
   125    127   
   126    128   #-------------------------------------------------------------------------
   127    129   # Test that tables other than the rightmost can be omitted from a
   128    130   # LEFT JOIN query.
   129    131   #
   130    132   do_execsql_test 4.0 {
................................................................................
   154    156   do_execsql_test 4.1.4 {
   155    157     SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
   156    158   } {2 v3 2 v3 1112 {} 1112 {}}
   157    159   
   158    160   do_eqp_test 4.1.5 {
   159    161     SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
   160    162   } {
   161         -  0 0 0 {SCAN TABLE c1} 
   162         -  0 1 1 {SEARCH TABLE c2 USING INTEGER PRIMARY KEY (rowid=?)}
   163         -  0 2 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)}
          163  +  QUERY PLAN
          164  +  |--SCAN TABLE c1
          165  +  |--SEARCH TABLE c2 USING INTEGER PRIMARY KEY (rowid=?)
          166  +  `--SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)
   164    167   }
   165    168   do_eqp_test 4.1.6 {
   166    169     SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
   167    170   } {
   168         -  0 0 0 {SCAN TABLE c1} 
   169         -  0 1 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)}
          171  +  QUERY PLAN
          172  +  |--SCAN TABLE c1
          173  +  `--SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)
   170    174   }
   171    175   
   172    176   do_execsql_test 4.2.0 {
   173    177     DROP TABLE c1;
   174    178     DROP TABLE c2;
   175    179     DROP TABLE c3;
   176    180     CREATE TABLE c1(k UNIQUE, v1);
................................................................................
   199    203   do_execsql_test 4.2.4 {
   200    204     SELECT v1, v3 FROM c1 LEFT JOIN c2 LEFT JOIN c3 ON (c3.k=v1+1);
   201    205   } {2 v3 2 v3 1112 {} 1112 {}}
   202    206   
   203    207   do_eqp_test 4.2.5 {
   204    208     SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2);
   205    209   } {
   206         -  0 0 0 {SCAN TABLE c1} 
   207         -  0 1 1 {SEARCH TABLE c2 USING INDEX sqlite_autoindex_c2_1 (k=?)}
   208         -  0 2 2 {SEARCH TABLE c3 USING INDEX sqlite_autoindex_c3_1 (k=?)}
          210  +  QUERY PLAN
          211  +  |--SCAN TABLE c1
          212  +  |--SEARCH TABLE c2 USING INDEX sqlite_autoindex_c2_1 (k=?)
          213  +  `--SEARCH TABLE c3 USING INDEX sqlite_autoindex_c3_1 (k=?)
   209    214   }
   210    215   do_eqp_test 4.2.6 {
   211    216     SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1);
   212    217   } {
   213         -  0 0 0 {SCAN TABLE c1} 
   214         -  0 1 2 {SEARCH TABLE c3 USING INDEX sqlite_autoindex_c3_1 (k=?)}
          218  +  QUERY PLAN
          219  +  |--SCAN TABLE c1
          220  +  `--SEARCH TABLE c3 USING INDEX sqlite_autoindex_c3_1 (k=?)
   215    221   }
   216    222   
   217    223   # 2017-11-23 (Thanksgiving day)
   218    224   # OSSFuzz found an assertion fault in the new LEFT JOIN eliminator code.
   219    225   #
   220    226   do_execsql_test 4.3.0 {
   221    227     DROP TABLE IF EXISTS t1;
................................................................................
   241    247     CREATE TABLE s1 (a INTEGER PRIMARY KEY);
   242    248     CREATE TABLE s2 (a INTEGER PRIMARY KEY);
   243    249     CREATE TABLE s3 (a INTEGER);
   244    250     CREATE UNIQUE INDEX ndx on s3(a);
   245    251   }
   246    252   do_eqp_test 5.1 {
   247    253     SELECT s1.a FROM s1 left join s2 using (a);
   248         -} {
   249         -  0 0 0 {SCAN TABLE s1}
   250         -}
          254  +} {SCAN TABLE s1}
          255  +
   251    256   do_eqp_test 5.2 {
   252    257     SELECT s1.a FROM s1 left join s3 using (a);
   253         -} {
   254         -  0 0 0 {SCAN TABLE s1}
   255         -}
          258  +} {SCAN TABLE s1}
   256    259   
   257    260   do_execsql_test 6.0 {
   258    261     CREATE TABLE u1(a INTEGER PRIMARY KEY, b, c);
   259    262     CREATE TABLE u2(a INTEGER PRIMARY KEY, b, c);
   260    263     CREATE INDEX u1ab ON u1(b, c);
   261    264   }
   262    265   do_eqp_test 6.1 {
   263    266     SELECT u2.* FROM u2 LEFT JOIN u1 ON( u1.a=u2.a AND u1.b=u2.b AND u1.c=u2.c );
   264         -} {
   265         -  0 0 0 {SCAN TABLE u2}
   266         -}
          267  +} {SCAN TABLE u2}
   267    268   
   268    269   db close
   269    270   sqlite3 db :memory:
   270    271   do_execsql_test 7.0 {
   271    272     CREATE TABLE t1(a,b);  INSERT INTO t1 VALUES(1,2),(3,4),(5,6);
   272    273     CREATE TABLE t2(c,d);  INSERT INTO t2 VALUES(2,4),(3,6);
   273    274     CREATE TABLE t3(x);    INSERT INTO t3 VALUES(9);

Changes to test/join5.test.

   260    260   }
   261    261   
   262    262   do_eqp_test 7.2 {
   263    263     SELECT * FROM t1 LEFT JOIN t2 ON (
   264    264       t2.x = t1.x AND (t2.y=? OR (t2.y=? AND t2.z IS NOT NULL))
   265    265     );
   266    266   } {
   267         -  0 0 0 {SCAN TABLE t1} 
   268         -  0 1 1 {SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)} 
   269         -  0 1 1 {SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)}
          267  +  QUERY PLAN
          268  +  |--SCAN TABLE t1
          269  +  |--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)
          270  +  `--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)
   270    271   }
   271    272   
   272    273   do_execsql_test 7.3 {
   273    274     CREATE TABLE t3(x);
   274    275   
   275    276     CREATE TABLE t4(x, y, z);
   276    277     CREATE INDEX t4xy ON t4(x, y);
................................................................................
   281    282   
   282    283     ANALYZE;
   283    284   }
   284    285   
   285    286   do_eqp_test 7.4 {
   286    287     SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t4.y = ? OR t4.z = ?);
   287    288   } {
   288         -  0 0 0 {SCAN TABLE t3} 
   289         -  0 1 1 {SEARCH TABLE t4 USING INDEX t4xz (x=?)}
          289  +  QUERY PLAN
          290  +  |--SCAN TABLE t3
          291  +  `--SEARCH TABLE t4 USING INDEX t4xz (x=?)
   290    292   } 
   291    293   
   292    294   finish_test
   293         -

Changes to test/mallocK.test.

   117    117   
   118    118     SELECT 'x' > '.';
   119    119   } {1}
   120    120   
   121    121   ifcapable stat4 {
   122    122     do_eqp_test 6.1 {
   123    123       SELECT DISTINCT c FROM t3 WHERE b BETWEEN '.xx..' AND '.xxxx';
   124         -  } {
   125         -    0 0 0 {SEARCH TABLE t3 USING INDEX i3 (ANY(a) AND b>? AND b<?)} 
   126         -    0 0 0 {USE TEMP B-TREE FOR DISTINCT}
   127         -  }
          124  +  } [string map {"\n  " \n} {
          125  +    QUERY PLAN
          126  +    |--SEARCH TABLE t3 USING INDEX i3 (ANY(a) AND b>? AND b<?)
          127  +    `--USE TEMP B-TREE FOR DISTINCT
          128  +  }]
   128    129   }
   129    130   
   130    131   do_faultsim_test 6 -faults oom* -body {
   131    132     db cache flush
   132    133     db eval { SELECT DISTINCT c FROM t3 WHERE b BETWEEN '.xx..' AND '.xxxx' }
   133    134   } -test {
   134    135     faultsim_test_result {0 {12 13 14 15}} 

Changes to test/orderby1.test.

   508    508     CREATE TABLE t1(a, b);
   509    509     CREATE INDEX i1 ON t1(a);
   510    510   }
   511    511   
   512    512   do_eqp_test 8.1 {
   513    513     SELECT * FROM t1 ORDER BY a, b;
   514    514   } {
   515         -  0 0 0 {SCAN TABLE t1 USING INDEX i1} 
   516         -  0 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY}
          515  +  QUERY PLAN
          516  +  |--SCAN TABLE t1 USING INDEX i1
          517  +  `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
   517    518   }
   518    519   
   519    520   do_execsql_test 8.2 {
   520    521     WITH cnt(i) AS (
   521    522       SELECT 1 UNION ALL SELECT i+1 FROM cnt WHERE i<10000
   522    523     )
   523    524     INSERT INTO t1 SELECT i%2, randomblob(500) FROM cnt;

Changes to test/rollback2.test.

    97     97   }
    98     98   
    99     99   #--------------------------------------------------------------------
   100    100   # Try with some index scans
   101    101   #
   102    102   do_eqp_test 3.1 {
   103    103     SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC;
   104         -} {0 0 0 {SCAN TABLE t1 USING INDEX i1}}
          104  +} {SCAN TABLE t1 USING INDEX i1}
   105    105   do_rollback_test 3.2 -setup {
   106    106     BEGIN;
   107    107       DELETE FROM t1 WHERE (i%2)==1;
   108    108   } -select {
   109    109     SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC;
   110    110   } -result {
   111    111     40 38 36 34 32 30 28 26 24 22 20 18 16 14 12 10  8  6  4  2
................................................................................
   127    127   # Now with some index scans that feature overflow keys.
   128    128   #
   129    129   set leader [string repeat "abcdefghij" 70]
   130    130   do_execsql_test 4.1 { UPDATE t1 SET h = $leader || h; }
   131    131   
   132    132   do_eqp_test 4.2 {
   133    133     SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC;
   134         -} {0 0 0 {SCAN TABLE t1 USING INDEX i1}}
          134  +} {SCAN TABLE t1 USING INDEX i1}
   135    135   do_rollback_test 4.3 -setup {
   136    136     BEGIN;
   137    137       DELETE FROM t1 WHERE (i%2)==1;
   138    138   } -select {
   139    139     SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC;
   140    140   } -result {
   141    141     2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40

Changes to test/rowvalue.test.

   171    171     INSERT INTO xy VALUES(3, 3, 3);
   172    172     INSERT INTO xy VALUES(4, 4, 4);
   173    173   }
   174    174   
   175    175   
   176    176   foreach {tn sql res eqp} {
   177    177     1 "SELECT * FROM xy WHERE (i, j) IS (2, 2)" {2 2 2} 
   178         -    "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid=?)}"
          178  +    "SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid=?)"
   179    179   
   180    180     2 "SELECT * FROM xy WHERE (k, j) < (2, 3)" {1 1 1 2 2 2}
   181         -    "0 0 0 {SCAN TABLE xy}"
          181  +    "SCAN TABLE xy"
   182    182   
   183    183     3 "SELECT * FROM xy WHERE (i, j) < (2, 3)" {1 1 1 2 2 2}
   184         -    "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid<?)}"
          184  +    "SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid<?)"
   185    185   
   186    186     4 "SELECT * FROM xy WHERE (i, j) > (2, 1)" {2 2 2 3 3 3 4 4 4}
   187         -    "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)}"
          187  +    "SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)"
   188    188   
   189    189     5 "SELECT * FROM xy WHERE (i, j) > ('2', 1)" {2 2 2 3 3 3 4 4 4}
   190         -    "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)}"
          190  +    "SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)"
   191    191   
   192    192   } {
   193    193     do_eqp_test 7.$tn.1 $sql $eqp
   194    194     do_execsql_test 7.$tn.2 $sql $res
   195    195   }
   196    196   
   197    197   do_execsql_test 8.0 {

Changes to test/rowvalue4.test.

   180    180       INSERT INTO c1(c, d) SELECT a, b FROM c1;
   181    181   
   182    182       CREATE INDEX c1ab ON c1(a, b);
   183    183       CREATE INDEX c1cd ON c1(c, d);
   184    184       ANALYZE;
   185    185     }
   186    186   
   187         -  do_eqp_test 3.1.1 { SELECT * FROM c1 WHERE a=1 AND c=2 } {
   188         -    0 0 0 {SEARCH TABLE c1 USING INDEX c1cd (c=?)}
   189         -  }
   190         -  do_eqp_test 3.1.2 { SELECT * FROM c1 WHERE a=1 AND b>'d' AND c=2 } {
   191         -    0 0 0 {SEARCH TABLE c1 USING INDEX c1cd (c=?)}
   192         -  }
   193         -  do_eqp_test 3.1.3 { SELECT * FROM c1 WHERE a=1 AND b>'l' AND c=2 } {
   194         -    0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=? AND b>?)}
   195         -  }
   196         -
   197         -  do_eqp_test 3.2.1 { SELECT * FROM c1 WHERE a=1 AND c>1 } {
   198         -    0 0 0 {SEARCH TABLE c1 USING INDEX c1cd (c>?)}
   199         -  }
   200         -  do_eqp_test 3.2.2 { SELECT * FROM c1 WHERE a=1 AND c>0 } {
   201         -    0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)}
   202         -  }
   203         -  do_eqp_test 3.2.3 { SELECT * FROM c1 WHERE a=1 AND c>=1 } {
   204         -    0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)}
   205         -  }
   206         -  do_eqp_test 3.2.4 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'c') } {
   207         -    0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)}
   208         -  }
   209         -  do_eqp_test 3.2.5 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'o') } {
   210         -    0 0 0 {SEARCH TABLE c1 USING INDEX c1cd ((c,d)>(?,?))}
   211         -  }
   212         -  do_eqp_test 3.2.6 { SELECT * FROM c1 WHERE a=1 AND (c, +b)>(1, 'c') } {
   213         -    0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)}
   214         -  }
          187  +  do_eqp_test 3.1.1 { SELECT * FROM c1 WHERE a=1 AND c=2 } \
          188  +     {SEARCH TABLE c1 USING INDEX c1cd (c=?)}
          189  +
          190  +  do_eqp_test 3.1.2 { SELECT * FROM c1 WHERE a=1 AND b>'d' AND c=2 } \
          191  +     {SEARCH TABLE c1 USING INDEX c1cd (c=?)}
          192  +
          193  +  do_eqp_test 3.1.3 { SELECT * FROM c1 WHERE a=1 AND b>'l' AND c=2 } \
          194  +     {SEARCH TABLE c1 USING INDEX c1ab (a=? AND b>?)}
          195  +
          196  +  do_eqp_test 3.2.1 { SELECT * FROM c1 WHERE a=1 AND c>1 } \
          197  +     {SEARCH TABLE c1 USING INDEX c1cd (c>?)}
          198  +
          199  +  do_eqp_test 3.2.2 { SELECT * FROM c1 WHERE a=1 AND c>0 } \
          200  +     {SEARCH TABLE c1 USING INDEX c1ab (a=?)}
          201  +
          202  +  do_eqp_test 3.2.3 { SELECT * FROM c1 WHERE a=1 AND c>=1 } \
          203  +     {SEARCH TABLE c1 USING INDEX c1ab (a=?)}
          204  +
          205  +  do_eqp_test 3.2.4 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'c') } \
          206  +     {SEARCH TABLE c1 USING INDEX c1ab (a=?)}
          207  +
          208  +  do_eqp_test 3.2.5 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'o') } \
          209  +     {SEARCH TABLE c1 USING INDEX c1cd ((c,d)>(?,?))}
          210  +
          211  +  do_eqp_test 3.2.6 { SELECT * FROM c1 WHERE a=1 AND (c, +b)>(1, 'c') } \
          212  +     {SEARCH TABLE c1 USING INDEX c1ab (a=?)}
          213  +
   215    214   }
   216    215   
   217    216   #------------------------------------------------------------------------
   218    217   
   219    218   do_execsql_test 5.0 {
   220    219     CREATE TABLE d1(x, y);
   221    220     CREATE TABLE d2(a, b, c);
................................................................................
   230    229   }
   231    230   
   232    231   do_eqp_test 5.1 {
   233    232     SELECT * FROM d2 WHERE 
   234    233       (a, b) IN (SELECT x, y FROM d1) AND
   235    234       (c) IN (SELECT y FROM d1)
   236    235   } {
   237         -  0 0 0 {SEARCH TABLE d2 USING INDEX d2ab (a=? AND b=?)}
   238         -  0 0 0 {EXECUTE LIST SUBQUERY 1} 
   239         -  1 0 0 {SCAN TABLE d1}
   240         -  0 0 0 {EXECUTE LIST SUBQUERY 2} 
   241         -  2 0 0 {SCAN TABLE d1}
          236  +  QUERY PLAN
          237  +  |--SEARCH TABLE d2 USING INDEX d2ab (a=? AND b=?)
          238  +  |--LIST SUBQUERY
          239  +  |  `--SCAN TABLE d1
          240  +  `--LIST SUBQUERY
          241  +     `--SCAN TABLE d1
   242    242   }
   243    243   
   244    244   do_execsql_test 6.0 {
   245    245     CREATE TABLE e1(a, b, c, d, e);
   246    246     CREATE INDEX e1ab ON e1(a, b);
   247    247     CREATE INDEX e1cde ON e1(c, d, e);
   248    248   }
   249    249   
   250    250   do_eqp_test 6.1 {
   251    251     SELECT * FROM e1 WHERE (a, b) > (?, ?)
   252         -} {
   253         -  0 0 0 {SEARCH TABLE e1 USING INDEX e1ab ((a,b)>(?,?))}
   254         -}
          252  +} {SEARCH TABLE e1 USING INDEX e1ab ((a,b)>(?,?))}
          253  +
   255    254   do_eqp_test 6.2 {
   256    255     SELECT * FROM e1 WHERE (a, b) < (?, ?)
   257         -} {
   258         -  0 0 0 {SEARCH TABLE e1 USING INDEX e1ab ((a,b)<(?,?))}
   259         -}
          256  +} {SEARCH TABLE e1 USING INDEX e1ab ((a,b)<(?,?))}
          257  +
   260    258   do_eqp_test 6.3 {
   261    259     SELECT * FROM e1 WHERE c = ? AND (d, e) > (?, ?)
   262         -} {
   263         -  0 0 0 {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)>(?,?))}
   264         -}
          260  +} {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)>(?,?))}
          261  +
   265    262   do_eqp_test 6.4 {
   266    263     SELECT * FROM e1 WHERE c = ? AND (d, e) < (?, ?)
   267         -} {
   268         -  0 0 0 {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)<(?,?))}
   269         -}
          264  +} {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)<(?,?))}
   270    265   
   271    266   do_eqp_test 6.5 {
   272    267     SELECT * FROM e1 WHERE (d, e) BETWEEN (?, ?) AND (?, ?) AND c = ?
   273         -} {
   274         -  0 0 0 
   275         -  {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)>(?,?) AND (d,e)<(?,?))}
   276         -}
          268  +} {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)>(?,?) AND (d,e)<(?,?))}
   277    269   
   278    270   #-------------------------------------------------------------------------
   279    271   
   280    272   do_execsql_test 7.1 {
   281    273     CREATE TABLE f1(a, b, c);
   282    274     CREATE INDEX f1ab ON f1(a, b);
   283    275   }

Changes to test/scanstatus.test.

   324    324   do_scanstatus_test 5.2.2 { 
   325    325     nLoop 1 nVisit 2 nEst 2.0 zName sqlite_autoindex_t1_1
   326    326     zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)}
   327    327   }
   328    328   
   329    329   do_eqp_test 5.3.1 {
   330    330     SELECT count(*) FROM t2 WHERE y = 'j';
   331         -} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}}
          331  +} {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
   332    332   do_execsql_test 5.3.2 {
   333    333     SELECT count(*) FROM t2 WHERE y = 'j';
   334    334   } {19}
   335    335   do_scanstatus_test 5.3.3 { 
   336    336     nLoop 1 nVisit 19 nEst 56.0 zName t2xy zExplain
   337    337     {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
   338    338   }
   339    339   
   340    340   do_eqp_test 5.4.1 {
   341    341     SELECT count(*) FROM t1, t2 WHERE y = c;
   342    342   } {
   343         -  0 0 0 {SCAN TABLE t1 USING COVERING INDEX t1bc}
   344         -  0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
          343  +  QUERY PLAN
          344  +  |--SCAN TABLE t1 USING COVERING INDEX t1bc
          345  +  `--SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)
   345    346   }
   346    347   do_execsql_test 5.4.2 {
   347    348     SELECT count(*) FROM t1, t2 WHERE y = c;
   348    349   } {200}
   349    350   do_scanstatus_test 5.4.3 { 
   350    351     nLoop 1 nVisit 10 nEst 10.0 zName t1bc 
   351    352     zExplain {SCAN TABLE t1 USING COVERING INDEX t1bc}
................................................................................
   352    353     nLoop 10 nVisit 200 nEst 56.0 zName t2xy 
   353    354     zExplain {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}
   354    355   }
   355    356   
   356    357   do_eqp_test 5.5.1 {
   357    358     SELECT count(*) FROM t1, t3 WHERE y = c;
   358    359   } {
   359         -  0 0 1 {SCAN TABLE t3} 
   360         -  0 1 0 {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)}
          360  +  QUERY PLAN
          361  +  |--SCAN TABLE t3
          362  +  `--SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)
   361    363   }
   362    364   do_execsql_test 5.5.2 {
   363    365     SELECT count(*) FROM t1, t3 WHERE y = c;
   364    366   } {200}
   365    367   do_scanstatus_test 5.5.3 { 
   366    368     nLoop 1 nVisit 501 nEst 480.0 zName t3 zExplain {SCAN TABLE t3}
   367    369     nLoop 501 nVisit 200 nEst 20.0 zName auto-index zExplain

Changes to test/selectA.test.

  1332   1332   
  1333   1333   do_eqp_test 4.1.2 {
  1334   1334     SELECT c, d FROM t5 
  1335   1335     UNION ALL
  1336   1336     SELECT a, b FROM t4 WHERE f()==f()
  1337   1337     ORDER BY 1,2
  1338   1338   } {
  1339         -  1 0 0 {SCAN TABLE t5 USING INDEX i2} 
  1340         -  1 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY}
  1341         -  2 0 0 {SCAN TABLE t4 USING INDEX i1} 
  1342         -  2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY}
  1343         -  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
         1339  +  QUERY PLAN
         1340  +  `--MERGE (UNION ALL)
         1341  +     |--LEFT
         1342  +     |  |--SCAN TABLE t5 USING INDEX i2
         1343  +     |  `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
         1344  +     `--RIGHT
         1345  +        |--SCAN TABLE t4 USING INDEX i1
         1346  +        `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
  1344   1347   }
  1345   1348   
  1346   1349   do_execsql_test 4.1.3 {
  1347   1350     SELECT c, d FROM t5 
  1348   1351     UNION ALL
  1349   1352     SELECT a, b FROM t4 WHERE f()==f()
  1350   1353     ORDER BY 1,2

Changes to test/skipscan2.test.

   195    195     for {set i 0} {$i < 1000} {incr i} {
   196    196       execsql { INSERT INTO t3 VALUES($i%2, $i, 'xyz') }
   197    197     }
   198    198     execsql { ANALYZE }
   199    199   } {}
   200    200   do_eqp_test skipscan2-3.3eqp {
   201    201     SELECT * FROM t3 WHERE b=42;
   202         -} {0 0 0 {SEARCH TABLE t3 USING PRIMARY KEY (ANY(a) AND b=?)}}
          202  +} {SEARCH TABLE t3 USING PRIMARY KEY (ANY(a) AND b=?)}
   203    203   
   204    204   
   205    205   finish_test

Changes to test/skipscan6.test.

   175    175     t3 t3_ba   {100 20 1 1}
   176    176   }
   177    177   
   178    178   # Use index "t3_a", as (a=?) is expected to match only a single row.
   179    179   #
   180    180   do_eqp_test 3.1 {
   181    181     SELECT * FROM t3 WHERE a = ? AND c = ?
   182         -} {
   183         -  0 0 0 {SEARCH TABLE t3 USING INDEX t3_a (a=?)}
   184         -}
          182  +} {SEARCH TABLE t3 USING INDEX t3_a (a=?)}
   185    183   
   186    184   # The same query on table t2. This should use index "t2_a", for the
   187    185   # same reason. At one point though, it was mistakenly using a skip-scan.
   188    186   #
   189    187   do_eqp_test 3.2 {
   190    188     SELECT * FROM t2 WHERE a = ? AND c = ?
   191         -} {
   192         -  0 0 0 {SEARCH TABLE t2 USING INDEX t2_a (a=?)}
   193         -}
   194         -
   195         -finish_test
   196         -
   197         -
   198         -
          189  +} {SEARCH TABLE t2 USING INDEX t2_a (a=?)}
   199    190   
   200    191   finish_test

Changes to test/tkt-385a5b56b9.test.

    30     30   
    31     31   do_execsql_test 2.0 {
    32     32     CREATE TABLE t2(x, y NOT NULL);
    33     33     CREATE UNIQUE INDEX t2x ON t2(x);
    34     34     CREATE UNIQUE INDEX t2y ON t2(y);
    35     35   }
    36     36   
    37         -do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } {
    38         -  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x}
    39         -}
           37  +do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } \
           38  +  {SCAN TABLE t2 USING COVERING INDEX t2x}
           39  +
           40  +do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } \
           41  +  {SCAN TABLE t2 USING COVERING INDEX t2y}
    40     42   
    41         -do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } {
    42         -  0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2y}
    43         -}
           43  +do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } \
           44  +  {SEARCH TABLE t2 USING INDEX t2y (y=?)}
    44     45   
    45         -do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } {
    46         -  0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?)}
    47         -}
           46  +do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } \
           47  +  {SEARCH TABLE t2 USING INDEX t2x (x=?)}
    48     48   
    49         -do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } {
    50         -  0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?)}
    51         -}
    52     49   
    53     50   finish_test

Changes to test/tkt-b75a9ca6b0.test.

    28     28     INSERT INTO t1 VALUES (3, 1);
    29     29   }
    30     30   
    31     31   do_execsql_test 1.1 {
    32     32     CREATE INDEX i1 ON t1(x, y);
    33     33   } 
    34     34   
    35         -set idxscan {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}}
    36         -set tblscan {0 0 0 {SCAN TABLE t1}}
    37         -set grpsort {0 0 0 {USE TEMP B-TREE FOR GROUP BY}}
    38         -set sort    {0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
           35  +set idxscan {SCAN TABLE t1 USING COVERING INDEX i1}
           36  +set tblscan {SCAN TABLE t1}
           37  +set grpsort {USE TEMP B-TREE FOR GROUP BY}
           38  +set sort    {USE TEMP B-TREE FOR ORDER BY}
    39     39   
    40     40   foreach {tn q res eqp} [subst -nocommands {
    41     41     1 "SELECT * FROM t1 GROUP BY x, y ORDER BY x,y"
    42     42     {1 3  2 2  3 1} {$idxscan}
    43     43   
    44     44     2 "SELECT * FROM t1 GROUP BY x, y ORDER BY x"
    45         -  {1 3  2 2  3 1} {$idxscan $sort}
           45  +  {1 3  2 2  3 1} {$idxscan*$sort}
    46     46   
    47     47     3 "SELECT * FROM t1 GROUP BY y, x ORDER BY y, x"
    48         -  {3 1  2 2  1 3} {$idxscan $sort}
           48  +  {3 1  2 2  1 3} {$idxscan*$sort}
    49     49     
    50     50     4 "SELECT * FROM t1 GROUP BY x ORDER BY x"
    51     51     {1 3  2 2  3 1} {$idxscan}
    52     52   
    53     53     5 "SELECT * FROM t1 GROUP BY y ORDER BY y"
    54         -  {3 1  2 2  1 3} {$tblscan $grpsort}
           54  +  {3 1  2 2  1 3} {$tblscan*$grpsort}
    55     55   
    56     56     6 "SELECT * FROM t1 GROUP BY y ORDER BY x"
    57         -  {1 3  2 2  3 1} {$tblscan $grpsort $sort}
           57  +  {1 3  2 2  3 1} {$tblscan*$grpsort*$sort}
    58     58   
    59     59     7 "SELECT * FROM t1 GROUP BY x, y ORDER BY x, y DESC"
    60         -  {1 3  2 2  3 1} {$idxscan $sort}
           60  +  {1 3  2 2  3 1} {$idxscan*$sort}
    61     61   
    62     62     8 "SELECT * FROM t1 GROUP BY x, y ORDER BY x DESC, y DESC"
    63         -  {3 1  2 2  1 3} {$idxscan $sort}
           63  +  {3 1  2 2  1 3} {$idxscan*$sort}
    64     64   
    65     65     9 "SELECT * FROM t1 GROUP BY x, y ORDER BY x ASC, y ASC"
    66     66     {1 3  2 2  3 1} {$idxscan}
    67     67   
    68     68     10 "SELECT * FROM t1 GROUP BY x, y ORDER BY x COLLATE nocase, y"
    69         -  {1 3  2 2  3 1} {$idxscan $sort}
           69  +  {1 3  2 2  3 1} {$idxscan*$sort}
    70     70   
    71     71   }] {
    72     72     do_execsql_test 1.$tn.1 $q $res
    73     73     do_eqp_test     1.$tn.2 $q $eqp
    74     74   }
    75     75   
    76     76   
    77     77   finish_test

Changes to test/tpch01.test.

   161    161                                  and p_type = 'LARGE PLATED STEEL'
   162    162                  ) as all_nations
   163    163          group by
   164    164                  o_year
   165    165          order by
   166    166                  o_year;}]
   167    167     set ::eqpres
   168         -} {/0 0 0 {SEARCH TABLE part USING INDEX bootleg_pti .P_TYPE=..} 0 1 2 {SEARCH TABLE lineitem USING INDEX lpki2 .L_PARTKEY=..}.*/}
          168  +} {/*SEARCH TABLE part USING INDEX bootleg_pti *SEARCH TABLE lineitem USING INDEX lpki2*/}
   169    169   do_test tpch01-1.1b {
   170    170     set ::eqpres
   171    171   } {/.* customer .* nation AS n1 .*/}
   172    172   do_test tpch01-1.1c {
   173    173     set ::eqpres
   174    174   } {/.* supplier .* nation AS n2 .*/}
   175    175   
................................................................................
   183    183       c_custkey = o_custkey    and l_orderkey = o_orderkey
   184    184       and o_orderdate >=  '1994-08-01'    and o_orderdate < date('1994-08-01', '+3 month')
   185    185       and l_returnflag = 'R'    and c_nationkey = n_nationkey
   186    186   group by
   187    187       c_custkey,    c_name,    c_acctbal,    c_phone,    n_name, c_address,    c_comment
   188    188   order by
   189    189       revenue desc;
   190         -} {0 0 1 {SEARCH TABLE orders USING INDEX odi (O_ORDERDATE>? AND O_ORDERDATE<?)} 0 1 0 {SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?)} 0 2 3 {SEARCH TABLE nation USING INDEX npki (N_NATIONKEY=?)} 0 3 2 {SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
          190  +} {
          191  +  QUERY PLAN
          192  +  |--SEARCH TABLE orders USING INDEX odi (O_ORDERDATE>? AND O_ORDERDATE<?)
          193  +  |--SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?)
          194  +  |--SEARCH TABLE nation USING INDEX npki (N_NATIONKEY=?)
          195  +  |--SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?)
          196  +  |--USE TEMP B-TREE FOR GROUP BY
          197  +  `--USE TEMP B-TREE FOR ORDER BY
          198  +}
   191    199   
   192    200   finish_test

Changes to test/unordered.test.

    36     36     if {$idxmode == "unordered"} {
    37     37       execsql { UPDATE sqlite_stat1 SET stat = stat || ' unordered' }
    38     38     }
    39     39     db close
    40     40     sqlite3 db test.db
    41     41     foreach {tn sql r(ordered) r(unordered)} {
    42     42       1   "SELECT * FROM t1 ORDER BY a"
    43         -        {0 0 0 {SCAN TABLE t1 USING INDEX i1}}
    44         -        {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
           43  +        {SCAN TABLE t1 USING INDEX i1}
           44  +        {SCAN TABLE t1*USE TEMP B-TREE FOR ORDER BY}
    45     45       2   "SELECT * FROM t1 WHERE a > 100"
    46         -        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
    47         -        {0 0 0 {SCAN TABLE t1}}
           46  +        {SEARCH TABLE t1 USING INDEX i1 (a>?)}
           47  +        {SCAN TABLE t1}
    48     48       3   "SELECT * FROM t1 WHERE a = ? ORDER BY rowid"
    49         -        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
    50         -        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 
    51         -         0 0 0 {USE TEMP B-TREE FOR ORDER BY}}
           49  +        {SEARCH TABLE t1 USING INDEX i1 (a=?)}
           50  +        {SEARCH TABLE t1 USING INDEX i1 (a=?)*USE TEMP B-TREE FOR ORDER BY}
    52     51       4   "SELECT max(a) FROM t1"
    53         -        {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1}}
    54         -        {0 0 0 {SEARCH TABLE t1}}
           52  +        {SEARCH TABLE t1 USING COVERING INDEX i1}
           53  +        {SEARCH TABLE t1}
    55     54       5   "SELECT group_concat(b) FROM t1 GROUP BY a"
    56         -        {0 0 0 {SCAN TABLE t1 USING INDEX i1}}
    57         -        {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}}
           55  +        {SCAN TABLE t1 USING INDEX i1}
           56  +        {SCAN TABLE t1*USE TEMP B-TREE FOR GROUP BY}
    58     57   
    59     58       6   "SELECT * FROM t1 WHERE a = ?"
    60         -        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
    61         -        {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
           59  +        {SEARCH TABLE t1 USING INDEX i1 (a=?)}
           60  +        {SEARCH TABLE t1 USING INDEX i1 (a=?)}
    62     61       7   "SELECT count(*) FROM t1"
    63         -        {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}}
    64         -        {0 0 0 {SCAN TABLE t1}}
           62  +        {SCAN TABLE t1 USING COVERING INDEX i1}
           63  +        {SCAN TABLE t1}
    65     64     } {
    66     65       do_eqp_test 1.$idxmode.$tn $sql $r($idxmode)
    67     66     }
    68     67   }
    69     68   
    70     69   finish_test

Changes to test/where7.test.

 23337  23337         c2 INTEGER,
 23338  23338         c4 INTEGER,
 23339  23339         FOREIGN KEY (c8) REFERENCES t301(c8)
 23340  23340     );
 23341  23341     CREATE INDEX t302_c3 on t302(c3);
 23342  23342     CREATE INDEX t302_c8_c3 on t302(c8, c3);
 23343  23343     CREATE INDEX t302_c5 on t302(c5);
 23344         -  
 23345         -  EXPLAIN QUERY PLAN
        23344  +}
        23345  +do_eqp_test where7-3.2 {
 23346  23346     SELECT t302.c1 
 23347  23347       FROM t302 JOIN t301 ON t302.c8 = +t301.c8
 23348  23348       WHERE t302.c2 = 19571
 23349  23349         AND t302.c3 > 1287603136
 23350  23350         AND (t301.c4 = 1407449685622784
 23351  23351              OR t301.c8 = 1407424651264000)
 23352  23352      ORDER BY t302.c5 LIMIT 200;
 23353  23353   } {
 23354         -  0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?)} 
 23355         -  0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)} 
 23356         -  0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)} 
 23357         -  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
        23354  +  QUERY PLAN
        23355  +  |--SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?)
        23356  +  |--SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)
        23357  +  |--SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)
        23358  +  `--USE TEMP B-TREE FOR ORDER BY
 23358  23359   }
 23359  23360   
 23360  23361   finish_test

Changes to test/where9.test.

   353    353        WHERE t1.a=t3.y OR t1.b=t3.y*11 OR (t1.c=27027 AND round(t1.d)==80)
   354    354       ORDER BY 1, 2, 3
   355    355     }
   356    356   } {1 80 2 1 80 28 1 80 54 1 80 80 2 80 2 2 80 28 2 80 54 2 80 80 scan 1 sort 1}
   357    357   
   358    358   
   359    359   ifcapable explain {
   360         -  do_execsql_test where9-3.1 {
   361         -    EXPLAIN QUERY PLAN
          360  +  do_eqp_test where9-3.1 {
   362    361       SELECT t2.a FROM t1, t2
   363    362       WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
   364         -  } {
   365         -    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} 
   366         -    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} 
   367         -    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)}
   368         -  }
   369         -  do_execsql_test where9-3.2 {
   370         -    EXPLAIN QUERY PLAN
          363  +  } [string map {"\n  " \n} {
          364  +    QUERY PLAN
          365  +    |--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
          366  +    |--SEARCH TABLE t2 USING INDEX t2d (d=?)
          367  +    `--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
          368  +  }]
          369  +  do_eqp_test where9-3.2 {
   371    370       SELECT coalesce(t2.a,9999)
   372    371       FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
   373    372       WHERE t1.a=80
   374         -  } {
   375         -    0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} 
   376         -    0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} 
   377         -    0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)}
   378         -  }
          373  +  } [string map {"\n  " \n} {
          374  +    QUERY PLAN
          375  +    |--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
          376  +    |--SEARCH TABLE t2 USING INDEX t2d (d=?)
          377  +    `--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
          378  +  }]
   379    379   } 
   380    380   
   381    381   # Make sure that INDEXED BY and multi-index OR clauses play well with
   382    382   # one another.
   383    383   #
   384    384   do_test where9-4.1 {
   385    385     count_steps {
................................................................................
   442    442       SELECT a FROM t1 INDEXED BY t1d
   443    443        WHERE b>1000
   444    444          AND (c=31031 OR d IS NULL)
   445    445        ORDER BY +a
   446    446     }
   447    447   } {1 {no query solution}}
   448    448   
   449         -ifcapable explain {
   450         -  # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
   451         -  # the former is an equality test which is expected to return fewer rows.
   452         -  #
   453         -  do_execsql_test where9-5.1 {
   454         -    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
   455         -  } {
   456         -    0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)} 
   457         -    0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?)}
   458         -  }
   459         -
   460         -  # In contrast, b=1000 is preferred over any OR-clause.
   461         -  #
   462         -  do_execsql_test where9-5.2 {
   463         -    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
   464         -  } {
   465         -    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}
   466         -  }
   467         -
   468         -  # Likewise, inequalities in an AND are preferred over inequalities in
   469         -  # an OR.
   470         -  #
   471         -  do_execsql_test where9-5.3 {
   472         -    EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL)
   473         -  } {
   474         -    0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?)}
   475         -  }
          449  +# The (c=31031 OR d IS NULL) clause is preferred over b>1000 because
          450  +# the former is an equality test which is expected to return fewer rows.
          451  +#
          452  +do_eqp_test where9-5.1 {
          453  +  SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
          454  +} {
          455  +  QUERY PLAN
          456  +  |--SEARCH TABLE t1 USING INDEX t1c (c=?)
          457  +  `--SEARCH TABLE t1 USING INDEX t1d (d=?)
   476    458   }
          459  +
          460  +# In contrast, b=1000 is preferred over any OR-clause.
          461  +#
          462  +do_eqp_test where9-5.2 {
          463  +  SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL)
          464  +} {SEARCH TABLE t1 USING INDEX t1b (b=?)}
          465  +
          466  +# Likewise, inequalities in an AND are preferred over inequalities in
          467  +# an OR.
          468  +#
          469  +do_eqp_test where9-5.3 {
          470  +  SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL)
          471  +} {SEARCH TABLE t1 USING INDEX t1b (b>?)}
   477    472   
   478    473   ############################################################################
   479    474   # Make sure OR-clauses work correctly on UPDATE and DELETE statements.
   480    475   
   481    476   do_test where9-6.2.1 {
   482    477     db eval {SELECT count(*) FROM t1 UNION ALL SELECT a FROM t1 WHERE a>=85}
   483    478   } {99 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99}

Changes to test/whereG.test.

    62     62   } {}
    63     63   do_eqp_test whereG-1.1 {
    64     64     SELECT DISTINCT aname
    65     65       FROM album, composer, track
    66     66      WHERE unlikely(cname LIKE '%bach%')
    67     67        AND composer.cid=track.cid
    68     68        AND album.aid=track.aid;
    69         -} {/.*composer.*track.*album.*/}
           69  +} {composer*track*album}
    70     70   do_execsql_test whereG-1.2 {
    71     71     SELECT DISTINCT aname
    72     72       FROM album, composer, track
    73     73      WHERE unlikely(cname LIKE '%bach%')
    74     74        AND composer.cid=track.cid
    75     75        AND album.aid=track.aid;
    76     76   } {{Mass in B Minor, BWV 232}}
................................................................................
   191    191   
   192    192   do_execsql_test 5.1 {
   193    193     CREATE TABLE t1(a, b, c);
   194    194     CREATE INDEX i1 ON t1(a, b);
   195    195   }
   196    196   do_eqp_test 5.1.2 {
   197    197     SELECT * FROM t1 WHERE a>?
   198         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}}
          198  +} {SEARCH TABLE t1 USING INDEX i1 (a>?)}
   199    199   do_eqp_test 5.1.3 {
   200    200     SELECT * FROM t1 WHERE likelihood(a>?, 0.9)
   201         -} {0 0 0 {SCAN TABLE t1}}
          201  +} {SCAN TABLE t1}
   202    202   do_eqp_test 5.1.4 {
   203    203     SELECT * FROM t1 WHERE likely(a>?)
   204         -} {0 0 0 {SCAN TABLE t1}}
          204  +} {SCAN TABLE t1}
   205    205   
   206    206   do_test 5.2 {
   207    207     for {set i 0} {$i < 100} {incr i} {
   208    208       execsql { INSERT INTO t1 VALUES('abc', $i, $i); }
   209    209     }
   210    210     execsql { INSERT INTO t1 SELECT 'def', b, c FROM t1; }
   211    211     execsql { ANALYZE }
   212    212   } {}
   213    213   do_eqp_test 5.2.2 {
   214    214     SELECT * FROM t1 WHERE likelihood(b>?, 0.01)
   215         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>?)}}
          215  +} {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>?)}
   216    216   do_eqp_test 5.2.3 {
   217    217     SELECT * FROM t1 WHERE likelihood(b>?, 0.9)
   218         -} {0 0 0 {SCAN TABLE t1}}
          218  +} {SCAN TABLE t1}
   219    219   do_eqp_test 5.2.4 {
   220    220     SELECT * FROM t1 WHERE likely(b>?)
   221         -} {0 0 0 {SCAN TABLE t1}}
          221  +} {SCAN TABLE t1}
   222    222   
   223    223   do_eqp_test 5.3.1 {
   224    224     SELECT * FROM t1 WHERE a=?
   225         -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}}
          225  +} {SEARCH TABLE t1 USING INDEX i1 (a=?)}
   226    226   do_eqp_test 5.3.2 {
   227    227     SELECT * FROM t1 WHERE likelihood(a=?, 0.9)
   228         -} {0 0 0 {SCAN TABLE t1}}
          228  +} {SCAN TABLE t1}
   229    229   do_eqp_test 5.3.3 {
   230    230     SELECT * FROM t1 WHERE likely(a=?)
   231         -} {0 0 0 {SCAN TABLE t1}}
          231  +} {SCAN TABLE t1}
   232    232   
   233    233   # 2015-06-18
   234    234   # Ticket [https://www.sqlite.org/see/tktview/472f0742a1868fb58862bc588ed70]
   235    235   #
   236    236   do_execsql_test 6.0 {
   237    237     DROP TABLE IF EXISTS t1;
   238    238     CREATE TABLE t1(i int, x, y, z);

Changes to test/whereI.test.

    25     25     CREATE INDEX i1 ON t1(b);
    26     26     CREATE INDEX i2 ON t1(c);
    27     27   }
    28     28   
    29     29   do_eqp_test 1.1 {
    30     30     SELECT a FROM t1 WHERE b='b' OR c='x'
    31     31   } {
    32         -  0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b=?)} 
    33         -  0 0 0 {SEARCH TABLE t1 USING INDEX i2 (c=?)}
           32  +  QUERY PLAN
           33  +  |--SEARCH TABLE t1 USING INDEX i1 (b=?)
           34  +  `--SEARCH TABLE t1 USING INDEX i2 (c=?)
    34     35   }
    35     36   
    36     37   do_execsql_test 1.2 {
    37     38     SELECT a FROM t1 WHERE b='b' OR c='x'
    38     39   } {2 3}
    39     40   
    40     41   do_execsql_test 1.3 {
................................................................................
    53     54     CREATE INDEX i3 ON t2(b);
    54     55     CREATE INDEX i4 ON t2(c);
    55     56   }
    56     57   
    57     58   do_eqp_test 2.1 {
    58     59     SELECT a FROM t2 WHERE b='b' OR c='x'
    59     60   } {
    60         -  0 0 0 {SEARCH TABLE t2 USING INDEX i3 (b=?)} 
    61         -  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)}
           61  +  QUERY PLAN
           62  +  |--SEARCH TABLE t2 USING INDEX i3 (b=?)
           63  +  `--SEARCH TABLE t2 USING INDEX i4 (c=?)
    62     64   }
    63     65   
    64     66   do_execsql_test 2.2 {
    65     67     SELECT a FROM t2 WHERE b='b' OR c='x'
    66     68   } {ii iii}
    67     69   
    68     70   do_execsql_test 2.3 {

Changes to test/whereJ.test.

   398    398   
   399    399   # This one should use index "idx_c".
   400    400   do_eqp_test 3.4 {
   401    401     SELECT * FROM t1 WHERE 
   402    402       a = 4 AND b BETWEEN 20 AND 80           -- Matches 80 rows
   403    403         AND
   404    404       c BETWEEN 150 AND 160                   -- Matches 10 rows
   405         -} {
   406         -  0 0 0 {SEARCH TABLE t1 USING INDEX idx_c (c>? AND c<?)}
   407         -}
          405  +} {SEARCH TABLE t1 USING INDEX idx_c (c>? AND c<?)}
   408    406   
   409    407   # This one should use index "idx_ab".
   410    408   do_eqp_test 3.5 {
   411    409     SELECT * FROM t1 WHERE 
   412    410       a = 5 AND b BETWEEN 20 AND 80           -- Matches 1 row
   413    411         AND
   414    412       c BETWEEN 150 AND 160                   -- Matches 10 rows
   415         -} {
   416         -  0 0 0 {SEARCH TABLE t1 USING INDEX idx_ab (a=? AND b>? AND b<?)}
   417         -}
          413  +} {SEARCH TABLE t1 USING INDEX idx_ab (a=? AND b>? AND b<?)}
   418    414   
   419    415   ###########################################################################################
   420    416   
   421    417   # Reset the database and setup for a test case derived from actual SQLite users
   422    418   #
   423    419   db close
   424    420   sqlite3 db test.db

Changes to test/with1.test.

   988    988       FROM xyz ORDER BY 1
   989    989     )
   990    990     SELECT 1 FROM xyz;
   991    991   } 1
   992    992   
   993    993   # EXPLAIN QUERY PLAN on a self-join of a CTE
   994    994   #
   995         -do_execsql_test 19.1 {
          995  +do_execsql_test 19.1a {
   996    996     DROP TABLE IF EXISTS t1;
   997    997     CREATE TABLE t1(x);
   998         -  EXPLAIN QUERY PLAN
          998  +}
          999  +do_eqp_test 19.1b {
   999   1000     WITH
  1000   1001       x1(a) AS (values(100))
  1001   1002     INSERT INTO t1(x)
  1002   1003       SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2);
  1003   1004     SELECT * FROM t1;
  1004         -} {0 0 0 {SCAN SUBQUERY 1} 0 1 1 {SCAN SUBQUERY 1}}
         1005  +} {
         1006  +  QUERY PLAN
         1007  +  |--MATERIALIZE xxxxxx
         1008  +  |--SCAN SUBQUERY xxxxxx
         1009  +  `--SCAN SUBQUERY xxxxxx
         1010  +}
  1005   1011   
  1006   1012   # 2017-10-28.
  1007   1013   # See check-in https://sqlite.org/src/info/0926df095faf72c2
  1008   1014   # Tried to optimize co-routine processing by changing a Copy opcode
  1009   1015   # into SCopy.  But OSSFuzz found two (similar) cases where that optimization
  1010   1016   # does not work.
  1011   1017   #

Changes to test/with3.test.

    75     75       ANALYZE;
    76     76   
    77     77     }
    78     78   
    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         -  } {
    83         -    3 0 0 {SCAN TABLE cnt} 
    84         -    1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)}
    85         -    0 0 0 {SCAN SUBQUERY 1} 
    86         -    0 1 1 {SEARCH TABLE y1 USING INDEX y1a (a=?)}
    87         -  }
           82  +  } [string map {"\n  " \n} {
           83  +    QUERY PLAN
           84  +    |--MATERIALIZE xxxxxx
           85  +    |  |--SETUP
           86  +    |  `--RECURSIVE STEP
           87  +    |     `--SCAN TABLE cnt
           88  +    |--SCAN SUBQUERY xxxxxx
           89  +    `--SEARCH TABLE y1 USING INDEX y1a (a=?)
           90  +  }]
    88     91   
    89     92     do_eqp_test 3.1.3 {
    90     93       WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000)
    91     94       SELECT * FROM cnt, y1 WHERE i=a
    92         -  } {
    93         -    3 0 0 {SCAN TABLE cnt} 
    94         -    1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)}
    95         -    0 0 1 {SCAN TABLE y1} 
    96         -    0 1 0 {SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (i=?)}
    97         -  }
           95  +  } [string map {"\n  " \n} {
           96  +    QUERY PLAN
           97  +    |--MATERIALIZE xxxxxx
           98  +    |  |--SETUP
           99  +    |  `--RECURSIVE STEP
          100  +    |     `--SCAN TABLE cnt
          101  +    |--SCAN TABLE y1
          102  +    `--SEARCH SUBQUERY xxxxxx USING AUTOMATIC COVERING INDEX (i=?)
          103  +  }]
    98    104   }
    99    105   
   100    106   do_execsql_test 3.2.1 {
   101    107     CREATE TABLE w1(pk INTEGER PRIMARY KEY, x INTEGER);
   102    108     CREATE TABLE w2(pk INTEGER PRIMARY KEY);
   103    109   }
   104    110   
   105    111   do_eqp_test 3.2.2 {
   106    112     WITH RECURSIVE c(w,id) AS (SELECT 0, (SELECT pk FROM w2 LIMIT 1)
   107    113        UNION ALL SELECT c.w + 1, x FROM w1, c LIMIT 1)
   108    114        SELECT * FROM c, w2, w1
   109    115        WHERE c.id=w2.pk AND c.id=w1.pk;
   110    116   } {
   111         -  2 0 0 {EXECUTE SCALAR SUBQUERY 3} 
   112         -  3 0 0 {SCAN TABLE w2} 
   113         -  4 0 0 {SCAN TABLE w1}
   114         -  4 1 1 {SCAN TABLE c} 
   115         -  1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)} 0 0 0 {SCAN SUBQUERY 1}
   116         -  0 1 1 {SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)} 
   117         -  0 2 2 {SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)}
          117  +  QUERY PLAN
          118  +  |--MATERIALIZE xxxxxx
          119  +  |  |--SETUP
          120  +  |  |  `--SCALAR SUBQUERY
          121  +  |  |     `--SCAN TABLE w2
          122  +  |  `--RECURSIVE STEP
          123  +  |     |--SCAN TABLE w1
          124  +  |     `--SCAN TABLE c
          125  +  |--SCAN SUBQUERY xxxxxx
          126  +  |--SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)
          127  +  `--SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)
   118    128   }
   119    129   
   120    130   finish_test

Changes to test/without_rowid1.test.

   234    234     INSERT INTO t45 VALUES(5, 'two', 'x');
   235    235     INSERT INTO t45 VALUES(7, 'two', 'x');
   236    236     INSERT INTO t45 VALUES(9, 'two', 'x');
   237    237   }
   238    238   
   239    239   do_eqp_test 5.1 {
   240    240     SELECT * FROM t45 WHERE b=? AND a>?
   241         -} {/*USING INDEX i45 (b=? AND a>?)*/}
          241  +} {USING INDEX i45 (b=? AND a>?)}
   242    242   
   243    243   do_execsql_test 5.2 {
   244    244     SELECT * FROM t45 WHERE b='two' AND a>4
   245    245   } {5 two x 7 two x 9 two x}
   246    246   
   247    247   do_execsql_test 5.3 {
   248    248     SELECT * FROM t45 WHERE b='one' AND a<8
................................................................................
   253    253     WITH r(x) AS (
   254    254       SELECT 1 UNION ALL SELECT x+1 FROM r WHERE x<100
   255    255     )
   256    256     INSERT INTO t46 SELECT x / 20, x % 20, x % 10, x FROM r;
   257    257   }
   258    258   
   259    259   set queries {
   260         -  1    2    "c = 5 AND a = 1"          {/*i46 (c=? AND a=?)*/}
   261         -  2    6    "c = 4 AND a < 3"          {/*i46 (c=? AND a<?)*/}
   262         -  3    4    "c = 2 AND a >= 3"         {/*i46 (c=? AND a>?)*/}
   263         -  4    1    "c = 2 AND a = 1 AND b<10" {/*i46 (c=? AND a=? AND b<?)*/}
   264         -  5    1    "c = 0 AND a = 0 AND b>5"  {/*i46 (c=? AND a=? AND b>?)*/}
          260  +  1    2    "c = 5 AND a = 1"          {i46 (c=? AND a=?)}
          261  +  2    6    "c = 4 AND a < 3"          {i46 (c=? AND a<?)}
          262  +  3    4    "c = 2 AND a >= 3"         {i46 (c=? AND a>?)}
          263  +  4    1    "c = 2 AND a = 1 AND b<10" {i46 (c=? AND a=? AND b<?)}
          264  +  5    1    "c = 0 AND a = 0 AND b>5"  {i46 (c=? AND a=? AND b>?)}
   265    265   }
   266    266   
   267    267   foreach {tn cnt where eqp} $queries {
   268    268     do_execsql_test 5.5.$tn.1 "SELECT count(*) FROM t46 WHERE $where" $cnt
   269    269   }
   270    270   
   271    271   do_execsql_test 5.6 {