Alex Volkov created IGNITE-7479:
-----------------------------------
Summary: NOT NULL constraints for complex PK
Key: IGNITE-7479
URL: https://issues.apache.org/jira/browse/IGNITE-7479
Project: Ignite
Issue Type: Bug
Components: sql
Reporter: Alex Volkov
According to SQL ANSI 99 spec (11.7 <unique constraint definition> point 5)
If the < unique specification > specifies PRIMARY KEY, then for each < column
name > in the explicit or implicit < unique column list > for which NOT NULL is
not specified, NOT NULL is implicit in the < column definition >.
But now we have violation:
{code:java}
0: jdbc:ignite:thin://127.0.0.1:10800/> create table
tmp_table_age_name_wage_complex_pk (key_field INT,AGE INT,field1 VARCHAR,field2
INT,PRIMARY KEY (key_field, AGE, field1));
SQL: create table tmp_table_age_name_wage_complex_pk (key_field INT,AGE
INT,field1 VARCHAR,field2 INT,PRIMARY KEY (key_field, AGE, field1))
No rows affected (0.129 seconds)
0: jdbc:ignite:thin://127.0.0.1:10800/> insert into
tmp_table_age_name_wage_complex_pk (key_field,AGE,field1,field2) values (11,
null,'John',30000);
SQL: insert into tmp_table_age_name_wage_complex_pk
(key_field,AGE,field1,field2) values (11, null,'John',30000)
1 row affected (0.121 seconds)
0: jdbc:ignite:thin://127.0.0.1:10800/> select * from
tmp_table_age_name_wage_complex_pk;
SQL: select * from tmp_table_age_name_wage_complex_pk
IN IncrementalRows 4
+--------------------------------+--------------------------------+--------------------------------+--------------------------------+
| KEY_FIELD | AGE |
FIELD1 | FIELD2 |
+--------------------------------+--------------------------------+--------------------------------+--------------------------------+
| 11 | null | John
| 30000 |
+--------------------------------+--------------------------------+--------------------------------+--------------------------------+
1 row selected (0.04 seconds)
0: jdbc:ignite:thin://127.0.0.1:10800/>
{code}
The same SQL script with H2 BD got:
{code:java}
create table tmp_table_age_name_wage_complex_pk (key_field INT,AGE INT,field1
VARCHAR,field2 INT,PRIMARY KEY (key_field, AGE, field1));
Update count: 0
(1 ms)
insert into tmp_table_age_name_wage_complex_pk (key_field,AGE,field1,field2)
values (11, null,'John',30000);
NULL not allowed for column "AGE"; SQL statement:
insert into tmp_table_age_name_wage_complex_pk (key_field,AGE,field1,field2)
values (11, null,'John',30000) [23502-195] 23502/23502
{code}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
