[BUG]: Column names starting with "not"

[Deathamns]

Describe the bug
When a column name starts with the string "not", then Phalcon throws en error like this one:

Column 'ifications' doesn't belong to any of the selected models (1), when preparing: SELECT notifications FROM Objects

To Reproduce

<?php
class Objects extends \Phalcon\Mvc\Model
{
	public $obj_id;
	public $notifications;

	/*public function columnMap()	{
		return [
			'notifications' => 'x',
			// 'notifications' => 'notx',
		];
	}*/
}

$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);
	echo $app->modelsManager->executeQuery(
		'SELECT notifications FROM Objects' // err
		// 'SELECT [notifications] FROM Objects' // ok with escaping
		// 'SELECT x FROM Objects' // ok with columnMap
		// 'SELECT x AS [notifications] FROM Objects' // ok with columnMap
		// 'SELECT x AS notifications FROM Objects' // err with columnMap
		// 'SELECT notx FROM Objects' // err with columnMap + "not" alias
		// 'SELECT [notx] FROM Objects' // ok with columnMap + "not" alias
	)->getFirst()->notifications;
} catch ( Exception $ex ) {
	print_r($ex->getMessage());
}

Expected behavior
To be able to access the column with its original name, without any workarounds (listed above).

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 DATABASE IF NOT EXISTS `test`;
USE `test`;

CREATE TABLE `objects` (
	`obj_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`obj_name` VARCHAR(20) NOT NULL,
	`obj_type` TINYINT(3) UNSIGNED NOT NULL,
	`notifications` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
	PRIMARY KEY (`obj_id`)
) ENGINE=InnoDB ;

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

[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]

I can confirm that this issue is related to the PHQL parser.

$query = new \Phalcon\Mvc\Model\Query('SELECT notifications FROM Objects', $di);
$query->parse(); //error

Gonna move this to 4.1.

[catalinsim]

in given example, modifying to this should work: SELECT [notifications] FROM Objects. Just add square brackets around column name starting with 'not'

[SamAntUA]

The same problem with 'end' in a column name:

• columns('end') leads to (surrounded with grave accents)
  Fatal error: Uncaught Phalcon\Mvc\Model\Exception: Scanning error before 'end` FROM...
• columns('end') leads to
  Fatal error: Uncaught Phalcon\Mvc\Model\Exception: Syntax error, unexpected token END, near to ' FROM...

[yesworld]

Phalcon 5, the same problem. Any news?

[dfernandesbsolus]

Guys, any news about this problem?

[niden]

Can you try enclosing your field in [] i.e. [notifications].

I remember we talked about this years ago and the decision was made not to change the parser because it has to identify the not keyword so as to parse correct syntax. Changing that at the time was not only very time consuming but could prove impossible.

I believe there is a note in the docs about reserved words and fields and that one should enclose the field in [ ].

Can you try and let us know please?

[alex-bunt]

Hi @niden I can confirm tableName.[Notes] fixes our issue.

[niden]

I am going to close this issue. This is part of the parser and it is near impossible to fix because we cannot predict the names of fields in the database and distinguish those with the NOT keyword.

As per the docs, one can enclose their field in [] i.e. [notifications].