MySQL Bugs: #8775: Using string literal with a name of a column makes query return too few rows

Bug #8775	Using string literal with a name of a column makes query return too few rows
Submitted:	24 Feb 2005 11:15	Modified:	14 Apr 2006 13:49
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.10	OS:	Windows (windows/linux)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
These two queries gives different number of rows, but both should give 4 rows.
This query only returns one row:
SELECT 'apid', apid,
COUNT(DISTINCT(sid)), 
SUM(IFNULL(amistd,0)), 
SUM(IFNULL(amiiod,0)+IFNULL(amiiod,0)), 
COUNT(DISTINCT(un)) 
FROM a_se, arfoo 
WHERE id = sid    
AND as_type IN (2,3)   
AND ams_ts BETWEEN 1108339200 AND (1108339200+3600*24)
group by apid

but the same query, but with a different string literal gives the correct 4 rows:
SELECT 'apid_', apid,
COUNT(DISTINCT(sid)), 
SUM(IFNULL(amistd,0)), 
SUM(IFNULL(amiiod,0)+IFNULL(amiiod,0)), 
COUNT(DISTINCT(un)) 
FROM a_se, arfoo 
WHERE id = sid    
AND as_type IN (2,3)   
AND ams_ts BETWEEN 1108339200 AND (1108339200+3600*24)
group by apid

How to repeat:
$ /cygdrive/c/mysql-4.1.10-win32/bin/mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35 to server version: 4.1.10-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database foobar;
Query OK, 1 row affected (0.01 sec)

mysql> \q
Bye

klas@PC320572325321 /cygdrive/c/bugs/4408
$ /cygdrive/c/mysql-4.1.10-win32/bin/mysql foobar < initbugdb.sql

klas@PC320572325321 /cygdrive/c/bugs/4408
$ /cygdrive/c/mysql-4.1.10-win32/bin/mysql foobar < queryCorrect.sql
apid_   apid    COUNT(DISTINCT(sid))    SUM(IFNULL(amistd,0))   SUM(IFNULL(amiio
d,0)+IFNULL(amiiod,0))  COUNT(DISTINCT(un))
apid_   NULL    1       0       0       1
apid_   lh      6       803     360204  1
apid_   lp1     5       2004    3519560 2
apid_   TH_L001 1       150     640     1

klas@PC320572325321 /cygdrive/c/bugs/4408
$ /cygdrive/c/mysql-4.1.10-win32/bin/mysql foobar < queryWithWrongResult.sql
apid    apid    COUNT(DISTINCT(sid))    SUM(IFNULL(amistd,0))   SUM(IFNULL(amiio
d,0)+IFNULL(amiiod,0))  COUNT(DISTINCT(un))
apid    lh      13      2957    3880404 5

klas@PC320572325321 /cygdrive/c/bugs/4408
$ diff queryCorrect.sql queryWithWrongResult.sql
1c1
< SELECT 'apid_', apid,
---
> SELECT 'apid', apid,

klas@PC320572325321 /cygdrive/c/bugs/4408
$

Get sql scripts to populate-DB from:
http://sjostaden1.se/eklas/mysqlbugDemo.zip

sql scripts to populate DB to reproduce error

Attachment: mysqlbugDemo.zip (application/zip, text), 11.53 KiB.

not a bug in mysql-5.0.2-alpha-win

Please retest to determine platform-specificness.

I installed the linux rpms for 4.1.13 and have a similar problem. 

create table test(t1 int, t2 int);
insert into test values (1,1), (1,2);

select DISTINCT t1, t2, 'a' from test;
+------+------+---+
| t1   | t2   | a |
+------+------+---+
|    1 |    1 | a |
|    1 |    2 | a |
+------+------+---+

-- missing a row here
select DISTINCT t1, 'a', t2 from test;
+------+---+------+
| t1   | a | t2   |
+------+---+------+
|    1 | a |    1 |
+------+---+------+

not a bug in  4.1.11-Debian_1-log |

test case

Attachment: bug8775.tar.gz (application/x-gzip, text), 873 bytes.

The 2nd incident is not reproduceable anymore with current 4.1 sources
but the original issue reported is still there (tested on linux)

i have attached a testcase for both failures

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

This looks like a valid behavior taking into account the extensions to the standard SQL that MySQL implements. The query in question :

select 'lit_val_col', lit_val_col from .... group by lit_val_col

involves two components : 

1. the fact that MySQL extends the meaning of the SQL standard by allowing referencing expression names from the select list not only into ORDER BY (as per the SQL standard) by also in GROUP BY and HAVING clauses. See section 13.2.7 (http://dev.mysql.com/doc/refman/4.1/en/select.html) : 
"A select_expr can be given an alias using AS alias_name. The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses)."

2. The 'lit_val_col' select expression (string literal) in the above statement has an implicit name of "lit_val_col" (just like the column reference next to it, because according to the SQL standard the names of the select expressions that don't have an explicit name specified by an AS clause are implementation dependent). So MySQL uses it to do the grouping instead of the real column value.

As a result you get the displayed behaviour.

I've updated the SELECT page in the manual to
point out how this ambiguity can occur, and that
you should use unique column names to avoid
the ambiguity.