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)