[IGNITE-7479] NOT NULL constraints for complex PK

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 2.2
• Component/s: sql
• Assignee: Unassigned
• Reporter: Alex Volkov
• Created: 19/Jan/18 14:52
• Updated: 19/Jan/18 14:52

Description

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:

 

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/> 

The same SQL script with H2 BD got:

 

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