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

    XMLWordPrintable

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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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