Bug #106332 unexpected repeated execution of non-idempotent function in a select
Submitted: 30 Jan 12:54 Modified: 30 Jan 14:37
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7/8.0 OS:CentOS (7)
Assigned to: CPU Architecture:Any

[30 Jan 12:54] Dave Pullin
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^
[30 Jan 14:37] MySQL Verification Team
Thank you for the bug report.