columns blank in select clause (coredump in master). Reproducible, tests provided, tested in master also

[masenocturnal]

Tested on :

• Ubuntu 12.04,
• Ubuntu 15.04
• Centos 6.7

Behavior (cphalcon/2.0.0) :

When executing a self join (it may occur under other scenarios, I am unsure) using the query builder the resulting select clause does not list any column names.

 SELECT ``, `` FROM `test_tree` AS `child`, `test_tree` AS `parent` WHERE `child`.`lft` BETWEEN `parent`.`lft` AND `parent`.`rgt`

Behaviour (cphalcon/master)

In the cphalcon/master it results in a core dump. commit #31bb51afeeae6dd9fb32ea70ef104fa80a8c9214 attempts to work around this issue however something further down the stack results in a dump.

Expected Behavior:

SELECT `child.foreign_id`, `parent.id` FROM `test_tree` AS `child`, `test_tree` AS `parent` WHERE `child`.`lft` BETWEEN `parent`.`lft` AND `parent`.`rgt`am@portland:~/projects/phalcontest/bin$ 

In addition, a PHP Notice is raised :
am@portland:~/projects/phalcontest/bin$ php TestTree.php
PHP Notice: Undefined index: 0 in phalcon/db/dialect.zep on line 364 in / /home/am/projects/phalcontest/bin/TestTree.php on line 40
PHP Stack trace:
PHP 1. {main}() /home/am/projects/phalcontest/bin/TestTree.php:0
PHP 2. TestTree->getTree() /home/am/projects/phalcontest/bin/TestTree.php:20
PHP 3. Phalcon\Db\Dialect->select() /home/am/projects/phalcontest/bin/TestTree.php:40
PHP Notice: Undefined index: 0 in phalcon/db/dialect.zep on line 364 in /home/am/projects/phalcontest/bin/TestTree.php on line 40

use case

As you may have already worked out this is used to implement a nested sets pattern within the database.

To reproduce

<?php
use Phalcon\Mvc\Model;
use Phalcon\Mvc\Model\Query;
use Phalcon\CLI\Console as ConsoleApp;

$di = new \Phalcon\DI\FactoryDefault\CLI();
$di['db'] = new \Phalcon\Db\Adapter\Pdo\Mysql([
    'host'     => 'localhost',
    'username' => 'root',
    'password' => '', 
    'dbname'   => 'phalcon_test',
]);

$loader = new \Phalcon\Loader();
$app = new ConsoleApp();
$app->setDi($di);

$testTree = new TestTree();
$testTree->getTree();

final class TestTree extends \Phalcon\Mvc\Model
{ 
    public function getTree()
    {
        $query = $this->_modelsManager->createBuilder()
            // also fails with this syntax
            // ->columns('child.foreign_id, parent.id')
            ->columns(['child.foreign_id', 'parent.id'])
            ->addFrom('TestTree', 'child')
            ->addFrom('TestTree', 'parent')
            ->andWhere('child.lft BETWEEN parent.lft AND parent.rgt')
            ->getQuery();

        $dialect = $this->getReadConnection()->getDialect();
        $sql = $query->parse();

        try {
            $x = $dialect->select($sql);
            echo $x;
        } catch (Exception $e) {
            echo $e->printStackTrace();
        }
    }
}

Database component required

BEGIN;

DROP TABLE IF EXISTS `test_tree`;
CREATE TABLE `test_tree` (
`id` int(11) NOT NULL auto_increment PRIMARY KEY,
`foreign_id` int(3) default NULL REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
`lft` int(11) NOT NULL,
`rgt` int(11) NOT NULL,  
KEY `foreign_id` (`foreign_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6722 DEFAULT CHARSET=utf8;

INSERT INTO `test_tree` VALUES (6375,20,1,546);
INSERT INTO `test_tree` VALUES (6380,212,6,51);
INSERT INTO `test_tree` VALUES (6381,258,7,8);
INSERT INTO `test_tree` VALUES (6382,222,9,10);
INSERT INTO `test_tree` VALUES (6383,232,11,12);
INSERT INTO `test_tree` VALUES (6384,243,13,14);
INSERT INTO `test_tree` VALUES (6385,244,15,16);
INSERT INTO `test_tree` VALUES (6386,233,17,18);
INSERT INTO `test_tree` VALUES (6387,234,19,22);
INSERT INTO `test_tree` VALUES (6388,245,23,24);
INSERT INTO `test_tree` VALUES (6389,240,25,26);
INSERT INTO `test_tree` VALUES (6390,236,27,28);
INSERT INTO `test_tree` VALUES (6391,247,29,32);
INSERT INTO `test_tree` VALUES (6392,231,30,31);
INSERT INTO `test_tree` VALUES (6393,246,33,34);
INSERT INTO `test_tree` VALUES (6394,235,35,36);
INSERT INTO `test_tree` VALUES (6395,242,37,38);
INSERT INTO `test_tree` VALUES (6396,241,39,40);
INSERT INTO `test_tree` VALUES (6397,248,41,42);
INSERT INTO `test_tree` VALUES (6398,238,43,44);
INSERT INTO `test_tree` VALUES (6399,239,45,48);
INSERT INTO `test_tree` VALUES (6400,237,49,50);
INSERT INTO `test_tree` VALUES (6401,251,52,71);
INSERT INTO `test_tree` VALUES (6402,101,53,54);
COMMIT;

$mysql phalcon_test < test_tree.sql

I've tried to debug this myself however I don't really understand where the issue lies. If someone can explain the issue I'm happy to have a go at fixing this.

[andresgutierrez]

Could you please try again compiling from 2.0.x?

[masenocturnal]

Will double check but I'm reasonably certain I have checked that also.

[masenocturnal]

I was wrong ..or this got fixed in the last day or so. Seems to work ok.

Many thanks.

am@portland:~/projects/phalcontest/bin$ php TestTree.php
SELECT child.foreign_id AS foreign_id, parent.id AS id FROM test_tree AS child, test_tree AS parent WHERE child.lft BETWEEN parent.lft AND parent.rgt

I have a few more complicated test cases which are actually running the code base I'd like to test first if before closing the issue if that's ok ?

Also do you know which commit likely fixes this issue? As I may need to backport this until the next release of phalcon

[Green-Cat]

@masenocturnal see #10247. 2.0.1 is probably getting released very soon.

[KorsaR-ZN]

@masenocturnal This has been fixed, see #10217

[masenocturnal]

All looks good. This can be closed.

Where is the design/ release date discussed? Is there a mailing list or irc meeting?

I have few questions regarding the change of interfaces between versions which so far both irc and the forums have been unable to answer.

On 6 May 2015 12:46:13 am ACST, Vladimir Metelitsa notifications@github.com wrote:

@masenocturnal see #10247. 2.0.1 is probably getting released very
soon.

---

Reply to this email directly or view it on GitHub:
#10248 (comment)

[KorsaR-ZN]

@masenocturnal Create a new issue to discuss here