select4.test

最新的sqlite3.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
  }
} {x 0 y 1}
do_test select4-9.3 {
  execsql2 {
    SELECT x, y FROM t2 EXCEPT SELECT a, b FROM t3 ORDER BY x LIMIT 1
  }
} {x 0 y 1}
do_test select4-9.4 {
  execsql2 {
    SELECT x, y FROM t2 INTERSECT SELECT 0 AS a, 1 AS b;
  }
} {x 0 y 1}
do_test select4-9.5 {
  execsql2 {
    SELECT 0 AS x, 1 AS y
    UNION
    SELECT 2 AS p, 3 AS q
    UNION
    SELECT 4 AS a, 5 AS b
    ORDER BY x LIMIT 1
  }
} {x 0 y 1}

ifcapable subquery {
do_test select4-9.6 {
  execsql2 {
    SELECT * FROM (
      SELECT 0 AS x, 1 AS y
      UNION
      SELECT 2 AS p, 3 AS q
      UNION
      SELECT 4 AS a, 5 AS b
    ) ORDER BY 1 LIMIT 1;
  }
} {x 0 y 1}
do_test select4-9.7 {
  execsql2 {
    SELECT * FROM (
      SELECT 0 AS x, 1 AS y
      UNION
      SELECT 2 AS p, 3 AS q
      UNION
      SELECT 4 AS a, 5 AS b
    ) ORDER BY x LIMIT 1;
  }
} {x 0 y 1}
} ;# ifcapable subquery

do_test select4-9.8 {
  execsql {
    SELECT 0 AS x, 1 AS y
    UNION
    SELECT 2 AS y, -3 AS x
    ORDER BY x LIMIT 1;
  }
} {0 1}

do_test select4-9.9.1 {
  execsql2 {
    SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a
  }
} {a 1 b 2 a 3 b 4}

ifcapable subquery {
do_test select4-9.9.2 {
  execsql2 {
    SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
     WHERE b=3
  }
} {}
do_test select4-9.10 {
  execsql2 {
    SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS b, 4 AS a)
     WHERE b=2
  }
} {a 1 b 2}
do_test select4-9.11 {
  execsql2 {
    SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
     WHERE b=2
  }
} {a 1 b 2}
do_test select4-9.12 {
  execsql2 {
    SELECT * FROM (SELECT 1 AS a, 2 AS b UNION ALL SELECT 3 AS e, 4 AS b)
     WHERE b>0
  }
} {a 1 b 2 a 3 b 4}
} ;# ifcapable subquery

# Try combining DISTINCT, LIMIT, and OFFSET.  Make sure they all work
# together.
#
do_test select4-10.1 {
  execsql {
    SELECT DISTINCT log FROM t1 ORDER BY log
  }
} {0 1 2 3 4 5}
do_test select4-10.2 {
  execsql {
    SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 4
  }
} {0 1 2 3}
do_test select4-10.3 {
  execsql {
    SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0
  }
} {}
do_test select4-10.4 {
  execsql {
    SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1
  }
} {0 1 2 3 4 5}
do_test select4-10.5 {
  execsql {
    SELECT DISTINCT log FROM t1 ORDER BY log LIMIT -1 OFFSET 2
  }
} {2 3 4 5}
do_test select4-10.6 {
  execsql {
    SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 3 OFFSET 2
  }
} {2 3 4}
do_test select4-10.7 {
  execsql {
    SELECT DISTINCT log FROM t1 ORDER BY +log LIMIT 3 OFFSET 20
  }
} {}
do_test select4-10.8 {
  execsql {
    SELECT DISTINCT log FROM t1 ORDER BY log LIMIT 0 OFFSET 3
  }
} {}
do_test select4-10.9 {
  execsql {
    SELECT DISTINCT max(n), log FROM t1 ORDER BY +log; -- LIMIT 2 OFFSET 1
  }
} {31 5}

# Make sure compound SELECTs with wildly different numbers of columns
# do not cause assertion faults due to register allocation issues.
#
do_test select4-11.1 {
  catchsql {
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
    UNION
    SELECT x FROM t2
  }
} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
do_test select4-11.2 {
  catchsql {
    SELECT x FROM t2
    UNION
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
  }
} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
do_test select4-11.3 {
  catchsql {
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
    UNION ALL
    SELECT x FROM t2
  }
} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
do_test select4-11.4 {
  catchsql {
    SELECT x FROM t2
    UNION ALL
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
  }
} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
do_test select4-11.5 {
  catchsql {
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
    EXCEPT
    SELECT x FROM t2
  }
} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
do_test select4-11.6 {
  catchsql {
    SELECT x FROM t2
    EXCEPT
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
  }
} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
do_test select4-11.7 {
  catchsql {
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
    INTERSECT
    SELECT x FROM t2
  }
} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
do_test select4-11.8 {
  catchsql {
    SELECT x FROM t2
    INTERSECT
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
  }
} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}

do_test select4-11.11 {
  catchsql {
    SELECT x FROM t2
    UNION
    SELECT x FROM t2
    UNION ALL
    SELECT x FROM t2
    EXCEPT
    SELECT x FROM t2
    INTERSECT
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
  }
} {1 {SELECTs to the left and right of INTERSECT do not have the same number of result columns}}
do_test select4-11.12 {
  catchsql {
    SELECT x FROM t2
    UNION
    SELECT x FROM t2
    UNION ALL
    SELECT x FROM t2
    EXCEPT
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
    EXCEPT
    SELECT x FROM t2
  }
} {1 {SELECTs to the left and right of EXCEPT do not have the same number of result columns}}
do_test select4-11.13 {
  catchsql {
    SELECT x FROM t2
    UNION
    SELECT x FROM t2
    UNION ALL
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
    UNION ALL
    SELECT x FROM t2
    EXCEPT
    SELECT x FROM t2
  }
} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
do_test select4-11.14 {
  catchsql {
    SELECT x FROM t2
    UNION
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
    UNION
    SELECT x FROM t2
    UNION ALL
    SELECT x FROM t2
    EXCEPT
    SELECT x FROM t2
  }
} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}
do_test select4-11.15 {
  catchsql {
    SELECT x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x,x FROM t2
    UNION
    SELECT x FROM t2
    INTERSECT
    SELECT x FROM t2
    UNION ALL
    SELECT x FROM t2
    EXCEPT
    SELECT x FROM t2
  }
} {1 {SELECTs to the left and right of UNION do not have the same number of result columns}}

} ;# ifcapable compound

finish_test