1*1da57d55SToomas Soome# 27c478bd9Sstevel@tonic-gate# 2002 May 24 37c478bd9Sstevel@tonic-gate# 47c478bd9Sstevel@tonic-gate# The author disclaims copyright to this source code. In place of 57c478bd9Sstevel@tonic-gate# a legal notice, here is a blessing: 67c478bd9Sstevel@tonic-gate# 77c478bd9Sstevel@tonic-gate# May you do good and not evil. 87c478bd9Sstevel@tonic-gate# May you find forgiveness for yourself and forgive others. 97c478bd9Sstevel@tonic-gate# May you share freely, never taking more than you give. 107c478bd9Sstevel@tonic-gate# 117c478bd9Sstevel@tonic-gate#*********************************************************************** 127c478bd9Sstevel@tonic-gate# This file implements regression tests for SQLite library. 137c478bd9Sstevel@tonic-gate# 147c478bd9Sstevel@tonic-gate# This file implements tests for joins, including outer joins. 157c478bd9Sstevel@tonic-gate# 167c478bd9Sstevel@tonic-gate# $Id: join2.test,v 1.1 2004/01/24 20:18:13 drh Exp $ 177c478bd9Sstevel@tonic-gate 187c478bd9Sstevel@tonic-gateset testdir [file dirname $argv0] 197c478bd9Sstevel@tonic-gatesource $testdir/tester.tcl 207c478bd9Sstevel@tonic-gate 217c478bd9Sstevel@tonic-gatedo_test join2-1.1 { 227c478bd9Sstevel@tonic-gate execsql { 237c478bd9Sstevel@tonic-gate CREATE TABLE t1(a,b); 247c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(1,11); 257c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(2,22); 267c478bd9Sstevel@tonic-gate INSERT INTO t1 VALUES(3,33); 277c478bd9Sstevel@tonic-gate SELECT * FROM t1; 28*1da57d55SToomas Soome } 297c478bd9Sstevel@tonic-gate} {1 11 2 22 3 33} 307c478bd9Sstevel@tonic-gatedo_test join2-1.2 { 317c478bd9Sstevel@tonic-gate execsql { 327c478bd9Sstevel@tonic-gate CREATE TABLE t2(b,c); 337c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(11,111); 347c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(33,333); 357c478bd9Sstevel@tonic-gate INSERT INTO t2 VALUES(44,444); 367c478bd9Sstevel@tonic-gate SELECT * FROM t2; 37*1da57d55SToomas Soome } 387c478bd9Sstevel@tonic-gate} {11 111 33 333 44 444}; 397c478bd9Sstevel@tonic-gatedo_test join2-1.3 { 407c478bd9Sstevel@tonic-gate execsql { 417c478bd9Sstevel@tonic-gate CREATE TABLE t3(c,d); 427c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(111,1111); 437c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(444,4444); 447c478bd9Sstevel@tonic-gate INSERT INTO t3 VALUES(555,5555); 457c478bd9Sstevel@tonic-gate SELECT * FROM t3; 46*1da57d55SToomas Soome } 477c478bd9Sstevel@tonic-gate} {111 1111 444 4444 555 5555} 487c478bd9Sstevel@tonic-gate 497c478bd9Sstevel@tonic-gatedo_test join2-1.4 { 507c478bd9Sstevel@tonic-gate execsql { 517c478bd9Sstevel@tonic-gate SELECT * FROM 527c478bd9Sstevel@tonic-gate t1 NATURAL JOIN t2 NATURAL JOIN t3 537c478bd9Sstevel@tonic-gate } 547c478bd9Sstevel@tonic-gate} {1 11 111 1111} 557c478bd9Sstevel@tonic-gatedo_test join2-1.5 { 567c478bd9Sstevel@tonic-gate execsql { 577c478bd9Sstevel@tonic-gate SELECT * FROM 587c478bd9Sstevel@tonic-gate t1 NATURAL JOIN t2 NATURAL LEFT OUTER JOIN t3 597c478bd9Sstevel@tonic-gate } 607c478bd9Sstevel@tonic-gate} {1 11 111 1111 3 33 333 {}} 617c478bd9Sstevel@tonic-gatedo_test join2-1.6 { 627c478bd9Sstevel@tonic-gate execsql { 637c478bd9Sstevel@tonic-gate SELECT * FROM 647c478bd9Sstevel@tonic-gate t1 NATURAL LEFT OUTER JOIN t2 NATURAL JOIN t3 657c478bd9Sstevel@tonic-gate } 667c478bd9Sstevel@tonic-gate} {1 11 111 1111} 677c478bd9Sstevel@tonic-gatedo_test join2-1.6 { 687c478bd9Sstevel@tonic-gate execsql { 697c478bd9Sstevel@tonic-gate SELECT * FROM 707c478bd9Sstevel@tonic-gate t1 NATURAL LEFT OUTER JOIN (t2 NATURAL JOIN t3) 717c478bd9Sstevel@tonic-gate } 727c478bd9Sstevel@tonic-gate} {1 11 111 1111 2 22 {} {} 3 33 {} {}} 737c478bd9Sstevel@tonic-gate 747c478bd9Sstevel@tonic-gatefinish_test 75