Identifiers starting with "not" inside PHQL strings are changed/suppressed

[tobispace]

Example query:

$notificationsManual = Notification::query()
	->andWhere( 'MyNs\Models\Application\Notification.Published = 1' )
	->andWhere( 'MyNs\Models\Application\Notification.Id NOT IN ( SELECT MyNs\Models\Application\NotificationLog.NotificationId FROM MyNs\Models\Application\NotificationLog WHERE MyNs\Models\Application\NotificationLog.Active = 1 )' )
	->andWhere( 'MyNs\Models\Application\Notification.DateToSend <= :today:', [ 'today' => date( 'Y-m-d' ) . ' 23:59:59' ] )
	->execute();

This query was working on Phalcon 3.4.4 (php 7.3)

When upgrading to Phalcon 4.1.2 (php 7.4), I get this error:

Column 'ificationId' doesn't belong to the model or alias 'SHL\Models\Application\Notification', when executing: 
SELECT [MyNs\Models\Application\Notification].* 
FROM [MyNs\Models\Application\Notification] WHERE ((MyNs\Models\Application\Notification.Published = 1) 
AND (MyNs\Models\Application\Notification.Id NOT IN ( SELECT MyNs\Models\Application\NotificationLog.NotificationId 
FROM MyNs\Models\Application\NotificationLog WHERE MyNs\Models\Application\NotificationLog.Active = 1 ))) 
AND (( MyNs\Models\Application\Notification.DateToSend <= :today: ))

This seems to happen with all PHQL queries where there is an identifier starting with "not" (case insensitive).

Why??

[quasipickle]

This is probably a bug in 4 that mangles occurrences of "not". Does the bug still occur if you don't specify the model in your nested SELECT query - if you just specify notification_id?

[marceloandrader]

I'm having the issue too, not sure if you found a workaround, I created the issue #15741 with a simple test case.

[marceloandrader]

I've found a workaround for this, using square brackets to escape this field names, i.e.:

->andWhere('PhalconRest\Models\Notes.[note_type] = :note_type:');