Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-27 Thread Marcus Bergner
My main reason for questioning the inconsistency in returned column names between the three following cases is that SQL standards and other SQL databases seem to all return short column names, not prefix.column name in the three below scenarios. Having SQLite behave differently than everyone else

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-26 Thread James K. Lowden
On Mon, 26 Jan 2015 09:05:32 +0200 RSmith wrote: > Understand, I do not think these are insurmountable problems, but two > questions arise: > - Who decides the rules for handling it so that it may become > "trusted" by DB users/admins/programmers, if not the SQL standard?

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-26 Thread RSmith
On 2015/01/26 14:00, Tim Streater wrote: On 26 Jan 2015 at 07:33, Hick Gunter wrote: It is never a good idea to rely on automatically assigned column names. If you want reproducible, predictable, release independant column names then please assign them with the AS clause.

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-26 Thread Tim Streater
On 26 Jan 2015 at 12:23, Hick Gunter wrote: > NO. Only if all of the following apply > > - you are parsing and/or displaying raw returned column names > - your select contains more than one table (a table joined to itself counts > as 2) > - your tables have columns that share

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-26 Thread Hick Gunter
[mailto:t...@clothears.org.uk] Gesendet: Montag, 26. Jänner 2015 13:00 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery On 26 Jan 2015 at 07:33, Hick Gunter <h...@scigames.at> wrote: > It is never a good ide

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-26 Thread Tim Streater
On 26 Jan 2015 at 07:33, Hick Gunter wrote: > It is never a good idea to rely on automatically assigned column names. If you > want reproducible, predictable, release independant column names then please > assign them with the AS clause. So you're saying that if I do:

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-25 Thread Hick Gunter
, 25. Jänner 2015 14:16 An: sqlite-users@sqlite.org Betreff: [sqlite] Invalid column prefix returned in SELECT with joined subquery Hi, Using the latest amalgamation build sqlite-autoconf-3080801 I'm seeing the following inconsistent behaviour: $ ./sqlite3 sqlite> .headers on sqlite>

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-25 Thread RSmith
On 2015/01/26 04:04, James K. Lowden wrote: On Sun, 25 Jan 2015 23:18:05 +0200 RSmith wrote: There is no documentation in either SQLite or the SQL standard that would lead anyone to believe that behavior is expected - in fact it is very clear about the returned column

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-25 Thread James K. Lowden
On Mon, 26 Jan 2015 02:28:33 + Simon Slavin wrote: > > each name in should be the shortest possible syntactically > > correct column reference. > > While you're discussing possibilties and alternatives, what should be > returned as the name for the following column >

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-25 Thread Scott Robison
On Jan 25, 2015 7:28 PM, "Simon Slavin" wrote: > > > While you're discussing possibilties and alternatives, what should be returned as the name for the following column > > SELECT 1*2 FROM myTable There are a few possibilities: "1*2" "2" "two" "bob" Or any case

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-25 Thread Simon Slavin
On 26 Jan 2015, at 2:04am, James K. Lowden wrote: > What should the "name" be that is returned to the user? Going back to > my example, > > select > from () as T > > each name in should be the shortest possible syntactically > correct column

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-25 Thread James K. Lowden
On Sun, 25 Jan 2015 23:18:05 +0200 RSmith wrote: > There is no documentation in either SQLite or the SQL standard > that would lead anyone to believe that behavior is expected - in fact > it is very clear about the returned column names being > non-deterministic if not

Re: [sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-25 Thread RSmith
On 2015/01/25 15:16, Marcus Bergner wrote: Hi, Using the latest amalgamation build sqlite-autoconf-3080801 I'm seeing the following inconsistent behaviour: $ ./sqlite3 sqlite> .headers on sqlite> pragma short_column_names; 1 sqlite> pragma full_column_names; 0 sqlite> create table tbl1 (id1

[sqlite] Invalid column prefix returned in SELECT with joined subquery

2015-01-25 Thread Marcus Bergner
Hi, Using the latest amalgamation build sqlite-autoconf-3080801 I'm seeing the following inconsistent behaviour: $ ./sqlite3 sqlite> .headers on sqlite> pragma short_column_names; 1 sqlite> pragma full_column_names; 0 sqlite> create table tbl1 (id1 integer, s1 text); sqlite> create table tbl2