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:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Radovan Antloga Gesendet: Donnerstag, 21. Dezember 2017 16:35 An: sqlite-users@mailinglists.sqlite.org 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 <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 ___________________________________________ 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 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
