[MCOL-495] Make string comparison not case sensitive Created: 2017-01-10  Updated: 2021-05-18  Resolved: 2020-12-03

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.0.6.1
Fix Version/s: 5.5.1

Type: Task Priority: Critical
Reporter: SuperC Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 8
Labels: Compatibility, datatype

Issue Links:
Blocks
is blocked by MCOL-4422 Remove mariadb.h and my_sys.h depende... Closed
is blocked by MDEV-21504 Collation: Create shared library for ... Closed
Relates
relates to MCOL-4064 Make JOIN collation aware Closed
relates to MCOL-4065 DISTINCT is case sensitive Closed
relates to MCOL-4388 Equality does not respect the NOPAD c... Closed
relates to MCOL-4470 collation aware join code crashes ExeMgr Closed
relates to MCOL-4498 LIKE is not collation aware Closed
relates to MCOL-4403 Trailing spaces not behaving as InnoDB Closed
relates to MCOL-4417 Non-equality comparison operators do ... Closed
relates to MCOL-4428 WHERE char6col <='STR' OR char6col IS... Closed
relates to MCOL-4534 MariaDB collation library: improve co... Stalled
relates to MCOL-4539 WHERE short_char_column='literal' ign... Closed
relates to MCOL-4726 Wrong result of WHERE char1_col='A' Closed
Epic Link: ColumnStore Compatibility Improvements

 Description   

ColumnStore is case sensitive for string comparisons in queries.
I know that the following query

select * from table1 where field1='aaa';

can be made not case sensitive writing

select * from table1 where field1 collate utf8_general_ci='aaa';

but, is there a way to make the engine not cs by default, without changing the client code?
There should be an option for this.

 Comments   
Comment by Robert MacInnis [ 2017-01-13 ]

I know this works fine in standard MariaDB on Linux. However since it does not work with ColumnStore, it is a showstopper/blocker based on our existing code and databases. For our company this is a blocker and as a result, we are looking into other alternatives.

Comment by SuperC [ 2017-01-16 ]

That was exactly the sense of my comment: the use of this engine without need to re-writing the client code (often based on some sort of framework, like Hibernate or similar).

Comment by Robert MacInnis [ 2018-02-23 ]

Any further movement on this issue?

Comment by David Hall (Inactive) [ 2020-08-18 ]

Columnstore 1.5.4 may be case insensitive in most cases, depending on the collation chosen. There are still some areas that have not been fixed:
MCOL-4064 Joins are not case insensitive
MCOL-4065 DISTINCT is not case insensitive

Also there is an anomaly that you will find annoying. Columnstore often (based on internal evaluation) optimizes WHERE statements into JOINs, and thus the WHERE would not be case insensitive regardless of the collation. This will magically go away when MCOL-4064 is complete.

Comment by Daniel Lee (Inactive) [ 2020-12-03 ]

Duplicate of MCOL-4064

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