[MDEV-3647] LP:607177 - Virtual columns do not evaluate properly with set/enum and certain expressions Created: 2010-07-19  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Minor
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug607177.xml    

 Description   

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.

 Comments   
Comment by Philip Stoev (Inactive) [ 2010-07-19 ]

Re: Virtual columns do not evaluate properly with set/enum and certain expressions
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 NULL
0 6 0 NULL NULL
7 2 7 NULL NULL
0 0 0 NULL NULL
0 0 0 NULL NULL
0 NULL 0 NULL NULL
0 0 NULL NULL NULL
0 1 0 NULL NULL
0 6 NULL NULL NULL
0 0 9 NULL NULL
0 0 3 NULL NULL

----------------------------------------------------------

Comment by Igor Babaev [ 2010-07-21 ]

Re: Virtual columns do not evaluate properly with set/enum and certain expressions
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).

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 607177

Generated at Thu Feb 08 06:50:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.