| Bug #44229 | COUNT() tries to coerce its expression into DOUBLE | ||
|---|---|---|---|
| Submitted: | 13 Apr 2009 2:02 | Modified: | 13 Apr 2009 21:09 |
| Reporter: | Baron Schwartz (Basic Quality Contributor) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.0.67, 5.1-bzr | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | qc | ||
[13 Apr 2009 4:16]
Valeriy Kravchuk
Thank you for the problem report. Verified with recent 5.1 from bzr also.
[13 Apr 2009 18:20]
Omer Barnir
Converting to floating point is typical MySQL behaviour. Not a bug.
[13 Apr 2009 19:05]
Baron Schwartz
The reason given for marking as "not a bug" seems wrong to me. A lot of typical behaviors are bugs. This should not be evaluated against what MySQL typically does. By that standard, everything MySQL does is not a bug. Fortunately, there IS a standard we can use to measure it: the SQL standard. According to SQL, what should that expression do? Maybe COUNT(c or null) isn't even valid SQL, for all I know.
[13 Apr 2009 21:09]
Sveta Smirnova
Yes, it seems to be not valid SQL: char converts to double.
PostgreSQL rejects it as well:
postgres=# create table t(c char(100) not null default '');
CREATE TABLE
postgres=# insert into t values('some string');
INSERT 0 1
postgres=# select count(c or null) from t;
ERROR: argument of OR must be type boolean, not type character
Before version 6.0 it works with varchar:
mysql> drop table if exists t;
Query OK, 0 rows affected (0.00 sec)
mysql> create table t(c varchar(100) not null default '');
Query OK, 0 rows affected (0.15 sec)
mysql> insert into t values('some string');
Query OK, 1 row affected (0.04 sec)
mysql> select count(c or null) from t;
+------------------+
| count(c or null) |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)
Since version 6.0 in case of varchar statement rejects too. So I re-close the report as "Not a Bug"
Description: The server tries to coerce a COUNT() expression into a DOUBLE data type when it shouldn't. This results in the warning "Truncated incorrect DOUBLE value". How to repeat: mysql> create table t(c char(100) not null default ''); Query OK, 0 rows affected (0.02 sec) mysql> insert into t values('some string'); Query OK, 1 row affected (0.00 sec) mysql> select count(c or null) from t; +------------------+ | count(c or null) | +------------------+ | 0 | +------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +---------+------+------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: 'some string ' | +---------+------+------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)