Kendall Miller

said
over 1 year ago

Offhand of course you shouldn't get inconsistent results, the best thing to do is open a support ticket to get something like this investigated. I'll convert this over to a ticket so we can help you out.

Rob Parker

said
over 1 year ago

It looks like VistaDB is generally disallowing sub-queries in aggregate queries which is more aggressive than necessary--non-correlated sub-queries should be allowed. We'll need to add logic to distinguish the safe sub-queries from the unsafe correlated sub-queries which can't be allowed in an aggregate query.

In the mean time, it does appear to accept building simple expressions around the Count(*) aggregate--such as the second query--so you can work around the error by saving the sub-query result to a scalar variable. An equivalent query would be: