42S22 SQL error: Column not found, why?

Question

This is my SQL:

SELECT `tbl`.*, 123 AS `test` FROM `tbl` GROUP BY `test`

It works when I run it directly in MySQL. But PDO says:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'test' in 'field list'

Why so? How to cure this problem? I'm using Zend_Db.

Answer 1

Remove the backticks from around test and replace with single quotes as follows:

SELECT `tbl`.*, 123 AS 'test' FROM `tbl` GROUP BY `test`

I am not certain if you will have to do the same thing around the test in your GROUP BY as well, but the initial change should take care of it.

The reason for this is that the backticks (`) signify databases, tables, and columns to the database. The PDO is more strict on this than when running directly in MySQL. Not sure on the reason for that, but that is the way it is.