Affects Version/s: 1.1.4
Fix Version/s: None
Environment:Doctrine 1.1.4, Oracle 10.2.0.4, PHP 5.2.10
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.