Zend Framework

Zend_DB_Select.__toString using MSSQL Adapter produces wrong limit statement

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: N/A N/A
  • Resolution: Fixed
  • Affects Version/s: 1.5.1
  • Fix Version/s: 1.9.2
  • Component/s: Zend_Db
  • Labels:
    None

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

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Wil Sinclair added a comment -

Please evaluate and categorize as necessary.

Show
Wil Sinclair added a comment - Please evaluate and categorize as necessary.
Hide
Permalink
blas lopez added a comment -

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 <originaltables> ORDER BY <original_order>) as INNERTBL
________ ORDER BY <original_order>) as OUTERTBL
___ORDER BY <original_order>

the second order must refered to INNERTBL (no original tables) and the third one must be referenced to OUTERTBL not to the original...

Show
blas lopez added a comment - 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 <originaltables> ORDER BY <original_order>) as INNERTBL ________ ORDER BY <original_order>) as OUTERTBL ___ORDER BY <original_order> the second order must refered to INNERTBL (no original tables) and the third one must be referenced to OUTERTBL not to the original...
Hide
Permalink
Wil Sinclair added a comment -

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

Show
Wil Sinclair added a comment - Reassigning to Ralph since he's the new maintainer of Zend_Db
Hide
Permalink
Ralph Schindler added a comment -

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

Show
Ralph Schindler added a comment - What is the expected generated statement for MSSQL? Do you know offhand? What is expected in general?
Hide
Permalink
blas lopez added a comment -

The generated statement is like:
SELECT *
___FROM (SELECT TOP nn *
__________FROM (SELECT TOP xx * FROM <originaltables> ORDER BY <original_order>) as INNERTBL
________ ORDER BY <original_order>) as OUTERTBL
___ORDER BY <original_order>

and it must be something like :

SELECT *
___FROM (SELECT TOP nn *
__________FROM (SELECT TOP xx * FROM <originaltables> ORDER BY <original_order>) as INNERTBL
________ ORDER BY <outer_order>) as OUTERTBL
___ORDER BY <final_order>

Suposing the original_order is table1.col1, table1.col1 ...<outer_order> must be INNERTBL.col1, INNERTBL.col2 and <final_order> OUTERTBL.col1, OUTERTBL.col2

When I reached the error I try the proposed syntax query directly to the database and it seems to work.

Show
blas lopez added a comment - The generated statement is like: SELECT * ___FROM (SELECT TOP nn * __________FROM (SELECT TOP xx * FROM <originaltables> ORDER BY <original_order>) as INNERTBL ________ ORDER BY <original_order>) as OUTERTBL ___ORDER BY <original_order> and it must be something like : SELECT * ___FROM (SELECT TOP nn * __________FROM (SELECT TOP xx * FROM <originaltables> ORDER BY <original_order>) as INNERTBL ________ ORDER BY <outer_order>) as OUTERTBL ___ORDER BY <final_order> Suposing the original_order is table1.col1, table1.col1 ...<outer_order> must be INNERTBL.col1, INNERTBL.col2 and <final_order> OUTERTBL.col1, OUTERTBL.col2 When I reached the error I try the proposed syntax query directly to the database and it seems to work.
Hide
Permalink
Ralph Schindler added a comment -

A fix has been provided in r17706, please test.

Show
Ralph Schindler added a comment - A fix has been provided in r17706, please test.

People

Vote (1)
Watch (3)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Zend Framework. Try JIRA - bug tracking software for your team.