[MDEV-8284] Comparison of YEAR to '1970' is not consistent - Jira

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.4(EOL)
Component/s: Data types, Temporal Types
Labels:
None

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.

relates to

MDEV-4912 Data type plugin API version 1

MDEV-16926 CAST(COALESCE(year_field)) returns wrong value

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

MariaDB Server