[MDEV-6971] Bad results with joins comparing TIME and DOUBLE/DECIMAL columns - Jira

XML

Word

Printable

DROP TABLE IF EXISTS t1,t2;

CREATE TABLE t1 (a TIME(6) PRIMARY KEY);

INSERT INTO t1 VALUES ('10:20:30');

DROP TABLE IF EXISTS t2;

CREATE TABLE t2 (a DOUBLE);

INSERT INTO t2 VALUES (102030),(102030.000000001);

SELECT t1.* FROM t1 JOIN t2 USING(a);

SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);

Both SELECT queries correctly return 2 rows.

If I add a primary key on t2 and return the same queries:

ALTER TABLE t2 ADD PRIMARY KEY(a);

SELECT t1.* FROM t1 JOIN t2 USING(a);

SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);

both SELECT queries return only one row.
This is wrong. Two rows are expected for both queries.

The same problem is repeatable if I change t2.a from DOUBLE to DECIMAL(30,10).

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