Uploaded image for project: 'Ignite'
  1. Ignite
  2. IGNITE-7479

NOT NULL constraints for complex PK

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 2.2
    • Fix Version/s: None
    • Component/s: sql
    • Labels:
      None

      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
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              avolkov Alex Volkov
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: