UNION of empty set with AVG function and any non-empty set
This is my first question, so please be patient. :)
We are two developers and both have the same MySql DB with the same tables and values.
One is MySql version 5.5 and works fine (apparently) as another developer told me.
On my machine with MySql 5.1.44 (basic MAMP installation) I am having the following weird problem.
A very large query (not mine) with the error "Column" xd "cannot be" null ".
I reduced the removal of pieces to this:
select xd, avg(media) from questionario_punteggi where somefield = 1 union select 1,2
Note that there is no entry with somefield = 1 , so the first selection returns an empty set
We have a SELECT function with an AVG () function that returns an empty UNION set of another SELECT that returns something (1,2 are just random values that I now put as an example)
- If I remove AVG () the query works.
- If I remove xd (and 2 of 1.2 to the right) the request is executed.
- If I remove the UNION the query works.
- If I set some entry with somefield = 1, the request is executed.
- On another 5.5 machine, the request is running.
Otherwise the error is:
1048 - Column 'xd' cannot be null
Fields:
`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? Thank.
UPDATE
Reported to me as a BUG in MySql <= 5.1, which was fixed before MySql 5.5. I have no data, but I trust the source
source to share
try using SELECT IFNULL();
Select IFNULL(xd,0), avg(media) f
rom questionario_punteggi
where somefield = 1
union
select 1,2
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull
source to share
I suggest changing the order of queries in UNION
.
This is due to the fact that the first SELECT
to UNION
determine the data type of the columns in the result set; in your case, the first column UNION
took the column type questionario_punteggi.xd
: CHAR(3) NOT NULL
.
Since you are applying the aggregated function over the first part UNION
, it results in one row even if no records match the filter criteria. As described in GROUP BY
(Implement) Functions :
AVG()
returnsNULL
if there were no matching rows.
The value taken for the hidden column xd
will usually be an undefined entry from among those matched by the filter (which is why you probably don't want to); however, since no records match in this case, the server returns instead NULL
(which obviously cannot hit the attribute column NOT NULL
).
By reordering UNION
, the column will have no attribute NOT NULL
. You may need to assign columns to 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 AVG () the query works.
Since the aggregation is no longer done, the first
SELECT
inUNION
gives an empty column and therefore an entryNULL
in the first column. -
If I remove xd (and 2 of 1.2 to the right) the request is executed.
Since the hidden column is no longer selected, MySQL no longer returns
NULL
to its place. -
If I remove the UNION, the request is executed.
This is a bug that was probably fixed between your MySQL version and your colleague: the attribute
NOT NULL
should not be applied to the resultUNION
. -
If I set some entry with some field = 1, the request is executed.
The value selected for the hidden column is undefined (but not the
NULL
value due to the column attributes) from the matching records. -
On another 5.5 machine, the request is running.
This bug (I'm still looking for it) should have been fixed between your respective MySQL versions.
source to share