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) .
          antonytcurtis Antony T Curtis (Inactive) created issue -
          ratzpo Rasmus Johansson (Inactive) added a comment -

          Launchpad bug id: 873142

          ratzpo Rasmus Johansson (Inactive) added a comment - Launchpad bug id: 873142
          ratzpo Rasmus Johansson (Inactive) made changes -
          Field Original Value New Value
          Labels Launchpad
          ratzpo Rasmus Johansson (Inactive) made changes -
          Key IMT-7447 MDEV-657
          Project ImportTest [ 10200 ] MariaDB Development [ 10000 ]
          Workflow jira [ 21096 ] defaullt [ 21501 ]
          serg Sergei Golubchik made changes -
          Priority Minor [ 4 ]
          elenst Elena Stepanova made changes -
          Fix Version/s 10.0.11 [ 15200 ]
          Affects Version/s 5.1.67 [ 12100 ]
          Affects Version/s 5.2.14 [ 12101 ]
          Affects Version/s 5.3.12 [ 12000 ]
          Affects Version/s 5.5.36 [ 14600 ]
          Affects Version/s 10.0.9 [ 14400 ]
          Assignee Alexander Barkov [ bar ]
          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));           		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.

          {code:sql}
          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));
          {code}
          Labels Launchpad Launchpad upstream
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0.12 [ 15201 ]
          Fix Version/s 10.0.11 [ 15200 ]
          serg Sergei Golubchik made changes -
          Workflow defaullt [ 21501 ] MariaDB v2 [ 44781 ]
          serg Sergei Golubchik made changes -
          Fix Version/s 10.0.13 [ 16000 ]
          Fix Version/s 10.0.12 [ 15201 ]
          ratzpo Rasmus Johansson (Inactive) made changes -
          Workflow MariaDB v2 [ 44781 ] MariaDB v3 [ 63808 ]
          bar Alexander Barkov made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          bar Alexander Barkov made changes -
          Fix Version/s 10.1 [ 16100 ]
          Fix Version/s 10.0 [ 16000 ]
          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 made changes -
          Sprint 10.1.8-4 [ 16 ]
          bar Alexander Barkov made changes -
          Rank Ranked higher
          bar Alexander Barkov added a comment -

          Repeatable in MySQL-5.7.8

          bar Alexander Barkov added a comment - Repeatable in MySQL-5.7.8
          bar Alexander Barkov made changes -
          Component/s OTHER [ 10125 ]
          Fix Version/s 10.1.8 [ 19605 ]
          Fix Version/s 10.1 [ 16100 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 63808 ] MariaDB v4 [ 145850 ]

          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.