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

LP:873142 - GREATEST() does not always return same signness of argument types.

Details

    • 10.1.8-4

    Description

      Description:
      There is a problem on all platforms; to varying degrees.

      Windows 32bit and Windows 64bit:
      When supplied BIGINT UNSIGNED, the return type is a BIGINT SIGNED,

      Others:
      The return type appears to be valid however for BIGINT values, the comparison is always
      performed as if the values were signed.

      Workaround would be to use IF(x>y,x,y) function instead.

      How to repeat:
      To reproduce this bug, create this simple table.

      DROP TABLE `cma`;
      		 
      CREATE TABLE `cma` (
      		 
        `a` binary(16) NOT NULL,
      		 
        `b` bigint(20) unsigned NOT NULL,
      		 
        PRIMARY KEY (`a`)
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      		 
       
      		 
      -- Execute the following statement twice
      		 
       
      		 
      INSERT INTO cma
      		 
      (a, b)
      		 
      VALUES ('foobar',13836376518955650385)
      		 
      ON DUPLICATE KEY UPDATE
      		 
      b=GREATEST(b,VALUES(b));

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          antonytcurtis Antony T Curtis (Inactive) added a comment -

          Win32/Win64: GREATEST() does not always return same signness of argument types.
          This problem occurs on Windows 32bit and Windows 64bit.

          To reproduce this bug, create this simple table.

          DROP TABLE `cma`;
          CREATE TABLE `cma` (
          `a` binary(16) NOT NULL,
          `b` bigint(20) unsigned NOT NULL,
          PRIMARY KEY (`a`)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

          – Execute the following statement twice

          INSERT INTO cma
          (a, b)
          VALUES ('foobar',13836376518955650385)
          ON DUPLICATE KEY UPDATE
          b=GREATEST(b,VALUES(b));

          A workaround may be to use CAST(GREATEST(...) AS UNSIGNED) .

          antonytcurtis Antony T Curtis (Inactive) added a comment - Win32/Win64: GREATEST() does not always return same signness of argument types. This problem occurs on Windows 32bit and Windows 64bit. To reproduce this bug, create this simple table. DROP TABLE `cma`; CREATE TABLE `cma` ( `a` binary(16) NOT NULL, `b` bigint(20) unsigned NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; – Execute the following statement twice INSERT INTO cma (a, b) VALUES ('foobar',13836376518955650385) ON DUPLICATE KEY UPDATE b=GREATEST(b,VALUES(b)); A workaround may be to use CAST(GREATEST(...) AS UNSIGNED) .
          ratzpo Rasmus Johansson (Inactive) added a comment -

          Launchpad bug id: 873142

          ratzpo Rasmus Johansson (Inactive) added a comment - Launchpad bug id: 873142
          bar Alexander Barkov added a comment -

          A script that demonstrates the problem:

          DROP TABLE IF EXISTS t1;
          		 
          CREATE TABLE t1 (a bigint(20) unsigned NOT NULL PRIMARY KEY);
          		 
          INSERT INTO t1 (a) VALUES (13836376518955650385) ON DUPLICATE KEY UPDATE a=GREATEST(a,VALUES(a));
          		 
          INSERT INTO t1 (a) VALUES (13836376518955650385) ON DUPLICATE KEY UPDATE a=GREATEST(a,VALUES(a));
          		 
          SHOW WARNINGS;
          		 
          SELECT * FROM t1;

          returns a warning:

          +---------+------+--------------------------------------------+
          		 
          | Level   | Code | Message                                    |
          		 
          +---------+------+--------------------------------------------+
          		 
          | Warning | 1264 | Out of range value for column 'a' at row 1 |
          		 
          +---------+------+--------------------------------------------+

          and this result set:

          +---+
          		 
          | a |
          		 
          +---+
          		 
          | 0 |
          		 
          +---+

          The expected result is to produce not warnings and return 13836376518955650385.

          bar Alexander Barkov added a comment - A script that demonstrates the problem: DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a bigint(20) unsigned NOT NULL PRIMARY KEY); INSERT INTO t1 (a) VALUES (13836376518955650385) ON DUPLICATE KEY UPDATE a=GREATEST(a,VALUES(a)); INSERT INTO t1 (a) VALUES (13836376518955650385) ON DUPLICATE KEY UPDATE a=GREATEST(a,VALUES(a)); SHOW WARNINGS; SELECT * FROM t1; returns a warning: +---------+------+--------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------+ | Warning | 1264 | Out of range value for column 'a' at row 1 | +---------+------+--------------------------------------------+ and this result set: +---+ | a | +---+ | 0 | +---+ The expected result is to produce not warnings and return 13836376518955650385.
          bar Alexander Barkov added a comment -

          Repeatable in MySQL-5.7.8

          bar Alexander Barkov added a comment - Repeatable in MySQL-5.7.8

          People

            bar Alexander Barkov
            antonytcurtis Antony T Curtis (Inactive)
            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.