[sqlite] random result

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

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

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

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

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

Re: [sqlite] sqlite3_column_name() issue with views

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

[sqlite] sqlite3_column_name() issue with views

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

Re: [sqlite] Troubleshooting nested query performance issue

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?

Re: [sqlite] Troubleshooting nested query performance issue

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

Re: [sqlite] Troubleshooting nested query performance issue

> 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

Re: [sqlite] Troubleshooting nested query performance issue

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

Re: [sqlite] Troubleshooting nested query performance issue

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?!?

Re: [sqlite] Troubleshooting nested query performance issue

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

[sqlite] Troubleshooting nested query performance issue

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

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

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:

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

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

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

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

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

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

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

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

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

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

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

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

Re: [sqlite] Understanding transactions

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

Re: [sqlite] Understanding transactions

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

Re: [sqlite] Understanding transactions

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

Re: [sqlite] Understanding transactions

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

Re: [sqlite] Understanding transactions

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

Re: [sqlite] "Common Table Expression"

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

[sqlite] recursive common table expression, an example

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

Re: [sqlite] Understanding transactions

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

Re: [sqlite] Understanding transactions

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,

Re: [sqlite] possible bug 3.8.1 /3.8.3

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>

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

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

[sqlite] Understanding transactions

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

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

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

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

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

Re: [sqlite] possible bug 3.8.1 /3.8.3

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,

Re: [sqlite] possible bug 3.8.1 /3.8.3

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

Re: [sqlite] possible bug 3.8.1 /3.8.3

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 = ?)

[sqlite] Trying to understand curious SQL tracing behaviour

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

[sqlite] possible bug 3.8.1 /3.8.3

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

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

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

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

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

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

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

[sqlite] APTCA Check failed

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,