[MCOL-1535] Case when~ vs case ~ when ~ Created: 2018-07-09  Updated: 2018-07-30  Resolved: 2018-07-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.1.4
Fix Version/s: 1.1.6

Type: Bug Priority: Major
Reporter: ssauravy Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

Centos 6.8

Issue Links:
Relates
relates to MCOL-1472 where does not properly evaluate case... Closed
relates to MCOL-1472 where does not properly evaluate case... Closed
relates to MCOL-1531 ColumnStore fails to make inner join ... Closed
Sprint: 2018-14, 2018-15

 Description   

mysql>create table t2(id int, nm varchar(10)) engine=columnstore;

mysql>insert into t2 values( 1, 'AAA');
mysql>insert into t2 values( 2, 'BBB');
mysql>insert into t2 values( 3, 'CCC');
mysql>

select
id,nm,
charset(nm),
collation(nm),
charset('AAA'),
collation('AAA'),
(case when nm='AAA' then 1 else 0 end) as nmchk1,
(case nm when 'AAA' then 1 else 0 end) as nmchk2
from t2;
------------------------------------------------------------------------------+

id nm charset(nm) collation(nm) charset('AAA') collation('AAA') nmchk1 nmchk2

------------------------------------------------------------------------------+

1 AAA utf8 utf8_general_ci utf8 utf8_general_ci 1 0
2 BBB utf8 utf8_general_ci utf8 utf8_general_ci 0 0
3 CCC utf8 utf8_general_ci utf8 utf8_general_ci 0 0

------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

------>>>> The values of case when and chse ~ when are returned differently. It looks like a bug. Please confirm.



 Comments   
Comment by Roman [ 2018-07-09 ]

Quick check shows that InnoDB table returns 1s in both cases:

MariaDB [cs]> show create table t25;
 
+-------+------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                           |
 
+-------+------------------------------------------------------------------------------------------------------------------------+
 
| t25   | CREATE TABLE `t25` (
 
  `id` int(11) DEFAULT NULL,
 
  `nm` varchar(10) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 
+-------+------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [cs]> select 
    -> id,nm,
 
    -> charset(nm), 
    -> collation(nm), 
    -> charset('AAA'), 
    -> collation('AAA'),
 
    -> (case when nm='AAA' then 1 else 0 end) as nmchk1,
 
    -> (case nm when 'AAA' then 1 else 0 end) as nmchk2
 
    -> from t25;
 
+------+------+-------------+-----------------+----------------+------------------+--------+--------+
 
| id   | nm   | charset(nm) | collation(nm)   | charset('AAA') | collation('AAA') | nmchk1 | nmchk2 |
 
+------+------+-------------+-----------------+----------------+------------------+--------+--------+
 
|    1 | AAA  | utf8        | utf8_general_ci | utf8           | utf8_general_ci  |      1 |      1 |
 
|    2 | BBB  | utf8        | utf8_general_ci | utf8           | utf8_general_ci  |      0 |      0 |
 
|    3 | CCC  | utf8        | utf8_general_ci | utf8           | utf8_general_ci  |      0 |      0 |
 
+------+------+-------------+-----------------+----------------+------------------+--------+--------+
 
3 rows in set (0.01 sec)

Comment by Andrew Hutchings (Inactive) [ 2018-07-09 ]

This might be a duplicate of MCOL-1472. CASE was re-written in 1.1.4 due to MariaDB handling it differently in that release and another bug that was found. It appears this caused a few edge case issues.

Comment by David Hall (Inactive) [ 2018-07-19 ]

It's not a duplicate of MCOL-1472.

Comment by David Hall (Inactive) [ 2018-07-23 ]

The problem is that caseOperationType() was not adjusted for the new simple case parameter order in 10.2.

Comment by Daniel Lee (Inactive) [ 2018-07-25 ]

Build verified: 1.1.6-1

/root/columnstore/mariadb-columnstore-server
commit 513775738f72ec990d055a5d47e2511e3c0e34dd
Merge: 3c37210 9236098
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Wed Jul 18 09:37:17 2018 +0100

Merge pull request #123 from drrtuy/MCOL-970

MCOL-970 Slow query log now contains original query even in vtable mode

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit f9f6dc43dd15ad3f2ca2d9e515b1e44028a16183
Merge: ced7eb4 1170b4e
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Tue Jul 24 18:06:24 2018 +0100

Merge pull request #526 from mariadb-corporation/MCOL-1535

Mcol 1535

MariaDB [mytest]> create table t2(id int, nm varchar(10)) engine=columnstore;
Query OK, 0 rows affected (0.18 sec)

MariaDB [mytest]> insert into t2 values( 1, 'AAA');
Query OK, 1 row affected (0.28 sec)

MariaDB [mytest]> insert into t2 values( 2, 'BBB');
Query OK, 1 row affected (0.06 sec)

MariaDB [mytest]> insert into t2 values( 3, 'CCC');
Query OK, 1 row affected (0.05 sec)

MariaDB [mytest]>
MariaDB [mytest]> select
-> id,nm,
-> charset(nm),
-> collation(nm),
-> charset('AAA'),
-> collation('AAA'),
-> (case when nm='AAA' then 1 else 0 end) as nmchk1,
-> (case nm when 'AAA' then 1 else 0 end) as nmchk2
-> from t2;
--------------------------------------------------------------------------------+

id nm charset(nm) collation(nm) charset('AAA') collation('AAA') nmchk1 nmchk2

--------------------------------------------------------------------------------+

1 AAA latin1 latin1_swedish_ci utf8 utf8_general_ci 1 1
2 BBB latin1 latin1_swedish_ci utf8 utf8_general_ci 0 0
3 CCC latin1 latin1_swedish_ci utf8 utf8_general_ci 0 0

--------------------------------------------------------------------------------+
3 rows in set (0.56 sec)

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