[sqlite] random result

[big stone]

A disturbing effect of the implementation of CTE :

with sample(rank,rand) as (values(1,random()),(2, random()))
 select * from sample a, sample b on a.rank=b.rank;

gives :


rank rand rank rand   0 1 -4662617171230690406 1 -8784008985057089983  1 2
-8243192423181459578 2 2566393184580211567
==> It would be nice if somehow we could suggest SQLite to pre-calculate a
CTE.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why doesn't althttpd.c quit soon enough for CGI scripts?

[Richard Hipp]

On Mon, Feb 3, 2014 at 9:11 PM, Sean Woods  wrote:

> I have a very basic althttpd setup:
>
> fieldston:test swoods$ tree
> .
> └── default.website
> ├── cgi
> ├── cgi.c
> └── index.html
>
> 1 directory, 3 files
>
> `cgi` is a build of `cgi.c`, which is:
>
> fieldston:test swoods$ cat default.website/cgi.c
> #include 
> #include 
>
> int main(int argc, char *argv[]) {
> printf("Content-type: text/html\r\n");
> printf("Connection: close\r\n\r\n");
> printf("Hello, world!");
> return 1;
> }
>
> I run althttpd using the `-port` command to create a standalone server.
>
> My problem is: while requesting a static resource with this method works
> just fine, requesting a CGI resource - even with `Connection: close` as
> a header - doesn't return right away.  It seems to be waiting for an
> additional connection (keep-alive ish).
>
> What's the "proper" way to send CGI output to althttpd?  I tried a
> `Content-length` parameter and that does work, but it means I need to
> buffer my output for each response - and I thought the server was
> supposed to do that for me?
>
>
Looking at the code, it appears that althttpd.c is *not* adding the
Content-Length: header if the CGI program omits it.  A missing
Content-Length on the reply can confuse many web-browsers.

It appears that althttpd.c CGI looks at just the first line of the reply
from the CGI.  It takes action only if that first line is "location:" or
"status:".  Otherwise, the first line and everything that follows is
blindly copied through and sent back to the browser, with no additional
parsing, and without adding any Content-Length or other header fields.

Fossil is the most common CGI program run from althttpd.  And Fossil does
supply a Content-Length, as well as other relevant header fields.  So this
issue has never come up before.

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

[sqlite] Why doesn't althttpd.c quit soon enough for CGI scripts?

[Sean Woods]

I have a very basic althttpd setup:

fieldston:test swoods$ tree
.
└── default.website
├── cgi
├── cgi.c
└── index.html

1 directory, 3 files

`cgi` is a build of `cgi.c`, which is:

fieldston:test swoods$ cat default.website/cgi.c
#include 
#include 

int main(int argc, char *argv[]) {
printf("Content-type: text/html\r\n");
printf("Connection: close\r\n\r\n");
printf("Hello, world!");
return 1;
}

I run althttpd using the `-port` command to create a standalone server.

My problem is: while requesting a static resource with this method works
just fine, requesting a CGI resource - even with `Connection: close` as
a header - doesn't return right away.  It seems to be waiting for an
additional connection (keep-alive ish).

What's the "proper" way to send CGI output to althttpd?  I tried a
`Content-length` parameter and that does work, but it means I need to
buffer my output for each response - and I thought the server was
supposed to do that for me?

I also tried setting a "Status" header to "200"

I read through the code but don't see anything obvious so it must be
more subtle...

Any pointers would be great!  Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_column_name() issue with views

[Richard Hipp]

On Sun, Feb 2, 2014 at 12:23 PM, Charles Leifer  wrote:

> I was surprised to find that, depending on whether I queried a table or a
> view, sqlite3_column_name would return different values for the column
> name. Specifically, when the table is aliased and the columns in the SELECT
> clause are quoted, sqlite returns an unquoted, unaliased column name, e.g.
> "
> t1.name" -> name. When querying a view, though, sqlite will return the
> quoted, aliased column name, e.g. "t1.name" -> "t1.name". Does this
> qualify
> as a bug, or is this intended behavior?
>

There is no "intended" behavior.
http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fied

If the result of sqlite3_column_name is important, please us an AS clause
to set it to exactly what you want.


>
> I observed this with sqlite versions 3.8.2, 3.7.13 and 3.7.9.
>
> Here is a short python script exemplifying the behavior:
>
> import sqlite3
> conn = sqlite3.connect(':memory:')
> cursor = conn.cursor()
>
> cursor.execute('create table people (name);')
> cursor.execute('insert into people values (?)', ('charlie',))
>
> cursor.execute('select t1.name from people as t1')
> print cursor.description
> # (('name', None, None, None, None, None, None),)
>
> cursor.execute('create view people_view as select * from people')
> cursor.execute('select t1.name from people_view as t1')
> print cursor.description
> # (('t1.name', None, None, None, None, None, None),)
>
> Thanks in advance,
>
> Charlie
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] sqlite3_column_name() issue with views

[Charles Leifer]

I was surprised to find that, depending on whether I queried a table or a
view, sqlite3_column_name would return different values for the column
name. Specifically, when the table is aliased and the columns in the SELECT
clause are quoted, sqlite returns an unquoted, unaliased column name, e.g. "
t1.name" -> name. When querying a view, though, sqlite will return the
quoted, aliased column name, e.g. "t1.name" -> "t1.name". Does this qualify
as a bug, or is this intended behavior?

I observed this with sqlite versions 3.8.2, 3.7.13 and 3.7.9.

Here is a short python script exemplifying the behavior:

import sqlite3
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

cursor.execute('create table people (name);')
cursor.execute('insert into people values (?)', ('charlie',))

cursor.execute('select t1.name from people as t1')
print cursor.description
# (('name', None, None, None, None, None, None),)

cursor.execute('create view people_view as select * from people')
cursor.execute('select t1.name from people_view as t1')
print cursor.description
# (('t1.name', None, None, None, None, None, None),)

Thanks in advance,

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

Re: [sqlite] Troubleshooting nested query performance issue

[Drake Wilson]

Quoth Petite Abeille , on 2014-02-03 23:49:14 +0100:
> Not directly related to your question, but… why oh why do people
> molest their queries by gratuitously and pointlessly aliasing
> perfectly good table name to meaningless random one letter codes?!?
> Masochism?

Because otherwise you wind up with things like

  long_table_name.foo, long_table_name.bar, long_table_name.baz,
  long_table_name.quux, long_table_name.plugh, long_table_name.plover

and that buries the changing part in a sea of sameness.

The "real" reason I use aliases-always in the first place (but which
wouldn't preclude longer aliases) is to semantically distinguish the
underlying table from the source as used to construct a particular
query; that also makes it more consistent when queries might have
multiple sources from the same table.  (I know not everyone thinks the
same way, so there's a subjective element.)

   ---> Drake Wilson

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

Re: [sqlite] Troubleshooting nested query performance issue

[James K. Lowden]

On Mon, 3 Feb 2014 23:49:14 +0100
Petite Abeille  wrote:

> > I have a query 
> 
> Not directly related to your question, but? why oh why do people
> molest their queries by gratuitously and pointlessly aliasing
> perfectly good table name to meaningless random one letter codes?!?
> Masochism?

Hey!  I'll allow gratuitious and pointless, but molest?  And definitely
not random.  

Even perfectly good table names can be quite long.  I'm sure you've
seen some more than 30 characters.  Just the physical act of typing
them is one drawback.  Yes, I have heard about that cut-and-paste
feature everyone's talking about these days.  Slows me down, say I.   

SQL is hardly a monument to succinct expression.  Shorter queries are
*easier* to understand, all thing being equal.  

The trick is to avoid meaningless and arbitrary aliases.  In a small
query, a single letter taken from the first letter of the tablename
usually works.  For larger queries I usually lean on 3-letter
abbreviations.  Three letters is space enough for mnemonic aliases;
it's  enough for, say,  all the world's airports and currencies.  

As Humpty-Dumpty said, "There's glory for you."  

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

Re: [sqlite] Troubleshooting nested query performance issue

[Joseph L. Casale]

> Not directly related to your question, but… why oh why do people molest their 
> queries by
> gratuitously and pointlessly aliasing perfectly good table name to 
> meaningless random
> one letter codes?!? Masochism?

lol, you're not wrong.

This code is used in Python, and we are strict by-the-sword PEP8 shop.
Its a double edged sword at times, and plus I look at those tables so often the 
abbreviations
are second nature to me.

Funny,
jlc

___
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] Troubleshooting nested query performance issue

[Joseph L. Casale]

> No.  It appears to be a correlated subquery.  It depends on the current row
> of the "d" table (diffset) because of the "ON r.guid_id=did" term and thus
> has to be reevalatued for every row of the "d" table.

Richard,

After a closer look, the subquery was useless and needed to be removed.

Thanks for the insight,
jlc
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Troubleshooting nested query performance issue

[Petite Abeille]

On Feb 3, 2014, at 11:30 PM, Joseph L. Casale  wrote:

> I have a query 

Not directly related to your question, but… why oh why do people molest their 
queries by gratuitously and pointlessly aliasing perfectly good table name to 
meaningless random one letter codes?!? Masochism?

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

Re: [sqlite] Troubleshooting nested query performance issue

[Richard Hipp]

On Mon, Feb 3, 2014 at 5:30 PM, Joseph L. Casale
wrote:

> I have a query where if I hard code the results of the nested SELECT
> DICTINCT to a few
> static values, it completes very fast. Leaving the select causes this
> query to slow down
> badly. Running an explain query plan wasn't obvious with my weak sql
> experience.
>
> Is the nested query not evaluated only once?
>

No.  It appears to be a correlated subquery.  It depends on the current row
of the "d" table (diffset) because of the "ON r.guid_id=did" term and thus
has to be reevalatued for every row of the "d" table.


>
> SELECT
>a.value,
>COUNT(*) total,
>SUM(CASE WHEN r.status IS NULL THEN 1 ELSE 0 END)
> unprocessed,
>SUM(CASE WHEN r.status='processed' THEN 1 ELSE 0 END)
> processed,
>SUM(CASE WHEN r.status='error' THEN 1 ELSE 0 END) error
>   FROM diffset d
>   JOIN request r
>ON r.guid_id=d.id
>   JOIN action a
>ON a.req_id=r.id
>AND a.key='operationType'
>AND a.value IN (
> SELECT DISTINCT(a.value)
>   FROM action a
>   JOIN request r
>ON r.guid_id=d.id
>  WHERE a.key='operationType'
>)
>  WHERE d.id=?
>  GROUP BY value
>
> Thanks,
> jlc
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Troubleshooting nested query performance issue

[Joseph L. Casale]

I have a query where if I hard code the results of the nested SELECT DICTINCT 
to a few
static values, it completes very fast. Leaving the select causes this query to 
slow down
badly. Running an explain query plan wasn't obvious with my weak sql experience.

Is the nested query not evaluated only once?

SELECT
   a.value,
   COUNT(*) total,
   SUM(CASE WHEN r.status IS NULL THEN 1 ELSE 0 END) unprocessed,
   SUM(CASE WHEN r.status='processed' THEN 1 ELSE 0 END) processed,
   SUM(CASE WHEN r.status='error' THEN 1 ELSE 0 END) error
  FROM diffset d
  JOIN request r
   ON r.guid_id=d.id
  JOIN action a
   ON a.req_id=r.id
   AND a.key='operationType'
   AND a.value IN (   
SELECT DISTINCT(a.value)
  FROM action a
  JOIN request r
   ON r.guid_id=d.id
 WHERE a.key='operationType' 
   )
 WHERE d.id=?
 GROUP BY value

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

Re: [sqlite] recursive common table expression, an example

[Petite Abeille]

On Feb 3, 2014, at 11:05 PM, big stone  wrote:

> group_concat is indeed super nice ! I didn't notice that little jewel of
> SQLite, thank you.

You are welcome. 

But *do* read the very fine prints associated with that aggregate function:

http://www.sqlite.org/lang_aggfunc.html

“… The order of the concatenated elements is arbitrary… “ … which, well, 
renders it pretty much useless for anything but display purpose… and even then… 
sigh...

> Is there a "standardized" SQL normalization for that ?

No. Not that I know of.

> (I see that oracle has a LISTAGG instead)

Yes, LISTAGG [1] is much more usable in that respect as it sports a WITHIN 
GROUP ( ORDER BY … ) clause which makes is more predictable.

[1] http://docs.oracle.com/cd/E11882_01/server.112/e17118/functions089.htm

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

Re: [sqlite] recursive common table expression, an example

[big stone]

group_concat is indeed super nice ! I didn't notice that little jewel of
SQLite, thank you.

Is there a "standardized" SQL normalization for that ? (I see that oracle
has a LISTAGG instead)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] recursive common table expression, an example

[Petite Abeille]

On Feb 3, 2014, at 10:11 PM, big stone  wrote:

>  bag colors  bag1 blue - red - yellow  bag2 green - yellow

Does that really require a recursive query? Wouldn’t a simple group by + 
group_concat do as well?

with
DataSet
as
(
  select 'bag1' as bag, 'blue' as color union all
  select 'bag1' as bag, 'red' as color union all
  select 'bag1' as bag, 'yellow' as color union all
  select 'bag2' as bag, 'green' as color union all
  select 'bag2' as bag, 'yellow' as color 
)
selectDataSet.bag as bag,
  group_concat( DataSet.color, ' - ' ) as colors
from  DataSet

group by  DataSet.bag

order by  DataSet.bag;


P.S.

I’m starting to develop a strong aversion to that ‘values( … )’ syntax… sigh... 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] System.Data.SQLite Preloading and ASP.Net debugging

[Joe Mistachkin]

Eric Schultz wrote:
>
> I can't seem to build the Interop.SQLite.dll files ("build.bat
> ReleaseNativeOnly Win32" fails due to a missing stdarg.h file).
>

What version of Visual Studio and/or MSBuild are you using?  Are you
able to build other MSVC projects in your environment?

Anyhow, I've created a private build that you can use to test all the
changes, here (this is actually the 1.0.91.0 beta):

https://system.data.sqlite.org/temporary/System.Data.SQLite.v91.beta.zip

>
> That said, using the previous Interop.SQLite.dll files, I no longer
> get a DLLNotFoundException but an  'EntryPointNotFoundException' with
> the message of "Unable to find an entry point named
> 'sqlite3_config_log_interop' in DLL 'SQLite.Interop.dll'."
>

You should not receive this error.  Perhaps you are using a version of
the interop assembly that is several releases out-of-date (i.e. that
would explain the missing export)?

>
> I assume something changed in SQLite.Interop.dll between the version
> you provided and the one I'm so I THINK everything is fine.
>

Depending on exactly how old your SQLite interop assembly is, many
things may have changed.  There has been some fairly major feature work
in the last couple releases.

--
Joe Mistachkin
 

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

Re: [sqlite] recursive common table expression, an example

[big stone]

Other usage  = A workaround to  "For XML PATH" (see
http://www.sqlusa.com/bestpractices/training/scripts/commadelimitedlist/ )

List of colors of a bag :
**

with colors(bag,color) as
(values('bag1','blue'),('bag1','red'),('bag2','green'),('bag2','yellow'),('bag1','yellow'))
   , bags (bag, colors, last) as (
   select  bag , min(color) as colors, min(color) as last from colors
group by bag
   union all
   select b.bag, colors ||' - ' ||  color  ,  color  from bags b ,
colors l
where b.bag=l.bag and l.color=(
select min(color) from colors as x where b.bag=x.bag and
b.last gives :
*
  bag colors  bag1 blue - red - yellow  bag2 green - yellow
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syntax diagram in draft docs for sqlite 3.8.3 confuses me

[Zsbán Ambrus]

On 1/30/14, Richard Hipp  wrote:
> Fixed.  Do you see any more problems?

Sqlite 3.8.3 is now released, but I found one more problem today.

The page "http://sqlite.org/lang_transaction.html; should show the
syntax diagrams for commit-stmt and rollback-stmt.  Similarly, the
page "http://sqlite.org/lang_savepoint.html; should show the diagram
for rollback-stmt.

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

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

[Labar, Ken]

Hello Simon, Clemens, and Richard,

Thank you for your help.


> Simon: "What are you seeing from your SELECT that you weren't expecting ?"
0 | Batt
0 | ClockBatt
0 | Batt
0 | BP
0 | ORP
0 | Ref
0 | pH
0 | pH
0 | DO
...


> Simon: "Can you reduce your INSERTs to just two rows, and still get results 
> you consider to be incorrect from the SELECT ?"
Yes, I reduced the table to 2 rows by:
DELETE FROM userparameter WHERE hepi_parameter_id !=32961 AND hepi_parameter_id 
!= 32881;

select (rtTableSort < 1000) as selected, abbrString from userParameter order by 
abbrString;

results:

0 | ORP

0 | DO


> DRH: "Can you try recompiling with all compiler optimizations turned off and 
> see if you still get the error?"

I have turned off all optimizations (FYI: only C++ optimizations were on before)


> DRH: ", can you compile with -DSQLITE_DEBUG then run the query *after* first 
> running "PRAGMA vdbe_debug=ON" and then send us the output?"
I have been struggling to enable SQLITE_DEBUG with our uCOS-II VFS port.
(namely our code does not have stdout or printf as compiled now. I love 
embedded;-) I'm working on a solution for debugging, more soon).


> Clemens: "What is the EXPLAIN QUERY PLAN output for this query on the 
> handheld?"
0|0|0|SCAN TABLE userParameter
0|0|0|USE TEMP B-TREE FOR ORDER BY

Thank you,
Ken

ken labar | Embedded Firmware Engineer
Hach Hydromet | www.hachhydromet.com | 
kla...@hach.com

Innovating technology behind better data




Please be advised that this email may contain confidential 
information.  If you are not the intended recipient, please notify us 
by email by replying to the sender and delete this message.  The 
sender disclaims that the content of this email constitutes an offer 
to enter into, or the acceptance of, any agreement; provided that the 
foregoing does not invalidate the binding effect of any digital or 
other electronic reproduction of a manual signature that is included 
in any attachment.

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

Re: [sqlite] Understanding transactions

[Igor Tandetnik]

On 2/3/2014 3:21 PM, Yuriy Kaminskiy wrote:

Igor Tandetnik wrote:

On 2/3/2014 1:07 PM, Baruch Burstein wrote:

1) How does a transaction affect SELECTs? If I start a transaction and do
an UPDATE/DELETE/INSERT, what data will a SELECT in the same transaction
see?


The new data. A transaction always sees its own changes.


What about a SELECT in a different connection?


Depends. In journal mode, the reader transaction is blocked while a
writer is in progress; you won't be able to run that SELECT statement


Erm, wrong.

[1] sqlite> create table t (i);
[1] sqlite> insert into t values (1);
[1] sqlite> begin;
[1] sqlite> insert into t values (2);
[2] sqlite> begin;
[2] sqlite> /* as you see, there are no problem in starting transaction
concurrently with pending update in [1]: */
[2] sqlite> select * from t;
1
[2] sqlite> /* as you see, SELECT is also successful and returns unmodified 
data: */


Ah, right. I oversimplified. [1] holds a reserved lock, indicating 
intention to write eventually; changes are parked in an in-memory cache 
for now. A reserved lock allows new readers to acquire shared locks.


At some future point, the writer wants to commit, or else the amount of 
changes becomes large enough that they cannot be held in RAM and need to 
be spilled to disk. At this point, the writer would escalate to PENDING 
lock, wait until all current readers clear while not allowing any new 
ones, then escalate once more to EXCLUSIVE lock, and hold it until the 
transaction is committed or rolled back.



There are *different* restriction in rollback journal mode: you cannot *COMMIT*
in [1] while transaction in [2] is still active.


Well, there are *both* sets of restrictions - the kind that I described, 
and the kind that you describe. Which set of restrictions applies 
depends on where in its lifecycle the writer is. If it's still holding a 
RESERVED lock, then new readers are allowed, and the writer is blocked 
until they clear. If it's holding a PENDING or EXCLUSIVE lock, then 
readers are blocked until the writer clears.

--
Igor Tandetnik

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

Re: [sqlite] Understanding transactions

[Yuriy Kaminskiy]

Igor Tandetnik wrote:
> On 2/3/2014 1:07 PM, Baruch Burstein wrote:
>> 1) How does a transaction affect SELECTs? If I start a transaction and do
>> an UPDATE/DELETE/INSERT, what data will a SELECT in the same transaction
>> see?
> 
> The new data. A transaction always sees its own changes.
> 
>> What about a SELECT in a different connection?
> 
> Depends. In journal mode, the reader transaction is blocked while a
> writer is in progress; you won't be able to run that SELECT statement

Erm, wrong.

[1] sqlite> create table t (i);
[1] sqlite> insert into t values (1);
[1] sqlite> begin;
[1] sqlite> insert into t values (2);
[2] sqlite> begin;
[2] sqlite> /* as you see, there are no problem in starting transaction
concurrently with pending update in [1]: */
[2] sqlite> select * from t;
1
[2] sqlite> /* as you see, SELECT is also successful and returns unmodified 
data: */

There are *different* restriction in rollback journal mode: you cannot *COMMIT*
in [1] while transaction in [2] is still active.
[1] sqlite> COMMIT;
Error: database is locked
[1] sqlite>
[2] sqlite> COMMIT; /* or ROLLBACK; */
[1] sqlite> COMMIT;
[1] sqlite> /* succeed this time */

In WAL mode, there are no such restriction.

(You *can* take any number of SHARED locks when one of transactions already got
RESERVED lock. You cannot upgrade that SHARED lock to RESERVED [required for any
db modification] if other transaction already got RESERVED lock; you cannot
upgrade RESERVED lock to EXCLUSIVE lock while there are other transaction(s)
that acquired SHARED lock [required for COMMIT in rollback journal mode, not
required in WAL mode])

> until the writer transaction commits (in which case SELECT will see new
> data) or rolls back (in which case it will see old data).
> 
> In WAL mode, a reader is allowed to read while a writer transaction is
> in progress. It reads the old data, before any changes.
> 
>> 2) Can 2 connections run 'BEGIN;' ?
> 
> Yes. They are presumed to be readers, until they attempt to run a data
> modification statement. Two readers can happily co-exist.

>> If so, who gets the final say on the data? The first to do the
>> 'COMMIT;'? What will happen when the other does a
>> COMMIT?
> 
> When these two transactions issue a BEGIN statement, each acquires a
> shared lock. The first one to run UPDATE or similar gets a reserved
> lock, which basically says "I'm going to write at some later time; for
> now, I'm waiting for all existing readers to clear". If the second
> transaction tries to run UPDATE, it will get an error when trying to
> obtain its own reserved lock. At this point, the system is in a deadlock
> - no progress can be made until one of the transactions rolls back.
> 
> To avoid this situation, use BEGIN IMMEDIATE or BEGIN EXCLUSIVE for
> transactions that you know will be making changes (not just reading).
> 
> For details, see
> 
> http://www.sqlite.org/lockingv3.html
> http://www.sqlite.org/atomiccommit.html
> http://www.sqlite.org/lang_transaction.html

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

Re: [sqlite] Understanding transactions

[Simon Slavin]

On 3 Feb 2014, at 7:51pm, Baruch Burstein  wrote:

> Thank you for the explanations. If I wrap a few SELECTs in a transaction,
> does this guarantee that the data I read will be consistent across all of
> the SELECTs?

Yes.  Unless the same connection that is doing all these SELECTs makes some 
changes itself.

I believe that the first SELECT will lock the database, but I'm not sure about 
this, or about how it works with the different types of journal.

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

Re: [sqlite] Understanding transactions

[Baruch Burstein]

Thank you for the explanations. If I wrap a few SELECTs in a transaction,
does this guarantee that the data I read will be consistent across all of
the SELECTs?

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Understanding transactions

[Igor Tandetnik]

On 2/3/2014 2:04 PM, Simon Slavin wrote:

1) How does a transaction affect SELECTs? If I start a transaction and do
an UPDATE/DELETE/INSERT, what data will a SELECT in the same transaction
see?


You can change it ...




A transaction always sees its own changes, no pragma necessary or possible.

pragma read_uncommitted only applies to multiple connections in the same 
process, running in shared cache mode ( 
http://www.sqlite.org/sharedcache.html ).

--
Igor Tandetnik

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

Re: [sqlite] "Common Table Expression"

[Petite Abeille]

On Jan 25, 2014, at 6:25 PM, Petite Abeille  wrote:

> 
> On Jan 25, 2014, at 6:05 AM, Keith Medcalf  wrote:
> 
>> Read the docs.  It explains how recursive CTEs are computed and how UNION 
>> ALL vs UNION work in CTEs.
> 
> 
> Hmmm… perhaps… doing is believing… so will experiment once the next SQLite 
> release is officially out.
> 

So… for the record… as it stands in 3.8.3, SQLite will happily recurse forever.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] recursive common table expression, an example

[Petite Abeille]

Now that 3.8.3 is officially out, we can all play with these nice little common 
table expressions! Yeah!

So, while solving sudoku puzzles is all fine and dandy, the bread and butter of 
recursive queries is more along the lines of plain, old hierarchies.

So, let create one:

  select  'A' as node, null as parent union all
  select  'B' as node, 'A' as parent union all
  select  'C' as node, 'B' as parent union all
  select  'D' as node, 'C' as parent 

A simple hierarchy, each node with one parent, the root node without one: A → B 
→ C → D. Nice and easy.

Let recurse!

with
DataSet
as
(
  select  'A' as node, null as parent union all
  select  'B' as node, 'A' as parent union all
  select  'C' as node, 'B' as parent union all
  select  'D' as node, 'C' as parent 
),
Hierarchy( node, parent, level, path )
as
(
  select  DataSet.node,
  DataSet.parent,
  1 as level,
  ' → ' || DataSet.node as path
  from DataSet
  whereDataSet.parent is null

  union all
  select  DataSet.node,
  DataSet.parent,
  Hierarchy.level + 1 as level,
  Hierarchy.path || ' → ' || DataSet.node as path
  fromHierarchy
  joinDataSet
  on  DataSet.parent = Hierarchy.node
)
select*
from  Hierarchy

order by  path;

node|parent|level|path
A||1| → A
B|A|2| → A → B
C|B|3| → A → B → C
D|C|4| → A → B → C → D

Beautiful. For each node, we get its level and full path, recursively. And 
that’s all there is to it. Very nice.

Recursive or not, common table expressions are your friend. Use them ☺


N.B.

One word of caution about circular recursion though… as it stands, SQLite 
doesn’t have any build-in mechanism to detect circularity… and will happily get 
into a funk and run forever if given the opportunity… so… watch out…

Little demonstration:

with
DataSet
as
(
  select  'A' as node, 'A' as parent
),
Hierarchy( node, parent, level, path )
as
(
  select  DataSet.node,
  DataSet.parent,
  1 as level,
  ' → ' || DataSet.node as path
  from DataSet

  union
  select  DataSet.node,
  DataSet.parent,
  Hierarchy.level + 1 as level,
  Hierarchy.path || ' → ' || DataSet.node as path
  fromHierarchy
  joinDataSet
  on  DataSet.parent = Hierarchy.node
)
select*
from  Hierarchy

order by  path;









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

Re: [sqlite] Understanding transactions

[Igor Tandetnik]

On 2/3/2014 1:07 PM, Baruch Burstein wrote:

1) How does a transaction affect SELECTs? If I start a transaction and do
an UPDATE/DELETE/INSERT, what data will a SELECT in the same transaction
see?


The new data. A transaction always sees its own changes.


What about a SELECT in a different connection?


Depends. In journal mode, the reader transaction is blocked while a 
writer is in progress; you won't be able to run that SELECT statement 
until the writer transaction commits (in which case SELECT will see new 
data) or rolls back (in which case it will see old data).


In WAL mode, a reader is allowed to read while a writer transaction is 
in progress. It reads the old data, before any changes.



2) Can 2 connections run 'BEGIN;' ?


Yes. They are presumed to be readers, until they attempt to run a data 
modification statement. Two readers can happily co-exist.



If so, who gets the final say on the data? The first to do the 'COMMIT;'? What 
will happen when the other does a
COMMIT?


When these two transactions issue a BEGIN statement, each acquires a 
shared lock. The first one to run UPDATE or similar gets a reserved 
lock, which basically says "I'm going to write at some later time; for 
now, I'm waiting for all existing readers to clear". If the second 
transaction tries to run UPDATE, it will get an error when trying to 
obtain its own reserved lock. At this point, the system is in a deadlock 
- no progress can be made until one of the transactions rolls back.


To avoid this situation, use BEGIN IMMEDIATE or BEGIN EXCLUSIVE for 
transactions that you know will be making changes (not just reading).


For details, see

http://www.sqlite.org/lockingv3.html
http://www.sqlite.org/atomiccommit.html
http://www.sqlite.org/lang_transaction.html

--
Igor Tandetnik

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

Re: [sqlite] Understanding transactions

[Simon Slavin]

On 3 Feb 2014, at 6:07pm, Baruch Burstein  wrote:

> I am a little unclear on some of the ways transactions affect multiple
> connections. I am assuming that multiple sqlite3 objects in one program is
> the same as multiple programs.

Wanted to check whether you'd read this, even though I have trouble 
understanding it myself:



> 1) How does a transaction affect SELECTs? If I start a transaction and do
> an UPDATE/DELETE/INSERT, what data will a SELECT in the same transaction
> see?

You can change it ...



... but you probably shouldn't.  It's normal to consider a 
transaction-in-progress as a transaction that you haven't decided to do yet.  
In other words, it's the COMMIT that does everything important.  Until that 
you're just making notes on the back of an old envelope.

> What about a SELECT in a different connection?

Generally speaking, either the database will have already been locked (in which 
case the SELECT will be delayed until unlock), or the SELECT will see the 
version of the database before changes.

> 2) Can 2 connections run 'BEGIN;' ? If so, who gets the final say on the
> data?

Whichever COMMIT gets processed last.  But it's your job as a programmer to use 
SELECT /inside/ your transaction to make sure there is never any argument.

> The first to do the 'COMMIT;'? What will happen when the other does a
> COMMIT?

Check out the differences between BEGIN DEFERRED, BEGIN IMMEDIATE, and BEGIN 
EXCLUSIVE:



The default is DEFERRED.  The simpler and safer way to think about it is to 
imagine everything does BEGIN IMMEDIATE.

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

Re: [sqlite] possible bug 3.8.1 /3.8.3

[Adam Devita]

Good day all,

Thank you for your replies.

Yes, I can provide the data if required, although I don't think it is
needed, as the bug is in the user's code.   The point about what happens if
several timestamps have the same value is valid, and in this case, I think
is the explanation.

sqlite>  SELECT id, timestamp, data_blob FROM data WHERE timestamp =
(SELECT MIN(timestamp) FROM data WHERE stream_num = 2) ;
3|12946000654830|☻
4|12946000654830|☺
5|12946000654830|☺

sqlite>  SELECT id, timestamp, data_blob FROM data WHERE timestamp =
(SELECT MIN(timestamp) FROM data WHERE stream_num = 2) and stream_num = 2;
4|12946000654830|☺

sqlite> SELECT id, timestamp, data_blob FROM data WHERE stream_num = 2
order by timestamp asc;
4|12946000654830|☺


Obviously, there are several records with the same timetamp, and putting
the restriction on the stream num ensures that the right one is picked.

regards,
Adam




On Mon, Feb 3, 2014 at 12:47 PM, Richard Hipp  wrote:

> Can you provide data?  Without some sample data, we cannot tell if the
> answer SQLite is providing is right or wrong.
>
>
> On Mon, Feb 3, 2014 at 12:25 PM, Adam Devita  wrote:
>
> > Good day,
> >
> > I'm debugging some code that uses 3.8.1, and I've tried just upgrading to
> > 3.8.3, which didn't work.  The observation is that
> >
> > This query:
> > SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp)
> > FROM data WHERE stream_num = ?) LIMIT 1
> >
> > seems to occasionally produce a wrong result (the content of data_blob is
> > incorrect given the values of stream_num)
> >
> > yet this query
> > SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp
> asc
> > LIMIT 1
> >
> > seems just fine, insofar as the same tests on the same data have not hit
> > any of the error condition / contradiction.
> >
> > in both cases sqlite3_bind_int(pStmt, 1, (int)nStream); is used for
> > parameter ?
> >
> >
> > We are using an in memory database as a smarter queue where timestamp
> data
> > gets inserted, and if the db size is sufficient (30 to 40 records) the
> > above query lets us pop the earliest timestamp (which is stored as int64
> > via sqlite3_bind_int64).
> >
> > Is this a possible bug or am I missing something?  Using the backup api
> to
> > look at it from a file
> > sqlite>.schema
> > CREATE TABLE data ( id INTEGER PRIMARY KEY, timestamp BIGINT NOT NULL,
> > stream_num TINYINT, source_seq_num TINYINT,
> > event_seq_num INT, data_address BIGINT NOT NULL, data_blob BLOB NOT
> NULL);
> >
> > sqlite> SELECT id, data_blob FROM data WHERE stream_num = 2 order by
> > timestamp asc  LIMIT 1;
> > 4|☺
> > sqlite> SELECT id, data_blob FROM data WHERE timestamp = (SELECT
> > MIN(timestamp) FROM data WHERE stream_num = 2) LIMIT 1;
> > 3|☻
> > sqlite>
> >
> > regards,
> > Adam DeVita
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] System.Data.SQLite Preloading and ASP.Net debugging

[Eric Schultz]

Joe,

I can't seem to build the Interop.SQLite.dll files ("build.bat
ReleaseNativeOnly Win32" fails due to a missing stdarg.h file). That said,
using the previous Interop.SQLite.dll files, I no longer get a
DLLNotFoundException but an  'EntryPointNotFoundException' with the message
of "Unable to find an entry point named 'sqlite3_config_log_interop' in DLL
'SQLite.Interop.dll'." I assume something changed in SQLite.Interop.dll
between the version you provided and the one I'm so I THINK everything is
fine.

Eric


On Sat, Feb 1, 2014 at 4:27 PM, Joe Mistachkin wrote:

>
> Eric Schultz wrote:
> >
> > One thing I did notice when debugging is that on these copied assemblies,
> > the Assembly.CodeBase property has the original location of the Assembly
> > before running.
> >
>
> I've added code to the native library pre-loader to attempt to handle using
> the code base of the assembly (via automatic detection):
>
> https://system.data.sqlite.org/index.html/ci/5ef906e34a
>
> Can you please try these changes in your environment and let us know if
> they
> work for you?
>
> --
> Joe Mistachkin
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 

Eric Schultz, Developer Advocate, Outercurve Foundation
http://www.outercurve.org
eschu...@outercurve.org
cell: 920-539-0404
skype: ericschultzwi
@EricOutercurve
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Understanding transactions

[Baruch Burstein]

I am a little unclear on some of the ways transactions affect multiple
connections. I am assuming that multiple sqlite3 objects in one program is
the same as multiple programs.

1) How does a transaction affect SELECTs? If I start a transaction and do
an UPDATE/DELETE/INSERT, what data will a SELECT in the same transaction
see? What about a SELECT in a different connection?
2) Can 2 connections run 'BEGIN;' ? If so, who gets the final say on the
data? The first to do the 'COMMIT;'? What will happen when the other does a
COMMIT?

Thanks

-- 
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trying to understand curious SQL tracing behaviour

[Stephan Beal]

On Mon, Feb 3, 2014 at 6:51 PM, Richard Hipp  wrote:

> line of output.  But then it noticed that the schema had changed, so it
> abandoned that execution, reprepared the statement with the new schema,
>

Aha - i can explain the schema change with the drop/create table code near
the top of the linked snippet (stolen from fossil, of course, so the
original SQL came from you ;).

Thanks! That clarifies it!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Trying to understand curious SQL tracing behaviour

[Richard Hipp]

On Mon, Feb 3, 2014 at 12:30 PM, Stephan Beal  wrote:

> Hi, all,
>
> i have a library which makes very heavy use of sqlite3 and sqlite3_trace().
> All is running fine and well, but recently, for one particular query, i
> started noticing that it gets traced twice: once with its bound values
> expanded and once in its raw form (without bound values), but the second
> form gets traced with a '-- ' comment prefix which is nowhere to be found
> in my code. For example, this is a single step over one query:
>
> SQL TRACE #765: SELECT (max(event.mtime)-2440587.5)*86400 FROM mlink, event
> WHERE mlink.mid=event.objid   AND mlink.fid=3202   AND +mlink.mid IN ok;
> SQL TRACE #767: -- SELECT (max(event.mtime)-2440587.5)*86400 FROM mlink,
> event WHERE mlink.mid=event.objid   AND mlink.fid=?   AND +mlink.mid IN ok;
>

SQLite sends trace output each time a statement first beings executing.  In
your case, the statement started executing once and generated the first
line of output.  But then it noticed that the schema had changed, so it
abandoned that execution, reprepared the statement with the new schema,
then started the statement a second time.  On the second start, the "--"
prefix was added.  The "--" prefix makes the second start a comment so that
you can reply your trace log through the sqlite3 command-line shell and get
the same sequence of SQL statements run.


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

Re: [sqlite] possible bug 3.8.1 /3.8.3

[Richard Hipp]

Can you provide data?  Without some sample data, we cannot tell if the
answer SQLite is providing is right or wrong.


On Mon, Feb 3, 2014 at 12:25 PM, Adam Devita  wrote:

> Good day,
>
> I'm debugging some code that uses 3.8.1, and I've tried just upgrading to
> 3.8.3, which didn't work.  The observation is that
>
> This query:
> SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp)
> FROM data WHERE stream_num = ?) LIMIT 1
>
> seems to occasionally produce a wrong result (the content of data_blob is
> incorrect given the values of stream_num)
>
> yet this query
> SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp asc
> LIMIT 1
>
> seems just fine, insofar as the same tests on the same data have not hit
> any of the error condition / contradiction.
>
> in both cases sqlite3_bind_int(pStmt, 1, (int)nStream); is used for
> parameter ?
>
>
> We are using an in memory database as a smarter queue where timestamp data
> gets inserted, and if the db size is sufficient (30 to 40 records) the
> above query lets us pop the earliest timestamp (which is stored as int64
> via sqlite3_bind_int64).
>
> Is this a possible bug or am I missing something?  Using the backup api to
> look at it from a file
> sqlite>.schema
> CREATE TABLE data ( id INTEGER PRIMARY KEY, timestamp BIGINT NOT NULL,
> stream_num TINYINT, source_seq_num TINYINT,
> event_seq_num INT, data_address BIGINT NOT NULL, data_blob BLOB NOT NULL);
>
> sqlite> SELECT id, data_blob FROM data WHERE stream_num = 2 order by
> timestamp asc  LIMIT 1;
> 4|☺
> sqlite> SELECT id, data_blob FROM data WHERE timestamp = (SELECT
> MIN(timestamp) FROM data WHERE stream_num = 2) LIMIT 1;
> 3|☻
> sqlite>
>
> regards,
> Adam DeVita
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] possible bug 3.8.1 /3.8.3

[Igor Tandetnik]

On 2/3/2014 12:25 PM, Adam Devita wrote:

This query:
SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp)
FROM data WHERE stream_num = ?) LIMIT 1

seems to occasionally produce a wrong result (the content of data_blob is
incorrect given the values of stream_num)

yet this query
SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp asc
LIMIT 1

seems just fine


If timestamp is not unique - if there is more than one record carrying 
the smallest timestamp - then these queries might produce different rows.


The first query doesn't even restrict the result to a particular 
stream_num. If you have two records with the same timestamp but 
different stream_num, then the first query might return the "wrong" one 
(one where stream_num does not equal the bound parameter). The second 
query is only ambiguous if there are two records with the same timestamp 
and the same stream_num.

--
Igor Tandetnik

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

Re: [sqlite] possible bug 3.8.1 /3.8.3

[Dan Kennedy]

On 02/04/2014 12:25 AM, Adam Devita wrote:

Good day,

I'm debugging some code that uses 3.8.1, and I've tried just upgrading to
3.8.3, which didn't work.  The observation is that

This query:
SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp)
FROM data WHERE stream_num = ?) LIMIT 1

seems to occasionally produce a wrong result (the content of data_blob is
incorrect given the values of stream_num)

yet this query
SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp asc
LIMIT 1

seems just fine, insofar as the same tests on the same data have not hit
any of the error condition / contradiction.

in both cases sqlite3_bind_int(pStmt, 1, (int)nStream); is used for
parameter ?


We are using an in memory database as a smarter queue where timestamp data
gets inserted, and if the db size is sufficient (30 to 40 records) the
above query lets us pop the earliest timestamp (which is stored as int64
via sqlite3_bind_int64).

Is this a possible bug or am I missing something?  Using the backup api to
look at it from a file
sqlite>.schema
CREATE TABLE data ( id INTEGER PRIMARY KEY, timestamp BIGINT NOT NULL,
stream_num TINYINT, source_seq_num TINYINT,
event_seq_num INT, data_address BIGINT NOT NULL, data_blob BLOB NOT NULL);

sqlite> SELECT id, data_blob FROM data WHERE stream_num = 2 order by
timestamp asc  LIMIT 1;
4|☺
sqlite> SELECT id, data_blob FROM data WHERE timestamp = (SELECT
MIN(timestamp) FROM data WHERE stream_num = 2) LIMIT 1;
3|☻
sqlite>


More than one entry in the table that matches the "timestamp = (SELECT 
MIN ...)" condition? Does the id=3 row show up in the output if you 
remove the LIMIT clause from the second query?






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

[sqlite] Trying to understand curious SQL tracing behaviour

[Stephan Beal]

Hi, all,

i have a library which makes very heavy use of sqlite3 and sqlite3_trace().
All is running fine and well, but recently, for one particular query, i
started noticing that it gets traced twice: once with its bound values
expanded and once in its raw form (without bound values), but the second
form gets traced with a '-- ' comment prefix which is nowhere to be found
in my code. For example, this is a single step over one query:

SQL TRACE #765: SELECT (max(event.mtime)-2440587.5)*86400 FROM mlink, event
WHERE mlink.mid=event.objid   AND mlink.fid=3202   AND +mlink.mid IN ok;
SQL TRACE #767: -- SELECT (max(event.mtime)-2440587.5)*86400 FROM mlink,
event WHERE mlink.mid=event.objid   AND mlink.fid=?   AND +mlink.mid IN ok;


Notice that the second copy has a '-- ' prefix and 'mlink.fid=?' instead of
'mlink.fid=3202'.

i have not seen this tracing duplication happening for any other query, and
several of them use bound parameters (so that is not the trigger).

Using sqlite3 amalgamation:

2014-01-31 11:50:20 21ce9e3a53aed62fbe075b0dbed9faa00218aadc


This is not a problem/bug, per se, just a bit curious and i'd like to
understand why it happens for this particular query and not for any of the
(very many) others.

The code which triggers this case is here:

http://fossil.wanderinghorse.net/repos/libfossil/index.cgi/artifact/8654ca39c00836eb4099462bb1deceafc52b74f3?ln=1060-1097

note that there's a lot of abstraction happening there (the sqlite3 APIs
are not seen directly), but there's nothing magical happening there which
can account for the "--" duplicates.

:-?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] possible bug 3.8.1 /3.8.3

[Adam Devita]

Good day,

I'm debugging some code that uses 3.8.1, and I've tried just upgrading to
3.8.3, which didn't work.  The observation is that

This query:
SELECT id, data_blob FROM data WHERE timestamp = (SELECT MIN(timestamp)
FROM data WHERE stream_num = ?) LIMIT 1

seems to occasionally produce a wrong result (the content of data_blob is
incorrect given the values of stream_num)

yet this query
SELECT id, data_blob FROM data WHERE stream_num = ? order by timestamp asc
LIMIT 1

seems just fine, insofar as the same tests on the same data have not hit
any of the error condition / contradiction.

in both cases sqlite3_bind_int(pStmt, 1, (int)nStream); is used for
parameter ?


We are using an in memory database as a smarter queue where timestamp data
gets inserted, and if the db size is sufficient (30 to 40 records) the
above query lets us pop the earliest timestamp (which is stored as int64
via sqlite3_bind_int64).

Is this a possible bug or am I missing something?  Using the backup api to
look at it from a file
sqlite>.schema
CREATE TABLE data ( id INTEGER PRIMARY KEY, timestamp BIGINT NOT NULL,
stream_num TINYINT, source_seq_num TINYINT,
event_seq_num INT, data_address BIGINT NOT NULL, data_blob BLOB NOT NULL);

sqlite> SELECT id, data_blob FROM data WHERE stream_num = 2 order by
timestamp asc  LIMIT 1;
4|☺
sqlite> SELECT id, data_blob FROM data WHERE timestamp = (SELECT
MIN(timestamp) FROM data WHERE stream_num = 2) LIMIT 1;
3|☻
sqlite>

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

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

[Richard Hipp]

On Sun, Feb 2, 2014 at 11:58 PM, Labar, Ken  wrote:
>
>
>
> This used to work until we upgraded sqlite to v3.8.2. It still does work
at the PC level.

And earlier:


> -  IAR C compiler

Can you try recompiling with all compiler optimizations turned off and see
if you still get the error?

If that fails to resolve the issue, can you compile with -DSQLITE_DEBUG
then run the query *after* first running "PRAGMA vdbe_debug=ON" and then
send us the output?

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

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

[Clemens Ladisch]

Labar, Ken wrote:
> [...]
> select (rtTableSort < 1000) as selected, abbrString from userParameter order 
> by abbrString;
>
> This used to work until we upgraded sqlite to v3.8.2. It still does work at 
> the PC level.

What is the EXPLAIN QUERY PLAN output for this query on the handheld?

When it works, it uses the index on the sort column to ensure that records
are returned in the correct order:
0|0|0|SCAN TABLE userParameter USING INDEX idxAbbrString


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

Re: [sqlite] ORDER BY issue v3.8.2 amalgamtion

[Simon Slavin]

On 3 Feb 2014, at 4:58am, Labar, Ken  wrote:

> select (rtTableSort < 1000) as selected, abbrString from userParameter order 
> by abbrString;
>  
>  
> This used to work until we upgraded sqlite to v3.8.2. It still does work at 
> the PC level.

Thanks for test data and SELECT command, which allow us to test our own 
installations.

What are you seeing from your SELECT that you weren't expecting ?

Can you reduce your INSERTs to just two rows, and still get results you 
consider to be incorrect from the SELECT ?  If so, which two INSERTs are shown 
in the wrong order ?  If not, at what point does the problem go away ?

> I’m wondering if we are short on RAM, and the sort engine is failing quietly, 
> returning the un-sorted query with SQLITE_OK.

If SQLite is doing that, then there is a serious bug in it which will be 
corrected very quickly.  SQLite is not designed to fail 'quietly'.  In the 
unlikely event that it runs out of a resource it should be returning an error.

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

[sqlite] APTCA Check failed

[Uwe Seibt]

Hi all,

The Windows App Certification Kit reports an error for the 
System.Data.SQLite.dll:

System.Data.SQLite.dll failed the APTCA Check

how is it possible to pass the APTCA Check test? Our Application is using .NET 
Framework 2.0 and .NET Framework 3.5

Thank you

Uwe


--

Uwe Seibt, Dipl.-Phys.
t +49 (731) 151 899-32 | @ se...@axaris.de | web 
www.axaris.de

axaris - software & systeme GmbH | Lise-Meitner-Str. 14 | 89081 Ulm | 
Deutschland
Geschäftsführer: Michael Volk, Markus Müller, Klaus Baumgartner | Amtsgericht 
Ulm, HRB 36 88
wissenschaftlicher Beirat: Prof. Dr. Franz Schweiggert

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