MySQL Bugs: #12382: View: Temporary wrong results

Bug #12382	View: Temporary wrong results
Submitted:	4 Aug 2005 13:42	Modified:	17 Aug 2005 18:09
Reporter:	Kai Voigt	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.10-beta-max/BK source	OS:	MacOS (MacOSX, Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
After setting up a simple table with auto_increment fields and a simple view, the same query on the view gives two different results.

How to repeat:
The following lines of SQL code trigger the bug.

drop database if exists viewbug;
create database viewbug;
use viewbug;
create table a (id int primary key not null auto_increment, f1 varchar(255), f2 varchar(255));
create view b as select id, f1 from a where id <=2;
insert into a values (NULL, "foo1", "bar1");
insert into a values (NULL, "foo2", "bar2");
select * from b; 
select * from b;

The results of the two queries:

+----+------+
| id | f1   |
+----+------+
|  1 | foo2 |
|  2 | foo2 |
+----+------+
2 rows in set (0.00 sec)

+----+------+
| id | f1   |
+----+------+
|  1 | foo1 |
|  2 | foo2 |
+----+------+
2 rows in set (0.01 sec)

Thank you for the bug report.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28198

ChangeSet
  1.1982 05/08/11 16:10:34 igor@rurik.mysql.com +3 -0
  sql_base.cc:
    Fixed bug #12382.
    INSERT statement effectively changed thd->set_query_id to 0,
    while SELECT statement changed it to 0. As a result
    the insert_fields function that expanded '*' was called
    with different values of thd->set_query_id for the query
    SELECT * FROM view depending on whether it was run after
    an INSERT or after a SELECT statement. This was corrected
    by restoring the old value of thd->set_query_id when
    returning from the function setup_fields where possible
    reset could occur.
    If the value of thd->set_query_id == 0 then the fields
    substituted instead of '*' were not registered as used
    for bitmaps used_keys. This caused selection of an invalid
    execution plan for the query SELECT * from <view>.
  view.result, view.test:
    Added a test case for bug #12382.

Fix will appear in 5.0.12.

Noted in 5.0.12 changelog.