Strange subquery behavior.
Hi,
We are upgrading from ids 10.00 to ids 12.10.
There is this strange behavior on ids 12.
When i select like this, i says that A.tabid must be in group by:
SELECT A.tabid
,(SELECT SUM(B.collength * A.rowsize)
FROM syscolumns B WHERE B.tabid = A.tabid)
FROM systables A
WHERE A.tabid = 1
But when i select this, i works. note "A.rowsize * B.collength" vs
"B.collength * A.rowsize":
SELECT A.tabid
,(SELECT SUM(A.rowsize * B.collength)
FROM syscolumns B WHERE B.tabid = A.tabid)
FROM systables A
WHERE A.tabid = 1
Is there some configurations which i can control the behavior?
is this kind of behavior relevant or is my ids 12.10 installation broken?
Note. this is just example i doubt this A.rowsize * B.collength has any other
meaninful valua than to show the behavior of switching "B.collength" and
"A.rowsize" place, in subquery.
T. Matti Jaatinen
I would open a PMR report with IBM because this sounds like a parsing bug.
BTW, I would move that sub-query into the FROM clause as a derived table
which would be more efficient than the correlated subquery you have here.
Yes, I know this is a contrived example, but it's a good point in general,
so...
SELECT A.tabid, C.sum_product
FROM syscolumns A,
(SELECT SUM(A.rowsize * B.collength) as sum_product
FROM syscolumns B WHERE B.tabid = 1) as C
WHERE A.tabid = C.tabid
Art S. Kagel, Principal Consultant
ASK Database Management
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Fri, May 23, 2014 at 6:03 AM, MATTI JAATINEN
<matti.jaatinen@norelco.fi>wrote:
> Hi,
>
> We are upgrading from ids 10.00 to ids 12.10.
>
> There is this strange behavior on ids 12.
>
> When i select like this, i says that A.tabid must be in group by:
>
> SELECT A.tabid
> ,(SELECT SUM(B.collength * A.rowsize)
> FROM syscolumns B WHERE B.tabid = A.tabid)
> FROM systables A
> WHERE A.tabid = 1>
> But when i select this, i works. note "A.rowsize * B.collength" vs
> "B.collength * A.rowsize":
>
> SELECT A.tabid
> ,(SELECT SUM(A.rowsize * B.collength)
> FROM syscolumns B WHERE B.tabid = A.tabid)
> FROM systables A
> WHERE A.tabid = 1>
> Is there some configurations which i can control the behavior?
> is this kind of behavior relevant or is my ids 12.10 installation broken?
>
> Note. this is just example i doubt this A.rowsize * B.collength has any
> other
> meaninful valua than to show the behavior of switching "B.collength" and
> "A.rowsize" place, in subquery.
>
> T. Matti Jaatinen
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e011615eaa0f77e04fa10052f