Re: [sqlite] VALUES clause quirk or bug?

ers@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

Re: [sqlite] VALUES clause quirk or bug?

(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

Re: [sqlite] VALUES clause quirk or bug?

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

Re: [sqlite] VALUES clause quirk or bug?

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 >

Re: [sqlite] VALUES clause quirk or bug?

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.

Re: [sqlite] VALUES clause quirk or bug?

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

Re: [sqlite] VALUES clause quirk or bug?

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.

Re: [sqlite] VALUES clause quirk or bug?

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

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

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

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

Re: [sqlite] VALUES clause quirk or bug?

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

Re: [sqlite] VALUES clause quirk or bug?

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

Re: [sqlite] VALUES clause quirk or bug?

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

Re: [sqlite] VALUES clause quirk or bug?

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

Re: [sqlite] VALUES clause quirk or bug?

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

Re: [sqlite] VALUES clause quirk or bug?

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

Re: [sqlite] VALUES clause quirk or bug?

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

Re: [sqlite] VALUES clause quirk or bug?

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

Re: [sqlite] VALUES clause quirk or bug?

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

[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