[sqlite] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

[Keith Maxwell]

sqlite-users,

Can anyone please explain the results of the query below? I have tried
create a very simple test case. In brief returning 9 rows each starting
with an integer between zero and nine. Then check if it less than or equal
to five, between zero and five and then less than six. I expect all of
these comparisons to have the same result. I can't understand the results,
and '.explain' isn't any help.

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> WITH
...> t1(X) AS (
...> SELECT 1
...> UNION ALL
...> SELECT X+1 FROM t1
...> LIMIT 9
...> )
...> ,t2(Y) AS (
...> SELECT abs(random() % 10) FROM t1
...> )
...> SELECT Y, Y <= 5, Y BETWEEN 0 AND 5, Y < 6 FROM t2;
7|1|1|0
3|1|0|0
2|1|1|1
2|0|0|1
0|1|1|1
3|1|1|0
5|1|1|0
6|1|1|0
0|1|1|1
sqlite>

Thanks in advance for your assistance.

Kind Regards

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

Re: [sqlite] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

[Hick Gunter]

My guess ist hat random() is being called once for each expression containing 
Y. Using constant values from a regular table works as expected. It is exactly 
the bytecode output by EXPLAIN  that could help to determine what 
happens, but unfortunately you did not post it.

asql> insert into t values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
asql> select y, y<=5, y between 0 and 5, y<6 from t;
y   y<=5  y between 0 and 5  y<6
--    -  ---
0   1 1  1
1   1 1  1
2   1 1  1
3   1 1  1
4   1 1  1
5   1 1  1
6   0 0  0
7   0 0  0
8   0 0  0
9   0 0  0

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Keith Maxwell
Gesendet: Dienstag, 11. April 2017 10:30
An: SQLite mailing list 
Betreff: [sqlite] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

sqlite-users,

Can anyone please explain the results of the query below? I have tried create a 
very simple test case. In brief returning 9 rows each starting with an integer 
between zero and nine. Then check if it less than or equal to five, between 
zero and five and then less than six. I expect all of these comparisons to have 
the same result. I can't understand the results, and '.explain' isn't any help.

SQLite version 3.18.0 2017-03-28 18:48:43
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>
sqlite> WITH
...> t1(X) AS (
...> SELECT 1
...> UNION ALL
...> SELECT X+1 FROM t1
...> LIMIT 9
...> )
...> ,t2(Y) AS (
...> SELECT abs(random() % 10) FROM t1
...> )
...> SELECT Y, Y <= 5, Y BETWEEN 0 AND 5, Y < 6 FROM t2;
7|1|1|0
3|1|0|0
2|1|1|1
2|0|0|1
0|1|1|1
3|1|1|0
5|1|1|0
6|1|1|0
0|1|1|1
sqlite>

Thanks in advance for your assistance.

Kind Regards

Keith Maxwell
___
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] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

[Clemens Ladisch]

Keith Maxwell wrote:
> Can anyone please explain the results of the query below?
>
> sqlite> WITH
> ...> t1(X) AS (
> ...> SELECT 1
> ...> UNION ALL
> ...> SELECT X+1 FROM t1
> ...> LIMIT 9
> ...> )
> ...> ,t2(Y) AS (
> ...> SELECT abs(random() % 10) FROM t1
> ...> )
> ...> SELECT Y, Y <= 5, Y BETWEEN 0 AND 5, Y < 6 FROM t2;
> 7|1|1|0
> 3|1|0|0
> 2|1|1|1
> 2|0|0|1
> 0|1|1|1
> 3|1|1|0
> 5|1|1|0
> 6|1|1|0
> 0|1|1|1

You have four separate calls to random() in each row.

This is because of subquery flattening.
http://www.sqlite.org/optoverview.html#flattening

You can force the database to create a temporary result for t2
by violating at least one of these rules, e.g., add "LIMIT 9" to
t2 and "WHERE 1" to the final SELECT.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unexpected results from <, <= and BETWEEN with a CTE in 3.18.0

[Keith Maxwell]

Thanks both that is really helpful!

I hadn't come across the query flattening optimisation before. The
documentation is helpful as is prepending EXPLAIN.

Thanks again.

Kind Regards

Keith

On 11 April 2017 at 09:55, Clemens Ladisch  wrote:

> Keith Maxwell wrote:
> > Can anyone please explain the results of the query below?
> >
> > sqlite> WITH
> > ...> t1(X) AS (
> > ...> SELECT 1
> > ...> UNION ALL
> > ...> SELECT X+1 FROM t1
> > ...> LIMIT 9
> > ...> )
> > ...> ,t2(Y) AS (
> > ...> SELECT abs(random() % 10) FROM t1
> > ...> )
> > ...> SELECT Y, Y <= 5, Y BETWEEN 0 AND 5, Y < 6 FROM t2;
> > 7|1|1|0
> > 3|1|0|0
> > 2|1|1|1
> > 2|0|0|1
> > 0|1|1|1
> > 3|1|1|0
> > 5|1|1|0
> > 6|1|1|0
> > 0|1|1|1
>
> You have four separate calls to random() in each row.
>
> This is because of subquery flattening.
> http://www.sqlite.org/optoverview.html#flattening
>
> You can force the database to create a temporary result for t2
> by violating at least one of these rules, e.g., add "LIMIT 9" to
> t2 and "WHERE 1" to the final SELECT.
>
>
> Regards,
> Clemens
> ___
> 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] Issue with LoadExtension with Spatialite

[Donny V.]

Have you guys seen this?
I think this is an issue with System.Data.SQLite for C#.
I'm having the same issue.
https://groups.google.com/forum/#!topic/spatialite-users/u2QZpQL_6ek

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

Re: [sqlite] json() number value parsing

[Rolf Ade]

Am 04/09/2017 10:34 AM, Olivier Mascia wrote:

Le 9 avr. 2017 à 03:08, Jens Alfke  a écrit :


On Apr 7, 2017, at 5:26 PM, Rolf Ade  wrote:
./sqlite3
SQLite version 3.19.0 2017-04-07 20:20:08
[...]
sqlite> select json(' { "this" : 000.23 } ');
{"this":000.23}

If I read RFC 7159 (http://www.rfc-editor.org/rfc/rfc7159.txt) correct
this should return: "Error: malformed JSON".


In this case I would go with Postel’s Law, paraphrased as “Be
strict in what you write, but lenient in what you read.” I don’t see
a point in disallowing something as trivial as redundant leading
zeroes.


Mr. Hipp has already fixed this:
https://www.sqlite.org/src/info/204e72f0080e8f08

If you think, that Postel's law should applied here, then Olivier
already pointed out rightfully:


If you'd go with Postal's Law, you would make it so:

sqlite> select json(' { "this" : 000.23 } ');  // be lenient in what you 
read
{"this":0.23} // be strict in what you write


I think, you do your users no good on the long run, if you accept not
recommendation compliant input (without explict request to do that by
the user). After all, JSON isn't a very complex standard and the
specification does not let much room (if ever) to argue if a certain
input string is valid or not. And JSON isn't an internet protocol, but
a data interchange format.

That all said I'm far from being religious about this. Even a "won't
fix" or a "works as designed" would have been OK with me.

This even wasn't a case I god bitten by this in the wild. For another
project I'm currently writing my 'own' JSON parser. To do that, I took
a look at the sqlite JSON parser implementation (just because I knew
it's on my hard disk and Mr. Hipps code is high quality). I stumbled
over this just by studying the sqlite json1.c code and wanted to make
sure, this implemenation detail is known and decided deliberately.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Visual 2017

[Tim Bateson]

Thanks for the update

On 6 Apr 2017 23:36, "Joe Mistachkin"  wrote:

>
> Tim Bateson wrote:
> >
> > Does anyone know when the SQLite for Visual Studio 2017 Design Time
> > Components will be released?
> >
>
> The current estimate is that support for Visual Studio 2017 will
> be included in the 1.0.106.0 release, which should be released at
> some point in the mid-June timeframe.
>
> --
> Joe Mistachkin @ https://urn.to/r/mistachkin
>
> ___
> 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] sqlite3_step and ORDER BY random() question.

[Reid Thompson]

On Mon, 2017-04-10 at 21:39 +0100, Simon Slavin wrote:
> On 10 Apr 2017, at 9:28pm, Reid Thompson  wrote:
> 
> > my questions are, if I prepare and utilize a statement for a result set
> > in the tens of thousands (or more) using a where clause along the lines
> > of
> >    "ORDER BY batch_id, due_datetime, random()"
> > 
> > 1) As I sqlite3_step through the result set, am I guaranteed to get each
> > row only once?
> 
> Yes.  _step() will return each row exactly once.  This assumes you will not 
> make any changes to the table those rows are in until you have finished 
> stepping.  If you make any changes to the table
> before the last call to _step() then things may get more complicated.

Thank you Simon.

so things may get more complicated:

Does 'more complicated' in the below scenario mean that there is
potential for records to be returned more than once if I use random()?

I have a manager process that manages the above step'ing. It gathers
records in batches of 30, marks them as claimed, and forwards those
batches to one of 50 worker processes. Each worker process performs work
based on each records data and when complete provides that information
back to the manager along with the record data.  The manager process
uses the returned information to update the records.  When a worker
completes a batche, the manager process sends them another batch. So
in most cases, I'm performing two updates to the record while step'ing
through the result set.


> > 2) is the order set only once on the first sqlite3_step, or does it
> > change with each sqlite3_step invocation?
> 
> Because no index already exists for that ORDER, you can imagine that SQLite 
> makes up a temporary index for the results when you do the first 
> sqlite3_step(), and deletes it when you execute
> sqlite3_reset() or sqlite3_finalize().
> 
> 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] sqlite3_step and ORDER BY random() question.

[Richard Hipp]

On 4/11/17, Reid Thompson  wrote:
> On Mon, 2017-04-10 at 21:39 +0100, Simon Slavin wrote:
>> On 10 Apr 2017, at 9:28pm, Reid Thompson  wrote:
>>
>> > my questions are, if I prepare and utilize a statement for a result set
>> > in the tens of thousands (or more) using a where clause along the lines
>> > of
>> >"ORDER BY batch_id, due_datetime, random()"
>> >
>> > 1) As I sqlite3_step through the result set, am I guaranteed to get
>> > each
>> > row only once?
>>
>> Yes.  _step() will return each row exactly once.  This assumes you will
>> not make any changes to the table those rows are in until you have
>> finished stepping.  If you make any changes to the table
>> before the last call to _step() then things may get more complicated.
>
>
> I have a manager process that manages the above step'ing. It gathers
> records in batches of 30, marks them as claimed, and forwards those
> batches to one of 50 worker processes. Each worker process performs work
> based on each records data and when complete provides that information
> back to the manager along with the record data.  The manager process
> uses the returned information to update the records.  When a worker
> completes a batche, the manager process sends them another batch. So
> in most cases, I'm performing two updates to the record while step'ing
> through the result set.

If you make changes to the table being queried in the middle of the
query, then there are no guarantees.

In your case, you are *probably* ok (for now) because the ORDER BY is
probably not being accomplished using an index.  But if batch_id and
due_datetime are both NOT NULL and unique, then the third ORDER BY
term will be ignored, and the UNIQUE index will be used to order the
output.  And in that case, if any of your worker threads modify the
batch_id or due_datetime fields, then there could be issues.

A safe work-around is to run the query into a TEMP table:

   CREATE TEMP TABLE work_to_do AS SELECT .

Then query the work_to_do table for stuff to be modified.
-- 
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] sqlite3_step and ORDER BY random() question.

[Simon Slavin]

On 11 Apr 2017, at 12:33pm, Reid Thompson  wrote:

> Does 'more complicated' in the below scenario mean that there is
> potential for records to be returned more than once if I use random()?

The problem is not to do with random().  The problem is to do with modifying a 
field used in your SELECT command.  Suppose you have an index on the column 
"name" and you execute

SELECT name FROM treetypes ORDER BY name

with the following names

apple
lemon
oak
orange
teak
willow

You execute _step() twice, returning the top two, then issue

UPDATE treetypes SET name="citrus" WHERE name="orange"

What do you expect SQLite to do for the SELECT ?  Should it return a list of 
names which is clearly not in the order you asked for ?  Should it miss out one 
of the rows even though you asked for them all ?  Should it return "orange" 
even though you have already changed that value ?

That’s the "more complicated".  And for the sanity of people debugging your 
code it’s better to ensure it never happens.

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

Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Reid Thompson]

On Tue, 2017-04-11 at 07:39 -0400, Richard Hipp wrote:
> On 4/11/17, Reid Thompson  wrote:
> > On Mon, 2017-04-10 at 21:39 +0100, Simon Slavin wrote:
> > > On 10 Apr 2017, at 9:28pm, Reid Thompson  wrote:
> > > 
> > > > my questions are, if I prepare and utilize a statement for a result set
> > > > in the tens of thousands (or more) using a where clause along the lines
> > > > of
> > > >    "ORDER BY batch_id, due_datetime, random()"
> > > > 
> > > > 1) As I sqlite3_step through the result set, am I guaranteed to get
> > > > each
> > > > row only once?
> > > 
> > > Yes.  _step() will return each row exactly once.  This assumes you will
> > > not make any changes to the table those rows are in until you have
> > > finished stepping.  If you make any changes to the table
> > > before the last call to _step() then things may get more complicated.
> > 
> > 
> > I have a manager process that manages the above step'ing. It gathers
> > records in batches of 30, marks them as claimed, and forwards those
> > batches to one of 50 worker processes. Each worker process performs work
> > based on each records data and when complete provides that information
> > back to the manager along with the record data.  The manager process
> > uses the returned information to update the records.  When a worker
> > completes a batche, the manager process sends them another batch. So
> > in most cases, I'm performing two updates to the record while step'ing
> > through the result set.
> 
> If you make changes to the table being queried in the middle of the
> query, then there are no guarantees.
> 
> In your case, you are *probably* ok (for now) because the ORDER BY is
> probably not being accomplished using an index.  But if batch_id and
> due_datetime are both NOT NULL and unique, then the third ORDER BY
> term will be ignored, and the UNIQUE index will be used to order the
> output.  And in that case, if any of your worker threads modify the
> batch_id or due_datetime fields, then there could be issues.
> 
> A safe work-around is to run the query into a TEMP table:
> 
>    CREATE TEMP TABLE work_to_do AS SELECT .
> 
> Then query the work_to_do table for stuff to be modified.

Thank you.  I'll make adjustments to my process.  One follow on
question.  This would be a concern regardless of whether random() is
used or not in the ORDER BY clause? 

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

Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Reid Thompson]

> 
> Thank you.  I'll make adjustments to my process.  One follow on
> question.  This would be a concern regardless of whether random() is
> used or not in the ORDER BY clause? 
> 
> reid

sorry - I posted this before receiving/seeing Simon's response.

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

Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Reid Thompson]

On Tue, 2017-04-11 at 13:17 +0100, Simon Slavin wrote:
> On 11 Apr 2017, at 12:33pm, Reid Thompson  wrote:
> 
> > Does 'more complicated' in the below scenario mean that there is
> > potential for records to be returned more than once if I use random()?
> 
> The problem is not to do with random().  The problem is to do with modifying 
> a field used in your SELECT command.  Suppose you have an index on the column 
> "name" and you execute
> 
> SELECT name FROM treetypes ORDER BY name
> 
> with the following names
> 
> apple
> lemon
> oak
> orange
> teak
> willow
> 
> You execute _step() twice, returning the top two, then issue
> 
> UPDATE treetypes SET name="citrus" WHERE name="orange"
> 
> What do you expect SQLite to do for the SELECT ?  Should it return a list of 
> names which is clearly not in the order you asked for ?  Should it miss out 
> one of the rows even though you asked for
> them all ?  Should it return "orange" even though you have already changed 
> that value ?
> 
> That’s the "more complicated".  And for the sanity of people debugging your 
> code it’s better to ensure it never happens.
> 
> Simon.

Thanks again for the explanation. 

> That’s the "more complicated".  And for the sanity of people debugging your 
> code it’s better to ensure it never happens.

;) yes. I'm debugging some inherited code.

I/and the original implementer, am/were more familiar with PostgreSQL's
MVCC.  So I think the issue was the assumption that the query being
stepped through would only ever see the rows as they were at the start
of the query and would walk through them from first to last.

Would
PRAGMA journal_mode=WAL;
provide that?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What does SQLITE_IOERR_VNODE mean?

[Yf Shen]

We have an application that connects to a small SQLite database in
read-only mode and do some very simple SELECT queries.

We recently upgraded Mac OS to 10.12, and the application started to
sporadically fail with a general (useless) error code SQLITE_IOERR. We
managed to get the extended error code SQLITE_IOERR_VNODE (6922), but could
not find any documentation about it or find where it is used in the source
code of SQLite except that we found where it was defined. Google knows
nothing about this error either probably because it is brand new. Can
someone shed some light?

Thanks,
Yufei
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What does SQLITE_IOERR_VNODE mean?

[Richard Hipp]

On 4/11/17, Yf Shen  wrote:
> We have an application that connects to a small SQLite database in
> read-only mode and do some very simple SELECT queries.
>
> We recently upgraded Mac OS to 10.12, and the application started to
> sporadically fail with a general (useless) error code SQLITE_IOERR. We
> managed to get the extended error code SQLITE_IOERR_VNODE (6922), but could
> not find any documentation about it or find where it is used in the source
> code of SQLite except that we found where it was defined. Google knows
> nothing about this error either probably because it is brand new. Can
> someone shed some light?

SQLITE_IOERR_VNODE is an error code used by proprietary modifications
to SQLite implemented by Apple for use on MacOS and iOS.  I am told
"The code indicates that a file relevant to the call was invalidated
by a dispatch vnode source event" but I do not understand what that
means.

-- 
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] sqlite3_step and ORDER BY random() question.

[Simon Slavin]

On 11 Apr 2017, at 2:16pm, Reid Thompson  wrote:

> I/and the original implementer, am/were more familiar with PostgreSQL's
> MVCC.  So I think the issue was the assumption that the query being
> stepped through would only ever see the rows as they were at the start
> of the query and would walk through them from first to last.
> 
> Would
> PRAGMA journal_mode=WAL;
> provide that?

Your situation would be that you have a single connection to the database, and 
it’s being used by two statements at the same time ?

You’re beyond what I can promise.  I need someone else to answer that.

The answer might be different if the SELECT was using a different connection to 
the one which was modifying the table.  Again, I hope someone else can help.

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

Re: [sqlite] Errors opening WAL-based databases with SQLITE_OPEN_READONLY

[Simon Slavin]

On 11 Apr 2017, at 12:33am, Simon Slavin  wrote:

> It may be that the documentation should be updated to reflect this.

After another look I’m going to withdraw everything I wrote in the thread.

 in section 1 says:

"It is not possible to open read-only WAL databases. The opening process must 
have write privileges for "-shm" wal-index shared memory file associated with 
the database, if that file exists, or else write access on the directory 
containing the database file if the "-shm" file does not exist."

J. King posted that earlier in the thread, but I didn’t see it before I 
replied.  I think it’s clear enough.  It’s the permissions granted by the OS 
that’s important, not the mode you tell SQLite to open the file in.

However, I went back and read your original post and you weren’t asking what I 
thought you were asking.  You may have found something strange going on.  My 
only doubt is whether the individual files (database and -shm file) were marked 
read-only.  If not, then there seems to be something wrong somewhere.

Sorry I wasted time upthread.

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

Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Richard Hipp]

On 4/11/17, Reid Thompson  wrote:
>
> I/and the original implementer, am/were more familiar with PostgreSQL's
> MVCC.  So I think the issue was the assumption that the query being
> stepped through would only ever see the rows as they were at the start
> of the query and would walk through them from first to last.
>

That is true, as long as the query and the workers are all using
separate database connections.  Trouble only arises when the query is
run partially - not to completion - and then the table being queried
is updated *on the same database connection*.  Changes implemented on
separate database connections are isolated and will not change the
result of the original query.

The problem scenario is impossible on PostgreSQL or any other
client-server database because the client/server databases run the
whole query all the way to the end before returning the results to
you.  It has nothing to do with MVCC.

You can emulate the PostgreSQL behavior by:

(1) Saving the query results in a TEMP table and then querying the
TEMP table separately and running the workers based on the TEMP table.

(2) Use the sqlite3_get_table() (or a similar wrapper of your own
concoction) to load all query results into memory prior to starting
the UPDATEs.

-- 
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] What does SQLITE_IOERR_VNODE mean?

[Simon Slavin]

On 11 Apr 2017, at 4:06pm, Richard Hipp  wrote:

> SQLITE_IOERR_VNODE is an error code used by proprietary modifications
> to SQLite implemented by Apple for use on MacOS and iOS.  I am told
> "The code indicates that a file relevant to the call was invalidated
> by a dispatch vnode source event" but I do not understand what that
> means.

I’m not surprised.  That’s not a useful explanation for anyone who isn’t 
messing with the OS X Virtual File System.  I’m a Mac expert and I have only 
the vaguest clue.  I think Apple is trying to implement memory-mapped file 
handling for SQLite in a very direct and efficient manner, and a strange 
combination of circumstances is triggering an error.

We're used to inodes in file storage.  Apple uses vnodes as well as inodes.  
They’re for storing a file’s metadata, like the file’s name and the last time 
the file was accessed.  If you change metadata VFS might decide to overwrite 
the contents of the existing vnode, or it might write the new data to a new 
vnode, then switch the file’s pointer to the new one and dispose of the old one.

What I think you’re being told is that you’re referring to a file, VFS is 
remembering the reference by pointing to the vnode, but that the file now has a 
new vnode because it has new metadata.  I have no clue why that might happen.  
vnodes are meant to be for internal use only.  If you’re not intentionally 
messing with them, you shouldn’t be getting vnode errors.

The nearest I can find is Working With the File System from



and then



There is one common use for vnodes: memory-mapped files.  The vnode pager 
maintains the relationship between the memory copy of a page of a file and the 
disk copy of the same page of the file.  So perhaps that piece of code is 
intentionally messing with memory-mapped file access.  Again, there’s no good 
reason for you to be getting errors with vnodes unless you’re messing at kernel 
level.

ObDisc: I am not familiar with this stuff.  I don’t program Macs at kernel 
level and don’t understand how it all works.  I’m just remembering stuff I read 
somewhere, possibly now obsolete.

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

Re: [sqlite] json_valid() crash on Windows - was: json() number value parsing

[Ralf Junker]

SQLite on Windows crashes when running this test:

  n_structure_10_opening_arrays.json

The crash results from a stack overflow because json_valid() is 
implemented using a recursive parser. All versions of json1.c up to 
current trunk are affected.


Here is a small SQL snippet that also triggers the overflow:

  SELECT json_valid(json) FROM (
WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt
WHERE x < 4) -- Eventually increase this!
SELECT group_concat('[', '') AS json FROM cnt);

Depending on compiler and available stack, you may need to increase 
4 to a larger number to reproduce the problem. sqlite3.exe 3.18.0 on 
Windows 7 ran out of stack at around 35000.


The problem might escape Linux testing because it usually has a much 
larger default stack size than Windows.


One solution would be to limit the parser's nesting depth as RFC 7159 
allows:


  https://tools.ietf.org/html/rfc7159#section-9

Ralf

On 10.04.2017 13:54, Richard Hipp wrote:

> SQLite returns true from json_valid() for the following cases which
> should allegedly be false:
>
>n_multidigit_number_then_00.json
>n_string_unescaped_newline.json
>n_string_unescaped_tab.json
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] SELECT this set ELSE that set

[R Smith]

Hi Howard,

You should re-post this on the SQLite-users list 
, it's more appropriate for it and 
you will get a lot more (and arguably better) responses from there. 
(I've gone ahead and included that list for you, just remove the dev 
list from replies to avoid duplication - thanks).


If I understand you properly, then what you need is a query that is 
fully listing either of two (or more) sets of filtered results, but not 
both / all.


Best would be to use a CTE or Sub-query set to get to something like this:

WITH A(y) AS (
SELECT Y FROM T WHERE x = 1
), B(y) AS (
SELECT Y FROM T WHERE x = 2
), C(y) AS (
SELECT Y FROM T WHERE x = 3
), K(ca, cb, cc) AS (
SELECT
(SELECT COUNT(*) FROM A) AS AV,
(SELECT COUNT(*) FROM B) AS BV,
(SELECT COUNT(*) FROM C) AS CV
)
  SELECT Y FROM K,A WHERE K.ca > 0 AND K.cb = 0 AND K.cc = 0
UNION ALL
  SELECT Y FROM K,B WHERE K.ca = 0 AND K.cb > 0 AND K.cc = 0
UNION ALL
  SELECT Y FROM K,C WHERE K.ca = 0 AND K.cb = 0 AND K.cc > 0
;


A better method might be to simply check the existence and not 
pre-render the queries in the CTE (and perhaps make it so the deciding 
bit prefers A over B over C), something like this:


WITH K(ca, cb, cc) AS (
SELECT
(SELECT COUNT(*) FROM T WHERE T.x=1),
(SELECT COUNT(*) FROM T WHERE T.x=2),
(SELECT COUNT(*) FROM T WHERE T.x=3)
)
  SELECT Y FROM K,T WHERE K.ca > 0 AND T.x = 1
UNION ALL
SELECT Y FROM K,T WHERE K.ca = 0 AND K.cb > 0 AND T.x = 2
UNION ALL
SELECT Y FROM K,T WHERE K.ca = 0 AND K.cb = 0 AND T.x = 3
;

(You can see one can even remove the cc term from the K CTE)

It can obviously also be simplified sans CTE into just:

  SELECT Y FROM T WHERE (SELECT COUNT(*) FROM T AS TA WHERE TA.x=1) > 0 
AND x = 1

UNION ALL
  SELECT Y FROM T WHERE (SELECT COUNT(*) FROM T AS TA WHERE TA.x=1) = 0 
AND (SELECT COUNT(*) FROM T AS TB WHERE TB.x=2) > 0 AND x = 2

UNION ALL
  SELECT Y FROM T WHERE (SELECT COUNT(*) FROM T AS TA WHERE TA.x=1) = 0 
AND (SELECT COUNT(*) FROM T AS TB WHERE TB.x=2) = 0 AND x = 3



Note also that SQLite Query Planner is somewhat clever in that it should 
run those WHERE clause COUNT queries only once, but it's probably safer 
to force that behaviour by doing the count up in the CTE as in the first 
example - though I agree with James on an often-made point that one 
should only ever state the relation that is correct in the SQL, and the 
Query engine should be trusted with figuring out the most efficient route.


Good luck!


On 2017/04/11 2:37 AM, Howard Kapustein wrote:


I’ve got a query that I want to match a set of data based on WHERE 
x=foo but if 0 matches, I want to match WHERE x=bar


Programmatically it’s

list=FindByX(x=1)

if list.isempty

   list=FindByX(x=2)

return list

To make matters worse it’s actually a multi table join e.g.

SELECT * FROM A

  INNER JOIN B ON A.a=B.a

  INNER JOIN C ON B.b=C.b

  INNER JOIN X ON C.c=X.c

  WHERE X.x=?

In theory it’s logically

SELECT * FROM (

IFNULL(SELECT * FROM A…WHERE X.x=foo, SELECT * FROM A…WHERE X.x=bar);

)

SQLITE has UNION which is the wrong verb. I want foo ELSE bar, not 
both, and not an interleaved result. All of just foo, or if no matches 
then all of just bar.


SQLITE’s WITH ctes seem like they might help, but in the end it still 
seems like >2 sub-queries to figure out the right set (foo vs bar), 
and a ton of complex SQL that’s uglier and questionable if perf any 
different than just doing the programmatic style with 1-2 SQL calls.


Any (sane) SQL solution for the query? Or is the programmatic approach 
the best answer?


  * Howard



___
sqlite-dev mailing list
sqlite-...@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev


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

Re: [sqlite] json_valid() crash on Windows - was: json() number value parsing

[Dominique Pellé]

Ralf Junker  wrote:

> SQLite on Windows crashes when running this test:
>
>   n_structure_10_opening_arrays.json
>
> The crash results from a stack overflow because json_valid() is implemented
> using a recursive parser. All versions of json1.c up to current trunk are
> affected.
>
> Here is a small SQL snippet that also triggers the overflow:
>
>   SELECT json_valid(json) FROM (
> WITH RECURSIVE
>   cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt
> WHERE x < 4) -- Eventually increase this!
> SELECT group_concat('[', '') AS json FROM cnt);
>
> Depending on compiler and available stack, you may need to increase 4 to
> a larger number to reproduce the problem. sqlite3.exe 3.18.0 on Windows 7
> ran out of stack at around 35000.
>
> The problem might escape Linux testing because it usually has a much larger
> default stack size than Windows.
>
> One solution would be to limit the parser's nesting depth as RFC 7159
> allows:
>
>   https://tools.ietf.org/html/rfc7159#section-9
>
> Ralf

A better solution is to avoid recursion when parsing JSON.

JSON parsing in Vim had the same kind of problem and it was
reworked in vim-8.0.169 to avoid recursion to be able to
parse n_structure_10_opening_arrays.json.
It can be a big change though.

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

Re: [sqlite] What does SQLITE_IOERR_VNODE mean?

[Jens Alfke]

Apple's darwin-dev mailing list, hosted at lists.apple.com 
, might be a good place to ask about this. 
(I'm subscribed to it, so I could forward a question if the OP doesn't want to 
go to the bother of subscribing.)

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

Re: [sqlite] [sqlite-dev] SELECT this set ELSE that set

[David Raymond]

Maybe:

with FooOrBar as (
select case exists(select 1 from X where x = foo)
when 1 then foo else bar end as FooOrBar),
select * from A
inner join B on A.a = B.a
inner join C on B.b = C.b
inner join X on C.c = X.c
where X.x in FooOrBar;

?

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Tuesday, April 11, 2017 1:56 PM
To: sqlite-...@mailinglists.sqlite.org; SQLite mailing list
Subject: Re: [sqlite] [sqlite-dev] SELECT this set ELSE that set

Hi Howard,

You should re-post this on the SQLite-users list 
, it's more appropriate for it and 
you will get a lot more (and arguably better) responses from there. 
(I've gone ahead and included that list for you, just remove the dev 
list from replies to avoid duplication - thanks).

If I understand you properly, then what you need is a query that is 
fully listing either of two (or more) sets of filtered results, but not 
both / all.

Best would be to use a CTE or Sub-query set to get to something like this:

WITH A(y) AS (
 SELECT Y FROM T WHERE x = 1
), B(y) AS (
 SELECT Y FROM T WHERE x = 2
), C(y) AS (
 SELECT Y FROM T WHERE x = 3
), K(ca, cb, cc) AS (
 SELECT
 (SELECT COUNT(*) FROM A) AS AV,
 (SELECT COUNT(*) FROM B) AS BV,
 (SELECT COUNT(*) FROM C) AS CV
)
   SELECT Y FROM K,A WHERE K.ca > 0 AND K.cb = 0 AND K.cc = 0
UNION ALL
   SELECT Y FROM K,B WHERE K.ca = 0 AND K.cb > 0 AND K.cc = 0
UNION ALL
   SELECT Y FROM K,C WHERE K.ca = 0 AND K.cb = 0 AND K.cc > 0
;


A better method might be to simply check the existence and not 
pre-render the queries in the CTE (and perhaps make it so the deciding 
bit prefers A over B over C), something like this:

WITH K(ca, cb, cc) AS (
 SELECT
 (SELECT COUNT(*) FROM T WHERE T.x=1),
 (SELECT COUNT(*) FROM T WHERE T.x=2),
 (SELECT COUNT(*) FROM T WHERE T.x=3)
)
   SELECT Y FROM K,T WHERE K.ca > 0 AND T.x = 1
UNION ALL
SELECT Y FROM K,T WHERE K.ca = 0 AND K.cb > 0 AND T.x = 2
UNION ALL
SELECT Y FROM K,T WHERE K.ca = 0 AND K.cb = 0 AND T.x = 3
;

(You can see one can even remove the cc term from the K CTE)

It can obviously also be simplified sans CTE into just:

   SELECT Y FROM T WHERE (SELECT COUNT(*) FROM T AS TA WHERE TA.x=1) > 0 
AND x = 1
UNION ALL
   SELECT Y FROM T WHERE (SELECT COUNT(*) FROM T AS TA WHERE TA.x=1) = 0 
AND (SELECT COUNT(*) FROM T AS TB WHERE TB.x=2) > 0 AND x = 2
UNION ALL
   SELECT Y FROM T WHERE (SELECT COUNT(*) FROM T AS TA WHERE TA.x=1) = 0 
AND (SELECT COUNT(*) FROM T AS TB WHERE TB.x=2) = 0 AND x = 3


Note also that SQLite Query Planner is somewhat clever in that it should 
run those WHERE clause COUNT queries only once, but it's probably safer 
to force that behaviour by doing the count up in the CTE as in the first 
example - though I agree with James on an often-made point that one 
should only ever state the relation that is correct in the SQL, and the 
Query engine should be trusted with figuring out the most efficient route.

Good luck!


On 2017/04/11 2:37 AM, Howard Kapustein wrote:
>
> I’ve got a query that I want to match a set of data based on WHERE 
> x=foo but if 0 matches, I want to match WHERE x=bar
>
> Programmatically it’s
>
> list=FindByX(x=1)
>
> if list.isempty
>
>list=FindByX(x=2)
>
> return list
>
> To make matters worse it’s actually a multi table join e.g.
>
> SELECT * FROM A
>
>   INNER JOIN B ON A.a=B.a
>
>   INNER JOIN C ON B.b=C.b
>
>   INNER JOIN X ON C.c=X.c
>
>   WHERE X.x=?
>
> In theory it’s logically
>
> SELECT * FROM (
>
> IFNULL(SELECT * FROM A…WHERE X.x=foo, SELECT * FROM A…WHERE X.x=bar);
>
> )
>
> SQLITE has UNION which is the wrong verb. I want foo ELSE bar, not 
> both, and not an interleaved result. All of just foo, or if no matches 
> then all of just bar.
>
> SQLITE’s WITH ctes seem like they might help, but in the end it still 
> seems like >2 sub-queries to figure out the right set (foo vs bar), 
> and a ton of complex SQL that’s uglier and questionable if perf any 
> different than just doing the programmatic style with 1-2 SQL calls.
>
> Any (sane) SQL solution for the query? Or is the programmatic approach 
> the best answer?
>
>   * Howard
>
>
>
> ___
> sqlite-dev mailing list
> sqlite-...@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev

___
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] Typo in documentation

[J. King]

The first paragraph in Section 2 of  states in 
part:

 ... And the SQLITE_OMIT_PROGESS_CALLBACK option is only usable by 
applications...

Note the missing R in PROGRESS.
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite - Interrogate Date/Time field Statement question

[Ron Barnes]

Hello all,

To everyone who helped me before - thank you very much!

I'm coding in Visual Basic .NET (Visual Studio 2015) Community.

I have to count a Date/Time field and the problem is, this field contains data 
in a format I'm not sure can be counted.

I need to count all the dates in the field but the dates are a combined Date 
and time in the format examples below.
My goal is to use the current Date/time ('NOW') and calculate the time 
difference in days, from my DB Sourced field. 

I need to capture...
Less than 1 month old
1 month old
2 months old
1 year old. 
all the way to greater than 10 years old. 

Is this even possible in SQLite and if so, how would I go about doing it?

I have been googling a few queries and come up blank.

I try this code and differing combinations of it but it always returns NULL.

SELECT CAST 
((datetime(julianday(datetime('now'))) - JulianDay(VI_Creation_Date)) As 
Integer)
FROM Volume_Information

Here is what I have to work with.

Table Name:
Volume_Information

Column name:
VI_Creation_Date

Date Format:
MM/DD/CCYY HH:MM:SS AM/PM

Examples:

10/30/2015 2:28:30 AM
2/13/2016 7:51:04 AM
5/15/2016 12:06:24 PM
10/7/2016 1:27:13 PM

Any Help would be greatly appreciated,

Thanks,

-Ron

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

Re: [sqlite] sqlite3_step and ORDER BY random() question.

[Keith Medcalf]

> I/and the original implementer, am/were more familiar with PostgreSQL's
> MVCC.  So I think the issue was the assumption that the query being
> stepped through would only ever see the rows as they were at the start
> of the query and would walk through them from first to last.
> 
> Would
> PRAGMA journal_mode=WAL;
> provide that?

Yes. WAL journal mode provides "Repeatable Read" isolation within a 
transaction.  This means that if you issue:

BEGIN;
SELECT .
wait 3 hours with lots of intervening updates to all the tables in the database 
including the ones in your select above, done on a different connection.
SELECT .
COMMIT;
SELECT .

Assuming that all the selects are the same, then the first two selects will 
return identical results.  All the updates will not be seen until after the 
read transaction is committed, so the third select will see the updates done in 
the updates that occurred on different connections.

If you do not explicitly BEGIN and COMMIT transactions, then SQLite3 
effectively wraps each statement in its own transaction.  Note that without 
WAL, the above example will prohibit updates on other connections during the 3 
hour perios you are holding the transaction open since without WAL, readers 
block writers and writers block readers.

Even in WAL mode changes to the database made ON THE SAME CONNECTION are 
immediately visible to every statement on that connection.


-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Reid Thompson
> Sent: Tuesday, 11 April, 2017 07:17
> To: sqlite-users@mailinglists.sqlite.org
> Subject: Re: [sqlite] sqlite3_step and ORDER BY random() question.
> 
> On Tue, 2017-04-11 at 13:17 +0100, Simon Slavin wrote:
> > On 11 Apr 2017, at 12:33pm, Reid Thompson 
> wrote:
> >
> > > Does 'more complicated' in the below scenario mean that there is
> > > potential for records to be returned more than once if I use random()?
> >
> > The problem is not to do with random().  The problem is to do with
> modifying a field used in your SELECT command.  Suppose you have an index
> on the column "name" and you execute
> >
> > SELECT name FROM treetypes ORDER BY name
> >
> > with the following names
> >
> > apple
> > lemon
> > oak
> > orange
> > teak
> > willow
> >
> > You execute _step() twice, returning the top two, then issue
> >
> > UPDATE treetypes SET name="citrus" WHERE name="orange"
> >
> > What do you expect SQLite to do for the SELECT ?  Should it return a
> list of names which is clearly not in the order you asked for ?  Should it
> miss out one of the rows even though you asked for
> > them all ?  Should it return "orange" even though you have already
> changed that value ?
> >
> > That’s the "more complicated".  And for the sanity of people debugging
> your code it’s better to ensure it never happens.
> >
> > Simon.
> 
> Thanks again for the explanation.
> 
> > That’s the "more complicated".  And for the sanity of people debugging
> your code it’s better to ensure it never happens.
> 
> ;) yes. I'm debugging some inherited code.
> 
> ___
> 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] Select Statement returning incorrect information

[Ron Barnes]

Hello All,

With the select statement below and my test data of 43 files, I expected the 
following results

22 'Less than 1MB'
4 'Less than 5MB'
7 'Less than 10MB'
4 'Less than 15MB'
6 'Less than 20MB'

Instead I get 
16 'Less than 1MB'
18 'Less than 5MB'
9 'Larger than 25GB'

I have been pulling my hair out trying to figure out where I went south.  If 
someone could, would you point out my mistake, please?


SELECTcategory, COUNT(*) AS Expr1
FROM(SELECT(CASE 
WHEN VI_File_Len < 1024000 THEN 'Less Than 1MB' 
WHEN VI_File_Len < 2048000 THEN 'Less Than 2MB' 
WHEN VI_File_Len < 512 THEN 'Less Than 5MB' 
WHEN VI_File_Len < 1024 THEN 'Less Than 10MB' 
WHEN VI_File_Len < 10240 THEN 'Less Than 100MB' 
WHEN VI_File_Len < 51200 THEN 'Less Than 500MB' 
WHEN VI_File_Len < 102400 THEN 'Less Than 1GB'
WHEN VI_File_Len < 204800 THEN 'Less Than 2 GB' 
WHEN VI_File_Len < 512000 THEN 'Less Than 5 GB' 
WHEN VI_File_Len < 1024000 THEN 'Less Than 10 GB' 
WHEN VI_File_Len < 1536000 THEN 'Less Than 15 GB' 
WHEN VI_File_Len < 2048 THEN 'Less Than 20 GB' 
WHEN VI_File_Len < 3584 THEN 'Less Than 25 GB' 
ELSE 'Larger Than 25GB' END)  AS category
FROM   Volume_Information) derivedtbl_1
GROUP BY category


Regards,

-Ron

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

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[Jim Callahan]

Can you convert the dates to ISO 8601 date time format?
https://en.wikipedia.org/wiki/ISO_8601

-MM-DD hh:mm:ss

ISO date strings (when zero filled) are sortable which necessarily includes
comparable (Java speak).
By "zero filled" I mean for March you have "03" and not just "3".

Then if you could generate/populate the boundary values in ISO format; the
comparisons would be straightforward and you could avoid the julian date
conversion.

Another disadvantage of Julian dates are the different base years used by
applications including Unix, MS Access, MS Excel for Windows and MS Excel
for MacIntosh. Each application is internally consistent, but the minute
you exchange data between applications...
https://support.microsoft.com/en-us/help/214330/differences-
between-the-1900-and-the-1904-date-system-in-excel

Your specification actually requires day counts; so you may need Julian
dates after all.

Jim Callahan
Orlando, FL



On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes  wrote:

> Hello all,
>
> To everyone who helped me before - thank you very much!
>
> I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
>
> I have to count a Date/Time field and the problem is, this field contains
> data in a format I'm not sure can be counted.
>
> I need to count all the dates in the field but the dates are a combined
> Date and time in the format examples below.
> My goal is to use the current Date/time ('NOW') and calculate the time
> difference in days, from my DB Sourced field.
>
> I need to capture...
> Less than 1 month old
> 1 month old
> 2 months old
> 1 year old.
> all the way to greater than 10 years old.
>
> Is this even possible in SQLite and if so, how would I go about doing it?
>
> I have been googling a few queries and come up blank.
>
> I try this code and differing combinations of it but it always returns
> NULL.
>
> SELECT CAST
> ((datetime(julianday(datetime('now'))) - JulianDay(VI_Creation_Date)) As
> Integer)
> FROM Volume_Information
>
> Here is what I have to work with.
>
> Table Name:
> Volume_Information
>
> Column name:
> VI_Creation_Date
>
> Date Format:
> MM/DD/CCYY HH:MM:SS AM/PM
>
> Examples:
>
> 10/30/2015 2:28:30 AM
> 2/13/2016 7:51:04 AM
> 5/15/2016 12:06:24 PM
> 10/7/2016 1:27:13 PM
>
> Any Help would be greatly appreciated,
>
> Thanks,
>
> -Ron
>
> ___
> 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] Select Statement returning incorrect information

[Richard Hipp]

On 4/11/17, Ron Barnes  wrote:
>
> I have been pulling my hair out trying to figure out where I went south.  If
> someone could, would you point out my mistake, please?

What does this query show:

  SELECT DISTINCT typeof(VI_File_Len) FROM Volume_Information;

And in particular, does it show that some of the VI_File_Len values are text?
-- 
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] Select Statement returning incorrect information

[Ron Barnes]

I figured it out.
I needed to add the Cast parameter. 

SELECTcategory, COUNT(*) AS Expr1
FROM(SELECT(CASE 
WHEN CAST(VI_File_Len AS INTEGER) < 1024000 THEN 'Less Than 1MB' 
WHEN CAST(VI_File_Len AS INTEGER) < 2048000 THEN 'Less Than 2MB' 
WHEN CAST(VI_File_Len AS INTEGER) < 512 THEN 'Less Than 5MB' 
WHEN CAST(VI_File_Len AS INTEGER) < 1024 THEN 'Less Than 10MB' 
WHEN CAST(VI_File_Len AS INTEGER) < 10240 THEN 'Less Than 100MB' 
WHEN CAST(VI_File_Len AS INTEGER) < 51200 THEN 'Less Than 500MB' 
WHEN CAST(VI_File_Len AS INTEGER) < 102400 THEN 'Less Than 1GB'
WHEN CAST(VI_File_Len AS INTEGER) < 204800 THEN 'Less Than 2 GB' 
WHEN CAST(VI_File_Len AS INTEGER) < 512000 THEN 'Less Than 5 GB' 
WHEN CAST(VI_File_Len AS INTEGER) < 1024000 THEN 'Less Than 10 GB' 
WHEN CAST(VI_File_Len AS INTEGER) < 1536000 THEN 'Less Than 15 GB' 
WHEN CAST(VI_File_Len AS INTEGER) < 2048 THEN 'Less Than 20 GB' 
WHEN CAST(VI_File_Len AS INTEGER) < 3584 THEN 'Less Than 25 GB' 
ELSE 'Larger Than 25GB' END)  AS category
FROM   Volume_Information) derivedtbl_1
GROUP BY category



-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Ron Barnes
Sent: Tuesday, April 11, 2017 9:15 PM
To: 'SQLite mailing list' 
Subject: [sqlite] Select Statement returning incorrect information

Hello All,

With the select statement below and my test data of 43 files, I expected the 
following results

22 'Less than 1MB'
4 'Less than 5MB'
7 'Less than 10MB'
4 'Less than 15MB'
6 'Less than 20MB'

Instead I get 
16 'Less than 1MB'
18 'Less than 5MB'
9 'Larger than 25GB'

I have been pulling my hair out trying to figure out where I went south.  If 
someone could, would you point out my mistake, please?


SELECTcategory, COUNT(*) AS Expr1
FROM(SELECT(CASE 
WHEN VI_File_Len < 1024000 THEN 'Less Than 1MB' 
WHEN VI_File_Len < 2048000 THEN 'Less Than 2MB' 
WHEN VI_File_Len < 512 THEN 'Less Than 5MB' 
WHEN VI_File_Len < 1024 THEN 'Less Than 10MB' 
WHEN VI_File_Len < 10240 THEN 'Less Than 100MB' 
WHEN VI_File_Len < 51200 THEN 'Less Than 500MB' 
WHEN VI_File_Len < 102400 THEN 'Less Than 1GB'
WHEN VI_File_Len < 204800 THEN 'Less Than 2 GB' 
WHEN VI_File_Len < 512000 THEN 'Less Than 5 GB' 
WHEN VI_File_Len < 1024000 THEN 'Less Than 10 GB' 
WHEN VI_File_Len < 1536000 THEN 'Less Than 15 GB' 
WHEN VI_File_Len < 2048 THEN 'Less Than 20 GB' 
WHEN VI_File_Len < 3584 THEN 'Less Than 25 GB' 
ELSE 'Larger Than 25GB' END)  AS category
FROM   Volume_Information) derivedtbl_1
GROUP BY category


Regards,

-Ron

___
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] Select Statement returning incorrect information

[Simon Slavin]

On 12 Apr 2017, at 2:27am, Ron Barnes  wrote:

> I needed to add the Cast parameter. 

Assuming you are actually storing integers, it might be better if you declared 
that column as integer in the first place.  Then you wouldn’t need the CAST.

However, well done for figuring it out, possibly with Richard’s help.

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

Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

[Ron Barnes]

Hi Jim,

I could alter the program that populates the Date/Time Column to the format you 
specify.  I'm trying real hard not to as that program has been in use for many 
years and it would be a significant undertaking to convert the program then 
convert the existing data.  Not saying I won't do it as I'm at that point, just 
wondering if it's possible to avoid that route.

If I converted the date/time field, would it be easier to create counts?

If you could, would you be able to offer a sample Select statement I can alter 
to fit my needs?  

Thank you very much for the reply!

Side note, I'll be visiting Disney in July!

Regards,

-Ron

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jim Callahan
Sent: Tuesday, April 11, 2017 9:15 PM
To: SQLite mailing list 
Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement question

Can you convert the dates to ISO 8601 date time format?
https://en.wikipedia.org/wiki/ISO_8601

-MM-DD hh:mm:ss

ISO date strings (when zero filled) are sortable which necessarily includes 
comparable (Java speak).
By "zero filled" I mean for March you have "03" and not just "3".

Then if you could generate/populate the boundary values in ISO format; the 
comparisons would be straightforward and you could avoid the julian date 
conversion.

Another disadvantage of Julian dates are the different base years used by 
applications including Unix, MS Access, MS Excel for Windows and MS Excel for 
MacIntosh. Each application is internally consistent, but the minute you 
exchange data between applications...
https://support.microsoft.com/en-us/help/214330/differences-
between-the-1900-and-the-1904-date-system-in-excel

Your specification actually requires day counts; so you may need Julian dates 
after all.

Jim Callahan
Orlando, FL



On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes  wrote:

> Hello all,
>
> To everyone who helped me before - thank you very much!
>
> I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
>
> I have to count a Date/Time field and the problem is, this field 
> contains data in a format I'm not sure can be counted.
>
> I need to count all the dates in the field but the dates are a 
> combined Date and time in the format examples below.
> My goal is to use the current Date/time ('NOW') and calculate the time 
> difference in days, from my DB Sourced field.
>
> I need to capture...
> Less than 1 month old
> 1 month old
> 2 months old
> 1 year old.
> all the way to greater than 10 years old.
>
> Is this even possible in SQLite and if so, how would I go about doing it?
>
> I have been googling a few queries and come up blank.
>
> I try this code and differing combinations of it but it always returns 
> NULL.
>
> SELECT CAST
> ((datetime(julianday(datetime('now'))) - JulianDay(VI_Creation_Date)) 
> As
> Integer)
> FROM Volume_Information
>
> Here is what I have to work with.
>
> Table Name:
> Volume_Information
>
> Column name:
> VI_Creation_Date
>
> Date Format:
> MM/DD/CCYY HH:MM:SS AM/PM
>
> Examples:
>
> 10/30/2015 2:28:30 AM
> 2/13/2016 7:51:04 AM
> 5/15/2016 12:06:24 PM
> 10/7/2016 1:27:13 PM
>
> Any Help would be greatly appreciated,
>
> Thanks,
>
> -Ron
>
> ___
> 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] Select Statement returning incorrect information

[Ron Barnes]

Hello Simon,

Got that one on my own!

The Data was created long before me but I do have the option to alter columns 
if needs be.

Hoping Richard can help out on the Date Select I'm struggling with!

Regards,
-Ron

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Simon Slavin
Sent: Tuesday, April 11, 2017 9:31 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Select Statement returning incorrect information


On 12 Apr 2017, at 2:27am, Ron Barnes  wrote:

> I needed to add the Cast parameter. 

Assuming you are actually storing integers, it might be better if you declared 
that column as integer in the first place.  Then you wouldn’t need the CAST.

However, well done for figuring it out, possibly with Richard’s help.

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] SQLite - Interrogate Date/Time field Statement question

[Jim Callahan]

This code:

SELECT ( substr('02/13/2016',7,4) || '-'
  || substr('02/13/2016',1,2) || '-'
  || substr('02/13/2016',4,2) ) ;

yields

2016-02-13

The above code, is dependent on fixed length strings (the leading zero) in
other words '02/13/2016' and not '2/13/2016'.

If you do not have fixed length date strings,  you would probably have to
use
globs or regular expressions.

*glob(X,Y)*

The glob(X,Y) function is equivalent to the expression "*Y GLOB X*". Note
that the X and Y arguments are reversed in the glob() function relative to
the infix GLOB  operator.

https://sqlite.org/lang_corefunc.html#glob


The REGEXP operator is a special syntax for the regexp() user function. No
regexp() user function is defined by default and so use of the REGEXP
operator will normally result in an error message. If an application-defined
SQL function  named "regexp"
is added at run-time, then the "*X* REGEXP *Y*" operator will be
implemented as a call to "regexp(*Y*,*X*)".

https://sqlite.org/lang_expr.html


Type of regular expression needed:
https://social.msdn.microsoft.com/Forums/en-US/7f38ee7b-15e2-4e2c-8389-1266f496e4b2/regular-expression-to-get-date-format-from-string?forum=csharplanguage

​Jim Callahan

On Tue, Apr 11, 2017 at 10:00 PM, Ron Barnes  wrote:

> Hi Jim,
>
> I could alter the program that populates the Date/Time Column to the
> format you specify.  I'm trying real hard not to as that program has been
> in use for many years and it would be a significant undertaking to convert
> the program then convert the existing data.  Not saying I won't do it as
> I'm at that point, just wondering if it's possible to avoid that route.
>
> If I converted the date/time field, would it be easier to create counts?
>
> If you could, would you be able to offer a sample Select statement I can
> alter to fit my needs?
>
> Thank you very much for the reply!
>
> Side note, I'll be visiting Disney in July!
>
> Regards,
>
> -Ron
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Jim Callahan
> Sent: Tuesday, April 11, 2017 9:15 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] SQLite - Interrogate Date/Time field Statement
> question
>
> Can you convert the dates to ISO 8601 date time format?
> https://en.wikipedia.org/wiki/ISO_8601
>
> -MM-DD hh:mm:ss
>
> ISO date strings (when zero filled) are sortable which necessarily
> includes comparable (Java speak).
> By "zero filled" I mean for March you have "03" and not just "3".
>
> Then if you could generate/populate the boundary values in ISO format; the
> comparisons would be straightforward and you could avoid the julian date
> conversion.
>
> Another disadvantage of Julian dates are the different base years used by
> applications including Unix, MS Access, MS Excel for Windows and MS Excel
> for MacIntosh. Each application is internally consistent, but the minute
> you exchange data between applications...
> https://support.microsoft.com/en-us/help/214330/differences-
> between-the-1900-and-the-1904-date-system-in-excel
>
> Your specification actually requires day counts; so you may need Julian
> dates after all.
>
> Jim Callahan
> Orlando, FL
>
>
>
> On Tue, Apr 11, 2017 at 7:24 PM, Ron Barnes  wrote:
>
> > Hello all,
> >
> > To everyone who helped me before - thank you very much!
> >
> > I'm coding in Visual Basic .NET (Visual Studio 2015) Community.
> >
> > I have to count a Date/Time field and the problem is, this field
> > contains data in a format I'm not sure can be counted.
> >
> > I need to count all the dates in the field but the dates are a
> > combined Date and time in the format examples below.
> > My goal is to use the current Date/time ('NOW') and calculate the time
> > difference in days, from my DB Sourced field.
> >
> > I need to capture...
> > Less than 1 month old
> > 1 month old
> > 2 months old
> > 1 year old.
> > all the way to greater than 10 years old.
> >
> > Is this even possible in SQLite and if so, how would I go about doing it?
> >
> > I have been googling a few queries and come up blank.
> >
> > I try this code and differing combinations of it but it always returns
> > NULL.
> >
> > SELECT CAST
> > ((datetime(julianday(datetime('now'))) - JulianDay(VI_Creation_Date))
> > As
> > Integer)
> > FROM Volume_Information
> >
> > Here is what I have to work with.
> >
> > Table Name:
> > Volume_Information
> >
> > Column name:
> > VI_Creation_Date
> >
> > Date Format:
> > MM/DD/CCYY HH:MM:SS AM/PM
> >
> > Examples:
> >
> > 10/30/2015 2:28:30 AM
> > 2/13/2016 7:51:04 AM
> > 5/15/2016 12:06:24 PM
> > 10/7/2016 1:27:13 PM
> >
> > Any Help would be greatly appreciated,
> >
> > Thanks,
> >
> > -Ron
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite

[sqlite] 1st Call For Papers - 24th Annual Tcl/Tk Conference (Tcl'2017)

[akupries]

Hello SQLite Users, fyi ...

24th Annual Tcl/Tk Conference (Tcl'2017)
http://www.tcl.tk/community/tcl2017/

October 16 - 20, 2017
Crowne Plaza Houston River Oaks
2712 Southwest Freeway, 77098
Houston, Texas, USA

Important Dates:

Abstracts and proposals due   August 21, 2017
Notification to authors   August 28, 2017
WIP and BOF reservations open July 24, 2017
Author materials due  September 25, 2017
Tutorials Start   October 16, 2017
Conference starts October 18, 2017

Email Contact:tclconfere...@googlegroups.com

Submission of Summaries

Tcl/Tk 2017 will be held in Houston, Texas, USA from October 16, 2017 to 
October 20, 2017.

The program committee is asking for papers and presentation proposals
from anyone using or developing with Tcl/Tk (and extensions). Past
conferences have seen submissions covering a wide variety of topics
including:

* Scientific and engineering applications
* Industrial controls
* Distributed applications and Network Managment
* Object oriented extensions to Tcl/Tk
* New widgets for Tk
* Simulation and application steering with Tcl/Tk
* Tcl/Tk-centric operating environments
* Tcl/Tk on small and embedded devices
* Medical applications and visualization
* Use of different programming paradigms in Tcl/Tk and proposals for new
  directions.
* New areas of exploration for the Tcl/Tk language

Submissions should consist of an abstract of about 100 words and a
summary of not more than two pages, and should be sent as plain text
to tclconfere...@googlegroups.com no later than August 21, 2017. Authors of 
accepted
abstracts will have until September 25, 2017 to submit their final
paper for the inclusion in the conference proceedings. The proceedings
will be made available on digital media, so extra materials such as
presentation slides, code examples, code for extensions etc. are
encouraged.

Printed proceedings will be produced as an on-demand book at lulu.com

The authors will have 30 minutes to present their paper at
the conference.

The program committee will review and evaluate papers according to the
following criteria:

* Quantity and quality of novel content
* Relevance and interest to the Tcl/Tk community
* Suitability of content for presentation at the conference

Proposals may report on commercial or non-commercial systems, but
those with only blatant marketing content will not be accepted.

Application and experience papers need to strike a balance between
background on the application domain and the relevance of Tcl/Tk to
the application. Application and experience papers should clearly
explain how the application or experience illustrates a novel use of
Tcl/Tk, and what lessons the Tcl/Tk community can derive from the
application or experience to apply to their own development efforts.

Papers accompanied by non-disclosure agreements will be returned to
the author(s) unread. All submissions are held in the highest
confidentiality prior to publication in the Proceedings, both as a
matter of policy and in accord with the U. S. Copyright Act of 1976.

The primary author for each accepted paper will receive registration
to the Technical Sessions portion of the conference at a reduced rate.

Other Forms of Participation

The program committee also welcomes proposals for panel discussions of
up to 90 minutes. Proposals should include a list of confirmed
panelists, a title and format, and a panel description with position
statements from each panelist. Panels should have no more than four
speakers, including the panel moderator, and should allow time for
substantial interaction with attendees. Panels are not presentations
of related research papers.

Slots for Works-in-Progress (WIP) presentations and Birds-of-a-Feather
sessions (BOFs) are available on a first-come, first-served basis
starting in July 24, 2017. Specific instructions for reserving WIP
and BOF time slots will be provided in the registration information
available in July 24, 2017. Some WIP and BOF time slots will be held open
for on-site reservation. All attendees with an interesting work in
progress should consider reserving a WIP slot.

Registration Information

More information on the conference is available the conference Web
site (http://www.tcl.tk/community/tcl2017/) and will be published on
various Tcl/Tk-related information channels.

To keep in touch with news regarding the conference and Tcl events in
general, subscribe to the tcl-announce list. See:
http://code.activestate.com/lists/tcl-announce to subscribe to the
tcl-announce mailing list.


Conference Committee

   * Alexandre Ferrieux
   * Andreas KupriesSUSE
   * Arjen Markus   Deltares
   * Brian Griffin  Mentor Graphics - A Siemens Business
   * Clif Flynt Noumena Corp
   * Gerald Lester  KnG Consulting LLC
   * Joe Mistachkin Mistachkin Systems
   * Ronald Fox CAEN Technologies
NSCL @ Michigan State University
   * Steve Landers  Di