drh | 588a9a1 | 2008-09-01 15:52:10 +0000 | [diff] [blame] | 1 | # 2001 September 15 |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 2 | # |
drh | b19a2bc | 2001-09-16 00:13:26 +0000 | [diff] [blame] | 3 | # The author disclaims copyright to this source code. In place of |
| 4 | # a legal notice, here is a blessing: |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 5 | # |
drh | b19a2bc | 2001-09-16 00:13:26 +0000 | [diff] [blame] | 6 | # May you do good and not evil. |
| 7 | # May you find forgiveness for yourself and forgive others. |
| 8 | # May you share freely, never taking more than you give. |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 9 | # |
| 10 | #*********************************************************************** |
| 11 | # This file implements regression tests for SQLite library. The |
| 12 | # focus of this file is testing the use of indices in WHERE clases. |
| 13 | # |
danielk1977 | 3072c5e | 2008-11-03 09:06:05 +0000 | [diff] [blame] | 14 | # $Id: where.test,v 1.50 2008/11/03 09:06:06 danielk1977 Exp $ |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 15 | |
| 16 | set testdir [file dirname $argv0] |
| 17 | source $testdir/tester.tcl |
| 18 | |
| 19 | # Build some test data |
| 20 | # |
| 21 | do_test where-1.0 { |
| 22 | execsql { |
| 23 | CREATE TABLE t1(w int, x int, y int); |
| 24 | CREATE TABLE t2(p int, q int, r int, s int); |
| 25 | } |
| 26 | for {set i 1} {$i<=100} {incr i} { |
| 27 | set w $i |
| 28 | set x [expr {int(log($i)/log(2))}] |
| 29 | set y [expr {$i*$i + 2*$i + 1}] |
| 30 | execsql "INSERT INTO t1 VALUES($w,$x,$y)" |
| 31 | } |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 32 | |
| 33 | ifcapable subquery { |
| 34 | execsql { |
| 35 | INSERT INTO t2 SELECT 101-w, x, (SELECT max(y) FROM t1)+1-y, y FROM t1; |
| 36 | } |
| 37 | } else { |
| 38 | set maxy [execsql {select max(y) from t1}] |
| 39 | execsql " |
| 40 | INSERT INTO t2 SELECT 101-w, x, $maxy+1-y, y FROM t1; |
| 41 | " |
| 42 | } |
| 43 | |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 44 | execsql { |
drh | edb04ed | 2015-09-04 12:54:01 +0000 | [diff] [blame] | 45 | CREATE INDEX i1w ON t1("w"); -- Verify quoted identifier names |
| 46 | CREATE INDEX i1xy ON t1(`x`,'y' ASC); -- Old MySQL compatibility |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 47 | CREATE INDEX i2p ON t2(p); |
| 48 | CREATE INDEX i2r ON t2(r); |
| 49 | CREATE INDEX i2qs ON t2(q, s); |
| 50 | } |
| 51 | } {} |
| 52 | |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 53 | # Do an SQL statement. Append the search count to the end of the result. |
| 54 | # |
| 55 | proc count sql { |
| 56 | set ::sqlite_search_count 0 |
| 57 | return [concat [execsql $sql] $::sqlite_search_count] |
| 58 | } |
| 59 | |
| 60 | # Verify that queries use an index. We are using the special variable |
| 61 | # "sqlite_search_count" which tallys the number of executions of MoveTo |
| 62 | # and Next operators in the VDBE. By verifing that the search count is |
| 63 | # small we can be assured that indices are being used properly. |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 64 | # |
drh | 4f07e5f | 2007-05-14 11:34:46 +0000 | [diff] [blame] | 65 | do_test where-1.1.1 { |
| 66 | count {SELECT x, y, w FROM t1 WHERE w=10} |
| 67 | } {3 121 10 3} |
drh | e0cc3c2 | 2015-05-13 17:54:08 +0000 | [diff] [blame] | 68 | do_test where-1.1.1b { |
| 69 | count {SELECT x, y, w FROM t1 WHERE w IS 10} |
| 70 | } {3 121 10 3} |
drh | 6fa978d | 2013-05-30 19:29:19 +0000 | [diff] [blame] | 71 | do_eqp_test where-1.1.2 { |
| 72 | SELECT x, y, w FROM t1 WHERE w=10 |
drh | 8a4380d | 2013-06-11 02:32:50 +0000 | [diff] [blame] | 73 | } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} |
drh | e0cc3c2 | 2015-05-13 17:54:08 +0000 | [diff] [blame] | 74 | do_eqp_test where-1.1.2b { |
| 75 | SELECT x, y, w FROM t1 WHERE w IS 10 |
| 76 | } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} |
drh | 4f07e5f | 2007-05-14 11:34:46 +0000 | [diff] [blame] | 77 | do_test where-1.1.3 { |
drh | d1d3848 | 2008-10-07 23:46:38 +0000 | [diff] [blame] | 78 | db status step |
| 79 | } {0} |
| 80 | do_test where-1.1.4 { |
| 81 | db eval {SELECT x, y, w FROM t1 WHERE +w=10} |
| 82 | } {3 121 10} |
| 83 | do_test where-1.1.5 { |
| 84 | db status step |
| 85 | } {99} |
drh | 6fa978d | 2013-05-30 19:29:19 +0000 | [diff] [blame] | 86 | do_eqp_test where-1.1.6 { |
| 87 | SELECT x, y, w FROM t1 WHERE +w=10 |
drh | 8a4380d | 2013-06-11 02:32:50 +0000 | [diff] [blame] | 88 | } {*SCAN TABLE t1*} |
drh | d1d3848 | 2008-10-07 23:46:38 +0000 | [diff] [blame] | 89 | do_test where-1.1.7 { |
drh | 4f07e5f | 2007-05-14 11:34:46 +0000 | [diff] [blame] | 90 | count {SELECT x, y, w AS abc FROM t1 WHERE abc=10} |
| 91 | } {3 121 10 3} |
drh | 6fa978d | 2013-05-30 19:29:19 +0000 | [diff] [blame] | 92 | do_eqp_test where-1.1.8 { |
| 93 | SELECT x, y, w AS abc FROM t1 WHERE abc=10 |
drh | 8a4380d | 2013-06-11 02:32:50 +0000 | [diff] [blame] | 94 | } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} |
drh | d1d3848 | 2008-10-07 23:46:38 +0000 | [diff] [blame] | 95 | do_test where-1.1.9 { |
| 96 | db status step |
| 97 | } {0} |
drh | 4f07e5f | 2007-05-14 11:34:46 +0000 | [diff] [blame] | 98 | do_test where-1.2.1 { |
| 99 | count {SELECT x, y, w FROM t1 WHERE w=11} |
| 100 | } {3 144 11 3} |
| 101 | do_test where-1.2.2 { |
| 102 | count {SELECT x, y, w AS abc FROM t1 WHERE abc=11} |
| 103 | } {3 144 11 3} |
| 104 | do_test where-1.3.1 { |
| 105 | count {SELECT x, y, w AS abc FROM t1 WHERE 11=w} |
| 106 | } {3 144 11 3} |
| 107 | do_test where-1.3.2 { |
| 108 | count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc} |
| 109 | } {3 144 11 3} |
drh | e0cc3c2 | 2015-05-13 17:54:08 +0000 | [diff] [blame] | 110 | do_test where-1.3.3 { |
| 111 | count {SELECT x, y, w AS abc FROM t1 WHERE 11 IS abc} |
| 112 | } {3 144 11 3} |
drh | 4f07e5f | 2007-05-14 11:34:46 +0000 | [diff] [blame] | 113 | do_test where-1.4.1 { |
| 114 | count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2} |
| 115 | } {11 3 144 3} |
drh | e0cc3c2 | 2015-05-13 17:54:08 +0000 | [diff] [blame] | 116 | do_test where-1.4.1b { |
| 117 | count {SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2} |
| 118 | } {11 3 144 3} |
drh | 6fa978d | 2013-05-30 19:29:19 +0000 | [diff] [blame] | 119 | do_eqp_test where-1.4.2 { |
| 120 | SELECT w, x, y FROM t1 WHERE 11=w AND x>2 |
drh | 8a4380d | 2013-06-11 02:32:50 +0000 | [diff] [blame] | 121 | } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} |
drh | e0cc3c2 | 2015-05-13 17:54:08 +0000 | [diff] [blame] | 122 | do_eqp_test where-1.4.2b { |
| 123 | SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2 |
| 124 | } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} |
drh | 4f07e5f | 2007-05-14 11:34:46 +0000 | [diff] [blame] | 125 | do_test where-1.4.3 { |
| 126 | count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2} |
| 127 | } {11 3 144 3} |
drh | 6fa978d | 2013-05-30 19:29:19 +0000 | [diff] [blame] | 128 | do_eqp_test where-1.4.4 { |
| 129 | SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2 |
drh | 8a4380d | 2013-06-11 02:32:50 +0000 | [diff] [blame] | 130 | } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 131 | do_test where-1.5 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 132 | count {SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2} |
| 133 | } {3 144 3} |
drh | 6fa978d | 2013-05-30 19:29:19 +0000 | [diff] [blame] | 134 | do_eqp_test where-1.5.2 { |
| 135 | SELECT x, y FROM t1 WHERE y<200 AND w=11 AND x>2 |
drh | 8a4380d | 2013-06-11 02:32:50 +0000 | [diff] [blame] | 136 | } {*SEARCH TABLE t1 USING INDEX i1w (w=?)*} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 137 | do_test where-1.6 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 138 | count {SELECT x, y FROM t1 WHERE y<200 AND x>2 AND w=11} |
| 139 | } {3 144 3} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 140 | do_test where-1.7 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 141 | count {SELECT x, y FROM t1 WHERE w=11 AND y<200 AND x>2} |
| 142 | } {3 144 3} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 143 | do_test where-1.8 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 144 | count {SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3} |
| 145 | } {3 144 3} |
drh | 6fa978d | 2013-05-30 19:29:19 +0000 | [diff] [blame] | 146 | do_eqp_test where-1.8.2 { |
| 147 | SELECT x, y FROM t1 WHERE w>10 AND y=144 AND x=3 |
drh | 8a4380d | 2013-06-11 02:32:50 +0000 | [diff] [blame] | 148 | } {*SEARCH TABLE t1 USING INDEX i1xy (x=? AND y=?)*} |
drh | 6fa978d | 2013-05-30 19:29:19 +0000 | [diff] [blame] | 149 | do_eqp_test where-1.8.3 { |
| 150 | SELECT x, y FROM t1 WHERE y=144 AND x=3 |
drh | 8a4380d | 2013-06-11 02:32:50 +0000 | [diff] [blame] | 151 | } {*SEARCH TABLE t1 USING COVERING INDEX i1xy (x=? AND y=?)*} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 152 | do_test where-1.9 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 153 | count {SELECT x, y FROM t1 WHERE y=144 AND w>10 AND x=3} |
| 154 | } {3 144 3} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 155 | do_test where-1.10 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 156 | count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121} |
| 157 | } {3 121 3} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 158 | do_test where-1.11 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 159 | count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10} |
| 160 | } {3 100 3} |
drh | e0cc3c2 | 2015-05-13 17:54:08 +0000 | [diff] [blame] | 161 | do_test where-1.11b { |
| 162 | count {SELECT x, y FROM t1 WHERE x IS 3 AND y IS 100 AND w<10} |
| 163 | } {3 100 3} |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 164 | |
| 165 | # New for SQLite version 2.1: Verify that that inequality constraints |
| 166 | # are used correctly. |
| 167 | # |
| 168 | do_test where-1.12 { |
| 169 | count {SELECT w FROM t1 WHERE x=3 AND y<100} |
| 170 | } {8 3} |
drh | e0cc3c2 | 2015-05-13 17:54:08 +0000 | [diff] [blame] | 171 | do_test where-1.12b { |
| 172 | count {SELECT w FROM t1 WHERE x IS 3 AND y<100} |
| 173 | } {8 3} |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 174 | do_test where-1.13 { |
| 175 | count {SELECT w FROM t1 WHERE x=3 AND 100>y} |
| 176 | } {8 3} |
| 177 | do_test where-1.14 { |
| 178 | count {SELECT w FROM t1 WHERE 3=x AND y<100} |
| 179 | } {8 3} |
drh | e0cc3c2 | 2015-05-13 17:54:08 +0000 | [diff] [blame] | 180 | do_test where-1.14b { |
| 181 | count {SELECT w FROM t1 WHERE 3 IS x AND y<100} |
| 182 | } {8 3} |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 183 | do_test where-1.15 { |
| 184 | count {SELECT w FROM t1 WHERE 3=x AND 100>y} |
| 185 | } {8 3} |
| 186 | do_test where-1.16 { |
| 187 | count {SELECT w FROM t1 WHERE x=3 AND y<=100} |
| 188 | } {8 9 5} |
| 189 | do_test where-1.17 { |
| 190 | count {SELECT w FROM t1 WHERE x=3 AND 100>=y} |
| 191 | } {8 9 5} |
| 192 | do_test where-1.18 { |
| 193 | count {SELECT w FROM t1 WHERE x=3 AND y>225} |
| 194 | } {15 3} |
drh | e0cc3c2 | 2015-05-13 17:54:08 +0000 | [diff] [blame] | 195 | do_test where-1.18b { |
| 196 | count {SELECT w FROM t1 WHERE x IS 3 AND y>225} |
| 197 | } {15 3} |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 198 | do_test where-1.19 { |
| 199 | count {SELECT w FROM t1 WHERE x=3 AND 225<y} |
| 200 | } {15 3} |
| 201 | do_test where-1.20 { |
| 202 | count {SELECT w FROM t1 WHERE x=3 AND y>=225} |
| 203 | } {14 15 5} |
| 204 | do_test where-1.21 { |
| 205 | count {SELECT w FROM t1 WHERE x=3 AND 225<=y} |
| 206 | } {14 15 5} |
| 207 | do_test where-1.22 { |
| 208 | count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196} |
| 209 | } {11 12 5} |
drh | e0cc3c2 | 2015-05-13 17:54:08 +0000 | [diff] [blame] | 210 | do_test where-1.22b { |
| 211 | count {SELECT w FROM t1 WHERE x IS 3 AND y>121 AND y<196} |
| 212 | } {11 12 5} |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 213 | do_test where-1.23 { |
| 214 | count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196} |
| 215 | } {10 11 12 13 9} |
| 216 | do_test where-1.24 { |
| 217 | count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y} |
| 218 | } {11 12 5} |
| 219 | do_test where-1.25 { |
| 220 | count {SELECT w FROM t1 WHERE x=3 AND 121<=y AND 196>=y} |
| 221 | } {10 11 12 13 9} |
| 222 | |
| 223 | # Need to work on optimizing the BETWEEN operator. |
| 224 | # |
| 225 | # do_test where-1.26 { |
| 226 | # count {SELECT w FROM t1 WHERE x=3 AND y BETWEEN 121 AND 196} |
| 227 | # } {10 11 12 13 9} |
| 228 | |
| 229 | do_test where-1.27 { |
| 230 | count {SELECT w FROM t1 WHERE x=3 AND y+1==122} |
danielk1977 | 21de2e7 | 2007-11-29 17:43:27 +0000 | [diff] [blame] | 231 | } {10 10} |
drh | 7cf6e4d | 2004-05-19 14:56:55 +0000 | [diff] [blame] | 232 | |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 233 | do_test where-1.28 { |
| 234 | count {SELECT w FROM t1 WHERE x+1=4 AND y+1==122} |
| 235 | } {10 99} |
| 236 | do_test where-1.29 { |
| 237 | count {SELECT w FROM t1 WHERE y==121} |
| 238 | } {10 99} |
| 239 | |
| 240 | |
| 241 | do_test where-1.30 { |
| 242 | count {SELECT w FROM t1 WHERE w>97} |
drh | 9012bcb | 2004-12-19 00:11:35 +0000 | [diff] [blame] | 243 | } {98 99 100 3} |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 244 | do_test where-1.31 { |
| 245 | count {SELECT w FROM t1 WHERE w>=97} |
drh | 9012bcb | 2004-12-19 00:11:35 +0000 | [diff] [blame] | 246 | } {97 98 99 100 4} |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 247 | do_test where-1.33 { |
| 248 | count {SELECT w FROM t1 WHERE w==97} |
drh | 9012bcb | 2004-12-19 00:11:35 +0000 | [diff] [blame] | 249 | } {97 2} |
drh | ac931eb | 2005-01-11 18:13:56 +0000 | [diff] [blame] | 250 | do_test where-1.33.1 { |
| 251 | count {SELECT w FROM t1 WHERE w<=97 AND w==97} |
| 252 | } {97 2} |
| 253 | do_test where-1.33.2 { |
| 254 | count {SELECT w FROM t1 WHERE w<98 AND w==97} |
| 255 | } {97 2} |
| 256 | do_test where-1.33.3 { |
| 257 | count {SELECT w FROM t1 WHERE w>=97 AND w==97} |
| 258 | } {97 2} |
| 259 | do_test where-1.33.4 { |
| 260 | count {SELECT w FROM t1 WHERE w>96 AND w==97} |
| 261 | } {97 2} |
| 262 | do_test where-1.33.5 { |
| 263 | count {SELECT w FROM t1 WHERE w==97 AND w==97} |
| 264 | } {97 2} |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 265 | do_test where-1.34 { |
| 266 | count {SELECT w FROM t1 WHERE w+1==98} |
| 267 | } {97 99} |
| 268 | do_test where-1.35 { |
| 269 | count {SELECT w FROM t1 WHERE w<3} |
drh | cfc6ca4 | 2014-02-14 23:49:13 +0000 | [diff] [blame] | 270 | } {1 2 3} |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 271 | do_test where-1.36 { |
| 272 | count {SELECT w FROM t1 WHERE w<=3} |
drh | cfc6ca4 | 2014-02-14 23:49:13 +0000 | [diff] [blame] | 273 | } {1 2 3 4} |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 274 | do_test where-1.37 { |
drh | 8aff101 | 2001-12-22 14:49:24 +0000 | [diff] [blame] | 275 | count {SELECT w FROM t1 WHERE w+1<=4 ORDER BY w} |
drh | 9012bcb | 2004-12-19 00:11:35 +0000 | [diff] [blame] | 276 | } {1 2 3 99} |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 277 | |
drh | 6977fea | 2002-10-22 23:38:04 +0000 | [diff] [blame] | 278 | do_test where-1.38 { |
| 279 | count {SELECT (w) FROM t1 WHERE (w)>(97)} |
drh | 9012bcb | 2004-12-19 00:11:35 +0000 | [diff] [blame] | 280 | } {98 99 100 3} |
drh | 6977fea | 2002-10-22 23:38:04 +0000 | [diff] [blame] | 281 | do_test where-1.39 { |
| 282 | count {SELECT (w) FROM t1 WHERE (w)>=(97)} |
drh | 9012bcb | 2004-12-19 00:11:35 +0000 | [diff] [blame] | 283 | } {97 98 99 100 4} |
drh | 6977fea | 2002-10-22 23:38:04 +0000 | [diff] [blame] | 284 | do_test where-1.40 { |
| 285 | count {SELECT (w) FROM t1 WHERE (w)==(97)} |
drh | 9012bcb | 2004-12-19 00:11:35 +0000 | [diff] [blame] | 286 | } {97 2} |
drh | 6977fea | 2002-10-22 23:38:04 +0000 | [diff] [blame] | 287 | do_test where-1.41 { |
| 288 | count {SELECT (w) FROM t1 WHERE ((w)+(1))==(98)} |
| 289 | } {97 99} |
| 290 | |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 291 | |
| 292 | # Do the same kind of thing except use a join as the data source. |
| 293 | # |
| 294 | do_test where-2.1 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 295 | count { |
| 296 | SELECT w, p FROM t2, t1 |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 297 | WHERE x=q AND y=s AND r=8977 |
| 298 | } |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 299 | } {34 67 6} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 300 | do_test where-2.2 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 301 | count { |
| 302 | SELECT w, p FROM t2, t1 |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 303 | WHERE x=q AND s=y AND r=8977 |
| 304 | } |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 305 | } {34 67 6} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 306 | do_test where-2.3 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 307 | count { |
| 308 | SELECT w, p FROM t2, t1 |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 309 | WHERE x=q AND s=y AND r=8977 AND w>10 |
| 310 | } |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 311 | } {34 67 6} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 312 | do_test where-2.4 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 313 | count { |
| 314 | SELECT w, p FROM t2, t1 |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 315 | WHERE p<80 AND x=q AND s=y AND r=8977 AND w>10 |
| 316 | } |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 317 | } {34 67 6} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 318 | do_test where-2.5 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 319 | count { |
| 320 | SELECT w, p FROM t2, t1 |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 321 | WHERE p<80 AND x=q AND 8977=r AND s=y AND w>10 |
| 322 | } |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 323 | } {34 67 6} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 324 | do_test where-2.6 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 325 | count { |
| 326 | SELECT w, p FROM t2, t1 |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 327 | WHERE x=q AND p=77 AND s=y AND w>5 |
| 328 | } |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 329 | } {24 77 6} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 330 | do_test where-2.7 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 331 | count { |
| 332 | SELECT w, p FROM t1, t2 |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 333 | WHERE x=q AND p>77 AND s=y AND w=5 |
| 334 | } |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 335 | } {5 96 6} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 336 | |
| 337 | # Lets do a 3-way join. |
| 338 | # |
| 339 | do_test where-3.1 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 340 | count { |
| 341 | SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 342 | WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=11 |
| 343 | } |
drh | 9012bcb | 2004-12-19 00:11:35 +0000 | [diff] [blame] | 344 | } {11 90 11 8} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 345 | do_test where-3.2 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 346 | count { |
| 347 | SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 348 | WHERE C.w=101-B.p AND B.r=10202-A.y AND A.w=12 |
| 349 | } |
drh | 9012bcb | 2004-12-19 00:11:35 +0000 | [diff] [blame] | 350 | } {12 89 12 8} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 351 | do_test where-3.3 { |
drh | 487ab3c | 2001-11-08 00:45:21 +0000 | [diff] [blame] | 352 | count { |
| 353 | SELECT A.w, B.p, C.w FROM t1 as A, t2 as B, t1 as C |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 354 | WHERE A.w=15 AND B.p=C.w AND B.r=10202-A.y |
| 355 | } |
drh | 9012bcb | 2004-12-19 00:11:35 +0000 | [diff] [blame] | 356 | } {15 86 86 8} |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 357 | |
drh | 08192d5 | 2002-04-30 19:20:28 +0000 | [diff] [blame] | 358 | # Test to see that the special case of a constant WHERE clause is |
| 359 | # handled. |
| 360 | # |
| 361 | do_test where-4.1 { |
| 362 | count { |
| 363 | SELECT * FROM t1 WHERE 0 |
| 364 | } |
| 365 | } {0} |
| 366 | do_test where-4.2 { |
| 367 | count { |
| 368 | SELECT * FROM t1 WHERE 1 LIMIT 1 |
| 369 | } |
drh | ec7429a | 2005-10-06 16:53:14 +0000 | [diff] [blame] | 370 | } {1 0 4 0} |
drh | 08192d5 | 2002-04-30 19:20:28 +0000 | [diff] [blame] | 371 | do_test where-4.3 { |
| 372 | execsql { |
| 373 | SELECT 99 WHERE 0 |
| 374 | } |
| 375 | } {} |
| 376 | do_test where-4.4 { |
| 377 | execsql { |
| 378 | SELECT 99 WHERE 1 |
| 379 | } |
| 380 | } {99} |
drh | ba0232a | 2005-06-06 17:27:19 +0000 | [diff] [blame] | 381 | do_test where-4.5 { |
| 382 | execsql { |
| 383 | SELECT 99 WHERE 0.1 |
| 384 | } |
| 385 | } {99} |
| 386 | do_test where-4.6 { |
| 387 | execsql { |
| 388 | SELECT 99 WHERE 0.0 |
| 389 | } |
| 390 | } {} |
drh | 85e9e22 | 2008-07-15 00:27:34 +0000 | [diff] [blame] | 391 | do_test where-4.7 { |
| 392 | execsql { |
| 393 | SELECT count(*) FROM t1 WHERE t1.w |
| 394 | } |
| 395 | } {100} |
drh | 08192d5 | 2002-04-30 19:20:28 +0000 | [diff] [blame] | 396 | |
drh | 48185c1 | 2002-06-09 01:55:20 +0000 | [diff] [blame] | 397 | # Verify that IN operators in a WHERE clause are handled correctly. |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 398 | # Omit these tests if the build is not capable of sub-queries. |
drh | 48185c1 | 2002-06-09 01:55:20 +0000 | [diff] [blame] | 399 | # |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 400 | ifcapable subquery { |
| 401 | do_test where-5.1 { |
| 402 | count { |
| 403 | SELECT * FROM t1 WHERE rowid IN (1,2,3,1234) order by 1; |
| 404 | } |
drh | 4db38a7 | 2005-09-01 12:16:28 +0000 | [diff] [blame] | 405 | } {1 0 4 2 1 9 3 1 16 4} |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 406 | do_test where-5.2 { |
| 407 | count { |
| 408 | SELECT * FROM t1 WHERE rowid+0 IN (1,2,3,1234) order by 1; |
| 409 | } |
danielk1977 | 21de2e7 | 2007-11-29 17:43:27 +0000 | [diff] [blame] | 410 | } {1 0 4 2 1 9 3 1 16 102} |
drh | 1b8fc65 | 2013-02-07 21:15:14 +0000 | [diff] [blame] | 411 | do_test where-5.3a { |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 412 | count { |
| 413 | SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1; |
| 414 | } |
drh | b1d607d | 2015-11-05 22:30:54 +0000 | [diff] [blame] | 415 | } {1 0 4 2 1 9 3 1 16 12} |
drh | 1b8fc65 | 2013-02-07 21:15:14 +0000 | [diff] [blame] | 416 | do_test where-5.3b { |
| 417 | count { |
| 418 | SELECT * FROM t1 WHERE w IN (3,-1,1,2) order by 1; |
| 419 | } |
drh | b1d607d | 2015-11-05 22:30:54 +0000 | [diff] [blame] | 420 | } {1 0 4 2 1 9 3 1 16 12} |
drh | 1b8fc65 | 2013-02-07 21:15:14 +0000 | [diff] [blame] | 421 | do_test where-5.3c { |
| 422 | count { |
| 423 | SELECT * FROM t1 WHERE w IN (3,2,-1,1,2) order by 1; |
| 424 | } |
drh | b1d607d | 2015-11-05 22:30:54 +0000 | [diff] [blame] | 425 | } {1 0 4 2 1 9 3 1 16 12} |
drh | 1b8fc65 | 2013-02-07 21:15:14 +0000 | [diff] [blame] | 426 | do_test where-5.3d { |
| 427 | count { |
| 428 | SELECT * FROM t1 WHERE w IN (-1,1,2,3) order by 1 DESC; |
| 429 | } |
drh | b1d607d | 2015-11-05 22:30:54 +0000 | [diff] [blame] | 430 | } {3 1 16 2 1 9 1 0 4 11} |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 431 | do_test where-5.4 { |
| 432 | count { |
| 433 | SELECT * FROM t1 WHERE w+0 IN (-1,1,2,3) order by 1; |
| 434 | } |
danielk1977 | 21de2e7 | 2007-11-29 17:43:27 +0000 | [diff] [blame] | 435 | } {1 0 4 2 1 9 3 1 16 102} |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 436 | do_test where-5.5 { |
| 437 | count { |
| 438 | SELECT * FROM t1 WHERE rowid IN |
| 439 | (select rowid from t1 where rowid IN (-1,2,4)) |
| 440 | ORDER BY 1; |
| 441 | } |
| 442 | } {2 1 9 4 2 25 3} |
| 443 | do_test where-5.6 { |
| 444 | count { |
| 445 | SELECT * FROM t1 WHERE rowid+0 IN |
| 446 | (select rowid from t1 where rowid IN (-1,2,4)) |
| 447 | ORDER BY 1; |
| 448 | } |
danielk1977 | 21de2e7 | 2007-11-29 17:43:27 +0000 | [diff] [blame] | 449 | } {2 1 9 4 2 25 103} |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 450 | do_test where-5.7 { |
| 451 | count { |
| 452 | SELECT * FROM t1 WHERE w IN |
| 453 | (select rowid from t1 where rowid IN (-1,2,4)) |
| 454 | ORDER BY 1; |
| 455 | } |
| 456 | } {2 1 9 4 2 25 9} |
| 457 | do_test where-5.8 { |
| 458 | count { |
| 459 | SELECT * FROM t1 WHERE w+0 IN |
| 460 | (select rowid from t1 where rowid IN (-1,2,4)) |
| 461 | ORDER BY 1; |
| 462 | } |
danielk1977 | 21de2e7 | 2007-11-29 17:43:27 +0000 | [diff] [blame] | 463 | } {2 1 9 4 2 25 103} |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 464 | do_test where-5.9 { |
| 465 | count { |
| 466 | SELECT * FROM t1 WHERE x IN (1,7) ORDER BY 1; |
| 467 | } |
drh | b1d607d | 2015-11-05 22:30:54 +0000 | [diff] [blame] | 468 | } {2 1 9 3 1 16 6} |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 469 | do_test where-5.10 { |
| 470 | count { |
| 471 | SELECT * FROM t1 WHERE x+0 IN (1,7) ORDER BY 1; |
| 472 | } |
| 473 | } {2 1 9 3 1 16 199} |
| 474 | do_test where-5.11 { |
| 475 | count { |
| 476 | SELECT * FROM t1 WHERE y IN (6400,8100) ORDER BY 1; |
| 477 | } |
| 478 | } {79 6 6400 89 6 8100 199} |
| 479 | do_test where-5.12 { |
| 480 | count { |
| 481 | SELECT * FROM t1 WHERE x=6 AND y IN (6400,8100) ORDER BY 1; |
| 482 | } |
drh | 3adc9ce | 2005-07-28 16:51:51 +0000 | [diff] [blame] | 483 | } {79 6 6400 89 6 8100 7} |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 484 | do_test where-5.13 { |
| 485 | count { |
| 486 | SELECT * FROM t1 WHERE x IN (1,7) AND y NOT IN (6400,8100) ORDER BY 1; |
| 487 | } |
drh | b1d607d | 2015-11-05 22:30:54 +0000 | [diff] [blame] | 488 | } {2 1 9 3 1 16 6} |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 489 | do_test where-5.14 { |
| 490 | count { |
| 491 | SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,10) ORDER BY 1; |
| 492 | } |
drh | fa17e13 | 2020-09-01 01:52:03 +0000 | [diff] [blame] | 493 | } {2 1 9 5} |
drh | 3adc9ce | 2005-07-28 16:51:51 +0000 | [diff] [blame] | 494 | do_test where-5.15 { |
| 495 | count { |
| 496 | SELECT * FROM t1 WHERE x IN (1,7) AND y IN (9,16) ORDER BY 1; |
| 497 | } |
drh | fa17e13 | 2020-09-01 01:52:03 +0000 | [diff] [blame] | 498 | } {2 1 9 3 1 16 9} |
drh | 2d96b93 | 2013-02-08 18:48:23 +0000 | [diff] [blame] | 499 | do_test where-5.100 { |
| 500 | db eval { |
| 501 | SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969) |
| 502 | ORDER BY x, y |
| 503 | } |
| 504 | } {2 1 9 54 5 3025 62 5 3969} |
| 505 | do_test where-5.101 { |
| 506 | db eval { |
| 507 | SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969) |
| 508 | ORDER BY x DESC, y DESC |
| 509 | } |
| 510 | } {62 5 3969 54 5 3025 2 1 9} |
| 511 | do_test where-5.102 { |
| 512 | db eval { |
| 513 | SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969) |
| 514 | ORDER BY x DESC, y |
| 515 | } |
| 516 | } {54 5 3025 62 5 3969 2 1 9} |
| 517 | do_test where-5.103 { |
| 518 | db eval { |
| 519 | SELECT w, x, y FROM t1 WHERE x IN (1,5) AND y IN (9,8,3025,1000,3969) |
| 520 | ORDER BY x, y DESC |
| 521 | } |
| 522 | } {2 1 9 62 5 3969 54 5 3025} |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 523 | } |
drh | 48185c1 | 2002-06-09 01:55:20 +0000 | [diff] [blame] | 524 | |
drh | 6bf8957 | 2004-11-03 16:27:01 +0000 | [diff] [blame] | 525 | # This procedure executes the SQL. Then it checks to see if the OP_Sort |
| 526 | # opcode was executed. If an OP_Sort did occur, then "sort" is appended |
| 527 | # to the result. If no OP_Sort happened, then "nosort" is appended. |
| 528 | # |
| 529 | # This procedure is used to check to make sure sorting is or is not |
| 530 | # occurring as expected. |
drh | e318474 | 2002-06-19 14:27:05 +0000 | [diff] [blame] | 531 | # |
| 532 | proc cksort {sql} { |
| 533 | set data [execsql $sql] |
drh | d1d3848 | 2008-10-07 23:46:38 +0000 | [diff] [blame] | 534 | if {[db status sort]} {set x sort} {set x nosort} |
drh | e318474 | 2002-06-19 14:27:05 +0000 | [diff] [blame] | 535 | lappend data $x |
| 536 | return $data |
| 537 | } |
| 538 | # Check out the logic that attempts to implement the ORDER BY clause |
| 539 | # using an index rather than by sorting. |
| 540 | # |
| 541 | do_test where-6.1 { |
| 542 | execsql { |
| 543 | CREATE TABLE t3(a,b,c); |
| 544 | CREATE INDEX t3a ON t3(a); |
| 545 | CREATE INDEX t3bc ON t3(b,c); |
| 546 | CREATE INDEX t3acb ON t3(a,c,b); |
| 547 | INSERT INTO t3 SELECT w, 101-w, y FROM t1; |
| 548 | SELECT count(*), sum(a), sum(b), sum(c) FROM t3; |
| 549 | } |
drh | 3d1d95e | 2005-09-08 10:37:01 +0000 | [diff] [blame] | 550 | } {100 5050 5050 348550} |
drh | e318474 | 2002-06-19 14:27:05 +0000 | [diff] [blame] | 551 | do_test where-6.2 { |
| 552 | cksort { |
| 553 | SELECT * FROM t3 ORDER BY a LIMIT 3 |
| 554 | } |
| 555 | } {1 100 4 2 99 9 3 98 16 nosort} |
| 556 | do_test where-6.3 { |
| 557 | cksort { |
| 558 | SELECT * FROM t3 ORDER BY a+1 LIMIT 3 |
| 559 | } |
| 560 | } {1 100 4 2 99 9 3 98 16 sort} |
| 561 | do_test where-6.4 { |
| 562 | cksort { |
| 563 | SELECT * FROM t3 WHERE a<10 ORDER BY a LIMIT 3 |
| 564 | } |
| 565 | } {1 100 4 2 99 9 3 98 16 nosort} |
| 566 | do_test where-6.5 { |
| 567 | cksort { |
| 568 | SELECT * FROM t3 WHERE a>0 AND a<10 ORDER BY a LIMIT 3 |
| 569 | } |
| 570 | } {1 100 4 2 99 9 3 98 16 nosort} |
| 571 | do_test where-6.6 { |
| 572 | cksort { |
| 573 | SELECT * FROM t3 WHERE a>0 ORDER BY a LIMIT 3 |
| 574 | } |
| 575 | } {1 100 4 2 99 9 3 98 16 nosort} |
drh | 8c098e6 | 2016-02-25 23:21:41 +0000 | [diff] [blame] | 576 | do_test where-6.7.1 { |
drh | e318474 | 2002-06-19 14:27:05 +0000 | [diff] [blame] | 577 | cksort { |
drh | 8c098e6 | 2016-02-25 23:21:41 +0000 | [diff] [blame] | 578 | SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 10 |
drh | e318474 | 2002-06-19 14:27:05 +0000 | [diff] [blame] | 579 | } |
drh | 8c098e6 | 2016-02-25 23:21:41 +0000 | [diff] [blame] | 580 | } {/1 100 4 2 99 9 3 98 16 .* nosort/} |
| 581 | do_test where-6.7.2 { |
| 582 | cksort { |
| 583 | SELECT * FROM t3 WHERE b>0 ORDER BY a LIMIT 1 |
| 584 | } |
drh | f559ed3 | 2018-07-28 21:01:55 +0000 | [diff] [blame] | 585 | } {1 100 4 nosort} |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 586 | ifcapable subquery { |
drh | 1b8fc65 | 2013-02-07 21:15:14 +0000 | [diff] [blame] | 587 | do_test where-6.8a { |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 588 | cksort { |
| 589 | SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a LIMIT 3 |
| 590 | } |
drh | 1b8fc65 | 2013-02-07 21:15:14 +0000 | [diff] [blame] | 591 | } {1 100 4 2 99 9 3 98 16 nosort} |
| 592 | do_test where-6.8b { |
| 593 | cksort { |
| 594 | SELECT * FROM t3 WHERE a IN (3,5,7,1,9,4,2) ORDER BY a DESC LIMIT 3 |
| 595 | } |
drh | 2d96b93 | 2013-02-08 18:48:23 +0000 | [diff] [blame] | 596 | } {9 92 100 7 94 64 5 96 36 nosort} |
danielk1977 | 3e8c37e | 2005-01-21 03:12:14 +0000 | [diff] [blame] | 597 | } |
drh | dd4852c | 2002-12-04 21:50:16 +0000 | [diff] [blame] | 598 | do_test where-6.9.1 { |
drh | e318474 | 2002-06-19 14:27:05 +0000 | [diff] [blame] | 599 | cksort { |
| 600 | SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 |
| 601 | } |
| 602 | } {1 100 4 nosort} |
drh | ac931eb | 2005-01-11 18:13:56 +0000 | [diff] [blame] | 603 | do_test where-6.9.1.1 { |
| 604 | cksort { |
| 605 | SELECT * FROM t3 WHERE a>=1 AND a=1 AND c>0 ORDER BY a LIMIT 3 |
| 606 | } |
| 607 | } {1 100 4 nosort} |
| 608 | do_test where-6.9.1.2 { |
| 609 | cksort { |
| 610 | SELECT * FROM t3 WHERE a<2 AND a=1 AND c>0 ORDER BY a LIMIT 3 |
| 611 | } |
| 612 | } {1 100 4 nosort} |
drh | dd4852c | 2002-12-04 21:50:16 +0000 | [diff] [blame] | 613 | do_test where-6.9.2 { |
| 614 | cksort { |
| 615 | SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3 |
| 616 | } |
| 617 | } {1 100 4 nosort} |
| 618 | do_test where-6.9.3 { |
| 619 | cksort { |
| 620 | SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c LIMIT 3 |
| 621 | } |
| 622 | } {1 100 4 nosort} |
| 623 | do_test where-6.9.4 { |
| 624 | cksort { |
| 625 | SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC LIMIT 3 |
| 626 | } |
| 627 | } {1 100 4 nosort} |
| 628 | do_test where-6.9.5 { |
| 629 | cksort { |
| 630 | SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c DESC LIMIT 3 |
| 631 | } |
| 632 | } {1 100 4 nosort} |
| 633 | do_test where-6.9.6 { |
| 634 | cksort { |
| 635 | SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c DESC LIMIT 3 |
| 636 | } |
| 637 | } {1 100 4 nosort} |
| 638 | do_test where-6.9.7 { |
| 639 | cksort { |
| 640 | SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY c,a LIMIT 3 |
| 641 | } |
drh | 4fe425a | 2013-06-12 17:08:06 +0000 | [diff] [blame] | 642 | } {1 100 4 nosort} |
drh | dd4852c | 2002-12-04 21:50:16 +0000 | [diff] [blame] | 643 | do_test where-6.9.8 { |
| 644 | cksort { |
| 645 | SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a DESC, c ASC LIMIT 3 |
| 646 | } |
drh | 9012bcb | 2004-12-19 00:11:35 +0000 | [diff] [blame] | 647 | } {1 100 4 nosort} |
drh | dd4852c | 2002-12-04 21:50:16 +0000 | [diff] [blame] | 648 | do_test where-6.9.9 { |
| 649 | cksort { |
| 650 | SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a ASC, c DESC LIMIT 3 |
| 651 | } |
drh | 9012bcb | 2004-12-19 00:11:35 +0000 | [diff] [blame] | 652 | } {1 100 4 nosort} |
drh | e318474 | 2002-06-19 14:27:05 +0000 | [diff] [blame] | 653 | do_test where-6.10 { |
| 654 | cksort { |
| 655 | SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a LIMIT 3 |
| 656 | } |
| 657 | } {1 100 4 nosort} |
| 658 | do_test where-6.11 { |
| 659 | cksort { |
| 660 | SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c LIMIT 3 |
| 661 | } |
| 662 | } {1 100 4 nosort} |
| 663 | do_test where-6.12 { |
| 664 | cksort { |
| 665 | SELECT * FROM t3 WHERE a=1 AND c>0 ORDER BY a,c,b LIMIT 3 |
| 666 | } |
| 667 | } {1 100 4 nosort} |
| 668 | do_test where-6.13 { |
| 669 | cksort { |
| 670 | SELECT * FROM t3 WHERE a>0 ORDER BY a DESC LIMIT 3 |
| 671 | } |
drh | dd4852c | 2002-12-04 21:50:16 +0000 | [diff] [blame] | 672 | } {100 1 10201 99 2 10000 98 3 9801 nosort} |
| 673 | do_test where-6.13.1 { |
| 674 | cksort { |
| 675 | SELECT * FROM t3 WHERE a>0 ORDER BY -a LIMIT 3 |
| 676 | } |
drh | e318474 | 2002-06-19 14:27:05 +0000 | [diff] [blame] | 677 | } {100 1 10201 99 2 10000 98 3 9801 sort} |
| 678 | do_test where-6.14 { |
| 679 | cksort { |
| 680 | SELECT * FROM t3 ORDER BY b LIMIT 3 |
| 681 | } |
| 682 | } {100 1 10201 99 2 10000 98 3 9801 nosort} |
| 683 | do_test where-6.15 { |
| 684 | cksort { |
| 685 | SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t3.a LIMIT 3 |
| 686 | } |
| 687 | } {1 0 2 1 3 1 nosort} |
| 688 | do_test where-6.16 { |
| 689 | cksort { |
| 690 | SELECT t3.a, t1.x FROM t3, t1 WHERE t3.a=t1.w ORDER BY t1.x, t3.a LIMIT 3 |
| 691 | } |
| 692 | } {1 0 2 1 3 1 sort} |
drh | c330af1 | 2002-08-14 03:03:57 +0000 | [diff] [blame] | 693 | do_test where-6.19 { |
| 694 | cksort { |
| 695 | SELECT y FROM t1 ORDER BY w LIMIT 3; |
| 696 | } |
| 697 | } {4 9 16 nosort} |
drh | b6c2989 | 2004-11-22 19:12:19 +0000 | [diff] [blame] | 698 | do_test where-6.20 { |
| 699 | cksort { |
| 700 | SELECT y FROM t1 ORDER BY rowid LIMIT 3; |
| 701 | } |
| 702 | } {4 9 16 nosort} |
| 703 | do_test where-6.21 { |
| 704 | cksort { |
| 705 | SELECT y FROM t1 ORDER BY rowid, y LIMIT 3; |
| 706 | } |
drh | cc19254 | 2006-12-20 03:24:19 +0000 | [diff] [blame] | 707 | } {4 9 16 nosort} |
drh | b6c2989 | 2004-11-22 19:12:19 +0000 | [diff] [blame] | 708 | do_test where-6.22 { |
| 709 | cksort { |
| 710 | SELECT y FROM t1 ORDER BY rowid, y DESC LIMIT 3; |
| 711 | } |
drh | cc19254 | 2006-12-20 03:24:19 +0000 | [diff] [blame] | 712 | } {4 9 16 nosort} |
drh | b6c2989 | 2004-11-22 19:12:19 +0000 | [diff] [blame] | 713 | do_test where-6.23 { |
| 714 | cksort { |
| 715 | SELECT y FROM t1 WHERE y>4 ORDER BY rowid, w, x LIMIT 3; |
| 716 | } |
drh | cc19254 | 2006-12-20 03:24:19 +0000 | [diff] [blame] | 717 | } {9 16 25 nosort} |
drh | b6c2989 | 2004-11-22 19:12:19 +0000 | [diff] [blame] | 718 | do_test where-6.24 { |
| 719 | cksort { |
| 720 | SELECT y FROM t1 WHERE y>=9 ORDER BY rowid, x DESC, w LIMIT 3; |
| 721 | } |
drh | cc19254 | 2006-12-20 03:24:19 +0000 | [diff] [blame] | 722 | } {9 16 25 nosort} |
drh | b6c2989 | 2004-11-22 19:12:19 +0000 | [diff] [blame] | 723 | do_test where-6.25 { |
| 724 | cksort { |
| 725 | SELECT y FROM t1 WHERE y>4 AND y<25 ORDER BY rowid; |
| 726 | } |
| 727 | } {9 16 nosort} |
| 728 | do_test where-6.26 { |
| 729 | cksort { |
| 730 | SELECT y FROM t1 WHERE y>=4 AND y<=25 ORDER BY oid; |
| 731 | } |
| 732 | } {4 9 16 25 nosort} |
| 733 | do_test where-6.27 { |
| 734 | cksort { |
| 735 | SELECT y FROM t1 WHERE y<=25 ORDER BY _rowid_, w+y; |
| 736 | } |
drh | cc19254 | 2006-12-20 03:24:19 +0000 | [diff] [blame] | 737 | } {4 9 16 25 nosort} |
drh | b6c2989 | 2004-11-22 19:12:19 +0000 | [diff] [blame] | 738 | |
drh | c330af1 | 2002-08-14 03:03:57 +0000 | [diff] [blame] | 739 | |
drh | dd4852c | 2002-12-04 21:50:16 +0000 | [diff] [blame] | 740 | # Tests for reverse-order sorting. |
| 741 | # |
| 742 | do_test where-7.1 { |
| 743 | cksort { |
| 744 | SELECT w FROM t1 WHERE x=3 ORDER BY y; |
| 745 | } |
| 746 | } {8 9 10 11 12 13 14 15 nosort} |
| 747 | do_test where-7.2 { |
| 748 | cksort { |
| 749 | SELECT w FROM t1 WHERE x=3 ORDER BY y DESC; |
| 750 | } |
| 751 | } {15 14 13 12 11 10 9 8 nosort} |
| 752 | do_test where-7.3 { |
| 753 | cksort { |
| 754 | SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y LIMIT 3; |
| 755 | } |
| 756 | } {10 11 12 nosort} |
| 757 | do_test where-7.4 { |
| 758 | cksort { |
| 759 | SELECT w FROM t1 WHERE x=3 AND y>100 ORDER BY y DESC LIMIT 3; |
| 760 | } |
| 761 | } {15 14 13 nosort} |
| 762 | do_test where-7.5 { |
| 763 | cksort { |
| 764 | SELECT w FROM t1 WHERE x=3 AND y>121 ORDER BY y DESC; |
| 765 | } |
| 766 | } {15 14 13 12 11 nosort} |
| 767 | do_test where-7.6 { |
| 768 | cksort { |
| 769 | SELECT w FROM t1 WHERE x=3 AND y>=121 ORDER BY y DESC; |
| 770 | } |
| 771 | } {15 14 13 12 11 10 nosort} |
| 772 | do_test where-7.7 { |
| 773 | cksort { |
| 774 | SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y DESC; |
| 775 | } |
| 776 | } {12 11 10 nosort} |
| 777 | do_test where-7.8 { |
| 778 | cksort { |
| 779 | SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y DESC; |
| 780 | } |
| 781 | } {13 12 11 10 nosort} |
| 782 | do_test where-7.9 { |
| 783 | cksort { |
| 784 | SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y DESC; |
| 785 | } |
| 786 | } {13 12 11 nosort} |
| 787 | do_test where-7.10 { |
| 788 | cksort { |
| 789 | SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y DESC; |
| 790 | } |
| 791 | } {12 11 10 nosort} |
| 792 | do_test where-7.11 { |
| 793 | cksort { |
| 794 | SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<196 ORDER BY y; |
| 795 | } |
| 796 | } {10 11 12 nosort} |
| 797 | do_test where-7.12 { |
| 798 | cksort { |
| 799 | SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196 ORDER BY y; |
| 800 | } |
| 801 | } {10 11 12 13 nosort} |
| 802 | do_test where-7.13 { |
| 803 | cksort { |
| 804 | SELECT w FROM t1 WHERE x=3 AND y>121 AND y<=196 ORDER BY y; |
| 805 | } |
| 806 | } {11 12 13 nosort} |
| 807 | do_test where-7.14 { |
| 808 | cksort { |
| 809 | SELECT w FROM t1 WHERE x=3 AND y>100 AND y<196 ORDER BY y; |
| 810 | } |
| 811 | } {10 11 12 nosort} |
drh | 1a844c3 | 2002-12-04 22:29:28 +0000 | [diff] [blame] | 812 | do_test where-7.15 { |
| 813 | cksort { |
| 814 | SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y; |
| 815 | } |
| 816 | } {nosort} |
| 817 | do_test where-7.16 { |
| 818 | cksort { |
| 819 | SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y; |
| 820 | } |
| 821 | } {8 nosort} |
| 822 | do_test where-7.17 { |
| 823 | cksort { |
| 824 | SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y; |
| 825 | } |
| 826 | } {nosort} |
| 827 | do_test where-7.18 { |
| 828 | cksort { |
| 829 | SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y; |
| 830 | } |
| 831 | } {15 nosort} |
| 832 | do_test where-7.19 { |
| 833 | cksort { |
| 834 | SELECT w FROM t1 WHERE x=3 AND y<81 ORDER BY y DESC; |
| 835 | } |
| 836 | } {nosort} |
| 837 | do_test where-7.20 { |
| 838 | cksort { |
| 839 | SELECT w FROM t1 WHERE x=3 AND y<=81 ORDER BY y DESC; |
| 840 | } |
| 841 | } {8 nosort} |
| 842 | do_test where-7.21 { |
| 843 | cksort { |
| 844 | SELECT w FROM t1 WHERE x=3 AND y>256 ORDER BY y DESC; |
| 845 | } |
| 846 | } {nosort} |
| 847 | do_test where-7.22 { |
| 848 | cksort { |
| 849 | SELECT w FROM t1 WHERE x=3 AND y>=256 ORDER BY y DESC; |
| 850 | } |
| 851 | } {15 nosort} |
| 852 | do_test where-7.23 { |
| 853 | cksort { |
| 854 | SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y; |
| 855 | } |
| 856 | } {nosort} |
| 857 | do_test where-7.24 { |
| 858 | cksort { |
| 859 | SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y; |
| 860 | } |
| 861 | } {1 nosort} |
| 862 | do_test where-7.25 { |
| 863 | cksort { |
| 864 | SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y; |
| 865 | } |
| 866 | } {nosort} |
| 867 | do_test where-7.26 { |
| 868 | cksort { |
| 869 | SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y; |
| 870 | } |
| 871 | } {100 nosort} |
| 872 | do_test where-7.27 { |
| 873 | cksort { |
| 874 | SELECT w FROM t1 WHERE x=0 AND y<4 ORDER BY y DESC; |
| 875 | } |
| 876 | } {nosort} |
| 877 | do_test where-7.28 { |
| 878 | cksort { |
| 879 | SELECT w FROM t1 WHERE x=0 AND y<=4 ORDER BY y DESC; |
| 880 | } |
| 881 | } {1 nosort} |
| 882 | do_test where-7.29 { |
| 883 | cksort { |
| 884 | SELECT w FROM t1 WHERE x=6 AND y>10201 ORDER BY y DESC; |
| 885 | } |
| 886 | } {nosort} |
| 887 | do_test where-7.30 { |
| 888 | cksort { |
| 889 | SELECT w FROM t1 WHERE x=6 AND y>=10201 ORDER BY y DESC; |
| 890 | } |
| 891 | } {100 nosort} |
drh | b6c2989 | 2004-11-22 19:12:19 +0000 | [diff] [blame] | 892 | do_test where-7.31 { |
| 893 | cksort { |
| 894 | SELECT y FROM t1 ORDER BY rowid DESC LIMIT 3 |
| 895 | } |
| 896 | } {10201 10000 9801 nosort} |
| 897 | do_test where-7.32 { |
| 898 | cksort { |
drh | a21c849 | 2005-09-01 17:47:51 +0000 | [diff] [blame] | 899 | SELECT y FROM t1 WHERE y<25 ORDER BY rowid DESC |
drh | b6c2989 | 2004-11-22 19:12:19 +0000 | [diff] [blame] | 900 | } |
| 901 | } {16 9 4 nosort} |
| 902 | do_test where-7.33 { |
| 903 | cksort { |
drh | a21c849 | 2005-09-01 17:47:51 +0000 | [diff] [blame] | 904 | SELECT y FROM t1 WHERE y<=25 ORDER BY rowid DESC |
drh | b6c2989 | 2004-11-22 19:12:19 +0000 | [diff] [blame] | 905 | } |
| 906 | } {25 16 9 4 nosort} |
| 907 | do_test where-7.34 { |
| 908 | cksort { |
| 909 | SELECT y FROM t1 WHERE y<25 AND y>4 ORDER BY rowid DESC, y DESC |
| 910 | } |
drh | cc19254 | 2006-12-20 03:24:19 +0000 | [diff] [blame] | 911 | } {16 9 nosort} |
drh | b6c2989 | 2004-11-22 19:12:19 +0000 | [diff] [blame] | 912 | do_test where-7.35 { |
| 913 | cksort { |
| 914 | SELECT y FROM t1 WHERE y<25 AND y>=4 ORDER BY rowid DESC |
| 915 | } |
| 916 | } {16 9 4 nosort} |
drh | 1a844c3 | 2002-12-04 22:29:28 +0000 | [diff] [blame] | 917 | |
| 918 | do_test where-8.1 { |
| 919 | execsql { |
| 920 | CREATE TABLE t4 AS SELECT * FROM t1; |
| 921 | CREATE INDEX i4xy ON t4(x,y); |
| 922 | } |
| 923 | cksort { |
| 924 | SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3; |
| 925 | } |
| 926 | } {30 29 28 nosort} |
| 927 | do_test where-8.2 { |
| 928 | execsql { |
| 929 | DELETE FROM t4; |
| 930 | } |
| 931 | cksort { |
| 932 | SELECT w FROM t4 WHERE x=4 and y<1000 ORDER BY y DESC limit 3; |
| 933 | } |
| 934 | } {nosort} |
| 935 | |
drh | d4d595f | 2003-04-17 12:44:23 +0000 | [diff] [blame] | 936 | # Make sure searches with an index work with an empty table. |
| 937 | # |
| 938 | do_test where-9.1 { |
| 939 | execsql { |
| 940 | CREATE TABLE t5(x PRIMARY KEY); |
| 941 | SELECT * FROM t5 WHERE x<10; |
| 942 | } |
| 943 | } {} |
| 944 | do_test where-9.2 { |
| 945 | execsql { |
| 946 | SELECT * FROM t5 WHERE x<10 ORDER BY x DESC; |
| 947 | } |
| 948 | } {} |
| 949 | do_test where-9.3 { |
| 950 | execsql { |
| 951 | SELECT * FROM t5 WHERE x=10; |
| 952 | } |
| 953 | } {} |
| 954 | |
drh | 37ea94b | 2003-04-19 16:34:04 +0000 | [diff] [blame] | 955 | do_test where-10.1 { |
| 956 | execsql { |
| 957 | SELECT 1 WHERE abs(random())<0 |
| 958 | } |
| 959 | } {} |
drh | 7bdc0c1 | 2003-04-19 17:27:24 +0000 | [diff] [blame] | 960 | do_test where-10.2 { |
| 961 | proc tclvar_func {vname} {return [set ::$vname]} |
| 962 | db function tclvar tclvar_func |
| 963 | set ::v1 0 |
| 964 | execsql { |
| 965 | SELECT count(*) FROM t1 WHERE tclvar('v1'); |
| 966 | } |
| 967 | } {0} |
| 968 | do_test where-10.3 { |
| 969 | set ::v1 1 |
| 970 | execsql { |
| 971 | SELECT count(*) FROM t1 WHERE tclvar('v1'); |
| 972 | } |
| 973 | } {100} |
| 974 | do_test where-10.4 { |
| 975 | set ::v1 1 |
| 976 | proc tclvar_func {vname} { |
| 977 | upvar #0 $vname v |
| 978 | set v [expr {!$v}] |
| 979 | return $v |
| 980 | } |
| 981 | execsql { |
| 982 | SELECT count(*) FROM t1 WHERE tclvar('v1'); |
| 983 | } |
| 984 | } {50} |
| 985 | |
drh | 9eb2028 | 2005-08-24 03:52:18 +0000 | [diff] [blame] | 986 | # Ticket #1376. The query below was causing a segfault. |
| 987 | # The problem was the age-old error of calling realloc() on an |
| 988 | # array while there are still pointers to individual elements of |
| 989 | # that array. |
| 990 | # |
| 991 | do_test where-11.1 { |
drh | 9eb2028 | 2005-08-24 03:52:18 +0000 | [diff] [blame] | 992 | execsql { |
| 993 | CREATE TABLE t99(Dte INT, X INT); |
| 994 | DELETE FROM t99 WHERE (Dte = 2451337) OR (Dte = 2451339) OR |
| 995 | (Dte BETWEEN 2451345 AND 2451347) OR (Dte = 2451351) OR |
| 996 | (Dte BETWEEN 2451355 AND 2451356) OR (Dte = 2451358) OR |
| 997 | (Dte = 2451362) OR (Dte = 2451365) OR (Dte = 2451367) OR |
| 998 | (Dte BETWEEN 2451372 AND 2451376) OR (Dte BETWEEN 2451382 AND 2451384) OR |
| 999 | (Dte = 2451387) OR (Dte BETWEEN 2451389 AND 2451391) OR |
| 1000 | (Dte BETWEEN 2451393 AND 2451395) OR (Dte = 2451400) OR |
| 1001 | (Dte = 2451402) OR (Dte = 2451404) OR (Dte BETWEEN 2451416 AND 2451418) OR |
| 1002 | (Dte = 2451422) OR (Dte = 2451426) OR (Dte BETWEEN 2451445 AND 2451446) OR |
| 1003 | (Dte = 2451456) OR (Dte = 2451458) OR (Dte BETWEEN 2451465 AND 2451467) OR |
| 1004 | (Dte BETWEEN 2451469 AND 2451471) OR (Dte = 2451474) OR |
| 1005 | (Dte BETWEEN 2451477 AND 2451501) OR (Dte BETWEEN 2451503 AND 2451509) OR |
| 1006 | (Dte BETWEEN 2451511 AND 2451514) OR (Dte BETWEEN 2451518 AND 2451521) OR |
| 1007 | (Dte BETWEEN 2451523 AND 2451531) OR (Dte BETWEEN 2451533 AND 2451537) OR |
| 1008 | (Dte BETWEEN 2451539 AND 2451544) OR (Dte BETWEEN 2451546 AND 2451551) OR |
| 1009 | (Dte BETWEEN 2451553 AND 2451555) OR (Dte = 2451557) OR |
| 1010 | (Dte BETWEEN 2451559 AND 2451561) OR (Dte = 2451563) OR |
| 1011 | (Dte BETWEEN 2451565 AND 2451566) OR (Dte BETWEEN 2451569 AND 2451571) OR |
| 1012 | (Dte = 2451573) OR (Dte = 2451575) OR (Dte = 2451577) OR (Dte = 2451581) OR |
| 1013 | (Dte BETWEEN 2451583 AND 2451586) OR (Dte BETWEEN 2451588 AND 2451592) OR |
| 1014 | (Dte BETWEEN 2451596 AND 2451598) OR (Dte = 2451600) OR |
| 1015 | (Dte BETWEEN 2451602 AND 2451603) OR (Dte = 2451606) OR (Dte = 2451611); |
| 1016 | } |
| 1017 | } {} |
| 1018 | |
drh | cc19254 | 2006-12-20 03:24:19 +0000 | [diff] [blame] | 1019 | # Ticket #2116: Make sure sorting by index works well with nn INTEGER PRIMARY |
| 1020 | # KEY. |
| 1021 | # |
| 1022 | do_test where-12.1 { |
| 1023 | execsql { |
| 1024 | CREATE TABLE t6(a INTEGER PRIMARY KEY, b TEXT); |
| 1025 | INSERT INTO t6 VALUES(1,'one'); |
| 1026 | INSERT INTO t6 VALUES(4,'four'); |
| 1027 | CREATE INDEX t6i1 ON t6(b); |
| 1028 | } |
| 1029 | cksort { |
| 1030 | SELECT * FROM t6 ORDER BY b; |
| 1031 | } |
| 1032 | } {4 four 1 one nosort} |
| 1033 | do_test where-12.2 { |
| 1034 | cksort { |
| 1035 | SELECT * FROM t6 ORDER BY b, a; |
| 1036 | } |
| 1037 | } {4 four 1 one nosort} |
| 1038 | do_test where-12.3 { |
| 1039 | cksort { |
| 1040 | SELECT * FROM t6 ORDER BY a; |
| 1041 | } |
| 1042 | } {1 one 4 four nosort} |
| 1043 | do_test where-12.4 { |
| 1044 | cksort { |
| 1045 | SELECT * FROM t6 ORDER BY a, b; |
| 1046 | } |
| 1047 | } {1 one 4 four nosort} |
| 1048 | do_test where-12.5 { |
| 1049 | cksort { |
| 1050 | SELECT * FROM t6 ORDER BY b DESC; |
| 1051 | } |
| 1052 | } {1 one 4 four nosort} |
| 1053 | do_test where-12.6 { |
| 1054 | cksort { |
| 1055 | SELECT * FROM t6 ORDER BY b DESC, a DESC; |
| 1056 | } |
| 1057 | } {1 one 4 four nosort} |
| 1058 | do_test where-12.7 { |
| 1059 | cksort { |
| 1060 | SELECT * FROM t6 ORDER BY b DESC, a ASC; |
| 1061 | } |
| 1062 | } {1 one 4 four sort} |
| 1063 | do_test where-12.8 { |
| 1064 | cksort { |
| 1065 | SELECT * FROM t6 ORDER BY b ASC, a DESC; |
| 1066 | } |
| 1067 | } {4 four 1 one sort} |
| 1068 | do_test where-12.9 { |
| 1069 | cksort { |
| 1070 | SELECT * FROM t6 ORDER BY a DESC; |
| 1071 | } |
| 1072 | } {4 four 1 one nosort} |
| 1073 | do_test where-12.10 { |
| 1074 | cksort { |
| 1075 | SELECT * FROM t6 ORDER BY a DESC, b DESC; |
| 1076 | } |
| 1077 | } {4 four 1 one nosort} |
| 1078 | do_test where-12.11 { |
| 1079 | cksort { |
| 1080 | SELECT * FROM t6 ORDER BY a DESC, b ASC; |
| 1081 | } |
| 1082 | } {4 four 1 one nosort} |
| 1083 | do_test where-12.12 { |
| 1084 | cksort { |
| 1085 | SELECT * FROM t6 ORDER BY a ASC, b DESC; |
| 1086 | } |
| 1087 | } {1 one 4 four nosort} |
| 1088 | do_test where-13.1 { |
| 1089 | execsql { |
| 1090 | CREATE TABLE t7(a INTEGER PRIMARY KEY, b TEXT); |
| 1091 | INSERT INTO t7 VALUES(1,'one'); |
| 1092 | INSERT INTO t7 VALUES(4,'four'); |
| 1093 | CREATE INDEX t7i1 ON t7(b); |
| 1094 | } |
| 1095 | cksort { |
| 1096 | SELECT * FROM t7 ORDER BY b; |
| 1097 | } |
| 1098 | } {4 four 1 one nosort} |
| 1099 | do_test where-13.2 { |
| 1100 | cksort { |
| 1101 | SELECT * FROM t7 ORDER BY b, a; |
| 1102 | } |
| 1103 | } {4 four 1 one nosort} |
| 1104 | do_test where-13.3 { |
| 1105 | cksort { |
| 1106 | SELECT * FROM t7 ORDER BY a; |
| 1107 | } |
| 1108 | } {1 one 4 four nosort} |
| 1109 | do_test where-13.4 { |
| 1110 | cksort { |
| 1111 | SELECT * FROM t7 ORDER BY a, b; |
| 1112 | } |
| 1113 | } {1 one 4 four nosort} |
| 1114 | do_test where-13.5 { |
| 1115 | cksort { |
| 1116 | SELECT * FROM t7 ORDER BY b DESC; |
| 1117 | } |
| 1118 | } {1 one 4 four nosort} |
| 1119 | do_test where-13.6 { |
| 1120 | cksort { |
| 1121 | SELECT * FROM t7 ORDER BY b DESC, a DESC; |
| 1122 | } |
| 1123 | } {1 one 4 four nosort} |
| 1124 | do_test where-13.7 { |
| 1125 | cksort { |
| 1126 | SELECT * FROM t7 ORDER BY b DESC, a ASC; |
| 1127 | } |
| 1128 | } {1 one 4 four sort} |
| 1129 | do_test where-13.8 { |
| 1130 | cksort { |
| 1131 | SELECT * FROM t7 ORDER BY b ASC, a DESC; |
| 1132 | } |
| 1133 | } {4 four 1 one sort} |
| 1134 | do_test where-13.9 { |
| 1135 | cksort { |
| 1136 | SELECT * FROM t7 ORDER BY a DESC; |
| 1137 | } |
| 1138 | } {4 four 1 one nosort} |
| 1139 | do_test where-13.10 { |
| 1140 | cksort { |
| 1141 | SELECT * FROM t7 ORDER BY a DESC, b DESC; |
| 1142 | } |
| 1143 | } {4 four 1 one nosort} |
| 1144 | do_test where-13.11 { |
| 1145 | cksort { |
| 1146 | SELECT * FROM t7 ORDER BY a DESC, b ASC; |
| 1147 | } |
| 1148 | } {4 four 1 one nosort} |
| 1149 | do_test where-13.12 { |
| 1150 | cksort { |
| 1151 | SELECT * FROM t7 ORDER BY a ASC, b DESC; |
| 1152 | } |
| 1153 | } {1 one 4 four nosort} |
| 1154 | |
drh | 7b4fc6a | 2007-02-06 13:26:32 +0000 | [diff] [blame] | 1155 | # Ticket #2211. |
| 1156 | # |
| 1157 | # When optimizing out ORDER BY clauses, make sure that trailing terms |
| 1158 | # of the ORDER BY clause do not reference other tables in a join. |
| 1159 | # |
dan | e825831 | 2012-12-05 19:04:32 +0000 | [diff] [blame] | 1160 | if {[permutation] != "no_optimization"} { |
drh | 7b4fc6a | 2007-02-06 13:26:32 +0000 | [diff] [blame] | 1161 | do_test where-14.1 { |
| 1162 | execsql { |
drh | 165674d | 2013-10-04 15:58:59 +0000 | [diff] [blame] | 1163 | CREATE TABLE t8(a INTEGER PRIMARY KEY, b TEXT UNIQUE, c CHAR(100)); |
| 1164 | INSERT INTO t8(a,b) VALUES(1,'one'); |
| 1165 | INSERT INTO t8(a,b) VALUES(4,'four'); |
drh | 7b4fc6a | 2007-02-06 13:26:32 +0000 | [diff] [blame] | 1166 | } |
| 1167 | cksort { |
| 1168 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b |
| 1169 | } |
drh | 6f0e400 | 2012-10-03 12:38:19 +0000 | [diff] [blame] | 1170 | } {1/4 1/1 4/4 4/1 nosort} |
drh | 7b4fc6a | 2007-02-06 13:26:32 +0000 | [diff] [blame] | 1171 | do_test where-14.2 { |
| 1172 | cksort { |
| 1173 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, y.b DESC |
| 1174 | } |
drh | 6f0e400 | 2012-10-03 12:38:19 +0000 | [diff] [blame] | 1175 | } {1/1 1/4 4/1 4/4 nosort} |
drh | 7b4fc6a | 2007-02-06 13:26:32 +0000 | [diff] [blame] | 1176 | do_test where-14.3 { |
| 1177 | cksort { |
| 1178 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b |
| 1179 | } |
drh | 3f4d1d1 | 2012-09-15 18:45:54 +0000 | [diff] [blame] | 1180 | } {1/4 1/1 4/4 4/1 nosort} |
drh | 7b4fc6a | 2007-02-06 13:26:32 +0000 | [diff] [blame] | 1181 | do_test where-14.4 { |
| 1182 | cksort { |
| 1183 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.a, x.b DESC |
| 1184 | } |
drh | 3f4d1d1 | 2012-09-15 18:45:54 +0000 | [diff] [blame] | 1185 | } {1/4 1/1 4/4 4/1 nosort} |
drh | 7b4fc6a | 2007-02-06 13:26:32 +0000 | [diff] [blame] | 1186 | do_test where-14.5 { |
dan | 99f8fb6 | 2012-04-20 15:24:53 +0000 | [diff] [blame] | 1187 | # This test case changed from "nosort" to "sort". See ticket 2a5629202f. |
drh | 7b4fc6a | 2007-02-06 13:26:32 +0000 | [diff] [blame] | 1188 | cksort { |
| 1189 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b |
| 1190 | } |
drh | 5343b2d | 2012-09-27 19:53:38 +0000 | [diff] [blame] | 1191 | } {/4/[14] 4/[14] 1/[14] 1/[14] sort/} |
drh | 7b4fc6a | 2007-02-06 13:26:32 +0000 | [diff] [blame] | 1192 | do_test where-14.6 { |
dan | 99f8fb6 | 2012-04-20 15:24:53 +0000 | [diff] [blame] | 1193 | # This test case changed from "nosort" to "sort". See ticket 2a5629202f. |
drh | 7b4fc6a | 2007-02-06 13:26:32 +0000 | [diff] [blame] | 1194 | cksort { |
| 1195 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||x.b DESC |
| 1196 | } |
drh | 5343b2d | 2012-09-27 19:53:38 +0000 | [diff] [blame] | 1197 | } {/4/[14] 4/[14] 1/[14] 1/[14] sort/} |
drh | 7b4fc6a | 2007-02-06 13:26:32 +0000 | [diff] [blame] | 1198 | do_test where-14.7 { |
| 1199 | cksort { |
| 1200 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b |
| 1201 | } |
| 1202 | } {4/1 4/4 1/1 1/4 sort} |
drh | 32ffdb7 | 2007-02-06 23:41:34 +0000 | [diff] [blame] | 1203 | do_test where-14.7.1 { |
| 1204 | cksort { |
| 1205 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, y.a||y.b |
| 1206 | } |
| 1207 | } {4/1 4/4 1/1 1/4 sort} |
| 1208 | do_test where-14.7.2 { |
| 1209 | cksort { |
| 1210 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a, x.a||x.b |
| 1211 | } |
drh | 3f4d1d1 | 2012-09-15 18:45:54 +0000 | [diff] [blame] | 1212 | } {4/4 4/1 1/4 1/1 nosort} |
drh | 7b4fc6a | 2007-02-06 13:26:32 +0000 | [diff] [blame] | 1213 | do_test where-14.8 { |
| 1214 | cksort { |
| 1215 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||y.b DESC |
| 1216 | } |
| 1217 | } {4/4 4/1 1/4 1/1 sort} |
| 1218 | do_test where-14.9 { |
| 1219 | cksort { |
| 1220 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b |
| 1221 | } |
| 1222 | } {4/4 4/1 1/4 1/1 sort} |
| 1223 | do_test where-14.10 { |
| 1224 | cksort { |
| 1225 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, x.a||y.b DESC |
| 1226 | } |
| 1227 | } {4/1 4/4 1/1 1/4 sort} |
| 1228 | do_test where-14.11 { |
| 1229 | cksort { |
| 1230 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b |
| 1231 | } |
| 1232 | } {4/1 4/4 1/1 1/4 sort} |
| 1233 | do_test where-14.12 { |
| 1234 | cksort { |
| 1235 | SELECT x.a || '/' || y.a FROM t8 x, t8 y ORDER BY x.b, y.a||x.b DESC |
| 1236 | } |
| 1237 | } {4/4 4/1 1/4 1/1 sort} |
dan | e825831 | 2012-12-05 19:04:32 +0000 | [diff] [blame] | 1238 | } ;# {permutation != "no_optimization"} |
drh | cc19254 | 2006-12-20 03:24:19 +0000 | [diff] [blame] | 1239 | |
danielk1977 | c9cf6e3 | 2007-06-25 16:29:33 +0000 | [diff] [blame] | 1240 | # Ticket #2445. |
| 1241 | # |
| 1242 | # There was a crash that could occur when a where clause contains an |
| 1243 | # alias for an expression in the result set, and that expression retrieves |
| 1244 | # a column of the second or subsequent table in a join. |
| 1245 | # |
| 1246 | do_test where-15.1 { |
| 1247 | execsql { |
| 1248 | CREATE TEMP TABLE t1 (a, b, c, d, e); |
| 1249 | CREATE TEMP TABLE t2 (f); |
| 1250 | SELECT t1.e AS alias FROM t2, t1 WHERE alias = 1 ; |
| 1251 | } |
| 1252 | } {} |
drh | 9eb2028 | 2005-08-24 03:52:18 +0000 | [diff] [blame] | 1253 | |
danielk1977 | 2d60549 | 2008-10-01 08:43:03 +0000 | [diff] [blame] | 1254 | # Ticket #3408. |
| 1255 | # |
| 1256 | # The branch of code in where.c that generated rowid lookups was |
| 1257 | # incorrectly deallocating a constant register, meaning that if the |
| 1258 | # vdbe code ran more than once, the second time around the constant |
| 1259 | # value may have been clobbered by some other value. |
| 1260 | # |
| 1261 | do_test where-16.1 { |
| 1262 | execsql { |
| 1263 | CREATE TABLE a1(id INTEGER PRIMARY KEY, v); |
| 1264 | CREATE TABLE a2(id INTEGER PRIMARY KEY, v); |
| 1265 | INSERT INTO a1 VALUES(1, 'one'); |
| 1266 | INSERT INTO a1 VALUES(2, 'two'); |
| 1267 | INSERT INTO a2 VALUES(1, 'one'); |
| 1268 | INSERT INTO a2 VALUES(2, 'two'); |
| 1269 | } |
| 1270 | } {} |
| 1271 | do_test where-16.2 { |
| 1272 | execsql { |
| 1273 | SELECT * FROM a2 CROSS JOIN a1 WHERE a1.id=1 AND a1.v='one'; |
| 1274 | } |
| 1275 | } {1 one 1 one 2 two 1 one} |
| 1276 | |
| 1277 | # The actual problem reported in #3408. |
| 1278 | do_test where-16.3 { |
| 1279 | execsql { |
| 1280 | CREATE TEMP TABLE foo(idx INTEGER); |
| 1281 | INSERT INTO foo VALUES(1); |
| 1282 | INSERT INTO foo VALUES(1); |
| 1283 | INSERT INTO foo VALUES(1); |
| 1284 | INSERT INTO foo VALUES(2); |
| 1285 | INSERT INTO foo VALUES(2); |
| 1286 | CREATE TEMP TABLE bar(stuff INTEGER); |
| 1287 | INSERT INTO bar VALUES(100); |
| 1288 | INSERT INTO bar VALUES(200); |
| 1289 | INSERT INTO bar VALUES(300); |
| 1290 | } |
| 1291 | } {} |
| 1292 | do_test where-16.4 { |
| 1293 | execsql { |
| 1294 | SELECT bar.RowID id FROM foo, bar WHERE foo.idx = bar.RowID AND id = 2; |
| 1295 | } |
| 1296 | } {2 2} |
| 1297 | |
drh | ed717fe | 2003-06-15 23:42:24 +0000 | [diff] [blame] | 1298 | integrity_check {where-99.0} |
drh | 08192d5 | 2002-04-30 19:20:28 +0000 | [diff] [blame] | 1299 | |
danielk1977 | 3072c5e | 2008-11-03 09:06:05 +0000 | [diff] [blame] | 1300 | #--------------------------------------------------------------------- |
| 1301 | # These tests test that a bug surrounding the use of ForceInt has been |
| 1302 | # fixed in where.c. |
| 1303 | # |
| 1304 | do_test where-17.1 { |
| 1305 | execsql { |
| 1306 | CREATE TABLE tbooking ( |
| 1307 | id INTEGER PRIMARY KEY, |
| 1308 | eventtype INTEGER NOT NULL |
| 1309 | ); |
| 1310 | INSERT INTO tbooking VALUES(42, 3); |
| 1311 | INSERT INTO tbooking VALUES(43, 4); |
| 1312 | } |
| 1313 | } {} |
| 1314 | do_test where-17.2 { |
| 1315 | execsql { |
| 1316 | SELECT a.id |
| 1317 | FROM tbooking AS a |
| 1318 | WHERE a.eventtype=3; |
| 1319 | } |
| 1320 | } {42} |
| 1321 | do_test where-17.3 { |
| 1322 | execsql { |
| 1323 | SELECT a.id, (SELECT b.id FROM tbooking AS b WHERE b.id>a.id) |
| 1324 | FROM tbooking AS a |
| 1325 | WHERE a.eventtype=3; |
| 1326 | } |
| 1327 | } {42 43} |
| 1328 | do_test where-17.4 { |
| 1329 | execsql { |
| 1330 | SELECT a.id, (SELECT b.id FROM tbooking AS b WHERE b.id>a.id) |
| 1331 | FROM (SELECT 1.5 AS id) AS a |
| 1332 | } |
| 1333 | } {1.5 42} |
| 1334 | do_test where-17.5 { |
| 1335 | execsql { |
| 1336 | CREATE TABLE tother(a, b); |
| 1337 | INSERT INTO tother VALUES(1, 3.7); |
| 1338 | SELECT id, a FROM tbooking, tother WHERE id>a; |
| 1339 | } |
| 1340 | } {42 1 43 1} |
| 1341 | |
drh | 67a5ec7 | 2013-09-03 14:03:47 +0000 | [diff] [blame] | 1342 | # Ticket [be84e357c035d068135f20bcfe82761bbf95006b] 2013-09-03 |
| 1343 | # Segfault during query involving LEFT JOIN column in the ORDER BY clause. |
| 1344 | # |
| 1345 | do_execsql_test where-18.1 { |
| 1346 | CREATE TABLE t181(a); |
| 1347 | CREATE TABLE t182(b,c); |
| 1348 | INSERT INTO t181 VALUES(1); |
| 1349 | SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL; |
| 1350 | } {1} |
| 1351 | do_execsql_test where-18.2 { |
| 1352 | SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c; |
| 1353 | } {1} |
| 1354 | do_execsql_test where-18.3 { |
| 1355 | SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c; |
| 1356 | } {1} |
| 1357 | do_execsql_test where-18.4 { |
| 1358 | INSERT INTO t181 VALUES(1),(1),(1),(1); |
| 1359 | SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +c; |
| 1360 | } {1} |
| 1361 | do_execsql_test where-18.5 { |
| 1362 | INSERT INTO t181 VALUES(2); |
| 1363 | SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY c IS NULL, +a; |
| 1364 | } {1 2} |
| 1365 | do_execsql_test where-18.6 { |
| 1366 | INSERT INTO t181 VALUES(2); |
| 1367 | SELECT DISTINCT a FROM t181 LEFT JOIN t182 ON a=b ORDER BY +a, +c IS NULL; |
| 1368 | } {1 2} |
| 1369 | |
drh | cb23e5d | 2018-04-24 16:41:37 +0000 | [diff] [blame] | 1370 | # Make sure the OR optimization works on a JOIN |
| 1371 | # |
| 1372 | do_execsql_test where-19.0 { |
| 1373 | CREATE TABLE t191(a INT UNIQUE NOT NULL, b INT UNIQUE NOT NULL,c,d); |
| 1374 | CREATE INDEX t191a ON t1(a); |
| 1375 | CREATE INDEX t191b ON t1(b); |
| 1376 | CREATE TABLE t192(x INTEGER PRIMARY KEY,y INT, z INT); |
| 1377 | |
| 1378 | EXPLAIN QUERY PLAN |
| 1379 | SELECT t191.rowid FROM t192, t191 WHERE (a=y OR b=y) AND x=?1; |
| 1380 | } {/.* sqlite_autoindex_t191_1 .* sqlite_autoindex_t191_2 .*/} |
| 1381 | |
drh | c447595 | 2018-04-24 17:34:03 +0000 | [diff] [blame] | 1382 | # 2018-04-24 ticket [https://www.sqlite.org/src/info/4ba5abf65c5b0f9a] |
| 1383 | # Index on expressions leads to an incorrect answer for a LEFT JOIN |
| 1384 | # |
| 1385 | do_execsql_test where-20.0 { |
| 1386 | CREATE TABLE t201(x); |
| 1387 | CREATE TABLE t202(y, z); |
| 1388 | INSERT INTO t201 VALUES('key'); |
| 1389 | INSERT INTO t202 VALUES('key', -1); |
| 1390 | CREATE INDEX t202i ON t202(y, ifnull(z, 0)); |
| 1391 | SELECT count(*) FROM t201 LEFT JOIN t202 ON (x=y) WHERE ifnull(z, 0) >=0; |
| 1392 | } {0} |
| 1393 | |
dan | ccb9eb7 | 2018-04-24 18:59:18 +0000 | [diff] [blame] | 1394 | do_execsql_test where-21.0 { |
| 1395 | CREATE TABLE t12(a, b, c); |
| 1396 | CREATE TABLE t13(x); |
| 1397 | CREATE INDEX t12ab ON t12(b, a); |
| 1398 | CREATE INDEX t12ac ON t12(c, a); |
| 1399 | |
| 1400 | INSERT INTO t12 VALUES(4, 0, 1); |
| 1401 | INSERT INTO t12 VALUES(4, 1, 0); |
| 1402 | INSERT INTO t12 VALUES(5, 0, 1); |
| 1403 | INSERT INTO t12 VALUES(5, 1, 0); |
| 1404 | |
| 1405 | INSERT INTO t13 VALUES(1), (2), (3), (4); |
| 1406 | } |
| 1407 | do_execsql_test where-21.1 { |
| 1408 | SELECT * FROM t12 WHERE |
| 1409 | a = (SELECT * FROM (SELECT count(*) FROM t13 LIMIT 5) ORDER BY 1 LIMIT 10) |
| 1410 | AND (b=1 OR c=1); |
| 1411 | } { |
| 1412 | 4 1 0 |
| 1413 | 4 0 1 |
| 1414 | } |
drh | 67a5ec7 | 2013-09-03 14:03:47 +0000 | [diff] [blame] | 1415 | |
drh | 383bb4f | 2018-11-05 07:53:17 +0000 | [diff] [blame] | 1416 | # 2018-11-05: ticket [https://www.sqlite.org/src/tktview/65eb38f6e46de8c75e188a] |
| 1417 | # Incorrect result in LEFT JOIN when STAT4 is enabled. |
| 1418 | # |
| 1419 | sqlite3 db :memory: |
| 1420 | do_execsql_test where-22.1 { |
| 1421 | CREATE TABLE t1(a INT); |
| 1422 | CREATE INDEX t1a ON t1(a); |
| 1423 | INSERT INTO t1(a) VALUES(NULL),(NULL),(42),(NULL),(NULL); |
| 1424 | CREATE TABLE t2(dummy INT); |
| 1425 | SELECT count(*) FROM t1 LEFT JOIN t2 ON a IS NOT NULL; |
| 1426 | } {5} |
| 1427 | |
drh | bc0a55c | 2019-02-22 21:33:56 +0000 | [diff] [blame] | 1428 | # 20190-02-22: A bug introduced by checkin |
| 1429 | # https://www.sqlite.org/src/info/fa792714ae62fa98. |
| 1430 | # |
| 1431 | do_execsql_test where-23.0 { |
| 1432 | DROP TABLE IF EXISTS t1; |
| 1433 | DROP TABLE IF EXISTS t2; |
| 1434 | CREATE TABLE t1(a INTEGER PRIMARY KEY); |
| 1435 | INSERT INTO t1(a) VALUES(1),(2),(3); |
| 1436 | CREATE TABLE t2(x INTEGER PRIMARY KEY, y INT); |
| 1437 | INSERT INTO t2(y) VALUES(2),(3); |
| 1438 | SELECT * FROM t1, t2 WHERE a=y AND y=3; |
| 1439 | } {3 2 3} |
drh | 383bb4f | 2018-11-05 07:53:17 +0000 | [diff] [blame] | 1440 | |
dan | 6cf3009 | 2019-04-05 20:47:15 +0000 | [diff] [blame] | 1441 | #------------------------------------------------------------------------- |
| 1442 | # |
| 1443 | reset_db |
| 1444 | do_execsql_test where-24.0 { |
| 1445 | CREATE TABLE t1(a INTEGER PRIMARY KEY, b); |
| 1446 | INSERT INTO t1 VALUES(1, 'one'); |
| 1447 | INSERT INTO t1 VALUES(2, 'two'); |
| 1448 | INSERT INTO t1 VALUES(3, 'three'); |
| 1449 | INSERT INTO t1 VALUES(4, 'four'); |
| 1450 | } |
| 1451 | |
| 1452 | foreach {tn sql res} { |
| 1453 | 1 "SELECT b FROM t1" {one two three four} |
| 1454 | 2 "SELECT b FROM t1 WHERE a<4" {one two three} |
| 1455 | 3 "SELECT b FROM t1 WHERE a>1" {two three four} |
| 1456 | 4 "SELECT b FROM t1 WHERE a>1 AND a<4" {two three} |
| 1457 | |
| 1458 | 5 "SELECT b FROM t1 WHERE a>? AND a<4" {} |
| 1459 | 6 "SELECT b FROM t1 WHERE a>1 AND a<?" {} |
| 1460 | 7 "SELECT b FROM t1 WHERE a>? AND a<?" {} |
| 1461 | |
| 1462 | 7 "SELECT b FROM t1 WHERE a>=? AND a<=4" {} |
| 1463 | 8 "SELECT b FROM t1 WHERE a>=1 AND a<=?" {} |
| 1464 | 9 "SELECT b FROM t1 WHERE a>=? AND a<=?" {} |
| 1465 | } { |
| 1466 | set rev [list] |
| 1467 | foreach r $res { set rev [concat $r $rev] } |
| 1468 | |
| 1469 | do_execsql_test where-24.$tn.1 "$sql" $res |
| 1470 | do_execsql_test where-24.$tn.2 "$sql ORDER BY rowid" $res |
| 1471 | do_execsql_test where-24.$tn.3 "$sql ORDER BY rowid DESC" $rev |
| 1472 | |
| 1473 | do_execsql_test where-24-$tn.4 " |
| 1474 | BEGIN; |
| 1475 | DELETE FROM t1; |
| 1476 | $sql; |
| 1477 | $sql ORDER BY rowid; |
| 1478 | $sql ORDER BY rowid DESC; |
| 1479 | ROLLBACK; |
| 1480 | " |
| 1481 | } |
| 1482 | |
| 1483 | #------------------------------------------------------------------------- |
| 1484 | # |
| 1485 | reset_db |
| 1486 | do_execsql_test where-25.0 { |
| 1487 | CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); |
| 1488 | CREATE UNIQUE INDEX i1 ON t1(c); |
| 1489 | INSERT INTO t1 VALUES(1, 'one', 'i'); |
| 1490 | INSERT INTO t1 VALUES(2, 'two', 'ii'); |
| 1491 | |
| 1492 | CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c); |
| 1493 | CREATE UNIQUE INDEX i2 ON t2(c); |
| 1494 | INSERT INTO t2 VALUES(1, 'one', 'i'); |
| 1495 | INSERT INTO t2 VALUES(2, 'two', 'ii'); |
| 1496 | INSERT INTO t2 VALUES(3, 'three', 'iii'); |
| 1497 | |
| 1498 | PRAGMA writable_schema = 1; |
drh | 346a70c | 2020-06-15 20:27:35 +0000 | [diff] [blame] | 1499 | UPDATE sqlite_schema SET rootpage = ( |
| 1500 | SELECT rootpage FROM sqlite_schema WHERE name = 'i2' |
dan | 6cf3009 | 2019-04-05 20:47:15 +0000 | [diff] [blame] | 1501 | ) WHERE name = 'i1'; |
| 1502 | } |
| 1503 | db close |
| 1504 | sqlite3 db test.db |
| 1505 | do_catchsql_test where-25.1 { |
| 1506 | DELETE FROM t1 WHERE c='iii' |
| 1507 | } {1 {database disk image is malformed}} |
| 1508 | do_catchsql_test where-25.2 { |
| 1509 | INSERT INTO t1 VALUES(4, 'four', 'iii') |
| 1510 | ON CONFLICT(c) DO UPDATE SET b=NULL |
| 1511 | } {1 {database disk image is malformed}} |
| 1512 | |
| 1513 | reset_db |
| 1514 | do_execsql_test where-25.3 { |
| 1515 | CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID; |
| 1516 | CREATE UNIQUE INDEX i1 ON t1(c); |
| 1517 | INSERT INTO t1 VALUES(1, 'one', 'i'); |
| 1518 | INSERT INTO t1 VALUES(2, 'two', 'ii'); |
| 1519 | |
| 1520 | CREATE TABLE t2(a INTEGER PRIMARY KEY, b, c); |
| 1521 | CREATE UNIQUE INDEX i2 ON t2(c); |
| 1522 | INSERT INTO t2 VALUES(1, 'one', 'i'); |
| 1523 | INSERT INTO t2 VALUES(2, 'two', 'ii'); |
| 1524 | INSERT INTO t2 VALUES(3, 'three', 'iii'); |
| 1525 | |
| 1526 | PRAGMA writable_schema = 1; |
drh | 346a70c | 2020-06-15 20:27:35 +0000 | [diff] [blame] | 1527 | UPDATE sqlite_schema SET rootpage = ( |
| 1528 | SELECT rootpage FROM sqlite_schema WHERE name = 'i2' |
dan | 6cf3009 | 2019-04-05 20:47:15 +0000 | [diff] [blame] | 1529 | ) WHERE name = 'i1'; |
| 1530 | } |
| 1531 | db close |
| 1532 | sqlite3 db test.db |
| 1533 | do_catchsql_test where-25.4 { |
| 1534 | SELECT * FROM t1 WHERE c='iii' |
| 1535 | } {0 {}} |
| 1536 | do_catchsql_test where-25.5 { |
| 1537 | INSERT INTO t1 VALUES(4, 'four', 'iii') |
| 1538 | ON CONFLICT(c) DO UPDATE SET b=NULL |
| 1539 | } {1 {corrupt database}} |
| 1540 | |
drh | 3e36480 | 2019-08-22 00:53:16 +0000 | [diff] [blame] | 1541 | # 2019-08-21 Ticket https://www.sqlite.org/src/info/d9f584e936c7a8d0 |
| 1542 | # |
| 1543 | db close |
| 1544 | sqlite3 db :memory: |
| 1545 | do_execsql_test where-26.1 { |
| 1546 | CREATE TABLE t0(c0 INTEGER PRIMARY KEY, c1 TEXT); |
| 1547 | INSERT INTO t0(c0, c1) VALUES (1, 'a'); |
| 1548 | CREATE TABLE t1(c0 INT PRIMARY KEY, c1 TEXT); |
| 1549 | INSERT INTO t1(c0, c1) VALUES (1, 'a'); |
| 1550 | SELECT * FROM t0 WHERE '-1' BETWEEN 0 AND t0.c0; |
| 1551 | } {1 a} |
| 1552 | do_execsql_test where-26.2 { |
| 1553 | SELECT * FROM t1 WHERE '-1' BETWEEN 0 AND t1.c0; |
| 1554 | } {1 a} |
| 1555 | do_execsql_test where-26.3 { |
| 1556 | SELECT * FROM t0 WHERE '-1'>=0 AND '-1'<=t0.c0; |
| 1557 | } {1 a} |
| 1558 | do_execsql_test where-26.4 { |
| 1559 | SELECT * FROM t1 WHERE '-1'>=0 AND '-1'<=t1.c0; |
| 1560 | } {1 a} |
| 1561 | do_execsql_test where-26.5 { |
| 1562 | SELECT '-1' BETWEEN 0 AND t0.c0 FROM t0; |
| 1563 | } {1} |
| 1564 | do_execsql_test where-26.6 { |
| 1565 | SELECT '-1' BETWEEN 0 AND t1.c0 FROM t1; |
| 1566 | } {1} |
| 1567 | do_execsql_test where-26.7 { |
| 1568 | SELECT '-1'>=0 AND '-1'<=t0.c0 FROM t0; |
| 1569 | } {1} |
| 1570 | do_execsql_test where-26.8 { |
| 1571 | SELECT '-1'>=0 AND '-1'<=t1.c0 FROM t1; |
| 1572 | } {1} |
| 1573 | |
drh | 6de4f4c | 2000-06-12 12:20:48 +0000 | [diff] [blame] | 1574 | finish_test |