# 2001 September 15

#

# 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 UNION, INTERSECT and EXCEPT operators

# in SELECT statements.

#

# $Id: select4.test,v 1.29 2008/08/04 03:51:24 danielk1977 Exp $

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# Most tests in this file depend on compound-select. But there are a couple

# right at the end that test DISTINCT, so we cannot omit the entire file.

#

ifcapable compound {

# Build some test data

#

execsql {

  CREATE TABLE t1(n int, log int);

  BEGIN;

}

for {set i 1} {$i<32} {incr i} {

  for {set j 0} {(1<<$j)<$i} {incr j} {}

  execsql "INSERT INTO t1 VALUES($i,$j)"

}

execsql {

  COMMIT;

}

do_test select4-1.0 {

  execsql {SELECT DISTINCT log FROM t1 ORDER BY log}

} {0 1 2 3 4 5}

# Union All operator

#

do_test select4-1.1a {

  lsort [execsql {SELECT DISTINCT log FROM t1}]

} {0 1 2 3 4 5}

do_test select4-1.1b {

  lsort [execsql {SELECT n FROM t1 WHERE log=3}]

} {5 6 7 8}

do_test select4-1.1c {

  execsql {

    SELECT DISTINCT log FROM t1

    UNION ALL

    SELECT n FROM t1 WHERE log=3

    ORDER BY log;

  }

} {0 1 2 3 4 5 5 6 7 8}

do_test select4-1.1d {

  execsql {

    CREATE TABLE t2 AS

      SELECT DISTINCT log FROM t1

      UNION ALL

      SELECT n FROM t1 WHERE log=3

      ORDER BY log;

    SELECT * FROM t2;

  }

} {0 1 2 3 4 5 5 6 7 8}

execsql {DROP TABLE t2}

do_test select4-1.1e {

  execsql {

    CREATE TABLE t2 AS

      SELECT DISTINCT log FROM t1

      UNION ALL

      SELECT n FROM t1 WHERE log=3

      ORDER BY log DESC;

    SELECT * FROM t2;

  }

} {8 7 6 5 5 4 3 2 1 0}

execsql {DROP TABLE t2}

do_test select4-1.1f {

  execsql {

    SELECT DISTINCT log FROM t1

    UNION ALL

    SELECT n FROM t1 WHERE log=2

  }

} {0 1 2 3 4 5 3 4}

do_test select4-1.1g {

  execsql {

    CREATE TABLE t2 AS 

      SELECT DISTINCT log FROM t1

      UNION ALL

      SELECT n FROM t1 WHERE log=2;

    SELECT * FROM t2;

  }

} {0 1 2 3 4 5 3 4}

execsql {DROP TABLE t2}

ifcapable subquery {

  do_test select4-1.2 {

    execsql {

      SELECT log FROM t1 WHERE n IN 

        (SELECT DISTINCT log FROM t1 UNION ALL

         SELECT n FROM t1 WHERE log=3)

      ORDER BY log;

    }

  } {0 1 2 2 3 3 3 3}

}

do_test select4-1.3 {

  set v [catch {execsql {

    SELECT DISTINCT log FROM t1 ORDER BY log

    UNION ALL

    SELECT n FROM t1 WHERE log=3

    ORDER BY log;

  }} msg]

  lappend v $msg

} {1 {ORDER BY clause should come after UNION ALL not before}}

# Union operator

#

do_test select4-2.1 {

  execsql {

    SELECT DISTINCT log FROM t1

    UNION

    SELECT n FROM t1 WHERE log=3

    ORDER BY log;

  }

} {0 1 2 3 4 5 6 7 8}

ifcapable subquery {

  do_test select4-2.2 {

    execsql {

      SELECT log FROM t1 WHERE n IN 

        (SELECT DISTINCT log FROM t1 UNION

         SELECT n FROM t1 WHERE log=3)

      ORDER BY log;

    }

  } {0 1 2 2 3 3 3 3}

}

do_test select4-2.3 {

  set v [catch {execsql {

    SELECT DISTINCT log FROM t1 ORDER BY log

    UNION

    SELECT n FROM t1 WHERE log=3

    ORDER BY log;

  }} msg]

  lappend v $msg

} {1 {ORDER BY clause should come after UNION not before}}

# Except operator

#

do_test select4-3.1.1 {

  execsql {

    SELECT DISTINCT log FROM t1

    EXCEPT

    SELECT n FROM t1 WHERE log=3

    ORDER BY log;

  }

} {0 1 2 3 4}

do_test select4-3.1.2 {

  execsql {

    CREATE TABLE t2 AS 

      SELECT DISTINCT log FROM t1

      EXCEPT

      SELECT n FROM t1 WHERE log=3

      ORDER BY log;

    SELECT * FROM t2;

  }

} {0 1 2 3 4}

execsql {DROP TABLE t2}

do_test select4-3.1.3 {

  execsql {

    CREATE TABLE t2 AS 

      SELECT DISTINCT log FROM t1

      EXCEPT

      SELECT n FROM t1 WHERE log=3

      ORDER BY log DESC;

    SELECT * FROM t2;

  }

} {4 3 2 1 0}

execsql {DROP TABLE t2}

ifcapable subquery {

  do_test select4-3.2 {

    execsql {

      SELECT log FROM t1 WHERE n IN 

        (SELECT DISTINCT log FROM t1 EXCEPT

         SELECT n FROM t1 WHERE log=3)

      ORDER BY log;

    }

  } {0 1 2 2}

}

do_test select4-3.3 {

  set v [catch {execsql {

    SELECT DISTINCT log FROM t1 ORDER BY log

    EXCEPT

    SELECT n FROM t1 WHERE log=3

    ORDER BY log;

  }} msg]

  lappend v $msg

} {1 {ORDER BY clause should come after EXCEPT not before}}

# Intersect operator

#

do_test select4-4.1.1 {

  execsql {

    SELECT DISTINCT log FROM t1

    INTERSECT

    SELECT n FROM t1 WHERE log=3

    ORDER BY log;

  }

} {5}

do_test select4-4.1.2 {

  execsql {

    SELECT DISTINCT log FROM t1

    UNION ALL

    SELECT 6

    INTERSECT

    SELECT n FROM t1 WHERE log=3

    ORDER BY t1.log;

  }

} {5 6}

do_test select4-4.1.3 {

  execsql {

    CREATE TABLE t2 AS

      SELECT DISTINCT log FROM t1 UNION ALL SELECT 6

      INTERSECT

      SELECT n FROM t1 WHERE log=3

      ORDER BY log;

    SELECT * FROM t2;

  }

} {5 6}

execsql {DROP TABLE t2}

do_test select4-4.1.4 {

  execsql {

    CREATE TABLE t2 AS

      SELECT DISTINCT log FROM t1 UNION ALL SELECT 6

      INTERSECT

      SELECT n FROM t1 WHERE log=3

      ORDER BY log DESC;

    SELECT * FROM t2;

  }

} {6 5}

execsql {DROP TABLE t2}

ifcapable subquery {

  do_test select4-4.2 {

    execsql {

      SELECT log FROM t1 WHERE n IN 

        (SELECT DISTINCT log FROM t1 INTERSECT

         SELECT n FROM t1 WHERE log=3)

      ORDER BY log;

    }

  } {3}

}

do_test select4-4.3 {

  set v [catch {execsql {

    SELECT DISTINCT log FROM t1 ORDER BY log

    INTERSECT

    SELECT n FROM t1 WHERE log=3

    ORDER BY log;

  }} msg]

  lappend v $msg

} {1 {ORDER BY clause should come after INTERSECT not before}}

# Various error messages while processing UNION or INTERSECT

#

do_test select4-5.1 {

  set v [catch {execsql {

    SELECT DISTINCT log FROM t2

    UNION ALL

    SELECT n FROM t1 WHERE log=3

    ORDER BY log;

  }} msg]

  lappend v $msg

} {1 {no such table: t2}}

do_test select4-5.2 {

  set v [catch {execsql {

    SELECT DISTINCT log AS "xyzzy" FROM t1

    UNION ALL

    SELECT n FROM t1 WHERE log=3

    ORDER BY xyzzy;

  }} msg]

  lappend v $msg

} {0 {0 1 2 3 4 5 5 6 7 8}}

do_test select4-5.2b {

  set v [catch {execsql {

    SELECT DISTINCT log AS xyzzy FROM t1

    UNION ALL

    SELECT n FROM t1 WHERE log=3

    ORDER BY "xyzzy";

  }} msg]

  lappend v $msg

} {0 {0 1 2 3 4 5 5 6 7 8}}

do_test select4-5.2c {

  set v [catch {execsql {

    SELECT DISTINCT log FROM t1

    UNION ALL

    SELECT n FROM t1 WHERE log=3

    ORDER BY "xyzzy";

  }} msg]

  lappend v $msg

} {1 {1st ORDER BY term does not match any column in the result set}}

do_test select4-5.2d {

  set v [catch {execsql {

    SELECT DISTINCT log FROM t1

    INTERSECT

    SELECT n FROM t1 WHERE log=3

    ORDER BY "xyzzy";

  }} msg]

  lappend v $msg

} {1 {1st ORDER BY term does not match any column in the result set}}

do_test select4-5.2e {

  set v [catch {execsql {

    SELECT DISTINCT log FROM t1

    UNION ALL

    SELECT n FROM t1 WHERE log=3

    ORDER BY n;

  }} msg]

  lappend v $msg

} {0 {0 1 2 3 4 5 5 6 7 8}}

do_test select4-5.2f {

  catchsql {

    SELECT DISTINCT log FROM t1

    UNION ALL

    SELECT n FROM t1 WHERE log=3

    ORDER BY log;

  }

} {0 {0 1 2 3 4 5 5 6 7 8}}

do_test select4-5.2g {

  catchsql {

    SELECT DISTINCT log FROM t1

    UNION ALL

    SELECT n FROM t1 WHERE log=3

    ORDER BY 1;

  }

} {0 {0 1 2 3 4 5 5 6 7 8}}

do_test select4-5.2h {

  catchsql {

    SELECT DISTINCT log FROM t1

    UNION ALL

    SELECT n FROM t1 WHERE log=3

    ORDER BY 2;

  }

} {1 {1st ORDER BY term out of range - should be between 1 and 1}}

do_test select4-5.2i {

  catchsql {

    SELECT DISTINCT 1, log FROM t1

    UNION ALL

    SELECT 2, n FROM t1 WHERE log=3

    ORDER BY 2, 1;

  }

} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}

do_test select4-5.2j {

  catchsql {

    SELECT DISTINCT 1, log FROM t1

    UNION ALL

    SELECT 2, n FROM t1 WHERE log=3

    ORDER BY 1, 2 DESC;

  }

} {0 {1 5 1 4 1 3 1 2 1 1 1 0 2 8 2 7 2 6 2 5}}

do_test select4-5.2k {

  catchsql {

    SELECT DISTINCT 1, log FROM t1

    UNION ALL

    SELECT 2, n FROM t1 WHERE log=3

    ORDER BY n, 1;

  }

} {0 {1 0 1 1 1 2 1 3 1 4 1 5 2 5 2 6 2 7 2 8}}

do_test select4-5.3 {

  set v [catch {execsql {

    SELECT DISTINCT log, n FROM t1

    UNION ALL

    SELECT n FROM t1 WHERE log=3

    ORDER BY log;

  }} msg]

  lappend v $msg

} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}

do_test select4-5.4 {

  set v [catch {execsql {

    SELECT log FROM t1 WHERE n=2

    UNION ALL

    SELECT log FROM t1 WHERE n=3

    UNION ALL

    SELECT log FROM t1 WHERE n=4

    UNION ALL

    SELECT log FROM t1 WHERE n=5

    ORDER BY log;

  }} msg]

  lappend v $msg

} {0 {1 2 2 3}}

do_test select4-6.1 {

  execsql {

    SELECT log, count(*) as cnt FROM t1 GROUP BY log

    UNION

    SELECT log, n FROM t1 WHERE n=7

    ORDER BY cnt, log;

  }

} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}

do_test select4-6.2 {

  execsql {

    SELECT log, count(*) FROM t1 GROUP BY log

    UNION

    SELECT log, n FROM t1 WHERE n=7

    ORDER BY count(*), log;

  }

} {0 1 1 1 2 2 3 4 3 7 4 8 5 15}

# NULLs are indistinct for the UNION operator.

# Make sure the UNION operator recognizes this

#

do_test select4-6.3 {

  execsql {

    SELECT NULL UNION SELECT NULL UNION

    SELECT 1 UNION SELECT 2 AS 'x'

    ORDER BY x;

  }

} {{} 1 2}

do_test select4-6.3.1 {

  execsql {

    SELECT NULL UNION ALL SELECT NULL UNION ALL

    SELECT 1 UNION ALL SELECT 2 AS 'x'

    ORDER BY x;

  }

} {{} {} 1 2}

# Make sure the DISTINCT keyword treats NULLs as indistinct.

#

ifcapable subquery {

  do_test select4-6.4 {

    execsql {

      SELECT * FROM (

         SELECT NULL, 1 UNION ALL SELECT NULL, 1

      );

    }

  } {{} 1 {} 1}

  do_test select4-6.5 {

    execsql {

      SELECT DISTINCT * FROM (

         SELECT NULL, 1 UNION ALL SELECT NULL, 1

      );

    }

  } {{} 1}

  do_test select4-6.6 {

    execsql {

      SELECT DISTINCT * FROM (

         SELECT 1,2  UNION ALL SELECT 1,2

      );

    }

  } {1 2}

}

# Test distinctness of NULL in other ways.

#

do_test select4-6.7 {

  execsql {

    SELECT NULL EXCEPT SELECT NULL

  }

} {}

# Make sure column names are correct when a compound select appears as

# an expression in the WHERE clause.

#

do_test select4-7.1 {

  execsql {

    CREATE TABLE t2 AS SELECT log AS 'x', count(*) AS 'y' FROM t1 GROUP BY log;

    SELECT * FROM t2 ORDER BY x;

  }

} {0 1 1 1 2 2 3 4 4 8 5 15}  

ifcapable subquery {

  do_test select4-7.2 {

    execsql2 {

      SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 INTERSECT SELECT x FROM t2)

      ORDER BY n

    }

  } {n 1 log 0 n 2 log 1 n 3 log 2 n 4 log 2 n 5 log 3}

  do_test select4-7.3 {

    execsql2 {

      SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 EXCEPT SELECT x FROM t2)

      ORDER BY n LIMIT 2

    }

  } {n 6 log 3 n 7 log 3}

  do_test select4-7.4 {

    execsql2 {

      SELECT * FROM t1 WHERE n IN (SELECT n FROM t1 UNION SELECT x FROM t2)

      ORDER BY n LIMIT 2

    }

  } {n 1 log 0 n 2 log 1}

} ;# ifcapable subquery

} ;# ifcapable compound

# Make sure DISTINCT works appropriately on TEXT and NUMERIC columns.

do_test select4-8.1 {

  execsql {

    BEGIN;

    CREATE TABLE t3(a text, b float, c text);

    INSERT INTO t3 VALUES(1, 1.1, '1.1');

    INSERT INTO t3 VALUES(2, 1.10, '1.10');

    INSERT INTO t3 VALUES(3, 1.10, '1.1');

    INSERT INTO t3 VALUES(4, 1.1, '1.10');

    INSERT INTO t3 VALUES(5, 1.2, '1.2');

    INSERT INTO t3 VALUES(6, 1.3, '1.3');

    COMMIT;

  }

  execsql {

    SELECT DISTINCT b FROM t3 ORDER BY c;

  }

} {1.1 1.2 1.3}

do_test select4-8.2 {

  execsql {

    SELECT DISTINCT c FROM t3 ORDER BY c;

  }

} {1.1 1.10 1.2 1.3}

# Make sure the names of columns are taken from the right-most subquery

# right in a compound query.  Ticket #1721

#

ifcapable compound {

do_test select4-9.1 {

  execsql2 {

    SELECT x, y FROM t2 UNION SELECT a, b FROM t3 ORDER BY x LIMIT 1

  }

} {x 0 y 1}

do_test select4-9.2 {

  execsql2 {

    SELECT x, y FROM t2 UNION ALL SELECT a, b FROM t3 ORDER BY x LIMIT 1