selecta.test

最新的sqlite3.6.2源代码

    ORDER BY c
  }
} {hello d D abc e e}
do_test selectA-2.61 {
  execsql {
    SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
    ORDER BY c COLLATE BINARY, b DESC, c, a, b, c, a, b, c
  }
} {hello d D abc e e}
do_test selectA-2.62 {
  execsql {
    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
    ORDER BY c DESC, a
  }
} {abc e e hello d D}
do_test selectA-2.63 {
  execsql {
    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
    ORDER BY c COLLATE NOCASE
  }
} {1 a a 9.9 b B {} C c}
do_test selectA-2.64 {
  execsql {
    SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
    ORDER BY c
  }
} {1 a a 9.9 b B {} C c}
do_test selectA-2.65 {
  execsql {
    SELECT a,b,c FROM t3 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
    ORDER BY c COLLATE NOCASE
  }
} {1 a a 9.9 b B {} C c}
do_test selectA-2.66 {
  execsql {
    SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t3
    ORDER BY c
  }
} {1 a a 9.9 b B {} C c}
do_test selectA-2.67 {
  execsql {
    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t3 WHERE b<'d'
    ORDER BY c DESC, a
  }
} {abc e e hello d D}
do_test selectA-2.68 {
  execsql {
    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
    INTERSECT SELECT a,b,c FROM t3
    EXCEPT SELECT b,c,a FROM t3
    ORDER BY c DESC, a
  }
} {abc e e hello d D}
do_test selectA-2.69 {
  execsql {
    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
    INTERSECT SELECT a,b,c FROM t3
    EXCEPT SELECT b,c,a FROM t3
    ORDER BY c COLLATE NOCASE
  }
} {1 a a 9.9 b B {} C c}
do_test selectA-2.70 {
  execsql {
    SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
    INTERSECT SELECT a,b,c FROM t3
    EXCEPT SELECT b,c,a FROM t3
    ORDER BY c
  }
} {1 a a 9.9 b B {} C c}
do_test selectA-2.71 {
  execsql {
    SELECT a,b,c FROM t1 WHERE b<'d'
    INTERSECT SELECT a,b,c FROM t1
    INTERSECT SELECT a,b,c FROM t3
    EXCEPT SELECT b,c,a FROM t3
    INTERSECT SELECT a,b,c FROM t1
    EXCEPT SELECT x,y,z FROM t2
    INTERSECT SELECT a,b,c FROM t3
    EXCEPT SELECT y,x,z FROM t2
    INTERSECT SELECT a,b,c FROM t1
    EXCEPT SELECT c,b,a FROM t3
    ORDER BY c
  }
} {1 a a 9.9 b B {} C c}
do_test selectA-2.72 {
  execsql {
    SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
    ORDER BY a,b,c
  }
} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
do_test selectA-2.73 {
  execsql {
    SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
    ORDER BY a DESC,b,c
  }
} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
do_test selectA-2.74 {
  execsql {
    SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
    ORDER BY a,c,b
  }
} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
do_test selectA-2.75 {
  execsql {
    SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
    ORDER BY b,a,c
  }
} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
do_test selectA-2.76 {
  execsql {
    SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
    ORDER BY b COLLATE NOCASE,a,c
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-2.77 {
  execsql {
    SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
    ORDER BY b COLLATE NOCASE DESC,a,c
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-2.78 {
  execsql {
    SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
    ORDER BY c,b,a
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-2.79 {
  execsql {
    SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
    ORDER BY c,a,b
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-2.80 {
  execsql {
    SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
    ORDER BY c DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-2.81 {
  execsql {
    SELECT a,b,c FROM t3 UNION SELECT x,y,z FROM t2
    ORDER BY c COLLATE BINARY DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
do_test selectA-2.82 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY a,b,c
  }
} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
do_test selectA-2.83 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY a DESC,b,c
  }
} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
do_test selectA-2.84 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY a,c,b
  }
} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
do_test selectA-2.85 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY b,a,c
  }
} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
do_test selectA-2.86 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY b COLLATE NOCASE,a,c
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-2.87 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY y COLLATE NOCASE DESC,x,z
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-2.88 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY c,b,a
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-2.89 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY c,a,b
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-2.90 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY c DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-2.91 {
  execsql {
    SELECT x,y,z FROM t2 UNION SELECT a,b,c FROM t3
    ORDER BY c COLLATE BINARY DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
do_test selectA-2.92 {
  execsql {
    SELECT x,y,z FROM t2
    INTERSECT SELECT a,b,c FROM t3
    EXCEPT SELECT c,b,a FROM t1
    UNION SELECT a,b,c FROM t3
    INTERSECT SELECT a,b,c FROM t3
    EXCEPT SELECT c,b,a FROM t1
    UNION SELECT a,b,c FROM t3
    ORDER BY y COLLATE NOCASE DESC,x,z
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-2.93 {
  execsql {
    SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1));
  }
} {A}
do_test selectA-2.94 {
  execsql {
    SELECT lower((SELECT c FROM t1 UNION ALL SELECT z FROM t2 ORDER BY 1));
  }
} {a}
do_test selectA-2.95 {
  execsql {
    SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1));
  }
} {{}}
do_test selectA-2.96 {
  execsql {
    SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1));
  }
} {m}


do_test selectA-3.0 {
  execsql {
    CREATE UNIQUE INDEX t1a ON t1(a);
    CREATE UNIQUE INDEX t1b ON t1(b);
    CREATE UNIQUE INDEX t1c ON t1(c);
    CREATE UNIQUE INDEX t2x ON t2(x);
    CREATE UNIQUE INDEX t2y ON t2(y);
    CREATE UNIQUE INDEX t2z ON t2(z);
    SELECT name FROM sqlite_master WHERE type='index'
  }
} {t1a t1b t1c t2x t2y t2z}
do_test selectA-3.1 {
  execsql {
    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
    ORDER BY a,b,c
  }
} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
do_test selectA-3.1.1 {  # Ticket #3314
  execsql {
    SELECT t1.a,b,t1.c FROM t1 UNION ALL SELECT x,y,z FROM t2
    ORDER BY a,t1.b,t1.c
  }
} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
do_test selectA-3.2 {
  execsql {
    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
    ORDER BY a DESC,b,c
  }
} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
do_test selectA-3.3 {
  execsql {
    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
    ORDER BY a,c,b
  }
} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
do_test selectA-3.4 {
  execsql {
    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
    ORDER BY b,a,c
  }
} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
do_test selectA-3.5 {
  execsql {
    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
    ORDER BY b COLLATE NOCASE,a,c
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-3.6 {
  execsql {
    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
    ORDER BY b COLLATE NOCASE DESC,a,c
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-3.7 {
  execsql {
    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
    ORDER BY c,b,a
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-3.8 {
  execsql {
    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
    ORDER BY c,a,b
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-3.9 {
  execsql {
    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
    ORDER BY c DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-3.10 {
  execsql {
    SELECT a,b,c FROM t1 UNION ALL SELECT x,y,z FROM t2
    ORDER BY c COLLATE BINARY DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
do_test selectA-3.11 {
  execsql {
    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
    ORDER BY a,b,c
  }
} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
do_test selectA-3.12 {
  execsql {
    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
    ORDER BY a DESC,b,c
  }
} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
do_test selectA-3.13 {
  execsql {
    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
    ORDER BY a,c,b
  }
} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
do_test selectA-3.14 {
  execsql {
    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
    ORDER BY b,a,c
  }
} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
do_test selectA-3.15 {
  execsql {
    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
    ORDER BY b COLLATE NOCASE,a,c
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-3.16 {
  execsql {
    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
    ORDER BY b COLLATE NOCASE DESC,a,c
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-3.17 {
  execsql {
    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
    ORDER BY c,b,a
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-3.18 {
  execsql {
    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
    ORDER BY c,a,b
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-3.19 {
  execsql {
    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
    ORDER BY c DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-3.20 {
  execsql {
    SELECT x,y,z FROM t2 UNION ALL SELECT a,b,c FROM t1
    ORDER BY c COLLATE BINARY DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u abc e e {} C c 1 a a hare m M hello d D 9.9 b B}
do_test selectA-3.21 {
  execsql {
    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
    ORDER BY a,b,c
  }
} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
do_test selectA-3.22 {
  execsql {
    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
    ORDER BY a DESC,b,c
  }
} {hare m M abc e e mad Z z hello d D 5200000.0 X x 9.9 b B 1 a a -23 Y y {} C c {} U u}
do_test selectA-3.23 {
  execsql {
    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
    ORDER BY a,c,b
  }
} {{} C c {} U u -23 Y y 1 a a 9.9 b B 5200000.0 X x hello d D mad Z z abc e e hare m M}
do_test selectA-3.24 {
  execsql {
    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
    ORDER BY b,a,c
  }
} {{} C c {} U u 5200000.0 X x -23 Y y mad Z z 1 a a 9.9 b B hello d D abc e e hare m M}
do_test selectA-3.25 {
  execsql {
    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
    ORDER BY b COLLATE NOCASE,a,c
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-3.26 {
  execsql {
    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
    ORDER BY b COLLATE NOCASE DESC,a,c
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-3.27 {
  execsql {
    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
    ORDER BY c,b,a
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-3.28 {
  execsql {
    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
    ORDER BY c,a,b
  }
} {1 a a 9.9 b B {} C c hello d D abc e e hare m M {} U u 5200000.0 X x -23 Y y mad Z z}
do_test selectA-3.29 {
  execsql {
    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
    ORDER BY c DESC,a,b
  }
} {mad Z z -23 Y y 5200000.0 X x {} U u hare m M abc e e hello d D {} C c 9.9 b B 1 a a}
do_test selectA-3.30 {
  execsql {
    SELECT a,b,c FROM t1 UNION SELECT x,y,z FROM t2
    ORDER BY c COLLATE BINARY DESC,a,b