[MDEV-8284] Comparison of YEAR to '1970' is not consistent Created: 2015-06-08  Updated: 2019-11-19

Status: Open
Project: MariaDB Server
Component/s: Data types, Temporal Types
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-4912 Data type plugin API version 1 Closed
relates to MDEV-16926 CAST(COALESCE(year_field)) returns wr... Closed
Epic Link: Data type cleanups

 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.
Generated at Thu Feb 08 07:25:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.