Description:
A prepared statement has different results between its first execution and subsequent executions.
The different only manifests in the column attributes (such has nullable)
You can repeat the testcase below without creating a table, by running
mysql --column-type-info
and compare the column info
In the example below the statement is executed twice.
1st time => some columns are declared nullable
2nd time => all culomns are declared not nullable
How to repeat:
drop table if exists t1, t2;
create table t1 (a int not null, c int not null, index(a));
create table t2 (b int not null, index(b));
insert into t1 select 1,1;
insert into t1 select 11,11;
insert into t2 select 1;
insert into t2 select 12;
drop prepare s1;
prepare s1 from 'create table t3 select * from t2 left join t1 on a=b where a = 1';
drop table t3;
execute s1;
desc t3;
drop table t3;
execute s1;
desc t3;
--- results
# 1st execute
execute s1;
desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| b | int(11) | NO | | | |
| a | int(11) | YES | | NULL | |
| c | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.06 sec)
# 2nd execute
drop table t3;
execute s1;
desc t3;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| b | int(11) | NO | | | |
| a | int(11) | NO | | | |
| c | int(11) | NO | | | |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.09 sec)
Suggested fix:
This may or may not be related too Bug #27430 / Bug #27690
This case no table was changed, nor does it lead to a crash.
I dont think it is related, as Bug #27690 also crashes, if you do NOT execute the statement before altering the tables. This indicates that Bug #27690 is caused by information collected during prepare.
While this bug seems to be caused by information collected during/after the 1st execute.
