selecta.test

最新的sqlite3.6.2源代码

  }
} {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.31 {
  execsql {
    SELECT x,y,z FROM t2 UNION 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.32 {
  execsql {
    SELECT x,y,z FROM t2 UNION 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.33 {
  execsql {
    SELECT x,y,z FROM t2 UNION 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.34 {
  execsql {
    SELECT x,y,z FROM t2 UNION 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.35 {
  execsql {
    SELECT x,y,z FROM t2 UNION 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.36 {
  execsql {
    SELECT x,y,z FROM t2 UNION 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.37 {
  execsql {
    SELECT x,y,z FROM t2 UNION 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.38 {
  execsql {
    SELECT x,y,z FROM t2 UNION 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.39 {
  execsql {
    SELECT x,y,z FROM t2 UNION 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.40 {
  execsql {
    SELECT x,y,z FROM t2 UNION 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.41 {
  execsql {
    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
    ORDER BY a,b,c
  }
} {{} C c 1 a a 9.9 b B}
do_test selectA-3.42 {
  execsql {
    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
    ORDER BY a,b,c
  }
} {hello d D abc e e}
do_test selectA-3.43 {
  execsql {
    SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
    ORDER BY a,b,c
  }
} {hello d D abc e e}
do_test selectA-3.44 {
  execsql {
    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
    ORDER BY a,b,c
  }
} {hello d D abc e e}
do_test selectA-3.45 {
  execsql {
    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
    ORDER BY a,b,c
  }
} {{} C c 1 a a 9.9 b B}
do_test selectA-3.46 {
  execsql {
    SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
    ORDER BY a,b,c
  }
} {{} C c 1 a a 9.9 b B}
do_test selectA-3.47 {
  execsql {
    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
    ORDER BY a DESC
  }
} {9.9 b B 1 a a {} C c}
do_test selectA-3.48 {
  execsql {
    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
    ORDER BY a DESC
  }
} {abc e e hello d D}
do_test selectA-3.49 {
  execsql {
    SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
    ORDER BY a DESC
  }
} {abc e e hello d D}
do_test selectA-3.50 {
  execsql {
    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
    ORDER BY a DESC
  }
} {abc e e hello d D}
do_test selectA-3.51 {
  execsql {
    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
    ORDER BY a DESC
  }
} {9.9 b B 1 a a {} C c}
do_test selectA-3.52 {
  execsql {
    SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
    ORDER BY a DESC
  }
} {9.9 b B 1 a a {} C c}
do_test selectA-3.53 {
  execsql {
    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
    ORDER BY b, a DESC
  }
} {{} C c 1 a a 9.9 b B}
do_test selectA-3.54 {
  execsql {
    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
    ORDER BY b
  }
} {hello d D abc e e}
do_test selectA-3.55 {
  execsql {
    SELECT a,b,c FROM t1 WHERE b>='d' INTERSECT SELECT a,b,c FROM t1
    ORDER BY b DESC, c
  }
} {abc e e hello d D}
do_test selectA-3.56 {
  execsql {
    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b<'d'
    ORDER BY b, c DESC, a
  }
} {hello d D abc e e}
do_test selectA-3.57 {
  execsql {
    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b<'d'
    ORDER BY b COLLATE NOCASE
  }
} {1 a a 9.9 b B {} C c}
do_test selectA-3.58 {
  execsql {
    SELECT a,b,c FROM t1 WHERE b<'d' INTERSECT SELECT a,b,c FROM t1
    ORDER BY b
  }
} {{} C c 1 a a 9.9 b B}
do_test selectA-3.59 {
  execsql {
    SELECT a,b,c FROM t1 EXCEPT SELECT a,b,c FROM t1 WHERE b>='d'
    ORDER BY c, a DESC
  }
} {1 a a 9.9 b B {} C c}
do_test selectA-3.60 {
  execsql {
    SELECT a,b,c FROM t1 INTERSECT SELECT a,b,c FROM t1 WHERE b>='d'
    ORDER BY c
  }
} {hello d D abc e e}
do_test selectA-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.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-3.93 {
  execsql {
    SELECT upper((SELECT c FROM t1 UNION SELECT z FROM t2 ORDER BY 1));
  }
} {A}
do_test selectA-3.94 {
  execsql {
    SELECT lower((SELECT c FROM t1 UNION ALL SELECT z FROM t2 ORDER BY 1));
  }
} {a}
do_test selectA-3.95 {
  execsql {
    SELECT lower((SELECT c FROM t1 INTERSECT SELECT z FROM t2 ORDER BY 1));
  }
} {{}}
do_test selectA-3.96 {
  execsql {
    SELECT lower((SELECT z FROM t2 EXCEPT SELECT c FROM t1 ORDER BY 1));
  }
} {m}
do_test selectA-3.97 {
  execsql {
    SELECT upper((SELECT x FROM (
      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}

finish_test