1# 2006 January 31 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 join reordering optimization 13# in cases that include a LEFT JOIN. 14# 15# $Id: where3.test,v 1.2 2006/06/06 11:45:55 drh Exp $ 16 17set testdir [file dirname $argv0] 18source $testdir/tester.tcl 19 20# The following is from ticket #1652. 21# 22# A comma join then a left outer join: A,B left join C. 23# Arrange indices so that the B table is chosen to go first. 24# Also put an index on C, but make sure that A is chosen before C. 25# 26do_test where3-1.1 { 27 execsql { 28 CREATE TABLE t1(a, b); 29 CREATE TABLE t2(p, q); 30 CREATE TABLE t3(x, y); 31 32 INSERT INTO t1 VALUES(111,'one'); 33 INSERT INTO t1 VALUES(222,'two'); 34 INSERT INTO t1 VALUES(333,'three'); 35 36 INSERT INTO t2 VALUES(1,111); 37 INSERT INTO t2 VALUES(2,222); 38 INSERT INTO t2 VALUES(4,444); 39 CREATE INDEX t2i1 ON t2(p); 40 41 INSERT INTO t3 VALUES(999,'nine'); 42 CREATE INDEX t3i1 ON t3(x); 43 44 SELECT * FROM t1, t2 LEFT JOIN t3 ON q=x WHERE p=2 AND a=q; 45 } 46} {222 two 2 222 {} {}} 47 48# Ticket #1830 49# 50# This is similar to the above but with the LEFT JOIN on the 51# other side. 52# 53do_test where3-1.2 { 54 execsql { 55 CREATE TABLE parent1(parent1key, child1key, Child2key, child3key); 56 CREATE TABLE child1 ( child1key NVARCHAR, value NVARCHAR ); 57 CREATE UNIQUE INDEX PKIDXChild1 ON child1 ( child1key ); 58 CREATE TABLE child2 ( child2key NVARCHAR, value NVARCHAR ); 59 60 INSERT INTO parent1(parent1key,child1key,child2key) 61 VALUES ( 1, 'C1.1', 'C2.1' ); 62 INSERT INTO child1 ( child1key, value ) VALUES ( 'C1.1', 'Value for C1.1' ); 63 INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.1', 'Value for C2.1' ); 64 65 INSERT INTO parent1 ( parent1key, child1key, child2key ) 66 VALUES ( 2, 'C1.2', 'C2.2' ); 67 INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.2', 'Value for C2.2' ); 68 69 INSERT INTO parent1 ( parent1key, child1key, child2key ) 70 VALUES ( 3, 'C1.3', 'C2.3' ); 71 INSERT INTO child1 ( child1key, value ) VALUES ( 'C1.3', 'Value for C1.3' ); 72 INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.3', 'Value for C2.3' ); 73 74 SELECT parent1.parent1key, child1.value, child2.value 75 FROM parent1 76 LEFT OUTER JOIN child1 ON child1.child1key = parent1.child1key 77 INNER JOIN child2 ON child2.child2key = parent1.child2key; 78 } 79} {1 {Value for C1.1} {Value for C2.1} 2 {} {Value for C2.2} 3 {Value for C1.3} {Value for C2.3}} 80 81finish_test 82