Bug #2069 using an aggregate udf, the order by clause changes NULL to 0
Submitted: 9 Dec 2003 20:17 Modified: 10 Dec 2003 13:52
Reporter: [ name withheld ] Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: User-defined functions ( UDF ) Severity:S2 (Serious)
Version:4.0.16 OS:Linux (Linux (Gentoo))
Assigned to: CPU Architecture:Any

[9 Dec 2003 20:17] [ name withheld ]
Description:
I have set up an aggregate UDF that can return a real or NULL.  With certain ORDER BY clauses, MySQL will incorrectly return 0, when NULL should be returned.  

This query will return NULL for the second result:

SELECT vendor_id, FIRSTVAL(IF(vendor_id = 2, NULL, price), qty) FROM item_test GROUP BY vendor_id ORDER BY vendor_id

while this next one returns 0:

SELECT vendor_id, FIRSTVAL(IF(vendor_id = 2, NULL, price), qty) FROM item_test GROUP BY vendor_id ORDER BY qty

As you can see, the statements are identical except for which column is being used to sort.

How to repeat:
1. Create test table and udf.
2. Execute the above queries
[9 Dec 2003 20:19] [ name withheld ]
export of test table and data

Attachment: udf_test.sql (application/octet-stream, text), 302 bytes.

[9 Dec 2003 20:19] [ name withheld ]
my udf code

Attachment: test.cc (text/plain), 2.77 KiB.

[10 Dec 2003 13:35] [ name withheld ]
I've narrowed this down a bit.  When MySQL needs to create a temporary table to hold the result, it will substitute a 0 for the NULL.
[10 Dec 2003 13:41] [ name withheld ]
Can you close this bug?  It turns out that I had not set maybe_null like I thought, so this was just user error.
[10 Dec 2003 13:52] Miguel Solorzano
According user reports himself resolved the problem with the
client code.