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

MAX(timestamp(6) column) in correlated sub-query returns non-existent row data in original table

    Details

      Description

      Recreate:
      =====

      CREATE TABLE maria_bug (
        id INT NOT NULL,
        update_time TIMESTAMP(6) NOT NULL
      );
      INSERT INTO maria_bug VALUES(1, '2001-01-01 00:00:00.200000');
      INSERT INTO maria_bug VALUES(1, '2001-01-01 00:00:00.100000');
      SELECT * FROM maria_bug;
      SELECT *, (SELECT MAX(m2.update_time) FROM maria_bug m2 WHERE m1.id <> 0 ) max_update FROM maria_bug m1;
      

      Output:
      =====

      mysql> CREATE TABLE maria_bug (
          ->   id INT NOT NULL,
          ->   update_time TIMESTAMP(6) NOT NULL
          -> );
      Query OK, 0 rows affected (0.01 sec)
       
      mysql> INSERT INTO maria_bug VALUES(1, '2001-01-01 00:00:00.200000');
      NSERT INTO mQuery OK, 1 row affected (0.00 sec)
       
      mysql> INSERT INTO maria_bug VALUES(1, '2001-01-01 00:00:00.100000');
      Query OK, 1 row affected (0.00 sec)
       
      mysql> SELECT * FROM maria_bug;
      +----+----------------------------+
      | id | update_time                |
      +----+----------------------------+
      |  1 | 2001-01-01 00:00:00.200000 |
      |  1 | 2001-01-01 00:00:00.100000 |
      +----+----------------------------+
      2 rows in set (0.00 sec)
       
      mysql> SELECT *, (SELECT MAX(m2.update_time) FROM maria_bug m2 WHERE m1.id <> 0 ) max_update FROM maria_bug m1;
      +----+----------------------------+----------------------------+
      | id | update_time                | max_update                 |
      +----+----------------------------+----------------------------+
      |  1 | 2001-01-01 00:00:00.200000 | 2001-01-01 00:00:00.200000 |
      |  1 | 2001-01-01 00:00:00.100000 | 2001-01-01 00:00:00.000000 |
      +----+----------------------------+----------------------------+
      2 rows in set (0.00 sec)
       
      mysql> select version();
      +-----------------+
      | version()       |
      +-----------------+
      | 10.1.10-MariaDB |
      +-----------------+
      1 row in set (0.00 sec)
      
      

      Problem:
      =====
      MAX() result '2001-01-01 00:00:00.000000' is not in the table.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                dylan Dylan Su
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: