UNION of empty set with AVG function and any non-empty set

Question

this is my first question ever, so please be patient.. :)

We are two developers and both have the same MySql DB with same tables and values.

One is MySql version 5.5 and works ok (apparently) as I am told by the other developer.

On my machine with MySql 5.1.44 (a basic MAMP install) I have the following weird problem.

A very huge query (not mine) fails with error "Column 'xd' cannot be null".

Removing pieces I slimmedi it down to this:

select xd, avg(media) from questionario_punteggi where somefield = 1 union select 1,2

Note, there is no record with somefield = 1 so the first select returns an empty set

We have a SELECT with AVG() function that returns an empty set UNION another SELECT that returns something (1,2 are just random values I put now as an example)

• If I remove the AVG() the query works.
• If I remove xd (and the 2 of 1,2 to the right) the query works.
• If I remove the UNION the query works.
• If I set some record with somefield = 1 the query works.
• On the other machine 5.5 the query works.

Otherwise the error is:

1048 - Column 'xd' cannot be null

Fields are:

`xd` char(3) NOT NULL DEFAULT '001',
`media` decimal(7,4) NOT NULL DEFAULT '0.0000',
`somefield` tinyint(4) NOT NULL DEFAULT '0',

Gosh. Any help? Thanks.

UPDATE

It has been reported to me as a BUG in MySql <= 5.1 that was fixed before MySql 5.5. I don't have the details but I trust the source

Answer 1

try using the SELECT IFNULL();

SELECT IFNULL(xd,0), avg(media) 
FROM questionario_punteggi 
WHERE somefield = 1 
UNION
SELECT 1,2

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull

Answer 2

I suggest reversing the order of the queries in the UNION.

This is because the first SELECT in a UNION determines the data type of the columns in the resultset; in your case, the first column of the UNION took the type of the questionario_punteggi.xd column: that is, CHAR(3) NOT NULL.

Since you are applying an aggregate function over the first part of the UNION, it results in a single row even though no records are matched by the filter criterion. As documented under GROUP BY (Aggregate) Functions:

AVG() returns NULL if there were no matching rows.

The value taken for the hidden xd column would normally be an indeterminately chosen record from those that match the filter (which is why you probably don't want to do that anyway); however, since in this case no records match, the server instead returns NULL (which obviously cannot go into a column with the NOT NULL attribute).

By reversing the order of the UNION, the column will not have the NOT NULL attribute. You may need to alias your columns appropriately:

SELECT 1 AS xd, 2 AS avg_media
UNION
SELECT xd, AVG(media) FROM questionario_punteggi WHERE somefield = 1

Using this to explain each of your observations in turn:

• If I remove the AVG() the query works.

  Since aggregation is no longer performed, the first SELECT in the UNION yields an empty recordset and therefore no NULL record in the first column.

• If I remove xd (and the 2 of 1,2 to the right) the query works.

  Since the hidden column is no longer selected, MySQL no longer returns NULL in its place.

• If I remove the UNION the query works.

  This is the bug that was likely fixed between your version of MySQL and your colleague's: the NOT NULL attribute shouldn't really apply to the UNION result.

• If I set some record with somefield = 1 the query works.

  The value selected for the hidden column is an indeterminate (but non-NULL value, due to the column's attributes) from the matching records.

• On the other machine 5.5 the query works.

  This bug (I'm still searching for it) must have been fixed between your respective versions of MySQL.