Bug #11771 View over InnoDB table, wrong result SELECT on VIEW, field->query_id wrong
Submitted: 6 Jul 2005 10:29 Modified: 12 Jul 2005 19:36
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0 OS:
Assigned to: Igor Babaev CPU Architecture:Any

[6 Jul 2005 10:29] Matthias Leich
Description:
Example:
CREATE TABLE t1 (f1 CHAR) ENGINE = innodb;
INSERT INTO t1 VALUES ('A');
SELECT * FROM t1;
f1
A
CREATE VIEW  v1 AS SELECT * FROM t1;
INSERT INTO t1 VALUES('B');
affected rows: 1
SELECT * FROM v1;
f1
B                <---- Wrong result. I expect to get one
B                <---- record with 'A' and one with 'B'.
SELECT * from t1;
f1
A
B
# sleep of 3 seconds
SELECT * FROM v1;
f1
A
B

1. The storage engines MyISAM and Memory do not show
    this problem, but maybe my notebook is not fast enough
    to reveal this bad effect on them.
2. It looks like this bug was introduced after 2005-06-23,
    because this effect did not appear before.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1893, 2005-07-06

How to repeat:
Please execute the statements above or use the
attached testscript.
  copy it to mysql-test/t
  touch r/ml030.result     # Produce a dummy file with 
                                    # expected results
  export engine=innodb ; ./mysql-test-run ml030
  inspect r/ml030.reject

Tests with other storage could be made via
  export engine=<storage engine> ; ./mysql-test-run ml030
[6 Jul 2005 10:52] Matthias Leich
testcase

Attachment: ml030.test (application/test, text), 397 bytes.

[6 Jul 2005 11:26] Heikki Tuuri
Hi!

The bug is that MySQL does not set field->query_id == thd->query_id in the first SELECT after the inserts. In the second SELECT it does set them correctly, and that is why the second SELECT works.

Maybe some VIEW bug fix in the past 2 weeks has broken the code?

Regards,

Heikki

Breakpoint 1, build_template (prebuilt=0x40af2a68, thd=0x8c3eae0,
    table=0x8c49120, templ_type=1) at ha_innodb.cc:2877
2877            ulint           n_requested_fields      = 0;
(gdb) next
2878            ibool           fetch_all_in_key        = FALSE;
(gdb)
2879            ibool           fetch_primary_key_cols  = FALSE;
(gdb)
2882            ulint           mysql_prefix_len        = 0;
(gdb)
2884            if (prebuilt->select_lock_type == LOCK_X) {
(gdb)
2892            if (templ_type == ROW_MYSQL_REC_FIELDS) {
(gdb)
2893                 if (prebuilt->hint_need_to_fetch_extra_cols
(gdb)
2911                } else if (prebuilt->hint_need_to_fetch_extra_cols
(gdb)
2923            clust_index = dict_table_get_first_index_noninline(prebuilt->tab
le);
(gdb)
2925            if (templ_type == ROW_MYSQL_REC_FIELDS) {
(gdb)
2926                    index = prebuilt->index;
(gdb)
2931            if (index == clust_index) {
(gdb)
2932                    prebuilt->need_to_access_clustered = TRUE;
(gdb)
2939            n_fields = (ulint)table->s->fields; /* number of columns */
(gdb)
2941            if (!prebuilt->mysql_template) {
(gdb)
2947            prebuilt->template_type = templ_type;
(gdb)
2948            prebuilt->null_bitmap_len = table->s->null_bytes;
(gdb)
2950            prebuilt->templ_contains_blob = FALSE;
(gdb)
2954            for (i = 0; i < n_fields; i++) {
(gdb)
2955                    templ = prebuilt->mysql_template + n_requested_fields;
(gdb)
2956                    field = table->field[i];
(gdb)
2958                    if (UNIV_LIKELY(templ_type == ROW_MYSQL_REC_FIELDS)) {
(gdb)
2961                            register const ibool    index_contains_field =
(gdb)
2964                            if (!index_contains_field && prebuilt->read_just
_key) {
(gdb)
2971                            if (index_contains_field && fetch_all_in_key) {
(gdb)
2977                            if (thd->query_id == field->query_id) {
(gdb)
2983                            if (fetch_primary_key_cols
(gdb) print thd->query_id
$2 = 8
(gdb) print field->query_id
$3 = 6
(gdb)
[12 Jul 2005 12:18] 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/26939
[12 Jul 2005 14:32] Igor Babaev
ChangeSet
  1.1905 05/07/12 05:18:05 igor@igor-inspiron.creware.com +4 -0
  view.result, view.test:
    Added a test case for bug #11771.
  item.h:
    Fixed bug #11771.
    Added method reset_query_id_processor to be able to adjust
    query_id for fields generated from * in queries like this:
    SELECT * FROM <view> ...
  sql_base.cc:
    Fixed bug #11771.
    Adjusted query_id for fields generated from * in queries
    like this: SELECT * FROM <view> ...

The fix will appear in 5.0.10
[12 Jul 2005 19:36] Paul Dubois
Noted in 5.0.10 changelog.