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

Incorrect operator when comparing large unsigned integers.

Details

    Description

      DROP DATABASE IF EXISTS database98;
      		 
      CREATE DATABASE database98;
      		 
      USE database98;
      		 
      CREATE TABLE t0(c0 BOOLEAN  UNIQUE NOT NULL, c1 MEDIUMINT  UNIQUE NOT NULL, c2 BOOLEAN  UNIQUE NOT NULL) engine=MyISAM;
      		 
      CREATE OR REPLACE TABLE t1(c0 INT  UNIQUE NOT NULL, c1 VARCHAR(100)  UNIQUE NOT NULL, PRIMARY KEY(c1, c0));
      		 
      INSERT INTO t0 VALUES (127885159, -441428384, 540566410);
      		 
      INSERT INTO t0 VALUES (-70694447, 228215695, -294915985);
      		 
      INSERT INTO t1 VALUES (-1668852992, 'rklt');
      		 
      INSERT INTO t1 VALUES (97930192, '');
      		 
      SELECT COUNT(*) FROM t0 WHERE ((1363502665 | -277659069) > c0);
      		 
      SELECT SUM(count) FROM (SELECT (((1363502665 | -277659069) > c0) IS TRUE) as count FROM t0) as asdf;

      The expected result is that the two query statements have the same query result, but in fact, the first query result is 1, and the second query result is 2

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          guozhentang guozhentang added a comment -

          guozhentang guozhentang added a comment -
          serg Sergei Golubchik added a comment -

          smaller test case:

          create table t1(c0 boolean unique);
          		 
          insert into t1 values (127);
          		 
          insert into t1 values (-128);
          		 
          select * from t1 where 18446744073700599371 > c0;
          		 
          select * from t1 ignore index (c0) where 18446744073700599371 > c0;
          		 
          drop table t1;

          serg Sergei Golubchik added a comment - smaller test case: create table t1(c0 boolean unique ); insert into t1 values (127); insert into t1 values (-128); select * from t1 where 18446744073700599371 > c0; select * from t1 ignore index (c0) where 18446744073700599371 > c0; drop table t1;
          Johnston Rex Johnston added a comment - - edited

          This started with commit eb483c5181ab430877c135c16224284cfc517b3d, range scan updates exposed an inconsistency.

          Johnston Rex Johnston added a comment - - edited This started with commit eb483c5181ab430877c135c16224284cfc517b3d, range scan updates exposed an inconsistency.
          bar Alexander Barkov added a comment -

          Hello Johnston,

          I have small suggestions for the patch:
          https://github.com/MariaDB/server/commit/f17a865c395a5c81401d0cd39c4c81a2de93b7bb

          This condition:

          if (op == SCALAR_CMP_LT && ((item_val > 0)
          		 
                             || (value->unsigned_flag && (ulonglong)item_val > 0 )))

          seems to be redundant from a glance.

          Shouldn't this be enough:

          if (op == SCALAR_CMP_LT && (item_val > 0 || value->unsigned_flag))

          ?

          Also, please put both queries into the test:

          select * from t1 where 18446744073700599371 > c0;
          		 
          select * from t1 ignore index (c0) where 18446744073700599371 > c0;

          to demonstrate that the result is equal with and without the index.

          bar Alexander Barkov added a comment - Hello Johnston , I have small suggestions for the patch: https://github.com/MariaDB/server/commit/f17a865c395a5c81401d0cd39c4c81a2de93b7bb This condition: if (op == SCALAR_CMP_LT && ((item_val > 0) || (value->unsigned_flag && (ulonglong)item_val > 0 ))) seems to be redundant from a glance. Shouldn't this be enough: if (op == SCALAR_CMP_LT && (item_val > 0 || value->unsigned_flag)) ? Also, please put both queries into the test: select * from t1 where 18446744073700599371 > c0; select * from t1 ignore index (c0) where 18446744073700599371 > c0; to demonstrate that the result is equal with and without the index.
          bar Alexander Barkov added a comment -

          Hello Johnston,
          The patch
          https://github.com/MariaDB/server/commit/f282d61014db1a8fbfc43bb6750116f768b9611a
          looks ok for me.

          bar Alexander Barkov added a comment - Hello Johnston , The patch https://github.com/MariaDB/server/commit/f282d61014db1a8fbfc43bb6750116f768b9611a looks ok for me.

          People

            Johnston Rex Johnston
            guozhentang guozhentang
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.