Iurii Gerzhedovich created IGNITE-22106: -------------------------------------------
Summary: Sql. Incorrect comparison CHAR data type in our own execution algorithms Key: IGNITE-22106 URL: https://issues.apache.org/jira/browse/IGNITE-22106 Project: Ignite Issue Type: Improvement Components: sql Reporter: Iurii Gerzhedovich SQL standard says: {code:java} 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.{code} The rule works for simple cases like {code:java} SELECT 'a' = 'a' AS t1, 'a' = 'b' AS t2, 'a' = 'a ' AS t3, 'a' = ' a' AS t4;{code} But doesn't work for internal algorithms that uses comparison, for example in JOIN {code:java} 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{code} -- This message was sent by Atlassian Jira (v8.20.10#820010)