test/selectB.test

# 2008 June 24
#
# 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. 
#
# $Id: selectB.test,v 1.10 2009/04/02 16:59:47 drh Exp $

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

ifcapable !compound {
  finish_test
  return
}

proc test_transform {testname sql1 sql2 results} {
  set ::vdbe1 [list]
  set ::vdbe2 [list]
  db eval "explain $sql1" { lappend ::vdbe1 $opcode }
  db eval "explain $sql2" { lappend ::vdbe2 $opcode }

  do_test $testname.transform {
    set ::vdbe1
  } $::vdbe2

  set ::sql1 $sql1
  do_test $testname.sql1 {
    execsql $::sql1
  } $results

  set ::sql2 $sql2
  do_test $testname.sql2 {
    execsql $::sql2
  } $results
}

do_test selectB-1.1 {
  execsql {
    CREATE TABLE t1(a, b, c);
    CREATE TABLE t2(d, e, f);

    INSERT INTO t1 VALUES( 2,  4,  6);
    INSERT INTO t1 VALUES( 8, 10, 12);
    INSERT INTO t1 VALUES(14, 16, 18);

    INSERT INTO t2 VALUES(3,   6,  9);
    INSERT INTO t2 VALUES(12, 15, 18);
    INSERT INTO t2 VALUES(21, 24, 27);
  }
} {}

for {set ii 1} {$ii <= 2} {incr ii} {

  if {$ii == 2} {
    do_test selectB-2.1 {
      execsql {
        CREATE INDEX i1 ON t1(a);
        CREATE INDEX i2 ON t2(d);
      }
    } {}
  }

  test_transform selectB-$ii.2 {
    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2
  } {2 8 14 3 12 21}
  
  test_transform selectB-$ii.3 {
    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1
  } {2 3 8 12 14 21}
  
  test_transform selectB-$ii.4 {
    SELECT * FROM 
      (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 
    WHERE a>10 ORDER BY 1
  } {
    SELECT a FROM t1 WHERE a>10 UNION ALL SELECT d FROM t2 WHERE d>10 ORDER BY 1
  } {12 14 21}
  
  test_transform selectB-$ii.5 {
    SELECT * FROM 
      (SELECT a FROM t1 UNION ALL SELECT d FROM t2) 
    WHERE a>10 ORDER BY a
  } {
    SELECT a FROM t1 WHERE a>10 
      UNION ALL 
    SELECT d FROM t2 WHERE d>10 
    ORDER BY a
  } {12 14 21}
  
  test_transform selectB-$ii.6 {
    SELECT * FROM 
      (SELECT a FROM t1 UNION ALL SELECT d FROM t2 WHERE d > 12) 
    WHERE a>10 ORDER BY a
  } {
    SELECT a FROM t1 WHERE a>10
      UNION ALL 
    SELECT d FROM t2 WHERE d>12 AND d>10
    ORDER BY a
  } {14 21}
  
  test_transform selectB-$ii.7 {
    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 
    LIMIT 2
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2
  } {2 3}
  
  test_transform selectB-$ii.8 {
    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2) ORDER BY 1 
    LIMIT 2 OFFSET 3
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 LIMIT 2 OFFSET 3
  } {12 14}

  test_transform selectB-$ii.9 {
    SELECT * FROM (
      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
    ) 
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
  } {2 8 14 3 12 21 6 12 18}
  
  test_transform selectB-$ii.10 {
    SELECT * FROM (
      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
    ) ORDER BY 1
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
    ORDER BY 1
  } {2 3 6 8 12 12 14 18 21}
  
  test_transform selectB-$ii.11 {
    SELECT * FROM (
      SELECT a FROM t1 UNION ALL SELECT d FROM t2 UNION ALL SELECT c FROM t1
    ) WHERE a>=10 ORDER BY 1 LIMIT 3
  } {
    SELECT a FROM t1 WHERE a>=10 UNION ALL SELECT d FROM t2 WHERE d>=10
    UNION ALL SELECT c FROM t1 WHERE c>=10
    ORDER BY 1 LIMIT 3
  } {12 12 14}

  test_transform selectB-$ii.12 {
    SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2)
  } {
    SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 2
  } {2 8}

  # An ORDER BY in a compound subqueries defeats flattening.  Ticket #3773
  # test_transform selectB-$ii.13 {
  #   SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a ASC)
  # } {
  #   SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 ASC
  # } {2 3 8 12 14 21}
  # 
  # test_transform selectB-$ii.14 {
  #  SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC)
  # } {
  #  SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
  # } {21 14 12 8 3 2}
  #
  # test_transform selectB-$ii.14 {
  #   SELECT * FROM (
  #     SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY a DESC
  #   ) LIMIT 2 OFFSET 2
  # } {
  #   SELECT a FROM t1 UNION ALL SELECT d FROM t2 ORDER BY 1 DESC
  #    LIMIT 2 OFFSET 2
  # } {12 8}
  #
  # test_transform selectB-$ii.15 {
  #   SELECT * FROM (
  #     SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
  #  )
  # } {
  #   SELECT a, b FROM t1 UNION ALL SELECT d, e FROM t2 ORDER BY a ASC, e DESC
  # } {2 4 3 6 8 10 12 15 14 16 21 24}
}

do_test selectB-3.0 {
  execsql {
    DROP INDEX i1;
    DROP INDEX i2;
  }
} {}

for {set ii 3} {$ii <= 6} {incr ii} {

  switch $ii {
    4 {
      optimization_control db query-flattener off
    }
    5 {
      optimization_control db query-flattener on
      do_test selectB-5.0 {
        execsql {
          CREATE INDEX i1 ON t1(a);
          CREATE INDEX i2 ON t1(b);
          CREATE INDEX i3 ON t1(c);
          CREATE INDEX i4 ON t2(d);
          CREATE INDEX i5 ON t2(e);
          CREATE INDEX i6 ON t2(f);
        }
      } {}
    }
    6 {
      optimization_control db query-flattener off
    }
  }

  do_test selectB-$ii.1 {
    execsql {
      SELECT DISTINCT * FROM 
        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
      ORDER BY 1;
    }
  } {6 12 15 18 24}
  
  do_test selectB-$ii.2 {
    execsql {
      SELECT c, count(*) FROM 
        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
      GROUP BY c ORDER BY 1;
    }
  } {6 2 12 1 15 1 18 1 24 1}
  do_test selectB-$ii.3 {
    execsql {
      SELECT c, count(*) FROM 
        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) 
      GROUP BY c HAVING count(*)>1;
    }
  } {6 2}
  do_test selectB-$ii.4 {
    execsql {
      SELECT t4.c, t3.a FROM 
        (SELECT c FROM t1 UNION ALL SELECT e FROM t2) AS t4, t1 AS t3
      WHERE t3.a=14
      ORDER BY 1
    }
  } {6 14 6 14 12 14 15 14 18 14 24 14}
  
  do_test selectB-$ii.5 {
    execsql {
      SELECT d FROM t2 
      EXCEPT 
      SELECT a FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
    }
  } {}
  do_test selectB-$ii.6 {
    execsql {
      SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
      EXCEPT 
      SELECT * FROM (SELECT a FROM t1 UNION ALL SELECT d FROM t2)
    }
  } {}
  do_test selectB-$ii.7 {
    execsql {
      SELECT c FROM t1
      EXCEPT 
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
    }
  } {12}
  do_test selectB-$ii.8 {
    execsql {
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      EXCEPT 
      SELECT c FROM t1
    }
  } {9 15 24 27}
  do_test selectB-$ii.9 {
    execsql {
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      EXCEPT 
      SELECT c FROM t1
      ORDER BY c DESC
    }
  } {27 24 15 9}
  
  do_test selectB-$ii.10 {
    execsql {
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      UNION 
      SELECT c FROM t1
      ORDER BY c DESC
    }
  } {27 24 18 15 12 9 6}
  do_test selectB-$ii.11 {
    execsql {
      SELECT c FROM t1
      UNION 
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      ORDER BY c
    }
  } {6 9 12 15 18 24 27}
  do_test selectB-$ii.12 {
    execsql {
      SELECT c FROM t1 UNION SELECT e FROM t2 UNION ALL SELECT f FROM t2
      ORDER BY c
    }
  } {6 9 12 15 18 18 24 27}
  do_test selectB-$ii.13 {
    execsql {
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      UNION 
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      ORDER BY 1
    }
  } {6 9 15 18 24 27}
  
  do_test selectB-$ii.14 {
    execsql {
      SELECT c FROM t1
      INTERSECT 
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      ORDER BY 1
    }
  } {6 18}
  do_test selectB-$ii.15 {
    execsql {
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      INTERSECT 
      SELECT c FROM t1
      ORDER BY 1
    }
  } {6 18}
  do_test selectB-$ii.16 {
    execsql {
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      INTERSECT 
      SELECT * FROM (SELECT e FROM t2 UNION ALL SELECT f FROM t2)
      ORDER BY 1
    }
  } {6 9 15 18 24 27}

  do_test selectB-$ii.17 {
    execsql {
      SELECT * FROM (
        SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4
      ) LIMIT 2
    }
  } {2 8}

  do_test selectB-$ii.18 {
    execsql {
      SELECT * FROM (
        SELECT a FROM t1 UNION ALL SELECT d FROM t2 LIMIT 4 OFFSET 2
      ) LIMIT 2
    }
  } {14 3}

  do_test selectB-$ii.19 {
    execsql {
      SELECT * FROM (
        SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
      )
    }
  } {0 1 1 0}

  do_test selectB-$ii.20 {
    execsql {
      SELECT DISTINCT * FROM (
        SELECT DISTINCT (a/10) FROM t1 UNION ALL SELECT DISTINCT(d%2) FROM t2
      )
    }
  } {0 1}

  do_test selectB-$ii.21 {
    execsql {
      SELECT * FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2) ORDER BY a+b
    }
  } {2 4 6 3 6 9 8 10 12 12 15 18 14 16 18 21 24 27}

  do_test selectB-$ii.22 {
    execsql {
      SELECT * FROM (SELECT 345 UNION ALL SELECT d FROM t2) ORDER BY 1;
    }
  } {3 12 21 345}

  do_test selectB-$ii.23 {
    execsql {
      SELECT x, y FROM (
        SELECT a AS x, b AS y FROM t1
        UNION ALL
        SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 JOIN t2 ON (c=d)
        UNION ALL
        SELECT a*100, b*100 FROM t1
      ) ORDER BY 1;
    }
  } {2 4 8 10 14 16 80.1 180.1 200 400 800 1000 1400 1600}

  do_test selectB-$ii.24 {
    execsql {
      SELECT x, y FROM (
        SELECT a AS x, b AS y FROM t1
        UNION ALL
        SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
        UNION ALL
        SELECT a*100, b*100 FROM t1
      ) ORDER BY 1;
    }
  } {2 4 8 10 14 16 20.1 {} 80.1 180.1 140.1 {} 200 400 800 1000 1400 1600}

  do_test selectB-$ii.25 {
    execsql {
      SELECT x+y FROM (
        SELECT a AS x, b AS y FROM t1
        UNION ALL
        SELECT a*10 + 0.1, f*10 + 0.1 FROM t1 LEFT JOIN t2 ON (c=d)
        UNION ALL
        SELECT a*100, b*100 FROM t1
      ) WHERE y+x NOT NULL ORDER BY 1;
    }
  } {6 18 30 260.2 600 1800 3000}
}

finish_test