Annotation of embedaddon/sqlite3/test/vtab6.test, revision 1.1

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

FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>