MySQL Bugs: #94800: Lost connection (for Debug version) or wrong result (for release version)

Bug #94800	Lost connection (for Debug version) or wrong result (for release version)
Submitted:	27 Mar 2019 14:11	Modified:	27 Mar 2019 21:20
Reporter:	Weidong Yu	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.7.23-4	OS:	CentOS
Assigned to:		CPU Architecture:	Any

Description:
Optimization issue related to optimizer_switch='firstmatch=off';

How to repeat:
For mysql to run the following command
use test;
create table t (a int);
insert into t values (1);
set optimizer_switch='firstmatch=off';
prepare st1 from "select 1 from t where (? in (select 1 from t)) in (select 1 from t)";
set @a=1;
execute st1 using @a;

For the release version, it return the empty result;
mysql> use test;
Database changed
mysql> create table t (a int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (1);
Query OK, 1 row affected (0.01 sec)

mysql> set optimizer_switch='firstmatch=off';
Query OK, 0 rows affected (0.00 sec)

mysql> prepare st1 from "select 1 from t where (? in (select 1 from t)) in (select 1 from t)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @a=1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute st1 using @a;
Empty set (0.00 sec)

For the debug version, it lost connection. 
mysql> execute st1 using @a;
ERROR 2013 (HY000): Lost connection to MySQL server during query

It is caused by 
DBUG_ASSERT(keyparts > 0);
in function calc_length_and_keyparts() in sql_select.cc (line 1204 in my version)
Trace log is as follows:
Thread pointer: 0x7fb89c000b80
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7fb8e4c29e40 thread_stack 0x40000
/mnt/ssd/work/build-debug/bin/mysqld(my_print_stacktrace+0x35)[0x18c3514]
/mnt/ssd/work/build-debug/bin/mysqld(handle_fatal_signal+0x3f6)[0xeda105]
/lib64/libpthread.so.0(+0xf5e0)[0x7fb8ece7d5e0]
/lib64/libc.so.6(gsignal+0x37)[0x7fb8eba7b1f7]
/lib64/libc.so.6(abort+0x148)[0x7fb8eba7c8e8]
/lib64/libc.so.6(+0x2e266)[0x7fb8eba74266]
/lib64/libc.so.6(+0x2e312)[0x7fb8eba74312]
/mnt/ssd/work/build-debug/bin/mysqld(_Z24calc_length_and_keypartsP7Key_useP8JOIN_TABjyPS0_PjS4_PyPb+0x22a)[0x15    b89cf]
/mnt/ssd/work/build-debug/bin/mysqld(_Z18create_ref_for_keyP4JOINP8JOIN_TABP7Key_usey+0x16d)[0x15b8b50]
/mnt/ssd/work/build-debug/bin/mysqld(_ZN4JOIN15init_ref_accessEv+0x129)[0x15b85d1]
/mnt/ssd/work/build-debug/bin/mysqld(_ZN4JOIN8optimizeEv+0x15a0)[0x153f388]
/mnt/ssd/work/build-debug/bin/mysqld(_ZN13st_select_lex8optimizeEP3THD+0xda)[0x15b81c4]
/mnt/ssd/work/build-debug/bin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x31e)[0x15b6856]
/mnt/ssd/work/build-debug/bin/mysqld[0x156b57d]
/mnt/ssd/work/build-debug/bin/mysqld(_Z21mysql_execute_commandP3THDb+0xd47)[0x15648b4]
/mnt/ssd/work/build-debug/bin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x58d)[0x15a4037]
/mnt/ssd/work/build-debug/bin/mysqld(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0x1de)[0x15a2f28]
/mnt/ssd/work/build-debug/bin/mysqld(_Z22mysql_sql_stmt_executeP3THD+0x1ee)[0x15a0b38]
/mnt/ssd/work/build-debug/bin/mysqld(_Z21mysql_execute_commandP3THDb+0xdb2)[0x156491f]
/mnt/ssd/work/build-debug/bin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x663)[0x156c537]
/mnt/ssd/work/build-debug/bin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xc09)[0x15614a4    ]
/mnt/ssd/work/build-debug/bin/mysqld(_Z10do_commandP3THD+0x536)[0x156038a]
/mnt/ssd/work/build-debug/bin/mysqld(handle_connection+0x210)[0x16a7562]
/mnt/ssd/work/build-debug/bin/mysqld(pfs_spawn_thread+0x170)[0x1d465ac]
/lib64/libpthread.so.0(+0x7e25)[0x7fb8ece75e25]
/lib64/libc.so.6(clone+0x6d)[0x7fb8ebb3e34d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fb89c006320): select 1 from t where (1 in (select 1 from t)) in (select 1 from t)
Connection ID (thread ID): 2
Status: NOT_KILLED

Suggested fix:
I am not sure whether it is correct solution. In file sql_optimizer.cc, in function  JOIN::optimize(), before call "if (init_ref_access()", I added four lines 463-466. This problem is fixed.

I think that it should have better solution

  444   /*
  445     Perform the same optimization on field evaluation for all join conditions.
  446   */
  447   for (uint i= const_tables; i < tables ; ++i)
  448   {
  449     JOIN_TAB *const tab= best_ref[i];
  450     if (tab->position() && tab->join_cond())
  451     {
  452       tab->set_join_cond(substitute_for_best_equal_field(tab->join_cond(),
  453                                                          tab->cond_equal,
  454                                                          map2table));
  455       if (thd->is_error())
  456       {
  457         error= 1;
  458         DBUG_PRINT("error",("Error from substitute_for_best_equal"));
  459         DBUG_RETURN(1);
  460       }
  461       tab->join_cond()->update_used_tables();
  462     }
  463     else if (tab->position() && tab->position()->key)
  464     {
  465       tab->position()->key->used_tables= 0;
  466     }
  467   }
  468
  469   if (init_ref_access())
  470   {
  471     error= 1;
  472     DBUG_PRINT("error",("Error from init_ref_access"));
  473     DBUG_RETURN(1);
  474   }

Thank you for the bug report. Version 8.0 not affected.

Release version wrong result.