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

Bad results with join comparing INT and VARCHAR columns

        Details

        • Type: Bug
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 10.0.14
        • Fix Version/s: 10.1
        • Component/s: None
        • Labels:
          None

          Description

          DROP TABLE IF EXISTS t1,t2;
CREATE TABLE t1 (str varchar(21) PRIMARY KEY);
CREATE TABLE t2 (num bigint unsigned);
INSERT INTO t1 VALUES ('1.8446744073709552e19');
INSERT INTO t2 VALUES (18446744073709551615), (18446744073709551614);
SELECT * FROM t1, t2 WHERE num=str;

          returns two rows:

          +-----------------------+----------------------+
| str                   | num                  |
+-----------------------+----------------------+
| 1.8446744073709552e19 | 18446744073709551615 |
| 1.8446744073709552e19 | 18446744073709551614 |
+-----------------------+----------------------+
2 rows in set (0.01 sec)

          INT and VARCHAR column are compared as double.
          There is no enough double precision to cover 20 significat digits, so both 18446744073709551615 and 18446744073709551614 are compared as equal to '1.8446744073709552e19'.

          If I add a primary key on t2 and re-run the query:

          ALTER TABLE t2 ADD PRIMARY KEY(num);
SELECT * FROM t1, t2 WHERE num=str;

          it returns only one row:

          +-----------------------+----------------------+
| str                   | num                  |
+-----------------------+----------------------+
| 1.8446744073709552e19 | 18446744073709551615 |
+-----------------------+----------------------+

          This is wrong. It should return the same row set with and without the primary key.

            Gliffy Diagrams

              Attachments

                Activity

                Hide
                Permalink
                bar Alexander Barkov added a comment -

                Sergei commented on the email list:

                I don't see how you can fix it. The correct fix would be to disable the index in the second query and compare as doubles. But I could only imagine how many applications it will break.

                Show
                bar Alexander Barkov added a comment - Sergei commented on the email list: I don't see how you can fix it. The correct fix would be to disable the index in the second query and compare as doubles. But I could only imagine how many applications it will break.

                  People

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

                    Dates

                    • Created:
                      Updated: