Bug #757 IFNULL cuts off result to second argument when using DISTINCT and subquery.

• Submitted: 30 Jun 2003 4:45
• Modified: 6 Jul 2003 12:39
• Reporter: Pieter Ennes
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1
• OS: Linux (Linux RedHat 9/Kernel 2.4.20)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[30 Jun 2003 4:45] Pieter Ennes

Description:
When using IFNULL() with a subquery as a first argument, the result is cut off to the length of the second argument when DISTINCT is used.

Hard to explain, but see below. I tried to strip off as much as possible, this is what is left. Running the query should reveal what i mean.

It was pretty hard to reproduce, as most of the options used are nessecary to reproduce.

Pieter.

How to repeat:
drop table if exists test1;
drop table if exists test2;
create table test1 (id int);
create table test2 (id int, name varchar(16));

insert into test1 values (1);
insert into test2 values (1,'longername'),(1,'evenlongername');

select
    ifnull(
        (select group_concat(concat(test2.id, ':', test2.name)) from test2 where test2.id=test1.id)
    ,
        'shortname'
    ) as 'without distinct: how it should be'
from
    test1;

select distinct
    ifnull(
        (select group_concat(concat(test2.id, ':', test2.name)) from test2 where test2.id=test1.id)
    ,
        'shortname'
    ) as 'with distinct: cutoff at length of shortname'
from
    test1;

Suggested fix:
N/A

[4 Jul 2003 17:05] Oleksandr Byelkin

ChangeSet 
  1.1567 03/07/05 03:03:31 [e-mail] +4 -0 
  reduced using of slow current_thd/current_lex macro 
  initialization of item_thd moved to constructor (in any case we need thd in 
constructor) 
  initialization of group_concat_max_len to constructor to avoid incorrect 
length reporting (BUG#757) 
  removed Item_func_group_concat::fix_length_and_dec() because item have its 
own fix_fields and will not have inherited items

[6 Jul 2003 12:39] Oleksandr Byelkin

Thank you for bugreport. Bug is fixed sources repository.