-- Illustrate some problems with aliases, fully qualified identifiers, and *
-- Uncomment the desired parts

.header on

create table main.tbl1 (col1 text primary key, col2 text);
create table main.tbl2 (col1 text primary key, col2 text);
create table main.tbl3 (col3 text primary key, col4 text);

insert into main.tbl1 values ('001', 'Jasmin');
--insert into main.tbl1 values ('002', 'Crocus');
--insert into main.tbl1 values ('003', 'Geranium');

insert into main.tbl2 values ('001', 'Jasmin');
--insert into main.tbl2 values ('002', 'Crocus');
--insert into main.tbl2 values ('003', 'Geranium');

insert into main.tbl3 values ('001', 'Jasmin');
--insert into main.tbl3 values ('002', 'Crocus');
--insert into main.tbl3 values ('003', 'Geranium');


ATTACH DATABASE ':memory:' AS aux1;

create table aux1.tbl1 (col1 text primary key, col2 text);
create table aux1.tbl2 (col1 text primary key, col2 text);
create table aux1.tbl3 (col3 text primary key, col4 text);

insert into aux1.tbl1 values ('001', 'Jasmin');
--insert into aux1.tbl1 values ('002', 'Crocus');
--insert into aux1.tbl1 values ('003', 'Geranium');

insert into aux1.tbl2 values ('001', 'Jasmin');
--insert into aux1.tbl2 values ('002', 'Crocus');
--insert into aux1.tbl2 values ('003', 'Geranium');

insert into aux1.tbl3 values ('001', 'Jasmin');
--insert into aux1.tbl3 values ('002', 'Crocus');
--insert into aux1.tbl3 values ('003', 'Geranium');



-- 1 db, 1 table (self-joined), 2 aliases (OK)
/*
select *                                   from main.tbl1 as t1 join main.tbl1 as t2 on t1.col1 = t2.col1;
select t1.*, t2.*                          from main.tbl1 as t1 join main.tbl1 as t2 on t1.col1 = t2.col1;
select t1.col1, t1.col2, t2.*              from main.tbl1 as t1 join main.tbl1 as t2 on t1.col1 = t2.col1;
select t2.col1, t2.col2, t1.*              from main.tbl1 as t1 join main.tbl1 as t2 on t1.col1 = t2.col1;
select t1.col1, t1.col2, t2.col1, t2.col2  from main.tbl1 as t1 join main.tbl1 as t2 on t1.col1 = t2.col1;
*/


-- 1 db, 1 table (self-joined), 1 alias (KO)
/*
select *                                                 from main.tbl1 as t1 join main.tbl1 on t1.col1 = main.tbl1.col1;
select t1.*, main.tbl1.*                                 from main.tbl1 as t1 join main.tbl1 on t1.col1 = main.tbl1.col1;
select t1.col1, main.tbl1.*                              from main.tbl1 as t1 join main.tbl1 on t1.col1 = main.tbl1.col1;
select t1.col1, main.tbl1.col1                           from main.tbl1 as t1 join main.tbl1 on t1.col1 = main.tbl1.col1;
select t1.col1, t1.col2, main.tbl1.*                     from main.tbl1 as t1 join main.tbl1 on t1.col1 = main.tbl1.col1;
select t1.*, main.tbl1.col1, main.tbl1.col2              from main.tbl1 as t1 join main.tbl1 on t1.col1 = main.tbl1.col1;
select t1.col1, t1.col2, main.tbl1.col1, main.tbl1.col2  from main.tbl1 as t1 join main.tbl1 on t1.col1 = main.tbl1.col1;
*/


-- 1 db, 2 tables (different name), 2 aliases (OK)
/*
select *                                   from main.tbl1 as t1 join main.tbl2 as t2 on t1.col1 = t2.col1;
select t1.*, t2.*                          from main.tbl1 as t1 join main.tbl2 as t2 on t1.col1 = t2.col1;
select t1.col1, t2.*                       from main.tbl1 as t1 join main.tbl2 as t2 on t1.col1 = t2.col1;
select t1.col1, t1.col2, t2.*              from main.tbl1 as t1 join main.tbl2 as t2 on t1.col1 = t2.col1;
select t1.*, t2.col1, t2.col2              from main.tbl1 as t1 join main.tbl2 as t2 on t1.col1 = t2.col1;
select t1.col1, t1.col2, t2.col1, t2.col2  from main.tbl1 as t1 join main.tbl2 as t2 on t1.col1 = t2.col1;
*/


-- 1 db, 2 tables (different name), 1 alias (KO)
/*
select *                                                 from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.*, main.tbl2.*                                 from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.*, tbl2.*                                      from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.col1, main.tbl2.*                              from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.col1, tbl2.*                                   from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.*, main.tbl2.col1                              from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.*, tbl2.*                                      from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.col1, tbl2.*                                   from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.col1, t1.col2, main.tbl2.*                     from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.col1, t1.col2, tbl2.*                          from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.*, main.tbl2.col1, main.tbl2.col2              from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.*, tbl2.col1, tbl2.col2                        from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.col1, t1.col2, main.tbl2.col1, main.tbl2.col2  from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
select t1.col1, t1.col2, tbl2.col1, tbl2.col2            from main.tbl1 as t1 join main.tbl2 on t1.col1 = main.tbl2.col1;
*/


-- 1 db, 2 tables (different name), without alias (KO)
/*
select *                                                               from main.tbl1 join main.tbl2 on main.tbl1.col1 = main.tbl2.col1;
select tbl1.*, tbl2.*                                                  from main.tbl1 join main.tbl2 on main.tbl1.col1 = main.tbl2.col1;
select tbl1.col1, tbl2.*                                               from main.tbl1 join main.tbl2 on main.tbl1.col1 = main.tbl2.col1;
select main.tbl1.*, tbl2.*                                             from main.tbl1 join main.tbl2 on main.tbl1.col1 = main.tbl2.col1;
select main.tbl1.*, main.tbl2.*                                        from main.tbl1 join main.tbl2 on main.tbl1.col1 = main.tbl2.col1;
select main.tbl1.col1, main.tbl1.col2, main.tbl2.*                     from main.tbl1 join main.tbl2 on main.tbl1.col1 = main.tbl2.col1;
select main.tbl1.col1, main.tbl1.col2, tbl2.*                          from main.tbl1 join main.tbl2 on main.tbl1.col1 = main.tbl2.col1;
select tbl1.col1, tbl1.col2, main.tbl2.*                               from main.tbl1 join main.tbl2 on main.tbl1.col1 = main.tbl2.col1;
select tbl1.col1, main.tbl1.col2, tbl2.*                               from main.tbl1 join main.tbl2 on main.tbl1.col1 = main.tbl2.col1;
select tbl1.col1, tbl1.col2, tbl2.*                                    from main.tbl1 join main.tbl2 on main.tbl1.col1 = main.tbl2.col1;
select main.tbl1.col1, main.tbl1.col2, main.tbl2.col1, main.tbl2.col2  from main.tbl1 join main.tbl2 on main.tbl1.col1 = main.tbl2.col1;
select tbl1.col1, tbl1.col2, tbl2.col1, tbl2.col2                      from main.tbl1 join main.tbl2 on main.tbl1.col1 = main.tbl2.col1;
*/


-- 1 db, 2 tables (different name and different columns), without alias (KO)
/*
select *                                                               from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select tbl1.*, tbl3.*                                                  from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select main.tbl1.*, tbl3.*                                             from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select main.tbl1.*, main.tbl3.*                                        from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select main.tbl1.col1, main.tbl1.col2, main.tbl3.*                     from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select main.tbl1.col1, main.tbl1.col2, tbl3.*                          from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select tbl1.col1, tbl1.col2, main.tbl3.*                               from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select tbl1.col1, main.tbl1.col2, tbl3.*                               from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select tbl1.col1, tbl1.col2, tbl3.*                                    from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select tbl1.*, tbl3.col3, tbl3.col4                                    from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select main.tbl1.col1, main.tbl1.col2, main.tbl3.col3, main.tbl3.col4  from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select main.tbl1.col1, tbl1.col2, tbl3.col3, main.tbl3.col4            from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select main.tbl1.col1, col2, tbl3.col3, main.tbl3.col4                 from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select tbl1.col1, tbl1.col2, tbl3.col3, tbl3.col4                      from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
select col1, col2, col3, col4                                          from main.tbl1 join main.tbl3 on main.tbl1.col1 = main.tbl3.col3;
*/


-- 2 db, 2 tables (same name), 2 aliases (OK)
/*
select *                                   from main.tbl1 as t1 join aux1.tbl1 as a1 on t1.col1 = a1.col1;
select t1.*, a1.*                          from main.tbl1 as t1 join aux1.tbl1 as a1 on t1.col1 = a1.col1;
select t1.col1, t1.col2, a1.*              from main.tbl1 as t1 join aux1.tbl1 as a1 on t1.col1 = a1.col1;
select t1.*, a1.col1, a1.col2              from main.tbl1 as t1 join aux1.tbl1 as a1 on t1.col1 = a1.col1;
select t1.col1, t1.col2, a1.col1, a1.col2  from main.tbl1 as t1 join aux1.tbl1 as a1 on t1.col1 = a1.col1;
*/


-- 2 db, 2 tables (same name), 1 alias (KO)
/*
select *                                                 from main.tbl1 as t1 join aux1.tbl1 on t1.col1 = aux1.tbl1.col1;
select t1.*, aux1.tbl1.*                                 from main.tbl1 as t1 join aux1.tbl1 on t1.col1 = aux1.tbl1.col1;
select t1.col1, t1.col2, aux1.tbl1.*                     from main.tbl1 as t1 join aux1.tbl1 on t1.col1 = aux1.tbl1.col1;
select t1.*, aux1.tbl1.col1, aux1.tbl1.col2              from main.tbl1 as t1 join aux1.tbl1 on t1.col1 = aux1.tbl1.col1;
select t1.col1, t1.col2, aux1.tbl1.col1, aux1.tbl1.col2  from main.tbl1 as t1 join aux1.tbl1 on t1.col1 = aux1.tbl1.col1;
*/


-- 2 db, 2 tables (same name), without alias (KO)
/*
select *                                                               from main.tbl1 join aux1.tbl1 on main.tbl1.col1 = aux1.tbl1.col1;
select main.tbl1.*, aux1.tbl1.*                                        from main.tbl1 join aux1.tbl1 on main.tbl1.col1 = aux1.tbl1.col1;
select main.tbl1.*, aux1.tbl1.col1, aux1.tbl1.col2                     from main.tbl1 join aux1.tbl1 on main.tbl1.col1 = aux1.tbl1.col1;
select main.tbl1.col1, main.tbl1.col2, aux1.tbl1.*                     from main.tbl1 join aux1.tbl1 on main.tbl1.col1 = aux1.tbl1.col1;
select main.tbl1.col1, main.tbl1.col2, aux1.tbl1.col1, aux1.tbl1.col2  from main.tbl1 join aux1.tbl1 on main.tbl1.col1 = aux1.tbl1.col1;
*/


-- 2 db, 2 tables (different name), 2 aliases (OK)
/*
select *                                   from main.tbl1 as t1 join aux1.tbl2 a2 on t1.col1 = a2.col1;
select t1.*, a2.*                          from main.tbl1 as t1 join aux1.tbl2 a2 on t1.col1 = a2.col1;
select t1.*, a2.col1, a2.col2              from main.tbl1 as t1 join aux1.tbl2 a2 on t1.col1 = a2.col1;
select t1.col1, t1.col2, a2.*              from main.tbl1 as t1 join aux1.tbl2 a2 on t1.col1 = a2.col1;
select t1.col1, t1.col2, a2.col1, a2.col2  from main.tbl1 as t1 join aux1.tbl2 a2 on t1.col1 = a2.col1;
*/


-- 2 db, 2 tables (different name), 1 alias (KO)
/*
select *                                                 from main.tbl1 as t1 join aux1.tbl2 on t1.col1 = aux1.tbl2.col1;
select t1.*, aux1.tbl2.*                                 from main.tbl1 as t1 join aux1.tbl2 on t1.col1 = aux1.tbl2.col1;
select t1.*, aux1.tbl2.col1, aux1.tbl2.col2              from main.tbl1 as t1 join aux1.tbl2 on t1.col1 = aux1.tbl2.col1;
select t1.col1, t1.col2, aux1.tbl2.*                     from main.tbl1 as t1 join aux1.tbl2 on t1.col1 = aux1.tbl2.col1;
select t1.col1, t1.col2, aux1.tbl2.col1, aux1.tbl2.col2  from main.tbl1 as t1 join aux1.tbl2 on t1.col1 = aux1.tbl2.col1;
*/


-- 2 db, 2 tables (different name), without alias (KO)
/*
select *                                                               from main.tbl1 join aux1.tbl2 on main.tbl1.col1 = aux1.tbl2.col1;
select main.tbl1.*, aux1.tbl2.*                                        from main.tbl1 join aux1.tbl2 on main.tbl1.col1 = aux1.tbl2.col1;
select main.tbl1.*, aux1.tbl2.col1, aux1.tbl2.col2                     from main.tbl1 join aux1.tbl2 on main.tbl1.col1 = aux1.tbl2.col1;
select main.tbl1.col1, main.tbl1.col2, aux1.tbl2.*                     from main.tbl1 join aux1.tbl2 on main.tbl1.col1 = aux1.tbl2.col1;
select main.tbl1.col1, main.tbl1.col2, aux1.tbl2.col1, aux1.tbl2.col2  from main.tbl1 join aux1.tbl2 on main.tbl1.col1 = aux1.tbl2.col1;
*/


-- 2 db, 2 tables (different name and different columns), without alias (KO)
/*
select *                                                               from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select tbl1.*, tbl3.*                                                  from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select main.tbl1.*, tbl3.*                                             from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select main.tbl1.*, aux1.tbl3.*                                        from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select main.tbl1.col1, main.tbl1.col2, aux1.tbl3.*                     from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select main.tbl1.col1, main.tbl1.col2, tbl3.*                          from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select tbl1.col1, tbl1.col2, aux1.tbl3.*                               from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select tbl1.col1, main.tbl1.col2, tbl3.*                               from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select tbl1.col1, tbl1.col2, tbl3.*                                    from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select tbl1.*, tbl3.col3, tbl3.col4                                    from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select main.tbl1.col1, main.tbl1.col2, aux1.tbl3.col3, aux1.tbl3.col4  from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select main.tbl1.col1, tbl1.col2, tbl3.col3, aux1.tbl3.col4            from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select main.tbl1.col1, col2, tbl3.col3, aux1.tbl3.col4                 from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select tbl1.col1, tbl1.col2, tbl3.col3, tbl3.col4                      from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
select col1, col2, col3, col4                                          from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;
*/
