LIKE vs ILIKE and wrong result with like operatror

1

Hi I have a dataset in parquet format with pretty big text field.
If I understand correctly these two query should bring same result
SELECT Distinct filed1 FROM table WHERE text like ‘%sometext%’ - 1 row
SELECT Distinct filed1 FROM table WHERE ilike(text, ‘%sometext%’) - 28 rows

More over when I run aggregated query with like in the where clause I can get complitelly wrong result.

2

I have an assumption: It seems ‘LIKE’ operator doesn’t work with multi line text data.

3

Hi @dmm,

Can you give a more specific example of the query or queries that are giving you unexpected results?

You could share query profiles, for example:

4

The two queries are not exactly equivalent: LIKE is case-sensitive, whereas ILIKE is case-insensitive

5

Dremio version: 3.3.2-201908142136370993-d60145d
Query:
SELECT COUNT(*) FROM (
SELECT ‘Single line example: some test data here’ TextData
UNION ALL
SELECT ‘multiline example:
some test data here’)
WHERE TextData LIKE ‘%some test data%’

Result: 1

SELECT COUNT(*) FROM (
SELECT ‘Single line example: some test data here’ TextData
UNION ALL
SELECT ‘multiline example:
some test data here’)
WHERE ILIKE(TextData,’%some test data%’)

Result: 2
Plans attached.

103ce44a-a3af-4b4e-b28a-1425a7bd9bb8.zip (6.0 KB)
e38499ae-dfb6-426c-a407-56f369af30bc.zip (6.1 KB)

6

Thanks @dmm,

This looks like there might be a bug in the way we handle the line break. Let me test further and get back to you.

1 Like