set tidb_cost_model_version=1;
drop table if exists person;
create table person (
id int,
name varchar(32),
billing_address_id int,
shipping_address_id int,
room_id int
);
drop table if exists address;
create table address (
id int,
state varchar(32),
city varchar(32),
phone1_id int,
phone2_id int,
addr_type int,
caution varchar(64)
);
drop table if exists zip;
create table zip (
state varchar(32),
city varchar(32),
zip_code int
);
drop table if exists phone;
create table phone (
id int,
phone varchar(32),
area_code varchar(2)
);
drop table if exists pet;
create table pet (
id int,
owner_id int,
name varchar(32),
room_id int
);
drop table if exists state_bird;
create table state_bird (
state varchar(32),
bird varchar(32)
);
drop table if exists room;
create table room (
room_id int,
room_name varchar(32)
);
drop table if exists brand;
create table brand (
id int,
name varchar(32)
);
drop table if exists product;
create table product (
product_id int,
product_name varchar(32),
brand_id int
);
insert into room (room_id, room_name) values (31, 'Sakura');
insert into room (room_id, room_name) values (32, 'Ume');
insert into room (room_id, room_name) values (33, 'Tsubaki');
insert into pet (id, owner_id, name, room_id) values (100, 1, 'Kotetsu', 32);
insert into pet (id, owner_id, name, room_id) values (101, 1, 'Chien', null);
insert into pet (id, owner_id, name, room_id) values (102, 3, 'Dodo', 31);
insert into phone (id, phone, area_code) values (1000, '0123', '11');
insert into phone (id, phone, area_code) values (1001, '4567', '33');
insert into phone (id, phone, area_code) values (1002, '8888', '55');
insert into phone (id, phone, area_code) values (1003, '9999', '77');
insert into state_bird (state, bird) values ('IL', 'Cardinal');
insert into state_bird (state, bird) values ('CA', 'California Valley Quail');
insert into state_bird (state, bird) values ('TX', 'Mockingbird');
insert into zip (state, city, zip_code) values ('IL', 'Chicago', 81);
insert into zip (state, city, zip_code) values ('CA', 'San Francisco', 82);
insert into zip (state, city, zip_code) values ('CA', 'Los Angeles', 83);
insert into zip (state, city, zip_code) values ('TX', 'Dallas', 84);
insert into address (id, state, city, phone1_id, phone2_id, addr_type, caution) values (10, 'IL', 'Chicago', 1000, 1001, 0, null);
insert into address (id, state, city, phone1_id, phone2_id, addr_type, caution) values (11, 'CA', 'San Francisco', 1002, null, 1, 'Has a big dog.');
insert into address (id, state, city, phone1_id, phone2_id, addr_type, caution) values (12, 'CA', 'Los Angeles', null, null, 1, 'No door bell.');
insert into address (id, state, city, phone1_id, phone2_id, addr_type, caution) values (13, 'TX', 'Dallas', 1003, 1001, 0, null);
insert into person (id, name, billing_address_id, shipping_address_id, room_id) values (1, 'John', 10, 11, 33);
insert into person (id, name, billing_address_id, shipping_address_id, room_id) values (2, 'Rebecca', 12, null, null);
insert into person (id, name, billing_address_id, shipping_address_id, room_id) values (3, 'Keith', null, 13, null);
insert into brand (id, name) values (1, 'alpha');
insert into product (product_id, product_name, brand_id) values (10, 'alpha', 1);
insert into product (product_id, product_name, brand_id) values (20, 'beta', 1);
select
person.id as person_id,
person.name as person_name,
billing_address.id as b_id,
billing_address.state as b_state,
billing_address.city as b_city,
billing_address.addr_type as b_addr_type,
billing_address.caution as b_caution,
case
when billing_address.phone1_id is null
and billing_address.phone2_id is null
then false
else true
end as b_has_phone,
shipping_address.id as s_id,
shipping_address.state as s_state,
shipping_address.city as s_city,
shipping_address.addr_type as s_addr_type,
shipping_address.caution as s_caution,
case
when shipping_address.phone1_id is null
and shipping_address.phone2_id is null
then false
else true
end as s_has_phone,
pet.id as pet_id,
pet.name as pet_name,
1
from person
left join address billing_address
on billing_address.id = person.billing_address_id
left join address shipping_address
on shipping_address.id = person.shipping_address_id
left join pet on pet.owner_id = person.id
order by person.id, pet.id;
person_id	person_name	b_id	b_state	b_city	b_addr_type	b_caution	b_has_phone	s_id	s_state	s_city	s_addr_type	s_caution	s_has_phone	pet_id	pet_name	1
1	John	10	IL	Chicago	0	NULL	1	11	CA	San Francisco	1	Has a big dog.	1	100	Kotetsu	1
1	John	10	IL	Chicago	0	NULL	1	11	CA	San Francisco	1	Has a big dog.	1	101	Chien	1
2	Rebecca	12	CA	Los Angeles	1	No door bell.	0	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	1
3	Keith	NULL	NULL	NULL	NULL	NULL	0	13	TX	Dallas	0	NULL	1	102	Dodo	1