Re: [sqlite] values ?

, December 12, 2017 2:26 PM To: SQLite mailing list Subject: Re: [sqlite] values ? My use case was this. For some given input, find which of those input values do not have corresponding rows in a given table. In other words something like this (but values seemed easier). select '1' as x union

Re: [sqlite] values ?

My use case was this. For some given input, find which of those input values do not have corresponding rows in a given table. In other words something like this (but values seemed easier). select '1' as x union select '2' as x union select '3 as x where x not in (select id from foo); Picture

Re: [sqlite] values ?

Nifty... but... With no option for "where" or "order by", where would this come in useful? On Tue, Dec 12, 2017 at 1:48 PM, Mark Wagner wrote: > Argh. Yes, I was on 3.8.2. Thanks! > > On Tue, Dec 12, 2017 at 10:45 AM, Richard Hipp wrote: > > > On 12/12/17,

Re: [sqlite] values ?

Argh. Yes, I was on 3.8.2. Thanks! On Tue, Dec 12, 2017 at 10:45 AM, Richard Hipp wrote: > On 12/12/17, Mark Wagner wrote: > > My reading of https://sqlite.org/syntax/select-core.html makes me think > > that I should be able to issue something like

Re: [sqlite] values ?

On 12/12/17, Mark Wagner wrote: > My reading of https://sqlite.org/syntax/select-core.html makes me think > that I should be able to issue something like values('foo'); and get a row > with a single column whose value is 'foo'. But I get a syntax error. > > Probably obvious to

Re: [sqlite] values ?

On Tue, Dec 12, 2017 at 12:34 PM, Mark Wagner wrote: > My reading of https://sqlite.org/syntax/select-core.html makes me think > that I should be able to issue something like values('foo'); and get a row > with a single column whose value is 'foo'. But I get a syntax error. > >

Re: [sqlite] values ?

Works OK here. What is your SQLite version? RBS On Tue, Dec 12, 2017 at 6:34 PM, Mark Wagner wrote: > My reading of https://sqlite.org/syntax/select-core.html makes me think > that I should be able to issue something like values('foo'); and get a row > with a single column

Re: [sqlite] VALUES clause quirk or bug?

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

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

Re: [sqlite] values containing dash - not evaluated

On Mon, Apr 26, 2010 at 7:59 PM, Black, Michael (IS) wrote: > > First off confirm it's not a bug with sqlite2: > Michael , thank you for this checklist.  Here is what I have. > > sqlite> create table Groups (name varchar(10)); > sqlite> insert into Groups values('bob'); >

Re: [sqlite] values containing dash - not evaluated

On Mon, Apr 26, 2010 at 8:23 PM, Simon Slavin wrote: > > On 26 Apr 2010, at 1:04pm, Michal Seliga wrote: > > > i had similar problems and it was caused by microsoft office > > it didn't used ordinary dash but some strange character with different > > ascii code - so search

Re: [sqlite] values containing dash - not evaluated

On 26 Apr 2010, at 1:04pm, Michal Seliga wrote: > i had similar problems and it was caused by microsoft office > it didn't used ordinary dash but some strange character with different > ascii code - so search based on it always failed > i had to convert these strange dashes to ordinary ones to

Re: [sqlite] values containing dash - not evaluated

i had similar problems and it was caused by microsoft office it didn't used ordinary dash but some strange character with different ascii code - so search based on it always failed i had to convert these strange dashes to ordinary ones to make it work try, maybe this is also your case

Re: [sqlite] values containing dash - not evaluated

; INSERT INTO table VALUES('jean-baptiste'); Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Igor Tandetnik Sent: Sun 4/25/2010 10:28 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] values contain

Re: [sqlite] values containing dash - not evaluated

> I don't know about SQLite2, but SQLite3 always expects strings in UTF-8 or > UTF-16 (depending on what API function you use: those that want UTF-16 > usually have "16" somewhere in their names). If you have a string in some > other encoding, you need to convert it to UTF-{8,16} before passing it

Re: [sqlite] values containing dash - not evaluated

jason d wrote: >> Even though I could not run your test, I believe you may be on to something > here. I suspected that encoding is a problem but I cannot seem to get > anything that explains this behaviour until you mention this. Does Sqlite2 > have anyway of specifying character encoding during

Re: [sqlite] values containing dash - not evaluated

On Mon, Apr 26, 2010 at 11:50 AM, Igor Tandetnik wrote: > jason d wrote: > > On Mon, Apr 26, 2010 at 11:28 AM, Igor Tandetnik >wrote: > >> What does this statement return: > >> > >> select name, hex(name) from Groups > >> where name like '%jean%'; > >>

Re: [sqlite] values containing dash - not evaluated

jason d wrote: > On Mon, Apr 26, 2010 at 11:28 AM, Igor Tandetnik wrote: >> What does this statement return: >> >> select name, hex(name) from Groups >> where name like '%jean%'; >> >> My guess is, you either have leading and/or trailing whitespace around the >> value, or

Re: [sqlite] values containing dash - not evaluated

On Mon, Apr 26, 2010 at 11:28 AM, Igor Tandetnik wrote: > jason d wrote: > > I believe you misunderstood my problem. Its not that records dont exist. > and > > select statement for Bob does work. a select * does display all the data. > > its the names with dashes that dont

Re: [sqlite] values containing dash - not evaluated

jason d wrote: > I believe you misunderstood my problem. Its not that records dont exist. and > select statement for Bob does work. a select * does display all the data. > its the names with dashes that dont shows up. and i have 40,000 records. > any with dashes do not give any result on a pure

Re: [sqlite] values containing dash - not evaluated

On Mon, Apr 26, 2010 at 11:01 AM, Simon Slavin wrote: > On Mon, Apr 26, 2010 at 10:32 AM, Simon Slavin > wrote: > > > >> SELECT * FROM names WHERE name = 'bob' > > On 26 Apr 2010, at 3:54am, jason d wrote: > > > Hello Simon, > > First , thank you for

Re: [sqlite] values containing dash - not evaluated

On Mon, Apr 26, 2010 at 10:32 AM, Simon Slavin wrote: > >> SELECT * FROM names WHERE name = 'bob' On 26 Apr 2010, at 3:54am, jason d wrote: > Hello Simon, > First , thank you for responding. You're welcome. New text below the text you're quoting, please. English is

Re: [sqlite] values containing dash - not evaluated

Hello Simon, First , thank you for responding. Yes maybe in the email i used double quotes, but I have actually tried every quote/ quoteless combination. In fact initially the SQL was in single quotes. It does not work as expected, no results are returned an no error is thrown. Jason On Mon,

Re: [sqlite] values containing dash - not evaluated

On 26 Apr 2010, at 3:17am, jason d wrote: > when I use > > Select * from names where "name" = "bob"; > > it works fine. but whenever I use "name" = "diana-rogers" SQLite uses single quotes for strings, not double quotes. And the thing 'name' is meant to be the name of a column, not a fixed