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

Comparison of YEAR to '1970' is not consistent

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • 10.4
    • 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

          Activity

            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.