mirror of
https://github.com/MariaDB/server.git
synced 2025-01-15 19:42:28 +01:00
64 lines
2.6 KiB
SQL
64 lines
2.6 KiB
SQL
|
|
# Trailing garbage in string literals
|
|
--eval CREATE TABLE t1 (a $TYPE);
|
|
INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01');
|
|
SELECT * FROM t1 WHERE a='2001-01-01 00:00:00x';
|
|
SELECT * FROM t1 WHERE LENGTH(a) != 20;
|
|
SELECT * FROM t1 WHERE LENGTH(a) != 20 AND a='2001-01-01 00:00:00x';
|
|
EXPLAIN EXTENDED
|
|
SELECT * FROM t1 WHERE LENGTH(a) != 20 AND a='2001-01-01 00:00:00x';
|
|
EXPLAIN EXTENDED
|
|
SELECT * FROM t1 WHERE LENGTH(a)!=30+RAND() AND a='2001-01-01 00:00:00x';
|
|
DROP TABLE t1;
|
|
|
|
# Leading spaces in string literals
|
|
--eval CREATE TABLE t1 (a $TYPE);
|
|
INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01');
|
|
SELECT * FROM t1 WHERE LENGTH(a)=19;
|
|
SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=' 2001-01-01 00:00:00';
|
|
EXPLAIN EXTENDED
|
|
SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=' 2001-01-01 00:00:00';
|
|
EXPLAIN EXTENDED
|
|
SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a=' 2001-01-01 00:00:00';
|
|
# This should not propagate
|
|
EXPLAIN EXTENDED
|
|
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=' garbage ';
|
|
DROP TABLE t1;
|
|
|
|
# Trailing fractional digits in temporal literals
|
|
--eval CREATE TABLE t1 (a $TYPE);
|
|
INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01');
|
|
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 00:00:00.000000';
|
|
SELECT * FROM t1 WHERE LENGTH(a)=19;
|
|
SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIMESTAMP'2001-01-01 00:00:00.000000';
|
|
EXPLAIN EXTENDED
|
|
SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIMESTAMP'2001-01-01 00:00:00.000000';
|
|
EXPLAIN EXTENDED
|
|
SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=TIMESTAMP'2001-01-01 00:00:00.000000';
|
|
DROP TABLE t1;
|
|
|
|
# Trailing fractional digits in temporal literals, the same precision
|
|
--eval CREATE TABLE t1 (a $TYPE(6));
|
|
INSERT INTO t1 VALUES ('2001-01-01 00:00:00.000000'),('2001-01-01 00:00:01.000000');
|
|
SELECT * FROM t1 WHERE a=TIMESTAMP'2001-01-01 00:00:00.000000';
|
|
SELECT * FROM t1 WHERE LENGTH(a)=26;
|
|
SELECT * FROM t1 WHERE LENGTH(a)=26 AND a=TIMESTAMP'2001-01-01 00:00:00.000000';
|
|
EXPLAIN EXTENDED
|
|
SELECT * FROM t1 WHERE LENGTH(a)=26 AND a=TIMESTAMP'2001-01-01 00:00:00.000000';
|
|
EXPLAIN EXTENDED
|
|
SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIMESTAMP'2001-01-01 00:00:00.000000';
|
|
DROP TABLE t1;
|
|
|
|
# DATETIME/TIMESTAMP column vs TIME literal
|
|
SET timestamp=UNIX_TIMESTAMP('2001-01-01 10:20:30');
|
|
--eval CREATE TABLE t1 (a $TYPE);
|
|
INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01');
|
|
SELECT * FROM t1 WHERE a=TIME'00:00:00';
|
|
SELECT * FROM t1 WHERE LENGTH(a)=19;
|
|
SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIME'00:00:00';
|
|
EXPLAIN EXTENDED
|
|
SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIME'00:00:00';
|
|
EXPLAIN EXTENDED
|
|
SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIME'00:00:00';
|
|
DROP TABLE t1;
|
|
|