Skip to content

Commit 9c3e90a

Browse files
mithuncymithuncy
committed
Bug #17080703: INCONSISTENT DATETIME CONVERSIONS WITH
 FRACTIONAL SECONDS ISSUE : ------- A temporal literal string without delimiters and having > 14 digits was validated as a TIMESTAMP/DATETIME value with two digit precision fractional seconds. For example, a 16 digit string 'xxxxxxxxxxxxxxxx' is interpreted as 'XXXX-XX-XX XX:XX:XX.xx'. But fractional seconds should always be separated from other parts of time. And, decimal point is the only delimiter which separates the fractional seconds from rest. SOLUTION : ---------- Reject all time typed strings where the fractional seconds are not separated by a decimal point.
1 parent e2a988c commit 9c3e90a

3 files changed

Lines changed: 413 additions & 1 deletion

File tree

‎mysql-test/r/type_temporal_fractional.result‎

Lines changed: 255 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17271,4 +17271,259 @@ SECOND(-4.99999999991e0)
1727117271
SELECT SECOND(TRUNCATE('5',180));
1727217272
SECOND(TRUNCATE('5',180))
1727317273
5
17274+
#
17275+
# Bug#17080703 INCONSISTENT DATETIME CONVERSIONS WITH FRACTIONAL SECONDS
17276+
#
17277+
# Test for TIMESTAMP datatype.
17278+
SELECT TIMESTAMP '20130710010203';
17279+
TIMESTAMP '20130710010203'
17280+
2013-07-10 01:02:03
17281+
SELECT TIMESTAMP '201307100102031';
17282+
ERROR HY000: Incorrect DATETIME value: '201307100102031'
17283+
SELECT TIMESTAMP '2013071001020312';
17284+
ERROR HY000: Incorrect DATETIME value: '2013071001020312'
17285+
SELECT TIMESTAMP '20130710010203123';
17286+
ERROR HY000: Incorrect DATETIME value: '20130710010203123'
17287+
SELECT TIMESTAMP '201307100102031234';
17288+
ERROR HY000: Incorrect DATETIME value: '201307100102031234'
17289+
SELECT TIMESTAMP '2013071001020312345';
17290+
ERROR HY000: Incorrect DATETIME value: '2013071001020312345'
17291+
SELECT TIMESTAMP '20130710010203123456';
17292+
ERROR HY000: Incorrect DATETIME value: '20130710010203123456'
17293+
# Test non decimal point as delimiter for fractional seconds.
17294+
SELECT TIMESTAMP '2013:07:10 01:02:03:04';
17295+
ERROR HY000: Incorrect DATETIME value: '2013:07:10 01:02:03:04'
17296+
# Test for invalid seconds >= 60 properly separated by decimal point with fractions.
17297+
SELECT TIMESTAMP '2013:07:10 01.02.131';
17298+
ERROR HY000: Incorrect DATETIME value: '2013:07:10 01.02.131'
17299+
SELECT TIMESTAMP '2013:07:10 01.02.131.2';
17300+
ERROR HY000: Incorrect DATETIME value: '2013:07:10 01.02.131.2'
17301+
SELECT TIMESTAMP '2013:07:10 01.02.0312.3';
17302+
ERROR HY000: Incorrect DATETIME value: '2013:07:10 01.02.0312.3'
17303+
SELECT TIMESTAMP '2013.07.10 01.02.131.23456';
17304+
ERROR HY000: Incorrect DATETIME value: '2013.07.10 01.02.131.23456'
17305+
SELECT TIMESTAMP '201307100102031.';
17306+
ERROR HY000: Incorrect DATETIME value: '201307100102031.'
17307+
SELECT TIMESTAMP '2012071001021.1';
17308+
ERROR HY000: Incorrect DATETIME value: '2012071001021.1'
17309+
# Test for valid seconds properly separated by decimal point with fractions.
17310+
SELECT TIMESTAMP '2013:07:10 01.02.0.31';
17311+
TIMESTAMP '2013:07:10 01.02.0.31'
17312+
2013-07-10 01:02:00.31
17313+
SELECT TIMESTAMP '20130710010203.';
17314+
TIMESTAMP '20130710010203.'
17315+
2013-07-10 01:02:03
17316+
# Test for valid seconds properly separated by decimal point with fractions.
17317+
SELECT TIMESTAMP '20130710010203.1';
17318+
TIMESTAMP '20130710010203.1'
17319+
2013-07-10 01:02:03.1
17320+
SELECT TIMESTAMP '20130710010203.12';
17321+
TIMESTAMP '20130710010203.12'
17322+
2013-07-10 01:02:03.12
17323+
SELECT TIMESTAMP '20130710010203.123';
17324+
TIMESTAMP '20130710010203.123'
17325+
2013-07-10 01:02:03.123
17326+
SELECT TIMESTAMP '20130710010203.1234';
17327+
TIMESTAMP '20130710010203.1234'
17328+
2013-07-10 01:02:03.1234
17329+
SELECT TIMESTAMP '20130710010203.12345';
17330+
TIMESTAMP '20130710010203.12345'
17331+
2013-07-10 01:02:03.12345
17332+
SELECT TIMESTAMP '20130710010203.123456';
17333+
TIMESTAMP '20130710010203.123456'
17334+
2013-07-10 01:02:03.123456
17335+
SELECT TIMESTAMP '20130710010203.1234567';
17336+
TIMESTAMP '20130710010203.1234567'
17337+
2013-07-10 01:02:03.123457
17338+
# Test for valid time literals in insert operation.
17339+
CREATE TABLE t(key1 INT UNIQUE, col TIMESTAMP(6));
17340+
INSERT INTO t VALUES (1, '20130710010203.1'), (2, '20130710010203.12'), (3, '20130710010203.123'), (4, '20130710010203.1234'),
17341+
(5,'20130710010203.12345'), (6, '20130710010203.123456'), (7, '201207100102');
17342+
SELECT * FROM t ORDER BY key1;
17343+
key1 col
17344+
1 2013-07-10 01:02:03.100000
17345+
2 2013-07-10 01:02:03.120000
17346+
3 2013-07-10 01:02:03.123000
17347+
4 2013-07-10 01:02:03.123400
17348+
5 2013-07-10 01:02:03.123450
17349+
6 2013-07-10 01:02:03.123456
17350+
7 2020-12-07 10:01:02.000000
17351+
DELETE FROM t;
17352+
# Test for invalid time literals in insert operation.
17353+
INSERT INTO t VALUES (1, '201307100102031'), (2, '2013071001020312'), (3, '20130710010203123'), (4, '201307100102031234'),
17354+
(5, '2013071001020312345'), (6, '20130710010203123456'), (7, '2013:07:10 01.02.031'), (8, '2013:07:10 01.02.031.2'),
17355+
(9, '2013:07:10 01.02.0312.3'), (10, '2012071001021.1'), (11, '2012071001021');
17356+
Warnings:
17357+
Warning 1265 Data truncated for column 'col' at row 1
17358+
Warning 1265 Data truncated for column 'col' at row 2
17359+
Warning 1265 Data truncated for column 'col' at row 3
17360+
Warning 1265 Data truncated for column 'col' at row 4
17361+
Warning 1265 Data truncated for column 'col' at row 5
17362+
Warning 1265 Data truncated for column 'col' at row 6
17363+
Warning 1265 Data truncated for column 'col' at row 9
17364+
Warning 1265 Data truncated for column 'col' at row 10
17365+
Warning 1265 Data truncated for column 'col' at row 11
17366+
SELECT * FROM t ORDER BY key1;
17367+
key1 col
17368+
1 2013-07-10 01:02:03.000000
17369+
2 2013-07-10 01:02:03.000000
17370+
3 2013-07-10 01:02:03.000000
17371+
4 2013-07-10 01:02:03.000000
17372+
5 2013-07-10 01:02:03.000000
17373+
6 2013-07-10 01:02:03.000000
17374+
7 2013-07-10 01:02:31.000000
17375+
8 2013-07-10 01:02:31.200000
17376+
9 0000-00-00 00:00:00.000000
17377+
10 2020-12-07 10:01:02.000000
17378+
11 2020-12-07 10:01:02.000000
17379+
DROP TABLE t;
17380+
# Test for DATETIME datatype.
17381+
# Test for valid time literals in insert operation.
17382+
CREATE TABLE t(key1 INT UNIQUE, col DATETIME(6));
17383+
INSERT INTO t VALUES (1, '20130710010203.1'), (2, '20130710010203.12'), (3, '20130710010203.123'), (4, '20130710010203.1234'),
17384+
(5, '20130710010203.12345'), (6, '20130710010203.123456'), (7, '201207100102');
17385+
SELECT * FROM t ORDER BY key1;
17386+
key1 col
17387+
1 2013-07-10 01:02:03.100000
17388+
2 2013-07-10 01:02:03.120000
17389+
3 2013-07-10 01:02:03.123000
17390+
4 2013-07-10 01:02:03.123400
17391+
5 2013-07-10 01:02:03.123450
17392+
6 2013-07-10 01:02:03.123456
17393+
7 2020-12-07 10:01:02.000000
17394+
DELETE FROM t;
17395+
# Test for invalid time literals in insert operation.
17396+
INSERT INTO t VALUES (1, '201307100102031'), (2, '2013071001020312'), (3, '20130710010203123'), (4, '201307100102031234'),
17397+
(5, '2013071001020312345'), (6, '20130710010203123456'), (7, '2013:07:10 01.02.031'), (8, '2013:07:10 01.02.031.2'),
17398+
(9, '2013:07:10 01.02.0312.3'), (10, '2012071001021.1'), (11, '2012071001021');
17399+
Warnings:
17400+
Warning 1265 Data truncated for column 'col' at row 1
17401+
Warning 1265 Data truncated for column 'col' at row 2
17402+
Warning 1265 Data truncated for column 'col' at row 3
17403+
Warning 1265 Data truncated for column 'col' at row 4
17404+
Warning 1265 Data truncated for column 'col' at row 5
17405+
Warning 1265 Data truncated for column 'col' at row 6
17406+
Warning 1265 Data truncated for column 'col' at row 9
17407+
Warning 1265 Data truncated for column 'col' at row 10
17408+
Warning 1265 Data truncated for column 'col' at row 11
17409+
SELECT * FROM t ORDER BY key1;
17410+
key1 col
17411+
1 2013-07-10 01:02:03.000000
17412+
2 2013-07-10 01:02:03.000000
17413+
3 2013-07-10 01:02:03.000000
17414+
4 2013-07-10 01:02:03.000000
17415+
5 2013-07-10 01:02:03.000000
17416+
6 2013-07-10 01:02:03.000000
17417+
7 2013-07-10 01:02:31.000000
17418+
8 2013-07-10 01:02:31.200000
17419+
9 0000-00-00 00:00:00.000000
17420+
10 2020-12-07 10:01:02.000000
17421+
11 2020-12-07 10:01:02.000000
17422+
DROP TABLE t;
17423+
# Test for TIME datatype.
17424+
SELECT TIME '010203';
17425+
TIME '010203'
17426+
01:02:03
17427+
SELECT TIME '1010203';
17428+
TIME '1010203'
17429+
101:02:03
17430+
SELECT TIME '01010203';
17431+
TIME '01010203'
17432+
101:02:03
17433+
SELECT TIME '10102031';
17434+
ERROR HY000: Incorrect TIME value: '10102031'
17435+
SELECT TIME '101020312';
17436+
ERROR HY000: Incorrect TIME value: '101020312'
17437+
SELECT TIME '1010203123';
17438+
ERROR HY000: Incorrect TIME value: '1010203123'
17439+
SELECT TIME '10102031234';
17440+
ERROR HY000: Incorrect TIME value: '10102031234'
17441+
SELECT TIME '101020312345';
17442+
ERROR HY000: Incorrect TIME value: '101020312345'
17443+
SELECT TIME '1010203123456';
17444+
ERROR HY000: Incorrect TIME value: '1010203123456'
17445+
# Test non decimal point as delimiter for fractional seconds.
17446+
SELECT TIME '01:02:03:04';
17447+
ERROR HY000: Incorrect TIME value: '01:02:03:04'
17448+
# Test for invalid seconds >= 60 properly separated by decimal point with fractions.
17449+
SELECT TIME '01:02:131';
17450+
ERROR HY000: Incorrect TIME value: '01:02:131'
17451+
SELECT TIME '01:02:131.2';
17452+
ERROR HY000: Incorrect TIME value: '01:02:131.2'
17453+
SELECT TIME '01:02:0312.3';
17454+
ERROR HY000: Incorrect TIME value: '01:02:0312.3'
17455+
SELECT TIME '01:02:131.23456';
17456+
ERROR HY000: Incorrect TIME value: '01:02:131.23456'
17457+
SELECT TIME '11102031.';
17458+
ERROR HY000: Incorrect TIME value: '11102031.'
17459+
# Test for valid seconds properly separated by decimal point with fractions.
17460+
SELECT TIME '01:02:0.31';
17461+
TIME '01:02:0.31'
17462+
01:02:00.31
17463+
SELECT TIME '010203.';
17464+
TIME '010203.'
17465+
01:02:03
17466+
# Test for valid seconds properly separated by decimal point with fractions.
17467+
SELECT TIME '110203.1';
17468+
TIME '110203.1'
17469+
11:02:03.1
17470+
SELECT TIME '010203.12';
17471+
TIME '010203.12'
17472+
01:02:03.12
17473+
SELECT TIME '010203.123';
17474+
TIME '010203.123'
17475+
01:02:03.123
17476+
SELECT TIME '010203.1234';
17477+
TIME '010203.1234'
17478+
01:02:03.1234
17479+
SELECT TIME '010203.12345';
17480+
TIME '010203.12345'
17481+
01:02:03.12345
17482+
SELECT TIME '010203.123456';
17483+
TIME '010203.123456'
17484+
01:02:03.123456
17485+
SELECT TIME '010203.1234567';
17486+
TIME '010203.1234567'
17487+
01:02:03.123457
17488+
SELECT TIME '01021.1';
17489+
TIME '01021.1'
17490+
00:10:21.1
17491+
# Test for valid time literals in insert operation.
17492+
CREATE TABLE t(key1 INT UNIQUE, col TIME(6));
17493+
INSERT INTO t VALUES (1, '010203.1'), (2, '010203.12'), (3, '010203.123'), (4, '010203.1234'),
17494+
(5, '010203.12345'), (6, '010203.123456'), (7, '0203.123456'), (8, '03.123456'), (9, '0.123456');
17495+
SELECT * FROM t ORDER BY key1;
17496+
key1 col
17497+
1 01:02:03.100000
17498+
2 01:02:03.120000
17499+
3 01:02:03.123000
17500+
4 01:02:03.123400
17501+
5 01:02:03.123450
17502+
6 01:02:03.123456
17503+
7 00:02:03.123456
17504+
8 00:00:03.123456
17505+
9 00:00:00.123456
17506+
DELETE FROM t;
17507+
# Test for invalid time literals in insert operation.
17508+
INSERT INTO t VALUES (1, '0102031'), (2, '01020312'), (3, '010203123'), (4, '0102031234'),
17509+
(5, '01020312345'), (6, '010203123456'), (7, '01:02:031'), (8, '01:02:031.2'),
17510+
(9, '01:02:0312.3');
17511+
Warnings:
17512+
Warning 1264 Out of range value for column 'col' at row 3
17513+
Warning 1264 Out of range value for column 'col' at row 4
17514+
Warning 1264 Out of range value for column 'col' at row 5
17515+
Note 1265 Data truncated for column 'col' at row 6
17516+
Warning 1264 Out of range value for column 'col' at row 9
17517+
SELECT * FROM t ORDER BY key1;
17518+
key1 col
17519+
1 10:20:31.000000
17520+
2 102:03:12.000000
17521+
3 838:59:59.000000
17522+
4 838:59:59.000000
17523+
5 838:59:59.000000
17524+
6 12:34:56.000000
17525+
7 01:02:31.000000
17526+
8 01:02:31.200000
17527+
9 00:00:00.000000
17528+
DROP TABLE t;
1727417529
# End of 5.6 tests

0 commit comments

Comments
 (0)