xref: /sqlite-3.40.0/test/where6.test (revision 8a29dfde) 
1# 2007 June 8
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 that terms in the ON clause of
13# a LEFT OUTER JOIN are not used with indices.  See ticket #3015.
14#
15# $Id: where6.test,v 1.1 2008/03/26 14:56:35 drh Exp $
16
17set testdir [file dirname $argv0]
18source $testdir/tester.tcl
19
20# Build some test data
21#
22do_test where6-1.0 {
23  execsql {
24    CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c);
25    INSERT INTO t1 VALUES(1,3,1);
26    INSERT INTO t1 VALUES(2,4,2);
27    CREATE TABLE t2(x INTEGER PRIMARY KEY);
28    INSERT INTO t2 VALUES(3);
29
30    SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1;
31  }
32} {1 3 1 3 2 4 2 {}}
33do_test where6-1.1 {
34  execsql {
35    SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1;
36  }
37} {1 3 1 3}
38do_test where6-1.2 {
39  execsql {
40    CREATE INDEX i1 ON t1(c);
41
42    SELECT * FROM t1 LEFT JOIN t2 ON b=x AND c=1;
43  }
44} {1 3 1 3 2 4 2 {}}
45do_test where6-1.3 {
46  execsql {
47    SELECT * FROM t1 LEFT JOIN t2 ON b=x WHERE c=1;
48  }
49} {1 3 1 3}
50
51finish_test
52