Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6969

Bad results with joins comparing DOUBLE to BIGINT/DECIMAL columns

        Details

        • Type: Bug
        • Status: Stalled
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 5.5.40, 10.0.14
        • Fix Version/s: 10.0
        • Component/s: OTHER
        • Labels:
          None

          Description

          Run this script:

          DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (a DOUBLE PRIMARY KEY);
INSERT INTO t1 VALUES (1.8446744073709552e19);
DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (a BIGINT UNSIGNED);
INSERT INTO t2 VALUES (18446744073709551614),(18446744073709551615);
SELECT t1.* FROM t1 JOIN t2 USING(a);
SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);

          Both SELECT queries return two rows:

          +-----------------------+
| a                     |
+-----------------------+
| 1.8446744073709552e19 |
| 1.8446744073709552e19 |
+-----------------------+
2 rows in set (0.00 sec)

          This is correct, because comparison is done as DOUBLE, and the two BIGINT values are mapped into the same DOUBLE value:

          mysql> SELECT CAST(a AS DOUBLE) FROM t2;
+-----------------------+
| CAST(a AS DOUBLE)     |
+-----------------------+
| 1.8446744073709552e19 |
| 1.8446744073709552e19 |
+-----------------------+
2 rows in set (0.01 sec)

          Now add a primary key on t2 and rerun the same SELECT 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);

          Now the first query with a natural join returns 0 rows,
          and the second query with a left join return 1 row.
          This looks wrong. Both queries should return 2 rows.

          EXPLAIN for the natural join reports eq_ref join method:

          mysql> EXPLAIN SELECT t1.* FROM t1 JOIN t2 USING(a);
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
| id   | select_type | table | type   | possible_keys | key     | key_len | ref       | rows | Extra                    |
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
|    1 | SIMPLE      | t1    | index  | PRIMARY       | PRIMARY | 8       | NULL      |    1 | Using index              |
|    1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 8       | test.t1.a |    1 | Using where; Using index |
+------+-------------+-------+--------+---------------+---------+---------+-----------+------+--------------------------+
2 rows in set (0.00 sec)

          which is wrong.

          EXPLAIN for the LEFT join says that t2 is eliminated:

          mysql> EXPLAIN SELECT t1.* FROM t1 LEFT JOIN t2 USING(a);
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 8       | NULL |    1 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

          which is also wrong.

          The same wrong behaviour is repeatable if I change the data type for t2.a from BIGINT to DECIMAL(30).

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    bar Alexander Barkov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: