1# Created for verifying bug#20577.
2# expects TABLE t1 (... , a DATE, ...)
3
4--sorted_result
5SELECT * FROM t1 WHERE a < '1001-01-01';
6--sorted_result
7SELECT * FROM t1 WHERE a <= '1001-01-01';
8--sorted_result
9SELECT * FROM t1 WHERE a >= '1001-01-01';
10--sorted_result
11SELECT * FROM t1 WHERE a > '1001-01-01';
12--sorted_result
13SELECT * FROM t1 WHERE a = '1001-01-01';
14--sorted_result
15SELECT * FROM t1 WHERE a < '1001-00-00';
16--sorted_result
17SELECT * FROM t1 WHERE a <= '1001-00-00';
18--sorted_result
19SELECT * FROM t1 WHERE a >= '1001-00-00';
20--sorted_result
21SELECT * FROM t1 WHERE a > '1001-00-00';
22--sorted_result
23SELECT * FROM t1 WHERE a = '1001-00-00';
24--echo # Disabling warnings for the invalid date
25--disable_warnings
26--sorted_result
27SELECT * FROM t1 WHERE a < '1999-02-31';
28--sorted_result
29SELECT * FROM t1 WHERE a <= '1999-02-31';
30--sorted_result
31SELECT * FROM t1 WHERE a >= '1999-02-31';
32--sorted_result
33SELECT * FROM t1 WHERE a > '1999-02-31';
34--sorted_result
35SELECT * FROM t1 WHERE a = '1999-02-31';
36--enable_warnings
37--sorted_result
38SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
39--sorted_result
40SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
41--sorted_result
42SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
43--sorted_result
44SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
45if ($explain_partitions)
46{
47EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-01-01';
48EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-01-01';
49EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-01-01';
50EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-01-01';
51EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-01-01';
52EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1001-00-00';
53EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1001-00-00';
54EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1001-00-00';
55EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1001-00-00';
56EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1001-00-00';
57--echo # Disabling warnings for the invalid date
58--disable_warnings
59EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a < '1999-02-31';
60EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a <= '1999-02-31';
61EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a >= '1999-02-31';
62EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a > '1999-02-31';
63EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a = '1999-02-31';
64--enable_warnings
65EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1002-00-00';
66EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0000-00-00' AND '1001-01-01';
67EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-02' AND '1002-00-00';
68EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a BETWEEN '0001-01-01' AND '1001-01-01';
69}
70