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 haveselect 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:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Radovan Antloga Gesendet: Freitag, 22. Dezember 2017 10:06 An: sqlite-users@mailinglists.sqlite.org 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: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 ___________________________________________ 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
