If the optimiser produces the wrong answer, I'd call that a bug.
Am I being naïve?
Printable View
If the optimiser produces the wrong answer, I'd call that a bug.
Am I being naïve?
PADDERS, You got it.
I don't like to write SQL statements using EMP, USER_OBJECTS etc. Some times we may get correct result, some times not.
Tamil
> Am I being naïve?
It's not an unreasonable expectation. Have a read of Johnathan Gennick's Subquery Madness (not to mention the articles it links to).
http://five.pairlist.net/pipermail/o...04/000012.html
Thanks padders - got it.
Yes, I've had the problem where filtering "invalid" rows fails to work, Ã* la Gennick.
The problem here is that dbms_random is not a deterministic** function (just as one requires for FBI's).
(** that's perhaps not the right word ****).
(**** it is - see create function)
That sounds like a philisophical statement.Quote:
Originally posted by DaPi
The problem here is that dbms_random is not a deterministic
If dbms_random was deterministic would it still be random?
You must be kidding, right? Did I test it? Well, not only that I tested it, I also specified where I tested it in my post together with the results. I specified the release I was testing it against, you didn't. You simply claimed "It doesn't work with 9i", and that raised my eyebrows, as I realy couldn't see any reason why it shouldn't work with 9i. So I tested it on 9.0.1 (AFAIK this release falls into 9i category, doesn't it) and it worked. And it still works, 100%. I didn't test it only on scott.emp, I tested it on various small and large tables, and it consistently returns correct resutlts - M is allways less than N! Noone, not the original poster, nor you nor anyone else ever mentioned the exact release where the query should actualy be used - all it was said was that "it doesn't work on 9i". So I used the first 9i release that I found running on my PC and it just happened that it was 9.0.1. And I tested it on it, and I demonstrated that it works and I prooved you vere wrong. So much about testing-before-posting from my side.Quote:
Originally posted by tamilselvan
Modic, Did you test the code before you posted here?
Now you demonstrated it doesn't work correctly (which is something different from simply saying "it doesn't work" with the additional mumbling something about DIMENSION as being the reason....) on 9.2.0.5. So we an conclude that it does work in 9i and 10g, however it returns the incorrect results on 9Rel2 (at least on 9.2.0.5, don't know about other 9.2 patchsets), while it returns perfectly correct results in both 9.0 and 10.1.
DaPi pointed out the only correct conclusion from all this - it is a bug in 9.2.0.5 optimizer, which obviously is not the case with 9.0.1 nor with 10.1. It is a bug, period. The article from Jonathan Gennick (and further references on Asktom) could not dispute this fact. It is a bug.
Why? Because the case we are observing here in this thread doesn't return any error, it simply return the wrong result. The result it returns in 9.2 is in clear contradiction with the logic of the query. The logic of the query, as it is written, is 100% correct and noone can deny this. But the result that 9.2 returns does not follow the logic of the query, so by any criteria this is a bug.
On the other hand, the cases that Gennick and Tom are dealing with can not be caracterised as bugs. There oracle returns correct results with one execution path, while returns an error with another execution path (and btw, the error reported with those alternate execution plan were totaly legitimate for that particular execution path).
If oracle gives you a resultset that is obviously incorrect, than the case falls 100% into "the bug category". Someone here should perhaps report raise a TAR and report this as a bug. Good chances are that it has been reported allready (as it is obviously fixed in 10g), though.
Actually most random number generators ARE deterministic! (I don't have a db to hand, so I can't check the code of dbms_random). The result depends on the input parameters and a hidden number that is intialised with the SEED and updated with each call. This means you can have repeatable results for testing by using a fixed seed and non-repeatble results for real-life by setting the seed with (e.g.) sysdate.Quote:
Originally posted by gandolf989
That sounds like a philisophical statement.
If dbms_random was deterministic would it still be random?
Strictly speaking such output is chaotic, not random.
(I've now had some worrying thoughts about SYSDATE - a non-deterministic function . . . . . more later, when I've experimented.)
> which obviously is not the case with 9.0.1 nor with 10.1
The behaviour certainly appears consistently different. However just creating another non-deterministic function over DBMS_RANDOM.VALUE returns us to the same behaviour...
And how about this one?Code:Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> CREATE OR REPLACE FUNCTION random_value (
2 low IN NUMBER, high IN NUMBER)
3 RETURN NUMBER
4 IS
5 BEGIN
6 RETURN DBMS_RANDOM.VALUE (low, high);
7 END;
8 /
Function created.
SQL> SELECT n, TRUNC (random_value (0, n)) m
2 FROM (SELECT TRUNC (random_value (1, 7)) n
3 FROM emp);
N M
---------- ----------
5 1
3 1
3 0
4 1
6 1
4 0
1 1
2 0
3 1
1 2 <--
1 3
4 1
3 3
2 4 <--
14 rows selected.
SQL>
Looks good so far...Code:Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> CREATE OR REPLACE FUNCTION random_value (
2 low IN NUMBER, high IN NUMBER)
3 RETURN NUMBER
4 IS
5 BEGIN
6 RETURN DBMS_RANDOM.VALUE (low, high);
7 END;
8 /
Function created.
SQL> SELECT n, TRUNC (random_value (0, n)) m
2 FROM (SELECT TRUNC (random_value (1, 7)) n
3 FROM emp);
N M
---------- ----------
5 1
3 1
3 0
4 1
6 1
4 0
1 1
2 0
3 1
1 2
1 3
4 1
3 3
2 4
14 rows selected.
SQL> SELECT *
2 FROM (SELECT n, TRUNC (DBMS_RANDOM.VALUE (0, n)) m
3 FROM (SELECT TRUNC (DBMS_RANDOM.VALUE (1, 7)) n
4 FROM emp))
5 WHERE m > n;
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL>
Ooops.Code:SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
no rows selected
SQL> /
N M
---------- ----------
4 3
1 0
3 2
3 1
1 0
2 1
5 0
2 1
4 0
1 0
4 3
4 0
5 4
3 2
14 rows selected.
SQL>
You are right. It apears that the bug is not (entirely) solved in 10g either. It looks that oracle's optimizer in certain circumstances still performs view merging when it clearly shouldn't. And your last example even shows that maybe view merging is not the culprit of the bug as it sometimes returns totaly wrong resultset even when view-merging does not take place.
In fact, very similar case as the one we discuss here is reported as a bug 3800878 (actualy a duplicate of bug 2391219, "INCONSISTENT RESULT WITH DBMS_RANDOM AS PREDICATE") on 10.1.0.2 on Metalink.
!Working.......
!Not working...
!Optimizer.....
!Bug...........
!Release.......
Hmmm, what else is pending to discuss?
Thanks PADDERS.
Tamil