Doctrine 1
  1. Doctrine 1
  2. DC-72

DQL Auto generated column aliases cause "ORA-00972: identifier is too long" on Oracle

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.4
    • Fix Version/s: None
    • Component/s: Query
    • Labels:
      None
    • Environment:
      Doctrine 1.1.4, Oracle 10.2.0.4, PHP 5.2.10

      Description

      Hi,

      I have an existing Oracle DB that has some tables with column names that have 28-30 characters (30 characters is the maximum identifier
      length with Oracle).

      I have created a YAML schema for the tables I want to query via DQL in this database and generated the models.

      When I run a DQL that selects some of the columns whose names are 28-30 characters in length, I receive an "ORA-00972: identifier is too long" error from Oracle.

      After some investigation, I found that Doctrine_Query is auto generating aliases for these columns that are more than 30 characters in length. In a nutshell, it appears that Doctrine_Query generates the alias by taking a single character for the table alias, plus two underscores, plus the column name (for example, t__alongerthanusualtablecolumnx).

      If the original column name was 28-30 characters in length, then the additional 3 characters exceed the 30 character identifier limit. If more tables are joined in the query, you can end up with longer table aliases (t0, t1, ... t10, etc) and the problem will affect tables with column names less than 28 characters in length as well.

      I worked around this problem by patching Doctrine_Query and Doctrine_Hydrator to use the numerical column indexes from the table definition instead of the column name when auto generating aliases.

      Attached is a patch for my workaround. I doubt it's the best way to fix this, but it should help to exemplify what is happening.

      Thanks,

      Jamie

        Activity

        Hide
        Jonathan H. Wage added a comment -

        This issue has been fixed in D2.

        Show
        Jonathan H. Wage added a comment - This issue has been fixed in D2.
        Hide
        Omer added a comment -

        After patching, i can observer an unexpected behaviour of doctrine.

        $q = Doctrine_Query::create()
        ->select('locale AS l') // Giving an Alias
        ->distinct()
        ->from('Translation')

        Before Patching:
        string(70) "SELECT DISTINCT t.locale AS t__0 FROM TRANSLATION t WHERE t.cotrid = ?"
        array(2) { [0]=> array(1)

        { ["l"]=> string(5) "de_DE" }

        [1]=> array(1)

        { ["l"]=> string(5) "en_GB" }

        }

        After Patching:
        string(70) "SELECT DISTINCT t.locale AS t__0 FROM TRANSLATION t WHERE t.cotrid = ?"
        array(2) { [0]=> array(1)

        { ["trasId"]=> string(5) "de_DE" }

        [1]=> array(1)

        { ["trasId"]=> string(5) "en_GB" }

        }

        After Patching its not possible to access the value by my alias "l".

        How can we fix this!?

        Show
        Omer added a comment - After patching, i can observer an unexpected behaviour of doctrine. $q = Doctrine_Query::create() ->select('locale AS l') // Giving an Alias ->distinct() ->from('Translation') Before Patching: string(70) "SELECT DISTINCT t.locale AS t__0 FROM TRANSLATION t WHERE t.cotrid = ?" array(2) { [0] => array(1) { ["l"]=> string(5) "de_DE" } [1] => array(1) { ["l"]=> string(5) "en_GB" } } After Patching: string(70) "SELECT DISTINCT t.locale AS t__0 FROM TRANSLATION t WHERE t.cotrid = ?" array(2) { [0] => array(1) { ["trasId"]=> string(5) "de_DE" } [1] => array(1) { ["trasId"]=> string(5) "en_GB" } } After Patching its not possible to access the value by my alias "l". How can we fix this!?
        Hide
        Omer added a comment -

        We have to use Doctrine 1.1.4 or 1.1.5. I think in the above fix it making no difference using 1.1.4 or 1.1.5

        Show
        Omer added a comment - We have to use Doctrine 1.1.4 or 1.1.5. I think in the above fix it making no difference using 1.1.4 or 1.1.5

          People

          • Assignee:
            Guilherme Blanco
            Reporter:
            James Augustine
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: