Re: [sqlite] Bug report: column name includes table alias when CTE is used

[Kevin Benson]

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

Re: [sqlite] Bug report: column name includes table alias when CTE is used

[Richard Hipp]

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

[Simon Slavin]

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

[sqlite] Bug report: column name includes table alias when CTE is used

[Andre]

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