1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/vtab6.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,574 @@
1.4 +# 2002 May 24
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library.
1.15 +#
1.16 +# This file implements tests for joins, including outer joins involving
1.17 +# virtual tables. The test cases in this file are copied from the file
1.18 +# join.test, and some of the comments still reflect that.
1.19 +#
1.20 +# $Id: vtab6.test,v 1.4 2008/07/12 14:52:21 drh Exp $
1.21 +
1.22 +set testdir [file dirname $argv0]
1.23 +source $testdir/tester.tcl
1.24 +
1.25 +ifcapable !vtab {
1.26 + finish_test
1.27 + return
1.28 +}
1.29 +
1.30 +register_echo_module [sqlite3_connection_pointer db]
1.31 +
1.32 +execsql {
1.33 + CREATE TABLE real_t1(a,b,c);
1.34 + CREATE TABLE real_t2(b,c,d);
1.35 + CREATE TABLE real_t3(c,d,e);
1.36 + CREATE TABLE real_t4(d,e,f);
1.37 + CREATE TABLE real_t5(a INTEGER PRIMARY KEY);
1.38 + CREATE TABLE real_t6(a INTEGER);
1.39 + CREATE TABLE real_t7 (x, y);
1.40 + CREATE TABLE real_t8 (a integer primary key, b);
1.41 + CREATE TABLE real_t9(a INTEGER PRIMARY KEY, b);
1.42 + CREATE TABLE real_t10(x INTEGER PRIMARY KEY, y);
1.43 + CREATE TABLE real_t11(p INTEGER PRIMARY KEY, q);
1.44 + CREATE TABLE real_t12(a,b);
1.45 + CREATE TABLE real_t13(b,c);
1.46 + CREATE TABLE real_t21(a,b,c);
1.47 + CREATE TABLE real_t22(p,q);
1.48 +}
1.49 +foreach t [list t1 t2 t3 t4 t5 t6 t7 t8 t9 t10 t11 t12 t13 t21 t22] {
1.50 + execsql "CREATE VIRTUAL TABLE $t USING echo(real_$t)"
1.51 +}
1.52 +
1.53 +do_test vtab6-1.1 {
1.54 + execsql {
1.55 + INSERT INTO t1 VALUES(1,2,3);
1.56 + INSERT INTO t1 VALUES(2,3,4);
1.57 + INSERT INTO t1 VALUES(3,4,5);
1.58 + SELECT * FROM t1;
1.59 + }
1.60 +} {1 2 3 2 3 4 3 4 5}
1.61 +do_test vtab6-1.2 {
1.62 + execsql {
1.63 + INSERT INTO t2 VALUES(1,2,3);
1.64 + INSERT INTO t2 VALUES(2,3,4);
1.65 + INSERT INTO t2 VALUES(3,4,5);
1.66 + SELECT * FROM t2;
1.67 + }
1.68 +} {1 2 3 2 3 4 3 4 5}
1.69 +
1.70 +do_test vtab6-1.3 {
1.71 + execsql2 {
1.72 + SELECT * FROM t1 NATURAL JOIN t2;
1.73 + }
1.74 +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
1.75 +do_test vtab6-1.3.1 {
1.76 + execsql2 {
1.77 + SELECT * FROM t2 NATURAL JOIN t1;
1.78 + }
1.79 +} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
1.80 +do_test vtab6-1.3.2 {
1.81 + execsql2 {
1.82 + SELECT * FROM t2 AS x NATURAL JOIN t1;
1.83 + }
1.84 +} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
1.85 +do_test vtab6-1.3.3 {
1.86 + execsql2 {
1.87 + SELECT * FROM t2 NATURAL JOIN t1 AS y;
1.88 + }
1.89 +} {b 2 c 3 d 4 a 1 b 3 c 4 d 5 a 2}
1.90 +do_test vtab6-1.3.4 {
1.91 + execsql {
1.92 + SELECT b FROM t1 NATURAL JOIN t2;
1.93 + }
1.94 +} {2 3}
1.95 +do_test vtab6-1.4.1 {
1.96 + execsql2 {
1.97 + SELECT * FROM t1 INNER JOIN t2 USING(b,c);
1.98 + }
1.99 +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
1.100 +do_test vtab6-1.4.2 {
1.101 + execsql2 {
1.102 + SELECT * FROM t1 AS x INNER JOIN t2 USING(b,c);
1.103 + }
1.104 +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
1.105 +do_test vtab6-1.4.3 {
1.106 + execsql2 {
1.107 + SELECT * FROM t1 INNER JOIN t2 AS y USING(b,c);
1.108 + }
1.109 +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
1.110 +do_test vtab6-1.4.4 {
1.111 + execsql2 {
1.112 + SELECT * FROM t1 AS x INNER JOIN t2 AS y USING(b,c);
1.113 + }
1.114 +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
1.115 +do_test vtab6-1.4.5 {
1.116 + execsql {
1.117 + SELECT b FROM t1 JOIN t2 USING(b);
1.118 + }
1.119 +} {2 3}
1.120 +do_test vtab6-1.5 {
1.121 + execsql2 {
1.122 + SELECT * FROM t1 INNER JOIN t2 USING(b);
1.123 + }
1.124 +} {a 1 b 2 c 3 c 3 d 4 a 2 b 3 c 4 c 4 d 5}
1.125 +do_test vtab6-1.6 {
1.126 + execsql2 {
1.127 + SELECT * FROM t1 INNER JOIN t2 USING(c);
1.128 + }
1.129 +} {a 1 b 2 c 3 b 2 d 4 a 2 b 3 c 4 b 3 d 5}
1.130 +do_test vtab6-1.7 {
1.131 + execsql2 {
1.132 + SELECT * FROM t1 INNER JOIN t2 USING(c,b);
1.133 + }
1.134 +} {a 1 b 2 c 3 d 4 a 2 b 3 c 4 d 5}
1.135 +
1.136 +do_test vtab6-1.8 {
1.137 + execsql {
1.138 + SELECT * FROM t1 NATURAL CROSS JOIN t2;
1.139 + }
1.140 +} {1 2 3 4 2 3 4 5}
1.141 +do_test vtab6-1.9 {
1.142 + execsql {
1.143 + SELECT * FROM t1 CROSS JOIN t2 USING(b,c);
1.144 + }
1.145 +} {1 2 3 4 2 3 4 5}
1.146 +do_test vtab6-1.10 {
1.147 + execsql {
1.148 + SELECT * FROM t1 NATURAL INNER JOIN t2;
1.149 + }
1.150 +} {1 2 3 4 2 3 4 5}
1.151 +do_test vtab6-1.11 {
1.152 + execsql {
1.153 + SELECT * FROM t1 INNER JOIN t2 USING(b,c);
1.154 + }
1.155 +} {1 2 3 4 2 3 4 5}
1.156 +do_test vtab6-1.12 {
1.157 + execsql {
1.158 + SELECT * FROM t1 natural inner join t2;
1.159 + }
1.160 +} {1 2 3 4 2 3 4 5}
1.161 +
1.162 +ifcapable subquery {
1.163 + do_test vtab6-1.13 {
1.164 + execsql2 {
1.165 + SELECT * FROM t1 NATURAL JOIN
1.166 + (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as t3
1.167 + }
1.168 + } {a 1 b 2 c 3 d 4 e 5}
1.169 + do_test vtab6-1.14 {
1.170 + execsql2 {
1.171 + SELECT * FROM (SELECT b as 'c', c as 'd', d as 'e' FROM t2) as 'tx'
1.172 + NATURAL JOIN t1
1.173 + }
1.174 + } {c 3 d 4 e 5 a 1 b 2}
1.175 +}
1.176 +
1.177 +do_test vtab6-1.15 {
1.178 + execsql {
1.179 + INSERT INTO t3 VALUES(2,3,4);
1.180 + INSERT INTO t3 VALUES(3,4,5);
1.181 + INSERT INTO t3 VALUES(4,5,6);
1.182 + SELECT * FROM t3;
1.183 + }
1.184 +} {2 3 4 3 4 5 4 5 6}
1.185 +do_test vtab6-1.16 {
1.186 + execsql {
1.187 + SELECT * FROM t1 natural join t2 natural join t3;
1.188 + }
1.189 +} {1 2 3 4 5 2 3 4 5 6}
1.190 +do_test vtab6-1.17 {
1.191 + execsql2 {
1.192 + SELECT * FROM t1 natural join t2 natural join t3;
1.193 + }
1.194 +} {a 1 b 2 c 3 d 4 e 5 a 2 b 3 c 4 d 5 e 6}
1.195 +do_test vtab6-1.18 {
1.196 + execsql {
1.197 + INSERT INTO t4 VALUES(2,3,4);
1.198 + INSERT INTO t4 VALUES(3,4,5);
1.199 + INSERT INTO t4 VALUES(4,5,6);
1.200 + SELECT * FROM t4;
1.201 + }
1.202 +} {2 3 4 3 4 5 4 5 6}
1.203 +do_test vtab6-1.19.1 {
1.204 + execsql {
1.205 + SELECT * FROM t1 natural join t2 natural join t4;
1.206 + }
1.207 +} {1 2 3 4 5 6}
1.208 +do_test vtab6-1.19.2 {
1.209 + execsql2 {
1.210 + SELECT * FROM t1 natural join t2 natural join t4;
1.211 + }
1.212 +} {a 1 b 2 c 3 d 4 e 5 f 6}
1.213 +do_test vtab6-1.20 {
1.214 + execsql {
1.215 + SELECT * FROM t1 natural join t2 natural join t3 WHERE t1.a=1
1.216 + }
1.217 +} {1 2 3 4 5}
1.218 +
1.219 +do_test vtab6-2.1 {
1.220 + execsql {
1.221 + SELECT * FROM t1 NATURAL LEFT JOIN t2;
1.222 + }
1.223 +} {1 2 3 4 2 3 4 5 3 4 5 {}}
1.224 +do_test vtab6-2.2 {
1.225 + execsql {
1.226 + SELECT * FROM t2 NATURAL LEFT OUTER JOIN t1;
1.227 + }
1.228 +} {1 2 3 {} 2 3 4 1 3 4 5 2}
1.229 +do_test vtab6-2.3 {
1.230 + catchsql {
1.231 + SELECT * FROM t1 NATURAL RIGHT OUTER JOIN t2;
1.232 + }
1.233 +} {1 {RIGHT and FULL OUTER JOINs are not currently supported}}
1.234 +do_test vtab6-2.4 {
1.235 + execsql {
1.236 + SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d
1.237 + }
1.238 +} {1 2 3 {} {} {} 2 3 4 {} {} {} 3 4 5 1 2 3}
1.239 +do_test vtab6-2.5 {
1.240 + execsql {
1.241 + SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t1.a>1
1.242 + }
1.243 +} {2 3 4 {} {} {} 3 4 5 1 2 3}
1.244 +do_test vtab6-2.6 {
1.245 + execsql {
1.246 + SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.d WHERE t2.b IS NULL OR t2.b>1
1.247 + }
1.248 +} {1 2 3 {} {} {} 2 3 4 {} {} {}}
1.249 +
1.250 +do_test vtab6-3.1 {
1.251 + catchsql {
1.252 + SELECT * FROM t1 NATURAL JOIN t2 ON t1.a=t2.b;
1.253 + }
1.254 +} {1 {a NATURAL join may not have an ON or USING clause}}
1.255 +do_test vtab6-3.2 {
1.256 + catchsql {
1.257 + SELECT * FROM t1 NATURAL JOIN t2 USING(b);
1.258 + }
1.259 +} {1 {a NATURAL join may not have an ON or USING clause}}
1.260 +do_test vtab6-3.3 {
1.261 + catchsql {
1.262 + SELECT * FROM t1 JOIN t2 ON t1.a=t2.b USING(b);
1.263 + }
1.264 +} {1 {cannot have both ON and USING clauses in the same join}}
1.265 +do_test vtab6-3.4 {
1.266 + catchsql {
1.267 + SELECT * FROM t1 JOIN t2 USING(a);
1.268 + }
1.269 +} {1 {cannot join using column a - column not present in both tables}}
1.270 +do_test vtab6-3.5 {
1.271 + catchsql {
1.272 + SELECT * FROM t1 USING(a);
1.273 + }
1.274 +} {0 {1 2 3 2 3 4 3 4 5}}
1.275 +do_test vtab6-3.6 {
1.276 + catchsql {
1.277 + SELECT * FROM t1 JOIN t2 ON t3.a=t2.b;
1.278 + }
1.279 +} {1 {no such column: t3.a}}
1.280 +do_test vtab6-3.7 {
1.281 + catchsql {
1.282 + SELECT * FROM t1 INNER OUTER JOIN t2;
1.283 + }
1.284 +} {1 {unknown or unsupported join type: INNER OUTER}}
1.285 +do_test vtab6-3.7 {
1.286 + catchsql {
1.287 + SELECT * FROM t1 LEFT BOGUS JOIN t2;
1.288 + }
1.289 +} {1 {unknown or unsupported join type: LEFT BOGUS}}
1.290 +
1.291 +do_test vtab6-4.1 {
1.292 + execsql {
1.293 + BEGIN;
1.294 + INSERT INTO t6 VALUES(NULL);
1.295 + INSERT INTO t6 VALUES(NULL);
1.296 + INSERT INTO t6 SELECT * FROM t6;
1.297 + INSERT INTO t6 SELECT * FROM t6;
1.298 + INSERT INTO t6 SELECT * FROM t6;
1.299 + INSERT INTO t6 SELECT * FROM t6;
1.300 + INSERT INTO t6 SELECT * FROM t6;
1.301 + INSERT INTO t6 SELECT * FROM t6;
1.302 + COMMIT;
1.303 + }
1.304 + execsql {
1.305 + SELECT * FROM t6 NATURAL JOIN t5;
1.306 + }
1.307 +} {}
1.308 +do_test vtab6-4.2 {
1.309 + execsql {
1.310 + SELECT * FROM t6, t5 WHERE t6.a<t5.a;
1.311 + }
1.312 +} {}
1.313 +do_test vtab6-4.3 {
1.314 + execsql {
1.315 + SELECT * FROM t6, t5 WHERE t6.a>t5.a;
1.316 + }
1.317 +} {}
1.318 +do_test vtab6-4.4 {
1.319 + execsql {
1.320 + UPDATE t6 SET a='xyz';
1.321 + SELECT * FROM t6 NATURAL JOIN t5;
1.322 + }
1.323 +} {}
1.324 +do_test vtab6-4.6 {
1.325 + execsql {
1.326 + SELECT * FROM t6, t5 WHERE t6.a<t5.a;
1.327 + }
1.328 +} {}
1.329 +do_test vtab6-4.7 {
1.330 + execsql {
1.331 + SELECT * FROM t6, t5 WHERE t6.a>t5.a;
1.332 + }
1.333 +} {}
1.334 +do_test vtab6-4.8 {
1.335 + execsql {
1.336 + UPDATE t6 SET a=1;
1.337 + SELECT * FROM t6 NATURAL JOIN t5;
1.338 + }
1.339 +} {}
1.340 +do_test vtab6-4.9 {
1.341 + execsql {
1.342 + SELECT * FROM t6, t5 WHERE t6.a<t5.a;
1.343 + }
1.344 +} {}
1.345 +do_test vtab6-4.10 {
1.346 + execsql {
1.347 + SELECT * FROM t6, t5 WHERE t6.a>t5.a;
1.348 + }
1.349 +} {}
1.350 +
1.351 +# A test for ticket #247.
1.352 +#
1.353 +do_test vtab6-7.1 {
1.354 + execsql {
1.355 + INSERT INTO t7 VALUES ("pa1", 1);
1.356 + INSERT INTO t7 VALUES ("pa2", NULL);
1.357 + INSERT INTO t7 VALUES ("pa3", NULL);
1.358 + INSERT INTO t7 VALUES ("pa4", 2);
1.359 + INSERT INTO t7 VALUES ("pa30", 131);
1.360 + INSERT INTO t7 VALUES ("pa31", 130);
1.361 + INSERT INTO t7 VALUES ("pa28", NULL);
1.362 +
1.363 + INSERT INTO t8 VALUES (1, "pa1");
1.364 + INSERT INTO t8 VALUES (2, "pa4");
1.365 + INSERT INTO t8 VALUES (3, NULL);
1.366 + INSERT INTO t8 VALUES (4, NULL);
1.367 + INSERT INTO t8 VALUES (130, "pa31");
1.368 + INSERT INTO t8 VALUES (131, "pa30");
1.369 +
1.370 + SELECT coalesce(t8.a,999) from t7 LEFT JOIN t8 on y=a;
1.371 + }
1.372 +} {1 999 999 2 131 130 999}
1.373 +
1.374 +# Make sure a left join where the right table is really a view that
1.375 +# is itself a join works right. Ticket #306.
1.376 +#
1.377 +ifcapable view {
1.378 +do_test vtab6-8.1 {
1.379 + execsql {
1.380 + BEGIN;
1.381 + INSERT INTO t9 VALUES(1,11);
1.382 + INSERT INTO t9 VALUES(2,22);
1.383 + INSERT INTO t10 VALUES(1,2);
1.384 + INSERT INTO t10 VALUES(3,3);
1.385 + INSERT INTO t11 VALUES(2,111);
1.386 + INSERT INTO t11 VALUES(3,333);
1.387 + CREATE VIEW v10_11 AS SELECT x, q FROM t10, t11 WHERE t10.y=t11.p;
1.388 + COMMIT;
1.389 + SELECT * FROM t9 LEFT JOIN v10_11 ON( a=x );
1.390 + }
1.391 +} {1 11 1 111 2 22 {} {}}
1.392 +ifcapable subquery {
1.393 + do_test vtab6-8.2 {
1.394 + execsql {
1.395 + SELECT * FROM t9 LEFT JOIN (SELECT x, q FROM t10, t11 WHERE t10.y=t11.p)
1.396 + ON( a=x);
1.397 + }
1.398 + } {1 11 1 111 2 22 {} {}}
1.399 +}
1.400 +do_test vtab6-8.3 {
1.401 + execsql {
1.402 + SELECT * FROM v10_11 LEFT JOIN t9 ON( a=x );
1.403 + }
1.404 +} {1 111 1 11 3 333 {} {}}
1.405 +} ;# ifcapable view
1.406 +
1.407 +# Ticket #350 describes a scenario where LEFT OUTER JOIN does not
1.408 +# function correctly if the right table in the join is really
1.409 +# subquery.
1.410 +#
1.411 +# To test the problem, we generate the same LEFT OUTER JOIN in two
1.412 +# separate selects but with on using a subquery and the other calling
1.413 +# the table directly. Then connect the two SELECTs using an EXCEPT.
1.414 +# Both queries should generate the same results so the answer should
1.415 +# be an empty set.
1.416 +#
1.417 +ifcapable compound {
1.418 +do_test vtab6-9.1 {
1.419 + execsql {
1.420 + BEGIN;
1.421 + INSERT INTO t12 VALUES(1,11);
1.422 + INSERT INTO t12 VALUES(2,22);
1.423 + INSERT INTO t13 VALUES(22,222);
1.424 + COMMIT;
1.425 + }
1.426 +} {}
1.427 +
1.428 +ifcapable subquery {
1.429 + do_test vtab6-9.1.1 {
1.430 + execsql {
1.431 + SELECT * FROM t12 NATURAL LEFT JOIN t13
1.432 + EXCEPT
1.433 + SELECT * FROM t12 NATURAL LEFT JOIN (SELECT * FROM t13 WHERE b>0);
1.434 + }
1.435 + } {}
1.436 +}
1.437 +ifcapable view {
1.438 + do_test vtab6-9.2 {
1.439 + execsql {
1.440 + CREATE VIEW v13 AS SELECT * FROM t13 WHERE b>0;
1.441 + SELECT * FROM t12 NATURAL LEFT JOIN t13
1.442 + EXCEPT
1.443 + SELECT * FROM t12 NATURAL LEFT JOIN v13;
1.444 + }
1.445 + } {}
1.446 +} ;# ifcapable view
1.447 +} ;# ifcapable compound
1.448 +
1.449 +ifcapable subquery {
1.450 +do_test vtab6-10.1 {
1.451 + execsql {
1.452 + CREATE INDEX i22 ON real_t22(q);
1.453 + SELECT a FROM t21 LEFT JOIN t22 ON b=p WHERE q=
1.454 + (SELECT max(m.q) FROM t22 m JOIN t21 n ON n.b=m.p WHERE n.c=1);
1.455 + }
1.456 +} {}
1.457 +} ;# ifcapable subquery
1.458 +
1.459 +do_test vtab6-11.1.0 {
1.460 + execsql {
1.461 + CREATE TABLE ab_r(a, b);
1.462 + CREATE TABLE bc_r(b, c);
1.463 +
1.464 + CREATE VIRTUAL TABLE ab USING echo(ab_r);
1.465 + CREATE VIRTUAL TABLE bc USING echo(bc_r);
1.466 +
1.467 + INSERT INTO ab VALUES(1, 2);
1.468 + INSERT INTO bc VALUES(2, 3);
1.469 + }
1.470 +} {}
1.471 +
1.472 +do_test vtab6-11.1.1 {
1.473 + execsql {
1.474 + SELECT a, b, c FROM ab NATURAL JOIN bc;
1.475 + }
1.476 +} {1 2 3}
1.477 +do_test vtab6-11.1.2 {
1.478 + execsql {
1.479 + SELECT a, b, c FROM bc NATURAL JOIN ab;
1.480 + }
1.481 +} {1 2 3}
1.482 +
1.483 +set ::echo_module_cost 1.0
1.484 +
1.485 +do_test vtab6-11.1.3 {
1.486 + execsql {
1.487 + SELECT a, b, c FROM ab NATURAL JOIN bc;
1.488 + }
1.489 +} {1 2 3}
1.490 +do_test vtab6-11.1.4 {
1.491 + execsql {
1.492 + SELECT a, b, c FROM bc NATURAL JOIN ab;
1.493 + }
1.494 +} {1 2 3}
1.495 +
1.496 +
1.497 +do_test vtab6-11.2.0 {
1.498 + execsql {
1.499 + CREATE INDEX ab_i ON ab_r(b);
1.500 + }
1.501 +} {}
1.502 +
1.503 +unset ::echo_module_cost
1.504 +
1.505 +do_test vtab6-11.2.1 {
1.506 + execsql {
1.507 + SELECT a, b, c FROM ab NATURAL JOIN bc;
1.508 + }
1.509 +} {1 2 3}
1.510 +do_test vtab6-11.2.2 {
1.511 + execsql {
1.512 + SELECT a, b, c FROM bc NATURAL JOIN ab;
1.513 + }
1.514 +} {1 2 3}
1.515 +
1.516 +set ::echo_module_cost 1.0
1.517 +
1.518 +do_test vtab6-11.2.3 {
1.519 + execsql {
1.520 + SELECT a, b, c FROM ab NATURAL JOIN bc;
1.521 + }
1.522 +} {1 2 3}
1.523 +do_test vtab6-11.2.4 {
1.524 + execsql {
1.525 + SELECT a, b, c FROM bc NATURAL JOIN ab;
1.526 + }
1.527 +} {1 2 3}
1.528 +
1.529 +unset ::echo_module_cost
1.530 +db close
1.531 +sqlite3 db test.db
1.532 +register_echo_module [sqlite3_connection_pointer db]
1.533 +
1.534 +do_test vtab6-11.3.1 {
1.535 + execsql {
1.536 + SELECT a, b, c FROM ab NATURAL JOIN bc;
1.537 + }
1.538 +} {1 2 3}
1.539 +
1.540 +do_test vtab6-11.3.2 {
1.541 + execsql {
1.542 + SELECT a, b, c FROM bc NATURAL JOIN ab;
1.543 + }
1.544 +} {1 2 3}
1.545 +
1.546 +set ::echo_module_cost 1.0
1.547 +
1.548 +do_test vtab6-11.3.3 {
1.549 + execsql {
1.550 + SELECT a, b, c FROM ab NATURAL JOIN bc;
1.551 + }
1.552 +} {1 2 3}
1.553 +do_test vtab6-11.3.4 {
1.554 + execsql {
1.555 + SELECT a, b, c FROM bc NATURAL JOIN ab;
1.556 + }
1.557 +} {1 2 3}
1.558 +
1.559 +unset ::echo_module_cost
1.560 +
1.561 +set ::echo_module_ignore_usable 1
1.562 +db cache flush
1.563 +
1.564 +do_test vtab6-11.4.1 {
1.565 + catchsql {
1.566 + SELECT a, b, c FROM ab NATURAL JOIN bc;
1.567 + }
1.568 +} {1 {table ab: xBestIndex returned an invalid plan}}
1.569 +do_test vtab6-11.4.2 {
1.570 + catchsql {
1.571 + SELECT a, b, c FROM bc NATURAL JOIN ab;
1.572 + }
1.573 +} {1 {table ab: xBestIndex returned an invalid plan}}
1.574 +
1.575 +unset ::echo_module_ignore_usable
1.576 +
1.577 +finish_test