Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
5.5.2
-
None
-
None
Description
Test case below illustrates the issue. I create a table with a few lines with 2 nullable columns fld1 and fld2. I perform a number of tests with the <=> operator
drop table manu_test;
CREATE TABLE `manu_test` (
id tinyint unsigned NOT NULL,
fld1 tinyint default null,
fld2 tinyint default null
) ENGINE=Columnstore DEFAULT CHARSET=utf8;
INSERT INTO `manu_test`
(`id`,
`fld1`,
`fld2`)
VALUES
(1,0,0),
(2,0,1),
(3,0,null),
(4,null,0),
(5,null,1),
(6,null,null),
(7,1,0),
(8,1,1),
(9,1,null)
;
I execute the following query:
SELECT
id,
fld1,
fld2,
CASE
WHEN ((fld1 <=> 0)) THEN 1
ELSE 0
END as test1,
CASE
WHEN (ISNULL(`fld1`) || (`fld1` <=> 0)) THEN 1
ELSE 0
END as test2,
CASE
WHEN (((ISNULL(`fld1`)) || (`fld1` = 0 OR (fld1 is null and 0 is null)))) THEN 1 – a = b OR (a IS NULL AND b IS NULL)
ELSE 0
END as test3,
CASE
WHEN ((fld2 <=> 0)) THEN 1
ELSE 0
END as test4,
CASE
WHEN (ISNULL(`fld2`) || (`fld2` <=> 0)) THEN 1
ELSE 0
END as test5,
CASE
WHEN (((ISNULL(`fld2`)) || (`fld2` = 0 OR (fld1 is null and 0 is null)))) THEN 1 – a = b OR (a IS NULL AND b IS NULL)
ELSE 0
END as test6
FROM
manu_test;
I get the results attached. Clearly test5 and test6 should have the same results. And it works fine if I create an InnoDB table.
The tests on the column fld1 are all correct. But test 5 ( CASE
WHEN (ISNULL(`fld2`) || (`fld2` <=> 0)) THEN 1
ELSE 0
END) fails (returns 1 in every instance.
However if I replace <=> by its definition in the mariadb documentation (test6), it works.