diff options
Diffstat (limited to 'mysql-test/r/range.result')
| -rw-r--r-- | mysql-test/r/range.result | 85 |
1 files changed, 85 insertions, 0 deletions
diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index e0e43f44a4b..990b00fc818 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -1,3 +1,12 @@ +drop table if exists t1; +CREATE TABLE t1 ( +event_date date DEFAULT '0000-00-00' NOT NULL, +type int(11) DEFAULT '0' NOT NULL, +event_id int(11) DEFAULT '0' NOT NULL, +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); +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; event_date type event_id 1999-07-10 100100 24 1999-07-11 100100 25 @@ -5,8 +14,10 @@ event_date type event_id 1999-07-13 100600 4 1999-07-13 100600 26 1999-07-14 100600 10 +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; Comment Impossible WHERE +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) or event_date >= "1999-07-01" AND event_date <= "1999-07-15" AND type=100099; event_date type event_id 1999-07-10 100100 24 1999-07-11 100100 25 @@ -15,6 +26,45 @@ event_date type event_id 1999-07-13 100600 26 1999-07-14 100600 10 1999-07-15 100600 16 +drop table t1; +CREATE TABLE t1 ( +PAPER_ID smallint(6) DEFAULT '0' NOT NULL, +YEAR smallint(6) DEFAULT '0' NOT NULL, +ISSUE smallint(6) DEFAULT '0' NOT NULL, +CLOSED tinyint(4) DEFAULT '0' NOT NULL, +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'); +select YEAR,ISSUE from t1 where PAPER_ID=3 and (YEAR>1999 or (YEAR=1999 and ISSUE>28)) order by YEAR,ISSUE; YEAR ISSUE 1999 29 1999 30 @@ -23,10 +73,23 @@ YEAR ISSUE 1999 33 1999 34 1999 35 +check table t1; Table Op Msg_type Msg_text test.t1 check status OK +repair table t1; Table Op Msg_type Msg_text test.t1 repair status OK +drop table t1; +CREATE TABLE t1 ( +id int(11) NOT NULL auto_increment, +parent_id int(11) DEFAULT '0' NOT NULL, +level tinyint(4) DEFAULT '0' NOT NULL, +PRIMARY KEY (id), +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); +SELECT * FROM t1 WHERE level = 1 AND parent_id = 1; id parent_id level 3 1 1 4 1 1 @@ -34,6 +97,7 @@ id parent_id level 6 1 1 7 1 1 5 1 1 +SELECT * FROM t1 WHERE level = 1 AND parent_id = 1 order by id; id parent_id level 2 1 1 3 1 1 @@ -41,17 +105,38 @@ id parent_id level 5 1 1 6 1 1 7 1 1 +drop table t1; +create table t1( +Satellite varchar(25) not null, +SensorMode varchar(25) not null, +FullImageCornersUpperLeftLongitude double not null, +FullImageCornersUpperRightLongitude double not null, +FullImageCornersUpperRightLatitude double not null, +FullImageCornersLowerRightLatitude double not null, +index two (Satellite, SensorMode, FullImageCornersUpperLeftLongitude, FullImageCornersUpperRightLongitude, FullImageCornersUpperRightLatitude, FullImageCornersLowerRightLatitude)); +insert into t1 values("OV-3","PAN1",91,-92,40,50); +insert into t1 values("OV-4","PAN1",91,-92,40,50); +select * from t1 where t1.Satellite = "OV-3" and t1.SensorMode = "PAN1" and t1.FullImageCornersUpperLeftLongitude > -90.000000 and t1.FullImageCornersUpperRightLongitude < -82.000000; Satellite SensorMode FullImageCornersUpperLeftLongitude FullImageCornersUpperRightLongitude FullImageCornersUpperRightLatitude FullImageCornersLowerRightLatitude OV-3 PAN1 91 -92 40 50 +drop table t1; +create table t1 ( aString char(100) not null default "", key aString (aString(10)) ); +insert t1 (aString) values ( "believe in myself" ), ( "believe" ), ("baaa" ), ( "believe in love"); +select * from t1 where aString < "believe in myself" order by aString; aString baaa believe believe in love +select * from t1 where aString > "believe in love" order by aString; aString believe in myself +alter table t1 drop key aString; +select * from t1 where aString < "believe in myself" order by aString; aString baaa believe believe in love +select * from t1 where aString > "believe in love" order by aString; aString believe in myself +drop table t1; |