Below is the list of changes that have just been committed into a local
5.0 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-05-10 10:55:28+03:00, mhansson@stripped +3 -0
Bug#27573: MIN() on an indexed column which is always NULL sets _other_ results
to NULL
We have the following case:
SELECT MIN(key_part_k)
FROM t1
WHERE key_part_1 = const and ... and key_part_k-1 = const
There is an optimization which will substitute a constant for
MIN(key_part_k), inserting the 1) minimum non-null value where the
WHERE clause still matches, or 2) null if there are only NULL
values for keypart2. If no row matches, the result will be 3) a row
of NULLs.
The previous implementation skipped step 2, and went straight to
a row of NULLs.
mysql-test/r/func_group.result@stripped, 2007-05-10 10:55:23+03:00, mhansson@stripped +47 -0
Bug #27573: Correct result
mysql-test/t/func_group.test@stripped, 2007-05-10 10:55:23+03:00, mhansson@stripped +33 -0
Bug #27573: test case
sql/opt_sum.cc@stripped, 2007-05-10 10:55:23+03:00, mhansson@stripped +54 -10
Bug #27573:
Added code that will try to read the
first non-null value for a given complete-field prefix, second
choice is to read the null, and lastly set the error code if no row
is found.
# 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.0o-bug27573
--- 1.61/sql/opt_sum.cc 2007-01-30 23:09:25 +02:00
+++ 1.62/sql/opt_sum.cc 2007-05-10 10:55:23 +03:00
@@ -206,12 +206,46 @@ int opt_sum_query(TABLE_LIST *tables, Li
if (!ref.key_length)
error= table->file->index_first(table->record[0]);
- else
- error= table->file->index_read(table->record[0],key_buff,
- ref.key_length,
- range_fl & NEAR_MIN ?
- HA_READ_AFTER_KEY :
- HA_READ_KEY_OR_NEXT);
+ else
+ {
+ /*
+ Read the smallest value using the search key. If the interval is
+ open, skip the first tuple. If read fails, and we're looking for a
+ MIN() value for a nullable cloumn, read the one we skipped.
+ */
+ if (!(range_fl & NEAR_MIN))
+ /*
+ Closed interval: Either The column is non-nullable, or we have
+ a >= predicate for the column.
+ */
+ error= table->file->index_read(table->record[0], ref.key_buff,
+ ref.key_length,
+ HA_READ_KEY_OR_NEXT);
+ else
+ {
+ /*
+ Open interval: There are two cases:
+ 1) We have only MIN() and the column is nullable, or
+ 2) there is a > predicate on it, nullability is irrelevant.
+ We need to scan the next bigger record first.
+ */
+ error= table->file->index_read(table->record[0], ref.key_buff,
+ ref.key_length, HA_READ_AFTER_KEY);
+ /*
+ If read failed (i.e. we are at the end of the index or outside
+ the group formed by prefix) we check if case 1 from above
+ holds. If so, we should read the skipped tuple.
+ Conditions are reordered to put cheapest first.
+ */
+ if (ref.key_buff[prefix_len] == 1 && // case 1, keypart is NULL
+ (error == HA_ERR_KEY_NOT_FOUND ||
+ key_cmp_if_same(table, ref.key_buff, ref.key, prefix_len)))
+ error= table->file->index_read(table->record[0], ref.key_buff,
+ ref.key_length,
+ HA_READ_KEY_OR_PREV);
+ }
+ }
+ /* Verify that the read tuple indeed matches the search key */
if (!error && reckey_in_range(0, &ref, item_field->field,
conds, range_fl, prefix_len))
error= HA_ERR_KEY_NOT_FOUND;
@@ -739,14 +773,24 @@ static bool find_key_for_maxmin(bool max
if (!max_fl && key_part_used == key_part_to_use && part->null_bit)
{
/*
- SELECT MIN(key_part2) FROM t1 WHERE key_part1=const
- If key_part2 may be NULL, then we want to find the first row
- that is not null
+ The query is on this form:
+
+ SELECT MIN(key_part_k)
+ FROM t1
+ WHERE key_part_1 = const and ... and key_part_k-1 = const
+
+ If key_part_k is nullable, we want to find the first matching row
+ where key_part_k is not null. The key buffer is now {const, ...,
+ NULL}. This will be passed to the handler along with a flag
+ indicating open interval. If a tuple is read that does not match
+ these search criteria, an attempt will be made to read an exact
+ match for the key buffer.
*/
+ /* Set the first byte of key_part_k to 1, that means NULL */
ref->key_buff[ref->key_length]= 1;
ref->key_length+= part->store_length;
*range_fl&= ~NO_MIN_RANGE;
- *range_fl|= NEAR_MIN; // > NULL
+ *range_fl|= NEAR_MIN; // Open interval
}
/*
The following test is false when the key in the key tree is
--- 1.59/mysql-test/r/func_group.result 2007-02-27 09:01:57 +02:00
+++ 1.60/mysql-test/r/func_group.result 2007-05-10 10:55:23 +03:00
@@ -1321,4 +1321,51 @@ SELECT a,AVG(DISTINCT b) AS average FROM
a average
1 32768.5000
DROP TABLE t1;
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2
+SELECT MIN(a), MIN(b) FROM t1;
+MIN(a) MIN(b)
+NULL 1
+CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
+INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
+EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref a a 5 const 2 Using where
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+MIN(b) MIN(c)
+3 2
+CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2), (2, NULL, 2), (3, NULL, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+MIN(a) MIN(b)
+2 NULL
+CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (2, NULL, 2), (3, 1, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+MIN(a) MIN(b)
+2 NULL
+SELECT MIN(b), min(c) FROM t4 where a = 2;
+MIN(b) min(c)
+NULL 2
+CREATE TABLE t5( a INT, b INT, KEY( a, b) );
+INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
+EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Select tables optimized away
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+MIN(a) MIN(b)
+1 1
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
+MIN(a) MIN(b)
+1 2
+DROP TABLE t1, t2, t3, t4, t5;
End of 5.0 tests
--- 1.54/mysql-test/t/func_group.test 2007-02-27 09:01:57 +02:00
+++ 1.55/mysql-test/t/func_group.test 2007-05-10 10:55:23 +03:00
@@ -817,5 +817,38 @@ SELECT a,AVG(DISTINCT b) AS average FROM
DROP TABLE t1;
+#
+# Bug #27573: MIN() on an indexed column which is always NULL sets _other_
+# results to NULL
+#
+CREATE TABLE t1 ( a INT, b INT, KEY(a) );
+INSERT INTO t1 VALUES (NULL, 1), (NULL, 2);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t1;
+SELECT MIN(a), MIN(b) FROM t1;
+
+CREATE TABLE t2( a INT, b INT, c INT, KEY(a, b) );
+INSERT INTO t2 ( a, b, c ) VALUES ( 1, NULL, 2 ), ( 1, 3, 4 ), ( 1, 4, 4 );
+EXPLAIN SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+SELECT MIN(b), MIN(c) FROM t2 WHERE a = 1;
+
+CREATE TABLE t3 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t3 VALUES (1, NULL, 1), (2, NULL, 2), (2, NULL, 2), (3, NULL, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+SELECT MIN(a), MIN(b) FROM t3 where a = 2;
+
+CREATE TABLE t4 (a INT, b INT, c int, KEY(a, b));
+INSERT INTO t4 VALUES (1, 1, 1), (2, NULL, 2), (2, NULL, 2), (3, 1, 3);
+EXPLAIN SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+SELECT MIN(a), MIN(b) FROM t4 where a = 2;
+SELECT MIN(b), min(c) FROM t4 where a = 2;
+
+CREATE TABLE t5( a INT, b INT, KEY( a, b) );
+INSERT INTO t5 VALUES( 1, 1 ), ( 1, 2 );
+EXPLAIN SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1;
+SELECT MIN(a), MIN(b) FROM t5 WHERE a = 1 and b > 1;
+
+DROP TABLE t1, t2, t3, t4, t5;
+
###
--echo End of 5.0 tests
| Thread |
|---|
| • bk commit into 5.0 tree (mhansson:1.2432) BUG#27573 | mhansson | 10 May |
