You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Chenjian (Jira)" <ji...@apache.org> on 2023/02/21 01:40:00 UTC
[jira] [Commented] (IGNITE-7360) SQL: incorrect date/time values could be inserted
[ https://issues.apache.org/jira/browse/IGNITE-7360?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17691329#comment-17691329 ]
Chenjian commented on IGNITE-7360:
----------------------------------
[~avolkov] Just checked, the newer version still has this problem.
Anyone is going to fix this?
If no, can I take this, and could anyone help to point out which test is used for test insert date type in existing?
> 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
(v8.20.10#820010)