Re: [sqlite] values ?

[David Raymond]

Normally I think you'd just loop through each on their own, since they don't 
have any dependencies on each other.

select 1 from foo where id = ?;--process if no records returned

But you could also do

with inputValues (inputValue) as (values (?), (?), (?))
select inputValue from inputValues
where not exists (select 1 from foo where id = inputValue);

But there you have to change the query itself depending on how many input 
values you have, etc.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Mark Wagner
Sent: Tuesday, 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 select '2' as x union select '3 as x where x not in
(select id from foo);

Picture the 1,2,3 as some form of input which requires further processing
if we don't have rows for them.

Perhaps there's a better way to do this that I'm not thinking of.


On Tue, Dec 12, 2017 at 11:07 AM, Stephen Chrzanowski <pontia...@gmail.com>
wrote:

> 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 <m...@google.com> wrote:
>
> > Argh.  Yes, I was on 3.8.2.  Thanks!
> >
> > On Tue, Dec 12, 2017 at 10:45 AM, Richard Hipp <d...@sqlite.org> wrote:
> >
> > > On 12/12/17, Mark Wagner <m...@google.com> 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 the right people but what am I missing?
> > >
> > > It probably means you are using an older version of SQLite.  The
> > > syntax you describe as introduced in version 3.8.3 (2014-02-03).
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > >
> > ___
> > 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
>
___
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 ?

[Mark Wagner]

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 the 1,2,3 as some form of input which requires further processing
if we don't have rows for them.

Perhaps there's a better way to do this that I'm not thinking of.


On Tue, Dec 12, 2017 at 11:07 AM, Stephen Chrzanowski 
wrote:

> 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, 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 the right people but what am I missing?
> > >
> > > It probably means you are using an older version of SQLite.  The
> > > syntax you describe as introduced in version 3.8.3 (2014-02-03).
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > >
> > ___
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values ?

[Stephen Chrzanowski]

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, 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 the right people but what am I missing?
> >
> > It probably means you are using an older version of SQLite.  The
> > syntax you describe as introduced in version 3.8.3 (2014-02-03).
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> >
> ___
> 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 ?

[Mark Wagner]

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 values('foo'); and get a
> row
> > with a single column whose value is 'foo'.  But I get a syntax error.
> >
> > Probably obvious to the right people but what am I missing?
>
> It probably means you are using an older version of SQLite.  The
> syntax you describe as introduced in version 3.8.3 (2014-02-03).
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values ?

[Richard Hipp]

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 the right people but what am I missing?

It probably means you are using an older version of SQLite.  The
syntax you describe as introduced in version 3.8.3 (2014-02-03).
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values ?

[John McKown]

On Tue, Dec 12, 2017 at 12:34 PM, Mark Wagner <m...@google.com> 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 the right people but what am I missing?
>
> sqlite> values('foo', 'bar');
> Error: near "values": syntax error
>

​Works for me too:

$ sqlite3
SQLite version 3.14.2 2016-09-12 18:50:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> values(1,2)
   ...> ;
1|2
sqlite> values('foo','bar');
foo|bar
sqlite>
​
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values ?

[Bart Smissaert]

Works OK here.
What is your SQLite version?

RBS

On Tue, Dec 12, 2017 at 6:34 PM, Mark Wagner <m...@google.com> 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 the right people but what am I missing?
>
> sqlite> values('foo', 'bar');
> Error: near "values": syntax error
> ___
> 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

[sqlite] values ?

[Mark Wagner]

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 the right people but what am I missing?

sqlite> values('foo', 'bar');
Error: near "values": syntax error
___
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?

[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 <slav...@bigfraud.org> wrote:
> On 9 Jul 2017, at 1:44am, Keith Medcalf <kmedc...@dessus.com> 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 <slav...@bigfraud.org> wrote:

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

Re: [sqlite] values containing dash - not evaluated

[jason d]

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');
> sqlite> insert into Groups values('jean-baptiste');
> sqlite> select * from Groups where name='jean-baptiste';
> jean-baptiste
>
> If you don't get a results this way tje sqlite2 is the problem (which I 
> doubt).

I get the result perfectly. So this is not sqlite2 issue. I doubt it
could be sqlite issue so I never looked at it this way. But worth a
shot.

>
> Then do an sql .dump of your table.
> sqlite> .dump Groups
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE Groups (name varchar(10));
> INSERT INTO "Groups" VALUES('bob');
> INSERT INTO "Groups" VALUES('jean-baptiste');
> COMMIT;
>
> Then you should be able to see the SQL representation of the string and 
> perhaps see what your problem is.
Ok, i tried this suggestion on both the test table we made above and
my own current DB. I see the dashes.


>
> I don't know if sqlite2 has the .mode command, but if it does it's simpler 
> yet.
>
> sqlite> .mode insert
> sqlite> select * from Groups where name like('%jean%');
> INSERT INTO table VALUES('jean-baptiste');
>
I did not go this far. Weirdly enough all your testing showed me the
real cause. The query egenrated by PHP uses quotes around column names
for select statements. so if I use
sqlite> Select * from Groups where 'name' = 'Jean-baptiste';
// will not work
sqlite> Select * from Groups where name = 'Jean-baptiste';
// works
sqlite> Select * from "Groups" where "name" = 'jean-baptiste';
//works for double quotes around tabel and column names.

This is weird, just the day I posted all the problems I used double
quotes. I even tried with various combos.  The PHP framework I have
been using always generates the last combination of quotes so it
should have worked. I don't know the reason for this happening or
something has changed since then that I cannot find. I am going to run
the the queries again to see if I get results in PHP, if not this is
not an sqlite problem anymore, mor elikely a driver or framework
issue.
Either that or I am incredibly stupid for having wasted everyone's
time. Apologies if that is the case.

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

Re: [sqlite] values containing dash - not evaluated

[jason d]

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 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
>
> Good catch.  MS Office, under some circumstances, automatically replaces
> the '-' (minus sign) character with a hyphen ('‐').  Technically the hyphen
> is the right character to use to join two words, but since it doesn't have
> an easy key-combination many people don't type it and like the automatic
> conversion that Office does.  Annoyingly neither of these are actually
> dashes: there are n-dash ('–') and m-dash ('—') characters too.  So there
> are four characters that all look similar but do not have the same hash
> value in normal text processing.
>
> Simon.
>
> PS: Don't get me started on figure-dashes and graphical horizontal lines.
>  Unicode should not include graphical icons.  Bah humbug.
>
>
Michal and Simon, Yes this is what Igor pointed out too.

Igor, I am sorry I havent had the chance to write a test to check Hex values
dump yet.
by the way I used the dash on my keyboard which is next to the number 0 if
that helps what you guys are talking about. I use eclipse or notepad++ on
windows and Nano on Linux to code as I move from place to place. all of them
show me a dash that works everywhere. in fact when I place the values
retrieved from SQlite Select * query, on the telnet request to a server I
get the reply correctly for the values containing dash. for some reason only
SQLite2 is reporting what no else can see. Let me get that Hex dump so
things will get clearer. thanks a lot
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values containing dash - not evaluated

[Simon Slavin]

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 make it work
> try, maybe this is also your case

Good catch.  MS Office, under some circumstances, automatically replaces the 
'-' (minus sign) character with a hyphen ('‐').  Technically the hyphen is the 
right character to use to join two words, but since it doesn't have an easy 
key-combination many people don't type it and like the automatic conversion 
that Office does.  Annoyingly neither of these are actually dashes: there are 
n-dash ('–') and m-dash ('—') characters too.  So there are four characters 
that all look similar but do not have the same hash value in normal text 
processing.

Simon.

PS: Don't get me started on figure-dashes and graphical horizontal lines.  
Unicode should not include graphical icons.  Bah humbug.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values containing dash - not evaluated

[Michal Seliga]

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


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

Re: [sqlite] values containing dash - not evaluated

[Black, Michael (IS)]

First off confirm it's not a bug with sqlite2:
 
sqlite> create table Groups (name varchar(10));
sqlite> insert into Groups values('bob');
sqlite> insert into Groups values('jean-baptiste');
sqlite> select * from Groups where name='jean-baptiste';
jean-baptiste
 
If you don't get a results this way tje sqlite2 is the problem (which I doubt).
 
Then do an sql .dump of your table.
sqlite> .dump Groups
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Groups (name varchar(10));
INSERT INTO "Groups" VALUES('bob');
INSERT INTO "Groups" VALUES('jean-baptiste');
COMMIT;
 
Then you should be able to see the SQL representation of the string and perhaps 
see what your problem is.
 
I don't know if sqlite2 has the .mode command, but if it does it's simpler yet.
 
sqlite> .mode insert
sqlite> select * from Groups where name like('%jean%');
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 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 select statement. but if I
> select on any other column and then work on the resultset it is ok. for
> example I may choose column projectname since it does not have a dash (-) in
> it. The information is clearly there, just its as if it does not equate to
> anything at all.
>
> SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.

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 the dash in the middle is not U+002D (HYPHEN-MINUS) but some other 
Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex dump 
would tell.
--
Igor Tandetnik

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


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

Re: [sqlite] values containing dash - not evaluated

[jason d]

> 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 to
> SQLite.
>
> However, if all your strings are pure 7-bit ASCII (and all your examples so
> far were such), then encoding shouldn't matter.
>
I believe so but I used PHP built in functions to do INSERTs. I did specify
any encoding explicitly  but it's assumed to be UTF-8 by default.

>
> > they do in MySQL etc etc. In which case I could test different character
> > encoding to see what result I am getting in my tests.
>
> Can't you just retrieve the string exactly as SQLite reports it, and dump
> numeric values of each individual byte (which is what built-in hex()
> function does in SQLite3). Post the dump here, and we'll try to figure out
> the encoding.
> --
>
All right. I get it. I need to write something up to get the hex values of
strings in database. Get back to you soon with the dump. thanks.
Jason
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values containing dash - not evaluated

[Igor Tandetnik]

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 INSERT or UPDATE?

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 to SQLite.

However, if all your strings are pure 7-bit ASCII (and all your examples so far 
were such), then encoding shouldn't matter.

> they do in MySQL etc etc. In which case I could test different character
> encoding to see what result I am getting in my tests.

Can't you just retrieve the string exactly as SQLite reports it, and dump 
numeric values of each individual byte (which is what built-in hex() function 
does in SQLite3). Post the dump here, and we'll try to figure out the encoding.
-- 
Igor Tandetnik

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

Re: [sqlite] values containing dash - not evaluated

[jason d]

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%';
> >>
> >> My guess is, you either have leading and/or trailing whitespace around
> the
> >> value, or the dash in the middle is not U+002D (HYPHEN-MINUS) but some
> other
> >> Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex
> >> dump would tell.
> >>
> > this is what I got. I am using SQLite version 2.8.17
> >
> > SQL error: no such function: hex
>
> Well, I don't have the reference for SQLite2, so you'll have to do some
> investigative work here. Isn't there some way to inspect actual character
> codes comprising a string? Perhaps you can write a test application to do
> that.
>
> 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 INSERT or UPDATE? like
they do in MySQL etc etc. In which case I could test different character
encoding to see what result I am getting in my tests. I have already tried
getting output in HTML with different encodings. however it does not give
away anything visually. For example if I use PHP to generate pretty URLs
with the dashes values i get the URL correctly with the dashes in the
browser address bar. Same results in command line. The dash is always
visible. So I am theorizing here, that if I could instead insert in
different encodings and then run a test to match against the values it might
give me which encoding is correct. Thanks for helping me brain storm on this
one.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values containing dash - not evaluated

[Igor Tandetnik]

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 the dash in the middle is not U+002D (HYPHEN-MINUS) but some other
>> Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex
>> dump would tell.
>> 
> this is what I got. I am using SQLite version 2.8.17
> 
> SQL error: no such function: hex

Well, I don't have the reference for SQLite2, so you'll have to do some 
investigative work here. Isn't there some way to inspect actual character codes 
comprising a string? Perhaps you can write a test application to do that.
-- 
Igor Tandetnik

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

Re: [sqlite] values containing dash - not evaluated

[jason d]

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 shows up. and i have 40,000 records.
> > any with dashes do not give any result on a pure select statement. but if
> I
> > select on any other column and then work on the resultset it is ok. for
> > example I may choose column projectname since it does not have a dash (-)
> in
> > it. The information is clearly there, just its as if it does not equate
> to
> > anything at all.
> >
> > SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.
>
> 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 the dash in the middle is not U+002D (HYPHEN-MINUS) but some other
> Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex
> dump would tell.
> --
> Igor Tandetnik
>
> this is what I got. I am using SQLite version 2.8.17

SQL error: no such function: hex
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values containing dash - not evaluated

[Igor Tandetnik]

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 select statement. but if I
> select on any other column and then work on the resultset it is ok. for
> example I may choose column projectname since it does not have a dash (-) in
> it. The information is clearly there, just its as if it does not equate to
> anything at all.
> 
> SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.

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 the dash in the middle is not U+002D (HYPHEN-MINUS) but some other 
Unicode character that looks like a dash, e.g. U+2013 (EN DASH). The hex dump 
would tell.
-- 
Igor Tandetnik

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

Re: [sqlite] values containing dash - not evaluated

[jason d]

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 responding.
>
> You're welcome.  New text below the text you're quoting, please.  English
> is read top to bottom.
>
> > 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.
>
> You do not want quotes of any kind around 'name'.  Try it exactly as I
> wrote it below and see if that works.
>
> > It does not work as
> > expected, no results are returned an no error is thrown.
>
> Then you have no records in your table that match your search criterion.
>  Are you sure you really do have a record for 'bob' ?  How do you prove it ?
>
> Simon.
>


sorry about the top posting.

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 select statement. but if I
select on any other column and then work on the resultset it is ok. for
example I may choose column projectname since it does not have a dash (-) in
it. The information is clearly there, just its as if it does not equate to
anything at all.

SELECT * from Groups WHERE name = 'jean-baptiste' ; zero result.

returns zero results and yes it is in database.

sorry but i just tried this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values containing dash - not evaluated

[Simon Slavin]

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 
read top to bottom.

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

You do not want quotes of any kind around 'name'.  Try it exactly as I wrote it 
below and see if that works.

> It does not work as
> expected, no results are returned an no error is thrown.

Then you have no records in your table that match your search criterion.  Are 
you sure you really do have a record for 'bob' ?  How do you prove it ?

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

Re: [sqlite] values containing dash - not evaluated

[jason d]

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, Apr 26, 2010 at 10:32 AM, Simon Slavin  wrote:

>
> 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 string.  So try
> something like
>
> SELECT * FROM names WHERE name = 'bob'
>
> and see if that works any better.
>
> > the column name is TEXT type (if that means anything is sqlite)
> > I have tried with VARCHAR but I get the same result.
>
> SQLite doesn't have a VARCHAR type.  It interprets it as identical to TEXT.
>  Won't do any harm to called it VARCHAR, I'm just telling you there's no
> point in trying it.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] values containing dash - not evaluated

[Simon Slavin]

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 string.  So try something like

SELECT * FROM names WHERE name = 'bob'

and see if that works any better.

> the column name is TEXT type (if that means anything is sqlite)
> I have tried with VARCHAR but I get the same result.

SQLite doesn't have a VARCHAR type.  It interprets it as identical to TEXT.  
Won't do any harm to called it VARCHAR, I'm just telling you there's no point 
in trying it.

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

[sqlite] values containing dash - not evaluated

[jason d]

Hi there everyone,
its very hard to find sqlite users so I am glad I joined this mailing list.
Recently it seems a lot of people are using sqlite which I dont get but
anyways.

I am still using sqlite2 as the libraries I use do not support sqlite3
(yet). I am using it for web development.

I ran into a strange problem recently. seems like a bug but i am no guru on
sql internals. so  here goes.

Lets say I have a colum "names" and have values "bob", "jones" and
"diana-rogers"
when I use

Select * from names where "name" = "bob";

it works fine. but whenever I use "name" = "diana-rogers"
or anything with a dash in it, it wont work. I have tried with PHP and with
command line (linux)
but there is no error and i get return values as if there really does not
exist this filed.

the column name is TEXT type (if that means anything is sqlite)
I have tried with VARCHAR but I get the same result.

I have hit a brick wall with this problem and have been at it for a couple
of weeks. trying everything I know and can see on gazillionth google search.

If anyone has any info on this and can please share it will be deeply
appreciated and given virtual cookies :D

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