VALUES clause quirk or bug?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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<i>" 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

Simon Slavin-3


On 8 Jul 2017, at 8:36pm, petern <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

R Smith
In reply to this post by petern

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<i>" 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

petern
In reply to this post by Simon Slavin-3
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 <[hidden email]> wrote:

>
>
> On 8 Jul 2017, at 8:36pm, petern <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

Simon Slavin-3


On 9 Jul 2017, at 12:53am, petern <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

petern
In reply to this post by R Smith
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

Keith Medcalf
In reply to this post by Simon Slavin-3


> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

Simon Slavin-3


On 9 Jul 2017, at 1:24am, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

Simon Slavin-3


On 9 Jul 2017, at 1:44am, Keith Medcalf <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]> wrote:

>
>
> On 9 Jul 2017, at 1:44am, Keith Medcalf <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

Scott Robison-2
On Sat, Jul 8, 2017 at 8:50 PM, petern <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

Simon Slavin-3
In reply to this post by petern


On 9 Jul 2017, at 3:50am, petern <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

R Smith
In reply to this post by petern
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.

</whitenoise>

Best of luck!
Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

Clemens Ladisch
In reply to this post by petern
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

Keith Medcalf
In reply to this post by petern

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
-------------------  -------------------
5                    8
6                    10
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
-------------------  -------------------
5                    8
6                    10
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:[hidden email]]
> 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 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 <[hidden email]> wrote:
>
> >
> >
> > On 9 Jul 2017, at 1:44am, Keith Medcalf <[hidden email]> 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
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

Simon Slavin-3


On 9 Jul 2017, at 9:53pm, Keith Medcalf <[hidden email]> wrote:

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

Presumably

<http://www.sqlite.org/src/info/70096c505d702a96>

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

<https://sqlite.org/c3ref/column_name.html>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: VALUES clause quirk or bug?

David Raymond
In reply to this post by Keith Medcalf
(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.000000 sys 0.000000

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.000000 sys 0.000000

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.000000 sys 0.000000

sqlite>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...