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

MAKETIME(-1e50,0,0) returns a wrong result

Details

    Description

      MAKETIME() returns bad results in some cases:

      SELECT
      		 
        MAKETIME(1e10,0,0),
      		 
        MAKETIME(-1e10,0,0),
      		 
        MAKETIME(1e50,0,0),
      		 
        MAKETIME(-1e50,0,0),
      		 
        MAKETIME(COALESCE(1e50),0,0),
      		 
        MAKETIME(COALESCE(-1e50),0,0)\G


      *************************** 1. row ***************************
      		 
                 MAKETIME(1e10,0,0): 838:59:59
      		 
                MAKETIME(-1e10,0,0): -838:59:59
      		 
                 MAKETIME(1e50,0,0): 838:59:59
      		 
                MAKETIME(-1e50,0,0): -00:00:00
      		 
       MAKETIME(COALESCE(1e50),0,0): -00:00:00
      		 
      MAKETIME(COALESCE(-1e50),0,0): -00:00:00
      		 
      1 row in set, 3 warnings (0.00 sec)

      Notice wrong '-00:00:00' value. The expected value is '838:59:59' or '-838:59:59' (depending on the sign).

      The problem happens for two reasons:

      • A wrong condition in Item_func_maketime::get_date() 

          if (-hour > TIME_MAX_HOUR || hour > TIME_MAX_HOUR)
        		 
            overflow= 1;

        It does not detect the value of -9223372036854775808 as overflow.

      • A wrong cast of an rint() to longlong: 

        longlong Item_func_hybrid_result_type::val_int()
        		 
        {
        		 
          ..
        		 
          case REAL_RESULT:
        		 
            return (longlong) rint(real_op());  
          ..
        		 
        }

      Item_func_hybrid_result_type::val_int is affected

      SELECT LEFT('a', COALESCE(1e30));


      +---------------------------+
      		 
      | LEFT('a', COALESCE(1e30)) |
      		 
      +---------------------------+
      		 
      |                           |
      		 
      +---------------------------+

      Field_float::val_int is also affected

      A similar problem is repeatable in this script:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a FLOAT);
      		 
      INSERT INTO t1 VALUES (1e30);
      		 
      SELECT * FROM t1;
      		 
      SELECT MAKETIME(a,0,0) FROM t1;


      +-----------------+
      		 
      | MAKETIME(a,0,0) |
      		 
      +-----------------+
      		 
      | -00:00:00       |
      		 
      +-----------------+

      A similar problem is repeatable in this script:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a FLOAT);
      		 
      INSERT INTO t1 VALUES (1e30);
      		 
      SELECT * FROM t1;
      		 
      SELECT LEFT('a',a), LEFT('a',1e30) FROM t1;


      +-------------+----------------+
      		 
      | LEFT('a',a) | LEFT('a',1e30) |
      		 
      +-------------+----------------+
      		 
      |             | a              |
      		 
      +-------------+----------------+

      The value for the first column is wrong. The length argument is treated as negative instead of big positive.

      Item_param::val_int is also affected

      A similar problem is repeatable in this script:

      PREPARE stmt FROM 'SELECT LEFT(111,?)';
      		 
      SET @a=1e30;
      		 
      EXECUTE stmt USING @a;


      +-------------+
      		 
      | LEFT(111,?) |
      		 
      +-------------+
      		 
      |             |
      		 
      +-------------+

      and in this script (in 10.3 and 10.4):

      EXECUTE IMMEDIATE 'SELECT LEFT(111,?)' USING 1e30;


      +-------------+
      		 
      | LEFT(111,?) |
      		 
      +-------------+
      		 
      |             |
      		 
      +-------------+

      The expected result is '111'. The length argument is treated as negative instead of big positive.

      Item_cache_real::val_int is also affected

      A similar problem is repeatable in this script:

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (1),(2),(3);
      		 
      SELECT LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1));


      +-----------------------------------------+
      		 
      | LEFT('a',(SELECT 1e30 FROM t1 LIMIT 1)) |
      		 
      +-----------------------------------------+
      		 
      |                                         |
      		 
      +-----------------------------------------+

      The expected result is 'a'. The length argument is treated as negative instead of big positive.

      Item_real_func::val_int is also affected

      SELECT LEFT('a',EXP(50));


      +-------------------+
      		 
      | LEFT('a',EXP(50)) |
      		 
      +-------------------+
      		 
      |                   |
      		 
      +-------------------+

      The expected result is 'a'.

      A few method in item_sum.h and item_sum.cc are also affected

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a DOUBLE);
      		 
      INSERT INTO t1 VALUES (1e30),(0);
      		 
      SELECT LEFT('a', SUM(a)) FROM t1;


      +-------------------+
      		 
      | LEFT('a', SUM(a)) |
      		 
      +-------------------+
      		 
      |                   |
      		 
      +-------------------+


      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a DOUBLE);
      		 
      INSERT INTO t1 VALUES (1e30),(0);
      		 
      SELECT LEFT('a', AVG(a)) FROM t1;


      +-------------------+
      		 
      | LEFT('a', AVG(a)) |
      		 
      +-------------------+
      		 
      |                   |
      		 
      +-------------------+

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-21977 main.func_math fails due to undefined behaviour

          • Major - Major loss of function.
          • Closed

          Activity

            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.