Below is the list of changes that have just been committed into a local
5.1 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-03-06 13:44:28+01:00, mhansson@stripped +4 -0
Bug #24778: Innodb: No result when using ORDER BY
This bug appears only in the case when the optimizer has chosen an index
for accessing a particular table but finds a covering index that enables
it to skip ORDER BY. This happens in test_if_skip_sort_order.
The bug is in the assumption that: If we change the index for accessing
a table in order to skip ORDER BY for a given plan operator, this plan
operator must be for the first non-const table in the plan. This is not
true: There can be other tables preceding it and the test case is an
example of that. What happens is that
create_ref_for_key is called with the set of const tables, in this case
the empty set. So here no key gets chosen, and we get the empty set.
We should have to find all tables preceding the plan operator at hand
in this case. On the other hand, since we know that create_ref_for_key
picked a key once, and we will simply swap it for another key for the same
plan operator, it is enough to send in the set of just the previous table.
It follows from the above reasoing that tab->key_dependent can be used,
which is precomputed.
mysql-test/r/key.result@stripped, 2007-03-06 13:44:21+01:00, mhansson@stripped +41 -0
Bug#24791
test case.
The bug causes the result to be the empty set.
mysql-test/t/key.test@stripped, 2007-03-06 13:44:21+01:00, mhansson@stripped +47 -0
Bug#24791
The minimal test case that reveals the bug. The reason for such a
complicated schema is that we have to convince the optimizer to pick one
index, then discard it in order to be able to skip ORDER BY.
sql/sql_select.cc@stripped, 2007-03-06 13:44:21+01:00, mhansson@stripped +36 -10
Bug#24791
In order of appearance:
- Added documantation about key_dependent to add_key_field.
- Aligned comments to the same column
- Corrected typo
- completed comments to test_if_subkey
- The change that fixes the bug. See cset comment
sql/sql_select.h@stripped, 2007-03-06 13:44:21+01:00, mhansson@stripped +23 -5
Bug#24791
In order of appearance:
- I spent a long time to find out the semantics of JOIN_TAB::key_dependent
so I hope this comment will save time for anyone else who needs to use it.
- Added comment above the field JOIN::tables
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: mhansson
# Host: linux-st28.site
# Root: /home/martin/mysql/src/5.1o-bug24778
--- 1.485/sql/sql_select.cc 2007-01-25 00:30:31 +01:00
+++ 1.486/sql/sql_select.cc 2007-03-06 13:44:21 +01:00
@@ -2746,6 +2746,9 @@ merge_key_fields(KEY_FIELD *start,KEY_FI
If we are doing a NOT NULL comparison on a NOT NULL field in a outer join
table, we store this to be able to do not exists optimization later.
+ This function updates key_dependent for the field's JOIN_TAB by looping
+ through the items in 'value' and collecting all tables.
+
RETURN
*key_fields is incremented if we stored a key in the array
*/
@@ -2799,8 +2802,8 @@ add_key_field(KEY_FIELD **key_fields,uin
Field op formula
Field IS NULL
Field IS NOT NULL
- Field BETWEEN ...
- Field IN ...
+ Field BETWEEN ...
+ Field IN ...
*/
stat[0].key_dependent|=used_tables;
@@ -8272,7 +8275,7 @@ static void reset_nj_counters(List<TABLE
/*
- Check interleaving with an inner tables of an outer join for extension table
+ Check interleaving with an inner table of an outer join for extension table
SYNOPSIS
check_interleaving_with_nj()
@@ -12066,16 +12069,33 @@ is_subkey(KEY_PART_INFO *key_part, KEY_P
}
/*
- Test if we can use one of the 'usable_keys' instead of 'ref' key for sorting
+ Test if we can use one of the 'usable_keys' instead of 'ref' key for sorting.
+ The function searches the shortest key that is sorted according to 'order'
+ and also covers 'ref', i.e. it consists of the same or more keyparts.
+ 'ref' is typically used for ref access to the table.
SYNOPSIS
test_if_subkey()
- ref Number of key, used for WHERE clause
- usable_keys Keys for testing
+ order Only keys sorted accordingly are considered
+ table The table whose usable keys we scan through
+ ref Ordinal number of key, used for WHERE clause
+ ref_key_parts Number of keyparts that make up the key
+ usable_keys Keys that may be used
RETURN
- MAX_KEY If we can't use other key
- the number of found key Otherwise
+ MAX_KEY If we can't use any other key in usable_keys
+ the ordinal of found key Otherwise
+
+ IMPLEMENTATION
+ Scans through all usable keys for 'table' and selects the one that:
+
+ - Has the shortest key_length
+ - Has the greatest number of key parts.
+ - Is a superkey of 'ref'
+ - Is ordered in the same way
+
+ If several keys are eligible, the last one in table->key_info will be
+ chosen.
*/
static uint
@@ -12345,8 +12365,14 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
KEYUSE *keyuse= tab->keyuse;
while (keyuse->key != new_ref_key && keyuse->table == tab->table)
keyuse++;
- if (create_ref_for_key(tab->join, tab, keyuse,
- tab->join->const_table_map))
+ /*
+ We will now create the ref access method for the new key,
+ and we know that another key was being used until now, so
+ we know that key will be in the set tab->key_dependent. Therefore
+ we don't need to send the whole set of previous tables in the plan
+ to create_ref_for_key, as we would normally have to.
+ */
+ if (create_ref_for_key(tab->join, tab, keyuse, tab->key_dependent))
DBUG_RETURN(0);
}
else
--- 1.117/sql/sql_select.h 2007-01-24 20:24:50 +01:00
+++ 1.118/sql/sql_select.h 2007-03-06 13:44:21 +01:00
@@ -180,7 +180,22 @@ typedef struct st_join_table {
*/
ha_rows read_time;
- table_map dependent,key_dependent;
+ table_map dependent;
+
+ /*
+ This member has a different purpose for outer and inner joins.
+
+ For outer joins:
+ This member echoes 'JOIN_TAB::dependent', i.e. they are the same. This is
+ initialized in make_join_statistics
+
+ For inner joins:
+ The set of all tables that access to this table depends upon, such that
+ there is a key that can be used for accessing this table. In other words,
+ this is not the set of *all* tables whose access must precede access to
+ this table.
+ */
+ table_map key_dependent;
uint use_quick,index;
uint status; // Save status for cache
uint used_fields,used_fieldlength,used_blobs;
@@ -258,10 +273,13 @@ public:
JOIN_TAB **map2table; // mapping between table indexes and JOIN_TABs
JOIN_TAB *join_tab_save; // saved join_tab for subquery reexecution
TABLE **table,**all_tables,*sort_by_table;
- uint tables,const_tables;
- uint send_group_parts;
- bool sort_and_group,first_record,full_join,group, no_field_update;
- bool do_send_rows;
+
+ /* Total number of tables in the query */
+ uint tables;
+ uint const_tables;
+ uint send_group_parts;
+ bool sort_and_group, first_record, full_join,group, no_field_update;
+ bool do_send_rows;
/*
TRUE when we want to resume nested loop iterations when
fetching data from a cursor
--- 1.40/mysql-test/r/key.result 2006-08-11 23:06:20 +02:00
+++ 1.41/mysql-test/r/key.result 2007-03-06 13:44:21 +01:00
@@ -482,3 +482,44 @@ alter table t1 drop index i3, drop index
alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
ERROR 23000: Duplicate entry '1' for key 'i1'
drop table t1;
+CREATE TABLE t1 (
+a INTEGER auto_increment PRIMARY KEY,
+b INTEGER NOT NULL,
+c INTEGER NOT NULL,
+d CHAR(64)
+);
+CREATE TABLE t2 (
+a INTEGER auto_increment PRIMARY KEY,
+b INTEGER NOT NULL,
+c SMALLINT NOT NULL,
+d DATETIME NOT NULL,
+e SMALLINT NOT NULL,
+f INTEGER NOT NULL,
+g INTEGER NOT NULL,
+h SMALLINT NOT NULL,
+i INTEGER NOT NULL,
+j INTEGER NOT NULL,
+UNIQUE INDEX (b),
+INDEX (b, d, e, f, g, h, i, j, c),
+INDEX (c)
+);
+INSERT INTO t2 VALUES
+(NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0),
+(NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0),
+(NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0),
+(NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0),
+(NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0),
+(NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0),
+(NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0),
+(NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0);
+INSERT INTO t1 (b, c, d) VALUES
+(3388000, -553000, NULL),
+(3388000, -553000, NULL);
+SELECT *
+FROM t2 c JOIN t1 pa ON c.b = pa.a
+WHERE c.c = 1
+ORDER BY c.b, c.d
+;
+a b c d e f g h i j a b c d
+2 2 1 2004-11-30 12:00:00 1 0 0 0 0 0 2 3388000 -553000 NULL
+DROP TABLE t1, t2;
--- 1.31/mysql-test/t/key.test 2006-07-20 20:41:52 +02:00
+++ 1.32/mysql-test/t/key.test 2007-03-06 13:44:21 +01:00
@@ -442,3 +442,50 @@ alter table t1 drop index i3, drop index
alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
drop table t1;
+#
+# Bug #24778 Innodb: No result when using ORDER BY
+#
+CREATE TABLE t1 (
+ a INTEGER auto_increment PRIMARY KEY,
+ b INTEGER NOT NULL,
+ c INTEGER NOT NULL,
+ d CHAR(64)
+);
+
+CREATE TABLE t2 (
+ a INTEGER auto_increment PRIMARY KEY,
+ b INTEGER NOT NULL,
+ c SMALLINT NOT NULL,
+ d DATETIME NOT NULL,
+ e SMALLINT NOT NULL,
+ f INTEGER NOT NULL,
+ g INTEGER NOT NULL,
+ h SMALLINT NOT NULL,
+ i INTEGER NOT NULL,
+ j INTEGER NOT NULL,
+ UNIQUE INDEX (b),
+ INDEX (b, d, e, f, g, h, i, j, c),
+ INDEX (c)
+);
+
+INSERT INTO t2 VALUES
+ (NULL, 1, 254, '1000-01-01 00:00:00', 257, 0, 0, 0, 0, 0),
+ (NULL, 2, 1, '2004-11-30 12:00:00', 1, 0, 0, 0, 0, 0),
+ (NULL, 3, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -21600, 0),
+ (NULL, 4, 1, '2004-11-30 12:00:00', 1, 0, 0, 2, -10800, 0),
+ (NULL, 5, 1, '2004-11-30 12:00:00', 1, 0, 0, 5, -10800, 0),
+ (NULL, 6, 1, '2004-11-30 12:00:00', 102, 0, 0, 0, 0, 0),
+ (NULL, 7, 1, '2004-11-30 12:00:00', 105, 2, 0, 0, 0, 0),
+ (NULL, 8, 1, '2004-11-30 12:00:00', 105, 10, 0, 0, 0, 0);
+
+INSERT INTO t1 (b, c, d) VALUES
+ (3388000, -553000, NULL),
+ (3388000, -553000, NULL);
+
+SELECT *
+FROM t2 c JOIN t1 pa ON c.b = pa.a
+WHERE c.c = 1
+ORDER BY c.b, c.d
+;
+
+DROP TABLE t1, t2;
| Thread |
|---|
| • bk commit into 5.1 tree (mhansson:1.2409) BUG#24778 | mhansson | 6 Mar |
