# 2016 June 17
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing "(...) IN (SELECT ...)" expressions
# where the SELECT statement returns more than one column.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix rowvalue3


do_execsql_test 1.0 {
  CREATE TABLE t1(a, b, c);
  CREATE INDEX i1 ON t1(a, b);
  INSERT INTO t1 VALUES(1, 2, 3);
  INSERT INTO t1 VALUES(4, 5, 6);
  INSERT INTO t1 VALUES(7, 8, 9);
}

foreach {tn sql res} {
  1  "SELECT 1 WHERE (4, 5) IN (SELECT a, b FROM t1)"  1
  2  "SELECT 1 WHERE (5, 5) IN (SELECT a, b FROM t1)"  {}
  3  "SELECT 1 WHERE (5, 4) IN (SELECT a, b FROM t1)"  {}
  4  "SELECT 1 WHERE (5, 4) IN (SELECT b, a FROM t1)"  1
  5  "SELECT 1 WHERE (SELECT a, b FROM t1 WHERE c=6) IN (SELECT a, b FROM t1)" 1
  6  "SELECT (5, 4) IN (SELECT a, b FROM t1)" 0
  7  "SELECT 1 WHERE (5, 4) IN (SELECT +b, +a FROM t1)"  1
  8  "SELECT (5, 4) IN (SELECT +b, +a FROM t1)"  1
  9  "SELECT (1, 2) IN (SELECT rowid, b FROM t1)"  1
  10 "SELECT 1 WHERE (1, 2) IN (SELECT rowid, b FROM t1)"  1
  11 "SELECT 1 WHERE (1, NULL) IN (SELECT rowid, b FROM t1)"  {}
} {
  do_execsql_test 1.$tn $sql $res
}

#-------------------------------------------------------------------------

do_execsql_test 2.0 {
  CREATE TABLE z1(x, y, z);
  CREATE TABLE kk(a, b);

  INSERT INTO z1 VALUES('a', 'b', 'c');
  INSERT INTO z1 VALUES('d', 'e', 'f');
  INSERT INTO z1 VALUES('g', 'h', 'i');

  -- INSERT INTO kk VALUES('y', 'y');
  INSERT INTO kk VALUES('d', 'e');
  -- INSERT INTO kk VALUES('x', 'x');

}

foreach {tn idx} {
  1 { }
  2 {  CREATE INDEX z1idx ON z1(x, y) }
  3 {  CREATE UNIQUE INDEX z1idx ON z1(x, y) }
} {
  execsql "DROP INDEX IF EXISTS z1idx"
  execsql $idx

  do_execsql_test 2.$tn.1 {
    SELECT * FROM z1 WHERE x IN (SELECT a FROM kk)
  } {d e f}

  do_execsql_test 2.$tn.2 {
    SELECT * FROM z1 WHERE (x,y) IN (SELECT a, b FROM kk)
  } {d e f}

  do_execsql_test 2.$tn.3 {
    SELECT * FROM z1 WHERE (x, +y) IN (SELECT a, b FROM kk)
  } {d e f}
  
  do_execsql_test 2.$tn.4 {
    SELECT * FROM z1 WHERE (x, +y) IN (SELECT a, b||'x' FROM kk)
  } {}

  do_execsql_test 2.$tn.5 {
    SELECT * FROM z1 WHERE (+x, y) IN (SELECT a, b FROM kk)
  } {d e f}
}

explain_i {
  SELECT * FROM z1 WHERE (x, y) IN (SELECT a, b FROM kk)
} 

finish_test