Hi!
On Jul 17, gshchepa@stripped wrote:
> ChangeSet@stripped, 2007-07-17 21:55:37+05:00, gshchepa@stripped +4 -0
> Fixed bug #29338.
> Optimization of queries with DETERMINISTIC functions in the
> WHERE clause was not effective: sequential scan was always
> used.
> Now a SF with the DETERMINISTIC flags is treated as constant
> when it's arguments are constants (or a SF doesn't has arguments).
>
> diff -Nrup a/mysql-test/r/sp.result b/mysql-test/r/sp.result
> --- a/mysql-test/r/sp.result 2007-06-07 12:07:38 +05:00
> +++ b/mysql-test/r/sp.result 2007-07-17 21:45:45 +05:00
> @@ -6175,5 +6177,31 @@ View Create View
> v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
> `v1` AS select `test`.`metered`(`t1`.`a`) AS `metered` from `t1`
> DROP VIEW v1;
> DROP FUNCTION metered;
> +DROP TABLE t1;
> +CREATE FUNCTION f1() RETURNS INT DETERMINISTIC RETURN 2;
> +CREATE FUNCTION f2(I INT) RETURNS INT DETERMINISTIC RETURN 3;
> +CREATE TABLE t1 (c1 INT, INDEX(c1));
> +INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
> +CREATE VIEW v1 AS SELECT c1 FROM t1;
> +EXPLAIN SELECT * FROM t1 WHERE c1=1;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
> +EXPLAIN SELECT * FROM t1 WHERE c1=f1();
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
> +EXPLAIN SELECT * FROM v1 WHERE c1=1;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
> +EXPLAIN SELECT * FROM v1 WHERE c1=f1();
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
> +EXPLAIN SELECT * FROM t1 WHERE c1=f2(10);
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 SIMPLE t1 ref c1 c1 5 const 1 Using where; Using index
> +EXPLAIN SELECT * FROM t1 WHERE f2(c1)=3;
> +id select_type table type possible_keys key key_len ref rows Extra
> +1 SIMPLE t1 index NULL c1 5 NULL 5 Using index
I'd prefer you to compare f1 and f2 in otherwise identical queries.
Above you compare a column with f1, and f2 with a constant. Put this
instead:
- EXPLAIN SELECT * FROM t1 WHERE f2(c1)=3;
+ EXPLAIN SELECT * FROM t1 WHERE c1=f2(c1);
+ EXPLAIN SELECT * FROM t1 WHERE c1=f2(rand());
> +DROP VIEW v1;
> +DROP FUNCTION f1;
> DROP TABLE t1;
> End of 5.0 tests
> diff -Nrup a/sql/item_func.cc b/sql/item_func.cc
> --- a/sql/item_func.cc 2007-06-18 18:45:52 +05:00
> +++ b/sql/item_func.cc 2007-07-17 21:45:31 +05:00
> @@ -5257,6 +5257,12 @@ Item_func_sp::Item_func_sp(Name_resoluti
> }
>
>
> +table_map Item_func_sp::used_tables() const
> +{
> + return m_sp->m_chistics->detistic ? 0 : RAND_TABLE_BIT;
> +}
This is wrong. I think you'd notice it if you'd have a correct test
case.
In your fix you return used_tables==0 (pure constant) if the function is
DETERMINISTIC, and RAND_TABLE_BIT otherwise. It doesn't take arguments
into account, if a function is deterministic but has non-constant
arguments it cannot be treated as a constant.
The correct fix is to put
used_tables_cache|= RAND_TABLE_BIT;
in update_used_tables() and fix_fields.
Regards / Mit vielen Grüssen,
Sergei
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik <serg@stripped>
/ /|_/ / // /\ \/ /_/ / /__ Principal Software Developer
/_/ /_/\_, /___/\___\_\___/ MySQL GmbH, Radlkoferstr. 2, D-81373 München
<___/ Geschäftsführer: Kaj Arnö - HRB
München 162140
