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 -
        Hide
        Dmitriy added a comment -

        Some very similar issue were reported and resolved here http://trac.doctrine-project.org/ticket/1038.

        Show
        Dmitriy added a comment - Some very similar issue were reported and resolved here http://trac.doctrine-project.org/ticket/1038 .
        Hide
        Dmitriy added a comment - - edited

        Reason of issue was founded. It appears because i'm using oci8 driver, and this drivername not be listed in if statement on line 1401 in Doctrine/Query.php:

        LINE 1401: if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc') {
        

        I changed to:

        LINE 1401: if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'oci8' || $driverName == 'mssql' || $driverName == 'odbc') {
        

        Sorry, but i don't know how to create patch diff file.

        Show
        Dmitriy added a comment - - edited Reason of issue was founded. It appears because i'm using oci8 driver, and this drivername not be listed in if statement on line 1401 in Doctrine/Query.php : LINE 1401: if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'mssql' || $driverName == 'odbc') { I changed to: LINE 1401: if ($driverName == 'pgsql' || $driverName == 'oracle' || $driverName == 'oci' || $driverName == 'oci8' || $driverName == 'mssql' || $driverName == 'odbc') { Sorry, but i don't know how to create patch diff file.
        Dmitriy made changes -
        Field Original Value New Value
        Component/s Query [ 10037 ]
        Component/s Connection [ 10021 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DC-918, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

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

            Dates

            • Created:
              Updated: