531
# Bug #55779: select does not work properly in mysql server
532
# Version "5.1.42 SUSE MySQL RPM"
534
CREATE TABLE t1 (a TIMESTAMP, KEY (a));
535
INSERT INTO t1 VALUES ('2000-01-01 00:00:00'), ('2000-01-01 00:00:00'),
536
('2000-01-01 00:00:01'), ('2000-01-01 00:00:01');
537
SELECT a FROM t1 WHERE a >= 20000101000000;
543
SELECT a FROM t1 WHERE a >= '20000101000000';
551
# Bug#50774: failed to get the correct resultset when timestamp values
552
# are appended with .0
554
CREATE TABLE t1 ( a TIMESTAMP, KEY ( a ) );
555
INSERT INTO t1 VALUES( '2010-02-01 09:31:01' );
556
INSERT INTO t1 VALUES( '2010-02-01 09:31:02' );
557
INSERT INTO t1 VALUES( '2010-02-01 09:31:03' );
558
INSERT INTO t1 VALUES( '2010-02-01 09:31:04' );
559
SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
564
SELECT * FROM t1 WHERE '2010-02-01 09:31:02.0' <= a;
569
SELECT * FROM t1 WHERE a <= '2010-02-01 09:31:02.0';
573
SELECT * FROM t1 WHERE '2010-02-01 09:31:02.0' >= a;
578
SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
579
id select_type table type possible_keys key key_len ref rows Extra
580
x x x range x x x x x x
581
SELECT * FROM t1 WHERE a >= '2010-02-01 09:31:02.0';
586
CREATE TABLE t2 ( a TIMESTAMP, KEY ( a DESC ) );
587
INSERT INTO t2 VALUES( '2010-02-01 09:31:01' );
588
INSERT INTO t2 VALUES( '2010-02-01 09:31:02' );
589
INSERT INTO t2 VALUES( '2010-02-01 09:31:03' );
590
INSERT INTO t2 VALUES( '2010-02-01 09:31:04' );
591
INSERT INTO t2 VALUES( '2010-02-01 09:31:05' );
592
INSERT INTO t2 VALUES( '2010-02-01 09:31:06' );
593
INSERT INTO t2 VALUES( '2010-02-01 09:31:07' );
594
INSERT INTO t2 VALUES( '2010-02-01 09:31:08' );
595
INSERT INTO t2 VALUES( '2010-02-01 09:31:09' );
596
INSERT INTO t2 VALUES( '2010-02-01 09:31:10' );
597
INSERT INTO t2 VALUES( '2010-02-01 09:31:11' );
598
# The bug would cause the range optimizer's comparison to use an open
599
# interval here. This reveals itself only in the number of reads
603
SELECT * FROM t2 WHERE a < '2010-02-01 09:31:02.0';
604
id select_type table type possible_keys key key_len ref rows Extra
605
x x x range x x x x x x
606
SELECT * FROM t2 WHERE a < '2010-02-01 09:31:02.0';
609
SHOW STATUS LIKE 'Handler_read_next';