[MDEV-14458] CASE statement precision Created: 2017-11-21  Updated: 2017-12-05

Status: Open
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.0, 10.1, 10.2
Fix Version/s: 10.2

Type: Bug Priority: Major
Reporter: Kieran Brahney Assignee: Vicențiu Ciorbaru
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Description:
Odd results when comparing the below query on MySQL 5.6 / 5.7 with Maria 10.0 - 10.2

How to repeat:

create table t1(a varchar(255));
 
ALTER TABLE `t1` ADD FULLTEXT KEY `a` (`a`);
 
insert into t1 values ("Install requirements");
 
 
 
select MATCH(`a`) AGAINST ("Install unimaginative" IN NATURAL LANGUAGE MODE) from `t1`;
 
select `a` from `t1` where case when true then MATCH(`a`) AGAINST ("Install unimaginative" IN NATURAL LANGUAGE MODE) end;

Actual Results:
Second SELECT query returns no results, yet on MySQL 5.6 / 5.7 it does (see: https://www.db-fiddle.com/f/tM2FxQMFrbjCThgW4oQE7X/1)

Expected Results:
Second SELECT query should return the "Install requirements" row

Additional Information:
Seems to work without the CASE statement:

select `a` from `t1` where MATCH(`a`) AGAINST ("Install unimaginative" IN NATURAL LANGUAGE MODE)

Seems to also work if you add > 0:

select `a` from `t1` where case when true then MATCH(`a`) AGAINST ("Install unimaginative" IN NATURAL LANGUAGE MODE) end > 0;



 Comments   
Comment by Elena Stepanova [ 2017-12-02 ]

I agree that the result is counter-intuitive at the first glance. However, given the existing logic with types and conversion, I don't understand why MySQL returns a result set here, it seems highly inconsistent.

The first SELECT returns a very small decimal (or rather double) value:

MySQL [test]> select MATCH(`a`) AGAINST ("Install unimaginative" IN NATURAL LANGUAGE MODE) from `t1`;
 
+-----------------------------------------------------------------------+
 
| MATCH(`a`) AGAINST ("Install unimaginative" IN NATURAL LANGUAGE MODE) |
 
+-----------------------------------------------------------------------+
 
|                                            0.000000001885928302414186 |
 
+-----------------------------------------------------------------------+
 
1 row in set (0.00 sec)

CASE has no magic, it keeps the value:

MySQL [test]> select case when true then MATCH(`a`) AGAINST ("Install unimaginative" IN NATURAL LANGUAGE MODE) end from t1;
 
+-----------------------------------------------------------------------------------------------+
 
| case when true then MATCH(`a`) AGAINST ("Install unimaginative" IN NATURAL LANGUAGE MODE) end |
 
+-----------------------------------------------------------------------------------------------+
 
|                                                                    0.000000001885928302414186 |
 
+-----------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)

So, effectively our query in question is 

select `a` from `t1` where 0.000000001885928302414186;

and it returns a result set. The table t1, its structure and fulltext indexes are not important here – where clause evaluates to FALSE for everything under 0.5:

MySQL [test]> SELECT 1 FROM DUAL WHERE 0.000000001885928302414186;
 
Empty set (0.00 sec)
 
 
 
MySQL [test]> SELECT 1 FROM DUAL WHERE 0.49;
 
Empty set (0.00 sec)
 
 
 
MySQL [test]> SELECT 1 FROM DUAL WHERE 0.5;
 
+---+
 
| 1 |
 
+---+
 
| 1 |
 
+---+
 
1 row in set (0.00 sec)

It works the same way in MySQL and MariaDB. While I haven't found an explicit statement in documentation yet, I assume it's because the value is cast to an integer. Thus, it seems only logical that the second query in the description returns an empty result.

Still, I'll assign the report to the data type expert bar to confirm whether everything works as expected.

Comment by Alexander Barkov [ 2017-12-05 ]

The problem is that the code responsible for condition calculation is very inconsistent in calling val_int() versus val_bool().

This scripts demonstrates inconsistency:

CREATE OR REPLACE TABLE t1 (a DOUBLE);
 
INSERT INTO t1 VALUES (0.1);
 
# Empty set
 
SELECT * FROM t1 WHERE 0.1;
 
SELECT * FROM t1 WHERE CASE WHEN TRUE THEN a END;
 
SELECT * FROM t1 WHERE COALESCE(a);
 
# One row
 
SELECT * FROM t1 WHERE a;
 
SELECT * FROM t1 WHERE COALESCE(a) AND a>0;
 
SELECT * FROM t1 WHERE CASE WHEN TRUE THEN a END AND a>0;

Should be checked by the optimizer team.

Generated at Thu Feb 08 08:13:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.