summaryrefslogtreecommitdiff
path: root/mysql-test/r/range.result
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/r/range.result')
-rw-r--r--mysql-test/r/range.result435
1 files changed, 435 insertions, 0 deletions
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result
index 27f62e180d2..f6ce69f4069 100644
--- a/mysql-test/r/range.result
+++ b/mysql-test/r/range.result
@@ -1219,3 +1219,438 @@ explain select * from t2 where a=1000 and b<11;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ref a a 5 const 502 Using index condition
drop table t1, t2;
+CREATE TABLE t1( a INT, b INT, KEY( a, b ) );
+CREATE TABLE t2( a INT, b INT, KEY( a, b ) );
+CREATE TABLE t3( a INT, b INT, KEY( a, b ) );
+INSERT INTO t1( a, b )
+VALUES (0, 1), (1, 2), (1, 4), (2, 3), (5, 0), (9, 7);
+INSERT INTO t2( a, b )
+VALUES ( 1, 1), ( 2, 1), ( 3, 1), ( 4, 1), ( 5, 1),
+( 6, 1), ( 7, 1), ( 8, 1), ( 9, 1), (10, 1),
+(11, 1), (12, 1), (13, 1), (14, 1), (15, 1),
+(16, 1), (17, 1), (18, 1), (19, 1), (20, 1);
+INSERT INTO t2 SELECT a, 2 FROM t2 WHERE b = 1;
+INSERT INTO t2 SELECT a, 3 FROM t2 WHERE b = 1;
+INSERT INTO t2 SELECT -1, -1 FROM t2;
+INSERT INTO t2 SELECT -1, -1 FROM t2;
+INSERT INTO t2 SELECT -1, -1 FROM t2;
+INSERT INTO t3
+VALUES (1, 0), (2, 0), (3, 0), (4, 0), (5, 0),
+(6, 0), (7, 0), (8, 0), (9, 0), (10, 0);
+INSERT INTO t3 SELECT * FROM t3 WHERE a = 10;
+INSERT INTO t3 SELECT * FROM t3 WHERE a = 10;
+SELECT * FROM t1 WHERE
+3 <= a AND a < 5 OR
+5 < a AND b = 3 OR
+3 <= a;
+a b
+5 0
+9 7
+EXPLAIN
+SELECT * FROM t1 WHERE
+3 <= a AND a < 5 OR
+5 < a AND b = 3 OR
+3 <= a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index
+SELECT * FROM t1 WHERE
+3 <= a AND a < 5 OR
+5 <= a AND b = 3 OR
+3 <= a;
+a b
+5 0
+9 7
+EXPLAIN
+SELECT * FROM t1 WHERE
+3 <= a AND a < 5 OR
+5 <= a AND b = 3 OR
+3 <= a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 4 Using where; Using index
+SELECT * FROM t1 WHERE
+3 <= a AND a <= 5 OR
+5 <= a AND b = 3 OR
+3 <= a;
+a b
+5 0
+9 7
+EXPLAIN
+SELECT * FROM t1 WHERE
+3 <= a AND a <= 5 OR
+5 <= a AND b = 3 OR
+3 <= a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index
+SELECT * FROM t1 WHERE
+3 <= a AND a <= 5 OR
+3 <= a;
+a b
+5 0
+9 7
+EXPLAIN
+SELECT * FROM t1 WHERE
+3 <= a AND a <= 5 OR
+3 <= a;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 range a a 5 NULL 3 Using where; Using index
+SELECT * FROM t2 WHERE
+5 <= a AND a < 10 AND b = 1 OR
+15 <= a AND a < 20 AND b = 3
+OR
+1 <= a AND b = 1;
+a b
+1 1
+2 1
+3 1
+4 1
+5 1
+6 1
+7 1
+8 1
+9 1
+10 1
+11 1
+12 1
+13 1
+14 1
+15 1
+15 3
+16 1
+16 3
+17 1
+17 3
+18 1
+18 3
+19 1
+19 3
+20 1
+EXPLAIN
+SELECT * FROM t2 WHERE
+5 <= a AND a < 10 AND b = 1 OR
+15 <= a AND a < 20 AND b = 3
+OR
+1 <= a AND b = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range a a 10 NULL 50 Using where; Using index
+SELECT * FROM t2 WHERE
+5 <= a AND a < 10 AND b = 2 OR
+15 <= a AND a < 20 AND b = 3
+OR
+1 <= a AND b = 1;
+a b
+1 1
+2 1
+3 1
+4 1
+5 1
+5 2
+6 1
+6 2
+7 1
+7 2
+8 1
+8 2
+9 1
+9 2
+10 1
+11 1
+12 1
+13 1
+14 1
+15 1
+15 3
+16 1
+16 3
+17 1
+17 3
+18 1
+18 3
+19 1
+19 3
+20 1
+EXPLAIN
+SELECT * FROM t2 WHERE
+5 <= a AND a < 10 AND b = 2 OR
+15 <= a AND a < 20 AND b = 3
+OR
+1 <= a AND b = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 range a a 10 NULL 50 Using where; Using index
+SELECT * FROM t3 WHERE
+5 <= a AND a < 10 AND b = 3 OR
+a < 5 OR
+a < 10;
+a b
+1 0
+2 0
+3 0
+4 0
+5 0
+6 0
+7 0
+8 0
+9 0
+EXPLAIN
+SELECT * FROM t3 WHERE
+5 <= a AND a < 10 AND b = 3 OR
+a < 5 OR
+a < 10;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t3 range a a 5 NULL 8 Using where; Using index
+DROP TABLE t1, t2, t3;
+#
+# Bug #47123: Endless 100% CPU loop with STRAIGHT_JOIN
+#
+CREATE TABLE t1(a INT, KEY(a));
+INSERT INTO t1 VALUES (1), (NULL);
+SELECT * FROM t1 WHERE a <> NULL and (a <> NULL or a <= NULL);
+a
+DROP TABLE t1;
+#
+# Bug#47925: regression of range optimizer and date comparison in 5.1.39!
+#
+CREATE TABLE t1 ( a DATE, KEY ( a ) );
+CREATE TABLE t2 ( a DATETIME, KEY ( a ) );
+# Make optimizer choose range scan
+INSERT INTO t1 VALUES ('2009-09-22'), ('2009-09-22'), ('2009-09-22');
+INSERT INTO t1 VALUES ('2009-09-23'), ('2009-09-23'), ('2009-09-23');
+INSERT INTO t2 VALUES ('2009-09-22 12:00:00'), ('2009-09-22 12:00:00'),
+('2009-09-22 12:00:00');
+INSERT INTO t2 VALUES ('2009-09-23 12:00:00'), ('2009-09-23 12:00:00'),
+('2009-09-23 12:00:00');
+# DATE vs DATE
+EXPLAIN
+SELECT * FROM t1 WHERE a >= '2009/09/23';
+id select_type table type possible_keys key key_len ref rows Extra
+X X X range a a X X X X
+SELECT * FROM t1 WHERE a >= '2009/09/23';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '20090923';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= 20090923;
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009-9-23';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009.09.23';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009:09:23';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+# DATE vs DATETIME
+EXPLAIN
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+id select_type table type possible_keys key key_len ref rows Extra
+X X X range a a X X X X
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '20090923';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= 20090923;
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009-9-23';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009.09.23';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009:09:23';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+# DATETIME vs DATETIME
+EXPLAIN
+SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00';
+id select_type table type possible_keys key key_len ref rows Extra
+X X X range a a X X X X
+SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '20090923120000';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= 20090923120000;
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009-9-23 12:00:00';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009.09.23 12:00:00';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+SELECT * FROM t2 WHERE a >= '2009:09:23 12:00:00';
+a
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+2009-09-23 12:00:00
+# DATETIME vs DATE
+EXPLAIN
+SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
+id select_type table type possible_keys key key_len ref rows Extra
+X X X range a a X X X X
+SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '20090923000000';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= 20090923000000;
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009-9-23 00:00:00';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009.09.23 00:00:00';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+SELECT * FROM t1 WHERE a >= '2009:09:23 00:00:00';
+a
+2009-09-23
+2009-09-23
+2009-09-23
+# Test of the new get_date_from_str implementation
+# Behavior differs slightly between the trunk and mysql-pe.
+# The former may give errors for the truncated values, while the latter
+# gives warnings. The purpose of this test is not to interfere, and only
+# preserve existing behavior.
+SELECT str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND
+str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20';
+str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND
+str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20'
+1
+Warnings:
+Warning 1292 Truncated incorrect date value: ''
+SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND
+str_to_date('2007-20-00', '%Y-%m-%d') <= '';
+str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND
+str_to_date('2007-20-00', '%Y-%m-%d') <= ''
+NULL
+Warnings:
+Warning 1292 Truncated incorrect date value: ''
+Error 1411 Incorrect datetime value: '2007-20-00' for function str_to_date
+Error 1411 Incorrect datetime value: '2007-20-00' for function str_to_date
+SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20';
+str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20'
+1
+Warnings:
+Warning 1292 Truncated incorrect datetime value: ''
+SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND '';
+str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND ''
+NULL
+Warnings:
+Error 1411 Incorrect datetime value: '2007-20-00' for function str_to_date
+SELECT str_to_date('', '%Y-%m-%d');
+str_to_date('', '%Y-%m-%d')
+0000-00-00
+DROP TABLE t1, t2;
+#
+# Bug#48459: valgrind errors with query using 'Range checked for each
+# record'
+#
+CREATE TABLE t1 (
+a INT,
+b CHAR(2),
+c INT,
+d INT,
+KEY ( c ),
+KEY ( d, a, b ( 2 ) ),
+KEY ( b ( 1 ) )
+);
+INSERT INTO t1 VALUES ( NULL, 'a', 1, 2 ), ( NULL, 'a', 1, 2 ),
+( 1, 'a', 1, 2 ), ( 1, 'a', 1, 2 );
+CREATE TABLE t2 (
+a INT,
+c INT,
+e INT,
+KEY ( e )
+);
+INSERT INTO t2 VALUES ( 1, 1, NULL ), ( 1, 1, NULL );
+# Should not give Valgrind warnings
+SELECT 1
+FROM t1, t2
+WHERE t1.d <> '1' AND t1.b > '1'
+AND t1.a = t2.a AND t1.c = t2.c;
+1
+1
+1
+1
+1
+DROP TABLE t1, t2;
+#
+# Bug #48665: sql-bench's insert test fails due to wrong result
+#
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (0,0), (1,1);
+EXPLAIN
+SELECT * FROM t1 FORCE INDEX (PRIMARY)
+WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10);
+id select_type table type possible_keys key key_len ref rows Extra
+@ @ @ range @ @ @ @ @ @
+# Should return 2 rows
+SELECT * FROM t1 FORCE INDEX (PRIMARY)
+WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10);
+a b
+0 0
+1 1
+DROP TABLE t1;
+End of 5.1 tests
View Lorry Controller Status