OCI8Statement is including DOCTRINE_ROWNUM as a column to be inserted.

[LucasHaddad]

Bug Report

Q	A
BC Break	yes
Version	~2.8

How to reproduce

Just try to make a insert using firstIndex !== 0 and any MaxResult.

Expected behaviour

Not including DOCTRINE_ROWNUM as a column to be inserted.

Log

PHP Fatal error:  Uncaught Doctrine\DBAL\Driver\OCI8\OCI8Exception: ORA-00904: "DOCTRINE_ROWNUM": invalid identifier in /my_local/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Exception.php:33
Stack trace:
#0 /my_local/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Statement.php(401): Doctrine\DBAL\Driver\OCI8\OCI8Exception::fromErrorInfo(Array)
#1 /my_local/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/OCI8/OCI8Connection.php(158): Doctrine\DBAL\Driver\OCI8\OCI8Statement->execute()
#2 /my_local/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(1091): Doctrine\DBAL\Driver\OCI8\OCI8Connection->exec('INSERT INTO TABLE...')
#3 /my_local/vendor/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php(222): Doctrine\DBAL\Connection->executeUpdate('INSERT INTO TABLE...', Array, Array)
#4 /my_local/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractOracleDriver.php on line 48

[Ocramius]

@LucasHaddad can you add a snippet of code on how to reproduce the problem?

[LucasHaddad]

require_once __DIR__."/../vendor/autoload.php";

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Query\QueryBuilder;

class Migrate{

    private $_conn = null;
    private $_rows = [];
    private $_pageSize = 500;
    private $_lastOfPage = 0;

    public function __construct(array $dbParams){
        $this->_conn = DriverManager::getConnection($dbParams);
    }

    public function run($params){
        try {
            $this->_pageSize = array_shift($params);
            $totalRows = $this->getTotalRows($params);

            while($totalRows > 0){
                echo "Missing: ".$totalRows.PHP_EOL;

                if(!$this->executeSelect($params))
                    return false;

                if(!$this->executeInsert($this->_rows))
                    return false;

                $this->_rows = [];

                $this->_lastOfPage += $this->_pageSize + 1;

                $totalRows-=$this->_pageSize;
            }

            $this->_conn->close();

            return true;
        } catch (\Exception $e) {
            throw $e;
        }
    }

    private function getTotalRows($params){
        $query = $this->_conn->createQueryBuilder()
            ->select('COUNT(*)')
            ->from('TABLE_NAME');

        $query = $this->prepareWhereClause($query, $params);

        $fetch = $query->execute()
            ->fetch();

        return reset($fetch);
    }

    private function executeSelect($params){
        $findQuery = $this->buildFindQuery($params);
        $findStmt = null;

        if(!$findStmt = $findQuery->execute())
            throw new \Exception("Couldn't select data from TABLE_NAME!");

        $this->_rows = $findStmt->fetchAll();

        $findStmt->closeCursor();

        echo "Selected: ".count($this->_rows).PHP_EOL;

        return !empty($this->_rows);
    }

    private function executeInsert($rows){
        $migrated = 0;
        foreach($rows as $row){
            $setQuery = $this->buildSetQuery($row);
            $setStmt = null;

            if(!$setStmt = $setQuery->execute())
                throw new \Exception("Couldn't insert data into NEW_TABLE!");
            
            $migrated++;
        }
        echo "Migrated: $migrated".PHP_EOL;

        return true;
    }

    private function prepareWhereClause(QueryBuilder $query, array $params) {
        if(!empty($params['start']))
            $query = $query->andWhere("TIMESTAMP >= TO_DATE('${params['start']}', '${params['format']}')");

        if(!empty($params['end']))
            $query = $query->andWhere("TIMESTAMP <= TO_DATE('${params['end']}', '${params['format']}')");

        if(!empty($params['type']))
            $query = $query->andWhere("TYPE = '${params['type']}'");

        if(!empty($params['code']))
            $query = $query->andWhere("HTTP_RESPONSE_CODE = ${params['code']}");

        if(!empty($params['route']))
            $query = $query->andWhere("ROUTE = '${params['route']}'");

        return $query->andWhere("REQUEST_TYPE <> 'GET'");
    }

    private function buildFindQuery(array $params) {
        $query = $this->_conn->createQueryBuilder()
            ->select('*')
            ->from('TABLE_NAME');

        $query = $query->setFirstResult($this->_lastOfPage);
        $query = $query->setMaxResults($this->_pageSize);

        return $this->prepareWhereClause($query, $params);
    }

    private function buildSetQuery($values) {
        return $this->_conn->createQueryBuilder()
            ->insert('NEW_TABLE')
            ->values($values);
    }
}

$dbParams = [
    'driver'      => 'oci8',      
    'host'        => 'host',          
    'port'        => 'port',         
    'user'        => 'user',         
    'password'    => 'pass',       
    'dbname'      => 'dbname',       
    'service'     => true,      
];

$migration = new Migrate($dbParams);

$params = [
    'pageSize'        => 500,        
    'type'            => 'RESPONSE',        
    'code'            => 200,      
    'start'           => '14/10/18',        
    'end'             => '16/10/18',     
    'format'          => 'DD/MM/YY',
    'route'           => '/my_test_route',          
];

$startTime = microtime(true);

if($migration->run($params))
    echo "\033[32m Migrated. \033".PHP_EOL;
else
    echo "\033[31m Migrated with warnings.\033".PHP_EOL;

die("\033[37m  Elapsed time: \033".(microtime(true) - $startTime).PHP_EOL);

I didn't clone the table directly cause one have different columns that are composed by one only from the first and a little o type handling that i removed from this code.
Environment:
PHP 7.2.11,
Debian 8 Jessie

[LucasHaddad]

@Ocramius
If I use "OFFSET x ROWS FETCH NEXT y ROWS ONLY" at select query instead of Doctrine's QueryBuilder->setFirstResult(x) and QueryBuilder->setMaxResults(y) it works nicely while Connection->fetch*() and don't add doctrine_rownum as a column to be inserted on my Connection->insert(). (Works for Oracle and SQLServer >= 2012).

For other databases like Postgre, MySQL, DB2 you can use LIMIT x, y or LIMIT x OFFSET y.

[morozov]

@LucasHaddad I think we could reimplement doModifyLimitQuery() for Oracle using the syntax above. The question is whether this syntax supported by all Oracle versions we support and what they are.

Could you find some changelog from Oracle showing when this syntax was introduced?

UPD: looks like it's supported starting Oracle 12c.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.