Scylla: Allow case-insensitive LIKE

[dekimir]

Currently, the LIKE operator is always case-sensitive. This is a feature request to allow the user to control whether LIKE ignores case.

[hartmut-co-uk]

Hi, any chance to get a solution. e.g. LOWER() into one of the next releases?
(see https://github.com/scylladb/scylla/issues/4477#issuecomment-493754616)

[dorlaor]

@dekimir wouldn't it be a no brainier to implement the case insensitivity?
CC @ultrabug

[avikivity]

@kostja what is your opinion about syntax?

col ILIKE pattern
LOWER(col) LIKE pattern
col COLLATE UTF8_GENERAL_CI LIKE pattern

COLLATE UTF8_GENERAL_CI seems very elegant.

[dekimir]

@dekimir wouldn't it be a no brainier to implement the case insensitivity?

To implement, yes. But to design it will require brains. That's why I'll let @avikivity and @kostja hammer out a design, and I'll implement it. ;)

[kostja]

• Avi Kivity [e-mail] [21/02/02 21:19]:

@kostja what is your opinion about syntax?

col ILIKE pattern
LOWER(col) LIKE pattern
col COLLATE UTF8_GENERAL_CI LIKE pattern

COLLATE UTF8_GENERAL_CI seems very elegant.

I think we should follow ANSI. The exact grammar is in the standard, but
judging by MySQL which is following ANSI here, the position of collate should
be after LIKE:

https://dev.mysql.com/doc/refman/8.0/en/charset-collate.html

In my working draft of SQL 2003/Foundation it's 10.7

--
Konstantin Osipov, Moscow, Russia

[kostja]

• Dejan Mircevski [e-mail] [21/02/02 21:39]:
  > > @dekimir wouldn't it be a no brainier to implement the case insensitivity?
  >
  > To implement, yes. But to design it will require brains.
  > That's why I'll let @avikivity and @kostja hammer out a design,
  > and I'll implement it. ;)

Collation is a property of a type in ANSI. There are rules for
collation algebra, i.e. what to do if an operator/function
collations do not match. We could begin by having a similar LIKE syntax.

--
Konstantin Osipov, Moscow, Russia

[avikivity]

Ok, let's follow MySQL.

[avikivity]

We don't need to support every possible language, UTF8_GENERAL_CI is enough to start with.

[tarzanek]

can we have an ILIKE "alias" to the default utf8 "collate ..." ? ;-) ?
(and I hope I am allowed to suggest it, having more than 2 accents in my name)

[avikivity]

ILIKE looks to be PostgresQL specific. Let's stick with standard syntax, unless there is an overriding reason not to.

[Pawel-Kr]

@dekimir Hi Dejan, do we have any update on implementing of case insensitive like? Thanks.

[dekimir]

@dekimir Hi Dejan, do we have any update on implementing of case insensitive like?

I'm afraid not yet. When there are updates, I usually put them in the issue immediately.