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

[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 is a compatibility problem that can prevent people from
using SQLite in a smooth manner.

select x.id1, x.s1, y.s2 from tbl1 x inner join tbl2 y on
x.id1=y.id2;
select x.id1, x.s1, subq.s2 from tbl1 x inner join (select * from
tbl2 y where y.id2=1) subq on x.id1=subq.id2;
select x.id1, x.s1, subq.* from tbl1 x inner join (select * from
tbl2 y where y.id2=1) subq on x.id1=subq.id2;

I did a small attempt yesterday at fixing my local sqlite build and I
needed a patch of about 5 lines in sqlite3ExprListSetSpan to make it
return consistent short names. I don't have the full test suite so can't
say for sure it is all good but looks fairly promising.

sqlite> select 1*2, x.id1, x.s1, subq.s2 from tbl1 x inner join (select
* from tbl2 y where y.id2=1) subq on x.id1=subq.id2;
1*2|id1|s1|s2
2|1|v1|v2
sqlite> select 1*2 as magicnumber, x.id1 as 'xyz.col', x.s1, subq.s2
from tbl1 x inner join (select * from tbl2 y where y.id2=1) subq on
x.id1=subq.id2;
magicnumber|xyz.col|s1|s2
2|1|v1|v2

Fix is pretty much this:

char *dot = (char*)memchr(pSpan->zStart, '.', (int)(pSpan->zEnd -
pSpan->zStart));
if (dot) {
  pItem->zSpan = after dot up to pSpan->zEnd
} else {
  pItem->zSpan = entire pSpan->zStart to zEnd string (same as before)
}

Kind regards,

Marcus Bergner

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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? 

My reading of the standard is that the column *name* is what's
returned.  As such, SQLite would be correct to return a name where
there is one, and none where there is not, without distinguishing
duplicates.  (FWIW that's what SQL Server does.)  If SQLite did that in
the next release, no applications would break, and new applications
would benefit from deterministic, standard column names.  

My suggestion is a little more complex.  I offered it because it seemed
to be in the spirit of SQLite's current behavior (assign some kind of
name to every column) with the benefit of being deterministic.  

It's perfectly fine for SQLite to choose nonstandard behavior, and
there's no question that the documentation in this case is clear.  I
think it's a stretch, though, to claim the current behavior is
permitted by the standard.  

I doubt anyone considers it a great feature.  It is clearly confusing,
witness the topic arising here about once a month.  It's more of a
quirk: predictable and easy to deal with if you know about it.  

> how many cpu cycles might be spent to finding suitable column names

I'm not convinced there's any real performance issue at stake here.
No one's said so.  My impression is the current behavior is more an
engineering artifact. Because it has a pretty simple workaround, it's
being left as-is for now.  Which is also understandable.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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.

So you're saying that if I do:

   create table wiggy (a,b,c,d,e,f,g,h);

and later do:

   select a, b, c, d, e, f from wiggy;

then I should really be doing:

   select a as a, b as b, c as c, d as d, e as e, f as f from wiggy;

That'll be a bit tedious changing the more than 300 select statements in my app.


Actually yes and no...  You are mixing two different problems here, namely the REQUEST and the RESULT. The first is referencing a 
table name and column name inside a query - this is the entire point of the SQL language and must always be understood correctly by 
the Engine to produce the correct result. For this, you can request: SELECT a,b FROM wiggy; and it MUST in all circumstances return 
exactly values found in those specific columns of that table, every time, without fail... and, the good news is: it does.


The problem as posted by the OP defines the second scenario where you use that same query (SELECT a,b FROM wiggy;) and then examine 
the RESULT, for example:


For every returned result, if itsResultColName=="a" then put the value in x and if 
itsResultColName=="b" then put the value*2 in y...
This is dangerous.

The correct thing would be:
For every returned result row, put the first value in x and second value*2 in 
y...

If you request first a, then b, you can and must expect it to be returned exactly like that, but expecting the name of the /result/ 
column headers to absolutely be "a" and "b" you may do if, and only if, you explicitly asked for it to be so (to borrow your 
example: SELECT a AS a, b AS b FROM wiggy; ).


Yes I know ALL SQL engines will return the correct column names for the above example queries because they are simple and it is the 
most obvious name to return, but please do not allow that to lure you into expecting that you can always trust the returned column 
name in more complex queries.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 the same name

Thanks for the clarification. I have a feeling this is not the first time this 
has come up - but the result of the discussion last time left me with the same 
impression I had when making my earlier post. If you're saying that in simple 
cases of doing a select one may rely on the defined names appearing, then 
things make more sense.


--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

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 the same name



-Ursprüngliche Nachricht-
Von: Tim Streater [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 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:

  create table wiggy (a,b,c,d,e,f,g,h);

and later do:

  select a, b, c, d, e, f from wiggy;

then I should really be doing:

  select a as a, b as b, c as c, d as d, e as e, f as f from wiggy;

That'll be a bit tedious changing the more than 300 select statements in my app.

I notice that in the PHP doc for SQLite result sets, they carefully talk about 
the "column names returned in the result set" rathe than just "column names", 
although I suppose that is just to indicate that names may be provided with AS.

--
Cheers  --  Tim


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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:

  create table wiggy (a,b,c,d,e,f,g,h);

and later do:

  select a, b, c, d, e, f from wiggy;

then I should really be doing:

  select a as a, b as b, c as c, d as d, e as e, f as f from wiggy;

That'll be a bit tedious changing the more than 300 select statements in my app.

I notice that in the PHP doc for SQLite result sets, they carefully talk about 
the "column names returned in the result set" rathe than just "column names", 
although I suppose that is just to indicate that names may be provided with AS.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Hick Gunter]

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.

-Ursprüngliche Nachricht-
Von: Marcus Bergner [mailto:marcusberg...@gmail.com]
Gesendet: Sonntag, 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> pragma short_column_names;
1
sqlite> pragma full_column_names;
0
sqlite> create table tbl1 (id1 integer, s1 text); create table tbl2 (id2
sqlite> integer, s2 text); insert into tbl1 values (1, 'v1'); insert
sqlite> into tbl2 values (1, 'v2'); select x.id1, x.s1, y.s2 from tbl1 x
sqlite> inner join tbl2 y on
x.id1=y.id2;
id1|s1|s2
1|v1|v2

So far so good, everything as expected. If I rewrite the above select statement 
to do a join with a subquery instead the resulting output changes in an 
unexpected way.

sqlite> select x.id1, x.s1, subq.s2 from tbl1 x inner join (select *
sqlite> from
tbl2 y where y.id2=1) subq on x.id1=subq.id2;
x.id1|x.s1|subq.s2
1|v1|v2

Here we get unexpected column prefixes on all fetched columns. If I rewrite the 
query again to a subq.* query the behaviour is different again where only the 
first two columns have prefixes.

sqlite> select x.id1, x.s1, subq.* from tbl1 x inner join (select * from
tbl2 y where y.id2=1) subq on x.id1=subq.id2;
x.id1|x.s1|id2|s2
1|v1|1|v2

Expected behaviour: returned columns should not contain prefixes in any of the 
above scenarios.

Kind regards,

Marcus Bergner
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 names being
non-deterministic if not explicitly requested by aliasing.

I no longer think that is entirely true.  In SQLite's documentation,
it's not in the description of the SELECT statement that I can find
(http://www.sqlite.org/lang_select.html).  It is mentioned in the
sqlite3_column_name description, so, OK, it's not a bug.

But I'm going to say that a function that "returns the name" of a
column really should return the column's *name*.  Especially if that
name would suffice to address the column syntactically if the form
statement were wrapped one more level, i.e.:

select * from () as T

Otherwise there's weird mismatch between the names SQL itself uses and
the ones presented to the user.


You are of course correct in feeling this way from a practical point of view, and I daresay SQLite (and the other engines) tries to 
return useful colmn naming where the cost of deduction does not impact performance. In fact, I believe it is this specific thing 
that lulls users or programmers into believing that the column naming returned by the engine for non-aliased columns is somehow 
governed by special rules or format and can therefore be trusted to be always conformant - where they really shouldn't.


Implementing a specific methodology for returning useful column naming based maybe on column references (as per your suggestion), I 
am not quite comfortable with.
Forgetting for the moment any calculated columns and other obvious column-reference/naming problems, even in straight-forward 
reference schemes I think there will be difficulties - I can think of tables joined to themselves (maybe even more than once, a 
useful trick as you know), multiple tables joined where some columns appear in both but others not and other possibilities that 
might come to mind if more time is spent on it.


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? 
- and,
   - What cost to performance will be acceptable? (i.e. how many cpu cycles might be spent to finding suitable column names when 
they are not specifically requested?)


Add to this the fact that for most queries where result column headers are not aliased to specifically required items, the actual 
naming just doesn't matter - and if it does,

   A - you are doing it wrong (which isn't a new rule), and
   B - It is so easy to add an alias request if you need an exact name.


I do understand that there is a small but important category of systems where this is paramount, like people making DB tools or 
admin tools which try to interpret data from user-specified queries, where random returned column naming is detrimental (I'm myself 
troubled by this), but I do not think the engines should cater for this if it comes at any sort of cost.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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
> 
> SELECT 1*2 FROM myTable

To quote me,  ;-)  

>> If there is no syntactically correct reference for a particular
>> column then, sure, make one up.  No one can complain about that.  

Acknowledged, not every selected column will have a name in the catalog
or designated alias.  For those that do, though, I think both the
standard and common sense, not to mention logical consistency, indicate
the column ought to be known by its name.  That unifies how the user
references it in SQL and in the API.  What could be simpler?  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 insensitive variation, of course.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 reference.

While you're discussing possibilties and alternatives, what should be returned 
as the name for the following column

SELECT 1*2 FROM myTable

?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 explicitly requested by aliasing.

I no longer think that is entirely true.  In SQLite's documentation,
it's not in the description of the SELECT statement that I can find
(http://www.sqlite.org/lang_select.html).  It is mentioned in the
sqlite3_column_name description, so, OK, it's not a bug.  

But I'm going to say that a function that "returns the name" of a
column really should return the column's *name*.  Especially if that
name would suffice to address the column syntactically if the form
statement were wrapped one more level, i.e.: 

select * from () as T

Otherwise there's weird mismatch between the names SQL itself uses and
the ones presented to the user.  

My reading of what I can find of the standard makes me think that if
this is left up to the implementation, it's a molehill we can't use to
shout from.  Definitely it's a POLA pain point from the user's
perspective.  

The standard is proprietary, but the old SQL-92 draft is online at
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt.  Part 6.4
describes a "column reference", and distinguishes a column *reference*
from its name: 

  ::= [   ] 

  ::=

  | 

So there's no such thing (in case anyone thinks there is) as a "fully
qualified name" in SQL.  There are names and references.  Sometimes a 
reference is letter-for-letter identical to the name; sometimes the
reference is longer to distinguish one name from another belonging to a
differerent entity.  

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 reference.  A  that designates a
unique set of names (by whatever means) thus yields an unambigiuous,
reasonable result. One that doesn't is more problematic to the user,
but then he's playing a little fast and loose in the first place.  If
there is no syntactically correct reference for a particular column
then, sure, make one up.  No one can complain about that.  

Implementation of such a policy would not break any existing
applications.  It might even yield improvements to SQLite internally
by making names more dependable, and by distinguishing more strongly
between the column's name and its syntactic reference.  

Humbly submitted.  

--jkl

P.S.  I tried testing this with sqlfiddle to get a vote, too.  But
they must have a problem with their output column logic; afaict
same-named columns are ignored after the first one (as it were).  If
you cross-join a table to itself, you get only one set of columns, not
two.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 integer, s1 text);
sqlite> create table tbl2 (id2 integer, s2 text);
sqlite> insert into tbl1 values (1, 'v1');
sqlite> insert into tbl2 values (1, 'v2');
sqlite> select x.id1, x.s1, y.s2 from tbl1 x inner join tbl2 y on
x.id1=y.id2;
id1|s1|s2
1|v1|v2

So far so good, everything as expected. If I rewrite the above select
statement to do a join with a subquery instead the resulting output changes
in an unexpected way.

sqlite> select x.id1, x.s1, subq.s2 from tbl1 x inner join (select * from
tbl2 y where y.id2=1) subq on x.id1=subq.id2;
x.id1|x.s1|subq.s2
1|v1|v2

Here we get unexpected column prefixes on all fetched columns. If I rewrite
the query again to a subq.* query the behaviour is different again where
only the first two columns have prefixes.

sqlite> select x.id1, x.s1, subq.* from tbl1 x inner join (select * from
tbl2 y where y.id2=1) subq on x.id1=subq.id2;
x.id1|x.s1|id2|s2
1|v1|1|v2

Expected behaviour: returned columns should not contain prefixes in any of
the above scenarios.


May I ask what exactly makes you "expect" your expected behavior? 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 explicitly requested by aliasing.


In short, if you need the column names to be something specific, you need to ask for it exactly so, else the SQL engine (any of 
them, not just SQLite) may return whatever they like by virtue of what might seem the most unconvoluted but also non-ambiguous (this 
latter simply being a minor paradigm,  not a rule of any kind).


My guess is you have come to expect the behavior by simply observing it in the past and not through consulting the standards 
(because that's how I made the mistake initially).


Always use aliasing when you need the results to conform.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users