#---- Bug 215 ----
SET STORAGE_ENGINE='Falcon';
drop table if exists t1,t2;
CREATE TABLE t1 (
number bigint(20) NOT NULL default '0',
cname char(15) NOT NULL default '',
carrier_id smallint(6) NOT NULL default '0'
);
INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90);
INSERT INTO t1 VALUES (650,'San Francisco',0);
INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90);
INSERT INTO t1 VALUES (333,'tubs',99);
CREATE TABLE t2 (
number bigint(20) NOT NULL default '0',
cname char(15) NOT NULL default '',
carrier_id smallint(6) NOT NULL default '0'
);
INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0);
INSERT INTO t2 VALUES (650,'San Francisco',90);
INSERT INTO t2 VALUES (333,'tubs',99);
select number, cname, carrier_id from t1 ORDER BY number, cname, carrier_id;
number	cname	carrier_id
333	tubs	99
650	San Francisco	0
302467	Sue's Subshop	90
4077711111	SeanWheeler	90
select number, cname, carrier_id from t2 ORDER BY number, cname, carrier_id;
number	cname	carrier_id
333	tubs	99
650	San Francisco	90
4077711111	SeanWheeler	0
select *
from t1 left join t2 on t1.number=t2.number 
where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or  (t1.carrier_id=90 and t2.number is null) 
ORDER BY t1.number, t1.cname, t1.carrier_id;
number	cname	carrier_id	number	cname	carrier_id
650	San Francisco	0	650	San Francisco	90
302467	Sue's Subshop	90	NULL	NULL	NULL
4077711111	SeanWheeler	90	4077711111	SeanWheeler	0
delete t1, t2 from t1 left join t2 on t1.number=t2.number 
where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or  (t1.carrier_id=90 and t2.number is null);
select number, cname, carrier_id from t1 ORDER BY number, cname, carrier_id;
number	cname	carrier_id
333	tubs	99
select number, cname, carrier_id from t2 ORDER BY number, cname, carrier_id;
number	cname	carrier_id
333	tubs	99
drop table t1,t2;
CREATE TABLE t1 (
number bigint(20) NOT NULL default '0',
cname char(15) NOT NULL default '',
carrier_id smallint(6) NOT NULL default '0',
privacy tinyint(4) NOT NULL default '0',
last_mod_date timestamp NOT NULL,
last_mod_id smallint(6) NOT NULL default '0',
last_app_date timestamp NOT NULL,
last_app_id smallint(6) default '-1',
version smallint(6) NOT NULL default '0',
assigned_scps int(11) default '0',
status tinyint(4) default '0'
);
INSERT INTO t1 VALUES (4077711111,'SeanWheeler',90,2,20020111112846,500,00000000000000,-1,2,3,1);
INSERT INTO t1 VALUES (9197722223,'berry',90,3,20020111112809,500,20020102114532,501,4,10,0);
INSERT INTO t1 VALUES (650,'San Francisco',0,0,20011227111336,342,00000000000000,-1,1,24,1);
INSERT INTO t1 VALUES (302467,'Sue\'s Subshop',90,3,20020109113241,500,20020102115111,501,7,24,0);
INSERT INTO t1 VALUES (6014911113,'SudzCarwash',520,1,20020102115234,500,20020102115259,501,33,32768,0);
INSERT INTO t1 VALUES (333,'tubs',99,2,20020109113440,501,20020109113440,500,3,10,0);
CREATE TABLE t2 (
number bigint(20) NOT NULL default '0',
cname char(15) NOT NULL default '',
carrier_id smallint(6) NOT NULL default '0',
privacy tinyint(4) NOT NULL default '0',
last_mod_date timestamp NOT NULL,
last_mod_id smallint(6) NOT NULL default '0',
last_app_date timestamp NOT NULL,
last_app_id smallint(6) default '-1',
version smallint(6) NOT NULL default '0',
assigned_scps int(11) default '0',
status tinyint(4) default '0'
);
INSERT INTO t2 VALUES (4077711111,'SeanWheeler',0,2,20020111112853,500,00000000000000,-1,2,3,1);
INSERT INTO t2 VALUES (9197722223,'berry',90,3,20020111112818,500,20020102114532,501,4,10,0);
INSERT INTO t2 VALUES (650,'San Francisco',90,0,20020109113158,342,00000000000000,-1,1,24,1);
INSERT INTO t2 VALUES (333,'tubs',99,2,20020109113453,501,20020109113453,500,3,10,0);
select * from t1 ORDER BY number, cname, carrier_id;
number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
333	tubs	99	2	2002-01-09 11:34:40	501	2002-01-09 11:34:40	500	3	10	0
650	San Francisco	0	0	2001-12-27 11:13:36	342	0000-00-00 00:00:00	-1	1	24	1
302467	Sue's Subshop	90	3	2002-01-09 11:32:41	500	2002-01-02 11:51:11	501	7	24	0
4077711111	SeanWheeler	90	2	2002-01-11 11:28:46	500	0000-00-00 00:00:00	-1	2	3	1
6014911113	SudzCarwash	520	1	2002-01-02 11:52:34	500	2002-01-02 11:52:59	501	33	32768	0
9197722223	berry	90	3	2002-01-11 11:28:09	500	2002-01-02 11:45:32	501	4	10	0
select * from t2 ORDER BY number, cname, carrier_id;
number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
333	tubs	99	2	2002-01-09 11:34:53	501	2002-01-09 11:34:53	500	3	10	0
650	San Francisco	90	0	2002-01-09 11:31:58	342	0000-00-00 00:00:00	-1	1	24	1
4077711111	SeanWheeler	0	2	2002-01-11 11:28:53	500	0000-00-00 00:00:00	-1	2	3	1
9197722223	berry	90	3	2002-01-11 11:28:18	500	2002-01-02 11:45:32	501	4	10	0
select * from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or  (t1.carrier_id=90 and t2.number is null) ORDER BY t1.number, t1.cname, t1.carrier_id;
number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status	number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
650	San Francisco	0	0	2001-12-27 11:13:36	342	0000-00-00 00:00:00	-1	1	24	1	650	San Francisco	90	0	2002-01-09 11:31:58	342	0000-00-00 00:00:00	-1	1	24	1
302467	Sue's Subshop	90	3	2002-01-09 11:32:41	500	2002-01-02 11:51:11	501	7	24	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
4077711111	SeanWheeler	90	2	2002-01-11 11:28:46	500	0000-00-00 00:00:00	-1	2	3	1	4077711111	SeanWheeler	0	2	2002-01-11 11:28:53	500	0000-00-00 00:00:00	-1	2	3	1
9197722223	berry	90	3	2002-01-11 11:28:09	500	2002-01-02 11:45:32	501	4	10	0	9197722223	berry	90	3	2002-01-11 11:28:18	500	2002-01-02 11:45:32	501	4	10	0
delete t1, t2 from t1 left join t2 on t1.number=t2.number where (t1.carrier_id=90 and t1.number=t2.number) or (t2.carrier_id=90 and t1.number=t2.number) or  (t1.carrier_id=90 and t2.number is null);
select * from t1 ORDER BY number, cname, carrier_id;
number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
333	tubs	99	2	2002-01-09 11:34:40	501	2002-01-09 11:34:40	500	3	10	0
6014911113	SudzCarwash	520	1	2002-01-02 11:52:34	500	2002-01-02 11:52:59	501	33	32768	0
select * from t2 ORDER BY number, cname, carrier_id;
number	cname	carrier_id	privacy	last_mod_date	last_mod_id	last_app_date	last_app_id	version	assigned_scps	status
333	tubs	99	2	2002-01-09 11:34:53	501	2002-01-09 11:34:53	500	3	10	0
drop table t1,t2;
