Doctrine 1
  1. Doctrine 1
  2. DC-918

Causing ORA-01791 when try to sort on relation field and use limit in query to Oracle DB

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Blocker Blocker
    • Resolution: Unresolved
    • Affects Version/s: 1.2.3
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      Windows 2003 Server, Oracle 10g, Symfony 1.4.8

      Description

      Schema in yml format
      in Symfony it looks more simple, but i'm going to show relation in detail

      PrType:
        columns:   
          name:                   { type: string(255), notnull: true }                          
      
      PrTypeTranslation:
        columns:   
          id:                     { type: integer, notnull: true }
          name:                   { type: string(255), notnull: true }
          lang:                   { type: string(255), notnull: true }
        relations:
          PrType:                 { onDelete: CASCADE, local: id_id, foreign: id, foreignAlias: Translation }
      

      When i try to execute this code:

         $q = Doctrine_Query::create()
                  ->from('PrType tp')
                  ->leftJoin('tp.Translation t WITH t.lang = ?', 'ru')
                  ->orderBy('t.name')
                  ->limit(10);
      

      doctrine executes next statement:

      SELECT "p"."id", "p2"."name" AS "p2__name", "p2"."lang" AS "p2__lang"
      FROM "pr_type" "p"
      LEFT JOIN "pr_type_translation" "p2" ON "p"."id" = "p2"."id" AND ("p2"."lang" = :oci_b_var_1)
      WHERE "p"."id" IN (
                    SELECT a."id" FROM ( 
                                SELECT DISTINCT "p3"."id"
                                FROM "pr_type" "p3"
                                INNER JOIN "pr_type_translation" "p4" ON "p3"."id" = "p4"."id" AND ("p4"."lang" = 'ru') 
                                ORDER BY "p4"."name" ) a 
                                WHERE ROWNUM <= 10) 
      ORDER BY "p2"."name"
      

      This sql code produces next error

      ORA-01791: not a SELECTed expression
      

      Error occures, because (from ORACODE)

      There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions

        Activity

        Dmitriy created issue -
        Dmitriy made changes -
        Field Original Value New Value
        Component/s Query [ 10037 ]
        Component/s Connection [ 10021 ]

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Dmitriy
          • Votes:
            2 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated: