[BUG] Invalid Oracle Limiting

[mingalevme]

$p = Package::findFirst(3);

Exception: Column "DB_ROWNUM" doesn't make part of the column map

Generates the SQL:

SELECT Z2.* FROM (SELECT Z1.*, ROWNUM DB_ROWNUM FROM ( SELECT [COLUMS] FROM "RADIO"."RADIO_PACKAGE" WHERE "RADIO_PACKAGE"."RADIO_PACKAGE_ID" = 3 ) Z1 ) Z2 WHERE Z2.DB_ROWNUM BETWEEN 1 AND 1
SELECT COUNT(*) AS "numrows" FROM (SELECT Z2.* FROM (SELECT Z1.*, ROWNUM DB_ROWNUM FROM ( SELECT [COLUMNS] FROM "RADIO"."RADIO_PACKAGE" WHERE "RADIO_PACKAGE"."RADIO_PACKAGE_ID" = 3 ) Z1 ) Z2 WHERE Z2.DB_ROWNUM BETWEEN 1 AND 1)

Want to back this issue? Post a bounty on it! We accept bounties via Bountysource.

[phalcon]

@marciopaiva can you take a look?

[marciopaiva]

checking...

[marciopaiva]

@mingalevme I can´t simulate the error.
which version of Oracle you are using??

[mingalevme]

@marciopaiva I think, the problem is in SQL-query and Column-mapping (Model::columnMap()). The SQL-query:

SELECT Z2.* FROM (SELECT Z1.*, ROWNUM DB_ROWNUM FROM ( SELECT [COLUMS] FROM "RADIO"."RADIO_PACKAGE" WHERE "RADIO_PACKAGE"."RADIO_PACKAGE_ID" = 3 ) Z1 ) Z2 WHERE Z2.DB_ROWNUM BETWEEN 1 AND 1

returns all table columns and column DB_ROWNUM, but model don't expect that column.
P.S. I use Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production.

[marciopaiva]

@mingalevme plz, post u Model.

[mingalevme]

<?php

namespace Radio\MVC;

abstract class Model extends \Phalcon\Mvc\Model
{
    public function initialize()
    {
        $this->setSchema('RADIO');
    }
}

<?php

class Package extends \Radio\MVC\Model
{
    public function getSource()
    {
        return 'RADIO_PACKAGE';
    }

    public static function columnMap()
    {
        return [
            'RADIO_PACKAGE_ID' => 'id',
            'RADIO_PACKAGE_TITLE' => 'title',
            'RADIO_PACKAGE_BODY_ORIG' => 'body',
            'RADIO_PACKAGE_BODY_REWRITE' => 'body_rewrite',
            'RADIO_PACKAGE_ANNOUNCE' => 'lead',
            'RADIO_PACKAGE_STATUS_ID' => 'status_id',
            'RADIO_PACKAGE_FILENAME' => 'filename',
            'RADIO_PACKAGE_DATE_CREATED' => 'date_created',
            'RADIO_PACKAGE_DATE_MODIFIED' => 'date_modified',
            'RADIO_PACKAGE_AIR_START_DATE' => 'air_start_date',
            'RADIO_PACKAGE_AIR_STOP_DATE' => 'air_stop_date',
            'RADIO_PACKAGE_AIR_PERIOD' => 'air_period',
            'RADIO_PACKAGE_DISCLAIMER' => 'disclaimer',
            'RADIO_PACKAGE_COMMENT' => 'comment',
            'RADIO_HOTDOG_ID' => 'hotdog_id',
            'RADIO_USER' => 'user',
            'RADIO_PACKAGE_IS_ACTIVE' => 'is_active',
        ];
    }
}

[marciopaiva]

@mingalevme put in u columnMap() map to "DB_ROWNUM"

<?php

class Package extends \Radio\MVC\Model
{
    public function getSource()
    {
        return 'RADIO_PACKAGE';
    }

    public static function columnMap()
    {
        return [
            'RADIO_PACKAGE_ID' => 'id',
            'RADIO_PACKAGE_TITLE' => 'title',
            'RADIO_PACKAGE_BODY_ORIG' => 'body',
            'RADIO_PACKAGE_BODY_REWRITE' => 'body_rewrite',
            'RADIO_PACKAGE_ANNOUNCE' => 'lead',
            'RADIO_PACKAGE_STATUS_ID' => 'status_id',
            'RADIO_PACKAGE_FILENAME' => 'filename',
            'RADIO_PACKAGE_DATE_CREATED' => 'date_created',
            'RADIO_PACKAGE_DATE_MODIFIED' => 'date_modified',
            'RADIO_PACKAGE_AIR_START_DATE' => 'air_start_date',
            'RADIO_PACKAGE_AIR_STOP_DATE' => 'air_stop_date',
            'RADIO_PACKAGE_AIR_PERIOD' => 'air_period',
            'RADIO_PACKAGE_DISCLAIMER' => 'disclaimer',
            'RADIO_PACKAGE_COMMENT' => 'comment',
            'RADIO_HOTDOG_ID' => 'hotdog_id',
            'RADIO_USER' => 'user',
            'RADIO_PACKAGE_IS_ACTIVE' => 'is_active',
            'DB_ROWNUM' => 'db_rownum',
        ];
    }
}

I will find a solution to this problem, stupid oracle :(

[andresgutierrez]

This is fixed in the 2.0.x branch (Phalcon 2.0.4), you have to change a global ORM setting to allow unexpected columns:

\Phalcon\Mvc\Model::setup(['ignoreUnknownColumns' => true]);