Dialect::select produces sql with missing columns

[iby]

$queryBuilder = new Builder();
$queryBuilder
    ->columns(['id', 'email'])
    ->from('Users')
    ->where('id > 0');

$dialect         = DI::getDefault()->get('db')->getDialect();
$intermediary = $queryBuilder->getQuery()->parse();
$sql              = $dialect->select($intermediary);
// SELECT ``, `` FROM `users` WHERE `users`.`id` > 0 

Same result when changing columns to anything else. However, if I do

$result = $queryBuilder->getQuery()->execute();
// $result->_result->_sqlStatement shows: 
// SELECT `users`.`id` AS `id`, `users`.`email` AS `email` FROM `users` WHERE `users`.`id` > 0

There seem to be some modifications to the intermediary value retrieved from $query->parse() in query.c on line 3953:

phalcon_array_update_string(&intermediate, SL("columns"), &select_columns, PH_COPY | PH_SEPARATE);

I guess that makes the difference?

[iby]

Dialect::select expects columns value to be an array of single value arrays with the original $intermediary['columns']['id']['column'] values – that transformation happens inside Query::_executeSelect before it's sent to the Dialect::select.

Returned value from $queryBuilder->getQuery()->parse():

Expected value in $dialect->select($intermediary):

A php fix for this would be:

foreach ($intermediary['columns'] as $key => $column) {
    $fixedColumns[$key] = [$column['column']];
}

A c solution (in php equivalent) for Dialect::select would be something like this:

if (isset($column[0])) {
    $sql = Dialect::getSqlExpression($column[0]);
} elseif (isset($column['column'])) {
    $sql = Dialect::getSqlExpression($column['column']);
}

[endeveit]

Same here.

$builder = \Phalcon\DI::getDefault()->getShared('modelsManager')->createBuilder()
    ->from('Model\Entity\AclRole')
    ->orderBy('name ASC, is_default DESC, application_type ASC');
$parsed  = $builder->getQuery()->parse();
$sql     = \Phalcon\DI::getDefault()->getShared('db')->getDialect()->select($parsed);
echo $sql . "\n";

Outputs:

PHP Notice:  Undefined index: 0 in /Users/endeveit/projects/phalcon-1905/tmp.php on line 6
PHP Stack trace:
PHP   1. {main}() /Users/endeveit/projects/phalcon-1905/tmp.php:0
PHP   2. Phalcon\Db\Dialect->select() /Users/endeveit/projects/phalcon-1905/tmp.php:6

Notice: Undefined index: 0 in /Users/endeveit/projects/phalcon-1905/tmp.php on line 6

Call Stack:
    0.0004     239616   1. {main}() /Users/endeveit/projects/phalcon-1905/tmp.php:0
    0.0574     767392   2. Phalcon\Db\Dialect->select() /Users/endeveit/projects/phalcon-1905/tmp.php:6

SELECT `` FROM `acl_role` ORDER BY `acl_role`.`name` ASC, `acl_role`.`is_default` DESC, `acl_role`.`application_type` ASC

[ghost]

Fix submitted but I am not sure this is the proper way to use the parsed PHQL.

[ghost]

@ianbytchek Could you please test now and close the issue if everything works for you?

[iby]

Looks good to me. Thanks.

[xblabs]

Will that fix be included in 1.2.7 ?