[BUG]:Join in createBuilder() is removing part of condition string on execute

[liangliangGit]

Questions? Discussions: https://phalcon.io/discussions or Discord: https://phalcon.io/discord

Describe the bug
When executing the query with createBuilder() in version 5.8.0 the 'not' part of the column name is being removed. Throwing an error saying that the column doesn't exist.
In version 3.x it was working as expected.

SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.ice_id' in 'field list'<br>
<pre>#0 [internal function]: PDOStatement->execute()
#1 [internal function]: Phalcon\Db\Adapter\Pdo\AbstractPdo->executePrepared(Object(PDOStatement), Array, Array)
#2 [internal function]: Phalcon\Db\Adapter\Pdo\AbstractPdo->query('SELECT `t1`.`id...', Array, Array)
#3 [internal function]: Phalcon\Mvc\Model\Query->executeSelect(Array, Array, Array)
#4 [internal function]: Phalcon\Mvc\Model\Query->execute()
#5 D:\work_project\upgrade\mes_mingjing\app\common\base\ControllerBase.php(167): Phalcon\Paginator\Adapter\QueryBuilder->paginate()
#6 D:\work_project\upgrade\mes_mingjing\app\modules\mes\controller\ReportController.php(35): Envsan\Common\Base\ControllerBase->getPagination(Object(Phalcon\Mvc\Model\Query\Builder))
#7 [internal function]: Envsan\Modules\Mes\Controller\ReportController->listAction('json')
#8 [internal function]: Phalcon\Dispatcher\AbstractDispatcher->callActionMethod(Object(Envsan\Modules\Mes\Controller\ReportController), 'listAction', Array)
#9 [internal function]: Phalcon\Dispatcher\AbstractDispatcher->dispatch()
#10 D:\work_project\upgrade\mes_mingjing\app\bootstrap_web.php(93): Phalcon\Mvc\Application->handle('/mes/report/lis...')
#11 D:\work_project\upgrade\mes_mingjing\public\index.php(2): require('D:\\work_project...')
#12 {main}</pre>

To Reproduce

$builder = $this->modelsManager->createBuilder()
            ->columns('
                t1.id,
                t1.uid,
                t1.code,
                t1.plan_type,
                t1.plan_begin_date,
                t1.plan_end_date,
                t1.ext_val,
                t1.status,
                t1.status_name,
                t3.code as order_code,
                t4.name as customer_name,
                t2.code as product_code,
                t2.name as product_name,
                t.uid as detail_uid,
                t.move_quantity,
                t.quantity
            ')
            ->addFrom('Envsan\Modules\Mes\Model\MesNoticeDetail', 't')
            ->leftJoin('Envsan\Modules\Mes\Model\MesNotice', 't.notice_id = t1.id', 't1')

Expected behavior
should be able to use any column name on joins just like in version 3.4

Details

• Phalcon version: 5.8.0
• PHP Version: 8.2
• Operating System: windows11
• Installation type: installing via package manager
• Zephir version (if any):
• Server: Nginx
• Other related info (Database, table schema):

[raicabogdan]

I can't find the exact referrence, I think we should document this somewhere. You need to escape the columns or tables that contain specific keywords related to query operators. For example 'notice' table, you can use ['notice'] table.

In your case

->addFrom(['Envsan\Modules\Mes\Model\MesNoticeDetail'], 't')

I can't exactly recall if this is the exact syntax, you'll have to test this, but I remember this was the workaround to this issue.

It can't be fixed unfortunately at this point as it is the C part of the sql parser causing this issue.