ZF-3140: Zend_DB_Select.__toString using MSSQL Adapter produces wrong limit statement
Description
Using __toString method of Zend_DB_Select class, it calls the limit function at Zend_Db_Adapter_Pdo_Mssql with params:
sql = SELECT "noticies".* FROM "noticies" ORDER BY "NOTICIES"."ID" ASC count = 1 offset = 0
Generates a sql statement like:
SELECT *
FROM (SELECT TOP 1 *
FROM (SELECT TOP 1 "noticies".*
FROM "noticies"
ORDER BY "NOTICIES"."ID" ASC) AS inner_tbl
ORDER BY "NOTICIES"."ID" DESC) AS outer_tbl
ORDER BY "NOTICIES"."ID" asc
Producing the error:
SQLSTATE[HY000]: General error: 10007 The column prefix 'NOTICIES' does not match with a table name or alias name used in the query. [10007] (severity 5) [(null)] File: Pdo.php, Line: 238
Comments
Posted by Wil Sinclair (wil) on 2008-04-18T13:20:28.000+0000
Please evaluate and categorize as necessary.
Posted by blas lopez (blas) on 2008-07-15T07:19:07.000+0000
The problem occurs when the limited statement has a order by. The SQL generated is like:
SELECT * ___FROM (SELECT TOP nn * __________FROM (SELECT TOP xx * FROM ORDER BY ) as INNERTBL ________ ORDER BY ) as OUTERTBL ___ORDER BY
the second order must refered to INNERTBL (no original tables) and the third one must be referenced to OUTERTBL not to the original...
Posted by Wil Sinclair (wil) on 2008-12-04T13:17:31.000+0000
Reassigning to Ralph since he's the new maintainer of Zend_Db
Posted by Ralph Schindler (ralph) on 2009-01-10T11:57:19.000+0000
What is the expected generated statement for MSSQL? Do you know offhand? What is expected in general?
Posted by blas lopez (blas) on 2009-01-11T00:28:30.000+0000
The generated statement is like: SELECT * ___FROM (SELECT TOP nn * __________FROM (SELECT TOP xx * FROM ORDER BY ) as INNERTBL ________ ORDER BY ) as OUTERTBL ___ORDER BY
and it must be something like :
SELECT * ___FROM (SELECT TOP nn * __________FROM (SELECT TOP xx * FROM ORDER BY ) as INNERTBL ________ ORDER BY ) as OUTERTBL ___ORDER BY
Suposing the original_order is table1.col1, table1.col1 ... must be INNERTBL.col1, INNERTBL.col2 and OUTERTBL.col1, OUTERTBL.col2
When I reached the error I try the proposed syntax query directly to the database and it seems to work.
Posted by Ralph Schindler (ralph) on 2009-08-21T07:08:19.000+0000
A fix has been provided in r17706, please test.