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)
