[MDEV-10306] Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery - Jira

XML

Word

Printable

There seems to be a problem with the combination of CONCAT, SUBSTR and CONVERT in subquery. Simplest test case so far that reproduces this is:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1);

INSERT INTO t1 VALUES('1234567');

SELECT CONCAT(SUBSTR(t2, 1, 3), SUBSTR(t2, 5)) c1

            , CONCAT(SUBSTR(t2,1,3),'---',SUBSTR(t2,5)) c2

   FROM (SELECT CONVERT(t USING latin1) t2 FROM t1) sub;

Expected result, produced by MariaDB 5.1.x and 5.2.x, and all tested versions of MySQL:

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

| c1     | c2        |

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

| 123567 | 123---567 |

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

MariaDB 5.3.x result:

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

| c1     | c2        |

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

| 123567 | 123456567 |

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

MariaDB 5.5.x - 10.1.x:

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

| c1     | c2        |

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

| 12356  | 123456567 |

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

relates to

MDEV-13118 Wrong results with LOWER and UPPER and subquery

MDEV-13119 Wrong results with CAST(AS CHAR) and subquery

MDEV-13120 Wrong results with MAKE_SET() and subquery

MDEV-13793 Wrong result with combination of CONCAT,SUBSTR,SHA

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.