Virtual columns do not evaluate properly with set/enum and certain expressions

[Philip Stoev]

set/enum columns that participate in virtual columns may lead to wrong results depending on the virtual column expression. There may be a deeper underlying problem since sometimes the expression is not evaluated properly even without a virtual column.

The test case below evaluates one and the same expression in a virtual column context and as a normal expression in the SELECT list.

SET @c1 = "
CREATE TABLE `X` (
  `f1` int(11) NOT NULL DEFAULT '0',
  `f4` enum('0','1','2') NOT NULL DEFAULT '0',
  `v4` double AS
";

SET @virtcol_def = " ( ROUND( 1 , NULLIF( f4 , 1 AND f1 ) ) ) ";
SET @create = CONCAT(@c1, @virtcol_def, ")");

PREPARE cs FROM @create;
EXECUTE cs;

INSERT INTO `X` (f1, f4) VALUES (7,'0');
INSERT INTO `X` (f1, f4) VALUES (7,'0');

SET @s1 = CONCAT("SELECT f1, f4, CAST( ",@virtcol_def," AS SIGNED) AS c1 , v4 FROM X");
PREPARE st1 FROM @s1;
EXECUTE st1;

The result set is as follows:

f1 f4 c1 v4
7 0 1 NULL
7 0 NULL NULL

The first two columns show that the underlying rows of the table are the same and there is no reason for the expression to return NULL or to return a different result for each row. However, the expression does return NULL when used in a virtual column and it does return a different result for each row when used as a standard expression in the SELECT list.

[Philip Stoev (pstoev-askmonty)]

Here is another example where ROUND() is broken, even wouthout enum/set:

CREATE TABLE `X` (
  `f1` double DEFAULT NULL,
  `f2` double NOT NULL DEFAULT '0',
  `f3` double DEFAULT '0',
  `f4` double NOT NULL DEFAULT '0',
  `v1` double AS ( ( ( f4 ) AND ( f3 ) )) VIRTUAL,
  `v2` double AS ( ( ( ( f4 >= f2 ) ) XOR ( ( f2 + f4 ) ) )) VIRTUAL,
  `v3` double AS ( ( ROUND( ( ( f2 ) XOR ( f3 ) ) , f1 ) )) VIRTUAL,
  `v4` double AS ( f4) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `X` VALUES (0,0,0,0,0,1,NULL,0);
INSERT INTO `X` VALUES (7,0,6,0,0,1,NULL,0);
INSERT INTO `X` VALUES (0,0,0,0,0,1,NULL,0);
INSERT INTO `X` VALUES (3,0,0,0,0,1,NULL,0);
INSERT INTO `X` VALUES (NULL,0,1,0,0,1,NULL,0);
INSERT INTO `X` VALUES (1,0,9,1,1,0,NULL,1);
INSERT INTO `X` VALUES (0,0,3,0,0,1,NULL,0);
INSERT INTO `X` VALUES (9,0,2,0,0,1,NULL,0);
INSERT INTO `X` VALUES (0,0,0,0,0,1,NULL,0);
INSERT INTO `X` VALUES (0,0,NULL,0,0,1,NULL,0);
INSERT INTO `X` VALUES (8,0,8,0,0,1,NULL,0);
INSERT INTO `X` VALUES (0,0,2,0,0,1,NULL,0);
INSERT INTO `X` VALUES (9,0,8,0,0,1,NULL,0);
INSERT INTO `X` VALUES (0,0,6,0,0,1,NULL,0);
INSERT INTO `X` VALUES (0,0,9,0,0,1,NULL,0);
INSERT INTO `X` VALUES (0,0,6,1,1,0,NULL,1);
INSERT INTO `X` VALUES (7,7,2,0,0,1,NULL,0);
INSERT INTO `X` VALUES (0,0,0,0,0,1,NULL,0);
INSERT INTO `X` VALUES (0,0,0,0,0,1,NULL,0);
INSERT INTO `X` VALUES (0,0,NULL,0,0,1,NULL,0);
INSERT INTO `X` VALUES (NULL,0,0,0,0,1,NULL,0);
INSERT INTO `X` VALUES (0,0,1,0,0,1,NULL,0);
INSERT INTO `X` VALUES (NULL,0,6,0,0,1,NULL,0);
INSERT INTO `X` VALUES (9,0,0,0,0,1,NULL,0);
INSERT INTO `X` VALUES (3,0,0,8,0,0,NULL,8);

SELECT v3
FROM X ;

This query returns only NULLs even though in-lining the same expression produces some integer results as well (plus one NULL that seems unwarranted, since none of the fields in that particular row are NULL):

mysql> SELECT f2, f3, f1, v3, ( ( ROUND( ( ( f2 ) XOR ( f3 ) ) , f1 ) )) FROM X;
+----+------+------+------+--------------------------------------------+
| f2 | f3 | f1 | v3 | ( ( ROUND( ( ( f2 ) XOR ( f3 ) ) , f1 ) )) |
+----+------+------+------+--------------------------------------------+
| 0 | 0 | 0 | NULL | 0 |
| 0 | 6 | 7 | NULL | 1 |
| 0 | 0 | 0 | NULL | 0 |
| 0 | 0 | 3 | NULL | 0 |
| 0 | 1 | NULL | NULL | 1 |
| 0 | 9 | 1 | NULL | NULL |
| 0 | 3 | 0 | NULL | NULL |
| 0 | 2 | 9 | NULL | NULL |
| 0 | 0 | 0 | NULL | NULL |
| 0 | NULL | 0 | NULL | NULL |
| 0 | 8 | 8 | NULL | NULL |
| 0 | 2 | 0 | NULL | NULL |
| 0 | 8 | 9 | NULL | NULL |
| 0 | 6 | 0 | NULL | NULL |
| 0 | 9 | 0 | NULL...

[Igor Babaev (igorb-seattle)]

The bug is in the code of the function Item_func_round::real_op.
The bug can be reproduced without using virtual columns either.
(See my report on bug #55423 in the MySQL bug database).