Description:
In this select:
select *,c1 as c2 from (select x,bug.dups_insert() as c1 from (select "x" as x) as t1 ) as t2
I expected the function bug.dups_insert() to be executed only once. It is executed twice.
The "*" and the "c1 as c2" both seem to cause the value of c1 to be evaluated.
(since both are required for the behavior to happen).
I suspect this is considered to be within the discretion of the optimizer and therefore not deemed a bug, but it confused me for a long time so I thought it worth isolating and reporting.
The test case shows the behavior by inserting into a table on each call to the function.
mysql> delimiter ^
mysql> create database if not exists bug^
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> drop table if exists bug.dups^
Query OK, 0 rows affected (0.00 sec)
mysql> create table bug.dups (dt datetime)^
Query OK, 0 rows affected (0.02 sec)
mysql> drop function if exists bug.dups_insert^
Query OK, 0 rows affected (0.00 sec)
mysql> create function bug.dups_insert() returns text begin insert into bug.dups values(now()); return "text"; end^
Query OK, 0 rows affected (0.00 sec)
mysql> select *,c1 as c2 from (select x,bug.dups_insert() as c1 from (select "x" as x) as t1 ) as t2^
+---+------+------+
| x | c1 | c2 |
+---+------+------+
| x | text | text |
+---+------+------+
1 row in set (0.00 sec)
mysql> select count(*) from bug.dups^
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
mysql>
How to repeat:
delimiter ^
create database if not exists bug^
drop table if exists bug.dups^
create table bug.dups (dt datetime)^
drop function if exists bug.dups_insert^
create function bug.dups_insert() returns text begin insert into bug.dups values(now()); return "text"; end^
select *,c1 as c2 from (select x,bug.dups_insert() as c1 from (select "x" as x) as t1 ) as t2^
select count(*) from bug.dups^
