PageRenderTime 39ms CodeModel.GetById 13ms RepoModel.GetById 0ms app.codeStats 1ms

/test/where8.test

https://bitbucket.org/aware/sqlite
Unknown | 719 lines | 669 code | 50 blank | 0 comment | 0 complexity | aac4aae7351a0d8397e81d6be4007f85 MD5 | raw file
  1. # 2008 December 23
  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 focus
  12. # is testing of where.c. More specifically, the focus is the optimization
  13. # of WHERE clauses that feature the OR operator.
  14. #
  15. # $Id:$
  16. set testdir [file dirname $argv0]
  17. source $testdir/tester.tcl
  18. # Test organization:
  19. #
  20. # where8-1.*: Tests to demonstrate simple cases work with a single table
  21. # in the FROM clause.
  22. #
  23. # where8-2.*: Tests surrounding virtual tables and the OR optimization.
  24. #
  25. # where8-3.*: Tests with more than one table in the FROM clause.
  26. #
  27. proc execsql_status {sql {db db}} {
  28. set result [uplevel $db eval [list $sql]]
  29. concat $result [db status step] [db status sort]
  30. }
  31. proc execsql_status2 {sql {db db}} {
  32. set ::sqlite_search_count 0
  33. set result [uplevel [list execsql_status $sql $db]]
  34. concat $result $::sqlite_search_count
  35. }
  36. do_test where8-1.1 {
  37. execsql {
  38. CREATE TABLE t1(a, b TEXT, c);
  39. CREATE INDEX i1 ON t1(a);
  40. CREATE INDEX i2 ON t1(b);
  41. INSERT INTO t1 VALUES(1, 'one', 'I');
  42. INSERT INTO t1 VALUES(2, 'two', 'II');
  43. INSERT INTO t1 VALUES(3, 'three', 'III');
  44. INSERT INTO t1 VALUES(4, 'four', 'IV');
  45. INSERT INTO t1 VALUES(5, 'five', 'V');
  46. INSERT INTO t1 VALUES(6, 'six', 'VI');
  47. INSERT INTO t1 VALUES(7, 'seven', 'VII');
  48. INSERT INTO t1 VALUES(8, 'eight', 'VIII');
  49. INSERT INTO t1 VALUES(9, 'nine', 'IX');
  50. INSERT INTO t1 VALUES(10, 'ten', 'X');
  51. }
  52. } {}
  53. do_test where8-1.2 {
  54. execsql_status2 { SELECT c FROM t1 WHERE a = 1 OR b = 'nine' }
  55. } {I IX 0 0 6}
  56. do_test where8-1.3 {
  57. execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b = 'two' }
  58. } {IX X II 0 0 6}
  59. do_test where8-1.4 {
  60. execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 't*' }
  61. } {IX X III II 0 0 9}
  62. do_test where8-1.5 {
  63. execsql_status2 { SELECT c FROM t1 WHERE a > 8 OR b GLOB 'f*' }
  64. } {IX X V IV 0 0 9}
  65. do_test where8-1.6 {
  66. execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY rowid }
  67. } {I III 0 1}
  68. do_test where8-1.7 {
  69. execsql_status { SELECT c FROM t1 WHERE a = 1 OR b = 'three' ORDER BY a }
  70. } {I III 0 1}
  71. do_test where8-1.8 {
  72. # 18 searches. 9 on the index cursor and 9 on the table cursor.
  73. execsql_status2 { SELECT c FROM t1 WHERE a > 1 AND c LIKE 'I%' }
  74. } {II III IV IX 0 0 18}
  75. do_test where8-1.9 {
  76. execsql_status2 { SELECT c FROM t1 WHERE a >= 9 OR b <= 'eight' }
  77. } {IX X VIII 0 0 6}
  78. do_test where8-1.10 {
  79. execsql_status2 {
  80. SELECT c FROM t1 WHERE (a >= 9 AND c != 'X') OR b <= 'eight'
  81. }
  82. } {IX VIII 0 0 6}
  83. do_test where8-1.11 {
  84. execsql_status2 {
  85. SELECT c FROM t1 WHERE (a >= 4 AND a <= 6) OR b = 'nine'
  86. }
  87. } {IV V VI IX 0 0 10}
  88. do_test where8-1.12.1 {
  89. execsql_status2 {
  90. SELECT c FROM t1 WHERE a IN(1, 2, 3) OR a = 5
  91. }
  92. } {I II III V 0 0 14}
  93. do_test where8-1.12.2 {
  94. execsql_status2 {
  95. SELECT c FROM t1 WHERE +a IN(1, 2, 3) OR +a = 5
  96. }
  97. } {I II III V 9 0 9}
  98. do_test where8-1.13 {
  99. execsql_status2 {
  100. SELECT c FROM t1
  101. WHERE a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6
  102. ORDER BY rowid
  103. }
  104. } {II III IV V VI 0 1 18}
  105. do_test where8-1.14 {
  106. execsql_status2 {
  107. SELECT c FROM t1
  108. WHERE
  109. a = 2 OR b = 'three' OR a = 4 OR b = 'five' OR a = 6 OR
  110. b = 'seven' OR a = 8 OR b = 'nine' OR a = 10
  111. ORDER BY rowid
  112. }
  113. } {II III IV V VI VII VIII IX X 0 1 33}
  114. do_test where8-1.15 {
  115. execsql_status2 {
  116. SELECT c FROM t1 WHERE
  117. a BETWEEN 2 AND 4 OR b = 'nine'
  118. ORDER BY rowid
  119. }
  120. } {II III IV IX 0 1 12}
  121. #--------------------------------------------------------------------------
  122. # Tests where8-2.*: Virtual tables
  123. #
  124. if 0 {
  125. ifcapable vtab {
  126. # Register the 'echo' module used for testing virtual tables.
  127. #
  128. register_echo_module [sqlite3_connection_pointer db]
  129. do_test where8-2.1 {
  130. execsql {
  131. CREATE VIRTUAL TABLE e1 USING echo(t1);
  132. SELECT b FROM e1;
  133. }
  134. } {one two three four five six seven eight nine ten}
  135. do_test where8-2.2.1 {
  136. set echo_module ""
  137. execsql {
  138. SELECT c FROM e1 WHERE a=1 OR b='three';
  139. }
  140. } {I III}
  141. do_test where8-2.2.2 {
  142. set echo_module
  143. } {TODO: What should this be?}
  144. }
  145. }
  146. #--------------------------------------------------------------------------
  147. # Tests where8-3.*: Cases with multiple tables in the FROM clause.
  148. #
  149. do_test where8-3.1 {
  150. execsql {
  151. CREATE TABLE t2(d, e, f);
  152. CREATE INDEX i3 ON t2(d);
  153. CREATE INDEX i4 ON t2(e);
  154. INSERT INTO t2 VALUES(1, NULL, 'I');
  155. INSERT INTO t2 VALUES(2, 'four', 'IV');
  156. INSERT INTO t2 VALUES(3, NULL, 'IX');
  157. INSERT INTO t2 VALUES(4, 'sixteen', 'XVI');
  158. INSERT INTO t2 VALUES(5, NULL, 'XXV');
  159. INSERT INTO t2 VALUES(6, 'thirtysix', 'XXXVI');
  160. INSERT INTO t2 VALUES(7, 'fortynine', 'XLIX');
  161. INSERT INTO t2 VALUES(8, 'sixtyeight', 'LXIV');
  162. INSERT INTO t2 VALUES(9, 'eightyone', 'LXXXIX');
  163. INSERT INTO t2 VALUES(10, NULL, 'C');
  164. }
  165. } {}
  166. do_test where8-3.2 {
  167. execsql_status {
  168. SELECT a, d FROM t1, t2 WHERE b=e
  169. }
  170. } {4 2 9 0}
  171. do_test where8-3.3 {
  172. execsql_status {
  173. SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = 6
  174. }
  175. } {2 6 3 6 0 0}
  176. do_test where8-3.4 {
  177. execsql_status {
  178. SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND d = a
  179. }
  180. } {2 2 3 3 0 0}
  181. do_test where8-3.5 {
  182. execsql_status {
  183. SELECT a, d FROM t1, t2 WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen')
  184. }
  185. } {2 2 2 4 3 3 3 4 0 0}
  186. do_test where8-3.6 {
  187. # The first part of the WHERE clause in this query, (a=2 OR a=3) is
  188. # transformed into "a IN (2, 3)". This is why the sort is required.
  189. #
  190. execsql_status {
  191. SELECT a, d
  192. FROM t1, t2
  193. WHERE (a = 2 OR a = 3) AND (d = a OR e = 'sixteen')
  194. ORDER BY t1.rowid
  195. }
  196. } {2 2 2 4 3 3 3 4 0 1}
  197. do_test where8-3.7 {
  198. execsql_status {
  199. SELECT a, d
  200. FROM t1, t2
  201. WHERE a = 2 AND (d = a OR e = 'sixteen')
  202. ORDER BY t1.rowid
  203. }
  204. } {2 2 2 4 0 0}
  205. do_test where8-3.8 {
  206. execsql_status {
  207. SELECT a, d
  208. FROM t1, t2
  209. WHERE (a = 2 OR b = 'three') AND (d = a OR e = 'sixteen')
  210. ORDER BY t1.rowid
  211. }
  212. } {2 2 2 4 3 3 3 4 0 1}
  213. do_test where8-3.9 {
  214. # The "OR c = 'IX'" term forces a linear scan.
  215. execsql_status {
  216. SELECT a, d
  217. FROM t1, t2
  218. WHERE (a = 2 OR b = 'three' OR c = 'IX') AND (d = a OR e = 'sixteen')
  219. ORDER BY t1.rowid
  220. }
  221. } {2 2 2 4 3 3 3 4 9 9 9 4 9 0}
  222. do_test where8-3.10 {
  223. execsql_status {
  224. SELECT d FROM t2 WHERE e IS NULL OR e = 'four'
  225. }
  226. } {1 3 5 10 2 0 0}
  227. do_test where8-3.11 {
  228. execsql_status {
  229. SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND a<5 ORDER BY a
  230. }
  231. } {1 1 2 2 3 3 4 2 4 4 0 0}
  232. do_test where8-3.12 {
  233. execsql_status {
  234. SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5 ORDER BY a
  235. }
  236. } {1 1 2 2 3 3 4 2 4 4 0 0}
  237. do_test where8-3.13 {
  238. execsql_status {
  239. SELECT a, d FROM t1, t2 WHERE (a=d OR b=e) AND +a<5
  240. }
  241. } {1 1 2 2 3 3 4 2 4 4 9 0}
  242. do_test where8-3.14 {
  243. execsql_status {
  244. SELECT c FROM t1 WHERE a > (SELECT d FROM t2 WHERE e = b) OR a = 5
  245. }
  246. } {IV V 9 0}
  247. do_test where8-3.15 {
  248. execsql_status {
  249. SELECT c FROM t1, t2 WHERE a BETWEEN 1 AND 2 OR a = (
  250. SELECT sum(e IS NULL) FROM t2 AS inner WHERE t2.d>inner.d
  251. )
  252. ORDER BY c
  253. }
  254. } {I I I I I I I I I I II II II II II II II II II II III III III III III 9 1}
  255. #-----------------------------------------------------------------------
  256. # The following tests - where8-4.* - verify that adding or removing
  257. # indexes does not change the results returned by various queries.
  258. #
  259. do_test where8-4.1 {
  260. execsql {
  261. BEGIN;
  262. CREATE TABLE t3(a INTEGER, b REAL, c TEXT);
  263. CREATE TABLE t4(f INTEGER, g REAL, h TEXT);
  264. INSERT INTO t3 VALUES('hills', NULL, 1415926535);
  265. INSERT INTO t3 VALUES('and', 'of', NULL);
  266. INSERT INTO t3 VALUES('have', 'towering', 53594.08128);
  267. INSERT INTO t3 VALUES(NULL, 45.64856692, 'Not');
  268. INSERT INTO t3 VALUES('same', 5028841971, NULL);
  269. INSERT INTO t3 VALUES('onlookers', 'in', 8214808651);
  270. INSERT INTO t3 VALUES(346.0348610, 2643383279, NULL);
  271. INSERT INTO t3 VALUES(1415926535, 'of', 'are');
  272. INSERT INTO t3 VALUES(NULL, 0.4811174502, 'snapshots');
  273. INSERT INTO t3 VALUES('over', 'the', 8628034825);
  274. INSERT INTO t3 VALUES(8628034825, 66.59334461, 2847564.823);
  275. INSERT INTO t3 VALUES('onlookers', 'same', 'and');
  276. INSERT INTO t3 VALUES(NULL, 'light', 6939937510);
  277. INSERT INTO t3 VALUES('from', 'their', 'viewed');
  278. INSERT INTO t3 VALUES('from', 'Alpine', 'snapshots');
  279. INSERT INTO t3 VALUES('from', 'sometimes', 'unalike');
  280. INSERT INTO t3 VALUES(1339.360726, 'light', 'have');
  281. INSERT INTO t3 VALUES(6939937510, 3282306647, 'other');
  282. INSERT INTO t3 VALUES('paintings', 8628034825, 'all');
  283. INSERT INTO t3 VALUES('paintings', NULL, 'same');
  284. INSERT INTO t3 VALUES('Alpine', 378678316.5, 'unalike');
  285. INSERT INTO t3 VALUES('Alpine', NULL, 'same');
  286. INSERT INTO t3 VALUES(1339.360726, 2847564.823, 'over');
  287. INSERT INTO t3 VALUES('villages', 'their', 'have');
  288. INSERT INTO t3 VALUES('unalike', 'remarkably', 'in');
  289. INSERT INTO t3 VALUES('and', 8979323846, 'and');
  290. INSERT INTO t3 VALUES(NULL, 1415926535, 'an');
  291. INSERT INTO t3 VALUES(271.2019091, 8628034825, 0.4811174502);
  292. INSERT INTO t3 VALUES('all', 3421170679, 'the');
  293. INSERT INTO t3 VALUES('Not', 'and', 1415926535);
  294. INSERT INTO t3 VALUES('of', 'other', 'light');
  295. INSERT INTO t3 VALUES(NULL, 'towering', 'Not');
  296. INSERT INTO t3 VALUES(346.0348610, NULL, 'other');
  297. INSERT INTO t3 VALUES('Not', 378678316.5, NULL);
  298. INSERT INTO t3 VALUES('snapshots', 8628034825, 'of');
  299. INSERT INTO t3 VALUES(3282306647, 271.2019091, 'and');
  300. INSERT INTO t3 VALUES(50.58223172, 378678316.5, 5028841971);
  301. INSERT INTO t3 VALUES(50.58223172, 2643383279, 'snapshots');
  302. INSERT INTO t3 VALUES('writings', 8979323846, 8979323846);
  303. INSERT INTO t3 VALUES('onlookers', 'his', 'in');
  304. INSERT INTO t3 VALUES('unalike', 8628034825, 1339.360726);
  305. INSERT INTO t3 VALUES('of', 'Alpine', 'and');
  306. INSERT INTO t3 VALUES('onlookers', NULL, 'from');
  307. INSERT INTO t3 VALUES('writings', 'it', 1339.360726);
  308. INSERT INTO t3 VALUES('it', 'and', 'villages');
  309. INSERT INTO t3 VALUES('an', 'the', 'villages');
  310. INSERT INTO t3 VALUES(8214808651, 8214808651, 'same');
  311. INSERT INTO t3 VALUES(346.0348610, 'light', 1415926535);
  312. INSERT INTO t3 VALUES(NULL, 8979323846, 'and');
  313. INSERT INTO t3 VALUES(NULL, 'same', 1339.360726);
  314. INSERT INTO t4 VALUES('his', 'from', 'an');
  315. INSERT INTO t4 VALUES('snapshots', 'or', NULL);
  316. INSERT INTO t4 VALUES('Alpine', 'have', 'it');
  317. INSERT INTO t4 VALUES('have', 'peak', 'remarkably');
  318. INSERT INTO t4 VALUES('hills', NULL, 'Not');
  319. INSERT INTO t4 VALUES('same', 'from', 2643383279);
  320. INSERT INTO t4 VALUES('have', 'angle', 8628034825);
  321. INSERT INTO t4 VALUES('sometimes', 'it', 2847564.823);
  322. INSERT INTO t4 VALUES(0938446095, 'peak', 'of');
  323. INSERT INTO t4 VALUES(8628034825, 'and', 'same');
  324. INSERT INTO t4 VALUES('and', 271.2019091, 'their');
  325. INSERT INTO t4 VALUES('the', 'of', 'remarkably');
  326. INSERT INTO t4 VALUES('and', 3421170679, 1415926535);
  327. INSERT INTO t4 VALUES('and', 'in', 'all');
  328. INSERT INTO t4 VALUES(378678316.5, 0.4811174502, 'snapshots');
  329. INSERT INTO t4 VALUES('it', 'are', 'have');
  330. INSERT INTO t4 VALUES('angle', 'snapshots', 378678316.5);
  331. INSERT INTO t4 VALUES('from', 1415926535, 8628034825);
  332. INSERT INTO t4 VALUES('snapshots', 'angle', 'have');
  333. INSERT INTO t4 VALUES(3421170679, 0938446095, 'Not');
  334. INSERT INTO t4 VALUES('peak', NULL, 0.4811174502);
  335. INSERT INTO t4 VALUES('same', 'have', 'Alpine');
  336. INSERT INTO t4 VALUES(271.2019091, 66.59334461, 0938446095);
  337. INSERT INTO t4 VALUES(8979323846, 'his', 'an');
  338. INSERT INTO t4 VALUES(NULL, 'and', 3282306647);
  339. INSERT INTO t4 VALUES('remarkably', NULL, 'Not');
  340. INSERT INTO t4 VALUES('villages', 4543.266482, 'his');
  341. INSERT INTO t4 VALUES(2643383279, 'paintings', 'onlookers');
  342. INSERT INTO t4 VALUES(1339.360726, 'of', 'the');
  343. INSERT INTO t4 VALUES('peak', 'other', 'peak');
  344. INSERT INTO t4 VALUES('it', 'or', 8979323846);
  345. INSERT INTO t4 VALUES('onlookers', 'Not', 'towering');
  346. INSERT INTO t4 VALUES(NULL, 'peak', 'Not');
  347. INSERT INTO t4 VALUES('of', 'have', 6939937510);
  348. INSERT INTO t4 VALUES('light', 'hills', 0.4811174502);
  349. INSERT INTO t4 VALUES(5028841971, 'Not', 'it');
  350. INSERT INTO t4 VALUES('and', 'Not', NULL);
  351. INSERT INTO t4 VALUES(346.0348610, 'villages', NULL);
  352. INSERT INTO t4 VALUES(8979323846, NULL, 6939937510);
  353. INSERT INTO t4 VALUES('an', 'light', 'peak');
  354. INSERT INTO t4 VALUES(5028841971, 6939937510, 'light');
  355. INSERT INTO t4 VALUES('sometimes', 'peak', 'peak');
  356. INSERT INTO t4 VALUES(378678316.5, 5028841971, 'an');
  357. INSERT INTO t4 VALUES(378678316.5, 'his', 'Alpine');
  358. INSERT INTO t4 VALUES('from', 'of', 'all');
  359. INSERT INTO t4 VALUES(0938446095, 'same', NULL);
  360. INSERT INTO t4 VALUES(0938446095, 'Alpine', NULL);
  361. INSERT INTO t4 VALUES('his', 'of', 378678316.5);
  362. INSERT INTO t4 VALUES(271.2019091, 'viewed', 3282306647);
  363. INSERT INTO t4 VALUES('hills', 'all', 'peak');
  364. CREATE TABLE t5(s);
  365. INSERT INTO t5 VALUES('tab-t5');
  366. CREATE TABLE t6(t);
  367. INSERT INTO t6 VALUES(123456);
  368. COMMIT;
  369. }
  370. } {}
  371. catch {unset results}
  372. catch {unset A}
  373. catch {unset B}
  374. set A 2
  375. foreach idxsql {
  376. {
  377. /* No indexes */
  378. } {
  379. CREATE INDEX i5 ON t3(a);
  380. } {
  381. CREATE INDEX i5 ON t3(a, b);
  382. CREATE INDEX i6 ON t4(f);
  383. } {
  384. CREATE UNIQUE INDEX i5 ON t3(a, b);
  385. CREATE INDEX i7 ON t3(c);
  386. CREATE INDEX i6 ON t4(f);
  387. CREATE INDEX i8 ON t4(h);
  388. } {
  389. CREATE INDEX i5 ON t3(a, b, c);
  390. CREATE INDEX i6 ON t4(f, g, h);
  391. CREATE INDEX i7 ON t3(c, b, a);
  392. CREATE INDEX i8 ON t4(h, g, f);
  393. }
  394. } {
  395. execsql {
  396. DROP INDEX IF EXISTS i5;
  397. DROP INDEX IF EXISTS i6;
  398. DROP INDEX IF EXISTS i7;
  399. DROP INDEX IF EXISTS i8;
  400. }
  401. execsql $idxsql
  402. foreach {B sql} {
  403. 1 { SELECT * FROM t3 WHERE c LIKE b }
  404. 2 { SELECT * FROM t3 WHERE c||'' LIKE 'the%' }
  405. 3 { SELECT * FROM t3 WHERE rowid LIKE '12%' }
  406. 4 { SELECT * FROM t3 WHERE +c LIKE 'the%' }
  407. 5 { SELECT * FROM t3 WHERE c LIKE 'the%' }
  408. 6 { SELECT * FROM t3 WHERE c GLOB '*llo' }
  409. 7 { SELECT * FROM t3 WHERE a = 'angle' }
  410. 8 { SELECT * FROM t3 WHERE a = 'it' OR b = 6939937510 }
  411. 9 { SELECT * FROM t3, t4 WHERE a = 'painting' OR a = 'are' OR a = f }
  412. 10 { SELECT * FROM t3, t4 WHERE a = 'all' OR a = 'and' OR a = h }
  413. 11 { SELECT * FROM t3, t4 WHERE a < 'of' OR b > 346 AND c IS NULL }
  414. 12 { SELECT * FROM t3, t4 WHERE 'the' > a OR b > 'have' AND c = 1415926535 }
  415. 13 { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a = 3421170679 }
  416. 14 { SELECT * FROM t3 WHERE a BETWEEN 'one' AND 'two' OR a IS NULL }
  417. 15 { SELECT * FROM t3 WHERE c > 'one' OR c >= 'one' OR c LIKE 'one%' }
  418. 16 { SELECT * FROM t3 WHERE c > 'one' OR c = c OR c = a }
  419. 17 { SELECT * FROM t3 WHERE c IS NULL OR a >= 'peak' }
  420. 18 { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') OR a>1 }
  421. 19 { SELECT * FROM t3 WHERE c IN ('other', 'all', 'snapshots') AND a>1 }
  422. 20 { SELECT * FROM t3 WHERE c IS NULL AND a>'one' }
  423. 21 { SELECT * FROM t3 WHERE c IS NULL OR a>'one' }
  424. 22 { SELECT * FROM t3 WHERE b = b AND a > 'are' }
  425. 23 { SELECT * FROM t3 WHERE c <= b OR b < 'snapshots' }
  426. 24 { SELECT * FROM t3 WHERE 'onlookers' >= c AND a <= b OR b = 'angle' }
  427. 25 { SELECT * FROM t3 WHERE b = 'from' }
  428. 26 { SELECT * FROM t3 WHERE b = 4543.266482 }
  429. 27 { SELECT * FROM t3 WHERE c < 3282306647 }
  430. 28 { SELECT * FROM t3 WHERE c IS NULL AND b >= c }
  431. 29 { SELECT * FROM t3 WHERE b > 0.4811174502 AND c = 'other' AND 'viewed' > a }
  432. 30 { SELECT * FROM t3 WHERE c = 'peak' }
  433. 31 { SELECT * FROM t3 WHERE c < 53594.08128 OR c <= b }
  434. 32 { SELECT * FROM t3 WHERE 'writings' <= b }
  435. 33 { SELECT * FROM t3 WHERE 2643383279 = b OR c < b AND b <= 3282306647 }
  436. 34 { SELECT * FROM t3 WHERE a IS NULL }
  437. 35 { SELECT * FROM t3 WHERE 'writings' = a OR b = 378678316.5 }
  438. 36 { SELECT * FROM t3 WHERE 'and' >= c }
  439. 37 { SELECT * FROM t3 WHERE c < 'from' }
  440. 38 { SELECT * FROM t3 WHERE 'his' < c OR b < b }
  441. 39 { SELECT * FROM t3 WHERE 53594.08128 = b AND c >= b }
  442. 40 { SELECT * FROM t3 WHERE 'unalike' < c AND 'are' >= c AND a <= b }
  443. 41 { SELECT * FROM t3 WHERE b >= 4543.266482 OR 'Alpine' > a OR 271.2019091 <= a }
  444. 42 { SELECT * FROM t3 WHERE b = c }
  445. 43 { SELECT * FROM t3 WHERE c > a AND b < 'all' }
  446. 44 { SELECT * FROM t3 WHERE c BETWEEN 'hills' AND 'snapshots' AND c <= 'the' OR c = a }
  447. 45 { SELECT * FROM t3 WHERE b > c AND c >= 'hills' }
  448. 46 { SELECT * FROM t3 WHERE b > 'or' OR a <= 'hills' OR c IS NULL }
  449. 47 { SELECT * FROM t3 WHERE c > b OR b BETWEEN 1339.360726 AND 'onlookers' OR 1415926535 >= b }
  450. 48 { SELECT * FROM t3 WHERE a IS NULL }
  451. 49 { SELECT * FROM t3 WHERE a > 'other' }
  452. 50 { SELECT * FROM t3 WHERE 'the' <= c AND a <= c }
  453. 51 { SELECT * FROM t3 WHERE 346.0348610 = a AND c = b }
  454. 52 { SELECT * FROM t3 WHERE c BETWEEN 50.58223172 AND 'same' AND a < b }
  455. 53 { SELECT * FROM t3 WHERE 'Alpine' <= b AND c >= 'angle' OR b <= 271.2019091 }
  456. 54 { SELECT * FROM t3 WHERE a < a AND 1415926535 > b }
  457. 55 { SELECT * FROM t3 WHERE c > a AND 'have' >= c }
  458. 56 { SELECT * FROM t3 WHERE b <= b AND c > b }
  459. 57 { SELECT * FROM t3 WHERE a IS NULL AND c <= c }
  460. 58 { SELECT * FROM t3 WHERE b < c OR b = c }
  461. 59 { SELECT * FROM t3 WHERE c < b AND b >= 'it' }
  462. 60 { SELECT * FROM t3 WHERE a = b AND a <= b OR b >= a }
  463. 61 { SELECT * FROM t3 WHERE b = c }
  464. 62 { SELECT * FROM t3 WHERE c BETWEEN 'the' AND 271.2019091 OR c <= 3282306647 AND c >= b }
  465. 63 { SELECT * FROM t3 WHERE c >= c AND c < 'writings' }
  466. 64 { SELECT * FROM t3 WHERE c <= 3282306647 AND b > a OR 'unalike' <= a }
  467. 65 { SELECT * FROM t3 WHERE a > c }
  468. 66 { SELECT * FROM t3 WHERE c = 'it' OR b >= b }
  469. 67 { SELECT * FROM t3 WHERE c = a OR b < c }
  470. 68 { SELECT * FROM t3 WHERE b > a }
  471. 69 { SELECT * FROM t3 WHERE a < b OR a > 4543.266482 OR 'same' = b }
  472. 70 { SELECT * FROM t3 WHERE c < c OR b <= c OR a <= b }
  473. 71 { SELECT * FROM t3 WHERE c > a }
  474. 72 { SELECT * FROM t3 WHERE c > b }
  475. 73 { SELECT * FROM t3 WHERE b <= a }
  476. 74 { SELECT * FROM t3 WHERE 3282306647 < b AND a >= 'or' OR a >= 378678316.5 }
  477. 75 { SELECT * FROM t3 WHERE 50.58223172 <= c OR c = c AND b < b }
  478. 76 { SELECT * FROM t3 WHERE 'and' < b OR b < c OR c > 1339.360726 }
  479. 77 { SELECT * FROM t3 WHERE b <= c }
  480. 78 { SELECT * FROM t3 WHERE 'in' <= c }
  481. 79 { SELECT * FROM t3 WHERE c <= b AND a > a AND c < b }
  482. 80 { SELECT * FROM t3 WHERE 'over' < b }
  483. 81 { SELECT * FROM t3 WHERE b >= b OR b < c OR a < b }
  484. 82 { SELECT * FROM t3 WHERE 'towering' <= b OR 'towering' = a AND c > b }
  485. 83 { SELECT * FROM t3 WHERE 'peak' = a OR b BETWEEN 2643383279 AND 'the' }
  486. 84 { SELECT * FROM t3 WHERE 'an' < c AND c > 'the' AND c IS NULL }
  487. 85 { SELECT * FROM t3 WHERE a <= 'sometimes' AND a BETWEEN 'unalike' AND 1339.360726 }
  488. 86 { SELECT * FROM t3 WHERE 1339.360726 < c AND c IS NULL }
  489. 87 { SELECT * FROM t3 WHERE b > 'the' }
  490. 88 { SELECT * FROM t3 WHERE 'and' = a }
  491. 89 { SELECT * FROM t3 WHERE b >= b }
  492. 90 { SELECT * FROM t3 WHERE b >= 8979323846 }
  493. 91 { SELECT * FROM t3 WHERE c <= a }
  494. 92 { SELECT * FROM t3 WHERE a BETWEEN 'have' AND 'light' OR a > b OR a >= 378678316.5 }
  495. 93 { SELECT * FROM t3 WHERE c > 3282306647 }
  496. 94 { SELECT * FROM t3 WHERE b > c }
  497. 95 { SELECT * FROM t3 WHERE b >= a AND 'villages' > a AND b >= c }
  498. 96 { SELECT * FROM t3 WHERE 'angle' > a }
  499. 97 { SELECT * FROM t3 WHERE 'paintings' >= a }
  500. 98 { SELECT * FROM t3 WHERE 'or' >= c }
  501. 99 { SELECT * FROM t3 WHERE c < b }
  502. 101 { SELECT * FROM t3, t4 WHERE f < 'sometimes' OR 'over' <= g AND h < 1415926535 }
  503. 102 { SELECT * FROM t3, t4 WHERE h >= 'from' AND h < 6939937510 OR g > h }
  504. 103 { SELECT * FROM t3, t4 WHERE c <= h AND g = h AND c >= 'all' }
  505. 104 { SELECT * FROM t3, t4 WHERE c = a }
  506. 105 { SELECT * FROM t3, t4 WHERE 'of' >= h }
  507. 106 { SELECT * FROM t3, t4 WHERE f >= b AND a < g AND h < 'and' }
  508. 107 { SELECT * FROM t3, t4 WHERE f <= 8628034825 AND 0938446095 >= b }
  509. 108 { SELECT * FROM t3, t4 WHERE a < 'the' }
  510. 109 { SELECT * FROM t3, t4 WHERE f = 'sometimes' OR b < 'of' }
  511. 110 { SELECT * FROM t3, t4 WHERE c IS NULL }
  512. 111 { SELECT * FROM t3, t4 WHERE 'have' = b OR g <= 346.0348610 }
  513. 112 { SELECT * FROM t3, t4 WHERE f > b AND b <= h }
  514. 113 { SELECT * FROM t3, t4 WHERE f > c OR 'the' = a OR 50.58223172 = a }
  515. 114 { SELECT * FROM t3, t4 WHERE 2643383279 <= a AND c = a }
  516. 115 { SELECT * FROM t3, t4 WHERE h >= b AND 'it' <= b }
  517. 116 { SELECT * FROM t3, t4 WHERE g BETWEEN 'from' AND 'peak' }
  518. 117 { SELECT * FROM t3, t4 WHERE 'their' > a AND g > b AND f <= c }
  519. 118 { SELECT * FROM t3, t4 WHERE h = 5028841971 AND 'unalike' <= f }
  520. 119 { SELECT * FROM t3, t4 WHERE c IS NULL AND a = 3282306647 OR a <= 'Alpine' }
  521. 120 { SELECT * FROM t3, t4 WHERE 'sometimes' <= f OR 8214808651 >= a AND b <= 53594.08128 }
  522. 121 { SELECT * FROM t3, t4 WHERE 6939937510 <= f OR c < f OR 'sometimes' = c }
  523. 122 { SELECT * FROM t3, t4 WHERE b < 'onlookers' AND 'paintings' = g AND c <= h }
  524. 123 { SELECT * FROM t3, t4 WHERE a BETWEEN 'all' AND 'from' OR c > 346.0348610 }
  525. 124 { SELECT * FROM t3, t4 WHERE 'from' <= b OR a BETWEEN 53594.08128 AND 'their' AND c > a }
  526. 125 { SELECT * FROM t3, t4 WHERE h = 2643383279 }
  527. 126 { SELECT * FROM t3, t4 WHERE a <= 'the' }
  528. 127 { SELECT * FROM t3, t4 WHERE h <= c }
  529. 128 { SELECT * FROM t3, t4 WHERE g <= 346.0348610 AND 66.59334461 >= f AND f <= f }
  530. 129 { SELECT * FROM t3, t4 WHERE g >= c OR 'in' < b OR b > g }
  531. 130 { SELECT * FROM t3, t4 WHERE 'over' > g AND b BETWEEN 'unalike' AND 'remarkably' }
  532. 131 { SELECT * FROM t3, t4 WHERE h <= 2847564.823 }
  533. 132 { SELECT * FROM t3, t4 WHERE h <= 'remarkably' AND 4543.266482 > h }
  534. 133 { SELECT * FROM t3, t4 WHERE a >= c AND 'it' > g AND c < c }
  535. 134 { SELECT * FROM t3, t4 WHERE h <= 66.59334461 AND b > 3421170679 }
  536. 135 { SELECT * FROM t3, t4 WHERE h < 'are' OR f BETWEEN 0938446095 AND 'are' OR b = b }
  537. 136 { SELECT * FROM t3, t4 WHERE h = a OR 66.59334461 <= f }
  538. 137 { SELECT * FROM t3, t4 WHERE f > 'of' OR h <= h OR a = f }
  539. 138 { SELECT * FROM t3, t4 WHERE 'other' >= g }
  540. 139 { SELECT * FROM t3, t4 WHERE b <= 3421170679 }
  541. 140 { SELECT * FROM t3, t4 WHERE 'all' = f AND 4543.266482 = b OR f BETWEEN 'and' AND 'angle' }
  542. 141 { SELECT * FROM t3, t4 WHERE 'light' = f OR h BETWEEN 'remarkably' AND 1415926535 }
  543. 142 { SELECT * FROM t3, t4 WHERE 'hills' = f OR 'the' >= f }
  544. 143 { SELECT * FROM t3, t4 WHERE a > 346.0348610 }
  545. 144 { SELECT * FROM t3, t4 WHERE 5028841971 = h }
  546. 145 { SELECT * FROM t3, t4 WHERE b >= c AND 'the' >= g OR 45.64856692 <= g }
  547. 146 { SELECT * FROM t3, t4 WHERE c < 5028841971 }
  548. 147 { SELECT * FROM t3, t4 WHERE a > a }
  549. 148 { SELECT * FROM t3, t4 WHERE c = 'snapshots' }
  550. 149 { SELECT * FROM t3, t4 WHERE h > 1339.360726 AND 'and' > c }
  551. 150 { SELECT * FROM t3, t4 WHERE 'and' > g OR 'sometimes' = c }
  552. 151 { SELECT * FROM t3, t4 WHERE g >= 'the' AND b >= 'onlookers' }
  553. 152 { SELECT * FROM t3, t4 WHERE h BETWEEN 'other' AND 2643383279 }
  554. 153 { SELECT * FROM t3, t4 WHERE 'it' = b }
  555. 154 { SELECT * FROM t3, t4 WHERE f = c OR c BETWEEN 'and' AND 0.4811174502 }
  556. 155 { SELECT * FROM t3, t4 WHERE b <= 'sometimes' OR c <= 0938446095 }
  557. 156 { SELECT * FROM t3, t4 WHERE 'and' <= b }
  558. 157 { SELECT * FROM t3, t4 WHERE g > a AND f = 'the' AND b < a }
  559. 158 { SELECT * FROM t3, t4 WHERE a < 'an' }
  560. 159 { SELECT * FROM t3, t4 WHERE a BETWEEN 'his' AND 'same' OR 8628034825 > f }
  561. 160 { SELECT * FROM t3, t4 WHERE b = 'peak' }
  562. 161 { SELECT * FROM t3, t4 WHERE f IS NULL AND a >= h }
  563. 162 { SELECT * FROM t3, t4 WHERE a IS NULL OR 2643383279 = c }
  564. 163 { SELECT * FROM t3, t4 WHERE b >= 5028841971 AND f < c AND a IS NULL }
  565. 164 { SELECT * FROM t3, t4 WHERE a >= g }
  566. 165 { SELECT * FROM t3, t4 WHERE c IS NULL }
  567. 166 { SELECT * FROM t3, t4 WHERE h >= h }
  568. 167 { SELECT * FROM t3, t4 WHERE 'over' <= h }
  569. 168 { SELECT * FROM t3, t4 WHERE b < 4543.266482 OR b = 2643383279 OR 8628034825 < b }
  570. 169 { SELECT * FROM t3, t4 WHERE g >= 6939937510 }
  571. 170 { SELECT * FROM t3, t4 WHERE 'or' < a OR b < g }
  572. 171 { SELECT * FROM t3, t4 WHERE h < 'hills' OR 'and' > g }
  573. 172 { SELECT * FROM t3, t4 WHERE 'from' > f OR f <= f }
  574. 173 { SELECT * FROM t3, t4 WHERE 'viewed' > b AND f < c }
  575. 174 { SELECT * FROM t3, t4 WHERE 'of' <= a }
  576. 175 { SELECT * FROM t3, t4 WHERE f > 0938446095 }
  577. 176 { SELECT * FROM t3, t4 WHERE a = g }
  578. 177 { SELECT * FROM t3, t4 WHERE g >= b AND f BETWEEN 'peak' AND 'and' }
  579. 178 { SELECT * FROM t3, t4 WHERE g = a AND 'it' > f }
  580. 179 { SELECT * FROM t3, t4 WHERE a <= b OR 'from' > f }
  581. 180 { SELECT * FROM t3, t4 WHERE f < 'and' }
  582. 181 { SELECT * FROM t3, t4 WHERE 6939937510 < b OR 'sometimes' < h }
  583. 182 { SELECT * FROM t3, t4 WHERE f > g AND f < 'peak' }
  584. 183 { SELECT * FROM t3, t4 WHERE a <= 53594.08128 AND c <= f AND f >= c }
  585. 184 { SELECT * FROM t3, t4 WHERE f = c OR 'it' > b OR g BETWEEN 'the' AND 'all' }
  586. 185 { SELECT * FROM t3, t4 WHERE c <= g OR a = h }
  587. 186 { SELECT * FROM t3, t4 WHERE 'same' = b OR c >= 2643383279 }
  588. 187 { SELECT * FROM t3, t4 WHERE h <= g OR c > 66.59334461 OR a <= f }
  589. 188 { SELECT * FROM t3, t4 WHERE b < c AND f = 'writings' }
  590. 189 { SELECT * FROM t3, t4 WHERE b < a }
  591. 190 { SELECT * FROM t3, t4 WHERE c >= f OR c = 'and' }
  592. 191 { SELECT * FROM t3, t4 WHERE f >= 'peak' AND g > f AND h > g }
  593. 192 { SELECT * FROM t3, t4 WHERE a >= 8979323846 AND 'same' > b OR c = 'and' }
  594. 193 { SELECT * FROM t3, t4 WHERE c >= g OR 'writings' >= c AND b = 'all' }
  595. 194 { SELECT * FROM t3, t4 WHERE 'remarkably' < g }
  596. 195 { SELECT * FROM t3, t4 WHERE a BETWEEN 'or' AND 'paintings' AND g <= f }
  597. 196 { SELECT * FROM t3, t4 WHERE 0938446095 > b OR g <= a OR h > b }
  598. 197 { SELECT * FROM t3, t4 WHERE g = 2643383279 AND f = g }
  599. 198 { SELECT * FROM t3, t4 WHERE g < 8979323846 }
  600. 199 { SELECT * FROM t3, t4 WHERE 'are' <= b }
  601. 200 { SELECT * FROM t3, t4 WHERE (a=1415926535 AND f=8628034825)
  602. OR (a=6939937510 AND f=2643383279) }
  603. 201 { SELECT * FROM t3, t4, t5, t6
  604. WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t!=5)
  605. OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t=123456) }
  606. 202 { SELECT * FROM t3, t4, t5, t6
  607. WHERE (a=1415926535 AND f=8628034825 AND s!='hello' AND t==5)
  608. OR (a=6939937510 AND f=2643383279 AND s='tab-t5' AND t!=123456) }
  609. } {
  610. do_test where8-4.$A.$B.1 {
  611. unset -nocomplain R
  612. set R [execsql $sql]
  613. if {![info exists results($B)]} {
  614. set results($B) $R
  615. }
  616. list
  617. } {}
  618. do_test where8-4.$A.$B.2 { lsort $R } [lsort $results($B)]
  619. }
  620. incr A
  621. }
  622. catch {unset results}
  623. catch {unset A}
  624. catch {unset B}
  625. # At one point the following tests provoked an invalid write error (writing
  626. # to memory that had already been freed). It was not possible to demonstrate
  627. # that this bug could cause a query to return bad data.
  628. #
  629. do_test where8-5.1 {
  630. db close
  631. sqlite3 db test.db
  632. sqlite3_db_config_lookaside db 0 0 0
  633. execsql {
  634. CREATE TABLE tA(
  635. a, b, c, d, e, f, g, h,
  636. i, j, k, l, m, n, o, p
  637. );
  638. }
  639. execsql {
  640. SELECT * FROM tA WHERE
  641. a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND
  642. i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND
  643. (p = 1 OR p = 2 OR p = 3)
  644. }
  645. } {}
  646. do_test where8-5.2 {
  647. execsql {
  648. SELECT * FROM tA WHERE
  649. a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND
  650. i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND
  651. (p = 1 OR p = 2 OR p = 3) AND n=6 AND o=7
  652. }
  653. } {}
  654. do_test where8-5.3 {
  655. execsql {
  656. INSERT INTO tA VALUES(1, 2, 3, 4, 5, 6, 7, 8, 1, 2, 3, 4, 5, 6, 7, 8);
  657. CREATE UNIQUE INDEX tAI ON tA(p);
  658. CREATE TABLE tB(x);
  659. INSERT INTO tB VALUES('x');
  660. }
  661. execsql {
  662. SELECT a, x FROM tA LEFT JOIN tB ON (
  663. a=1 AND b=2 AND c=3 AND d=4 AND e=5 AND f=6 AND g=7 AND h=8 AND
  664. i=1 AND j=2 AND k=3 AND l=4 AND m=5 AND n=6 AND o=7 AND
  665. (p = 1 OR p = 2 OR p = 3)
  666. )
  667. }
  668. } {1 {}}
  669. finish_test