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.

我们是两个开发人员，都有相同的MySql DB和相同的表和值。

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

其中一个是MySql version 5.5，并且运行良好(显然)，正如其他开发人员告诉我的那样。

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

在我的MySql 5.1.44(一个基本的MAMP安装)机器上，我有以下奇怪的问题。

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

一个非常大的查询(不是我的)错误“列'xd'不能为空”。

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

注意，没有记录与somefield = 1，所以第一个选择返回空集。

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)

我们有一个SELECT with AVG()函数，它返回一个空集联合另一个选择返回某个值(1,2只是我现在举的例子中的随机值)

If I remove the AVG() the query works.
如果我删除AVG()，查询就会工作。
If I remove xd (and the 2 of 1,2 to the right) the query works.
如果我删除了xd(和右边的2和2)，查询就可以工作了。
If I remove the UNION the query works.
如果我删除联合，查询就会工作。
If I set some record with somefield = 1 the query works.
如果我用somefield = 1设置一些记录，查询就会工作。
On the other machine 5.5 the query works.
在另一台机器5.5上，查询工作。

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

MySql <= 5.1中有一个BUG，是在MySql 5.5之前修复的。我不知道细节，但我相信消息来源

2 个解决方案

#1

try using the SELECT IFNULL();

尝试使用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

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

#2

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

我建议颠倒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.

这是因为UNION中的第一个SELECT决定了resultset中列的数据类型;在您的例子中，UNION的第一列采用了questionario_punteggi的类型。xd列:即，CHAR(3)不是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:

由于在UNION的第一部分中应用了一个聚合函数，所以它会导致单个行，即使没有记录与筛选条件匹配。按组(汇总)职能编制:

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

如果没有匹配的行，则AVG()返回NULL。

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).

为隐藏的xd列取的值通常是与筛选器匹配的那些不确定地选择的记录(这就是为什么您可能不希望这样做);但是，由于在本例中没有记录匹配，服务器将返回NULL(显然不能使用NOT NULL属性进入列)。

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

通过反转UNION的顺序，列将没有not NULL属性。您可能需要适当地对列进行别名:

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.

如果我删除AVG()，查询就会工作。

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.

由于聚合不再执行，UNION中的第一个SELECT会产生一个空的recordset，因此在第一列中没有空记录。
If I remove xd (and the 2 of 1,2 to the right) the query works.

如果我删除了xd(和右边的2和2)，查询就可以工作了。

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

由于不再选择隐藏列，所以MySQL不再返回NULL。
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.

这是MySQL版本和同事版本之间的bug: NOT NULL属性不应该真正应用于UNION结果。
If I set some record with somefield = 1 the query works.

如果我用somefield = 1设置一些记录，查询就会工作。

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.

在另一台机器5.5上，查询工作。

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

这个bug(我还在搜索)一定是在各自的MySQL版本之间修复的。

#1