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 ]
• Status: Closed
• Category: MySQL Server: User-defined functions ( UDF )
• Severity: S2 (Serious)
• Version: 4.0.16
• OS: Linux (Linux (Gentoo))
• 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] MySQL Verification Team

According user reports himself resolved the problem with the
client code.