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)
