MDEV-16871 in_predicate_conversion_threshold cannot be set in my.cnf

10 files changed, 35 insertions, 29 deletions

diff --git a/mysql-test/main/mysqld--help.result b/mysql-test/main/mysqld--help.result
index 4f99ccc9372..2a1455b05b0 100644
--- a/mysql-test/main/mysqld--help.result
+++ b/mysql-test/main/mysqld--help.result
@@ -346,6 +346,10 @@ The following specify which files/extra groups are read (specified before remain
  Specifies a directory to add to the ignore list when
  collecting database names from the datadir. Put a blank
  argument to reset the list accumulated so far.
+ --in-predicate-conversion-threshold=# 
+ The minimum number of scalar elements in the value list
+ of IN predicate that triggers its conversion to IN
+ subquery. Set to 0 to disable the conversion.
  --init-connect=name Command(s) that are executed for each new connection
  (unless the user has SUPER privilege)
  --init-file=name    Read SQL commands from this file at startup
@@ -1442,6 +1446,7 @@ idle-transaction-timeout 0
 idle-write-transaction-timeout 0
 ignore-builtin-innodb FALSE
 ignore-db-dirs 
+in-predicate-conversion-threshold 1000
 init-connect 
 init-file (No default value)
 init-rpl-role MASTER
diff --git a/mysql-test/main/mysqld--help.test b/mysql-test/main/mysqld--help.test
index 0225ef7447e..6e5ad058330 100644
--- a/mysql-test/main/mysqld--help.test
+++ b/mysql-test/main/mysqld--help.test
@@ -30,7 +30,7 @@ perl;
               feedback debug temp-pool ssl des-key-file xtradb sequence
               thread-concurrency super-large-pages mutex-deadlock-detector
               connect null-audit aria oqgraph sphinx thread-handling
-              test-sql-discovery query-cache-info in-predicate-conversion-threshold
+              test-sql-discovery query-cache-info
               query-response-time metadata-lock-info locales unix-socket
               wsrep file-key-management cracklib-password-check user-variables/;
 
diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test
index 911dc997892..7319dbdc9e8 100644
--- a/mysql-test/main/opt_tvc.test
+++ b/mysql-test/main/opt_tvc.test
@@ -2,7 +2,7 @@
 # MDEV-12176 Transform [NOT] IN predicate with long list of values INTO [NOT] IN subquery
 #
 source include/have_debug.inc;
---source include/default_optimizer_switch.inc
+source include/default_optimizer_switch.inc;
 
 create table t1 (a int, b int);
 
diff --git a/mysql-test/suite/sys_vars/inc/sysvars_server.inc b/mysql-test/suite/sys_vars/inc/sysvars_server.inc
index 8e2df93dcdd..36b41cbdc09 100644
--- a/mysql-test/suite/sys_vars/inc/sysvars_server.inc
+++ b/mysql-test/suite/sys_vars/inc/sysvars_server.inc
@@ -23,8 +23,7 @@ select VARIABLE_NAME,VARIABLE_SCOPE,VARIABLE_TYPE,VARIABLE_COMMENT,NUMERIC_MIN_V
           variable_name not like 'wsrep%' and
           variable_name not like 's3%' and
           variable_name not in (
-          'log_tc_size',
-          'in_predicate_conversion_threshold'
+          'log_tc_size'
         )
   order by variable_name;
 
diff --git a/mysql-test/suite/sys_vars/r/sysvars_debug.result b/mysql-test/suite/sys_vars/r/sysvars_debug.result
index b544f6b50d9..f50e796bff4 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_debug.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_debug.result
@@ -1,6 +1,5 @@
 select * from information_schema.system_variables
 where variable_name like 'debug%'
-     or variable_name = 'in_predicate_conversion_threshold'
   order by variable_name;
 VARIABLE_NAME	DEBUG
 SESSION_VALUE	
@@ -86,17 +85,3 @@ NUMERIC_BLOCK_SIZE	NULL
 ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	NULL
-VARIABLE_NAME	IN_PREDICATE_CONVERSION_THRESHOLD
-SESSION_VALUE	1000
-GLOBAL_VALUE	1000
-GLOBAL_VALUE_ORIGIN	COMPILE-TIME
-DEFAULT_VALUE	1000
-VARIABLE_SCOPE	SESSION
-VARIABLE_TYPE	INT UNSIGNED
-VARIABLE_COMMENT	The minimum number of scalar elements in the value list of IN predicate that triggers its conversion to IN subquery
-NUMERIC_MIN_VALUE	0
-NUMERIC_MAX_VALUE	4294967295
-NUMERIC_BLOCK_SIZE	1
-ENUM_VALUE_LIST	NULL
-READ_ONLY	NO
-COMMAND_LINE_ARGUMENT	OPTIONAL
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
index 6d1524f05b9..658f20727d4 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result
@@ -9,8 +9,7 @@ where variable_name not like 'debug%' and
 variable_name not like 'wsrep%' and
 variable_name not like 's3%' and
 variable_name not in (
-'log_tc_size',
-'in_predicate_conversion_threshold'
+'log_tc_size'
         )
 order by variable_name;
 VARIABLE_NAME	ALTER_ALGORITHM
@@ -1293,6 +1292,16 @@ NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	IN_PREDICATE_CONVERSION_THRESHOLD
+VARIABLE_SCOPE	SESSION
+VARIABLE_TYPE	INT UNSIGNED
+VARIABLE_COMMENT	The minimum number of scalar elements in the value list of IN predicate that triggers its conversion to IN subquery. Set to 0 to disable the conversion.
+NUMERIC_MIN_VALUE	0
+NUMERIC_MAX_VALUE	4294967295
+NUMERIC_BLOCK_SIZE	1
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	IN_TRANSACTION
 VARIABLE_SCOPE	SESSION ONLY
 VARIABLE_TYPE	BIGINT UNSIGNED
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index 2aa282e6eb6..93d0cac7f24 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -9,8 +9,7 @@ where variable_name not like 'debug%' and
 variable_name not like 'wsrep%' and
 variable_name not like 's3%' and
 variable_name not in (
-'log_tc_size',
-'in_predicate_conversion_threshold'
+'log_tc_size'
         )
 order by variable_name;
 VARIABLE_NAME	ALTER_ALGORITHM
@@ -1383,6 +1382,16 @@ NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	IN_PREDICATE_CONVERSION_THRESHOLD
+VARIABLE_SCOPE	SESSION
+VARIABLE_TYPE	INT UNSIGNED
+VARIABLE_COMMENT	The minimum number of scalar elements in the value list of IN predicate that triggers its conversion to IN subquery. Set to 0 to disable the conversion.
+NUMERIC_MIN_VALUE	0
+NUMERIC_MAX_VALUE	4294967295
+NUMERIC_BLOCK_SIZE	1
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	IN_TRANSACTION
 VARIABLE_SCOPE	SESSION ONLY
 VARIABLE_TYPE	BIGINT UNSIGNED
diff --git a/mysql-test/suite/sys_vars/t/sysvars_debug.test b/mysql-test/suite/sys_vars/t/sysvars_debug.test
index 71d396e2df3..fbdcbd683df 100644
--- a/mysql-test/suite/sys_vars/t/sysvars_debug.test
+++ b/mysql-test/suite/sys_vars/t/sysvars_debug.test
@@ -4,5 +4,4 @@
 --vertical_results
 select * from information_schema.system_variables
   where variable_name like 'debug%'
-     or variable_name = 'in_predicate_conversion_threshold'
   order by variable_name;
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index c9b55fe210e..816c6fe1089 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -972,7 +972,8 @@ bool Item_func_in::to_be_transformed_into_in_subq(THD *thd)
   if (args[1]->type() == Item::ROW_ITEM)
     values_count*= ((Item_row *)(args[1]))->cols();
 
-  if (values_count < thd->variables.in_subquery_conversion_threshold)
+  if (thd->variables.in_subquery_conversion_threshold == 0 ||
+      thd->variables.in_subquery_conversion_threshold > values_count)
     return false;
 
   return true;
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index fd40f9c5f83..c20fcc4fe85 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -6192,14 +6192,13 @@ static Sys_var_mybool Sys_session_track_state_change(
 
 #endif //EMBEDDED_LIBRARY
 
-#ifndef DBUG_OFF
 static Sys_var_uint Sys_in_subquery_conversion_threshold(
        "in_predicate_conversion_threshold",
        "The minimum number of scalar elements in the value list of "
-       "IN predicate that triggers its conversion to IN subquery",
-       SESSION_VAR(in_subquery_conversion_threshold), CMD_LINE(OPT_ARG),
+       "IN predicate that triggers its conversion to IN subquery. Set to "
+       "0 to disable the conversion.",
+       SESSION_VAR(in_subquery_conversion_threshold), CMD_LINE(REQUIRED_ARG),
        VALID_RANGE(0, UINT_MAX), DEFAULT(IN_SUBQUERY_CONVERSION_THRESHOLD), BLOCK_SIZE(1));
-#endif
 
 static Sys_var_enum Sys_secure_timestamp(
        "secure_timestamp", "Restricts direct setting of a session "