Bug #29901 result of prepared statement changes between 2 executions
Submitted: 19 Jul 2007 14:50 Modified: 8 Aug 2007 11:39
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.21 5.0.46 OS:FreeBSD
Assigned to:
Tags: prepared statement

[19 Jul 2007 14:50] Martin Friebe
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.
[19 Jul 2007 15:36] Miguel Solorzano
Thank you for the bug report. Verified as described.
[8 Aug 2007 11:39] Konstantin Osipov
Thank you for your bug report.
This is a duplicate of Bug#2812 "wrong value for stmt->field_count".