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

Comparison of YEAR to '1970' is not consistent

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.4
    • Component/s: Temporal Types
    • Labels:
      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

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated: