Re: [sqlite] Result set column names

[Tim Streater]

On 10 Dec 2019, at 21:42, Simon Slavin  wrote:

> On 10 Dec 2019, at 9:35pm, Doug  wrote:
>
>> For column "xxx" what will the full column name be? "foo.xxx"?
>
> For column "xxx" in table "foo", yes.
>
> However, that's only if you've used "PRAGMA full_column_names".  And that
> PRAGMA is now deprecated so you shouldn't use it.

Why are these pragmas deprecated? And what does "deprecated" mean, in this 
context, anyway? There's lots of HTML that's "deprecated", but browsers will 
continue to support it until kingdom come.


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

Re: [sqlite] Result set column names

[Simon Slavin]

On 10 Dec 2019, at 9:35pm, Doug  wrote:

> For column "xxx" what will the full column name be? "foo.xxx"?

For column "xxx" in table "foo", yes.

However, that's only if you've used "PRAGMA full_column_names".  And that 
PRAGMA is now deprecated so you shouldn't use it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Doug]

For column "xxx" what will the full column name be? "foo.xxx"?
Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Simon Slavin
> Sent: Tuesday, December 10, 2019 10:37 AM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Result set column names
> 
> On 10 Dec 2019, at 5:13pm, Doug  wrote:
> 
> > Just to be clear: If I define a column in a table named "xxx",
> and I "select xxx from table", the report will show a column named
> "xxx" always. That's what the standard says, right? It's not
> implementation dependent, right?
> 
> Unless you have specified another column "AS xxx", or used "PRAGMA
> full_column_names".
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Result set column names

[Simon Slavin]

On 10 Dec 2019, at 5:13pm, Doug  wrote:

> Just to be clear: If I define a column in a table named "xxx", and I "select 
> xxx from table", the report will show a column named "xxx" always. That's 
> what the standard says, right? It's not implementation dependent, right?

Unless you have specified another column "AS xxx", or used "PRAGMA 
full_column_names".
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Doug]

Just to be clear: If I define a column in a table named "xxx", and I "select 
xxx from table", the report will show a column named "xxx" always. That's what 
the standard says, right? It's not implementation dependent, right?

CREATE TABLE foo(xxx)
INSERT INTO foo(xxx) VALUES(25)
SELECT xxx FROM foo
---|-
xxx 25

Doug

> -Original Message-
> From: sqlite-users 
> On Behalf Of Adrian Ho
> Sent: Tuesday, December 10, 2019 8:04 AM
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] Result set column names
> 
> On 9/12/19 3:45 PM, Graham Holden wrote:
> > Monday, December 09, 2019, 7:25:25 AM, Clemens Ladisch
>  wrote:
> >
> >> The SQL-92 standard actually says:
> >>
> >> |Syntax Rules
> >> |
> >> |9) Case:
> >> |
> >> |   b) If the i-th  in the 
> does not
> >> | specify an  and the  of
> that
> >> |  is a single , then
> the
> >> |  of the i-th column of the result is C.
> >> |
> > Presumably the third line of clause (b) of the standard SHOULD
> have
> > read: "is a single  C,", otherwise "is C" has
> > nothing to refer to.
> 
> C is actually defined in a preceding paragraph:
> 
>  5) Let C be some column. Let QS be the  specification>. Let
> DCi, for i ranging from 1 to the number of  column>s
> inclusively, be the i-th  simply
> contained in
> the  of QS. For all i, C is an underlying
> column
> of DCi, and of any  that identifies
> DCi, if
> and only if C is an underlying column of the  expression>
> of DCi, or C is an underlying column of the  expression>
> immediately contained in QS.
> 
> --
> Best Regards,
> Adrian
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Result set column names

[Adrian Ho]

On 9/12/19 3:45 PM, Graham Holden wrote:
> Monday, December 09, 2019, 7:25:25 AM, Clemens Ladisch  
> wrote:
>
>> The SQL-92 standard actually says:
>>
>> |Syntax Rules
>> |
>> |9) Case:
>> |
>> |   b) If the i-th  in the  does not
>> | specify an  and the  of that
>> |  is a single , then the
>> |  of the i-th column of the result is C.
>> |
> Presumably the third line of clause (b) of the standard SHOULD have
> read: "is a single  C,", otherwise "is C" has
> nothing to refer to.

C is actually defined in a preceding paragraph:

 5) Let C be some column. Let QS be the . Let
    DCi, for i ranging from 1 to the number of s
    inclusively, be the i-th  simply contained in
    the  of QS. For all i, C is an underlying column
    of DCi, and of any  that identifies DCi, if
    and only if C is an underlying column of the 
    of DCi, or C is an underlying column of the 
    immediately contained in QS.

-- 
Best Regards,
Adrian

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

Re: [sqlite] Result set column names

[Graham Holden]

Monday, December 09, 2019, 7:25:25 AM, Clemens Ladisch  
wrote:

> The SQL-92 standard actually says:
>
> |Syntax Rules
> |
> |9) Case:
> |
> |   b) If the i-th  in the  does not
> | specify an  and the  of that
> |  is a single , then the
> |  of the i-th column of the result is C.
> |

Presumably the third line of clause (b) of the standard SHOULD have
read: "is a single  C,", otherwise "is C" has
nothing to refer to.

Graham

(And it's not a transcription error, according to:
https://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt


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

Re: [sqlite] Result set column names

[Clemens Ladisch]

Keith Medcalf wrote:
> If you do not provide as AS clause to give a result column a name, then
> each implementation is free to provide whatever names it feels like (so
> sayeth the standard).

The SQL-92 standard actually says:
|6.4  
|
| ::= [   ] 
|
|7.9  
|
| ::=
| SELECT [  ]  
|
| ::=
|   
| |  [ {   }... ]
|
| ::=
|   
| |   
|
| ::=  [  ]
|
| ::= [ AS ] 
|
|Syntax Rules
|
|9) Case:
|
|   a) If the i-th  in the  specifies
| an  that contains a  C, then the
|  of the i-th column of the result is C.
|
|   b) If the i-th  in the  does not
| specify an  and the  of that
|  is a single , then the
|  of the i-th column of the result is C.
|
|   c) Otherwise, the  of the i-th column of the  is implementation-dependent and different
| from the  of any column, other than itself, of
| a table referenced by any  contained in the
| SQL-statement.

SQLite's default short_column_names setting implements rule b).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Keith Medcalf]

On Saturday, 7 December, 2019 16:05, Tim Streater  wrote:

>At various times in various threads on this list it has been stated that
>the column name in a result set is not guaranteed unless one uses AS.
>IOW, one should say
>
>  select abc as abc from mytable where i=23;
>
>rather than just:
>
>  select abc from mytable where i=23;
>
>I'm trying to find where on the SQLite website that is documented, if it
>is, so I can point someone at it. The Xojo documentation doesn't mwention
>this so either that is a potential problem for all us Xojo users or
>they've worked around it somehow in their SQLite wrapper.

Currently the default is that 

pragma short_column_names=1;
pragma full_column_names=0;

and the rules as described for the pragma full_column_names is in effect.  The 
use of the pragma's is deprecated.

https://sqlite.org/pragma.html#pragma_full_column_names

Anonymous columns are named "columnX" where x is the column number.  For 
example, "values (1,2),(3,4);" generates a two column table with columns named 
column1 and column2 and two rows (1,2) and (3,4).

You may have duplicate column names in a result set:

create table a(id);
create table b(id);
create table c(id);
insert into a values (1);
insert into b values (1);
insert into c values (1);
select * from a, b, c;

you will get a table that is the result of the join and the first column will 
be named "id", the second "id", and the third "id".  Which one of these 
represents which id from which table depends on the nesting order that the 
optimizer selects when the query is prepared.  If you care, then you need to 
explicitly select the columns by name and give them an output column name using 
AS:

select a.id as a_id, b.id as b_id, c.id as c_id from a,b,c;

You may also use the result column metadata function described at
https://sqlite.org/c3ref/column_database_name.html
to get metadata about the underlying database, table, and column provided that 
you compiled the version of the SQLite3 library you are using with the 
appropropriate SQLITE_ENABLE_COLUMN_METADATA C-preprocessor symbol defined.

The naming of columns in the result set is NOT GUARANTEED unless you use the AS 
clause to provide a name.  If you do not provide as AS clause to give a result 
column a name, then each implementation is free to provide whatever names it 
feels like (so sayeth the standard).  In other words, in *ALL* SQL 
implementations the only time you have guaranteed result column names is if you 
use the AS clause to give the result set columns names.  IN ALL OTHER CASES AND 
FOR ALL IMPLEMENTATIONS OF ALL SQL INTERFACES the name given the result set 
column is a non-specified implementation detail.

However, there is a general reliance on the rules as outlined above so they are 
unlikely to change.  It is possible that a "pragma random_result_names" might 
be introduced at some point in the future to assist in finding code that relies 
on "implementation details" much as "pragma reverse_unordered_selects" assists 
in finding where reliance is placed on the ordering of result sets when no 
order by is used.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Result set column names

[Simon Slavin]

On 7 Dec 2019, at 11:09pm, J. King  wrote:

> It's stated here, at least:
> 

Also note

https://sqlite.org/pragma.html#pragma_short_column_names

https://sqlite.org/pragma.html#pragma_full_column_names

even though they are now both deprecated.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[J. King]

On December 7, 2019 6:04:36 p.m. EST, Tim Streater  
wrote:
>At various times in various threads on this list it has been stated
>that the column name in a result set is not guaranteed unless one uses
>AS. IOW, one should say
>
>  select abc as abc from mytable where i=23;
>
>rather than just:
>
>  select abc from mytable where i=23;
>
>I'm trying to find where on the SQLite website that is documented, if
>it is, so I can point someone at it. The Xojo documentation doesn't
>mwention this so either that is a potential problem for all us Xojo
>users or they've worked around it somehow in their SQLite wrapper.
>
>Thanks for a pointer.
>
>
>-- 
>Cheers  --  Tim
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

It's stated here, at least:

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

[sqlite] Result set column names

[Tim Streater]

At various times in various threads on this list it has been stated that the 
column name in a result set is not guaranteed unless one uses AS. IOW, one 
should say

  select abc as abc from mytable where i=23;

rather than just:

  select abc from mytable where i=23;

I'm trying to find where on the SQLite website that is documented, if it is, so 
I can point someone at it. The Xojo documentation doesn't mwention this so 
either that is a potential problem for all us Xojo users or they've worked 
around it somehow in their SQLite wrapper.

Thanks for a pointer.


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

[sqlite] result set column names, when row count = 0

[Larry Brasfield]

After a lengthy, fruitless search for an option to make the sqlite3 
shell do this, I've modified shell.c to emit a column header row, (after 
.headers ON), even when the result set is empty.


The reason for this is to facilitate creation of tab-separated-value, 
column-name-headed, text tables for import elsewhere.  It bugged me a 
lot that the shell came so close to being able to do it but seemed to 
treat an empty result as a special case.  (It presently will never emit 
a header when there are 0 result rows.)


Having done this work, in a careful manner, I wonder if I should offer a 
shell.c patch to make it available for others.  I imagine this has 
bothered others with similar purposes and expectations.  Alternatively, 
maybe somebody can briefly explain why it's really fine to sometimes get 
nothing when headers have been requested, in a non-interactive context.


To retain the old behavior with previously valid input, this behavior 
could be kludgedly activated by adding a third argument value for the 
.headers command, "ALWAYS" in addition to "ON" and "OFF".  Or a 
compile-time switch may be better.  (cleaner, certainly)  I have neither 
in place now because I never want to not see a header after entering 
.headers ON .


Even if nobody is much interested now, I'll be happy to provide this to 
anybody who stumbles across this post in the archives during a similar 
(but not so fruitless) search.  Just decode my email address and use it.


Cheers,
--
Larry Brasfield
firstinitial.lastn...@something.org
where computer is really something.

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

Re: [sqlite] Result set column names

[Peter]

Jay A. Kreibich wrote, On 08/01/11 14:07:
> On Sat, Jan 08, 2011 at 09:12:36AM +, Simon Slavin scratched on the wall:
>>
>> On 8 Jan 2011, at 9:09am, Peter wrote:
>>
>>> So the 'column name' may be some internal representation/magic number
>>> and bear no direct relationship to the name in the projector as supplied
>>> in the SELECT?
>>
>> For columns which are exact copies of columns in a table, the name
>> is always a copy of the table's column name.  You can rely on that.
>
>Unless the name is prefixed with a table name.
>
>Or unless the name is prefixed with a table alias (AS in a FROM clause).
>
>I wouldn't even be surprised to find version that prefix database names,
>when appropriate.
>
>
>
>You can rely on "AS" names, and that's pretty much it-- just as the
>docs warn.
>
> -j
>
Well so long as it _is_ a column name, just that it's not specified 
whether it will include tablename or alias then that's all that matters.

I interpreted Igor's reply to mean that unless the column name was 
specified by an AS in the projectors then that function could return 
anything, including garbage.


-- 
Peter Hardman

'For every complex problem there is a solution that is simple, 
straightforward - and wrong'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Jay A. Kreibich]

On Sat, Jan 08, 2011 at 09:12:36AM +, Simon Slavin scratched on the wall:
> 
> On 8 Jan 2011, at 9:09am, Peter wrote:
> 
> > So the 'column name' may be some internal representation/magic number 
> > and bear no direct relationship to the name in the projector as supplied 
> > in the SELECT?
> 
> For columns which are exact copies of columns in a table, the name
> is always a copy of the table's column name.  You can rely on that.

  Unless the name is prefixed with a table name.

  Or unless the name is prefixed with a table alias (AS in a FROM clause).

  I wouldn't even be surprised to find version that prefix database names,
  when appropriate.



  You can rely on "AS" names, and that's pretty much it-- just as the
  docs warn.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Peter]

On 08/01/11 09:12, Simon Slavin wrote:
>
> On 8 Jan 2011, at 9:09am, Peter wrote:
>
>> So the 'column name' may be some internal representation/magic number
>> and bear no direct relationship to the name in the projector as supplied
>> in the SELECT?
>
> For columns which are exact copies of columns in a table, the name is always 
> a copy of the table's column name.  You can rely on that.  For columns which 
> are the results of a calculation, there is no documentation for the name 
> produced by SQLite.  Different versions of SQLite may produce different names.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>

That sounds better. I just have to always use an AS clause on calculated 
fields.

Maybe the documentation should be clarified?

Pete

-- 
Peter Hardman

'For every complex problem there is a solution that is simple, 
straightforward - and wrong'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Simon Slavin]

On 8 Jan 2011, at 9:09am, Peter wrote:

> So the 'column name' may be some internal representation/magic number 
> and bear no direct relationship to the name in the projector as supplied 
> in the SELECT?

For columns which are exact copies of columns in a table, the name is always a 
copy of the table's column name.  You can rely on that.  For columns which are 
the results of a calculation, there is no documentation for the name produced 
by SQLite.  Different versions of SQLite may produce different names.

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

Re: [sqlite] Result set column names

[Peter]

On 07/01/11 18:41, Igor Tandetnik wrote:
> On 1/7/2011 1:31 PM, Peter wrote:
>> In the C interface documentation we are told:
>>
>> "The name of a result column is the value of the "AS" clause for that
>> column, if there is an AS clause. If there is no AS clause then the name
>> of the column is unspecified and may change from one release of SQLite
>> to the next."
>>
>> What exactly is meant by 'unspecified'?
>
> Generated by internal logic that is not precisely documented, and is
> subject to change without notice; should be treated as unpredictable,
> and not relied on for anything of substance.
>
>> Does it mean that in the absence
>> of an "AS" clause I will get either TABLE.COLUMN or COLUMN, or are
>> there/may there be other alternatives?
>
> There may potentially be other alternatives, now or in the future.

So the 'column name' may be some internal representation/magic number 
and bear no direct relationship to the name in the projector as supplied 
in the SELECT?

Not a very useful function then.

-- 
Peter Hardman

'For every complex problem there is a solution that is simple, 
straightforward - and wrong'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Result set column names

[Artur Reilin]

Isn't it also depending if there are columns which have the same name or not?

> On 1/7/2011 1:31 PM, Peter wrote:
>> In the C interface documentation we are told:
>>
>> "The name of a result column is the value of the "AS" clause for that
>> column, if there is an AS clause. If there is no AS clause then the name
>> of the column is unspecified and may change from one release of SQLite
>> to the next."
>>
>> What exactly is meant by 'unspecified'?
>
> Generated by internal logic that is not precisely documented, and is
> subject to change without notice; should be treated as unpredictable,
> and not relied on for anything of substance.
>
>> Does it mean that in the absence
>> of an "AS" clause I will get either TABLE.COLUMN or COLUMN, or are
>> there/may there be other alternatives?
>
> There may potentially be other alternatives, now or in the future.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


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

Re: [sqlite] Result set column names

[Igor Tandetnik]

On 1/7/2011 1:31 PM, Peter wrote:
> In the C interface documentation we are told:
>
> "The name of a result column is the value of the "AS" clause for that
> column, if there is an AS clause. If there is no AS clause then the name
> of the column is unspecified and may change from one release of SQLite
> to the next."
>
> What exactly is meant by 'unspecified'?

Generated by internal logic that is not precisely documented, and is 
subject to change without notice; should be treated as unpredictable, 
and not relied on for anything of substance.

> Does it mean that in the absence
> of an "AS" clause I will get either TABLE.COLUMN or COLUMN, or are
> there/may there be other alternatives?

There may potentially be other alternatives, now or in the future.
-- 
Igor Tandetnik

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

Re: [sqlite] Result set column names

[Peter]

In the C interface documentation we are told:

"The name of a result column is the value of the "AS" clause for that 
column, if there is an AS clause. If there is no AS clause then the name 
of the column is unspecified and may change from one release of SQLite 
to the next."

What exactly is meant by 'unspecified'? Does it mean that in the absence 
of an "AS" clause I will get either TABLE.COLUMN or COLUMN, or are 
there/may there be other alternatives?

I've just discovered I can get COLUMN for some names and TABLE.COLUMN 
for others depending (apparently) on how many tables I have JOIN'ed in 
the query.

Pete
-- 
Peter Hardman

'For every complex problem there is a solution that is simple, 
straightforward - and wrong'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users