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
