Bug#45983 ibmdb2i_create_index_option=1 not working for primary key

With ibmdb2i_create_index_option set to 1, creating an IBMDB2I table
with a primary key should produce an additional index that uses EBCDIC
hexadecimal sorting. However, this does not work. Adding indexes that
are not primary keys does work. The ibmdb2i_create_index_option should
be honoured when creating a table with a primary key.

This patch adds code to the create() function to check for the value
of the ibmdb2i_create_index_option variable and, when appropriate, to 
generate a *HEX-based shadow index in DB2 for the primary key. Previously 
this behavior was limited to secondary indexes.

Additionally, this patch restricts the creation of shadow indexes to
cases in which a non-*HEX sort sequence is used, as the documentation
for ibmdb2i_create_index_option describes. Previously, the shadow index
would in some cases be created even when the MySQL-specific index used
*HEX sorting, leading to redundant indexes.

Finally, the code used to generate the list of fields for indexes 
and the code used to generate the SQL statement for the shadow
indexes has been refactored into individual functions.

mysql-test/suite/ibmdb2i/r/ibmdb2i_bug_45983.result:
  Bug#45983 ibmdb2i_create_index_option=1 not working for primary key
  
  Result file for the test case.
mysql-test/suite/ibmdb2i/t/ibmdb2i_bug_45983.test:
  Bug#45983 ibmdb2i_create_index_option=1 not working for primary key
  
  Add tests to verify that the ibmdb2i_create_index_option is being honoured
  when creating a table with a primary key.
storage/ibmdb2i/ha_ibmdb2i.cc:
  Bug#45983 ibmdb2i_create_index_option=1 not working for primary key
  
  - Add code to the create() function to check for the value of the
    ibmdb2i_create_index_option variable and, when appropriate, to 
    generate a *HEX-based shadow index in DB2 for the primary key.
  
  - Restrict the creation of shadow indexes to cases in which a
    non-*HEX sort sequence is used.
  
  - Refractor code used to generate the list of fields for indexes
    and the code used to generate the SQL statement for the shadow
    indexes into individual functions.
storage/ibmdb2i/ha_ibmdb2i.h:
  Bug#45983 ibmdb2i_create_index_option=1 not working for primary key
  
  Add function prototypes for the functions that.
  
  - Generate the list of fields for indexes
  - Generate the SQL statement for the shadow
    indexes

2 files changed, 67 insertions, 0 deletions

diff --git a/mysql-test/suite/ibmdb2i/r/ibmdb2i_bug_45983.result b/mysql-test/suite/ibmdb2i/r/ibmdb2i_bug_45983.result
new file mode 100644
index 00000000000..b9f4dcfc656
--- /dev/null
+++ b/mysql-test/suite/ibmdb2i/r/ibmdb2i_bug_45983.result
@@ -0,0 +1,20 @@
+set ibmdb2i_create_index_option=1;
+drop schema if exists test1;
+create schema test1;
+use test1;
+CREATE TABLE t1 (f int primary key, index(f)) engine=ibmdb2i;
+drop table t1;
+CREATE TABLE t1 (f char(10) collate utf8_bin primary key, index(f)) engine=ibmdb2i;
+drop table t1;
+CREATE TABLE t1 (f char(10) collate latin1_swedish_ci primary key, index(f)) engine=ibmdb2i;
+drop table t1;
+CREATE TABLE t1 (f char(10) collate latin1_swedish_ci primary key, i int, index i(i,f)) engine=ibmdb2i;
+drop table t1;
+create table fd (SQSSEQ CHAR(10)) engine=ibmdb2i;
+select * from fd;
+SQSSEQ
+*HEX
+*HEX
+*HEX
+*HEX
+drop table fd;
diff --git a/mysql-test/suite/ibmdb2i/t/ibmdb2i_bug_45983.test b/mysql-test/suite/ibmdb2i/t/ibmdb2i_bug_45983.test
new file mode 100644
index 00000000000..695d8e90ada
--- /dev/null
+++ b/mysql-test/suite/ibmdb2i/t/ibmdb2i_bug_45983.test
@@ -0,0 +1,47 @@
+source suite/ibmdb2i/include/have_ibmdb2i.inc;
+
+# Confirm that ibmdb2i_create_index_option causes additional *HEX sorted indexes to be created for all non-binary keys.
+
+set ibmdb2i_create_index_option=1;
+--disable_warnings
+drop schema if exists test1;
+create schema test1;
+use test1;
+--enable_warnings
+
+--disable_abort_on_error
+--error 0,255
+exec system "DLTF QGPL/FDOUT" > /dev/null;
+--enable_abort_on_error
+
+#No additional index because no string fields in key
+CREATE TABLE t1 (f int primary key, index(f)) engine=ibmdb2i;
+--error 255
+exec system "DSPFD FILE(\"test1\"/PRIM0001) TYPE(*SEQ) OUTPUT(*OUTFILE) OUTFILE(QGPL/FDOUT) OUTMBR(*FIRST *ADD)" > /dev/null;
+--error 255
+exec system "DSPFD FILE(\"test1\"/\"f___H_t1\") TYPE(*SEQ) OUTPUT(*OUTFILE) OUTFILE(QGPL/FDOUT) OUTMBR(*FIRST *ADD)" > /dev/null;
+drop table t1;
+
+#No additional index because binary sorting
+CREATE TABLE t1 (f char(10) collate utf8_bin primary key, index(f)) engine=ibmdb2i;
+--error 255
+exec system "DSPFD FILE(\"test1\"/PRIM0001) TYPE(*SEQ) OUTPUT(*OUTFILE) OUTFILE(QGPL/FDOUT) OUTMBR(*FIRST *ADD)" > /dev/null;
+--error 255
+exec system "DSPFD FILE(\"test1\"/\"f___H_t1\") TYPE(*SEQ) OUTPUT(*OUTFILE) OUTFILE(QGPL/FDOUT) OUTMBR(*FIRST *ADD)" > /dev/null;
+drop table t1;
+
+CREATE TABLE t1 (f char(10) collate latin1_swedish_ci primary key, index(f)) engine=ibmdb2i;
+exec system "DSPFD FILE(\"test1\"/PRIM0001) TYPE(*SEQ) OUTPUT(*OUTFILE) OUTFILE(QGPL/FDOUT) OUTMBR(*FIRST *ADD)" > /dev/null;
+exec system "DSPFD FILE(\"test1\"/\"f___H_t1\") TYPE(*SEQ) OUTPUT(*OUTFILE) OUTFILE(QGPL/FDOUT) OUTMBR(*FIRST *ADD)" > /dev/null;
+drop table t1;
+
+CREATE TABLE t1 (f char(10) collate latin1_swedish_ci primary key, i int, index i(i,f)) engine=ibmdb2i;
+exec system "DSPFD FILE(\"test1\"/PRIM0001) TYPE(*SEQ) OUTPUT(*OUTFILE) OUTFILE(QGPL/FDOUT) OUTMBR(*FIRST *ADD)" > /dev/null;
+exec system "DSPFD FILE(\"test1\"/\"i___H_t1\") TYPE(*SEQ) OUTPUT(*OUTFILE) OUTFILE(QGPL/FDOUT) OUTMBR(*FIRST *ADD)" > /dev/null;
+drop table t1;
+
+
+create table fd (SQSSEQ CHAR(10)) engine=ibmdb2i;
+system system "CPYF FROMFILE(QGPL/FDOUT) TOFILE(\"test1\"/\"fd\") mbropt(*replace) fmtopt(*drop *map)" > /dev/null;
+select * from fd;
+drop table fd;