MySQL Bugs: #77391: Round function incorrect in where-clause using local variable

Bug #77391	Round function incorrect in where-clause using local variable
Submitted:	17 Jun 2015 15:52	Modified:	21 Jul 2015 17:39
Reporter:	Lars Winderling	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.6.21, 5.6.25, 5.6.26, 5.5.45, 5.7.8	OS:	Linux (ubuntu 14.04)
Assigned to:	Tor Didriksen	CPU Architecture:	Any

Description:
When using a local variable as number of digits for rounding, this works partly:
- as part of the column list: correct
- as part of the where-clause: incorrect

When executing the attached code, three different selects are issued.
(1) show correct behaviour of round with or without local variable
(2) show correct behaviour of round in where-clause when using literal
(3) show incorrect behaviour of round in where-clause when using local variable.

How to repeat:
DELIMITER ;;
DROP PROCEDURE IF EXISTS test_round_fn;
CREATE PROCEDURE `test_round_fn`()
BEGIN
  DECLARE num_digits int;
  SET num_digits := 3;
  
  DROP TEMPORARY TABLE IF EXISTS tmp_test;
  CREATE TEMPORARY TABLE tmp_test (
    number double
  );
  INSERT INTO tmp_test
  VALUES (1);
  
  SELECT
    number,
    Round(number, num_digits) > 0,
    Round(number, 3) > 0
  FROM
    tmp_test;

  SELECT
    number,
    Round(number, num_digits) > 0,
    Round(number, 3) > 0
  FROM
    tmp_test
  WHERE
    Round(number, 3) > 0;
  
  SELECT
    number,
    Round(number, num_digits) > 0,
    Round(number, 3) > 0
  FROM
    tmp_test
  WHERE
    Round(number, num_digits) > 0;

END;;
DELIMITER ;
CALL test_round_fn();

Suggested fix:
I cannot contribute to fixing it. But for sure, it should round correctly in any place.

Hello Lars Winderling,

Thank you for the report and test case.

Thanks,
Umesh

Noted in 5.7.9, 5.8.0 changelogs.

A predicate of the form WHERE ROUND(X,Y) > 0, where X is a column
name and Y is a program local variable, could return false when it
should return true.