In my last blog entry “Starting off with a classic…” I presented a classic issue that may appear when the database software is upgraded.
The problem was that sometimes result sets are delivered to the user in a sorted manner even if no ORDER BY was specified. If the user/developer relies on this order it may be a cause for application errors, as the database don’t guarantee the order if ORDER BY is omitted.
Now I came across a customer message that described a problem after the upgrade from Oracle 9i to Oracle 10g which was quite similiar – but only on the second look.
THE TABLE
To understand this problem it’s handy to have a demo table.
My one looks like this:
create table tt (cdate varchar2(8), info varchar2(10));
For the test, we also need some data:
insert into tt values('20070101', 'TEST-JAN');
insert into tt values('20070104', 'TEST-JAN');
insert into tt values('20070104', 'TEST-JAN');
insert into tt values('20070104', 'TEST-JAN');
insert into tt values('20070104', 'TEST-JAN');
insert into tt values('20070124', 'TEST-JAN');
insert into tt values('20070124', 'TEST-JAN');
insert into tt values('20070124', 'TEST-JAN');
insert into tt values('20070201', 'TEST-FEB');
insert into tt values('20070201', 'TEST-FEB');
insert into tt values('20070203', 'TEST-FEB');
insert into tt values('20070212', 'TEST-FEB');
analyze table tt compute statistics;Now a quick check, what’s in the table now:
break on cdate skip 1
select * from tt order by cdate asc; CDATE INFO -------- ---------- 20070101 TEST-JAN 20070104 TEST-JAN TEST-JAN TEST-JAN TEST-JAN 20070124 TEST-JAN TEST-JAN TEST-JAN 20070201 TEST-FEB TEST-FEB 20070203 TEST-FEB 20070212 TEST-FEB
12 rows selected.
As we see there are several different dates with different numer of entries for the different dates. That’s pretty much like it was on the customer system.
The Query
The customer now wanted to know the newest cdate of the table. That is of course the one that is the highest one.
He complained that after the upgrade to Oracle 10g the database does NOT return the highest cdate value anymore. So, what happened?
What he coded was:
select max(cdate) from tt group by cdate;
The result sets looked like this:
Oracle 9i:
MAX(CDAT -------- 20070101 20070104 > 20070124 > 20070201 > 20070203 > 20070212 > 6 rows selected.
Oracle 10g:
MAX(CDAT -------- 20070203 20070212 > 20070201 < 20070101 < 20070104 > 20070124 > 6 rows selected.
For easier comparisation I marked each line with < or > denoting that the current line cdate is smalle < or bigger > than the preceding one.
So the customer made a correct observation: the result of his query was sorted before the upgrade and is not sorted afterwards. But as we already now, the order of the result set was not guaranteed since no ORDER BY clause was included.
Anyhow we want to know why this is happening…
The Analysis
With Oracle 10g the query is executed like this:
set autotrace traceonly exp select max(cdate) from tt group by cdate; ------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | 6 | | 1 | HASH GROUP BY | | 6 | | 2 | TABLE ACCESS FULL| TT | 12 | -------------------------------------------
Switching back to Oracle 9i we see that something has changed here:
alter session set optimizer_features_enable='9.2.0'; select max(cdate) from tt group by cdate; ------------------------------------------- | Id | Operation | Name | Rows | ------------------------------------------- | 0 | SELECT STATEMENT | | 6 | | 1 | SORT GROUP BY | | 6 | | 2 | TABLE ACCESS FULL| TT | 12 | -------------------------------------------
While Oracle 9i used a SORT GROUP BY operation to produce the result set Oracle 10g employs a HASH GROUP BY.
As the name already tells, the former one sorts the data for this operation thus producing a sorted resultset. So up to now we know that the result set order changed and why it has changed, but still the question is: why does it produce a wrong result for MAX()?
The answer is simple: it does not produce a wrong result at all.
The Solution
What is wrong in this example is the statement itself. The customer wanted the newest cdate value in the table. But he asked for the highest value for each different value, since he grouped his data over cdate. As he always got back a resultset that included all distinct values for cdate the developer must have scrolled through the result set to get the highest value…
The correct implementation of this question in SQL would of course be this one:
select max(cdate) from tt;
This one would deliver only one single value (SELECT SINGLE!): the highest value for cdate.
No sort order in the result set, no scrolling trough it. Just the query and the result.
As you can see, this “problem” might come up at very different corners of your SQL code.
So better doublecheck it, before assuming a database bug.
Best regards,
Lars