Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

[Radovan Antloga]

Behaviour is not consistent when using
create table as
or just select statement.
Try this simple test.

create table test(a int, b int);
insert into test values (1, 1);

select d from (select c as d from (select a as c from test));

you get column name d as expected
but when you have create table as statement

create table test2 as
select d from (select c as d from (select a as c from test));

you get table test2 with column name a.

If you change to this

create table test2 as
select d as d from (select c as d from (select a as c from test));

you will get name correct. I think it should
be the same as when just using select statement.

Best Regards
Radovan


select a from (select b from (select c from test)))

Richard Hipp je 21.12.2017 ob 14:52 napisal:

The behavior change is a bug fix.  See
http://sqlite.org/src/info/de3403bf5ae for details.

On 12/21/17, Radovan Antloga <radovan.antl...@siol.net> wrote:

I have table (create statement):

CREATE TABLE SOPP1 (
    STAT  varchar(1) collate systemnocase,
    RID  varchar(2) collate systemnocase,
    VP  integer,
    BLANK  varchar(6) collate systemnocase,
    NAZIV  varchar(24) collate systemnocase,
    KN  varchar(12) collate systemnocase,
    A  varchar(1) collate systemnocase,
    B  varchar(1) collate systemnocase,
    RACUN  varchar(1) collate systemnocase,
    URE  varchar(1) collate systemnocase,
    ZN  varchar(1) collate systemnocase,
    TOCKE  varchar(1) collate systemnocase,
    PRC  varchar(1) collate systemnocase,
    UP  varchar(1) collate systemnocase,
    IZPIS  varchar(1) collate systemnocase,
    D  varchar(1) collate systemnocase,
    F2U  varchar(1) collate systemnocase,
    F2O  varchar(1) collate systemnocase,
    F2T  varchar(1) collate systemnocase,
    F2Z  varchar(1) collate systemnocase,
    F2P_1  integer,
    F2P_2  integer,
    F2P_3  integer,
    F5  varchar(1) collate systemnocase,
    AJPES  varchar(1) collate systemnocase,
    ZZ  integer,
    VD  integer,
    NS  integer,
    MES  integer,
    NORURE  varchar(1) collate systemnocase,
    G  varchar(1) collate systemnocase,
    E  varchar(1) collate systemnocase,
    H  varchar(1) collate systemnocase,
    I  varchar(1) collate systemnocase,
    J  varchar(1) collate systemnocase,
    SM  varchar(1) collate systemnocase,
    NO  varchar(1) collate systemnocase,
    PRIO  varchar(1) collate systemnocase,
    V_1  varchar(1) collate systemnocase,
    V_2  varchar(1) collate systemnocase,
    V_3  varchar(1) collate systemnocase,
    V_4  varchar(1) collate systemnocase,
    V_5  varchar(1) collate systemnocase,
    V_6  varchar(1) collate systemnocase,
    V_7  varchar(1) collate systemnocase,
    V_8  varchar(1) collate systemnocase,
    V_9  varchar(1) collate systemnocase,
    V_10  varchar(1) collate systemnocase,
    V_11  varchar(1) collate systemnocase,
    V_12  varchar(1) collate systemnocase,
    FOR  integer,
    P_1  integer,
    P_2  integer,
    P_3  integer,
    P_4  integer,
    P_5  integer,
    P_6  integer,
    primary key (RID, VP, BLANK));

When I create new table using this sql:

drop table if exists WM4P;
create table WM4P as
select
    P, A, B, AB, U, H, ZZ,
    case
      when AB in ('7') then 99
      when AB in ('57', '58', '59', '5M') then null
      when AB = '56' and ZZ = 12 then 01
      when AB = '56' then 02
      when A = '3' then 03
      when AB in ('1M') then 08
      when AB in ('10') then 07
      when AB in ('12') then null
      when A in ('1', '5') and H = '1' then 02
      when A in ('5') then 02
      when A in ('1') then 01
    end as M4_OP
from (
    select
      VP as P, ifnull(A,'') as A, ifnull(B,'') as B,
ifnull(A,'')||ifnull(B,'') as AB,
      ifnull(URE,'') as U, ifnull(H,'') as H, ZZ
    from SOPP1
    );

You will see that first column name is VP instead of P.
In previous versions name was P not VP.

Best Regards
Radovan Antloga
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users