[DDC-2441] Incorrect SQL Query being generated Created: 09/May/13  Updated: 07/Jun/13  Resolved: 07/Jun/13

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: DQL
Affects Version/s: 2.4
Fix Version/s: 2.4
Security Level: All

Type: Bug Priority: Critical
Reporter: Paul Mansell Assignee: Fabio B. Silva
Resolution: Fixed Votes: 0
Labels: None

Using Doctrine in Symfony 2.2.1 on Windows Platform


The following DQL :

SELECT s,ba,c,mno,ss,sws,ccs,cns,cws FROM WLCoreBundle:SIM s INNER JOIN s.billingAccount ba LEFT JOIN s.connection c INNER JOIN s.status ss LEFT JOIN s.workflowStatus sws INNER JOIN c.customerStatus ccs INNER JOIN c.networkStatus cns LEFT JOIN c
.workflowStatus cws INNER JOIN s.mno mno ORDER BY c.msisdn ASC

Produces the following SQL :

SELECT * FROM (SELECT c0_.id AS id0, c0_.iccid AS iccid1, c0_.created AS created2, c0_.updated AS updated3, c0_.spreference AS spreference4, c1_.id ASid5, c1_.account_number AS account_number6, c1_.name AS name7, c1_.address1 AS address18, c1_.address2 AS address29, c1_.address3 AS address310, c1_.address4 AS address411, c1_.address5 AS address512, c1_.address6 AS address613, c1_.email_address AS email_address14, c1_.spreference AS spreference15, c2_.id AS id16, c2_.msisdn AS msisdn17, c2_.local AS local18, c2_.imsi AS imsi19, c2_.data AS data20, c2_.fax AS fax21, c2_.api AS api22, c2_.activation_date AS activation_date23, c2_.contract_end_date AS contract_end_date24, c2_.created AS created25, c2_.updated AS updated26, c2_.spreference AS spreference27, c3_.id AS id28, c3_.ident AS ident29, c3_.label AS label30, c3_.description AS description31, c4_.id AS id32, c4_.ident AS ident33, c4_.label AS label34, c4_.description AS description35, c4_.customer_label AS customer_label36, c4_.customer_description AS customer_description37, c5_.id AS id38, c5_.ident AS ident39, c5_.label AS label40, c5_.description AS description41, c6_.id AS id42, c6_.ident AS ident43, c6_.label AS label44, c6_.description AS description45, c7_.id AS id46, c7_.ident AS ident47, c7_.label AS label48, c7_.description AS description49, c7_.customer_label AS customer_label50, c7_.customer_description AS customer_description51, c8_.id AS id52, c8_.name AS name53, c8_.email_address AS email_address54, c8_.is_active AS is_active55, c8_.spreference AS spreference56, c0_.billing_account AS billing_account57, c0_.customerHierarchy AS customerHierarchy58, c0_.mno AS mno59, c0_.status AS status60, c0_.workflow_status AS workflow_status61, c1_.customer_hierarchy AS customer_hierarchy62, c1_.country AS country63, c1_.tax_rate AS tax_rate64, c1_.currency AS currency65, c1_.status AS status66, c1_.priority AS priority67, c2_.sim AS sim68, c2_.customer_status AS customer_status69, c2_.network_status AS network_status70, c2_.workflow_status AS workflow_status71, ROW_NUMBER() OVER (ORDER BY msisdn17 ASC) AS doctrine_rownum FROM core_sim c0_ WITH (NOLOCK) INNER JOIN core_billing_account c1_ ON c0_.billing_account = c1_.id LEFT JOIN core_connection c2_ ON c0_.id = c2_.sim INNER JOIN core_sim_status c3_ ON c0_.status = c3_.id LEFT JOIN core_sim_workflow_status c4_ ON c0_.workflow_status = c4_.id INNER JOIN core_connection_customer_status c5_ ON c2_.customer_status = c5_.id INNER JOIN core_connection_network_status c6_ ON c2_.network_status = c6_.id LEFT JOIN core_connection_workflow_status c7_ ON c2_.workflow_status = c7_.id INNER JOIN core_mno c8_ ON c0_.mno = c8_.id) AS doctrine_tbl WHERE doctrine_rownum BETWEEN 1 AND 10

Which returns an error :

SQLSTATE[42S22]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'msisdn17'.

Same query works fine in Doctrine 2.3

Comment by Guilherme Blanco [ 05/Jun/13 ]

Which version of SQL Server are you using?

Comment by Guilherme Blanco [ 05/Jun/13 ]

Seems to be related to DDC-2470

Comment by Paul Mansell [ 05/Jun/13 ]

Im using SQL Server 2008 ... yes does seem the same / similar error

Comment by Fabio B. Silva [ 07/Jun/13 ]

Hi Paul Mansell

Could you please test this branch DDC-2470 with this fix in your environment ?


Comment by Fabio B. Silva [ 07/Jun/13 ]

Merged : https://github.com/doctrine/doctrine2/commit/753d63c2d48facdecba5d84f6ed2450024de2867

Comment by Fabio B. Silva [ 07/Jun/13 ]

Paul Mansell, please let us know if the problem continues after update

Generated at Fri Oct 09 02:46:25 EDT 2015 using JIRA 6.4.10#64025-sha1:5b8b74079161cd76a20ab66dda52747ee6701bd6.