
--echo #---- Bug 215 ----
SET STORAGE_ENGINE='Falcon';

#                                                                     #
# This test is derived from innodb.test                               #
#                                                                     #

--enable_abort_on_error

--disable_warnings
drop table if exists t1,t2;
--enable_warnings

#
# Test of multi-table-delete
#

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;
select number, cname, carrier_id from t2 ORDER BY number, cname, carrier_id;
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;
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;
select number, cname, carrier_id from t2 ORDER BY number, cname, carrier_id;
drop table t1,t2;

# Original testcase

#
# Test of multi-table-delete
#

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;
select * from t2 ORDER BY number, cname, carrier_id;
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;
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;
select * from t2 ORDER BY number, cname, carrier_id;
drop table t1,t2;
