ignite-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Alex Volkov (JIRA)" <j...@apache.org>
Subject [jira] [Created] (IGNITE-7479) NOT NULL constraints for complex PK
Date Fri, 19 Jan 2018 14:53:00 GMT
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)

Mime
View raw message