[MDEV-6971] Bad results with joins comparing TIME and DOUBLE/DECIMAL columns Created: 2014-10-29  Updated: 2014-11-18  Resolved: 2014-11-18

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5.40, 10.0.14
Fix Version/s: 10.0.15

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 Description    

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).
Generated at Thu Feb 08 07:15:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.