Issues

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

Please evaluate and categorize as necessary.

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

Reassigning to Ralph since he's the new maintainer of Zend_Db

What is the expected generated statement for MSSQL? Do you know offhand? What is expected in general?

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.

A fix has been provided in r17706, please test.