Re: [sqlite] VALUES clause quirk or bug?

[Hick Gunter]

You should NOT be relying on column names UNLESS you set them yourself.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von petern
Gesendet: Samstag, 08. Juli 2017 21:37
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Betreff: [sqlite] VALUES clause quirk or bug?

Why does the choice of data value quotation mark influence the output column 
name of the inline VALUES clause? [This quirk was the origin of a recent bug in 
a current project.]

sqlite> .version
SQLite 3.19.3 2017-06-08 14:26:16
0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b

sqlite> .header on
sqlite> .mode csv
sqlite> SELECT * FROM (VALUES (1,2),(3,4));
"",:1
1,2
3,4
sqlite> SELECT * FROM (VALUES ("1",2),(3,4));
1,""
1,2
3,4
sqlite> SELECT * FROM (VALUES ('1',2),(3,4));
"",:1
1,2
3,4

I am aware that a less quirky "column" output column naming is available if 
the VALUES clause is evaluated directly.  See below.  However, this form is not 
applicable for task at hand, specifying inline constant tables within a query.

sqlite> VALUES ("1",2),(3,4);
column1,column2
1,2
3,4

Yes, I also thought carefully about the WITH clause.  See below.  While the 
WITH clause is natural for brief queries in a few columns, the wordiness an 
unnatural order is not helpful for local constant representation in the very 
lengthy queries needed for by this particular project.

sqlite> WITH Constants(c1,c2) AS (VALUES ("1",2),(3,4)) SELECT * FROM
Constants;
c1,c2
1,2
3,4

Is there anything I missed?  Are there other undocumented tricks of the VALUE 
clause that could help or hinder my quest?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/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@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] VALUES clause quirk or bug?

[David Raymond]

(New changes in testing look good, so sorry if this is reopening this)

If you need column names with a VALUES table, why not just kick the VALUES to 
the front in a CTE where you can name the fields? Then you don't need a temp 
table or temp view that you need to remember to drop, and since you usually 
know what you're putting into the values table right at the start you're not 
gonna be complicating anything with the CTE.


WITH valueTable (c1, c2) AS (values (1, 2), (3, 4))
SELECT custom_aggregate(c1, c2) FROM valueTable;


SQLite version 3.19.3 2017-06-08 14:26:16
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> with valueTable (field1, field2) as (values (1, 2), (3, 4)) select * 
from valueTable;
--EQP-- 0,0,0,SCAN SUBQUERY 1
field1|field2
1|2
3|4
Run Time: real 0.001 user 0.00 sys 0.00

sqlite> with valueTable (field1, field2) as (values (1, 2), (3, 4)) select 
field2, field1 from valueTable;
--EQP-- 0,0,0,SCAN SUBQUERY 1
field2|field1
2|1
4|3
Run Time: real 0.001 user 0.00 sys 0.00

sqlite> with valueTable (c1, c2, c3) as (values (1, 2, 3), (4, 5, 6)) select 
max(c1, c3) from valueTable;
--EQP-- 0,0,0,SCAN SUBQUERY 1
max(c1, c3)
3
6
Run Time: real 0.006 user 0.00 sys 0.00

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

Re: [sqlite] VALUES clause quirk or bug?

[Simon Slavin]

On 9 Jul 2017, at 9:53pm, Keith Medcalf  wrote:

> Richard has checked in fixes for this on trunk which will likely appear in 
> the next release of SQLite. 

Presumably



.  Is the behaviour of column names now consistent enough that the fix could 
include an update to



to reflect these names ?  The text current says

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

which is what I was basing my previous responses on.

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

Re: [sqlite] VALUES clause quirk or bug?

[Keith Medcalf]

Richard has checked in fixes for this on trunk which will likely appear in the 
next release of SQLite.  Note this is a change to the underlying SQLite engine 
(SQLite3.dll) and not the command line shell.  Column names from "values" are 
predictable and the same even in subqueries.  Note that shell simply outputs 
what is returned by executing the SQL.

Note that presently if you use a double-quote for the first value row, and that 
quoted-identified is not an identified but treated as a quoted-string, it still 
becomes the column name in the subquery.

SQLite version 3.20.0 2017-07-09 18:55:29
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .mode col
sqlite> .header on

sqlite> values (1,2), (2,3);
column1 column2
--  --
1   2
2   3

sqlite> select * from (values (1,2), (2,3));
column1 column2
--  --
1   2
2   3

sqlite> create table x as values (1,2), (3,4);
sqlite> select * from x;
column1 column2
--  --
1   2
3   4

sqlite> create table y as select * from (values (1,2), (2,3));
sqlite> select * from y;
column1 column2
--  --
1   2
2   3

sqlite> create view v as values (1,2), (2,3);
sqlite> select * from v;
column1 column2
--  --
1   2
2   3

sqlite> create view w as select * from (values (1,2), (2,3));
sqlite> select * from w;
column1 column2
--  --
1   2
2   3

sqlite> with x as (values (1,2), (2,3)) select * from x;
column1 column2
--  --
1   2
2   3

sqlite> with x as (select * from (values (1,2), (2,3))) select * from x;
column1 column2
--  --
1   2
2   3

sqlite> with
   ...> x as (values (1,2), (2,3)),
   ...> y as (values (4,5), (5,6))
   ...> select x.column1*y.column2, x.column2*y.column1 from x,y;
x.column1*y.column2  x.column2*y.column1
---  ---
58
610
10   12
12   15

sqlite> select x.column1*y.column2, x.column2*y.column1
   ...> from (values (1,2), (2,3)) as x,
   ...>  (values (4,5), (5,6)) as y;
x.column1*y.column2  x.column2*y.column1
---  ---
58
610
10   12
12   15
sqlite>

And since the values now return consistent names, you can "rename" them in the 
select more easily if you so desire.

sqlite> select column1 as idx, column2 as value from (values (1,2), (2,3));
idx value
--  --
1   2
2   3
sqlite>

However:

sqlite> values ("1", 2), ('3', 4);
column1 column2
--  --
1   2
3   4

sqlite> values ('1', 2), ("3", 4);
column1 column2
--  --
1   2
3   4
sqlite>

sqlite> select * from (values ("1", 2), ('3', 4));
1   column2
--  --
1   2
3   4

sqlite> select * from (values ('1', 2), ("3", 4));
column1 column2
--  --
1   2
3   4
sqlite>


> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of petern
> Sent: Saturday, 8 July, 2017 20:51
> To: SQLite mailing list
> Subject: Re: [sqlite] VALUES clause quirk or bug?
> 
> A lot of replies.  To be clear, this is not a quirk of the shell and I'm
> not just monkeying around looking for haphazard guesses about why I'm
> "trying to change the SQL standard".  The following query sent through
> sqlite3_exec() produces the following output:
> 
> SELECT max([],[:1]) FROM (VALUES (1,2),(3,4));
> max([],[:1])
> 2
> 4
> 
> This is what SQLite 3.19.3 VALUES clause presently does independently of
> shell.c.  My question would be, is this feature going to produce stable
> column names going forward caveat the strange behavior of double quotes.
> 
> Simon, your rhetorical question asking how having an inconsistent number
> of
> columns in the VALUES list makes sense is non-sequitur.  I am not
> proposing
> some illogical bizarre syntax.  I hope you see how your remark is out of
> line and amounts to noise on this thread.
> 
> The bug here is how the VALUES logic can't have it both ways.  If double
> quotes are invalid for column value literals they should be rejected or at
> least ignored.  They should not suddenly be injected into the column
> name(s) observed by the outer scope of the VALUES clause.   That is of
> course unless there is 

Re: [sqlite] VALUES clause quirk or bug?

[Clemens Ladisch]

petern wrote:
> I was hoping someone could shed light on what is actually going on in the
> VALUE clause.

  VALUES (a, b), (c, d) ...

is actually just a shortcut for

  SELECT a, b UNION ALL SELECT c, d ...

If you want to control the column names, you have to use the second form
with AS.


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

Re: [sqlite] VALUES clause quirk or bug?

[R Smith]

On 2017/07/09 4:50 AM, petern wrote:

The bug here is how the VALUES logic can't have it both ways.  If double
quotes are invalid for column value literals they should be rejected or at
least ignored.  They should not suddenly be injected into the column
name(s) observed by the outer scope of the VALUES clause.


It's not a bug, but it is an irritation - and we've been petitioning 
since the dawn of time to have a "STRICT" mode in SQLite where these 
shenanigans are indeed not allowed. As I understood from previous 
dev-team remarks, is that it's relatively easy to change, but not easy 
to maintain "both ways" without bloating the code-base, and since this 
quirk existed from day 1, it is required to keep existing for backward 
compatibility. (And backward compatibility IS important, since it is the 
very thing you are asking future guarantees for now).


That being said, for most of us it is/was just a once-off irritation 
that one time when we used double-quotes in stead of single quotes and 
got weird results. We fixed it, and so life goes on. In your case you 
have arrived at some mechanism or exploit of the SQLite internals to 
achieve an interesting functionality, but the quotes are biting you.


As already stated, best use is to avoid the double quotes in the VALUES. 
You are however quite welcome to use them in the identifier section (in 
lieu of [ and ]), but you can use the MSSQL style square brackets too. 
As to the question of future-proofing this method, well, it's not 
documented and not set in stone, BUT, SQLite isn't in the habit of 
haphazardly changing these internals. You are very likely to have a 
stable future regime for it - just not a 100% guarantee.


My advice would be to include test code, or create a separate test 
system that tests all your syntax forms, which you verify with in future 
before incorporating any future SQLite upgrade in your projects. Also, 
try to make your code based on constants and things in a way that a 
change would be very easy should it ever be required. (That's 1st week 
Computer science 101) :)


One last option, since you seem skilled in the coding of things, SQLite 
/IS/ open source, you could maintain a fork which implements a few lines 
of code to ensure your method works, or even make it work better. The 
code is very well documented, and this would guarantee your future 
compatibility.



That is of
course unless there is a software easter egg here that, depending on as yet
undisclosed well crafted literal values, allows the column names to be
elegantly specified in addition to the row values.



None that I know of, and even if there were, it's not documented and so 
would fall under the exact same problem as your current solutions: We 
are not 100% sure it will remain in future exactly as it is now.




Best of luck!
Ryan

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

Re: [sqlite] VALUES clause quirk or bug?

[Simon Slavin]

On 9 Jul 2017, at 3:50am, petern  wrote:

> This is what SQLite 3.19.3 VALUES clause presently does independently of
> shell.c.  My question would be, is this feature going to produce stable
> column names going forward caveat the strange behavior of double quotes.

I answered your question twice.  Both answers were "no".

I don’t know how I can state this any more clearly.

> The bug here is how the VALUES logic can't have it both ways.

A bug would be a situation where SQLite does not perform the way the 
documentation says it does.  The documentation does not state what column names 
VALUES leads to, therefore there is no such bug here.

Since they are not documented, column names for impromptu results — those not 
retrieved from a TABLE or VIEW — can be anything, and can change between 
different versions of SQLite.  This is not restricted to the use of double 
quotes.  Do not write code which depends on the result of sqlite3_column_name() 
for such values.

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

Re: [sqlite] VALUES clause quirk or bug?

[Scott Robison]

On Sat, Jul 8, 2017 at 8:50 PM, petern  wrote:
> The bug here is how the VALUES logic can't have it both ways.  If double
> quotes are invalid for column value literals they should be rejected or at
> least ignored.  They should not suddenly be injected into the column
> name(s) observed by the outer scope of the VALUES clause.   That is of
> course unless there is a software easter egg here that, depending on as yet
> undisclosed well crafted literal values, allows the column names to be
> elegantly specified in addition to the row values.
>
> Also sent through sqlite3_exec():
>
> SELECT * FROM (VALUES ("1",2),(3,4));
> 1,
> 1,2
> 3,4

The ultimate problem here is that long ago a decision was made to be
liberal with interpretation of SQL text so that people had a better
chance of seeing what they expected. Single quoted text is a string,
double quoted text is an identifier. If a string is given where an
identifier is expected, it'll be treated as an identifier in an
attempt to be helpful. If an identifier is given where a string is
expected, and no such identifier exists, it'll be interpreted as a
string instead.

I've read commentary from DRH suggesting that if he had it to do over
again, some of these "helpful" things might not have been done.
Unfortunately, backward compatibility demands that this not be changed
at this point in time.

Further (to me anyway), the keyword "VALUES" seems to explicitly
exclude the option of naming columns. "VALUES" it says, and that's all
you can get from it. I can certainly see the utility of inline
anonymous tables with named columns, but that particular syntax goes
out of its way to omit the potential of naming the columns.

So, should SQLite be pickier in the syntax it supports? Probably. Can
it be changed retroactively and break a bunch of existing code?
Probably not (though it's not my position to say one way or the
other). Are there other syntactic constructs that give you the ability
to have named columns in an otherwise anonymous query? Yes.

Good luck!

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

Re: [sqlite] VALUES clause quirk or bug? (create table x (col1, col2) as select ... parse bug?

[Keith Medcalf]

On Saturday, 8 July, 2017 19:02, Simon Slavin  wrote:
> On 9 Jul 2017, at 1:44am, Keith Medcalf  wrote:
 
> > I can't visualize what you mean.  Something like:

> > create view constants (col1, col2) as values (1,1), (1,2);
>
> > seems the most straightforward to me, and allows you to assign column
> > names to the data.

> But that’s not SQL syntax, is it ?  (Read that in a puzzled voice.  I
> genuinely don’t know.) You’re just using the word VALUE as if it does what
> you want.

Yes, it is valid SQL ... at least as understood by SQLite.

SQLite version 3.20.0 2017-07-07 22:47:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create view constants (col1, col2) as values (1,1), (1,2);
sqlite> .header on
sqlite> select * from constants;
col1|col2
1|1
1|2
sqlite>

> I was thinking of a CREATE TABLE command, and a SELECT command with "AS"
> clauses.  Or of something similar with a VIEW.

The syntax create table ... as select ...

appears to be buggy?

sqlite> create table x (col1, col2) as select * from (values (1,2), (2,3));
Error: near "as": syntax error

however, omitting the column names appears to work, but you get the same column 
names as the values clause directly.

create table x as select * from (values (1,2), (2,3));

sqlite> create table x as select * from (values (1,2), (2,3));
sqlite> select * from x;
|:1
1|2
2|3

in this case however

sqlite> create table x as values (1,2), (2,3);
sqlite> select * from x;
|:1
1|2
2|3

works just as well.

It would appear that you would need two statements to build a table with column 
names from a values clause:

create table x (col1, col2);
insert into x values (1,2), (2,3);

I would suspect that using a table would be far more efficient then dynamically 
re-generating the values every time they are needed anyway.

Of course, just using the values clause directly (without the select) seems to 
generate different column names that are not carried forth ...

sqlite> values (1,2),(2,3);
column1|column2
1|2
2|3

sqlite> values ("1",2), (3,4);
column1|column2
1|2
3|4





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

Re: [sqlite] VALUES clause quirk or bug?

[petern]

A lot of replies.  To be clear, this is not a quirk of the shell and I'm
not just monkeying around looking for haphazard guesses about why I'm
"trying to change the SQL standard".  The following query sent through
sqlite3_exec() produces the following output:

SELECT max([],[:1]) FROM (VALUES (1,2),(3,4));
max([],[:1])
2
4

This is what SQLite 3.19.3 VALUES clause presently does independently of
shell.c.  My question would be, is this feature going to produce stable
column names going forward caveat the strange behavior of double quotes.

Simon, your rhetorical question asking how having an inconsistent number of
columns in the VALUES list makes sense is non-sequitur.  I am not proposing
some illogical bizarre syntax.  I hope you see how your remark is out of
line and amounts to noise on this thread.

The bug here is how the VALUES logic can't have it both ways.  If double
quotes are invalid for column value literals they should be rejected or at
least ignored.  They should not suddenly be injected into the column
name(s) observed by the outer scope of the VALUES clause.   That is of
course unless there is a software easter egg here that, depending on as yet
undisclosed well crafted literal values, allows the column names to be
elegantly specified in addition to the row values.

Also sent through sqlite3_exec():

SELECT * FROM (VALUES ("1",2),(3,4));
1,
1,2
3,4




On Sat, Jul 8, 2017 at 6:01 PM, Simon Slavin  wrote:

>
>
> On 9 Jul 2017, at 1:44am, Keith Medcalf  wrote:
>
> > I can't visualize what you mean.  Something like:
> >
> > create view constants (col1, col2) as values (1,1), (1,2);
> >
> > seems the most straightforward to me, and allows you to assign column
> names to the data.
>
> But that’s not SQL syntax, is it ?  (Read that in a puzzled voice.  I
> genuinely don’t know.) You’re just using the word VALUE as if it does what
> you want.
>
> I was thinking of a CREATE TABLE command, and a SELECT command with "AS"
> clauses.  Or of something similar with a VIEW.
>
> Simon.
> ___
> 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] VALUES clause quirk or bug?

[Simon Slavin]

On 9 Jul 2017, at 1:44am, Keith Medcalf  wrote:

> I can't visualize what you mean.  Something like:
> 
> create view constants (col1, col2) as values (1,1), (1,2);
> 
> seems the most straightforward to me, and allows you to assign column names 
> to the data.

But that’s not SQL syntax, is it ?  (Read that in a puzzled voice.  I genuinely 
don’t know.) You’re just using the word VALUE as if it does what you want.

I was thinking of a CREATE TABLE command, and a SELECT command with "AS" 
clauses.  Or of something similar with a VIEW.

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

Re: [sqlite] VALUES clause quirk or bug?

[Keith Medcalf]

> >> If you want columns to have names, create a TABLE or VIEW,
> >> and specify what those names should be using "AS".

> > You would define the column names in the definition of the table or the
> > view.  There would not be any AS clauses (they will not work).
 
> Sorry, I meant that the "AS" clauses would be in the SELECT command which
> consulted the TABLE or VIEW, or could be in the SELECT command which was
> in the VIEW.

I can't visualize what you mean.  Something like:

create view constants (col1, col2) as values (1,1), (1,2);

seems the most straightforward to me, and allows you to assign column names to 
the data.




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

Re: [sqlite] VALUES clause quirk or bug?

[Simon Slavin]

On 9 Jul 2017, at 1:24am, Keith Medcalf  wrote:

>> If you want columns to have names, create a TABLE or VIEW, 
>> and specify what those names should be using "AS".
> 
> You would define the column names in the definition of the table or the view. 
>  There would not be any AS clauses (they will not work).

Sorry, I meant that the "AS" clauses would be in the SELECT command which 
consulted the TABLE or VIEW, or could be in the SELECT command which was in the 
VIEW.

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

Re: [sqlite] VALUES clause quirk or bug?

[Keith Medcalf]

> If you want columns to have names, create a TABLE or VIEW, 
> and specify what those names should be using "AS".

You would define the column names in the definition of the table or the view.  
There would not be any AS clauses (they will not work).




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

Re: [sqlite] VALUES clause quirk or bug?

[petern]

Thanks Ryan.

Thank you very much for the detailed analysis on how the column names are
arrived at.

Presumably the column names "",":1",":2",... will be stable in future.  I
use them frequently in the following pattern.

SELECT custom_aggregate("",":1") FROM  (VALUES (1,2),(3,4));

Or, with slightly more readable [column] delimiters:

SELECT custom_aggregate([],[:1]) FROM  (VALUES (1,2),(3,4));

The ideal of course would be a VALUE clause which supported a full
anonymous table syntax mentioned in reply to Simon:

SELECT custom_aggregate(c1,c2) FROM (VALUES (1,2),(3,4) AS (c1,c2));

I'm looking forward to reading more comments on this topic.

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

Re: [sqlite] VALUES clause quirk or bug?

[Simon Slavin]

On 9 Jul 2017, at 12:53am, petern  wrote:

> Is there some sort of easter egg there?  Is there a way for VALUE to take
> the first row exclusively as column names?  Perhaps there is a trick with
> other special characters?

The thing you’re doing does not have columns, just values.  Consider what this 
would mean:

SELECT * FROM (VALUES (0),(1,2),(3,4,5),(6,7,8,9));

Should this be valid SQL syntax ?  If so, how many columns does the third row 
have, and why ?

Although the shell tool is showing names because it’s expecting a table of 
results, you shouldn’t depend on them, and the next version of SQLite might 
pick other names.  If you want columns to have names, create a TABLE or VIEW, 
and specify what those names should be using "AS".

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

Re: [sqlite] VALUES clause quirk or bug?

[petern]

I was hoping someone could shed light on what is actually going on in the
VALUE clause.

Is there some sort of easter egg there?  Is there a way for VALUE to take
the first row exclusively as column names?  Perhaps there is a trick with
other special characters?

Trying the obvious only produces the unusable. The first row becomes both
column names and first row:

sqlite> SELECT * FROM (VALUES ("c1","c2"),("1",2));
c1,c2
c1,c2
1,2

Not working at all are these variants of directly supplying column names:

sqlite> SELECT * FROM (VALUES ((1)c1,(2)c2),(3,4));
Error: near "c1": syntax error

sqlite> SELECT * FROM (VALUES ((1)"c1",(2)"c2"),(3,4));
Error: near ""c1"": syntax error

sqlite> SELECT * FROM (VALUES ((1) AS "c1",(2) AS "c2"),(3,4));
Error: near "AS": syntax error

Now regarding the AS clause.  Were the merits of the supporting an ordinary
full anonymous table VALUE syntax ever considered?  For example:

sqlite> VALUES (1,2),(3,4) AS (c1,c2);
Error: near "AS": syntax error

sqlite> SELECT * FROM (VALUES (1,2),(3,4) AS (c1,c2));
Error: near "AS": syntax error

The desired output in both cases would of course be:

c1,c2
1,2
3,4


On Sat, Jul 8, 2017 at 2:20 PM, Simon Slavin  wrote:

>
>
> On 8 Jul 2017, at 8:36pm, petern  wrote:
>
> > Why does the choice of data value quotation mark influence the output
> > column name of the inline VALUES clause?
>
> I admire your set of examples, which show the behaviour well.
>
> Column names in SQLite are not dependable unless you have set them using
> an "AS" clause.  Not only can they vary with the type of value, as you have
> demonstrated, but they have changed with different versions of SQLite and
> may change again.  There are also PRAGMAs which change column names, though
> they are deprecated.
>
> If your code needs to depend on column names for anything other than a
> simple SELECT of TABLE columns, set them explicitly using "AS" for each
> column.
>
> > sqlite> SELECT * FROM (VALUES ("1",2),(3,4));
>
> This is not a recommended syntax for use with SQLite.  Strings should be
> enclosed in single quotes (apostrophes) as you did in another example.
> Double quotes are expected to refer to entity names, though they are rarely
> needed even for that.  Although SQLite does not complain when you use
> double quotes for values in the above way, its interpretation can be
> inconsistent and unhelpful.
>
> Simon.
> ___
> 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] VALUES clause quirk or bug?

[R Smith]

On 2017/07/08 9:36 PM, petern wrote:

Why does the choice of data value quotation mark influence the output
column name of the inline VALUES clause? [This quirk was the origin of a
recent bug in a current project.]


As to the "Why" question: It is because Double-Quotes denote 
Identifiers, not strings/values. This is well documented and stems from 
the SQL standard, not SQLite per se.



sqlite> .version
SQLite 3.19.3 2017-06-08 14:26:16
0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b

sqlite> .header on
sqlite> .mode csv
sqlite> SELECT * FROM (VALUES (1,2),(3,4));
"",:1
1,2
3,4


Here the standard SQLite thing happens: The first Column has a header of 
"Empty String" as an IDENTIFIER, and every next column has a :1, :2, 
:3... :n placeholder since the Empty column name can only appear once, 
and every next item identifier avoids duplication by increasing the 
placeholder header value.




sqlite> SELECT * FROM (VALUES ("1",2),(3,4));
1,""
1,2
3,4


Here the first column is regarded as an IDENTIFIER since the QP assumes 
you are referring to an identifier thanks to the double-quotes (but 
which is clear upon execution that there is no such identifier so SQLite 
helps by reverting to the string value, which is the root of the 
confusion), so it puts a 1, the second column is now no longer a 
duplication so it is free to use the Empty string, and the third+ 
columns would again have to avoid duplication by using the :1, :2, etc.




sqlite> SELECT * FROM (VALUES ('1',2),(3,4));
"",:1
1,2
3,4


Here the first column is back to a normal value (non-identifier exactly 
like in the 1st example) and so it can show the empty string and the 
rest playing ball...





I am aware that a less quirky "column" output column naming is available
if the VALUES clause is evaluated directly.  See below.  However, this form
is not applicable for task at hand, specifying inline constant tables
within a query.


Doesn't matter - Just use single quotes for values (in stead of double 
quotes which are reserved for identifier names as per the SQL standard) 
and you can count on the returned headers being consistent - at least 
for any single release of SQLite. If you need column names to be 
specific forever across multiple releases with some modicum of surety, 
then (as Simon noted) you need to specify the names exactly in either a 
CTE or a named sub-query.


Suggested Examples:


SELECT 1 AS AX, 2 AS BX, 3 AS CX UNION ALL
SELECT * FROM (VALUES (2,3,4),(5,6,7),(10,11,12));

  --  AX  |  BX  |  CX
  --  |  | 
  --   1  |   2  |   3
  --   2  |   3  |   4
  --   5  |   6  |   7
  --  10  |  11  |  12


WITH C(AX,BX,CX) AS (VALUES (1,2,3),(2,3,4),(5,6,7),(10,11,12))
SELECT * FROM C;


  --  AX  |  BX  |  CX
  --  |  | 
  --   1  |   2  |   3
  --   2  |   3  |   4
  --   5  |   6  |   7
  --  10  |  11  |  12


Good luck!
Ryan

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

Re: [sqlite] VALUES clause quirk or bug?

[Simon Slavin]

On 8 Jul 2017, at 8:36pm, petern  wrote:

> Why does the choice of data value quotation mark influence the output
> column name of the inline VALUES clause?

I admire your set of examples, which show the behaviour well.

Column names in SQLite are not dependable unless you have set them using an 
"AS" clause.  Not only can they vary with the type of value, as you have 
demonstrated, but they have changed with different versions of SQLite and may 
change again.  There are also PRAGMAs which change column names, though they 
are deprecated.

If your code needs to depend on column names for anything other than a simple 
SELECT of TABLE columns, set them explicitly using "AS" for each column.

> sqlite> SELECT * FROM (VALUES ("1",2),(3,4));

This is not a recommended syntax for use with SQLite.  Strings should be 
enclosed in single quotes (apostrophes) as you did in another example.  Double 
quotes are expected to refer to entity names, though they are rarely needed 
even for that.  Although SQLite does not complain when you use double quotes 
for values in the above way, its interpretation can be inconsistent and 
unhelpful.

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

[sqlite] VALUES clause quirk or bug?

[petern]

Why does the choice of data value quotation mark influence the output
column name of the inline VALUES clause? [This quirk was the origin of a
recent bug in a current project.]

sqlite> .version
SQLite 3.19.3 2017-06-08 14:26:16
0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b

sqlite> .header on
sqlite> .mode csv
sqlite> SELECT * FROM (VALUES (1,2),(3,4));
"",:1
1,2
3,4
sqlite> SELECT * FROM (VALUES ("1",2),(3,4));
1,""
1,2
3,4
sqlite> SELECT * FROM (VALUES ('1',2),(3,4));
"",:1
1,2
3,4

I am aware that a less quirky "column" output column naming is available
if the VALUES clause is evaluated directly.  See below.  However, this form
is not applicable for task at hand, specifying inline constant tables
within a query.

sqlite> VALUES ("1",2),(3,4);
column1,column2
1,2
3,4

Yes, I also thought carefully about the WITH clause.  See below.  While the
WITH clause is natural for brief queries in a few columns, the wordiness an
unnatural order is not helpful for local constant representation in the
very lengthy queries needed for by this particular project.

sqlite> WITH Constants(c1,c2) AS (VALUES ("1",2),(3,4)) SELECT * FROM
Constants;
c1,c2
1,2
3,4

Is there anything I missed?  Are there other undocumented tricks of the
VALUE clause that could help or hinder my quest?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users