diff options
Diffstat (limited to 'mysql-test/t/range.test')
| -rw-r--r-- | mysql-test/t/range.test | 84 |
1 files changed, 52 insertions, 32 deletions
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 31b3ced6cc6..8d341837acd 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -1,5 +1,5 @@ # -# Problem med range optimizer +# Problem with range optimizer # --disable_warnings @@ -13,7 +13,24 @@ CREATE TABLE t1 ( PRIMARY KEY (event_date,type,event_id) ); -INSERT INTO t1 VALUES ('1999-07-10',100100,24),('1999-07-11',100100,25),('1999-07-13',100600,0),('1999-07-13',100600,4),('1999-07-13',100600,26),('1999-07-14',100600,10),('1999-07-15',100600,16),('1999-07-15',100800,45),('1999-07-15',101000,47),('1999-07-16',100800,46),('1999-07-20',100600,5),('1999-07-20',100600,27),('1999-07-21',100600,11),('1999-07-22',100600,17),('1999-07-23',100100,39),('1999-07-24',100100,39),('1999-07-24',100500,40),('1999-07-25',100100,39),('1999-07-27',100600,1),('1999-07-27',100600,6),('1999-07-27',100600,28),('1999-07-28',100600,12),('1999-07-29',100500,41),('1999-07-29',100600,18),('1999-07-30',100500,41),('1999-07-31',100500,41),('1999-08-01',100700,34),('1999-08-03',100600,7),('1999-08-03',100600,29),('1999-08-04',100600,13),('1999-08-05',100500,42),('1999-08-05',100600,19),('1999-08-06',100500,42),('1999-08-07',100500,42),('1999-08-08',100500,42),('1999-08-10',100600,2),('1999-08-10',100600,9),('1999-08-10',100600,30),('1999-08-11',100600,14),('1999-08-12',100600,20),('1999-08-17',100500,8),('1999-08-17',100600,31),('1999-08-18',100600,15),('1999-08-19',100600,22),('1999-08-24',100600,3),('1999-08-24',100600,32),('1999-08-27',100500,43),('1999-08-31',100600,33),('1999-09-17',100100,37),('1999-09-18',100100,37),('1999-09-19',100100,37),('2000-12-18',100700,38); +INSERT INTO t1 VALUES ('1999-07-10',100100,24), ('1999-07-11',100100,25), +('1999-07-13',100600,0), ('1999-07-13',100600,4), ('1999-07-13',100600,26), +('1999-07-14',100600,10), ('1999-07-15',100600,16), ('1999-07-15',100800,45), +('1999-07-15',101000,47), ('1999-07-16',100800,46), ('1999-07-20',100600,5), +('1999-07-20',100600,27), ('1999-07-21',100600,11), ('1999-07-22',100600,17), +('1999-07-23',100100,39), ('1999-07-24',100100,39), ('1999-07-24',100500,40), +('1999-07-25',100100,39), ('1999-07-27',100600,1), ('1999-07-27',100600,6), +('1999-07-27',100600,28), ('1999-07-28',100600,12), ('1999-07-29',100500,41), +('1999-07-29',100600,18), ('1999-07-30',100500,41), ('1999-07-31',100500,41), +('1999-08-01',100700,34), ('1999-08-03',100600,7), ('1999-08-03',100600,29), +('1999-08-04',100600,13), ('1999-08-05',100500,42), ('1999-08-05',100600,19), +('1999-08-06',100500,42), ('1999-08-07',100500,42), ('1999-08-08',100500,42), +('1999-08-10',100600,2), ('1999-08-10',100600,9), ('1999-08-10',100600,30), +('1999-08-11',100600,14), ('1999-08-12',100600,20), ('1999-08-17',100500,8), +('1999-08-17',100600,31), ('1999-08-18',100600,15), ('1999-08-19',100600,22), +('1999-08-24',100600,3), ('1999-08-24',100600,32), ('1999-08-27',100500,43), +('1999-08-31',100600,33), ('1999-09-17',100100,37), ('1999-09-18',100100,37), +('1999-09-19',100100,37), ('2000-12-18',100700,38); select event_date,type,event_id from t1 WHERE event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date; explain select event_date,type,event_id from t1 WHERE type = 100601 and event_date >= "1999-07-01" AND event_date < "1999-07-15" AND (type=100600 OR type=100100) ORDER BY event_date; @@ -28,35 +45,21 @@ CREATE TABLE t1 ( ISS_DATE date DEFAULT '0000-00-00' NOT NULL, PRIMARY KEY (PAPER_ID,YEAR,ISSUE) ); -INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'); -INSERT INTO t1 VALUES (1,1999,111,0,'1999-03-23'); -INSERT INTO t1 VALUES (1,1999,222,0,'1999-03-23'); -INSERT INTO t1 VALUES (3,1999,33,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,32,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,31,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,30,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,29,0,'1999-07-12'); -INSERT INTO t1 VALUES (3,1999,28,0,'1999-07-12'); -INSERT INTO t1 VALUES (1,1999,40,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,41,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,42,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,46,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,47,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,48,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,49,1,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,50,0,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,51,0,'1999-05-01'); -INSERT INTO t1 VALUES (1,1999,200,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,52,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,53,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,54,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,55,0,'1999-06-28'); -INSERT INTO t1 VALUES (1,1999,56,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,57,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,58,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,59,0,'1999-07-01'); -INSERT INTO t1 VALUES (1,1999,60,0,'1999-07-01'); -INSERT INTO t1 VALUES (3,1999,35,0,'1999-07-12'); +INSERT INTO t1 VALUES (3,1999,34,0,'1999-07-12'), (1,1999,111,0,'1999-03-23'), + (1,1999,222,0,'1999-03-23'), (3,1999,33,0,'1999-07-12'), + (3,1999,32,0,'1999-07-12'), (3,1999,31,0,'1999-07-12'), + (3,1999,30,0,'1999-07-12'), (3,1999,29,0,'1999-07-12'), + (3,1999,28,0,'1999-07-12'), (1,1999,40,1,'1999-05-01'), + (1,1999,41,1,'1999-05-01'), (1,1999,42,1,'1999-05-01'), + (1,1999,46,1,'1999-05-01'), (1,1999,47,1,'1999-05-01'), + (1,1999,48,1,'1999-05-01'), (1,1999,49,1,'1999-05-01'), + (1,1999,50,0,'1999-05-01'), (1,1999,51,0,'1999-05-01'), + (1,1999,200,0,'1999-06-28'), (1,1999,52,0,'1999-06-28'), + (1,1999,53,0,'1999-06-28'), (1,1999,54,0,'1999-06-28'), + (1,1999,55,0,'1999-06-28'), (1,1999,56,0,'1999-07-01'), + (1,1999,57,0,'1999-07-01'), (1,1999,58,0,'1999-07-01'), + (1,1999,59,0,'1999-07-01'), (1,1999,60,0,'1999-07-01'), + (3,1999,35,0,'1999-07-12'); select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28)) order by YEAR,ISSUE; check table t1; repair table t1; @@ -70,7 +73,12 @@ CREATE TABLE t1 ( KEY parent_id (parent_id), KEY level (level) ); -INSERT INTO t1 VALUES (1,0,0),(3,1,1),(4,1,1),(8,2,2),(9,2,2),(17,3,2),(22,4,2),(24,4,2),(28,5,2),(29,5,2),(30,5,2),(31,6,2),(32,6,2),(33,6,2),(203,7,2),(202,7,2),(20,3,2),(157,0,0),(193,5,2),(40,7,2),(2,1,1),(15,2,2),(6,1,1),(34,6,2),(35,6,2),(16,3,2),(7,1,1),(36,7,2),(18,3,2),(26,5,2),(27,5,2),(183,4,2),(38,7,2),(25,5,2),(37,7,2),(21,4,2),(19,3,2),(5,1,1),(179,5,2); +INSERT INTO t1 VALUES (1,0,0), (3,1,1), (4,1,1), (8,2,2), (9,2,2), (17,3,2), +(22,4,2), (24,4,2), (28,5,2), (29,5,2), (30,5,2), (31,6,2), (32,6,2), (33,6,2), +(203,7,2), (202,7,2), (20,3,2), (157,0,0), (193,5,2), (40,7,2), (2,1,1), +(15,2,2), (6,1,1), (34,6,2), (35,6,2), (16,3,2), (7,1,1), (36,7,2), (18,3,2), +(26,5,2), (27,5,2), (183,4,2), (38,7,2), (25,5,2), (37,7,2), (21,4,2), +(19,3,2), (5,1,1), (179,5,2); SELECT * FROM t1 WHERE level = 1 AND parent_id = 1; # The following select returned 0 rows in 3.23.8 SELECT * FROM t1 WHERE level = 1 AND parent_id = 1 order by id; @@ -166,3 +174,15 @@ select count(*) from t1 where art = 'j' or art = 'J'; select count(*) from t1 where art = 'j'; select count(*) from t1 where art = 'J'; drop table t1; + +create table t1 ( id1 int not null, id2 int not null, idnull int null, c char(20), primary key (id1,id2)); +insert into t1 values (0,1,NULL,"aaa"), (1,1,NULL,"aaa"), (2,1,NULL,"aaa"), + (3,1,NULL,"aaa"), (4,1,NULL,"aaa"), (5,1,NULL,"aaa"), + (6,1,NULL,"aaa"), (7,1,NULL,"aaa"), (8,1,NULL,"aaa"), + (9,1,NULL,"aaa"), (10,1,NULL,"aaa"), (11,1,NULL,"aaa"), + (12,1,NULL,"aaa"), (13,1,NULL,"aaa"), (14,1,NULL,"aaa"), + (15,1,NULL,"aaa"), (16,1,NULL,"aaa"), (17,1,NULL,"aaa"), + (18,1,NULL,"aaa"), (19,1,NULL,"aaa"), (20,1,NULL,"aaa"); +select a.id1, b.idnull from t1 as a, t1 as b where a.id2=1 and a.id1=1 and b.id1=a.idnull order by b.id2 desc limit 1; +drop table t1; + |