On Sat, Jul 8, 2017 at 8:50 PM, petern <peter.nichvolo...@gmail.com> 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
