Attachment "bugselect_test.sqlite" to
ticket [5ac9b497]
added by
nobody
2013-01-02 12:49:11.
-- 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;
*/