Re: Re[2]: to_number question

From: Dan Tow <dantow_at_singingsql.com>
Date: Thu, 15 Jul 2004 12:06:23 -0500
Message-ID: <1089911183.40f6b98f6de2e@www.singingsql.com>

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_at_gennick.com>:

> Thursday, July 15, 2004, 8:08:04 AM, Stephen.Lee_at_DTAG.Com
> (Stephen.Lee_at_DTAG.Com) 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@gennick.com
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org 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
-----------------------------------------------------------------

Received on Thu Jul 15 2004 - 12:03:19 CDT