在SQL Server上使用PDO时，为什么不能按列别名进行排序？

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.

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.

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).