MySQL Bugs: #24864: Functions with a known result are NOT 'resolved'

Bug #24864	Functions with a known result are NOT 'resolved'
Submitted:	6 Dec 2006 19:13	Modified:	13 Mar 2007 19:03
Reporter:	Andre Timmer	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.32-BK, 5.0.18	OS:	Linux (Linux)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
First statement executes in 1s.
Second one, a copy of the first statement but with 'resolved functions', in 0.5s.

How to repeat:

-- first statement
select * 
from   attribute
where  message_id = 14 
and    f_attribute_path(822) = substring(f_attribute_path(id), 1, length(f_attribute_path(822)))
;

Executes in 1s.

-- second statement (with resolved functions)
'select f_attribute_path(822)' yields '/root/ClientSuwi'

select * 
from   attribute
where  message_id = 14 
and    '/root/ClientSuwi' = substring(f_attribute_path(id), 1, length('/root/ClientSuwi'))
;

Executes in 0.5s

The difference is noticable because it's this function is quite slow.

Hypothesis: 
- it's not detected that the function has a fixed outcome and could be 'resolved'

Suggested fix:
Detect that function has a fixed outcome and replace those functions by the result  before starting the query.
Maybe this influences even optimizer decisions.

substring(f_attribute_path(id), 1, length('/root/ClientSuwi')
 could be resolved to 
substring(f_attribute_path(id), 1, 16)

Thank you for a problem report. Please, send the results of SHOW CREATE TABLE and SHOW TABLE STATUS for that attribute table. Complete CREATE statement for that f_attribute_path function will be also useful. We need a complete test case that demonstrates the problem on latest (5.0.27) version of MySQL server.

Example isn't worth that much without testdata.
This is much work.

A simpeler testcase:
SELECT BENCHMARK(1000000,substring('hello and goodbye', length('hello and'), length('goodbye'))) test1;
SELECT BENCHMARK(1000000,substring('hello and goodbye', 9, 7)) test2;

Both statements do the same, but the second statement is always faster.
Your SQL compiler builders should be able to conform wheather intermediate results which will not changed are 'resolved/reused'.

Verified just as described in the last comment, with 5.0.32-BK on Linux:

mysql> explain extended SELECT BENCHMARK(1000000,substring('hello and goodbye',
 length('hello and'), length('goodbye'))) test1;
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows
 | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL
 | No tables used |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+----------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select benchmark(1000000,substr(_latin1'hello and goodbye',length(_lati
n1'hello and'),length(_latin1'goodbye'))) AS `test1`
1 row in set (0.00 sec)

So, as you can see above, functions are not "resolved" really. And it makes differtence for wall-clock run time:

mysql> SELECT BENCHMARK(100000000,substring('hello and goodbye', 9, 7)) test2;
+-------+
| test2 |
+-------+
|     0 |
+-------+
1 row in set (9.82 sec)

mysql> SELECT BENCHMARK(100000000,substring('hello and goodbye', length('hello
and'), length('goodbye'))) test1;
+-------+
| test1 |
+-------+
|     0 |
+-------+
1 row in set (11.25 sec)

We do not not resolve constant expressions neither when using BENCHMARK (which would have devaluated BENCHMARK to a great extent) nor when using prepared statements. We resolve constant expressions only before execution (which can be easily checked in the debugger) of the query.