support NULL fields in keybb-10.5-MDEV-16978-without-overlaps

4 files changed, 52 insertions, 29 deletions

diff --git a/mysql-test/suite/period/r/overlaps.result b/mysql-test/suite/period/r/overlaps.result
index cf980afd7f0..e52b21496b5 100644
--- a/mysql-test/suite/period/r/overlaps.result
+++ b/mysql-test/suite/period/r/overlaps.result
@@ -104,16 +104,30 @@ show create table t;
 Table	Create Table
 t	CREATE TABLE `t` (
   `id` int(11) NOT NULL,
-  `u` int(11) NOT NULL,
+  `u` int(11) DEFAULT NULL,
   `s` date NOT NULL,
   `e` date NOT NULL,
   PERIOD FOR `p` (`s`, `e`),
   PRIMARY KEY (`id`,`p` WITHOUT OVERLAPS),
   UNIQUE KEY `u` (`u`,`p` WITHOUT OVERLAPS)
 ) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1
+insert into t values (2, NULL, '2003-03-01', '2003-05-01');
+insert into t values (2, NULL, '2003-03-01', '2003-05-01');
+ERROR 23000: Duplicate entry '2-2003-05-01-2003-03-01' for key 'PRIMARY'
+insert into t values (3, NULL, '2003-03-01', '2003-05-01');
 insert into t values (1, 1, '2003-03-01', '2003-05-01');
 insert into t values (1, 2, '2003-05-01', '2003-07-01');
-insert into t values (2, 1, '2003-05-01', '2003-07-01');
+insert into t values (4, NULL, '2003-03-01', '2003-05-01');
+create sequence seq start=5;
+update t set id= nextval(seq), u= nextval(seq), s='2003-05-01', e='2003-07-01'
+         where u is NULL;
+select * from t;
+id	u	s	e
+1	1	2003-03-01	2003-05-01
+1	2	2003-05-01	2003-07-01
+5	6	2003-05-01	2003-07-01
+7	8	2003-05-01	2003-07-01
+9	10	2003-05-01	2003-07-01
 create or replace table t(id int, s date, e date,
 period for p(s,e));
 insert into t values (1, '2003-01-01', '2003-03-01'),
diff --git a/mysql-test/suite/period/t/overlaps.test b/mysql-test/suite/period/t/overlaps.test
index d89b8bb2b87..e78510711a7 100644
--- a/mysql-test/suite/period/t/overlaps.test
+++ b/mysql-test/suite/period/t/overlaps.test
@@ -98,15 +98,29 @@ insert into t values (1, 2, '2003-05-01', '2003-07-01');
 --error ER_DUP_ENTRY
 insert into t values (1, 3, '2003-04-01', '2003-05-01');
 
+
+
 create or replace table t(id int, u int, s date, e date,
                           period for p(s,e),
                           primary key(id, p without overlaps),
                           unique(u, p without overlaps));
 --replace_result $default_engine DEFAULT_ENGINE
 show create table t;
+
+insert into t values (2, NULL, '2003-03-01', '2003-05-01');
+--error ER_DUP_ENTRY
+insert into t values (2, NULL, '2003-03-01', '2003-05-01');
+insert into t values (3, NULL, '2003-03-01', '2003-05-01');
 insert into t values (1, 1, '2003-03-01', '2003-05-01');
 insert into t values (1, 2, '2003-05-01', '2003-07-01');
-insert into t values (2, 1, '2003-05-01', '2003-07-01');
+insert into t values (4, NULL, '2003-03-01', '2003-05-01');
+
+create sequence seq start=5;
+update t set id= nextval(seq), u= nextval(seq), s='2003-05-01', e='2003-07-01'
+         where u is NULL;
+
+--sorted_result
+select * from t;
 
 create or replace table t(id int, s date, e date,
                           period for p(s,e));
diff --git a/sql/handler.cc b/sql/handler.cc
index 917386f4392..16f57533f27 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -6983,8 +6983,10 @@ int handler::ha_check_overlaps(const uchar *old_data, const uchar* new_data)
   if (!check_overlaps_buffer)
     check_overlaps_buffer= (uchar*)alloc_root(&table_share->mem_root,
                                               table_share->max_unique_length
+                                              + table_share->null_fields
                                               + table_share->reclength);
-  auto *record_buffer= check_overlaps_buffer + table_share->max_unique_length;
+  auto *record_buffer= check_overlaps_buffer + table_share->max_unique_length
+                                             + table_share->null_fields;
   auto *handler= this;
   // handler->inited can be NONE on INSERT
   if (handler->inited != NONE)
@@ -7015,7 +7017,8 @@ int handler::ha_check_overlaps(const uchar *old_data, const uchar* new_data)
       bool key_used= false;
       for (uint k= 0; k < key_parts && !key_used; k++)
         key_used= bitmap_is_set(table->write_set,
-                                key_info.key_part[k].fieldnr - 1);
+                                key_info.key_part[k].fieldnr - 1)
+                  && !key_info.key_part[k].field->is_null_in_record(new_data);
       if (!key_used)
         continue;
     }
@@ -7064,8 +7067,15 @@ int handler::ha_check_overlaps(const uchar *old_data, const uchar* new_data)
         error= handler->ha_index_next(record_buffer);
     }
 
-    if (!error && table->check_period_overlaps(key_info, key_info,
-                                               new_data, record_buffer) == 0)
+    bool null_in_key= false;
+    for (uint k= 0; k < key_parts && !null_in_key; k++)
+    {
+      null_in_key= key_info.key_part[k].field->is_null_in_record(record_buffer);
+    }
+
+    if (!null_in_key && !error
+        && table->check_period_overlaps(key_info, key_info,
+                                        new_data, record_buffer) == 0)
       error= HA_ERR_FOUND_DUPP_KEY;
 
     if (error == HA_ERR_KEY_NOT_FOUND || error == HA_ERR_END_OF_FILE)
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 74d28ede25f..0f6a750c7f5 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3959,28 +3959,6 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
         DBUG_RETURN(TRUE);
       }
 
-      switch (key->type) {
-        case Key::UNIQUE:
-          if (!key->period)
-            break;
-          /* Fall through:
-             WITHOUT OVERLAPS forces fields to be NOT NULL
-           */
-        case Key::PRIMARY:
-          /* Implicitly set primary key fields to NOT NULL for ISO conf. */
-          if (!(sql_field->flags & NOT_NULL_FLAG))
-          {
-            /* Implicitly set primary key fields to NOT NULL for ISO conf. */
-            sql_field->flags|= NOT_NULL_FLAG;
-            sql_field->pack_flag&= ~FIELDFLAG_MAYBE_NULL;
-            null_fields--;
-          }
-          break;
-        default:
-          // Fall through
-          break;
-      }
-
       cols2.rewind();
       switch(key->type) {
 
@@ -4018,6 +3996,13 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
 	                                                          *sql_field,
 	                                                          file))
           DBUG_RETURN(TRUE);
+        if (!(sql_field->flags & NOT_NULL_FLAG))
+        {
+          /* Implicitly set primary key fields to NOT NULL for ISO conf. */
+          sql_field->flags|= NOT_NULL_FLAG;
+          sql_field->pack_flag&= ~FIELDFLAG_MAYBE_NULL;
+          null_fields--;
+        }
         break;
 
       case Key::MULTIPLE: