diff options
| -rw-r--r-- | mysql-test/r/derived.result | 23 | ||||
| -rw-r--r-- | mysql-test/t/derived.test | 21 | ||||
| -rw-r--r-- | sql/sql_select.cc | 4 | ||||
| -rw-r--r-- | sql/table.cc | 8 | ||||
| -rw-r--r-- | sql/table.h | 2 |
5 files changed, 53 insertions, 5 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index 0340ebcbe98..4805616841c 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -441,3 +441,26 @@ SELECT MAX(b) FROM (SELECT * FROM t1) AS t WHERE a = 100; MAX(b) 5 DROP TABLE t1; +# +# LP bug #799499: query over a materialized view +# accessed by a key +# +CREATE TABLE t1 (a int) ; +INSERT INTO t1 VALUES (8); +CREATE TABLE t2 (a int, b int) ; +INSERT INTO t2 VALUES +(262, NULL), (253, 190), (260, NULL), (250, 163), (188, 8), +(257,200), (256, NULL), (255, 8), (249, NULL), (259, 7); +CREATE VIEW v1 AS SELECT a, MIN(b) AS b FROM t2 GROUP BY a; +EXPLAIN +SELECT * FROM v1, t1 WHERE v1.b=t1.a ORDER BY v1.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 system NULL NULL NULL NULL 1 Using filesort +1 PRIMARY <derived2> ref key0 key0 5 const 1 Using where +2 DERIVED t2 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort +SELECT * FROM v1, t1 WHERE v1.b=t1.a ORDER BY v1.a; +a b a +188 8 8 +255 8 8 +DROP VIEW v1; +DROP TABLE t1,t2; diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test index 4bba495ca09..b989bfbcf24 100644 --- a/mysql-test/t/derived.test +++ b/mysql-test/t/derived.test @@ -348,3 +348,24 @@ SELECT MAX(b) FROM (SELECT * FROM t1) AS t WHERE a = 100; DROP TABLE t1; +--echo # +--echo # LP bug #799499: query over a materialized view +--echo # accessed by a key +--echo # + +CREATE TABLE t1 (a int) ; +INSERT INTO t1 VALUES (8); + +CREATE TABLE t2 (a int, b int) ; +INSERT INTO t2 VALUES + (262, NULL), (253, 190), (260, NULL), (250, 163), (188, 8), + (257,200), (256, NULL), (255, 8), (249, NULL), (259, 7); + +CREATE VIEW v1 AS SELECT a, MIN(b) AS b FROM t2 GROUP BY a; + +EXPLAIN +SELECT * FROM v1, t1 WHERE v1.b=t1.a ORDER BY v1.a; +SELECT * FROM v1, t1 WHERE v1.b=t1.a ORDER BY v1.a; + +DROP VIEW v1; +DROP TABLE t1,t2; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6b4666a6b5f..c3e1c785b59 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7151,7 +7151,8 @@ static bool create_hj_key_for_table(JOIN *join, JOIN_TAB *join_tab, (first_keyuse || keyuse->keypart != (keyuse-1)->keypart)) { Field *field= table->field[keyuse->keypart]; - table->create_key_part_by_field(keyinfo, key_part_info, field); + uint fieldnr= keyuse->keypart+1; + table->create_key_part_by_field(keyinfo, key_part_info, field, fieldnr); first_keyuse= FALSE; key_part_info++; } @@ -13312,6 +13313,7 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, *reg_field= 0; *blob_field= 0; // End marker share->fields= field_count; + share->column_bitmap_size= bitmap_buffer_size(share->fields); /* If result table is small; use a heap */ /* future: storage engine selection can be made dynamic? */ diff --git a/sql/table.cc b/sql/table.cc index 8427ca6e112..2d2311d8987 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -5241,13 +5241,14 @@ bool TABLE::alloc_keys(uint key_count) void TABLE::create_key_part_by_field(KEY *keyinfo, KEY_PART_INFO *key_part_info, - Field *field) + Field *field, uint fieldnr) { field->flags|= PART_KEY_FLAG; key_part_info->null_bit= field->null_bit; key_part_info->null_offset= (uint) (field->null_ptr - (uchar*) record[0]); key_part_info->field= field; + key_part_info->fieldnr= fieldnr; key_part_info->offset= field->offset(record[0]); key_part_info->length= (uint16) field->pack_length(); keyinfo->key_length+= key_part_info->length; @@ -5340,11 +5341,12 @@ bool TABLE::add_tmp_key(uint key, uint key_parts, for (i= 0; i < key_parts; i++) { - reg_field= field + next_field_no(arg); + uint fld_idx= next_field_no(arg); + reg_field= field + fld_idx; if (key_start) (*reg_field)->key_start.set_bit(key); (*reg_field)->part_of_key.set_bit(key); - create_key_part_by_field(keyinfo, key_part_info, *reg_field); + create_key_part_by_field(keyinfo, key_part_info, *reg_field, fld_idx+1); key_start= FALSE; key_part_info++; } diff --git a/sql/table.h b/sql/table.h index 4205b81dc12..46d6e666bc3 100644 --- a/sql/table.h +++ b/sql/table.h @@ -959,7 +959,7 @@ struct st_table { uint (*next_field_no) (uchar *), uchar *arg, bool unique); void create_key_part_by_field(KEY *keyinfo, KEY_PART_INFO *key_part_info, - Field *field); + Field *field, uint fieldnr); void use_index(int key_to_save); void set_table_map(table_map map_arg, uint tablenr_arg) { |