[sqlite] Bug report: column name includes table alias when CTE is used
Hi, Apparently when a CTE is used, the column name includes the table alias. However, when no CTE is used, the alias is not present in the returned column name. SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table X (columnA int); sqlite> insert into X values (1); sqlite> .header on sqlite> select alias.columnA from X alias; *columnA* 1 sqlite> with CTE as (select columnA from X) select alias.columnA from CTE alias; *alias.columnA* 1 sqlite> I experienced this when rewriting a query to use CTE in an application that based some logic on the column name. I'd expect not to see the alias either way. Is this a bug or is it expected for CTEs? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug report: column name includes table alias when CTE is used
On 21 Apr 2014, at 6:16am, Andre wrote: > Apparently when a CTE is used, the column name includes the table alias. > However, when no CTE is used, the alias is not present in the returned > column name. SQLite has no standards at all about column names unless you specifically set a column name using "AS" in your SELECT. If you don't use "AS" then SQLite can return all sorts of weird things as the column names, even without using CTE. If you are expecting a specific column name, you're welcome to tell us what you expect and why, but so far nobody has come up with any good reason for a specific form. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug report: column name includes table alias when CTE is used
On Mon, Apr 21, 2014 at 1:16 AM, Andre wrote: > Hi, > > Apparently when a CTE is used, the column name includes the table alias. > However, when no CTE is used, the alias is not present in the returned > column name. > > SQLite version 3.8.4.3 2014-04-03 16:53:12 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> create table X (columnA int); > sqlite> insert into X values (1); > sqlite> .header on > sqlite> select alias.columnA from X alias; > *columnA* > 1 > sqlite> with CTE as (select columnA from X) select alias.columnA from CTE > alias; > *alias.columnA* > 1 > sqlite> > > I experienced this when rewriting a query to use CTE in an application that > based some logic on the column name. I'd expect not to see the alias either > way. Is this a bug or is it expected for CTEs? > See http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fiedfor further information. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug report: column name includes table alias when CTE is used
On Mon, Apr 21, 2014 at 8:29 AM, Richard Hipp wrote: > On Mon, Apr 21, 2014 at 1:16 AM, Andre wrote: > > > Hi, > > > > Apparently when a CTE is used, the column name includes the table alias. > > However, when no CTE is used, the alias is not present in the returned > > column name. > > > > SQLite version 3.8.4.3 2014-04-03 16:53:12 > > Enter ".help" for usage hints. > > Connected to a transient in-memory database. > > Use ".open FILENAME" to reopen on a persistent database. > > sqlite> create table X (columnA int); > > sqlite> insert into X values (1); > > sqlite> .header on > > sqlite> select alias.columnA from X alias; > > *columnA* > > 1 > > sqlite> with CTE as (select columnA from X) select alias.columnA from CTE > > alias; > > *alias.columnA* > > 1 > > sqlite> > > > > I experienced this when rewriting a query to use CTE in an application > that > > based some logic on the column name. I'd expect not to see the alias > either > > way. Is this a bug or is it expected for CTEs? > > > > See > http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fiedfor > further information. > > Fixed this link for myself and future referrers: http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fied -- -- -- --Ô¿Ô-- K e V i N ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users