Bug #13607 Comment in query leads to incorrect results

• Submitted: 29 Sep 2005 13:10
• Modified: 29 Sep 2005 13:42
• Reporter: [ name withheld ] (Basic Quality Contributor)
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.13
• OS: Windows (WinXP sp2)
• CPU Architecture: Any

[29 Sep 2005 13:10] [ name withheld ]

Description:
A comment in multiline query from command line leads to incorrect result, see below, the expected result should be:

mysql> select 'one'
    -> -- , sum(2+2)
    -> , sum(1+1);
+-----+----------+
| one | sum(1+1) |
+-----+----------+
| one |     2 |
+-----+----------+

But I got what in the "how to repeat section"

How to repeat:
mysql> select 'one'
    -> -- , sum(2+2)
    -> , sum(1+1);
+-----+----------+
| one | sum(1+1) |
+-----+----------+
| one |     NULL |
+-----+----------+
1 row in set (0.00 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.0.13-rc-nt-max |
+------------------+
1 row in set (0.00 sec)

[29 Sep 2005 13:42] Valeriy Kravchuk

The problem you encountered has nothing to do with comments. Your expected results are wrong. According to the manual (http://dev.mysql.com/doc/mysql/en/group-by-functions.html):

"SUM() returns NULL if there were no matching rows."

That is what you have:

mysql> select 'one', sum(1+1);
+-----+----------+
| one | sum(1+1) |
+-----+----------+
| one | NULL     |
+-----+----------+
1 row in set (0.00 sec)

mysql> select 'one',
    -> sum(1+1);
+-----+----------+
| one | sum(1+1) |
+-----+----------+
| one | NULL     |
+-----+----------+
1 row in set (0.00 sec)

mysql> select 'one',
    -> -- sum(2+2),
    -> sum(1+1);
+-----+----------+
| one | sum(1+1) |
+-----+----------+
| one | NULL     |
+-----+----------+
1 row in set (0.01 sec)

mysql> select 'one', 1+1;
+-----+-----+
| one | 1+1 |
+-----+-----+
| one |   2 |
+-----+-----+
1 row in set (0.00 sec)

It is a documented behaviour.

[29 Sep 2005 13:58] [ name withheld ]

You are right about the comments not having a role in this, but the expected result is generally right, and my expected behaviour is what I actually get in SQLServer 2000 and PostgreSQL 8.1 beta.
Infact my query DOES find a matching row, it's the one I'm providing, and the sum() function should work on that.
You see:
select sum(2); should work on the value provided (2) just like it got it from a normal query, and not return NULL.

[29 Sep 2005 15:27] Valeriy Kravchuk

I am sorry, but this is how it works in MySQL. SELECT without FROM is not a standard statement in any case, so each vendor can decide its own way of implementation...

In Oracle, for example, there is a special table DUAL with one real row and one column, just for selecting such a simple things.