MySQL Bugs: #98160: execution of stored function in a where clause is skipped

Bug #98160	execution of stored function in a where clause is skipped
Submitted:	8 Jan 2020 17:31	Modified:	13 Jan 2020 15:51
Reporter:	John LeSueur	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.16-8.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
as found here: https://forums.mysql.com/read.php?115,683498,683498

When an indexed integer column is compared to a string numeric value, a condition that includes a stored function is ignored, and the stored function is not executed.

the simplest expression of the bug is this:
select 1 from t1 where t1.id = '1234' and 1=returnzero()
returns a single row.
select 1 from t1 where t1.id = 1234 and 1=returnzero()
returns no rows.

We expect both queries to return no rows.

How to repeat:
drop table if exists t1;
create table t1 (id int PRIMARY KEY);
insert into t1 VALUES(1234);
drop function if exists returnzero;
create function returnzero() returns int(11)
begin
  return 0;
end;
select 1 from t1 where t1.id = '1234' and 1=returnzero();
select 1 from t1 where t1.id = 1234 and 1=returnzero();

Here is the optimizer trace of the broken query:
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select 1 AS `1` from `t1` where ((`t1`.`id` = '1234') and (1 = `returnzero`()))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`t1`.`id` = '1234') and (1 = `returnzero`()))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`t1`.`id` = '1234') and (1 = `returnzero`()))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`t1`.`id` = '1234') and (1 = `returnzero`()))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`t1`.`id` = 1234) and (1 = `returnzero`()))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t1`",
                "field": "id",
                "equals": "1234",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "rows": 1,
                "cost": 1,
                "table_type": "const",
                "empty": false
              }
            ]
          },
          {
            "condition_on_constant_tables": "('1234' = 1234)",
            "condition_value": true
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(('1234' = 1234) and (1 = `returnzero`()))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
              ]
            }
          },
          {
            "refine_plan": [
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

And here is the trace of the working query (in addition, there is an entry in the OPTIMIZER_TRACE table for the function call, but there's nothing interesting there):
{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select 1 AS `1` from `t1` where ((`t1`.`id` = 1234) and (1 = `returnzero`()))"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`t1`.`id` = 1234) and (1 = `returnzero`()))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((1 = `returnzero`()) and multiple equal(1234, `t1`.`id`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((1 = `returnzero`()) and multiple equal(1234, `t1`.`id`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((1 = `returnzero`()) and multiple equal(1234, `t1`.`id`))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`t1`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`t1`",
                "field": "id",
                "equals": "1234",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`t1`",
                "rows": 1,
                "cost": 1,
                "table_type": "const",
                "empty": false
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((1 = `returnzero`()))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
              ]
            }
          },
          {
            "refine_plan": [
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

Hi Mr. LeSueur,

Thank you for your bug report.

However, I am not able to repeat your problem. Both queries return a row.

They should both return zero rows, but you have not provided correct attributes for your stored function.

Hi,

Actually I re-checked against 5.7, so no attributes are needed.

This is a regression bug in 8.0.

Verified as reported.

Hi,

This bug is fixed in 8.0.19, which is already out.

Thank you! I see this note in the release notes, which looks applicable:

When optimizer extracts conditions on constant tables for early evaluation, it does not include WHERE conditions that are expensive to evaluate, including conditions involving stored functions. When the extracted condition evaluated to true because it involved only const tables, the entire WHERE condition was incorrectly removed. Now in such cases, a check for expensive conditions is performed prior to any removal of the WHERE condition. (Bug #30520714)

Is that bug number from an internal bug tracker? can't seem to find anything like it.

This is a bug number from our internal and non-public bug database.