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:Mac OS X (MacOSX, Linux)
Assigned to: Igor Babaev CPU Architecture:Any

[4 Aug 2005 13:42] Kai Voigt
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;
[4 Aug 2005 13:42] Kai Voigt
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)
[4 Aug 2005 13:53] Miguel Solorzano
Thank you for the bug report.
[11 Aug 2005 23:10] Bugs System
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
[14 Aug 2005 14:55] Igor Babaev
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.
[17 Aug 2005 18:09] Paul Dubois
Noted in 5.0.12 changelog.