[MCOL-1246] Trailing whitespace in CHAR/VARCHAR break string matches Created: 2018-03-07  Updated: 2020-11-12  Resolved: 2018-03-23

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

Type: Bug Priority: Major
Reporter: Andrew Hutchings (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-1403 Trailing whitespace in CHAR/VARCHAR b... Closed
relates to MCOL-4388 Equality does not respect the NOPAD c... Closed
Epic Link: ColumnStore Compatibility Improvements
Sprint: 2018-05, 2018-06

 Description   

If there is a trailing space in a char/varchar column then string matches become impossible:

MariaDB [test]> create table t1 (a int, b varchar(4)) engine=columnstore;
 
Query OK, 0 rows affected (0.34 sec)
 
 
 
MariaDB [test]> insert into t1 values (1, 'ABC ');
 
Query OK, 1 row affected (0.53 sec)
 
 
 
MariaDB [test]> select * from t1 where b = 'ABC';
 
Empty set (0.16 sec)
 
 
 
MariaDB [test]> select * from t1 where b like 'ABC';
 
Empty set (0.03 sec)
 
 
 
MariaDB [test]> select a, hex(b) from t1;
 
+------+----------+
 
| a    | hex(b)   |
 
+------+----------+
 
|    1 | 41424320 |
 
+------+----------+
 
1 row in set (0.03 sec)

Whereas with InnoDB:

MariaDB [test]> create table t2 (a int, b varchar(4));
 
Query OK, 0 rows affected (0.03 sec)
 
 
 
MariaDB [test]> insert into t2 values (1, 'ABC ');
 
Query OK, 1 row affected (0.01 sec)
 
 
 
MariaDB [test]> select * from t2 where b = 'ABC';
 
+------+------+
 
| a    | b    |
 
+------+------+
 
|    1 | ABC  |
 
+------+------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [test]> select * from t2 where b like 'ABC';
 
Empty set (0.01 sec)
 
 
 
MariaDB [test]> select a, hex(b) from t2;
 
+------+----------+
 
| a    | hex(b)   |
 
+------+----------+
 
|    1 | 41424320 |
 
+------+----------+
 
1 row in set (0.00 sec)



 Comments   
Comment by Andrew Hutchings (Inactive) [ 2018-03-07 ]

Affects partition elimination as well

Comment by Andrew Hutchings (Inactive) [ 2018-03-07 ]

Pull request open.

For QA: ColumnStore should behave like the InnoDB in the description after the patch.

Comment by Roman [ 2018-03-09 ]

Bug is fixed, however there are corresponding issues.
A text column type has the same problem. Please pay attention to rows 7 and 8.
It occurs that the CS(even with the change) doesn't process trailing tabulation as InnoDB does.

Comment by Roman [ 2018-03-09 ]

Need some attention from Andrew.

Comment by Andrew Hutchings (Inactive) [ 2018-03-09 ]

Ooo... Interesting, we do a direct BPS step for this instead of a DSS step. This could also be an optimisation issue...

Edit: nope, actually DSS doesn't work for TEXT, that is why I disabled it for TEXT columns (see commit 6128293ad for more info)

Comment by Andrew Hutchings (Inactive) [ 2018-03-09 ]

Added commit to support for BPS step so that TEXT and some other VARCHAR cases are also covered.

Comment by Andrew Hutchings (Inactive) [ 2018-03-09 ]

Added support for Roman's second case after looking at the correct SQL-92 support. Test case pull request in the regression suite.

Comment by Daniel Lee (Inactive) [ 2018-03-23 ]

Build verified: 1.1.4-1 source

/root/columnstore/mariadb-columnstore-server
commit 3b5242143b394423dbdf96d888410a3c33f9ff97
Merge: b7d93b7 23900e6
Author: benthompson15 <ben.thompson@mariadb.com>
Date: Wed Mar 7 10:39:40 2018 -0600

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

update version

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 4e167285c6665c9aa0e204eac2de0add61709be4
Merge: 819c69c b800fc7
Author: benthompson15 <ben.thompson@mariadb.com>
Date: Wed Mar 14 13:25:21 2018 -0500

Merge pull request #427 from mariadb-corporation/MCOL-1225

Mcol 1225 - fix mysql/lib library path

Reproduced issue in 1.1.3-1 and verified fix in 1.1.4-1

[root@localhost ~]# mcsmysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 21
Server version: 10.2.13-MariaDB-log Columnstore 1.1.4-1

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use mytest
Database changed
MariaDB [mytest]> create table t1 (a int, b varchar(4)) engine=columnstore;
Query OK, 0 rows affected (0.64 sec)

MariaDB [mytest]> insert into t1 values (1, 'ABC ');
Query OK, 1 row affected (0.46 sec)

MariaDB [mytest]> select * from t1 where b = 'ABC';
----------+

a b

----------+

1 ABC

----------+
1 row in set (0.14 sec)

MariaDB [mytest]> select * from t1 where b like 'ABC';
Empty set (0.03 sec)

MariaDB [mytest]> select a, hex(b) from t1;
--------------+

a hex(b)

--------------+

1 41424320

--------------+
1 row in set (0.06 sec)

MariaDB [mytest]>

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