[BUG]: PHQL; Undefined index: ... in phalcon/Mvc/Model/Resultset/Complex.zep

[Deathamns]

Describe the bug
Can't run PHQL query when selecting columns from a table with asterisk, and specific table aliased columns from some other table. No problem in v3.4.5.
Might be related to #14488.

PHP Notice: Undefined index: obj_name in phalcon/Mvc/Model/Resultset/Complex.zep on line 224

To Reproduce

<?php
class Objects extends \Phalcon\Mvc\Model
{
	public $obj_id;
	public $obj_name;
	public $obj_type;
}

class ObjectDetails extends \Phalcon\Mvc\Model
{
	public $obj_id;
	public $details;
}

$di = new \Phalcon\Di\FactoryDefault;

$di['db'] = new \Phalcon\Db\Adapter\Pdo\Mysql([
	'dbname'   => 'test',
	'username' => 'test',
	'password' => 'test',
]);

try {
	$app = new \Phalcon\Mvc\Application($di);
	$res = $app->modelsManager->executeQuery(
		'SELECT o.obj_name, obj_type, od.* FROM Objects AS o ' .
		'LEFT JOIN ObjectDetails AS od ON od.obj_id = o.obj_id ' .
		'WHERE o.obj_id  = :obj_id:',
		['obj_id' => 1]
	)->getFirst();

	echo $res->obj_name;
	// $res->od is available, and if there are values, it is correctly filled
} catch ( Exception $ex ) {
	print_r($ex->getMessage());
}

Expected behavior
To be able to run the query and access the table aliased columns.

Details

• Phalcon version: 4.0.0, Dec 21 2019 18:42:18, Zephir Version 0.12.15-814db50
• PHP Version: PHP 7.4.1 (cli) (built: Dec 17 2019 19:23:59) ( NTS Visual C++ 2017 x64 )
• Database, table schema:

CREATE TABLE IF NOT EXISTS `objects` (
  `obj_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `obj_name` VARCHAR(20) NOT NULL,
  `obj_type` TINYINT(3) UNSIGNED NOT NULL,
  PRIMARY KEY (`obj_id`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `object_details` (
  `obj_id` INT(10) UNSIGNED NOT NULL,
  `details` VARCHAR(225) NOT NULL,
  PRIMARY KEY (`obj_id`)
) ENGINE=InnoDB;

INSERT INTO `objects` (`obj_id`, `obj_name`, `obj_type`) VALUES (1, 'test1', 2);

[ruudboon]

Let's investigate this bug in the current sprint. If we can fix it easily in Zephir we do it in 4.0.x if work is required in the PHQL parser we move this to 4.1.x

[ruudboon]

Looks like this issue still occurs after the #14566 fix. Let's have a chat @niden if we can fix it in this iteration or have to reschedule this.

[niden]

I just spent 5 hours on this and not getting close to finding it. From previous iterations of this bug such as #14488 (because they are related) I found that there was a change made to the columnMap and how aliases are calculated (AS x). I have no idea which commit it is that did this but it happened clearly in v4.

For the above example we could access

$res->obj_name

i.e. the results would come in ignoring the AS o from the query. Right now it comes back as:

$res->o_obj_name

If we remove the AS o from the Objects table this works:

$res->obj_name

[niden]

Honestly it will be easier to tell people that if you declare an alias, then it will prefix your fields (in the docs) but I am not 100% sure that this is the case and I have not been able to find the commit (or the place in the code) that does this....

[ruudboon]

Let's add the comment to our docs for now. We can close this or keep it open and move it to 4.1 and have a fresh look at this when we look at the PHQL fixes as well.

[shurastik]

Honestly it will be easier to tell people that if you declare an alias, then it will prefix your fields (in the docs) but I am not 100% sure that this is the case and I have not been able to find the commit (or the place in the code) that does this....

But how about legacy applications? If this behavior becomes a BC-break, a ton of code should be rewritten.

[ruudboon]

@sergeyklay Can you shine your light on this one?

[sergeyklay]

I'll try to sort out

[kirill-voronov]

This also crashes Phalcon\Mvc\Model\Query\Builder result set, for example:

// ... build some query into $builder
$items = $builder->getQuery()->execute();
foreach ($items as $item) { // this line fails
}

[sergeyklay]

Btw, there is a typo in the SQL (ambiguous column name: obj_type):

SELECT
    o.obj_name,
-   obj_type,
+   o.obj_type,
    od.*
FROM
    Objects AS o
LEFT JOIN ObjectDetails AS od ON
    od.obj_id = o.obj_id
WHERE
    o.obj_id = 1

[sergeyklay]

Found the bad commit using git bisect: e851f48 (#13663)

Code to reproduce:

use Phalcon\Db\Adapter\Pdo\Sqlite;
use Phalcon\Di;
use Phalcon\Di\FactoryDefault;
use Phalcon\Mvc\Application;
use Phalcon\Mvc\Model;

Di::setDefault($container = new FactoryDefault());

$container->setShared(
    'db',
    new Sqlite(
        ['dbname' => '/tmp/test.sqlite']
    )
);

class Objects extends Model
{
    public $obj_id;
    public $obj_name;
    public $obj_type;
}

class ObjectDetails extends Model
{
    public $obj_id;
    public $details;
}

$app = new Application($container);

$sql = <<<EOF
SELECT
    o.obj_name,
    o.obj_type,
    od.*
FROM
    Objects AS o
LEFT JOIN ObjectDetails AS od ON
    od.obj_id = o.obj_id
WHERE
    o.obj_id = :obj_id:
EOF;

/** @var \Phalcon\Mvc\Model\Query $query */
$query = $app->modelsManager->createQuery($sql);

print_r(explode(',', $query->getSql()['sql']));

Commit e851f48 introduces the following changes:

Array
(
+     [0] => SELECT "o"."obj_name" AS "o_obj_name"
-     [0] => SELECT "o"."obj_name" AS "obj_name"
+     [1] =>  "o"."obj_type" AS "o_obj_type"
-     [1] =>  "o"."obj_type" AS "obj_type"
      [2] =>  "od"."obj_id" AS "_od_obj_id"
      [3] =>  "od"."details" AS "_od_details"
      [4] =>  "od"."obj_type" AS "_od_obj_type" FROM "objects" AS "o"  LEFT JOIN "object_details" AS "od" ON "od"."obj_id" = "o"."obj_id" WHERE "o"."obj_id" = :obj_id
)

[sergeyklay]

Fixed in the 4.0.x branch. Feel free to open a new issue if the problem appears again. Thank you for the bug report, and for helping us make Phalcon better.