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

Incorrect query results for some integer data types

Details

    • Bug
    • Status: Confirmed (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 11.8.1
    • 11.8
    • Server
    • None

    Description

      I run the following case, in which the query should return an empty result.

      CREATE TABLE t1 (c1 TINYINT);
      		 
      INSERT INTO t1 (c1) VALUES (1);
      		 
      SELECT c1 FROM t1 WHERE ((c1) NOT IN (FROM_DAYS(0))) NOT IN (1); -- actual: {1}, expected: {}

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          John Jove John Jove created issue -
          John Jove John Jove made changes -
          Field Original Value New Value
          Summary Incorrect query results for the TINYINT type Incorrect query results for some integer data types
          John Jove John Jove added a comment -

          I try other integer data types like follows, in which the `BIGINT` data type returns the expected data type.

          CREATE TABLE t1 (c1 SMALLINT);
          		 
          INSERT INTO t1 (c1) VALUES (1);
          		 
          SELECT c1 FROM t1 WHERE ((c1) NOT IN (FROM_DAYS(0))) NOT IN (1); -- actual: {1}, expected: {}


          CREATE TABLE t1 (c1 INT);
          		 
          INSERT INTO t1 (c1) VALUES (1);
          		 
          SELECT c1 FROM t1 WHERE ((c1) NOT IN (FROM_DAYS(0))) NOT IN (1); -- actual: {1}, expected: {}


          CREATE TABLE t1 (c1 BIGINT);
          		 
          INSERT INTO t1 (c1) VALUES (1);
          		 
          SELECT c1 FROM t1 WHERE ((c1) NOT IN (FROM_DAYS(0))) NOT IN (1); -- actual: {}, expected: {}

          John Jove John Jove added a comment - I try other integer data types like follows, in which the `BIGINT` data type returns the expected data type. CREATE TABLE t1 (c1 SMALLINT ); INSERT INTO t1 (c1) VALUES (1); SELECT c1 FROM t1 WHERE ((c1) NOT IN (FROM_DAYS(0))) NOT IN (1); -- actual: {1}, expected: {} CREATE TABLE t1 (c1 INT ); INSERT INTO t1 (c1) VALUES (1); SELECT c1 FROM t1 WHERE ((c1) NOT IN (FROM_DAYS(0))) NOT IN (1); -- actual: {1}, expected: {} CREATE TABLE t1 (c1 BIGINT ); INSERT INTO t1 (c1) VALUES (1); SELECT c1 FROM t1 WHERE ((c1) NOT IN (FROM_DAYS(0))) NOT IN (1); -- actual: {}, expected: {}
          serg Sergei Golubchik added a comment -

          Already incorrect result with

          SELECT 1 IN (date'0000-00-00');

          serg Sergei Golubchik added a comment - Already incorrect result with SELECT 1 IN ( date '0000-00-00' );
          serg Sergei Golubchik added a comment -

          the last example is oversimplified, it's a different bug. It fails because Item_cache_temporal::cache_value ignores null_value that was set in the failed datetime conversion, can be fixed with

          --- a/sql/item.cc
          		 
          +++ b/sql/item.cc
          		 
          @@ -10637,7 +10637,7 @@ bool Item_cache_temporal::cache_value()
          		 
               return false;
          		 
             value_cached= true;
          		 
             value= example->val_datetime_packed_result(current_thd);
          		 
          -  null_value_inside= null_value= example->null_value;
          		 
          +  null_value_inside= null_value;
          		 
             return true;
          		 
           }

          This test

          create table t1 (c1 tinyint);
          		 
          insert into t1 (c1) values (1);
          		 
          select c1 in (date'0000-00-00') from t1;
          		 
          drop table t1;

          fails differently. It's because Item_field::val_datetime_packed() invokes Field::val_datetime_packed() which cannot return NULL for failed datetime conversion. Item_field::get_date() is correct, though.

          serg Sergei Golubchik added a comment - the last example is oversimplified, it's a different bug. It fails because Item_cache_temporal::cache_value ignores null_value that was set in the failed datetime conversion, can be fixed with --- a/sql/item.cc +++ b/sql/item.cc @@ -10637,7 +10637,7 @@ bool Item_cache_temporal::cache_value() return false; value_cached= true; value= example->val_datetime_packed_result(current_thd); - null_value_inside= null_value= example->null_value; + null_value_inside= null_value; return true; } This test create table t1 (c1 tinyint); insert into t1 (c1) values (1); select c1 in ( date '0000-00-00' ) from t1; drop table t1; fails differently. It's because Item_field::val_datetime_packed() invokes Field::val_datetime_packed() which cannot return NULL for failed datetime conversion. Item_field::get_date() is correct, though.
          serg Sergei Golubchik made changes -
          Fix Version/s 11.8 [ 29921 ]
          serg Sergei Golubchik made changes -
          Assignee Alexander Barkov [ bar ]
          John Jove John Jove added a comment -

          Thanks for the quick response. Shall we change the bug status into confirmed?

          John Jove John Jove added a comment - Thanks for the quick response. Shall we change the bug status into confirmed?
          serg Sergei Golubchik made changes -
          Status Open [ 1 ] Confirmed [ 10101 ]

          People

            bar Alexander Barkov
            John Jove John Jove
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.