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:[email protected]] Im Auftrag von Radovan Antloga Gesendet: Freitag, 22. Dezember 2017 10:06 An: [email protected] 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:[email protected]] Im Auftrag von > Radovan Antloga > Gesendet: Donnerstag, 21. Dezember 2017 16:35 > An: [email protected] > 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 <[email protected]> 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 >>> [email protected] >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> > _______________________________________________ > sqlite-users mailing list > [email protected] > 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 > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
