Alex Volkov created IGNITE-7360: ----------------------------------- Summary: SQL: incorrect date/time values could be inserted Key: IGNITE-7360 URL: https://issues.apache.org/jira/browse/IGNITE-7360 Project: Ignite Issue Type: Bug Components: sql Affects Versions: 2.1 Reporter: Alex Volkov Priority: Minor
According to ansi 99 spec.: ||Keyword||Valid values of datetime fields|| |YEAR|0001 to 9999| |MONTH|01 to 12| |DAY|Within the range 1 (one) to 31, but further constrained by the value of MONTH and YEAR fields, according to the rules for well-formed dates in the Gregorian calendar.| |HOUR|00 to 23| and so on. But these values could be inserted: *For DATE data type:* {code:java} 0: jdbc:ignite:thin://127.0.0.1:10800/> create table tmp_table_date_and_time (key_field INT PRIMARY KEY,field1 DATE,field2 TIME); SQL: create table tmp_table_date_and_time (key_field INT PRIMARY KEY,field1 DATE,field2 TIME) No rows affected (0.028 seconds) 0: jdbc:ignite:thin://127.0.0.1:10800/> insert into tmp_table_date_and_time (key_field,field1,field2) values (2, '10000-12-09','13:44:56'); SQL: insert into tmp_table_date_and_time (key_field,field1,field2) values (2, '10000-12-09','13:44:56') 1 row affected (0.009 seconds) 0: jdbc:ignite:thin://127.0.0.1:10800/> select * from tmp_table_date_and_time; SQL: select * from tmp_table_date_and_time IN IncrementalRows 3 +--------------------------------+--------------------------------+--------------------------------+ | KEY_FIELD | FIELD1 | FIELD2 | +--------------------------------+--------------------------------+--------------------------------+ | 2 | Sat Dec 09 00:00:00 MSK 10000 | 13:44:56 | +--------------------------------+--------------------------------+--------------------------------+ 1 row selected (0.007 seconds) 0: jdbc:ignite:thin://127.0.0.1:10800/> insert into tmp_table_date_and_time (key_field,field1,field2) values (6, '-2017-12-09','13:44:56'); SQL: insert into tmp_table_date_and_time (key_field,field1,field2) values (6, '-2017-12-09','13:44:56') 1 row affected (0.011 seconds) 0: jdbc:ignite:thin://127.0.0.1:10800/> select * from tmp_table_date_and_time; SQL: select * from tmp_table_date_and_time IN IncrementalRows 3 +--------------------------------+--------------------------------+--------------------------------+ | KEY_FIELD | FIELD1 | FIELD2 | +--------------------------------+--------------------------------+--------------------------------+ | 2 | Sat Dec 09 00:00:00 MSK 10000 | 13:44:56 | | 6 | Tue Dec 09 00:00:00 MSK 2018 | 13:44:56 | +--------------------------------+--------------------------------+--------------------------------+ 2 rows selected (0.016 seconds) 0: jdbc:ignite:thin://127.0.0.1:10800/> {code} For TIME data type: {code:java} 0: jdbc:ignite:thin://127.0.0.1:10800/> create table tmp_table_date_and_time (key_field INT PRIMARY KEY,field1 DATE,field2 TIME); SQL: create table tmp_table_date_and_time (key_field INT PRIMARY KEY,field1 DATE,field2 TIME) No rows affected (0.131 seconds) 0: jdbc:ignite:thin://127.0.0.1:10800/> insert into tmp_table_date_and_time (key_field,field1,field2) values (3, '2017-12-09','25:44:56'); SQL: insert into tmp_table_date_and_time (key_field,field1,field2) values (3, '2017-12-09','25:44:56') 1 row affected (0.097 seconds) 0: jdbc:ignite:thin://127.0.0.1:10800/> select * from tmp_table_date_and_time; SQL: select * from tmp_table_date_and_time IN IncrementalRows 3 +--------------------------------+--------------------------------+--------------------------------+ | KEY_FIELD | FIELD1 | FIELD2 | +--------------------------------+--------------------------------+--------------------------------+ | 3 | Sat Dec 09 00:00:00 MSK 2017 | 01:44:56 | +--------------------------------+--------------------------------+--------------------------------+ 1 row selected (0.017 seconds) 0: jdbc:ignite:thin://127.0.0.1:10800/> {code} -- This message was sent by Atlassian JIRA (v6.4.14#64029)