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

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

注意:以下示例中惟一的区别是ORDER BY子句。

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?

为什么第二个代码块不能工作?如果我直接运行这个查询的任何一个版本(在SQL Management Studio中)，它都可以工作。如果我去掉PHP中的问号，并将值硬编码到查询中(而不是绑定它)，那也可以!这是怎么回事?

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.

更新:这里有一个示例PHP脚本，可以更好地说明这个问题:http://snipt.org/ALhd1。在这个链接的示例代码中，我包括了5个“测试”。测试#1、2和4都返回结果，而测试#3和5没有并且应该说明问题。

2 个解决方案

#1

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

我已经设法重现了PHP 5.4和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.

问题似乎在于PDO的ODBC驱动程序。成功的测试使用两个驱动程序给出相同的结果，但是下面使用test3作为示例。

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

使用Microsoft(3.0)的本地SQL Server PHP驱动程序可以得到正确的结果;

$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;

…使用ODBC运行完全相同的代码时，会给出完全失败的结果;

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

换句话说，这不是PDO本身或SQL Server中的限制，而是ODBC驱动程序中的限制/错误。

#2

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

select的附加级别应该会让人相信，在某些地方，您的值不是常量(并且仍然为它分配一个值)。

#1