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

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-4284 Assertion `cmp_items[(uint)cmp_type]' fails in sql/item_cmpfunc.cc

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            dylan Dylan Su created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Description Recreate:
            =====

            {code:java}
            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;

            {code}


            Output:
            =====

            {code:java}
            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)

            {code}


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

            {code:sql}
            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;
            {code}


            Output:
            =====

            {code}
            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)

            {code}


            Problem:
            =====
            MAX() result '2001-01-01 00:00:00.000000' is not in the table.
            elenst Elena Stepanova made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            elenst Elena Stepanova added a comment -

            Thanks for the report and the test case.

            Reproducible on current and older versions of 5.5, 10.0, 10.1. Not reproducible on MySQL 5.6.

            The problem was introduced by this commit:

            commit 19fd5dcb159414ea54329b0d3fec6c6d0dc5c0d7
            		 
            Author: Sergei Golubchik <sergii@pisem.net>
            		 
            Date:   Sun Mar 17 11:41:25 2013 +0100
            		 
             
            		 
                MDEV-4284 Assertion `cmp_items[(uint)cmp_type]' fails in sql/item_cmpfunc.cc
            		 
                
                Flip the switch and create Item_cache based on the argument's cmp_type, not argument's result_type().
            		 
                Fix subselect_engine to calculate cmp_type correctly
            		 
                
                sql/item_subselect.h:
            		 
                  mdev:4284

            elenst Elena Stepanova added a comment - Thanks for the report and the test case. Reproducible on current and older versions of 5.5, 10.0, 10.1. Not reproducible on MySQL 5.6. The problem was introduced by this commit: commit 19fd5dcb159414ea54329b0d3fec6c6d0dc5c0d7 Author: Sergei Golubchik <sergii@pisem.net> Date: Sun Mar 17 11:41:25 2013 +0100   MDEV-4284 Assertion `cmp_items[(uint)cmp_type]' fails in sql/item_cmpfunc.cc Flip the switch and create Item_cache based on the argument's cmp_type, not argument's result_type(). Fix subselect_engine to calculate cmp_type correctly sql/item_subselect.h: mdev:4284
            elenst Elena Stepanova made changes -
            Component/s Temporal Types [ 11000 ]
            Fix Version/s 5.5 [ 15800 ]
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 10.1 [ 16100 ]
            Affects Version/s 5.5 [ 15800 ]
            Affects Version/s 10.0 [ 16000 ]
            Affects Version/s 10.1 [ 16100 ]
            Assignee Sergei Golubchik [ serg ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Status In Progress [ 3 ] Stalled [ 10000 ]
            serg Sergei Golubchik made changes -
            Fix Version/s 5.5.49 [ 21600 ]
            Fix Version/s 10.0.25 [ 21701 ]
            Fix Version/s 10.1.14 [ 21804 ]
            Fix Version/s 5.5 [ 15800 ]
            Fix Version/s 10.0 [ 16000 ]
            Fix Version/s 10.1 [ 16100 ]
            Resolution Fixed [ 1 ]
            Status Stalled [ 10000 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 74519 ] MariaDB v4 [ 150208 ]

            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.