to_number question 2004-07-15 - By Dan Tow
I 've been walking backwards through this thread, and seeing the long
back-and-forth about whether an RDBMS should be able to transform
order-of-execution or stick with what SQL seems to imply (make sure subqueries
are treated as independent tables, when it might matter to the result). The
issue here is not so much that the transformation changes the rows theoretically
returned (which is never allowed, of course), but that it sometimes results in
an error performing the to_number() function, and sometimes does not, because
it sometimes "sees " a row (which doesn 't successfully convert to number) that
it would later throw out, and sometimes does not (taking a path that avoids
touching it in the first place). I 've certainly run into the issue, myself, and
it 's a pain in the neck. I find
that I can work around it by nesting functions correctly and counting on Oracle
(as it does) to evaluate inner functions before outer ones.
However, that 's an ugly workaround that should not be necessary in a case like
this, so here 's a modest proposal:
When an RDBMS generates an error evaluating some function (dividing by zero,
converting something that doesn 't convert,...) instead of instantly returning
an error, it should just silently *mark* the row as a
potentially-error-generating row and continue processing. If the row is *later*
discarded because (perhaps after some joins and filtering the on the
joined-table columns, or just evaluating some more conditions on the same
table) some WHERE-clause condition (which is evaluated unambiguously *without*
getting an error) is false for that row, then the RDBMS "forgets " the error - I
reason that since the error was generated on a row the application wasn 't
asking for, anyway, it is irrelevant to the query. If the RDBMS *completes* the
potentially-error-generating row to be returned without discarding it, *then*
it should return the error! (Note that it does not have to actually wait to the
point where it would *return* the result-set row - that would force the error
to wait for reading all rows and sorting them, in most ORDER-BY cases, and
that 's not necessary, since we know the ORDER-BY won 't change the fact the
result set contains an error-generating row.)
I believe this change in behavior would trump the dilemna of whether to allow
transformations in these cases - the transformations are allowed any time they
cannot change the result set, and they will never change the result set, *nor*
will they result in an error (which might not otherwise happen, without the
transformation) along the way to returning the result set.
Dan Tow
650-858-1557
www.singingsql.com
Quoting Jonathan Gennick <jonathan@(protected) >:
> Thursday, July 15, 2004, 8:08:04 AM, Stephen.Lee@(protected)
> (Stephen.Lee@(protected)) wrote:
> SLDC > I believe the purpose of the subquery was to "request
> SLDC > it ". The function was being applied to data that was
> SLDC > not in the subquery. If this is part of the laws of
> SLDC > SQL processing, "then the law is an ass ". I 'm more
> SLDC > inclined to say that this is a bug.
>
> Stephen 's original problem and Tanel 's solution are
> fascinating. I can see where the optimizer might try and
> combine Stephen 's main query and subquery into just one query.
> However, it seems to me that an optimization should *never*
> return different results from the original operation that is
> being optimized.
>
> This begs the question of how the optimizer should decide
> whether it 's safe to merge a subquery and main query.
> Clearly, the optimizer seems to have made the wrong call
> in the case of the query we 've been talking about.
>
> I wish I had more time to spend thinking about all this, but
> I 've got to get to work for the day.
>
> Best regards,
>
> Jonathan Gennick --- Brighten the corner where you are
> http://Gennick.com * 906.387.1698 * mailto:jonathan@(protected)
>
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
