Hi Shawn-
First, thanks for responding. You're re-written query works as I would
expect, even using IFNULL instead of COASLESCE (see PS:).
I'm not sure I explained my issue well enough. Basically, I feel that outer
joins with correlated sub-queries using not exists are broken in MySQL.
The NOT EXISTS clause is filtering out records that have effective dates
that are 1) later than the transaction date, and 2) have matching records
with earlier effective dates. (Note: In Oracle, the EXPLAIN shows that this
is what happens and that the query was not internally rewritten by
Horracle.) Basically, it's picking out the record that was effective at the
time of the transaction.
I've always had a fun time with NOT EXISTS...kind of mind-bending at times.
I swear it works like a dream in Horracle (both 8.x and 9.x, haven't tried
it in 10.x).
Thanks again for looking at this. The left join syntax in MySQL is new to
me and a little challenging at times - your example helps a lot. Although I
still feel that there's some sort of problem with the way MySQL is handling
NOT EXISTS for this case.
Best regards,
Rick
PS:
There's no IsNULL() function in MySQL that I know of. I'm using IFNULL() -
according to the doc:
"IFNULL(expr1,expr2) : If expr1 is not NULL, IFNULL() returns expr1, else it
returns expr2."
Rewritten query (with ifnull):
select
a.id id,
a.trandate trandate,
a.acct acct,
a.rateplan rateplan,
ifnull(a.rangeid, 99999) rangeid,
case ifnull(a.rangeid, 99999) when 99999 then 'null rangeid' else
ifnull(b.descr, 'null descr') end descr
from
transactions a
left join ref_info b
on a.rateplan = b.rateplan
and a.rangeid = b.rangeid
and a.trandate > b.effdate
left join ref_info c
on c.rateplan = b.rateplan
and c.rangeid = b.rangeid
and c.effdate < a.trandate
and c.effdate > b.effdate
where
a.acct = 123
and c.rateplan is null
order by 2 desc, 1 desc
;
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, June 11, 2004 3:05 PM
To: [EMAIL PROTECTED]
Cc: Mysql
Subject: Re: Possible problem with outer join in 4.1.2
Hi Rick,
First, the MySQL IsNULL() function does not operate like the ORACLE or MS
SQL version. It is merely a test and returns either 1 or 0. You will need to
use COALESCE() to provide a non-null replacement for a null value.
I am not sure what you are trying to accomplish with your EXISTS() clause
but that was what was killing you. I tried it both as EXISTS and as NOT
EXISTS and I didn't get *any* records where rangeid=4. I can't fully
explain the behavior but I think its failure is linked to the fact that
there are zero b table rows for rangeid of 4.
I rewrote your exclusion as another LEFT JOIN and I have the right number of
rows but I need you to verify the results:
select a.id id,
a.trandate trandate,
a.acct acct,
a.rateplan rateplan,
a.rangeid rangeid,
case coalesce(a.rangeid, 99999)
when 99999 then 'null rangeid'
else coalesce(b.descr, 'null descr') end descr from transactions a
left outer join ref_info b
on a.rateplan = b.rateplan
and a.rangeid = b.rangeid
and a.trandate > b.effdate
left join ref_info c
on c.rateplan = b.rateplan
and c.rangeid = b.rangeid
and c.effdate < a.trandate
and c.effdate > b.effdate
where a.acct =123
and c.rateplan is null;
Let me know if this worked as you wanted. I will keep trying to figure out
why that EXISTS clause failed....
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Rick Robinson"
<[EMAIL PROTECTED]> To: "Mysql"
<[EMAIL PROTECTED]>
cc:
06/11/2004 12:48 Fax to:
PM Subject: Possible problem
with outer join in 4.1.2
Please respond to
rick
Hi all-
I'm not certain if this is a bug in MySQL, a bug in Oracle, or a possible
miscoding of my outer join, but I have a scenario in which I've replicated a
table set up and query from an Oracle application and I'm not getting the
same result set. The following script sets up the representative tables and
data and has the query.
drop table if exists ref_info;
drop table if exists transactions;
create table ref_info
(
rateplan char(3) not null,
rangeid int not null,
effdate datetime not null,
descr char(20) not null,
primary key(rateplan, rangeid, effdate)
)
type=myisam
;
create table transactions
(
id int not null auto_increment,
acct int not null,
rateplan char(3) not null,
trandate datetime not null,
rangeid int null,
primary key(id),
key(acct, rateplan, trandate)
)
type=myisam
;
insert into ref_info values
('aaa',1,'1970-01-01','aaa:1:1970-01-01'),
('aaa',1,'2004-06-01','aaa:1:2004-06-01'),
('aaa',1,'2004-06-03','aaa:1:2004-06-03'),
('aaa',1,'2004-06-05','aaa:1:2004-06-05'),
('aaa',2,'1970-01-01','aaa:2:1970-01-01'),
('aaa',2,'2004-06-01','aaa:2:2004-06-01'),
('aaa',2,'2004-06-03','aaa:2:2004-06-03'),
('aaa',2,'2004-06-05','aaa:2:2004-06-05'),
('aaa',3,'1970-01-01','aaa:3:1970-01-01'),
('aaa',3,'2004-06-01','aaa:3:2004-06-01'),
('aaa',3,'2004-06-03','aaa:3:2004-06-03'),
('aaa',3,'2004-06-05','aaa:3:2004-06-05'),
('aaa',5,'1970-01-01','aaa:5:1970-01-01'),
('aaa',5,'2004-06-01','aaa:5:2004-06-01'),
('aaa',5,'2004-06-03','aaa:5:2004-06-03'),
('aaa',5,'2004-06-05','aaa:5:2004-06-05')
;
insert into transactions values
(null,123,'aaa','2004-05-30',1),
(null,123,'aaa','2004-06-02',1),
(null,123,'aaa','2004-06-04',1),
(null,123,'aaa','2004-06-06',1),
(null,123,'aaa','2004-05-30',2),
(null,123,'aaa','2004-06-02',2),
(null,123,'aaa','2004-06-04',2),
(null,123,'aaa','2004-06-06',2),
(null,123,'aaa','2004-05-30',3),
(null,123,'aaa','2004-06-02',3),
(null,123,'aaa','2004-06-04',3),
(null,123,'aaa','2004-06-06',3),
(null,123,'aaa','2004-05-30',4),
(null,123,'aaa','2004-06-02',4),
(null,123,'aaa','2004-06-04',4),
(null,123,'aaa','2004-06-06',4),
(null,123,'aaa','2004-05-30',5),
(null,123,'aaa','2004-06-02',5),
(null,123,'aaa','2004-06-04',5),
(null,123,'aaa','2004-06-06',5),
(null,123,'aaa','2004-05-30',null),
(null,123,'aaa','2004-06-02',null),
(null,123,'aaa','2004-06-04',null),
(null,123,'aaa','2004-06-06',null)
;
select count(*) from transactions
;
select
a.id id,
a.trandate trandate,
a.acct acct,
a.rateplan rateplan,
ifnull(a.rangeid, 99999) rangeid,
case ifnull(a.rangeid, 99999) when 99999 then 'null rangeid' else
ifnull(b.descr, 'null descr') end descr from
transactions a left outer join ref_info b
on a.rateplan = b.rateplan and a.rangeid = b.rangeid and a.trandate >
b.effdate where
a.acct = 123
and not exists
(select 1 from ref_info c
where c.rateplan = b.rateplan
and c.rangeid = b.rangeid
and c.effdate < a.trandate
and c.effdate > b.effdate
)
order by 2 desc, 1 desc
;
On the nifty outer join query, I expect to get 24 rows back, but only get
16; all rows with either a null a.rangeid or a.rangeid=4 are missing. This
(effectively) same query works in Oracle and returns all data. (as FYI, the
query is trying to get back descriptions that are effective dated - however,
some descriptions may not be there for various reasons).
So, am I writing this query incorrectly for MySQL? Is this a possible bug?
Thanks for any help. But please skip suggestions on trying to make changes
to the schema definition.
Running MySQL 4.1.2 binary on both Solaris 2.9 and WinXP.
Best regards,
Rick
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
