Description:
resolve_derived function called from fill_schema_table_by_open at https://github.com/mysql/mysql-server/blob/5.7/sql/sql_show.cc#L3950
could return errors.
This is causing that thd->derived_tables_processing became true and previous value is not restored to original value.
Saved here:
https://github.com/mysql/mysql-server/blob/5.7/sql/sql_derived.cc#L52
Restored only if there is no errors:
https://github.com/mysql/mysql-server/blob/5.7/sql/sql_derived.cc#L89
How to repeat:
On production system, according to gdb operation on I_S.columns causing invalid output for further select statements inside stored procedure.
If I adding thd->derived_tables_processing= true; at https://github.com/mysql/mysql-server/blob/5.7/sql/sql_show.cc#L3954 the same behavior could be seen with test case:
--source include/have_innodb.inc
CREATE TABLE `t1` (
`name` varchar(64) NOT NULL DEFAULT '',
`value` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
insert into t1 values ('a',1),('b',2),('c',3);
delimiter |;
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
SQL SECURITY INVOKER
BEGIN
select count(*) from information_schema.columns c;
select * from t1;
select * from t1 where t1.name='a';
END |
delimiter ;|
call test;
select * from t1;
drop procedure test;
drop table t1;
Patch (applied to git 5.7 f5ed5d58ffae0ff4c668830376faa411987915a1):
iff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 8c8963d..024d924 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -3951,6 +3951,7 @@ fill_schema_table_by_open(THD *thd, MEM_ROOT *mem_root,
if (!result)
result= table_list->setup_materialized_derived(thd);
}
+ thd->derived_tables_processing= true;
/*
Restore old value of sql_command back as it is being looked at in
process_table() function.
The modification is not a patch to fix an issue, it's just an attempt to reproduce the picture visible in gdb.
Test results:
CREATE TABLE `t1` (
`name` varchar(64) NOT NULL DEFAULT '',
`value` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
insert into t1 values ('a',1),('b',2),('c',3);
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
SQL SECURITY INVOKER
BEGIN
select count(*) from information_schema.columns c;
select * from t1;
select * from t1 where t1.name='a';
END |
call test;
count(*)
3081
name value
c 3 <- this is Bad: select returned last row multiple times instead of all rows
c 3
c 3
name value
a 3
select * from t1;
name value
a 1
b 2
c 3
drop procedure test;
drop table t1;
Suggested fix:
Expected results:
CREATE TABLE `t1` (
`name` varchar(64) NOT NULL DEFAULT '',
`value` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
insert into t1 values ('a',1),('b',2),('c',3);
CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
SQL SECURITY INVOKER
BEGIN
select count(*) from information_schema.columns c;
select * from t1;
select * from t1 where t1.name='a';
END |
call test;
count(*)
3081
name value
a 1
b 2
c 3
name value
a 3
select * from t1;
name value
a 1
b 2
c 3
drop procedure test;
drop table t1;
