PageRenderTime 55ms CodeModel.GetById 28ms RepoModel.GetById 0ms app.codeStats 0ms

/test/where2.test

https://bitbucket.org/aware/sqlite
Unknown | 654 lines | 627 code | 27 blank | 0 comment | 0 complexity | cd60e8c73d17e16eaa317d7229c9a78e MD5 | raw file
  1. # 2005 July 28
  2. #
  3. # The author disclaims copyright to this source code. In place of
  4. # a legal notice, here is a blessing:
  5. #
  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.
  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 clauses
  13. # based on recent changes to the optimizer.
  14. #
  15. # $Id:$
  16. set testdir [file dirname $argv0]
  17. source $testdir/tester.tcl
  18. # Build some test data
  19. #
  20. do_test where2-1.0 {
  21. execsql {
  22. BEGIN;
  23. CREATE TABLE t1(w int, x int, y int, z int);
  24. }
  25. for {set i 1} {$i<=100} {incr i} {
  26. set w $i
  27. set x [expr {int(log($i)/log(2))}]
  28. set y [expr {$i*$i + 2*$i + 1}]
  29. set z [expr {$x+$y}]
  30. ifcapable tclvar {
  31. execsql {INSERT INTO t1 VALUES($::w,$::x,$::y,$::z)}
  32. } else {
  33. execsql {INSERT INTO t1 VALUES(:w,:x,:y,:z)}
  34. }
  35. }
  36. execsql {
  37. CREATE UNIQUE INDEX i1w ON t1(w);
  38. CREATE INDEX i1xy ON t1(x,y);
  39. CREATE INDEX i1zyx ON t1(z,y,x);
  40. COMMIT;
  41. }
  42. } {}
  43. # Do an SQL statement. Append the search count to the end of the result.
  44. #
  45. proc count sql {
  46. set ::sqlite_search_count 0
  47. return [concat [execsql $sql] $::sqlite_search_count]
  48. }
  49. # This procedure executes the SQL. Then it checks to see if the OP_Sort
  50. # opcode was executed. If an OP_Sort did occur, then "sort" is appended
  51. # to the result. If no OP_Sort happened, then "nosort" is appended.
  52. #
  53. # This procedure is used to check to make sure sorting is or is not
  54. # occurring as expected.
  55. #
  56. proc cksort {sql} {
  57. set data [execsql $sql]
  58. if {[db status sort]} {set x sort} {set x nosort}
  59. lappend data $x
  60. return $data
  61. }
  62. # This procedure executes the SQL. Then it appends to the result the
  63. # "sort" or "nosort" keyword (as in the cksort procedure above) then
  64. # it appends the ::sqlite_query_plan variable.
  65. #
  66. proc queryplan {sql} {
  67. set ::sqlite_sort_count 0
  68. set data [execsql $sql]
  69. if {$::sqlite_sort_count} {set x sort} {set x nosort}
  70. lappend data $x
  71. return [concat $data $::sqlite_query_plan]
  72. }
  73. # Prefer a UNIQUE index over another index.
  74. #
  75. do_test where2-1.1 {
  76. queryplan {
  77. SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396
  78. }
  79. } {85 6 7396 7402 nosort t1 i1w}
  80. # Always prefer a rowid== constraint over any other index.
  81. #
  82. do_test where2-1.3 {
  83. queryplan {
  84. SELECT * FROM t1 WHERE w=85 AND x=6 AND y=7396 AND rowid=85
  85. }
  86. } {85 6 7396 7402 nosort t1 *}
  87. # When constrained by a UNIQUE index, the ORDER BY clause is always ignored.
  88. #
  89. do_test where2-2.1 {
  90. queryplan {
  91. SELECT * FROM t1 WHERE w=85 ORDER BY random();
  92. }
  93. } {85 6 7396 7402 nosort t1 i1w}
  94. do_test where2-2.2 {
  95. queryplan {
  96. SELECT * FROM t1 WHERE x=6 AND y=7396 ORDER BY random();
  97. }
  98. } {85 6 7396 7402 sort t1 i1xy}
  99. do_test where2-2.3 {
  100. queryplan {
  101. SELECT * FROM t1 WHERE rowid=85 AND x=6 AND y=7396 ORDER BY random();
  102. }
  103. } {85 6 7396 7402 nosort t1 *}
  104. # Efficient handling of forward and reverse table scans.
  105. #
  106. do_test where2-3.1 {
  107. queryplan {
  108. SELECT * FROM t1 ORDER BY rowid LIMIT 2
  109. }
  110. } {1 0 4 4 2 1 9 10 nosort t1 *}
  111. do_test where2-3.2 {
  112. queryplan {
  113. SELECT * FROM t1 ORDER BY rowid DESC LIMIT 2
  114. }
  115. } {100 6 10201 10207 99 6 10000 10006 nosort t1 *}
  116. # The IN operator can be used by indices at multiple layers
  117. #
  118. ifcapable subquery {
  119. do_test where2-4.1 {
  120. queryplan {
  121. SELECT * FROM t1 WHERE z IN (10207,10006) AND y IN (10000,10201)
  122. AND x>0 AND x<10
  123. ORDER BY w
  124. }
  125. } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
  126. do_test where2-4.2 {
  127. queryplan {
  128. SELECT * FROM t1 WHERE z IN (10207,10006) AND y=10000
  129. AND x>0 AND x<10
  130. ORDER BY w
  131. }
  132. } {99 6 10000 10006 sort t1 i1zyx}
  133. do_test where2-4.3 {
  134. queryplan {
  135. SELECT * FROM t1 WHERE z=10006 AND y IN (10000,10201)
  136. AND x>0 AND x<10
  137. ORDER BY w
  138. }
  139. } {99 6 10000 10006 sort t1 i1zyx}
  140. ifcapable compound {
  141. do_test where2-4.4 {
  142. queryplan {
  143. SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
  144. AND y IN (10000,10201)
  145. AND x>0 AND x<10
  146. ORDER BY w
  147. }
  148. } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
  149. do_test where2-4.5 {
  150. queryplan {
  151. SELECT * FROM t1 WHERE z IN (SELECT 10207 UNION SELECT 10006)
  152. AND y IN (SELECT 10000 UNION SELECT 10201)
  153. AND x>0 AND x<10
  154. ORDER BY w
  155. }
  156. } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
  157. }
  158. do_test where2-4.6 {
  159. queryplan {
  160. SELECT * FROM t1
  161. WHERE x IN (1,2,3,4,5,6,7,8)
  162. AND y IN (10000,10001,10002,10003,10004,10005)
  163. ORDER BY 2
  164. }
  165. } {99 6 10000 10006 sort t1 i1xy}
  166. # Duplicate entires on the RHS of an IN operator do not cause duplicate
  167. # output rows.
  168. #
  169. do_test where2-4.6 {
  170. queryplan {
  171. SELECT * FROM t1 WHERE z IN (10207,10006,10006,10207)
  172. ORDER BY w
  173. }
  174. } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
  175. ifcapable compound {
  176. do_test where2-4.7 {
  177. queryplan {
  178. SELECT * FROM t1 WHERE z IN (
  179. SELECT 10207 UNION ALL SELECT 10006
  180. UNION ALL SELECT 10006 UNION ALL SELECT 10207)
  181. ORDER BY w
  182. }
  183. } {99 6 10000 10006 100 6 10201 10207 sort t1 i1zyx}
  184. }
  185. } ;# ifcapable subquery
  186. # The use of an IN operator disables the index as a sorter.
  187. #
  188. do_test where2-5.1 {
  189. queryplan {
  190. SELECT * FROM t1 WHERE w=99 ORDER BY w
  191. }
  192. } {99 6 10000 10006 nosort t1 i1w}
  193. ifcapable subquery {
  194. do_test where2-5.2 {
  195. queryplan {
  196. SELECT * FROM t1 WHERE w IN (99) ORDER BY w
  197. }
  198. } {99 6 10000 10006 sort t1 i1w}
  199. }
  200. # Verify that OR clauses get translated into IN operators.
  201. #
  202. set ::idx {}
  203. ifcapable subquery {set ::idx i1w}
  204. do_test where2-6.1.1 {
  205. queryplan {
  206. SELECT * FROM t1 WHERE w=99 OR w=100 ORDER BY +w
  207. }
  208. } [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
  209. do_test where2-6.1.2 {
  210. queryplan {
  211. SELECT * FROM t1 WHERE 99=w OR 100=w ORDER BY +w
  212. }
  213. } [list 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
  214. do_test where2-6.2 {
  215. queryplan {
  216. SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=w ORDER BY +w
  217. }
  218. } [list 6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 $::idx]
  219. do_test where2-6.3 {
  220. queryplan {
  221. SELECT * FROM t1 WHERE w=99 OR w=100 OR 6=+w ORDER BY +w
  222. }
  223. } {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}}
  224. do_test where2-6.4 {
  225. queryplan {
  226. SELECT * FROM t1 WHERE w=99 OR +w=100 OR 6=w ORDER BY +w
  227. }
  228. } {6 2 49 51 99 6 10000 10006 100 6 10201 10207 sort t1 {}}
  229. set ::idx {}
  230. ifcapable subquery {set ::idx i1zyx}
  231. do_test where2-6.5 {
  232. queryplan {
  233. SELECT b.* FROM t1 a, t1 b
  234. WHERE a.w=1 AND (a.y=b.z OR b.z=10)
  235. ORDER BY +b.w
  236. }
  237. } [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
  238. do_test where2-6.6 {
  239. queryplan {
  240. SELECT b.* FROM t1 a, t1 b
  241. WHERE a.w=1 AND (b.z=10 OR a.y=b.z OR b.z=10)
  242. ORDER BY +b.w
  243. }
  244. } [list 1 0 4 4 2 1 9 10 sort a i1w b $::idx]
  245. # Ticket #2249. Make sure the OR optimization is not attempted if
  246. # comparisons between columns of different affinities are needed.
  247. #
  248. do_test where2-6.7 {
  249. execsql {
  250. CREATE TABLE t2249a(a TEXT UNIQUE);
  251. CREATE TABLE t2249b(b INTEGER);
  252. INSERT INTO t2249a VALUES('0123');
  253. INSERT INTO t2249b VALUES(123);
  254. }
  255. queryplan {
  256. -- Because a is type TEXT and b is type INTEGER, both a and b
  257. -- will attempt to convert to NUMERIC before the comparison.
  258. -- They will thus compare equal.
  259. --
  260. SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b;
  261. }
  262. } {123 0123 nosort t2249b {} t2249a {}}
  263. do_test where2-6.9 {
  264. queryplan {
  265. -- The + operator removes affinity from the rhs. No conversions
  266. -- occur and the comparison is false. The result is an empty set.
  267. --
  268. SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b;
  269. }
  270. } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
  271. do_test where2-6.9.2 {
  272. # The same thing but with the expression flipped around.
  273. queryplan {
  274. SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a
  275. }
  276. } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
  277. do_test where2-6.10 {
  278. queryplan {
  279. -- Use + on both sides of the comparison to disable indices
  280. -- completely. Make sure we get the same result.
  281. --
  282. SELECT * FROM t2249b CROSS JOIN t2249a WHERE +a=+b;
  283. }
  284. } {nosort t2249b {} t2249a {}}
  285. do_test where2-6.11 {
  286. # This will not attempt the OR optimization because of the a=b
  287. # comparison.
  288. queryplan {
  289. SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=b OR a='hello';
  290. }
  291. } {123 0123 nosort t2249b {} t2249a {}}
  292. do_test where2-6.11.2 {
  293. # Permutations of the expression terms.
  294. queryplan {
  295. SELECT * FROM t2249b CROSS JOIN t2249a WHERE b=a OR a='hello';
  296. }
  297. } {123 0123 nosort t2249b {} t2249a {}}
  298. do_test where2-6.11.3 {
  299. # Permutations of the expression terms.
  300. queryplan {
  301. SELECT * FROM t2249b CROSS JOIN t2249a WHERE 'hello'=a OR b=a;
  302. }
  303. } {123 0123 nosort t2249b {} t2249a {}}
  304. do_test where2-6.11.4 {
  305. # Permutations of the expression terms.
  306. queryplan {
  307. SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR b=a;
  308. }
  309. } {123 0123 nosort t2249b {} t2249a {}}
  310. ifcapable explain&&subquery {
  311. # These tests are not run if subquery support is not included in the
  312. # build. This is because these tests test the "a = 1 OR a = 2" to
  313. # "a IN (1, 2)" optimisation transformation, which is not enabled if
  314. # subqueries and the IN operator is not available.
  315. #
  316. do_test where2-6.12 {
  317. # In this case, the +b disables the affinity conflict and allows
  318. # the OR optimization to be used again. The result is now an empty
  319. # set, the same as in where2-6.9.
  320. queryplan {
  321. SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR a='hello';
  322. }
  323. } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
  324. do_test where2-6.12.2 {
  325. # In this case, the +b disables the affinity conflict and allows
  326. # the OR optimization to be used again. The result is now an empty
  327. # set, the same as in where2-6.9.
  328. queryplan {
  329. SELECT * FROM t2249b CROSS JOIN t2249a WHERE a='hello' OR +b=a;
  330. }
  331. } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
  332. do_test where2-6.12.3 {
  333. # In this case, the +b disables the affinity conflict and allows
  334. # the OR optimization to be used again. The result is now an empty
  335. # set, the same as in where2-6.9.
  336. queryplan {
  337. SELECT * FROM t2249b CROSS JOIN t2249a WHERE +b=a OR a='hello';
  338. }
  339. } {nosort t2249b {} {} sqlite_autoindex_t2249a_1}
  340. do_test where2-6.13 {
  341. # The addition of +a on the second term disabled the OR optimization.
  342. # But we should still get the same empty-set result as in where2-6.9.
  343. queryplan {
  344. SELECT * FROM t2249b CROSS JOIN t2249a WHERE a=+b OR +a='hello';
  345. }
  346. } {nosort t2249b {} t2249a {}}
  347. }
  348. # Variations on the order of terms in a WHERE clause in order
  349. # to make sure the OR optimizer can recognize them all.
  350. do_test where2-6.20 {
  351. queryplan {
  352. SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a
  353. }
  354. } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
  355. ifcapable explain&&subquery {
  356. # These tests are not run if subquery support is not included in the
  357. # build. This is because these tests test the "a = 1 OR a = 2" to
  358. # "a IN (1, 2)" optimisation transformation, which is not enabled if
  359. # subqueries and the IN operator is not available.
  360. #
  361. do_test where2-6.21 {
  362. queryplan {
  363. SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE x.a=y.a OR y.a='hello'
  364. }
  365. } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
  366. do_test where2-6.22 {
  367. queryplan {
  368. SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a=x.a OR y.a='hello'
  369. }
  370. } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
  371. do_test where2-6.23 {
  372. queryplan {
  373. SELECT * FROM t2249a x CROSS JOIN t2249a y WHERE y.a='hello' OR x.a=y.a
  374. }
  375. } {0123 0123 nosort x {} {} sqlite_autoindex_t2249a_1}
  376. }
  377. # Unique queries (queries that are guaranteed to return only a single
  378. # row of result) do not call the sorter. But all tables must give
  379. # a unique result. If any one table in the join does not give a unique
  380. # result then sorting is necessary.
  381. #
  382. do_test where2-7.1 {
  383. cksort {
  384. create table t8(a unique, b, c);
  385. insert into t8 values(1,2,3);
  386. insert into t8 values(2,3,4);
  387. create table t9(x,y);
  388. insert into t9 values(2,4);
  389. insert into t9 values(2,3);
  390. select y from t8, t9 where a=1 order by a, y;
  391. }
  392. } {3 4 sort}
  393. do_test where2-7.2 {
  394. cksort {
  395. select * from t8 where a=1 order by b, c
  396. }
  397. } {1 2 3 nosort}
  398. do_test where2-7.3 {
  399. cksort {
  400. select * from t8, t9 where a=1 and y=3 order by b, x
  401. }
  402. } {1 2 3 2 3 sort}
  403. do_test where2-7.4 {
  404. cksort {
  405. create unique index i9y on t9(y);
  406. select * from t8, t9 where a=1 and y=3 order by b, x
  407. }
  408. } {1 2 3 2 3 nosort}
  409. # Ticket #1807. Using IN constrains on multiple columns of
  410. # a multi-column index.
  411. #
  412. ifcapable subquery {
  413. do_test where2-8.1 {
  414. execsql {
  415. SELECT * FROM t1 WHERE x IN (20,21) AND y IN (1,2)
  416. }
  417. } {}
  418. do_test where2-8.2 {
  419. execsql {
  420. SELECT * FROM t1 WHERE x IN (1,2) AND y IN (-5,-6)
  421. }
  422. } {}
  423. execsql {CREATE TABLE tx AS SELECT * FROM t1}
  424. do_test where2-8.3 {
  425. execsql {
  426. SELECT w FROM t1
  427. WHERE x IN (SELECT x FROM tx WHERE rowid<0)
  428. AND +y IN (SELECT y FROM tx WHERE rowid=1)
  429. }
  430. } {}
  431. do_test where2-8.4 {
  432. execsql {
  433. SELECT w FROM t1
  434. WHERE x IN (SELECT x FROM tx WHERE rowid=1)
  435. AND y IN (SELECT y FROM tx WHERE rowid<0)
  436. }
  437. } {}
  438. #set sqlite_where_trace 1
  439. do_test where2-8.5 {
  440. execsql {
  441. CREATE INDEX tx_xyz ON tx(x, y, z, w);
  442. SELECT w FROM tx
  443. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
  444. AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
  445. AND z IN (SELECT z FROM t1 WHERE w BETWEEN 12 AND 14)
  446. }
  447. } {12 13 14}
  448. do_test where2-8.6 {
  449. execsql {
  450. SELECT w FROM tx
  451. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
  452. AND y IN (SELECT y FROM t1 WHERE w BETWEEN 12 AND 14)
  453. AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
  454. }
  455. } {12 13 14}
  456. do_test where2-8.7 {
  457. execsql {
  458. SELECT w FROM tx
  459. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 12 AND 14)
  460. AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
  461. AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
  462. }
  463. } {10 11 12 13 14 15}
  464. do_test where2-8.8 {
  465. execsql {
  466. SELECT w FROM tx
  467. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
  468. AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
  469. AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
  470. }
  471. } {10 11 12 13 14 15 16 17 18 19 20}
  472. do_test where2-8.9 {
  473. execsql {
  474. SELECT w FROM tx
  475. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
  476. AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
  477. AND z IN (SELECT z FROM t1 WHERE w BETWEEN 2 AND 4)
  478. }
  479. } {}
  480. do_test where2-8.10 {
  481. execsql {
  482. SELECT w FROM tx
  483. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
  484. AND y IN (SELECT y FROM t1 WHERE w BETWEEN 2 AND 4)
  485. AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
  486. }
  487. } {}
  488. do_test where2-8.11 {
  489. execsql {
  490. SELECT w FROM tx
  491. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 2 AND 4)
  492. AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
  493. AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
  494. }
  495. } {}
  496. do_test where2-8.12 {
  497. execsql {
  498. SELECT w FROM tx
  499. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
  500. AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
  501. AND z IN (SELECT z FROM t1 WHERE w BETWEEN -4 AND -2)
  502. }
  503. } {}
  504. do_test where2-8.13 {
  505. execsql {
  506. SELECT w FROM tx
  507. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
  508. AND y IN (SELECT y FROM t1 WHERE w BETWEEN -4 AND -2)
  509. AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
  510. }
  511. } {}
  512. do_test where2-8.14 {
  513. execsql {
  514. SELECT w FROM tx
  515. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN -4 AND -2)
  516. AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
  517. AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
  518. }
  519. } {}
  520. do_test where2-8.15 {
  521. execsql {
  522. SELECT w FROM tx
  523. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
  524. AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
  525. AND z IN (SELECT z FROM t1 WHERE w BETWEEN 200 AND 300)
  526. }
  527. } {}
  528. do_test where2-8.16 {
  529. execsql {
  530. SELECT w FROM tx
  531. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 10 AND 20)
  532. AND y IN (SELECT y FROM t1 WHERE w BETWEEN 200 AND 300)
  533. AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
  534. }
  535. } {}
  536. do_test where2-8.17 {
  537. execsql {
  538. SELECT w FROM tx
  539. WHERE x IN (SELECT x FROM t1 WHERE w BETWEEN 200 AND 300)
  540. AND y IN (SELECT y FROM t1 WHERE w BETWEEN 10 AND 20)
  541. AND z IN (SELECT z FROM t1 WHERE w BETWEEN 10 AND 20)
  542. }
  543. } {}
  544. do_test where2-8.18 {
  545. execsql {
  546. SELECT w FROM tx
  547. WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
  548. AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
  549. AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 200 AND 300)
  550. }
  551. } {}
  552. do_test where2-8.19 {
  553. execsql {
  554. SELECT w FROM tx
  555. WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 10 AND 20)
  556. AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 200 AND 300)
  557. AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
  558. }
  559. } {}
  560. do_test where2-8.20 {
  561. execsql {
  562. SELECT w FROM tx
  563. WHERE x IN (SELECT x FROM t1 WHERE +w BETWEEN 200 AND 300)
  564. AND y IN (SELECT y FROM t1 WHERE +w BETWEEN 10 AND 20)
  565. AND z IN (SELECT z FROM t1 WHERE +w BETWEEN 10 AND 20)
  566. }
  567. } {}
  568. }
  569. # Make sure WHERE clauses of the form A=1 AND (B=2 OR B=3) are optimized
  570. # when we have an index on A and B.
  571. #
  572. ifcapable or_opt&&tclvar {
  573. do_test where2-9.1 {
  574. execsql {
  575. BEGIN;
  576. CREATE TABLE t10(a,b,c);
  577. INSERT INTO t10 VALUES(1,1,1);
  578. INSERT INTO t10 VALUES(1,2,2);
  579. INSERT INTO t10 VALUES(1,3,3);
  580. }
  581. for {set i 4} {$i<=1000} {incr i} {
  582. execsql {INSERT INTO t10 VALUES(1,$i,$i)}
  583. }
  584. execsql {
  585. CREATE INDEX i10 ON t10(a,b);
  586. COMMIT;
  587. SELECT count(*) FROM t10;
  588. }
  589. } 1000
  590. ifcapable subquery {
  591. do_test where2-9.2 {
  592. count {
  593. SELECT * FROM t10 WHERE a=1 AND (b=2 OR b=3)
  594. }
  595. } {1 2 2 1 3 3 7}
  596. }
  597. }
  598. # Indices with redundant columns
  599. #
  600. do_test where2-11.1 {
  601. execsql {
  602. CREATE TABLE t11(a,b,c,d);
  603. CREATE INDEX i11aba ON t11(a,b,a,c); -- column A occurs twice.
  604. INSERT INTO t11 VALUES(1,2,3,4);
  605. INSERT INTO t11 VALUES(5,6,7,8);
  606. INSERT INTO t11 VALUES(1,2,9,10);
  607. INSERT INTO t11 VALUES(5,11,12,13);
  608. SELECT c FROM t11 WHERE a=1 AND b=2 ORDER BY c;
  609. }
  610. } {3 9}
  611. do_test where2-11.2 {
  612. execsql {
  613. CREATE INDEX i11cccccccc ON t11(c,c,c,c,c,c,c,c); -- repeated column
  614. SELECT d FROM t11 WHERE c=9;
  615. }
  616. } {10}
  617. do_test where2-11.3 {
  618. execsql {
  619. SELECT d FROM t11 WHERE c IN (1,2,3,4,5);
  620. }
  621. } {4}
  622. do_test where2-11.4 {
  623. execsql {
  624. SELECT d FROM t11 WHERE c=7 OR (a=1 AND b=2) ORDER BY d;
  625. }
  626. } {4 8 10}
  627. finish_test