Radovan. Thank you for sticking to your guns. Your appeal to expected behavior under other DB engines was also a very good post. I see the usual suspects of the echo chamber uselessly piled on against you to clog the forum nevertheless. That happens too frequently.
BTW, here is a simpler test suite which pinpoints the problem: SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c)); d 1 CREATE TABLE test AS SELECT d FROM (SELECT c AS d FROM (SELECT 1 AS c)); SELECT * FROM test; c 1 CREATE TABLE test AS SELECT c AS d FROM (SELECT 1 AS c); SELECT * FROM test; d 1 And still one more that illustrates the contradictory and unstable behavior. Use '*' instead of 'd' and it works as expected. CREATE TABLE test AS SELECT * FROM (SELECT c AS d FROM (SELECT 1 AS c)); SELECT * FROM test; d 1 Obviously the most immediate well formed alias 'd' should be carried through irrespective of nesting. FYI, there was a checkin just now with some changes to track intermediate aliases: http://www.sqlite.org/src/info/5efd854fe2147033 Peter On Fri, Dec 22, 2017 at 11:34 AM, Radovan Antloga <[email protected]> wrote: > Thank you Klaus! > > Klaus Maas je 22.12.2017 ob 20:30 napisal: > >> Radovan is correct. >> Executing the same command sequence in version 3.11.0 and 3.21.0 results >> in different column names for table test2. >> (Xubuntu 16.04 with sqlite3.11.0 and sqlite3.21.0) >> I marked the results with '<=====' >> Klaus >> >> >> SQLite version 3.11.0 2016-02-15 17:29:24 >> Enter ".help" for usage hints. >> Connected to a transient in-memory database. >> Use ".open FILENAME" to reopen on a persistent database. >> sqlite> .header on >> sqlite> >> sqlite> create table test(a int, b int); >> sqlite> insert into test values (1,1); >> sqlite> >> sqlite> select d from (select c as d from (select a as c from test)); >> d >> 1 >> sqlite> >> sqlite> create table test2 as >> ...> select d from (select c as d from (select a as c from test)); >> sqlite> SELECT * FROM test2; >> d <===== >> 1 >> sqlite> >> >> >> >> SQLite version 3.21.0 2017-10-24 18:55:49 >> Enter ".help" for usage hints. >> Connected to a transient in-memory database. >> Use ".open FILENAME" to reopen on a persistent database. >> sqlite> .header on >> sqlite> create table test(a int, b int); >> sqlite> insert into test values (1,1); >> sqlite> >> sqlite> select d from (select c as d from (select a as c from test)); >> d >> 1 >> sqlite> create table test2 as >> ...> select d from (select c as d from (select a as c from test)); >> sqlite> SELECT * FROM test2; >> a <===== >> 1 >> sqlite> >> >> email signature Klaus Maas >> ------------------------------------------------------------------------ >> On 2017-12-22 20:11, Radovan Antloga wrote: >> >>> Just try this sql-s: >>> >>> 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)); >>> >>> will return name d. >>> >>> create table test2 as >>> select d from (select c as d from (select a as c from test)); >>> >>> will create table test2 with column name a. >>> >>> with PostgreSQL I get table test2 with name d. >>> >>> Thank you very much for your time! >>> >>> >>> Richard Hipp je 22.12.2017 ob 20:06 napisal: >>> >>>> On 12/22/17, Radovan Antloga <[email protected]> wrote: >>>> >>>>> My point is you do not have to change anything regards >>>>> to how select works. SELECT statement is working just >>>>> great or OK or fine. >>>>> >>>>> CREATE TABLE AS >>>>> SELECT .... >>>>> >>>>> does not give same name as SELECT does. >>>>> >>>>> SELECT give name d >>>>> >>>>> CREATE TABLE AS SELECT >>>>> create table with name a instead of d. >>>>> >>>> Can you provide a simple test case for this behavior? >>>> >>>> >>> _______________________________________________ >>> 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 >> > > _______________________________________________ > 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
