Why can't I ORDER BY a column alias when using PDO on SQL Server?

Question

Note: the only difference in the following examples is the ORDER BY clause.

Good code:

$sql = 'SELECT [date], ? AS [name] 
FROM [transactions] 
WHERE [category_id] = 10 
GROUP BY [date] 
ORDER BY [date] ASC';

$stmt = $db->prepare($sql);
$stmt->bindValue(1, 'Test', PDO::PARAM_STR);
$stmt->execute();
$data = $stmt->fetchAll();
//returns rows in $data

Bad code:

$sql = 'SELECT [date], ? AS [name] 
FROM [transactions] 
WHERE [category_id] = 10 
GROUP BY [date] 
ORDER BY [date] ASC, [name] ASC';

$stmt = $db->prepare($sql);
$stmt->bindValue(1, 'Test', PDO::PARAM_STR);
$stmt->execute();
$data = $stmt->fetchAll();
//returns an empty array

Why is my second block of code not working? If I run either version of this query directly (in SQL Management Studio), it works either way. And if I get rid of the question mark in PHP and hardcode the value into the query (rather than binding it), that works too! What is going on here?

Update: Here is a sample PHP script that better illustrates the problem: http://snipt.org/ALhd1. In this linked sample code, I include 5 "tests." Tests #1, 2, and 4 all return results, while tests #3 and 5 do not and should illustrate the problem.

Is the bound parameter supposed to be a column name? Because identifiers can't be prepared params in PDO. It will be interpreted as value/string. Thus the ORDER clause might reject it as not a real column name. What does the error message say? — mario, Jul 14 '13 at 22:25
"Table and Column names cannot be replaced by parameters in PDO" from [this answer](http://stackoverflow.com/questions/182287/can-php-pdo-statements-accept-the-table-name-as-parameter) — , Jul 14 '13 at 22:44
@Akam - then why does the first version work? I'm still binding the same value there, and that DOES return data. — soapergem, Jul 14 '13 at 22:47
@mario - The error message returned using `$db->errorInfo()` is the same in both cases: error "00000", which means no error. — soapergem, Jul 14 '13 at 22:49
Plus, @mario, the bound parameter isn't the column name. The column name is clearly `[name]`. The bound variable in both cases here is merely the value. So the `[name]` column returns a string value of `Test`. — soapergem, Jul 15 '13 at 01:15
And @Akam - just in case it's not clear, the SQL query I am ultimately trying to run here looks like this: `SELECT [date], 'Test' AS [name] FROM [transactions] WHERE [category_id] = 10 GROUP BY [date] ORDER BY [date] ASC, [name] ASC`. I'm not trying to substitute a column name where I have the question mark; I'm trying to substitute a string value. And like I said, this DOES work in the first version. I just have no idea why it would stop working in the second. — soapergem, Jul 16 '13 at 15:32
Will it work, if you change `[name]` to e.g. `[myName2]` ? (may be some internal bug). Also, why do you wish to necessarily have `[name] ASC` within `order by`? Being a constant it adds nothing to sort order. — i-one, Jul 18 '13 at 14:01
@i-one - changing the column name makes no difference. I'm interested because I want to know if this is a legit bug with PDO, or if I'm just doing it wrong. And because I generally want to understand PDO better. This `ORDER BY` example is actually a much watered-down query that seemed to isolate the problem, but I was writing more complicated queries with `UNION ALL` that seemed to choke on this same problem (of substituting static values as dynamic columns). Frankly, I really don't understand why this wouldn't work, so I'm starting to suspect that I'm not crazy and it's a bug with PDO. — soapergem, Jul 18 '13 at 17:03
There are several arguments against doing things this way but try changing your `ORDER BY` to `ORDER BY [date] ASC, 2 ASC`. `ORDER BY` allows for referencing column position. — Kenneth Fisher, Jul 19 '13 at 18:52
it might be a good time to state your version of sql server and php so others can try to reproduce — goat, Jul 21 '13 at 22:33
Can't repeat the problem with PHP 5.4 and 3.0 SQL server drivers, all sample queries return correct results. — Joachim Isaksson, Jul 23 '13 at 08:35
What if you add single quotes around the `?`? Wondering if the value you are passing is being treated as a column name and not a string literal... — gvee, Jul 23 '13 at 12:06
should be select col1,col2 from tbl1 group by col1,col2 order by col1, col2 order by is defaulted to asc when not specified. when you group the grouped column itself must be displayed unless you use sum/count/avg functions. — RoMEoMusTDiE, Jul 24 '13 at 03:10
@JoachimIsaksson - I had been on PHP 5.3 and SQL Server 2008 Express, but I just upgraded to PHP 5.5 and SQL Server Express 2012 this morning, and the problem still exists. So while that didn't fix it, it was about time for an upgrade anyway. — soapergem, Jul 24 '13 at 16:26
@gvee - when I add quotes around the question mark, it then doesn't do any variable substitution even though I'm telling it to. What happens then is that it returns data with a question mark as the value for that column. — soapergem, Jul 24 '13 at 16:28
@RhianA - no, you are not correct. As I already said, if I just run the query directly rather than through PHP, it works fine and returns rows. That is plenty of proof that the query syntax is valid. The problem only comes into play when I try to escape my variables in PHP using PDO. — soapergem, Jul 24 '13 at 16:30
@KennethFisher - nice try, but unfortunately that didn't work either. Same problem. — soapergem, Jul 24 '13 at 16:31
@SoaperGEM I run PHP 5.4 on Windows with 3.0 drivers and SQL Server 2012 (Developer version I think) and I get replies to all queries. Can't explain why you don't then, sorry. — Joachim Isaksson, Jul 24 '13 at 16:33

Joachim Isaksson · Accepted Answer · 2013-07-24T17:20:29.170

I've managed to reproduce the problem with PHP 5.4 and SQL Server 2012.

The problem seems to lie in the ODBC driver for PDO. The successful tests give the same result using both drivers, but the below uses test3 as a sample.

Using the native SQL Server PHP driver from Microsoft (3.0) gives the correct result;

$db = new PDO('sqlsrv:server=.\\SQLEXPRESS');

array(3) { [0]=> string(5) "00000" [1]=> NULL [2]=> NULL }
array(1) { [0]=> array(4) {
    ["date"]=> string(23) "2013-07-23 10:34:24.497"
    [0]=>      string(23) "2013-07-23 10:34:24.497"
    ["name"]=> string(4) "Test"
    [1]=>      string(4) "Test"
  }
}

...while running the exact same code using ODBC gives your exact failed result;

$db = new PDO('odbc:driver={SQL Server Native Client 11.0};server=.\SQLEXPRESS;Trusted_Connection=yes');

array(4) { [0]=>string(5) "00000" [1]=> int(0)  
           [2]=> string(24) " ((null)[0] at (null):0)" [3]=> string(0) "" }
array(0) { }

In other words, it's not a limitation in PDO itself or in SQL Server, it's a limitation/bug in the ODBC driver.

Interesting! Thanks for digging into this. I just installed the native drivers and confirmed that it does work with the native driver. So I'll just use that from now on; thanks so much! I wonder though... would I still submit a bug report to the PHP folks about this, or does someone else make that driver? — soapergem, Jul 24 '13 at 20:39

score 0 · Answer 2 · answered Jul 19 '13 at 18:39

0

I have tried to reproduce the problem, and I cannot. However, my suspicion is that the "prepare" is identifying a constant in the order by and this is an error. You can easily see an error by using an explicit constant:

select *
from information_schema.tables t
order by 'a'

This fails with the error:

Msg 408, Level 16, State 1, Line 3
A constant expression was encountered in the ORDER BY list, position 1.

That said, this works:

select *, 'a' as name
from information_schema.tables t
order by name;

Here is a suggestion on fixing the problem. Try using a subquery:

SELECT [date], (select ?) AS [name] 
FROM [transactions] 
WHERE [category_id] = 10 
GROUP BY [date] 
ORDER BY [date] ASC, [name] ASC;

The additional level of select should convince something, somewhere that your value is not a constant (and still assign it a single value).

answered Jul 19 '13 at 18:39

Gordon Linoff

1,242,037
58
646
786

Thanks for responding. The issue here is not trying to order by a constant. I wasn't trying to say `ORDER BY 'Test'`; I was saying `ORDER BY [name]`. And the point is that the query itself is valid and runs properly when I drop it into SQL Management Studio. It's just when I try using ?s to escape variables (via PDO), it stops working. I tested out using the subquery as you suggested, and that made no difference. I think perhaps I'll update the question to include some sample code that people can run directly, since it seems you're not the only one having a hard time understanding the problem. – soapergem Jul 21 '13 at 21:47
@SoaperGEM . . . I think I understand the problem. The issue appears to be in the `prepare` step, which is somehow causing SQL to recognize a constant in the `order by`. Does using `(select 'test') as name` have the same problem? – Gordon Linoff Jul 21 '13 at 21:49
yes, using the subquery as you suggested has the same problem. – soapergem Jul 21 '13 at 21:57
Also, I just updated the main question body with the following linked code snippet, which I am hoping will explain the issue better: http://snipt.org/ALhd1 – soapergem Jul 21 '13 at 21:59

Why can't I ORDER BY a column alias when using PDO on SQL Server?

2 Answers2