Changed behaviour or bug using field alias in 3.21.0

classic Classic list List threaded Threaded
31 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Changed behaviour or bug using field alias in 3.21.0

Radovan Antloga
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

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

On 12/21/17, Radovan Antloga <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Hick Gunter
The behaviour does not need to match what you think of as consistent.

The only way to force a certain column name is with the AS clause *on the outermost statement*. Otherwise, the column name is implementation defined and may change between releases. You should not be relying on column names other than those you explicitly set using the AS clause. This is a common mistake.

If you really need the column names, then just CREATE TABLE first (this gives you defined column names AND declared data types) and the INSERT INTO ... SELECT later (which ignores the generated column names from the select statement).

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Radovan Antloga
Gesendet: Donnerstag, 21. Dezember 2017 16:35
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0

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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Radovan Antloga
I'm using sqlite from version 2. I have 1000+
sql-s written already. I understand that
outermost statement must have AS clause as I
have explained below (select d as d ...).
Sqlite authors always say that new version can
not break sql-s for milions of users using
sqlite. But this change does.

Sorry but I did not ask how can I fix my sql-s.
I know I can create table and insert into.
Change select to select d as d is just ridiculous.
Rename column name into same name. What is that?


Hick Gunter je 22.12.2017 ob 7:55 napisal:

> The behaviour does not need to match what you think of as consistent.
>
> The only way to force a certain column name is with the AS clause *on the outermost statement*. Otherwise, the column name is implementation defined and may change between releases. You should not be relying on column names other than those you explicitly set using the AS clause. This is a common mistake.
>
> If you really need the column names, then just CREATE TABLE first (this gives you defined column names AND declared data types) and the INSERT INTO ... SELECT later (which ignores the generated column names from the select statement).
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Radovan Antloga
> Gesendet: Donnerstag, 21. Dezember 2017 16:35
> An: [hidden email]
> Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
>
> 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 <[hidden email]> 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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Hick Gunter
The problem lies in your reliance on unspecified behaviour. Unspecified behaviour is allowed to change.

I am sure you have read (and ignored) the following guarantee taken from http://sqlite.org/c3ref/column_name.html:

"Column Names In A Result Set
...
The name of a result column is the value of the "AS" clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next."


Here is another common trap to fall into, taken from http://sqlite.org/lang_select.html :

" The ORDER BY clause

If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined."

The order in which rows are returned is determined by the selected query plan. This may change after running ANALYZE, adding or deleting indices, or when changes are made to the Query Planner itself.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Radovan Antloga
Gesendet: Freitag, 22. Dezember 2017 10:06
An: [hidden email]
Betreff: Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0

I'm using sqlite from version 2. I have 1000+ sql-s written already. I understand that outermost statement must have AS clause as I have explained below (select d as d ...).
Sqlite authors always say that new version can not break sql-s for milions of users using sqlite. But this change does.

Sorry but I did not ask how can I fix my sql-s.
I know I can create table and insert into.
Change select to select d as d is just ridiculous.
Rename column name into same name. What is that?


Hick Gunter je 22.12.2017 ob 7:55 napisal:

> The behaviour does not need to match what you think of as consistent.
>
> The only way to force a certain column name is with the AS clause *on the outermost statement*. Otherwise, the column name is implementation defined and may change between releases. You should not be relying on column names other than those you explicitly set using the AS clause. This is a common mistake.
>
> If you really need the column names, then just CREATE TABLE first (this gives you defined column names AND declared data types) and the INSERT INTO ... SELECT later (which ignores the generated column names from the select statement).
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users
> [mailto:[hidden email]] Im Auftrag von
> Radovan Antloga
> Gesendet: Donnerstag, 21. Dezember 2017 16:35
> An: [hidden email]
> Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field
> alias in 3.21.0
>
> 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 <[hidden email]> 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
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick | Software Engineer | Scientific Games International
> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
> 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Radovan Antloga
But column name is specified in inner select with AS clause.
It can be used in outer select and work as expected.

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

I hope you did read my example. You can see explicitly defined

name in select a AS c from test

then again explicitly defined new name

select c AS d from ....

and finally outer select can select only name d or what you think?

select statement gives correct answer or column name

but when you put CREATE TABLE AS in front of this statement you
get a as column name not d in created table. Do you understand that?

So outer select must have select d AS d so column must be renamed
into it self to get proper name?!

If I have long name then I would have

select some_long_name_to_understand_column as
some_long_name_to_understand_column

and if I have 20 columns like that. Just ridiculous or what.

So once again I use AS clause but why I must use AS clause in
outer statement if name is already defined in inner select.




Hick Gunter je 22.12.2017 ob 10:57 napisal:

> The problem lies in your reliance on unspecified behaviour. Unspecified behaviour is allowed to change.
>
> I am sure you have read (and ignored) the following guarantee taken from http://sqlite.org/c3ref/column_name.html:
>
> "Column Names In A Result Set
> ...
> The name of a result column is the value of the "AS" clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next."
>
>
> Here is another common trap to fall into, taken from http://sqlite.org/lang_select.html :
>
> " The ORDER BY clause
>
> If a SELECT statement that returns more than one row does not have an ORDER BY clause, the order in which the rows are returned is undefined."
>
> The order in which rows are returned is determined by the selected query plan. This may change after running ANALYZE, adding or deleting indices, or when changes are made to the Query Planner itself.
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Radovan Antloga
> Gesendet: Freitag, 22. Dezember 2017 10:06
> An: [hidden email]
> Betreff: Re: [sqlite] [EXTERNAL] Re: Changed behaviour or bug using field alias in 3.21.0
>
> I'm using sqlite from version 2. I have 1000+ sql-s written already. I understand that outermost statement must have AS clause as I have explained below (select d as d ...).
> Sqlite authors always say that new version can not break sql-s for milions of users using sqlite. But this change does.
>
> Sorry but I did not ask how can I fix my sql-s.
> I know I can create table and insert into.
> Change select to select d as d is just ridiculous.
> Rename column name into same name. What is that?
>
>
> Hick Gunter je 22.12.2017 ob 7:55 napisal:
>> The behaviour does not need to match what you think of as consistent.
>>
>> The only way to force a certain column name is with the AS clause *on the outermost statement*. Otherwise, the column name is implementation defined and may change between releases. You should not be relying on column names other than those you explicitly set using the AS clause. This is a common mistake.
>>
>> If you really need the column names, then just CREATE TABLE first (this gives you defined column names AND declared data types) and the INSERT INTO ... SELECT later (which ignores the generated column names from the select statement).
>>
>> -----Ursprüngliche Nachricht-----
>> Von: sqlite-users
>> [mailto:[hidden email]] Im Auftrag von
>> Radovan Antloga
>> Gesendet: Donnerstag, 21. Dezember 2017 16:35
>> An: [hidden email]
>> Betreff: [EXTERNAL] Re: [sqlite] Changed behaviour or bug using field
>> alias in 3.21.0
>>
>> 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 <[hidden email]> 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
>>>> [hidden email]
>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___________________________________________
>>    Gunter Hick | Software Engineer | Scientific Games International
>> GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
>> 0430013 | (O) +43 1 80100 - 0
>>
>> May be privileged. May be confidential. Please delete if not the addressee.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

R Smith-2
In reply to this post by Radovan Antloga

On 2017/12/22 11:06 AM, Radovan Antloga wrote:
> I'm using sqlite from version 2. I have 1000+
> sql-s written already. I understand that
> outermost statement must have AS clause as I
> have explained below (select d as d ...).
> Sqlite authors always say that new version can
> not break sql-s for milions of users using
> sqlite. But this change does.

It didn't break for millions of users, it only broke for the few who
relied on behaviour that is documented to not be reliable and that might
change in future.

Usually the Dev team is not in the habit of going around messing with
features just because it is documented as unspecified, but in this case,
it had to change to fix another real bug that had real-world problematic
implications.

>
> Sorry but I did not ask how can I fix my sql-s.
> I know I can create table and insert into.
> Change select to select d as d is just ridiculous.
> Rename column name into same name. What is that?

The first "d" tells SQLite where to find or how to evaluate the value,
the second "d" tells SQLite how to name the output of that SELECT. It
isn't any more ridiculous than SELECT (P+3) AS 'P+3' would be ridiculous.
This is not English101, this is Programming. Rules are rules, even silly
ones.


.../and later/...
 >If I have long name then I would have
 >select some_long_name_to_understand_column as
some_long_name_to_understand_column
 >and if I have 20 columns like that. Just ridiculous or what.
 >So once again I use AS clause but why I must use AS clause in
 >outer statement if name is already defined in inner select.

Why would you use such a long silly name in the inner query KNOWING that
it is ignored (wrt. to output naming anyway)?  That doesn't make a sound
argument.

The CREATE TABLE Evaluates the select with some special considerations
(as documented), nothing prescribes the resulting column name except the
final outer statement's AS clause (as documented). Internally sqlite
doesn't even see Aliases, it has numbers for columns. The outer select
is however the boss, when it says "this-column" AS 'this_name' then
sqlite (and any other DB engine) /has/ to abide by that (as
documented).  The aliasing in the inner queries matter none other than
to allow reliable reference pointing between query levels (as documented).

If it were undocumented behaviour you'd still not have a case but some
anger would be understandable, however, you've explicitly relied on
/documented-to-be-non-reliable/ behaviour, I still feel your pain, but
it still isn't a bug and calling it "ridiculous" still won't change that.
(Btw. - the devs might change the behaviour again, maybe even to your
liking if they feel merit and it doesn't break another behaviour, the
point remains that it must not be trusted until it becomes the
documented behaviour.)


I sincerely hope not too many of those 1000+ sql-s need fixing...
Good luck!
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Radovan Antloga
How do you test your sql-s? I write sql
from step to step from very simple to complex.
So I have select and finally I need table for
that. It was simple until now. Just put
create table as before select and you get
same result (same names) as in select.

Your example is not the same. If using expression
name can be different and can change. This is
true for every datatase I know. In Firebird If
I write select 1+2 from rdb$database I get
column name ADD. If I specify with AS clause
I get name I want.

This works in sqlite and is not a problem. If
I do not specify name it can change from version
to version and everbody knows it must be specified.

But in my example name is explicitly defined in
inner select and for select I get correct name
but for create table as I do not.

Please dont tell me again what is documented because
at first very little was and not as one would like but
I understand that. It is not the point in this example.

The point is different behavior or result or names
when you have select or create table as from that select.
It should give same result or is better just not using
create table as at all because you will never know
what you can expect. Maybe as you eplained some day
create table as will create name like 1, 2, 3, 4....

Long names sometimes are used for excel export or some
simple reports with just sql behind.


R Smith je 22.12.2017 ob 12:34 napisal:

>
> On 2017/12/22 11:06 AM, Radovan Antloga wrote:
>> I'm using sqlite from version 2. I have 1000+
>> sql-s written already. I understand that
>> outermost statement must have AS clause as I
>> have explained below (select d as d ...).
>> Sqlite authors always say that new version can
>> not break sql-s for milions of users using
>> sqlite. But this change does.
>
> It didn't break for millions of users, it only broke for the few who
> relied on behaviour that is documented to not be reliable and that
> might change in future.
>
> Usually the Dev team is not in the habit of going around messing with
> features just because it is documented as unspecified, but in this
> case, it had to change to fix another real bug that had real-world
> problematic implications.
>
>>
>> Sorry but I did not ask how can I fix my sql-s.
>> I know I can create table and insert into.
>> Change select to select d as d is just ridiculous.
>> Rename column name into same name. What is that?
>
> The first "d" tells SQLite where to find or how to evaluate the value,
> the second "d" tells SQLite how to name the output of that SELECT. It
> isn't any more ridiculous than SELECT (P+3) AS 'P+3' would be ridiculous.
> This is not English101, this is Programming. Rules are rules, even
> silly ones.
>
>
> .../and later/...
> >If I have long name then I would have
> >select some_long_name_to_understand_column as
> some_long_name_to_understand_column
> >and if I have 20 columns like that. Just ridiculous or what.
> >So once again I use AS clause but why I must use AS clause in
> >outer statement if name is already defined in inner select.
>
> Why would you use such a long silly name in the inner query KNOWING
> that it is ignored (wrt. to output naming anyway)?  That doesn't make
> a sound argument.
>
> The CREATE TABLE Evaluates the select with some special considerations
> (as documented), nothing prescribes the resulting column name except
> the final outer statement's AS clause (as documented). Internally
> sqlite doesn't even see Aliases, it has numbers for columns. The outer
> select is however the boss, when it says "this-column" AS 'this_name'
> then sqlite (and any other DB engine) /has/ to abide by that (as
> documented).  The aliasing in the inner queries matter none other than
> to allow reliable reference pointing between query levels (as
> documented).
>
> If it were undocumented behaviour you'd still not have a case but some
> anger would be understandable, however, you've explicitly relied on
> /documented-to-be-non-reliable/ behaviour, I still feel your pain, but
> it still isn't a bug and calling it "ridiculous" still won't change that.
> (Btw. - the devs might change the behaviour again, maybe even to your
> liking if they feel merit and it doesn't break another behaviour, the
> point remains that it must not be trusted until it becomes the
> documented behaviour.)
>
>
> I sincerely hope not too many of those 1000+ sql-s need fixing...
> Good luck!
> Ryan
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Tim Streater-3
In reply to this post by Hick Gunter
On 22 Dec 2017, at 09:57, Hick Gunter <[hidden email]> wrote:

> The problem lies in your reliance on unspecified behaviour. Unspecified
> behaviour is allowed to change.
>
> I am sure you have read (and ignored) the following guarantee taken from
> http://sqlite.org/c3ref/column_name.html :

My questions are these:

1) That the name without AS is documented as being unspecified, is that the case with all/most SQL systems or is it SQLite specific?

2) I don't see this issue mentioned when I read the PHP documentation about their SQLite interface, nor do I see it in the Xojo docs about *their* interface either. I assume their interfaces are not rewriting SELECT statements to include AS for every column selected, so should they be warning their users about it?


--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Simon Slavin-3


On 22 Dec 2017, at 3:04pm, Tim Streater <[hidden email]> wrote:

> 2) I don't see this issue mentioned when I read the PHP documentation about their SQLite interface, nor do I see it in the Xojo docs about *their* interface either. I assume their interfaces are not rewriting SELECT statements to include AS for every column selected, so should they be warning their users about it?

If you want to depend on the name of a column, use AS.  If column names without AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that standard, then there would need to be a warning.  But there’s no standard for column names without AS in the SQL standard so no warning is needed.

If you think that column names are simple, please consider this:

CREATE TABLE ta (ca INTEGER);
CREATE TABLE tb (cb INTEGER);
SELECT ca FROM ta UNION SELECT cb FROM tb;

What should the name of the resulting column be ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Radovan Antloga
Sorry but this is not related to my example.
I have AS in inner select. My select return
name as specified.

Problem I have is with create table as where
name is changed.

I give example like this:

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

I get d as column name. If I create table with
create table as I get a as column name.


Simon Slavin je 22.12.2017 ob 17:33 napisal:

>
> On 22 Dec 2017, at 3:04pm, Tim Streater <[hidden email]> wrote:
>
>> 2) I don't see this issue mentioned when I read the PHP documentation about their SQLite interface, nor do I see it in the Xojo docs about *their* interface either. I assume their interfaces are not rewriting SELECT statements to include AS for every column selected, so should they be warning their users about it?
> If you want to depend on the name of a column, use AS.  If column names without AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that standard, then there would need to be a warning.  But there’s no standard for column names without AS in the SQL standard so no warning is needed.
>
> If you think that column names are simple, please consider this:
>
> CREATE TABLE ta (ca INTEGER);
> CREATE TABLE tb (cb INTEGER);
> SELECT ca FROM ta UNION SELECT cb FROM tb;
>
> What should the name of the resulting column be ?
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Simon Slavin-3


On 22 Dec 2017, at 4:50pm, Radovan Antloga <[hidden email]> wrote:

> select d from (select c AS d from (select a AS c from test));
>
> I get d as column name. If I create table with
> create table as I get a as column name.

As I wrote, you did not specify AS for d, so you cannot depend on a column name.

If you want to know what the column will be called, specify AS:

select d AS myColumnNameHere from (select c AS d from (select a AS c from test));

Now you know what the column will be called.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

David Raymond
In reply to this post by Simon Slavin-3
I think the underlying feeling here is that if you're not doing anything tricky, and just straight up referencing a column name, that it should be fair to assume that the result will have that column name.

I.e. with
create table foo (bar);
if I run
select bar from foo;
I should be able to assume the result column is named "bar".

So...
select a as c from test
is certain to have a field named "c",

select c as d from (something with a field named "c")
is certain to have a field named "d",

but select d from (something with a field named "d")
doesn't give a field named "d"?

I'd say it's fair to be confused at that.
I definitely don't want to have to do queries in the form of "select foo as foo, bar as bar, baz as baz..."

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
Sent: Friday, December 22, 2017 11:34 AM
To: SQLite mailing list
Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0



On 22 Dec 2017, at 3:04pm, Tim Streater <[hidden email]> wrote:

> 2) I don't see this issue mentioned when I read the PHP documentation about their SQLite interface, nor do I see it in the Xojo docs about *their* interface either. I assume their interfaces are not rewriting SELECT statements to include AS for every column selected, so should they be warning their users about it?

If you want to depend on the name of a column, use AS.  If column names without AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that standard, then there would need to be a warning.  But there’s no standard for column names without AS in the SQL standard so no warning is needed.

If you think that column names are simple, please consider this:

CREATE TABLE ta (ca INTEGER);
CREATE TABLE tb (cb INTEGER);
SELECT ca FROM ta UNION SELECT cb FROM tb;

What should the name of the resulting column be ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Radovan Antloga
Thank you very much to understand my point!
This is exactly what I think. I have name
and select is working ok. It gives me my
name but create table as not.

David Raymond je 22.12.2017 ob 17:59 napisal:

> I think the underlying feeling here is that if you're not doing anything tricky, and just straight up referencing a column name, that it should be fair to assume that the result will have that column name.
>
> I.e. with
> create table foo (bar);
> if I run
> select bar from foo;
> I should be able to assume the result column is named "bar".
>
> So...
> select a as c from test
> is certain to have a field named "c",
>
> select c as d from (something with a field named "c")
> is certain to have a field named "d",
>
> but select d from (something with a field named "d")
> doesn't give a field named "d"?
>
> I'd say it's fair to be confused at that.
> I definitely don't want to have to do queries in the form of "select foo as foo, bar as bar, baz as baz..."
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]] On Behalf Of Simon Slavin
> Sent: Friday, December 22, 2017 11:34 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Changed behaviour or bug using field alias in 3.21.0
>
>
>
> On 22 Dec 2017, at 3:04pm, Tim Streater <[hidden email]> wrote:
>
>> 2) I don't see this issue mentioned when I read the PHP documentation about their SQLite interface, nor do I see it in the Xojo docs about *their* interface either. I assume their interfaces are not rewriting SELECT statements to include AS for every column selected, so should they be warning their users about it?
> If you want to depend on the name of a column, use AS.  If column names without AS were documented in the SQL standard, and SQLite / PHP / Xojo violated that standard, then there would need to be a warning.  But there’s no standard for column names without AS in the SQL standard so no warning is needed.
>
> If you think that column names are simple, please consider this:
>
> CREATE TABLE ta (ca INTEGER);
> CREATE TABLE tb (cb INTEGER);
> SELECT ca FROM ta UNION SELECT cb FROM tb;
>
> What should the name of the resulting column be ?
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Radovan Antloga
In reply to this post by Simon Slavin-3
What?

I have to write select d as d. You are not
serious. You dont understand what I wrote.

I get select correct but create table as
does not have same name as select has. Why
different result?

If select gives some name I don't care what
I expect create table as give me same name.
Is this so hard to understand.

Simon Slavin je 22.12.2017 ob 17:58 napisal:

>
> On 22 Dec 2017, at 4:50pm, Radovan Antloga <[hidden email]> wrote:
>
>> select d from (select c AS d from (select a AS c from test));
>>
>> I get d as column name. If I create table with
>> create table as I get a as column name.
> As I wrote, you did not specify AS for d, so you cannot depend on a column name.
>
> If you want to know what the column will be called, specify AS:
>
> select d AS myColumnNameHere from (select c AS d from (select a AS c from test));
>
> Now you know what the column will be called.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Radovan Antloga
In reply to this post by Simon Slavin-3
Just tried my example with PostgreSQL that have
create table as statement.

It work as expected. It creates table test2 with
column name d. sqlite3 creates table with column
name a.

sqlite3 try to mimic postgresql but in this example
is not.

Simon Slavin je 22.12.2017 ob 17:58 napisal:

>
> On 22 Dec 2017, at 4:50pm, Radovan Antloga <[hidden email]> wrote:
>
>> select d from (select c AS d from (select a AS c from test));
>>
>> I get d as column name. If I create table with
>> create table as I get a as column name.
> As I wrote, you did not specify AS for d, so you cannot depend on a column name.
>
> If you want to know what the column will be called, specify AS:
>
> select d AS myColumnNameHere from (select c AS d from (select a AS c from test));
>
> Now you know what the column will be called.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Richard Hipp-3
These are the rules that SQLite uses to name a column in the result set:

(1) If there is an AS clause, use it.

(2) If the result-set value comes from a table column (even
indirectly, such as through a subquery, but not if the value is
altered by an expression) then use the name of the column as it
appears in the original CREATE TABLE statement for the table.

(3) Otherwise, name the result-set column using the SQL text in the
SELECT statement.

What set of rules are you proposing that we should use instead?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Radovan Antloga
In my example I have AS clause so rule 1.

sqlite select statement is correct or name
is correct.

sqlite create table as statement create table
with different name that select statement return
Problem is different result or name.


Richard Hipp je 22.12.2017 ob 19:27 napisal:

> These are the rules that SQLite uses to name a column in the result set:
>
> (1) If there is an AS clause, use it.
>
> (2) If the result-set value comes from a table column (even
> indirectly, such as through a subquery, but not if the value is
> altered by an expression) then use the name of the column as it
> appears in the original CREATE TABLE statement for the table.
>
> (3) Otherwise, name the result-set column using the SQL text in the
> SELECT statement.
>
> What set of rules are you proposing that we should use instead?
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Changed behaviour or bug using field alias in 3.21.0

Richard Hipp-3
On 12/22/17, Radovan Antloga <[hidden email]> wrote:
> In my example I have AS clause so rule 1.

You did not understand my question.

I think what you are asking for is that we should enhance rule 1 so
that it applies even if the AS clause is contained within a subquery.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12