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

Wrong result of MIN(time_expr) and MAX(time_expr) with GROUP BY

Details

    Description

      I run this script:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (i INT, d TIME);
      		 
      INSERT INTO t1 VALUES (1,'10:20:30');
      		 
      INSERT INTO t1 VALUES (1,'100:20:20');
      		 
      SELECT MIN(d), MAX(d) FROM t1;
      		 
      SELECT i, MIN(d), MAX(d) FROM t1 GROUP BY i;

      The first SELECT returns:

      +----------+-----------+
      		 
      | MIN(d)   | MAX(d)    |
      		 
      +----------+-----------+
      		 
      | 10:20:30 | 100:20:20 |
      		 
      +----------+-----------+

      Looks correct.

      The second SELECT returns:

      +------+-----------+----------+
      		 
      | i    | MIN(d)    | MAX(d)   |
      		 
      +------+-----------+----------+
      		 
      |    1 | 100:20:20 | 10:20:30 |
      		 
      +------+-----------+----------+

      Looks wrong. The MIN and MAX values swapped.

      The problem happens in this code:

      void Item_sum_min_max::update_field()
      		 
      {
      		 
      ...
      		 
          switch (Item_sum_min_max::type_handler()->cmp_type()) {
      		 
          case STRING_RESULT:
      		 
          case TIME_RESULT:
      		 
            min_max_update_str_field();

      Notice, it compares TIME values in text format.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23838 Possibly wrong result or Assertion `0' failed in Item_func_round::native_op

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23537 Comparison with temporal columns is slow in MariaDB

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23551 Performance degratation in temporal literals in 10.4

          • Major - Major loss of function.
          • Closed

          Activity

            bar Alexander Barkov added a comment -

            Benchmarks:

            TIME(0)

            # Create tables:
            		 
            # t3 with 10 records
            		 
            # t2 with 1000 records
            		 
             
            		 
            DROP TABLE IF EXISTS t3,t2;
            		 
            CREATE TABLE t3 (a INT);
            		 
            INSERT INTO t3 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
            		 
            CREATE TABLE t2 AS SELECT 1 FROM t3 t3a, t3 t3b, t3 t3c;
            		 
             
            		 
            # Create table t1 with 1 million records
            		 
            DROP TABLE IF EXISTS t1;
            		 
            CREATE TABLE t1 (id INT, a TIME) ENGINE=HEAP;
            		 
            INSERT INTO t1 SELECT 1, '10:10:10' FROM t2,t2 t2a;
            		 
            SELECT id, MAX(a) FROM t1 GROUP BY id;
            		 
             
            		 
            # My80: 0.153
            		 
            # 10.3: 0.108
            		 
            # 10.4: 0.093 (fixed)

            TIME(6)

            # Create tables:
            		 
            # t3 with 10 records
            		 
            # t2 with 1000 records
            		 
             
            		 
            DROP TABLE IF EXISTS t3,t2;
            		 
            CREATE TABLE t3 (a INT);
            		 
            INSERT INTO t3 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
            		 
            CREATE TABLE t2 AS SELECT 1 FROM t3 t3a, t3 t3b, t3 t3c;
            		 
             
            		 
            # Create table t1 with 1 million records
            		 
            DROP TABLE IF EXISTS t1;
            		 
            CREATE TABLE t1 (id INT, a TIME(6)) ENGINE=HEAP;
            		 
            INSERT INTO t1 SELECT 1, '10:10:10.999999' FROM t2,t2 t2a;
            		 
            SELECT id, MAX(a) FROM t1 GROUP BY id;
            		 
             
            		 
            # My80: 0.154
            		 
            # 10.3: 0.135
            		 
            # 10.4: 0.093 (fixed)

            bar Alexander Barkov added a comment - Benchmarks: TIME(0) # Create tables: # t3 with 10 records # t2 with 1000 records   DROP TABLE IF EXISTS t3,t2; CREATE TABLE t3 (a INT ); INSERT INTO t3 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); CREATE TABLE t2 AS SELECT 1 FROM t3 t3a, t3 t3b, t3 t3c;   # Create table t1 with 1 million records DROP TABLE IF EXISTS t1; CREATE TABLE t1 (id INT , a TIME ) ENGINE=HEAP; INSERT INTO t1 SELECT 1, '10:10:10' FROM t2,t2 t2a; SELECT id, MAX (a) FROM t1 GROUP BY id;   # My80: 0.153 # 10.3: 0.108 # 10.4: 0.093 (fixed) TIME(6) # Create tables: # t3 with 10 records # t2 with 1000 records   DROP TABLE IF EXISTS t3,t2; CREATE TABLE t3 (a INT ); INSERT INTO t3 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); CREATE TABLE t2 AS SELECT 1 FROM t3 t3a, t3 t3b, t3 t3c;   # Create table t1 with 1 million records DROP TABLE IF EXISTS t1; CREATE TABLE t1 (id INT , a TIME (6)) ENGINE=HEAP; INSERT INTO t1 SELECT 1, '10:10:10.999999' FROM t2,t2 t2a; SELECT id, MAX (a) FROM t1 GROUP BY id;   # My80: 0.154 # 10.3: 0.135 # 10.4: 0.093 (fixed)

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.