[MCOL-657] Support NULL safe equals (<=>) Created: 2017-04-06  Updated: 2018-02-15  Resolved: 2017-05-11

Status: Closed
Project: MariaDB ColumnStore
Component/s: MDB Plugin
Affects Version/s: 1.0.8, 1.1.0
Fix Version/s: 1.0.9, 1.1.0

Type: New Feature Priority: Major
Reporter: Andrew Hutchings (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: community

Issue Links:
Relates
relates to MCOL-1216 Null safe operator does not work on j... Closed
Sprint: 2017-9, 2017-10

 Description   

The NULL safe equals operator (<=>) gives a not supported error "Error Code: 122. IDB-1001: Function '<=>' can only be used in the outermost select or order by clause and cannot be used in conjunction with an aggregate function.".

This should be implemented. We could implement it by expanding it to:

a = b OR (a IS NULL AND b IS NULL)

As outlined in: https://mariadb.com/kb/en/mariadb/null-safe-equal/

 Comments   
Comment by David Thompson (Inactive) [ 2017-04-13 ]

This is a query pattern that tableau will generate for the mysql connector so is hard to workaround for tableau users.

Comment by Andrew Hutchings (Inactive) [ 2017-05-09 ]

Four pull requests, two for engine (develop, develop-1.0), two for regression suite (master, 1.0).

Test case:

create table if not exists MCOL657 (a int, b int) engine=columnstore;
 
insert into MCOL657 values (1,1), (2,2), (3,4), (NULL, NULL), (NULL, 2), (3, NULL);
 
select * from MCOL657 where a<=>b;

Should return:

a	b
 
1	1
 
2	2
 
NULL	NULL

Comment by Daniel Lee (Inactive) [ 2017-05-11 ]

Build verified: Github source 1.1.0

[root@localhost mariadb-columnstore-server]# git show
commit 349cae544b6bc71910267a3b3b0fa3fb57b0a587
Merge: bd13090 2ecb85c
Author: benthompson15 <ben.thompson@mariadb.com>
Date: Thu May 4 16:06:16 2017 -0500

Merge pull request #50 from mariadb-corporation/10.2-fixes

10.2 fixes

[root@localhost mariadb-columnstore-server]# cd mariadb-columnstore-engine/
[root@localhost mariadb-columnstore-engine]# git show
commit b4883e6abd18fb63c4af450679c2720cdb537dad
Merge: 85a5eaa 83331f4
Author: dhall-InfiniDB <david.hall@mariadb.com>
Date: Thu May 11 09:43:28 2017 -0500

Merge pull request #182 from mariadb-corporation/MCOL-712

MCOL-712 Support TEXT for GROUP BY

Comment by Daniel Lee (Inactive) [ 2017-05-11 ]

Build verified: 1.0.9-1

Repeat test case.

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