[IGNITE-22106] Sql. Incorrect comparison CHAR data type in our own execution algorithms

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: sql
• Labels: ignite-3
• Ignite Flags: Docs Required, Release Notes Required
• Assignee: Unassigned
• Reporter: Iurii Gerzhedovich
• Created: 25/Apr/24 07:22
• Updated: 25/Apr/24 07:50

Description

SQL standard says:

The comparison of two character string expressions depends on the collation used for the comparison (see
Subclause 9.15, “Collation determination”). When values of unequal length are compared, if the collation for
the comparison has the NO PAD characteristic and the shorter value is equal to some prefix of the longer value,
then the shorter value is considered less than the longer value. If the collation for the comparison has the PAD
SPACE characteristic, for the purposes of the comparison, the shorter value is effectively extended to the length
of the longer by concatenation of <space>s on the right.

The rule works for simple cases like 

SELECT 'a' = 'a' AS t1, 'a' = 'b' AS t2, 'a' = 'a ' AS t3, 'a' = ' a' AS t4;

But doesn't work for internal algorithms that uses comparison, for example in JOIN

CREATE TABLE t2(c1 CHAR(3));
INSERT INTO t2 VALUES ('123'),('2'),('1'); 

CREATE TABLE t1(c1 CHAR(5));
INSERT INTO t1 VALUES('1 '), (' 2');

SELECT t1.c1 || t2.c1 FROM t1 join t2  ON (t1.c1=t2.c1); 
-- no rows return right now
-- expected result is one row - 11

 

 

Issue Links

• Discovered while testing: IGNITE-21953 Cover SQL E021-01(Character string types. CHARACTER data type) feature by tests