Raj, It's early in the morning and I may not be thinking straight yet, but...
It's certainly interesting that the group by in the location shown changes
the results - obviously a max() function should return 1 row only and
therefore not require a group by. I'm more interested though in the second
clause of your query... You have a second table aliased to "b" which isn't
even used in the subquery, and you have a "and 1 != 1" clause.
Is this a trick to get a particular result set? I would have thought that
"and 1 != 1" will always return false and therefore the subquery will never
return a row. Does the fact that one table is not joined to (ie: is a
cartesian instead) somehow affect this? What business rule would a query
like this implement? I ask purely because I'm always looking for new ways
to express business rules in queries, just recently I learnt how to use an
analytical function to return only the first two rows from a child table
for each different requested parent in a single query.
I'm wondering if you have hit a bug by reusing the alias "b" as a cartesian
in a subquery - just trying to think of things the Oracle folk may never
have tested for. If adding the group by changes those results though then
yes, you seem to have found a bug of some sort, regardless of the query.
Cheers.
"Jamadagni,
Rajendra" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
<Rajendra.Jamadagni cc:
@espn.com> Subject: RE: Urgent problem with
query in CBO Vs RBO
Sent by:
[EMAIL PROTECTED]
17/10/2002 23:13
Please respond to
ORACLE-L
Here is an example ...
RULE Hint works ... but we can't find all the SQLs that will fail. The only
other option suggested by OWS is setting optimizer_features_enable='8.1.6'.
It works fine ...
Raj
--------------- start ----------------------------
set feedback off
set define off
drop table mjc_junk;
drop table mjc_mst;
prompt Creating MJC_JUNK...
-- Create table
create table MJC_JUNK
(
COLA NUMBER(9)
);
prompt Creating MJC_MST...
-- Create table
create table MJC_MST
(
COLA NUMBER(9) not null,
COLB NUMBER(9) not null,
CDT DATE
);
prompt Loading MJC_MST...
insert into MJC_MST (COLA, COLB, CDT)
values (1284457, 213841, to_date('06-06-2001 09:28:40', 'dd-mm-yyyy
hh24:mi:ss'));
commit;
prompt 41 records loaded
declare
begin
sys.dbms_stats.gather_table_stats(ownname => null,
tabname => 'MJC_JUNK',
estimate_percent => 20,
block_sample => false,
method_opt => 'FOR ALL COLUMNS',
degree => 4,
granularity => 'DEFAULT',
cascade => true);
end;
/
set feedback on
set define on
prompt This query returns invalid results:
select sUM(a.colb)
FROM mjc_mst a
WHERE a.colb = 213841
AND a.cdt = (SELECT MAX(b.cdt)
FROM mjc_mst b
WHERE b.cola = a.cola
AND b.colb = a.colb
)
and exists ( select 1 from mjc_junk b, mjc_junk m where
a.colb = m.cola
and 1 != 1);
prompt This query that returns valid results:
select sum(a.colb)
FROM mjc_mst a
WHERE a.colb = 213841
AND a.cdt = (SELECT MAX(b.cdt)
FROM mjc_mst b
WHERE b.cola = a.cola
AND b.colb = a.colb
group by 1
)
and exists ( select 1 from mjc_junk b, mjc_junk m where
a.colb = m.cola
and 1 != 1)
prompt Go figure.
------------ end ----------------------
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN
Inc.
QOTD: Any clod can have facts, but having an opinion is an art! (See
attached file: ESPN_Disclaimer.txt)
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
ESPN_Disclaimer.txt
Description: Binary data
