| Subject | Re: [ib-support] Invalid Query or Bug ? |
|---|---|
| Author | Helen Borrie |
| Post date | 2002-02-17T02:42:51Z |
At 06:45 PM 16-02-02 -0700, you wrote:
Exactly how the intermediate set for the DISTINCT is held in memory I'm not sure but I think that, in order to be able to determine a DISTINCT set involving the correlated sub-query, the engine would have to hold simultaneous access to the same subquery on Y for each occurrence of A in table X . Since DISTINCT cannot be determined until all of the candidate members for the comparison set are formed, it would have to keep recursing into a (somehow) freshly aliased subselect for each possible correlation between X and Y. I can't imagine how it could do that.
Another element that might be here is that TABLE is a single table and X and Y are aliases (which your pseudocode seems to suggest...) - re-entrant query. If this is the case, then the interdependencies/scalarity of the correlation might be even more convoluted.
If you want a DISTINCT output set I think you will have to use a JOIN in order to eliminate the potential for an error to occur in the intermediate set.
If not a re-entrant query:
SELECT DISTINCT X.A, Y.B
FROM X
JOIN Y ON X.A = Y.B
If it is a re-entrant query:
SELECT DISTINCT X.A, Y.B
FROM atable X
JOIN atable Y ON X.A = Y.B
(but it's still possible that the join-correlation will be absurd in the DISTINCT context if the query is re-entrant. My blood sugar is a bit low at the moment to get too focused on that!)
Just some pre-Sunday-brunch thoughts, really.
H.
All for Open and Open for All
Firebird Open SQL Database ยท http://firebirdsql.org
_______________________________________________________
>Reduced to simplest form, I ran intoDiane B. will be the one to pronounce on this re standards per "expected behaviour" but my first take is that it's an invalid query. A correlated subquery, by nature, must always be scalar (1:1). DISTINCT implies a non-singleton select, hence the context error.
>a problem
>
>SELECT A,
>(SELECT Y.B FROM TABLE Y WHERE Y.B = X.A)
>FROM TABLE X
>
>Works fine .. but
>
>SELECT DISTINCT A,
>(SELECT Y.B FROM TABLE Y WHERE Y.B = X.A)
>FROM TABLE X
>
>produces following error
>
>ISC ERROR CODE:335544343
>
>ISC ERROR MESSAGE:
>invalid request BLR at offset ..
>context already in use (BLR error)
>
>STATEMENT:
>TIB_Cursor: "frmWISQL.crEdit"
>
>Is this an Invalid Query or Bug ?
Exactly how the intermediate set for the DISTINCT is held in memory I'm not sure but I think that, in order to be able to determine a DISTINCT set involving the correlated sub-query, the engine would have to hold simultaneous access to the same subquery on Y for each occurrence of A in table X . Since DISTINCT cannot be determined until all of the candidate members for the comparison set are formed, it would have to keep recursing into a (somehow) freshly aliased subselect for each possible correlation between X and Y. I can't imagine how it could do that.
Another element that might be here is that TABLE is a single table and X and Y are aliases (which your pseudocode seems to suggest...) - re-entrant query. If this is the case, then the interdependencies/scalarity of the correlation might be even more convoluted.
If you want a DISTINCT output set I think you will have to use a JOIN in order to eliminate the potential for an error to occur in the intermediate set.
If not a re-entrant query:
SELECT DISTINCT X.A, Y.B
FROM X
JOIN Y ON X.A = Y.B
If it is a re-entrant query:
SELECT DISTINCT X.A, Y.B
FROM atable X
JOIN atable Y ON X.A = Y.B
(but it's still possible that the join-correlation will be absurd in the DISTINCT context if the query is re-entrant. My blood sugar is a bit low at the moment to get too focused on that!)
Just some pre-Sunday-brunch thoughts, really.
H.
All for Open and Open for All
Firebird Open SQL Database ยท http://firebirdsql.org
_______________________________________________________