SQLite

View Ticket
Login
Plaintext
Ticket Hash: 5ac9b497de6881c01df2704abf3dc8200fd1e11b
Title: Some issues with aliases, fully qualified identifiers, and *
Status: Fixed Type: Feature_Request
Severity: Minor Priority: Low
Subsystem: Parser Resolution: Fixed
Last Modified: 2014-03-11 13:18:44
Version Found In: 3.7.13
User Comments:
nobody added on 2013-01-02 04:34:16:

I stumbled upon some problems with aliases, fully qualified identifiers, and *. Here is a non exhaustive script that illustrates the problem: 

 -- 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.col1, main.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;
*/

nobody added on 2013-01-02 12:47:34:

A variant of the same issue: 

-- 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;

Regards,

Stéphane Aulery

drh added on 2013-01-02 12:50:00:

As best I can determine from the description above, the OP is concerned that wildcards of the form:

DATABASE.TABLE.*

are not expanded. SQLite has never claimed to expand terms of this kind. So, the OP seems to be requesting a new feature.

nobody added on 2013-01-02 21:16:42:

Ok. This feature seemed me quite natural from the point where it is possible to attach multiple databases. In your opinion, does the following errors are also of the same lack ? 

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

-- 2 db, 2 tables (different name and different columns), without alias (KO)
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 tbl1.col1, tbl1.col2, aux1.tbl3.*           from main.tbl1 join aux1.tbl3 on main.tbl1.col1 = aux1.tbl3.col3;

Regards,

Stéphane Aulery

Attachments: