MySQL Bugs: #4358: Problem with HAVING clause that uses alias from the select list and TEXT field

Bug #4358	Problem with HAVING clause that uses alias from the select list and TEXT field
Submitted:	1 Jul 2004 7:45	Modified:	12 Aug 2004 21:20
Reporter:	Igor Rud	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.18	OS:	Linux (Fedora Core 2)
Assigned to:	Victor Vagin	CPU Architecture:	Any

Description:
When running query, that uses left join of two tables, and in HAVING clause TEXT field and alias from select list are used, query can return empty set.

It happans when first table contain only one record and second table is empty. If first table contain two records, query works correctly.

When using VARCHAR data type instead of TEXT, query works correctly.

We noticed this for 4.0.18 version; with 4.0.12 query works correctly

How to repeat:
Dump file and query will be attached

File with dump

Attachment: dump.sql (text/plain), 434 bytes.

File with query

Attachment: query.sql (text/plain), 142 bytes.

Verified against 4.21 on Suse.

bk commit - 4.0 tree (vva:1.1893) BUG#4358

ChangeSet
  1.1893 04/08/12 20:37:31 vva@eagle.mysql.r18.ru +3 -0
  fixed Bug #4358 Problem with HAVING clause that uses alias
         from the select list and TEXT field 
  
  make setup_copy_fields to insert Item_copy_string for blobs in 
  the beginning of the copy_funcs (push_back instead of push_front)
  
  the thing is that Item_copy_string::copy for function can call 
  Item_copy_string::val_int for blob via Item_ref.
  But if Item_copy_string::copy for blob isn't called before, 
  it's value will be wrong.
  
  So all the Item_copy_string::copy for blobs should be called before 
  Item_copy_string::copy for functions.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The fix will be in mysql-4.0.21