Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.3
    • Fix Version/s: 2.2
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None

      Description

      Hi,

      When the query is executed the follow problem occur:
      ORA-00972:identifier is too long
      The problem occur because a column name has 30 characters and the
      Doctrine creates an alias with <column_name>+<position_in_select>
      becoming 31 characters, in Oracle the max allowed characters in the name
      is 30.
      Is there any configuration that disable this mechanism?
      How can I solve this issue?
      Thanks!
      Regards,

      Matheus Souza.

      Progress
      Resolved Sub-Tasks

      Sub-Tasks

      1.
      Fix for columns Sub-task Resolved Alexander
       
      2.
      Fix for tables Sub-task Resolved Alexander
       

        Activity

        Matheus Luis Ramos de Souza created issue -
        Hide
        Benjamin Eberlei added a comment -

        This is tricky. No way for you to reduce the column length to < 30 chars? The problem with a fix for this would be that we would need to execute a bunch of functions for every database vendor and every column alias used in every SQL statement: quite some overhead

        Show
        Benjamin Eberlei added a comment - This is tricky. No way for you to reduce the column length to < 30 chars? The problem with a fix for this would be that we would need to execute a bunch of functions for every database vendor and every column alias used in every SQL statement: quite some overhead
        Hide
        Matheus Luis Ramos de Souza added a comment -

        I get it.
        I don't know the implementation, but is there a possibilty to create a property that disable the "suffix field position" in alias, staying just <column_name>?

        Tks!

        Show
        Matheus Luis Ramos de Souza added a comment - I get it. I don't know the implementation, but is there a possibilty to create a property that disable the "suffix field position" in alias, staying just <column_name>? Tks!
        Hide
        Benjamin Eberlei added a comment -

        The following workaround will get you around this problem until i find a better solution.

        1. Create a MyOraclePlatform extends \Doctrine\DBAL\Platforms\OraclePlatform
        2. Override the "getSQLResultCasing($string)" to do:

        $val = parent::getSQLResultCasing($string);
        return substr($val, -30);
        

        This substrings every result alias to 30 chars.

        Show
        Benjamin Eberlei added a comment - The following workaround will get you around this problem until i find a better solution. 1. Create a MyOraclePlatform extends \Doctrine\DBAL\Platforms\OraclePlatform 2. Override the "getSQLResultCasing($string)" to do: $val = parent::getSQLResultCasing($string); return substr($val, -30); This substrings every result alias to 30 chars.
        Hide
        Matheus Luis Ramos de Souza added a comment -

        But how does the Doctrine will know about my implementation?
        I will have to change the Driver too?

        Thanks!

        Show
        Matheus Luis Ramos de Souza added a comment - But how does the Doctrine will know about my implementation? I will have to change the Driver too? Thanks!
        Hide
        Benjamin Eberlei added a comment -

        no, you can pass the "platform" into the DriverManager::create method as "platform" parameter for the $params AFAIK, relevant code is Doctrine\DBAL\Connection::__construct if you want to check it out.

        Show
        Benjamin Eberlei added a comment - no, you can pass the "platform" into the DriverManager::create method as "platform" parameter for the $params AFAIK, relevant code is Doctrine\DBAL\Connection::__construct if you want to check it out.
        Hide
        Matheus Luis Ramos de Souza added a comment -

        In some cases it works, but it doesn't work when the SqlWalker is invoked, I think that the problem is in walkSelectClause($selectClause) method in the else clause for this verification:

        if ($class->isInheritanceTypeSingleTable() || $class->isInheritanceTypeJoined()) {
        
        if ($class->isInheritanceTypeSingleTable() || $class->isInheritanceTypeJoined()) {
        ...
        } else {
                        // Add foreign key columns to SQL, if necessary
                        if ($addMetaColumns) {
                            $sqlTableAlias = $this->getSqlTableAlias($class->table['name'], $dqlAlias);
                            foreach ($class->associationMappings as $assoc) {
                                if ($assoc['isOwningSide'] && $assoc['type'] & ClassMetadata::TO_ONE) {
                                    foreach ($assoc['targetToSourceKeyColumns'] as $srcColumn) {
                                        $columnAlias = $this->getSqlColumnAlias($srcColumn);
                                        $sql .= ', ' . $sqlTableAlias . '.' . $srcColumn . ' AS ' . $columnAlias;
                                        $columnAlias = $this->_platform->getSQLResultCasing($columnAlias);
                                        $this->_rsm->addMetaResult($dqlAlias, $this->_platform->getSQLResultCasing($columnAlias), $srcColumn);
                                    }
                                }
                            }
                        }
                    }
                }
        

        the problem is in the follow lines, because the getSQLResultCasing has no effect in $sql variable that is returned:

        $columnAlias = $this->getSqlColumnAlias($srcColumn);
        $sql .= ', ' . $sqlTableAlias . '.' . $srcColumn . ' AS ' . $columnAlias;
        $columnAlias = $this->_platform->getSQLResultCasing($columnAlias);
        

        The correct would be this way, wouldn't be?

        $columnAlias = $this->getSqlColumnAlias($srcColumn);
        $columnAlias = $this->_platform->getSQLResultCasing($columnAlias);
        $sql .= ', ' . $sqlTableAlias . '.' . $srcColumn . ' AS ' . $columnAlias;
        
        • In the version of Doctrine that I'm using, there is no DriverManager::create method, I'm using
          DriverManager::getConnection(array $params,Configuration $config = null,EventManager $eventManager = null) instead.

        Tks!!

        Show
        Matheus Luis Ramos de Souza added a comment - In some cases it works, but it doesn't work when the SqlWalker is invoked, I think that the problem is in walkSelectClause($selectClause) method in the else clause for this verification: if ($class->isInheritanceTypeSingleTable() || $class->isInheritanceTypeJoined()) { if ($class->isInheritanceTypeSingleTable() || $class->isInheritanceTypeJoined()) { ... } else { // Add foreign key columns to SQL, if necessary if ($addMetaColumns) { $sqlTableAlias = $ this ->getSqlTableAlias($class->table['name'], $dqlAlias); foreach ($class->associationMappings as $assoc) { if ($assoc['isOwningSide'] && $assoc['type'] & ClassMetadata::TO_ONE) { foreach ($assoc['targetToSourceKeyColumns'] as $srcColumn) { $columnAlias = $ this ->getSqlColumnAlias($srcColumn); $sql .= ', ' . $sqlTableAlias . '.' . $srcColumn . ' AS ' . $columnAlias; $columnAlias = $ this ->_platform->getSQLResultCasing($columnAlias); $ this ->_rsm->addMetaResult($dqlAlias, $ this ->_platform->getSQLResultCasing($columnAlias), $srcColumn); } } } } } } the problem is in the follow lines, because the getSQLResultCasing has no effect in $sql variable that is returned: $columnAlias = $ this ->getSqlColumnAlias($srcColumn); $sql .= ', ' . $sqlTableAlias . '.' . $srcColumn . ' AS ' . $columnAlias; $columnAlias = $ this ->_platform->getSQLResultCasing($columnAlias); The correct would be this way, wouldn't be? $columnAlias = $ this ->getSqlColumnAlias($srcColumn); $columnAlias = $ this ->_platform->getSQLResultCasing($columnAlias); $sql .= ', ' . $sqlTableAlias . '.' . $srcColumn . ' AS ' . $columnAlias; In the version of Doctrine that I'm using, there is no DriverManager::create method, I'm using DriverManager::getConnection(array $params,Configuration $config = null,EventManager $eventManager = null) instead. Tks!!
        Hide
        Benjamin Eberlei added a comment -

        Narf, sorry that this doesn't work.

        This is rather unreliable if this fails here, can we trust it in other places? I think i have to work on this by hard, its just very problematic to test this, it may be easy to miss a location.

        Show
        Benjamin Eberlei added a comment - Narf, sorry that this doesn't work. This is rather unreliable if this fails here, can we trust it in other places? I think i have to work on this by hard, its just very problematic to test this, it may be easy to miss a location.
        Hide
        Benjamin Eberlei added a comment -

        we found a simple way to fix this, expect a patch for this soon!

        Show
        Benjamin Eberlei added a comment - we found a simple way to fix this, expect a patch for this soon!
        Hide
        Alexander added a comment -

        The issue should be fixed with the code over here:
        https://github.com/doctrine/doctrine2/pull/167

        I haven't been able to test it on a real Oracle DB yet. Maybe you guys can give it a go?

        Show
        Alexander added a comment - The issue should be fixed with the code over here: https://github.com/doctrine/doctrine2/pull/167 I haven't been able to test it on a real Oracle DB yet. Maybe you guys can give it a go?
        Hide
        Benjamin Eberlei added a comment -

        Fixed, however will not be merged into 2.1.x because the patch is really large

        Show
        Benjamin Eberlei added a comment - Fixed, however will not be merged into 2.1.x because the patch is really large
        Benjamin Eberlei made changes -
        Field Original Value New Value
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.2-DEV [ 10157 ]
        Resolution Fixed [ 1 ]
        Benjamin Eberlei made changes -
        Workflow jira [ 13028 ] jira-feedback [ 15032 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback [ 15032 ] jira-feedback2 [ 16896 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 16896 ] jira-feedback3 [ 19149 ]

        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=DDC-1384, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Matheus Luis Ramos de Souza
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: