Bug #176 Mixing aliases and aggregate functions in GROUP BY results in empty set

• Submitted: 21 Mar 2003 11:32
• Modified: 24 Mar 2003 12:57
• Reporter: Alexander Keremidarski
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: all
• OS: Any
• CPU Architecture: Any

[21 Mar 2003 11:32] Alexander Keremidarski

Description:
Mixing aggregate functions with aliases in GROUP BY clauses causes problems.

When there are aliases only or aggregate funtions only everything works as expected.

How to repeat:
#create and populate test table:
create table t (id int not null, qty int not null);
insert into t values (1,2),(1,3),(2,4),(2,5);

#query1, 0 resultlines, should be 2:
select id, sum(qty) as sqty, count(qty) as cqty from t     group by id having sum(qty)>2 and cqty>1;

select id, sum(qty) as sqty from t 
group by id having sqty>2 and count(qty)>1;

#query2, 2 resultlines, correct:
select id, sum(qty) as sqty, count(qty) as cqty from t     group by id having sqty>2 and cqty>1;

#query3, 2 resultlines, correct:
select id, sum(qty) as sqty, count(qty) as cqty from t     group by id having sum(qty)>2 and count(qty)>1;

[22 Mar 2003 11:59] MySQL Verification Team

===== sql/item_func.cc 1.90 vs edited =====
*** /tmp/item_func.cc-1.90-27748        Wed Mar 19 17:46:31 2003
--- edited/sql/item_func.cc     Sat Mar 22 21:38:53 2003
***************
*** 70,84 ****
    {                                           // Print purify happy
      for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
      {
!       if ((*arg)->fix_fields(thd,tables))
        return 1;                               /* purecov: inspected */
!       if ((*arg)->maybe_null)
        maybe_null=1;
!       if ((*arg)->binary)
        binary=1;
!       with_sum_func= with_sum_func || (*arg)->with_sum_func;
!       used_tables_cache|=(*arg)->used_tables();
!       const_item_cache&= (*arg)->const_item();
      }
    }
    fix_length_and_dec();
--- 70,87 ----
    {                                           // Print purify happy
      for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
      {
!       Item *item=*arg;
!       if (item->fix_fields(thd,tables))
        return 1;                               /* purecov: inspected */
!       if (item->type() == Item::REF_ITEM)
!       item=*((Item_ref *)item)->ref;
!       if (item->maybe_null)
        maybe_null=1;
!       if (item->binary)
        binary=1;
!       with_sum_func= with_sum_func || item->with_sum_func;
!       used_tables_cache|=item->used_tables();
!       const_item_cache&= item->const_item();
      }
    }
    fix_length_and_dec();

[24 Mar 2003 12:57] MySQL Verification Team

Closed with bug fix corrected by Monty.

Fix will come in 4.0.13