PageRenderTime 44ms CodeModel.GetById 18ms RepoModel.GetById 0ms app.codeStats 0ms

/test/vtab6.test

https://bitbucket.org/aware/sqlite
Unknown | 573 lines | 532 code | 41 blank | 0 comment | 0 complexity | 78f376fa46708a1d1e24b46857c49691 MD5 | raw file
  1. # 2002 May 24
  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.
  12. #
  13. # This file implements tests for joins, including outer joins involving
  14. # virtual tables. The test cases in this file are copied from the file
  15. # join.test, and some of the comments still reflect that.
  16. #
  17. # $Id:$
  18. set testdir [file dirname $argv0]
  19. source $testdir/tester.tcl
  20. ifcapable !vtab {
  21. finish_test
  22. return
  23. }
  24. register_echo_module [sqlite3_connection_pointer db]
  25. execsql {
  26. CREATE TABLE real_t1(a,b,c);
  27. CREATE TABLE real_t2(b,c,d);
  28. CREATE TABLE real_t3(c,d,e);
  29. CREATE TABLE real_t4(d,e,f);
  30. CREATE TABLE real_t5(a INTEGER PRIMARY KEY);
  31. CREATE TABLE real_t6(a INTEGER);
  32. CREATE TABLE real_t7 (x, y);
  33. CREATE TABLE real_t8 (a integer primary key, b);
  34. CREATE TABLE real_t9(a INTEGER PRIMARY KEY, b);
  35. CREATE TABLE real_t10(x INTEGER PRIMARY KEY, y);
  36. CREATE TABLE real_t11(p INTEGER PRIMARY KEY, q);
  37. CREATE TABLE real_t12(a,b);
  38. CREATE TABLE real_t13(b,c);
  39. CREATE TABLE real_t21(a,b,c);
  40. CREATE TABLE real_t22(p,q);
  41. }
  42. foreach t [list t1 t2 t3 t4 t5 t6 t7 t8 t9 t10 t11 t12 t13 t21 t22] {
  43. execsql "CREATE VIRTUAL TABLE $t USING echo(real_$t)"
  44. }
  45. do_test vtab6-1.1 {
  46. execsql {
  47. INSERT INTO t1 VALUES(1,2,3);
  48. INSERT INTO t1 VALUES(2,3,4);
  49. INSERT INTO t1 VALUES(3,4,5);
  50. SELECT * FROM t1;
  51. }
  52. } {1 2 3 2 3 4 3 4 5}
  53. do_test vtab6-1.2 {
  54. execsql {
  55. INSERT INTO t2 VALUES(1,2,3);
  56. INSERT INTO t2 VALUES(2,3,4);
  57. INSERT INTO t2 VALUES(3,4,5);
  58. SELECT * FROM t2;
  59. }
  60. } {1 2 3 2 3 4 3 4 5}
  61. do_test vtab6-1.3 {
  62. execsql2 {
  63. SELECT * FROM t1 NATURAL JOIN t2;
  64. }
  65. } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
  66. do_test vtab6-1.3.1 {
  67. execsql2 {
  68. SELECT * FROM t2 NATURAL JOIN t1;
  69. }
  70. } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
  71. do_test vtab6-1.3.2 {
  72. execsql2 {
  73. SELECT * FROM t2 AS x NATURAL JOIN t1;
  74. }
  75. } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
  76. do_test vtab6-1.3.3 {
  77. execsql2 {
  78. SELECT * FROM t2 NATURAL JOIN t1 AS y;
  79. }
  80. } {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
  81. do_test vtab6-1.3.4 {
  82. execsql {
  83. SELECT b FROM t1 NATURAL JOIN t2;
  84. }
  85. } {2 3}
  86. do_test vtab6-1.4.1 {
  87. execsql2 {
  88. SELECT * FROM t1 INNER JOIN t2 USING(b,c);
  89. }
  90. } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
  91. do_test vtab6-1.4.2 {
  92. execsql2 {
  93. SELECT * FROM t1 AS x INNER JOIN t2 USING(b,c);
  94. }
  95. } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
  96. do_test vtab6-1.4.3 {
  97. execsql2 {
  98. SELECT * FROM t1 INNER JOIN t2 AS y USING(b,c);
  99. }
  100. } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
  101. do_test vtab6-1.4.4 {
  102. execsql2 {
  103. SELECT * FROM t1 AS x INNER JOIN t2 AS y USING(b,c);
  104. }
  105. } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
  106. do_test vtab6-1.4.5 {
  107. execsql {
  108. SELECT b FROM t1 JOIN t2 USING(b);
  109. }
  110. } {2 3}
  111. do_test vtab6-1.5 {
  112. execsql2 {
  113. SELECT * FROM t1 INNER JOIN t2 USING(b);
  114. }
  115. } {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5}
  116. do_test vtab6-1.6 {
  117. execsql2 {
  118. SELECT * FROM t1 INNER JOIN t2 USING(c);
  119. }
  120. } {a 1 b 2 c 3 b 2 d 4 a 2 b 3 c 4 b 3 d 5}
  121. do_test vtab6-1.7 {
  122. execsql2 {
  123. SELECT * FROM t1 INNER JOIN t2 USING(c,b);
  124. }
  125. } {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
  126. do_test vtab6-1.8 {
  127. execsql {
  128. SELECT * FROM t1 NATURAL CROSS JOIN t2;
  129. }
  130. } {1 2 3 4 2 3 4 5}
  131. do_test vtab6-1.9 {
  132. execsql {
  133. SELECT * FROM t1 CROSS JOIN t2 USING(b,c);
  134. }
  135. } {1 2 3 4 2 3 4 5}
  136. do_test vtab6-1.10 {
  137. execsql {
  138. SELECT * FROM t1 NATURAL INNER JOIN t2;
  139. }
  140. } {1 2 3 4 2 3 4 5}
  141. do_test vtab6-1.11 {
  142. execsql {
  143. SELECT * FROM t1 INNER JOIN t2 USING(b,c);
  144. }
  145. } {1 2 3 4 2 3 4 5}
  146. do_test vtab6-1.12 {
  147. execsql {
  148. SELECT * FROM t1 natural inner join t2;
  149. }
  150. } {1 2 3 4 2 3 4 5}
  151. ifcapable subquery {
  152. do_test vtab6-1.13 {
  153. execsql2 {
  154. SELECT * FROM t1 NATURAL JOIN
  155. (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3
  156. }
  157. } {a 1 b 2 c 3 d 4 e 5}
  158. do_test vtab6-1.14 {
  159. execsql2 {
  160. SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx'
  161. NATURAL JOIN t1
  162. }
  163. } {c 3 d 4 e 5 a 1 b 2}
  164. }
  165. do_test vtab6-1.15 {
  166. execsql {
  167. INSERT INTO t3 VALUES(2,3,4);
  168. INSERT INTO t3 VALUES(3,4,5);
  169. INSERT INTO t3 VALUES(4,5,6);
  170. SELECT * FROM t3;
  171. }
  172. } {2 3 4 3 4 5 4 5 6}
  173. do_test vtab6-1.16 {
  174. execsql {
  175. SELECT * FROM t1 natural join t2 natural join t3;
  176. }
  177. } {1 2 3 4 5 2 3 4 5 6}
  178. do_test vtab6-1.17 {
  179. execsql2 {
  180. SELECT * FROM t1 natural join t2 natural join t3;
  181. }
  182. } {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6}
  183. do_test vtab6-1.18 {
  184. execsql {
  185. INSERT INTO t4 VALUES(2,3,4);
  186. INSERT INTO t4 VALUES(3,4,5);
  187. INSERT INTO t4 VALUES(4,5,6);
  188. SELECT * FROM t4;
  189. }
  190. } {2 3 4 3 4 5 4 5 6}
  191. do_test vtab6-1.19.1 {
  192. execsql {
  193. SELECT * FROM t1 natural join t2 natural join t4;
  194. }
  195. } {1 2 3 4 5 6}
  196. do_test vtab6-1.19.2 {
  197. execsql2 {
  198. SELECT * FROM t1 natural join t2 natural join t4;
  199. }
  200. } {a 1 b 2 c 3 d 4 e 5 f 6}
  201. do_test vtab6-1.20 {
  202. execsql {
  203. SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1
  204. }
  205. } {1 2 3 4 5}
  206. do_test vtab6-2.1 {
  207. execsql {
  208. SELECT * FROM t1 NATURAL LEFT JOIN t2;
  209. }
  210. } {1 2 3 4 2 3 4 5 3 4 5 {}}
  211. do_test vtab6-2.2 {
  212. execsql {
  213. SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
  214. }
  215. } {1 2 3 {} 2 3 4 1 3 4 5 2}
  216. do_test vtab6-2.3 {
  217. catchsql {
  218. SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
  219. }
  220. } {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
  221. do_test vtab6-2.4 {
  222. execsql {
  223. SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
  224. }
  225. } {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
  226. do_test vtab6-2.5 {
  227. execsql {
  228. SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
  229. }
  230. } {2 3 4 {} {} {} 3 4 5 1 2 3}
  231. do_test vtab6-2.6 {
  232. execsql {
  233. SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1
  234. }
  235. } {1 2 3 {} {} {} 2 3 4 {} {} {}}
  236. do_test vtab6-3.1 {
  237. catchsql {
  238. SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b;
  239. }
  240. } {1 {a NATURAL join may not have an ON or USING clause}}
  241. do_test vtab6-3.2 {
  242. catchsql {
  243. SELECT * FROM t1 NATURAL JOIN t2 USING(b);
  244. }
  245. } {1 {a NATURAL join may not have an ON or USING clause}}
  246. do_test vtab6-3.3 {
  247. catchsql {
  248. SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b);
  249. }
  250. } {1 {cannot have both ON and USING clauses in the same join}}
  251. do_test vtab6-3.4 {
  252. catchsql {
  253. SELECT * FROM t1 JOIN t2 USING(a);
  254. }
  255. } {1 {cannot join using column a - column not present in both tables}}
  256. do_test vtab6-3.5 {
  257. catchsql { SELECT * FROM t1 USING(a) }
  258. } {1 {a JOIN clause is required before USING}}
  259. do_test vtab6-3.6 {
  260. catchsql {
  261. SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
  262. }
  263. } {1 {no such column: t3.a}}
  264. do_test vtab6-3.7 {
  265. catchsql {
  266. SELECT * FROM t1 INNER OUTER JOIN t2;
  267. }
  268. } {1 {unknown or unsupported join type: INNER OUTER}}
  269. do_test vtab6-3.7 {
  270. catchsql {
  271. SELECT * FROM t1 LEFT BOGUS JOIN t2;
  272. }
  273. } {1 {unknown or unsupported join type: LEFT BOGUS}}
  274. do_test vtab6-4.1 {
  275. execsql {
  276. BEGIN;
  277. INSERT INTO t6 VALUES(NULL);
  278. INSERT INTO t6 VALUES(NULL);
  279. INSERT INTO t6 SELECT * FROM t6;
  280. INSERT INTO t6 SELECT * FROM t6;
  281. INSERT INTO t6 SELECT * FROM t6;
  282. INSERT INTO t6 SELECT * FROM t6;
  283. INSERT INTO t6 SELECT * FROM t6;
  284. INSERT INTO t6 SELECT * FROM t6;
  285. COMMIT;
  286. }
  287. execsql {
  288. SELECT * FROM t6 NATURAL JOIN t5;
  289. }
  290. } {}
  291. do_test vtab6-4.2 {
  292. execsql {
  293. SELECT * FROM t6, t5 WHERE t6.a<t5.a;
  294. }
  295. } {}
  296. do_test vtab6-4.3 {
  297. execsql {
  298. SELECT * FROM t6, t5 WHERE t6.a>t5.a;
  299. }
  300. } {}
  301. do_test vtab6-4.4 {
  302. execsql {
  303. UPDATE t6 SET a='xyz';
  304. SELECT * FROM t6 NATURAL JOIN t5;
  305. }
  306. } {}
  307. do_test vtab6-4.6 {
  308. execsql {
  309. SELECT * FROM t6, t5 WHERE t6.a<t5.a;
  310. }
  311. } {}
  312. do_test vtab6-4.7 {
  313. execsql {
  314. SELECT * FROM t6, t5 WHERE t6.a>t5.a;
  315. }
  316. } {}
  317. do_test vtab6-4.8 {
  318. execsql {
  319. UPDATE t6 SET a=1;
  320. SELECT * FROM t6 NATURAL JOIN t5;
  321. }
  322. } {}
  323. do_test vtab6-4.9 {
  324. execsql {
  325. SELECT * FROM t6, t5 WHERE t6.a<t5.a;
  326. }
  327. } {}
  328. do_test vtab6-4.10 {
  329. execsql {
  330. SELECT * FROM t6, t5 WHERE t6.a>t5.a;
  331. }
  332. } {}
  333. # A test for ticket #247.
  334. #
  335. do_test vtab6-7.1 {
  336. execsql {
  337. INSERT INTO t7 VALUES ("pa1", 1);
  338. INSERT INTO t7 VALUES ("pa2", NULL);
  339. INSERT INTO t7 VALUES ("pa3", NULL);
  340. INSERT INTO t7 VALUES ("pa4", 2);
  341. INSERT INTO t7 VALUES ("pa30", 131);
  342. INSERT INTO t7 VALUES ("pa31", 130);
  343. INSERT INTO t7 VALUES ("pa28", NULL);
  344. INSERT INTO t8 VALUES (1, "pa1");
  345. INSERT INTO t8 VALUES (2, "pa4");
  346. INSERT INTO t8 VALUES (3, NULL);
  347. INSERT INTO t8 VALUES (4, NULL);
  348. INSERT INTO t8 VALUES (130, "pa31");
  349. INSERT INTO t8 VALUES (131, "pa30");
  350. SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
  351. }
  352. } {1 999 999 2 131 130 999}
  353. # Make sure a left join where the right table is really a view that
  354. # is itself a join works right. Ticket #306.
  355. #
  356. ifcapable view {
  357. do_test vtab6-8.1 {
  358. execsql {
  359. BEGIN;
  360. INSERT INTO t9 VALUES(1,11);
  361. INSERT INTO t9 VALUES(2,22);
  362. INSERT INTO t10 VALUES(1,2);
  363. INSERT INTO t10 VALUES(3,3);
  364. INSERT INTO t11 VALUES(2,111);
  365. INSERT INTO t11 VALUES(3,333);
  366. CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p;
  367. COMMIT;
  368. SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x );
  369. }
  370. } {1 11 1 111 2 22 {} {}}
  371. ifcapable subquery {
  372. do_test vtab6-8.2 {
  373. execsql {
  374. SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p)
  375. ON( a=x);
  376. }
  377. } {1 11 1 111 2 22 {} {}}
  378. }
  379. do_test vtab6-8.3 {
  380. execsql {
  381. SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );
  382. }
  383. } {1 111 1 11 3 333 {} {}}
  384. } ;# ifcapable view
  385. # Ticket #350 describes a scenario where LEFT OUTER JOIN does not
  386. # function correctly if the right table in the join is really
  387. # subquery.
  388. #
  389. # To test the problem, we generate the same LEFT OUTER JOIN in two
  390. # separate selects but with on using a subquery and the other calling
  391. # the table directly. Then connect the two SELECTs using an EXCEPT.
  392. # Both queries should generate the same results so the answer should
  393. # be an empty set.
  394. #
  395. ifcapable compound {
  396. do_test vtab6-9.1 {
  397. execsql {
  398. BEGIN;
  399. INSERT INTO t12 VALUES(1,11);
  400. INSERT INTO t12 VALUES(2,22);
  401. INSERT INTO t13 VALUES(22,222);
  402. COMMIT;
  403. }
  404. } {}
  405. ifcapable subquery {
  406. do_test vtab6-9.1.1 {
  407. execsql {
  408. SELECT * FROM t12 NATURAL LEFT JOIN t13
  409. EXCEPT
  410. SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0);
  411. }
  412. } {}
  413. }
  414. ifcapable view {
  415. do_test vtab6-9.2 {
  416. execsql {
  417. CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0;
  418. SELECT * FROM t12 NATURAL LEFT JOIN t13
  419. EXCEPT
  420. SELECT * FROM t12 NATURAL LEFT JOIN v13;
  421. }
  422. } {}
  423. } ;# ifcapable view
  424. } ;# ifcapable compound
  425. ifcapable subquery {
  426. do_test vtab6-10.1 {
  427. execsql {
  428. CREATE INDEX i22 ON real_t22(q);
  429. SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q=
  430. (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1);
  431. }
  432. } {}
  433. } ;# ifcapable subquery
  434. do_test vtab6-11.1.0 {
  435. execsql {
  436. CREATE TABLE ab_r(a, b);
  437. CREATE TABLE bc_r(b, c);
  438. CREATE VIRTUAL TABLE ab USING echo(ab_r);
  439. CREATE VIRTUAL TABLE bc USING echo(bc_r);
  440. INSERT INTO ab VALUES(1, 2);
  441. INSERT INTO bc VALUES(2, 3);
  442. }
  443. } {}
  444. do_test vtab6-11.1.1 {
  445. execsql {
  446. SELECT a, b, c FROM ab NATURAL JOIN bc;
  447. }
  448. } {1 2 3}
  449. do_test vtab6-11.1.2 {
  450. execsql {
  451. SELECT a, b, c FROM bc NATURAL JOIN ab;
  452. }
  453. } {1 2 3}
  454. set ::echo_module_cost 1.0
  455. do_test vtab6-11.1.3 {
  456. execsql {
  457. SELECT a, b, c FROM ab NATURAL JOIN bc;
  458. }
  459. } {1 2 3}
  460. do_test vtab6-11.1.4 {
  461. execsql {
  462. SELECT a, b, c FROM bc NATURAL JOIN ab;
  463. }
  464. } {1 2 3}
  465. do_test vtab6-11.2.0 {
  466. execsql {
  467. CREATE INDEX ab_i ON ab_r(b);
  468. CREATE INDEX bc_i ON bc_r(b);
  469. }
  470. } {}
  471. unset ::echo_module_cost
  472. do_test vtab6-11.2.1 {
  473. execsql {
  474. SELECT a, b, c FROM ab NATURAL JOIN bc;
  475. }
  476. } {1 2 3}
  477. do_test vtab6-11.2.2 {
  478. execsql {
  479. SELECT a, b, c FROM bc NATURAL JOIN ab;
  480. }
  481. } {1 2 3}
  482. set ::echo_module_cost 1.0
  483. do_test vtab6-11.2.3 {
  484. execsql {
  485. SELECT a, b, c FROM ab NATURAL JOIN bc;
  486. }
  487. } {1 2 3}
  488. do_test vtab6-11.2.4 {
  489. execsql {
  490. SELECT a, b, c FROM bc NATURAL JOIN ab;
  491. }
  492. } {1 2 3}
  493. unset ::echo_module_cost
  494. db close
  495. sqlite3 db test.db
  496. register_echo_module [sqlite3_connection_pointer db]
  497. do_test vtab6-11.3.1 {
  498. execsql {
  499. SELECT a, b, c FROM ab NATURAL JOIN bc;
  500. }
  501. } {1 2 3}
  502. do_test vtab6-11.3.2 {
  503. execsql {
  504. SELECT a, b, c FROM bc NATURAL JOIN ab;
  505. }
  506. } {1 2 3}
  507. set ::echo_module_cost 1.0
  508. do_test vtab6-11.3.3 {
  509. execsql {
  510. SELECT a, b, c FROM ab NATURAL JOIN bc;
  511. }
  512. } {1 2 3}
  513. do_test vtab6-11.3.4 {
  514. execsql {
  515. SELECT a, b, c FROM bc NATURAL JOIN ab;
  516. }
  517. } {1 2 3}
  518. unset ::echo_module_cost
  519. set ::echo_module_ignore_usable 1
  520. db cache flush
  521. do_test vtab6-11.4.1 {
  522. catchsql {
  523. SELECT a, b, c FROM ab NATURAL JOIN bc;
  524. }
  525. } {1 {table bc: xBestIndex returned an invalid plan}}
  526. do_test vtab6-11.4.2 {
  527. catchsql {
  528. SELECT a, b, c FROM bc NATURAL JOIN ab;
  529. }
  530. } {1 {table ab: xBestIndex returned an invalid plan}}
  531. unset ::echo_module_ignore_usable
  532. finish_test