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

Comparison of YEAR to '1970' is not consistent

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.4(EOL)
    • Data types, Temporal Types
    • None

    Description

      This script

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a YEAR(2));
      		 
      INSERT INTO t1 VALUES (70);
      		 
      SELECT a<=>'1970' FROM t1;
      		 
      SELECT a <=>(SELECT '1970') FROM t1;

      returns 1 for both SELECT queries. Looks good.

      Now if I rewrite the two queries slightly:

      SELECT (SELECT a FROM t1)<=>'1970';
      		 
      SELECT (SELECT a FROM t1) <=>(SELECT '1970');

      it start to return 0.

      The difference originates in this piece of the code:

      void Item_func::convert_const_compared_to_int_field(THD *thd)
      		 
      {
      		 
        DBUG_ASSERT(arg_count >= 2); // Item_func_nullif has arg_count == 3
      		 
        if (!thd->lex->is_ps_or_view_context_analysis())
      		 
        {
      		 
          int field;
      		 
          if (args[field= 0]->real_item()->type() == FIELD_ITEM ||
      		 
              args[field= 1]->real_item()->type() == FIELD_ITEM)  
          {
      		 
            Item_field *field_item= (Item_field*) (args[field]->real_item());
      		 
            if ((field_item->field_type() ==  MYSQL_TYPE_LONGLONG ||
      		 
                 field_item->field_type() ==  MYSQL_TYPE_YEAR))
      		 
              convert_const_to_int(thd, field_item, &args[!field]);
      		 
          }
      		 
        }  
      }

      Notice, it catches FIELD_ITEM, but it does not handle SUBSELECT_ITEM.

      Attachments

        Issue Links

          relates to

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-4912 Data type plugin API version 1

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-16926 CAST(COALESCE(year_field)) returns wrong value

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Description This script
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a YEAR(2));
            INSERT INTO t1 VALUES (70);
            SELECT a<=>'1970' FROM t1;
            SELECT a <=>(SELECT '1970') FROM t1;
            {code}
            returns 1 for both SELECT queries. Looks good.

            Now if I rewrite the two queries slightly:
            {code}
            SELECT (SELECT a FROM t1)<=>'1970';
            SELECT (SELECT a FROM t1) <=>(SELECT '1970');
            {code}
            it start to returns 0.
            		 This script
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a YEAR(2));
            INSERT INTO t1 VALUES (70);
            SELECT a<=>'1970' FROM t1;
            SELECT a <=>(SELECT '1970') FROM t1;
            {code}
            returns 1 for both SELECT queries. Looks good.

            Now if I rewrite the two queries slightly:
            {code}
            SELECT (SELECT a FROM t1)<=>'1970';
            SELECT (SELECT a FROM t1) <=>(SELECT '1970');
            {code}
            it start to return 0.
            bar Alexander Barkov made changes -
            Description This script
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a YEAR(2));
            INSERT INTO t1 VALUES (70);
            SELECT a<=>'1970' FROM t1;
            SELECT a <=>(SELECT '1970') FROM t1;
            {code}
            returns 1 for both SELECT queries. Looks good.

            Now if I rewrite the two queries slightly:
            {code}
            SELECT (SELECT a FROM t1)<=>'1970';
            SELECT (SELECT a FROM t1) <=>(SELECT '1970');
            {code}
            it start to return 0.
            		 This script
            {code}
            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1 (a YEAR(2));
            INSERT INTO t1 VALUES (70);
            SELECT a<=>'1970' FROM t1;
            SELECT a <=>(SELECT '1970') FROM t1;
            {code}
            returns 1 for both SELECT queries. Looks good.

            Now if I rewrite the two queries slightly:
            {code}
            SELECT (SELECT a FROM t1)<=>'1970';
            SELECT (SELECT a FROM t1) <=>(SELECT '1970');
            {code}
            it start to return 0.

            The difference originates in this piece of the code:
            {code}
            void Item_func::convert_const_compared_to_int_field(THD *thd)
            {
              DBUG_ASSERT(arg_count >= 2); // Item_func_nullif has arg_count == 3
              if (!thd->lex->is_ps_or_view_context_analysis())
              {
                int field;
                if (args[field= 0]->real_item()->type() == FIELD_ITEM ||
                    args[field= 1]->real_item()->type() == FIELD_ITEM)
                {
                  Item_field *field_item= (Item_field*) (args[field]->real_item());
                  if ((field_item->field_type() == MYSQL_TYPE_LONGLONG ||
                       field_item->field_type() == MYSQL_TYPE_YEAR))
                    convert_const_to_int(thd, field_item, &args[!field]);
                }
              }
            }
            {code}

            Notice, it catches FIELD_ITEM, but it does not handle SUBSELECT_ITEM.
            bar Alexander Barkov made changes -
            Component/s Temporal Types [ 11000 ]
            bar Alexander Barkov made changes -
            Affects Version/s 10.2 [ 14601 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.2 [ 14601 ]
            Fix Version/s 10.1 [ 16100 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.3 [ 22126 ]
            Fix Version/s 10.2 [ 14601 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Affects Version/s 10.3 [ 22126 ]
            Affects Version/s 10.4 [ 22408 ]
            bar Alexander Barkov made changes -
            Fix Version/s 10.4 [ 22408 ]
            Fix Version/s 10.3 [ 22126 ]
            bar Alexander Barkov made changes -
            Component/s Data types [ 13906 ]
            bar Alexander Barkov made changes -
            Epic Link MDEV-21071 [ 80504 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 69860 ] MariaDB v4 [ 139841 ]

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.