New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Column doesn't belong to any of the selected models (1) #13006

Closed
mbrostami opened this Issue Aug 4, 2017 · 5 comments

Comments

Projects
None yet
3 participants
@mbrostami
Copy link
Contributor

mbrostami commented Aug 4, 2017

Expected and Actual Behavior

Related to this issues: #1731 , #10934
When I want to use subquery in where conditions and having beside that I got this error:

Column 'SomethingNotInModels' doesn't belong to any of the selected models (1), when preparing: SELECT "aaa" AS [SomethingNotInModels] FROM [Trumpet\Models\Ad] AS [a] WHERE a.IDCategory IN (SELECT c.ID from Trumpet\Models\Category c) HAVING SomethingNotInModels = "aaa"

#0 [internal function]: Phalcon\Mvc\Model\Query->_getQualified(Array)
#1 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array, true)
#2 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array)
#3 [internal function]: Phalcon\Mvc\Model\Query->_prepareSelect()
#4 [internal function]: Phalcon\Mvc\Model\Query->parse()

Reproduce the issue:

$builder->columns('"aaa" as SomethingNotInModels')
            ->from(['a' => 'Trumpet\Models\Ad'])
            ->where('a.IDCategory IN (SELECT c.ID from Trumpet\Models\Category c)')
            ->having('SomethingNotInModels = "aaa"');
$builder->getQuery()->execute();

If I remove 'where' OR 'having' it doesn't trigger error.

Details

  • Phalcon version: 3.1.2
  • PHP Version: 5.6.30-12~ubuntu14.04.1+deb.sury.org+1
  • Operating System: Ubuntu
  • Installation type: installing via package manager
  • Server: Nginx
  • Other related info (Database, table schema):
create table Ad (ID INT(20), IDCategory INT(20));
create table Category (ID INT(20));
@stale

This comment has been minimized.

Copy link

stale bot commented Apr 16, 2018

Thank you for contributing to this issue. As it has been 90 days since the last activity, we are automatically closing the issue. This is often because the request was already solved in some way and it just wasn't updated or it's no longer applicable. If that's not the case, please feel free to either reopen this issue or open a new one. We will be more than happy to look at it again! You can read more here: https://blog.phalconphp.com/post/github-closing-old-issues

@stale stale bot added the stale label Apr 16, 2018

@sergeyklay sergeyklay closed this Apr 16, 2018

@alexbusu

This comment has been minimized.

Copy link
Contributor

alexbusu commented Dec 3, 2018

I have this issue as well.
So, to reproduce you just have to

  1. select a column with alias
  2. use a sub-query in WHERE clause
  3. use the alias further in HAVING OR ORDER BY (which in SQL will apply to selected result set, of course)
    In my case the error trace is
#0 [internal function]: Phalcon\Mvc\Model\Query->_getQualified(Array)
#1 [internal function]: Phalcon\Mvc\Model\Query->_getExpression(Array)
#2 [internal function]: Phalcon\Mvc\Model\Query->_getOrderClause(Array)
#3 [internal function]: Phalcon\Mvc\Model\Query->_prepareSelect()
#4 [internal function]: Phalcon\Mvc\Model\Query->parse()
...

because I have used the alias in ORDER BY expression.

The issue is very strange, as after chasing the error it is not clear why this happens (looking at source code, this is not supposed to happen 😄).

I have tried to debug by comparing Query of two cases:

  1. the failing case described above and
  2. a successful case, when I remove the sub-query in WHERE block.

What is the strange part I noticed is that all _sql* fields of Phalcon\Mvc\Model\Query are the same in both cases, except _sqlColumnAliases which, in failing case, was an empty array.
As I see, the _sqlColumnAliases variab;e is set only here (I run Phalcon v3.3.2 in prod, but the issue is still there for v3.4.2 as well - tested on local). So the problem may only be inside for column in selectColumns loop block above, right? Since the AST is the same except where property, then we should get the same result in _sqlColumnAliases (since column parsing happens before where and having and orderBy).
Here's the comparison (failing vs successful):
image
image

Since the columns in AST have aliases (and the _ast.select.columns is identical in both cases) they should always appear in _sqlColumnAliases, right?
image

Please help me sort this out!

@alexbusu

This comment has been minimized.

Copy link
Contributor

alexbusu commented Dec 4, 2018

Here is what I play with, trying to trace this issue

$case = 'ok-without-where'; // can be 'fail', 'ok', 'ok-without-where'
$phql = sprintf(
    'SELECT [%1$s].[id], (SELECT [%2$s].[payout] FROM [%2$s] LIMIT 1) as publisherPayout '
    . 'FROM [%1$s] '
    . ([
            'fail' => 'WHERE [%1$s].[campaign_id] IN (SELECT [%2$s].[camapign_id] FROM [%2$s]) ',
            'ok' => 'WHERE [%1$s].[campaign_id] IN (1, 2, "a") ',
        ]
        [$case] ?? '')
    . 'ORDER BY publisherPayout DESC',
    Campaigns::class,
    CampaignPayoutHistory::class
);
$altQuery = new class($phql, \Phalcon\Di::getDefault()) extends \Phalcon\Mvc\Model\Query
{
    public function getAst()
    {
        try {
            $this->parse();
        } catch (\Exception $e) {
            fprintf(STDERR, $e->getMessage());
        }
        return $this->_ast;
    }
};
$ast = $altQuery->getAst();

It seems there is something wrong related to the where block with sub-select, as the AST.select.columns are the same in all cases.

@alexbusu

This comment has been minimized.

Copy link
Contributor

alexbusu commented Dec 4, 2018

Found the issue.
The _sqlColumnAliases gets overwritten in the last sub-select.
If you want to play alone:

$case = 'fix'; // can be 'fail', 'ok', 'ok-without-where', 'enlightning', 'fix'
$phql = sprintf(
    'SELECT [%1$s].[id], (SELECT [%2$s].[payout] FROM [%2$s] LIMIT 1) as publisherPayout '
    . 'FROM [%1$s] '
    . ([
            'fail' => 'WHERE [%1$s].[campaign_id] IN (SELECT [%2$s].[camapign_id] FROM [%2$s]) ',
            'ok' => 'WHERE [%1$s].[campaign_id] IN (1, 2, "a") ',
            'enlightning' => 'WHERE [%1$s].[campaign_id] IN (SELECT [%2$s].[camapign_id] as youGotIt FROM [%2$s]) ',
            'fix' => 'WHERE [%1$s].[campaign_id] IN (SELECT [%2$s].[camapign_id] FROM [%2$s]) AND 1 IN (SELECT 1 as publisherPayout FROM [%1$s]) ',
        ]
        [$case] ?? '')
    . 'ORDER BY publisherPayout DESC',
    Campaigns::class,
    CampaignPayoutHistory::class
);
$altQuery = new class($phql, \Phalcon\Di::getDefault()) extends \Phalcon\Mvc\Model\Query
{
    public function getAst()
    {
        try {
            $this->parse();
        } catch (\Exception $e) {
            fprintf(STDERR, $e->getMessage());
        }
        return $this->_ast;
    }
};
$ast = $altQuery->getAst();

As you see, as a temporary workaround one could use a fake sub-select expression in where expression, just to get the columns needed.

After all this journey I see that each select expression should have its own _sqlColumnAliases.

Regards!

@alexbusu

This comment has been minimized.

Copy link
Contributor

alexbusu commented Dec 10, 2018

ℹ️ Workaround in Phalcon v3.x
As v3.x will not include this update in further (security) releases, if any, here is a workaround for this issue, in v3.

$parameters = [
  'conditions' => [ ... ],
  'columns' => [ ... ],
  //...
];
// adding aliases fix
$aliasFixConditionPhql = "EXISTS (SELECT 1 as myColNameOrAlias1, 1 as myColNameOrAlias2 FROM [\App\Model\DummyExistingModel])";
$parameters['conditions'][] = $aliasFixConditionPhql;
// fix is added, resume normal flow
$parameters['conditions'] .= implode(' AND ', $parameters['conditions']);
// ...

This basically adds a fake sub-query that will restore the aliases in \Phalcon\Mvc\Model\Query parser (as defined in $parameters['columns'] or from other source, depending on your implementation).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment