| 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: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0 | OS: | |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
[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.
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