[MCOL-1403] Trailing whitespace in CHAR/VARCHAR break string matches - Revisited Created: 2018-05-09  Updated: 2020-08-25  Resolved: 2018-05-22

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

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

Issue Links:
Blocks
is blocked by MCOL-1673 Different trailing behaviour between ... Closed
Relates
relates to MCOL-1246 Trailing whitespace in CHAR/VARCHAR b... Closed
Epic Link: ColumnStore Compatibility Improvements
Sprint: 2018-10, 2018-11

 Description   

This is a continuation of the following bug (which was fixed in 1.1.4):

https://jira.mariadb.org/browse/MCOL-1246

However, the bug does not address some edge case comparisons.

For instance, we've identified 2 cases where this is not working as expected:

1. This returns 0 results when the 'name' is "Majestic12 " (note trailing space):

SELECT name from `wpafb` where lower(name) like 'majestic12 ';

2. This one, I'm not as sure if it is a bug or not, but was reported nonetheless. This query finds all rows in the table whether they end in space of not:

SELECT CONCAT(name,'#') from `hangar18` where name like '% ';



 Comments   
Comment by Andrew Hutchings (Inactive) [ 2018-05-10 ]

ccalender I'm assuming in this case 'name' is a VARCHAR (not a CHAR)?

Comment by Chris Calender (Inactive) [ 2018-05-10 ]

LinuxJedi Yes, exactly, this is a VARCHAR.

Comment by Andrew Hutchings (Inactive) [ 2018-05-10 ]

Test case:

InnoDB:

MariaDB [tpch1]> create table ws (a int, b char(20), c varchar(20));
 
Query OK, 0 rows affected (0.05 sec)
 
 
 
MariaDB [tpch1]> insert into ws values (1, 'Majestic12 ', 'Majestic12 ');
 
Query OK, 1 row affected (0.01 sec)
 
 
 
MariaDB [tpch1]> select * from ws where lower(b) like 'majestic12 ';
 
Empty set (0.00 sec)
 
 
 
MariaDB [tpch1]> select * from ws where lower(c) like 'majestic12 ';
 
+------+------------+-------------+
 
| a    | b          | c           |
 
+------+------------+-------------+
 
|    1 | Majestic12 | Majestic12  |
 
+------+------------+-------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [tpch1]> select concat(b, '#') from ws where b like '% ';
 
Empty set (0.00 sec)
 
 
 
MariaDB [tpch1]> select concat(c, '#') from ws where c like '% ';
 
+----------------+
 
| concat(c, '#') |
 
+----------------+
 
| Majestic12 #   |
 
+----------------+
 
1 row in set (0.00 sec)

ColumnStore:

MariaDB [tpch1]> create table ws2 (a int, b char(20), c varchar(20)) engine=columnstore;
 
Query OK, 0 rows affected (0.76 sec)
 
 
 
MariaDB [tpch1]> insert into ws2 values (1, 'Majestic12 ', 'Majestic12 ');
 
Query OK, 1 row affected (0.67 sec)
 
 
 
MariaDB [tpch1]> select * from ws2 where lower(b) like 'majestic12 ';
 
+------+------------+-------------+
 
| a    | b          | c           |
 
+------+------------+-------------+
 
|    1 | Majestic12 | Majestic12  |
 
+------+------------+-------------+
 
1 row in set (0.21 sec)
 
 
 
MariaDB [tpch1]> select * from ws2 where lower(c) like 'majestic12 ';
 
Empty set (0.04 sec)
 
 
 
MariaDB [tpch1]> select concat(b, '#') from ws2 where b like '% ';
 
+----------------+
 
| concat(b, '#') |
 
+----------------+
 
| Majestic12#    |
 
+----------------+
 
1 row in set (0.05 sec)
 
 
 
MariaDB [tpch1]> select concat(c, '#') from ws2 where c like '% ';
 
+----------------+
 
| concat(c, '#') |
 
+----------------+
 
| Majestic12 #   |
 
+----------------+
 
1 row in set (0.04 sec)

Comment by Andrew Hutchings (Inactive) [ 2018-05-10 ]

Whitespace trim on filter was added in InfiniDB as their fix for something similar to MCOL-1246. Testing a patch with their hack removed as MCOL-1246 fix should cover their case.

Comment by Andrew Hutchings (Inactive) [ 2018-05-10 ]

Regression suite passes with patch. Pull request for engine and regression suite.

For QA: See my comment with test case (same test case added to regression suite).

Comment by Daniel Lee (Inactive) [ 2018-05-22 ]

Build verified: 1.1.5-1 source

[root@localhost ~]# cat mariadb-columnstore-1.1.5-1-centos7.x86_64.bin.tar.gz.txt
/root/columnstore/mariadb-columnstore-server
commit 0c983bff02172849a174dde46b62d76aa66485f8
Merge: 6b8a674 d5e6d89
Author: benthompson15 <ben.thompson@mariadb.com>
Date: Thu Apr 26 16:16:51 2018 -0500

Merge pull request #112 from mariadb-corporation/davidhilldallas-patch-3

update to 1.1.5

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 1ea5198e0e9ecc2a8d13e6b44bf6c632f8561199
Merge: 4533116 59858aa
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Fri May 18 12:37:47 2018 +0100

Merge pull request #475 from drrtuy/MCOL-1415

MCOL-1415

Reproduced the issues in 1.1.4-1 and verified fix.
MariaDB [mytest]> create table ws2 (a int, b char(20), c varchar(20)) engine=columnstore;
Query OK, 0 rows affected (0.12 sec)

MariaDB [mytest]> insert into ws2 values (1, 'Majestic12 ', 'Majestic12 ');
Query OK, 1 row affected (0.17 sec)

MariaDB [mytest]> select * from ws2 where lower(b) like 'majestic12 ';
Empty set (0.07 sec)

MariaDB [mytest]> select * from ws2 where lower(c) like 'majestic12 ';
---------------------------

a b c

---------------------------

1 Majestic12 Majestic12

---------------------------
1 row in set (0.01 sec)

MariaDB [mytest]> select concat(b, '#') from ws2 where b like '% ';
Empty set (0.01 sec)

MariaDB [mytest]> select concat(c, '#') from ws2 where c like '% ';
----------------

concat(c, '#')

----------------

Majestic12 #

----------------
1 row in set (0.01 sec)

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