Issues

ZF-3140: Zend_DB_Select.__toString using MSSQL Adapter produces wrong limit statement

Issue Type: Bug Created: 2008-04-18T01:20:26.000+0000 Last Updated: 2009-08-27T16:01:36.000+0000 Status: Resolved Fix version(s): - 1.9.2 (25/Aug/09)

Reporter: blas lopez (blas) Assignee: Ralph Schindler (ralph) Tags: - Zend_Db

Related issues: Attachments:

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:

<pre class="highlight">
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.

Have you found an issue?

See the Overview section for more details.

Copyright

© 2006-2016 by Zend, a Rogue Wave Company. Made with by awesome contributors.

This website is built using zend-expressive and it runs on PHP 7.

Contacts