Re: [sqlite] write internal blob vs external file
On Fri, Sep 12, 2014 at 11:48 PM, Eric Rubin-Smith eas@gmail.com wrote: Looking at the sqlite web site and mailing lists shows that the SQLite team has taken a stab at answering the question, is it faster to read a blob out of sqlite or out of a file?. See the links below. Does the team have analogous guidance regarding write speeds? Depends if you care only about the initial write, or also about incremental writes. SQLite is transactional, but does not have a level of indirection between the row data, and the blob data. i.e. the blob is in row. So whenever you want to update a single byte of a blob, you must rewrite the whole row, i.e. the whole blob(s) in that row (and I think this applies to any column of the row, not only blob columns). This is just prohibitive, and unmanageable in practice. Oracle has this, and maintains in-row only a lob index, which basically points to the pages of the lob (applies to character lobs too, which SQLite would store as text). Assuming a 4-byte page index, and an 8K page, that's 2,000x less data to rewrite on row updates. You still have to rewrite the row I think, since a single byte update to a blob implies an update to the index to maintain transaction integrity (and Oracle's MVCC), but you're rewriting a lot less data. Until SQLite introduces such blob specific pages and a similar level of indirection (for both blob and text), I'm afraid SQLite cannot reasonably to used for some use cases, which happen to matter to what I'm doing. A new pragma to define a threshold after which large blob and text columns switch from in-row to out-of-row storage would just make my day :). I'm neither an SQLite expert, nor an Oracle expert, so please feel free to correct the above if it's not true or incorrect in any way. Thanks, --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SET (x,y) = (x1,y1)?
I occasionally have the need to update two columns based on complex sub queries, which are often very similar UPDATE t SET x = ( SELECT 1...), y = ( SELECT 2...)-- nearly the same as SELECT 1 ; Normally one could use a CTE to do the work once: WITH cte AS ( SELECT 1 AS x, 2 AS y ) UPDATE t SET x = cte.x, y = cte.y ; However CTEs don't work within triggers. I was wondering hard it would be to support the SET syntax as shown in the subject line. I believe something like that works in PostgreSQL and I could use it in SQLite for performance reasons. UPDATE t SET (x,y) = (SELECT 1,2) ; Alternatively, is there any effort underway to make CTEs work inside triggers? -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SET (x,y) = (x1,y1)?
On Mon Sep 15, 2014 at 10:51:04AM +0200, Mark Lawrence wrote: Normally one could use a CTE to do the work once: WITH cte AS ( SELECT 1 AS x, 2 AS y ) UPDATE t SET x = cte.x, y = cte.y ; Actually this doesn't appear to work. I assumed it would based on the documentation which says: ...common table expressions (ordinary and recursive) are created by prepending a WITH clause in front of a SELECT, INSERT, DELETE, or UPDATE statement. Unfortunately there are no examples given for how a CTE works with an UPDATE. So I tried accessing the cte using subqueries which is perhaps how it is intended: WITH cte AS ( SELECT 1 AS x, 2 AS y ) UPDATE t SET x = (SELECT x FROM cte), y = (SELECT y FROM cte) ; Maybe the documentation could be improved with a couple of UPDATE/INSERT/DELETE examples? However CTEs don't work within triggers. This is still my issue of course. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] write internal blob vs external file
On 09/15/2014 03:18 PM, Dominique Devienne wrote: On Fri, Sep 12, 2014 at 11:48 PM, Eric Rubin-Smith eas@gmail.com wrote: Looking at the sqlite web site and mailing lists shows that the SQLite team has taken a stab at answering the question, is it faster to read a blob out of sqlite or out of a file?. See the links below. Does the team have analogous guidance regarding write speeds? Depends if you care only about the initial write, or also about incremental writes. SQLite is transactional, but does not have a level of indirection between the row data, and the blob data. i.e. the blob is in row. So whenever you want to update a single byte of a blob, you must rewrite the whole row, i.e. the whole blob(s) in that row (and I think this applies to any column of the row, not only blob columns). This is just prohibitive, and unmanageable in practice. I think the only exception to this is if you use the incremental-blob API: http://www.sqlite.org/c3ref/blob_open.html Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] write internal blob vs external file
On Mon, Sep 15, 2014 at 12:29 PM, Dan Kennedy danielk1...@gmail.com wrote: On 09/15/2014 03:18 PM, Dominique Devienne wrote: On Fri, Sep 12, 2014 at 11:48 PM, Eric Rubin-Smith eas@gmail.com wrote: Looking at the sqlite web site and mailing lists shows that the SQLite team has taken a stab at answering the question, is it faster to read a blob out of sqlite or out of a file?. See the links below. Does the team have analogous guidance regarding write speeds? Depends if you care only about the initial write, or also about incremental writes. SQLite is transactional, but does not have a level of indirection between the row data, and the blob data. i.e. the blob is in row. So whenever you want to update a single byte of a blob, you must rewrite the whole row, i.e. the whole blob(s) in that row (and I think this applies to any column of the row, not only blob columns). This is just prohibitive, and unmanageable in practice. I think the only exception to this is if you use the incremental-blob API: http://www.sqlite.org/c3ref/blob_open.html What do you mean Dan? Are you saying that opening an existing blob, writing 1 byte somewhere, and closing it, does not yield the whole row to be updated? Which therefore implies copying the whole old row value somewhere for undo? (or writing the new row value to the WAL, old value + bytes changed). Is this somehow not transactional? or SQLite supports sub-row or sub-page deltas to implement its transactions? I'm confused by your remark. I'm not saying SQLite lacks an API to do incremental updates to blobs, but that its implementation and current file format lacks a way to implement them efficiently as of now, like a file system can. Basically it want to use SQLite as a mini transactional filesystem (w/o any need for directory operations, just file ones, like SQLite own VFL more or less), in libraries that like SQLite support a VFL abstraction. I'd implement those libraries VFLs on top of SQLite blob incremental APIs. It's possible, but nowhere near efficient enough ATM, for the partial update case which is required by those libraries. Just imagine how slow SQLite itself would be, if you based its own VFL on the SQLite's blob APIs. Nest an inner SQLite DB inside a blob inside the outer SQLite, and you'll get what I mean I think, which is that doing any SQL update on the inner DB implies doing copies of the whole inner DB. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Set compile time option
Hi, How do I set the sqlite compile time option SQLITE_DEBUG ? Can you please specify how I should set it in the command line ? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] write internal blob vs external file
On 15 Sep 2014, at 12:19pm, Dominique Devienne ddevie...@gmail.com wrote: On Mon, Sep 15, 2014 at 12:29 PM, Dan Kennedy danielk1...@gmail.com wrote: On 09/15/2014 03:18 PM, Dominique Devienne wrote: whenever you want to update a single byte of a blob, you must rewrite the whole row, i.e. the whole blob(s) in that row (and I think this applies to any column of the row, not only blob columns). This is just prohibitive, and unmanageable in practice. I think the only exception to this is if you use the incremental-blob API: http://www.sqlite.org/c3ref/blob_open.html What do you mean Dan? Are you saying that opening an existing blob, writing 1 byte somewhere, and closing it, does not yield the whole row to be updated? Correct. That's what the incremental-blob API does. It just rewrites bytes wherever the existing row is on disk, updates indexes and performs TRIGGERS where appropriate. It does not rewrite the whole row record, and you can't use it to change the length of the BLOB. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Set compile time option
On 15 Sep 2014, at 12:46pm, Prakash Premkumar prakash.p...@gmail.com wrote: How do I set the sqlite compile time option SQLITE_DEBUG ? Include the following line in your C source code: #define SQLITE_DEBUG 1 near the top, along with any other lines you have starting with '#'. The line should not end with a semicolon. Can you please specify how I should set it in the command line ? That would depend on which compiler you're using. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] write internal blob vs external file
On Mon, Sep 15, 2014 at 1:59 PM, Simon Slavin slav...@bigfraud.org wrote: On 15 Sep 2014, at 12:19pm, Dominique Devienne ddevie...@gmail.com wrote: On Mon, Sep 15, 2014 at 12:29 PM, Dan Kennedy danielk1...@gmail.com wrote: On 09/15/2014 03:18 PM, Dominique Devienne wrote: want to update a single byte of a blob, you must rewrite the whole row, i.e. the whole blob(s) in that row (and I think this applies to any column of the row, not only blob columns). This is just prohibitive, and unmanageable in practice. I think the only exception to this is if you use the incremental-blob API: http://www.sqlite.org/c3ref/blob_open.html What do you mean Dan? Are you saying that opening an existing blob, writing 1 byte somewhere, and closing it, does not yield the whole row to be updated? Correct. That's what the incremental-blob API does. It just rewrites bytes wherever the existing row is on disk, Then how is this transaction safe? I might not be reading the doc right, but I don't see it saying that the incremental bob APIs are not transaction safe. [...] and you can't use it to change the length of the BLOB. And there lies the rub... Just like SQLite might need to allocate a new page for the inner SQLite DB within an outer blob value, thus extending the file / blob, so does those libraries I'm talking about. In any case, I appreciate the details, even though they raise as many questions in mind as they answer, obviously. --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] write internal blob vs external file
On 15 Sep 2014, at 1:23pm, Dominique Devienne ddevie...@gmail.com wrote: On Mon, Sep 15, 2014 at 1:59 PM, Simon Slavin slav...@bigfraud.org wrote: It just rewrites bytes wherever the existing row is on disk, Then how is this transaction safe? I might not be reading the doc right, but I don't see it saying that the incremental bob APIs are not transaction safe. Sorry, but I don't know the details. That API requires that you 'open' the BLOB, do some writes to it and then 'close' it. So perhaps it does a BEGIN before your 'open' and an END after your 'close'. Or perhaps it just wraps the 'close' in the transaction and never makes changes until the 'close'. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Set compile time option
On Mon, Sep 15, 2014 at 8:08 AM, Simon Slavin slav...@bigfraud.org wrote: On 15 Sep 2014, at 12:46pm, Prakash Premkumar prakash.p...@gmail.com wrote: How do I set the sqlite compile time option SQLITE_DEBUG ? Include the following line in your C source code: #define SQLITE_DEBUG 1 near the top, along with any other lines you have starting with '#'. The line should not end with a semicolon. I think Prakash probably wants to set SQLITE_DEBUG on the sqlite3.c source file, not on his own code, which means that adding the #define above won't help. Prakash: Use the -DSQLITE_DEBUG command-line option when you compile. Edit the makefiles if necessary to add that option to the line that runs the compiler. -- 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] write internal blob vs external file
Dominique Devienne wrote: Looking at the sqlite web site and mailing lists shows that the SQLite team has taken a stab at answering the question, is it faster to read a blob out of sqlite or out of a file?. See the links below. Does the team have analogous guidance regarding write speeds? Depends if you care only about the initial write, or also about incremental writes. Thanks to those of you who responded already! I'll give a little more flavor for those who are interested. My particular application is a cache of large, immutable files. The UPDATE case doesn't apply to me for this particular application. I already have a system on top of SQLite's blobs in which I store the file data in chunks, one chunk per row. (I'm downloading the files from the network, and at the moment am using a version of the Python SQLite API that does not have hooks to the incremental blob I/O.) So I download a few megabytes, start the next hunk download in the background (using the requests-futures Python library, which is really sexy BTW), write out the current hunk, commit, continue. The row also has the byte range that it is storing: CREATE TABLE IF NOT EXISTS file( fileID TEXT NOT NULL, start INTEGER NOT NULL, -- offset of first byte of chunk endINTEGER NOT NULL, -- offset of last byte of chunk bytes BLOB NOT NULL, CONSTRAINT BlobSize CHECK(length(bytes) == (end-start+1)) ); All requests are for retrieval of a particular range of bytes for a particular file. So I have an index: CREATE INDEX IF NOT EXISTS FileIdxNameStart ON file( fileID, start ); and I create my own little query plan in application logic for servicing a particular request on the read side. The read side is fast enough for my purposes. Inserts are slow and deletes are slow. The disk I am using is capable of writing about 6.5MB/sec, but my app + SQLite only hit around 600KB-1MB/sec under this scheme, depending on some variables. This is approximately half of the overall speed that I expect (because if I use 'wget' to just write out a downloaded file to disk, I get about 2MB/sec sustained over hundreds of MB). I've played with the chunk size, the synchronous pragma, the commit interval, the page size pragma and the cache size pragma. I have not cleared my application of all blame yet, since I have not taken enough measurements. I'm considering prototyping a C implementation that uses incremental blob I/O to write out the file and is cleverer about the download state machine, but was hoping to know from this group if that is a fool's errand (e.g. if you all have some particular reason for knowing that huge blobs in SQLite are a priori a bad idea for performance reasons). Deletes by fileID are really awful compared to a simple filesystem delete -- like 2 orders of magnitude worse by my eyeballing, at least with the default 1024-byte page size. Again, I think you'd expect it to be -- an unlink(2) only has to touch a few pages of disk, whereas an SQLite delete of 1GB worth of blob rows I guess goes and touches something every page_size bytes (right?). So I'm thinking of just storing the data out of the database, perhaps retaining an SQLite index. But I'm worried about the safety issues that arise from that. E.g. I finish writing my file, I write an index entry and commit it, and immediately afterward I get a power failure. Am I sure that the file bytes are correct? And so on. All of those considerations are right in SQLite's wheelhouse -- I'd hate not to be able to take advantage of it. -- Eric A. Rubin-Smith ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG: Aggregate functions in subqueries
On Sun, Sep 14, 2014 at 12:18 AM, Lea Verou l...@verou.me wrote: Per the 3.7.11 changelog [1], queries of the form SELECT max(x), y FROM table return the value of y from the same row that contains the maximum x value. However, this: select y from (SELECT max(x), y FROM table); would not return the same y rows. This would work as expected: select m, y from (SELECT max(x) as m, y FROM table); I'm not sure if this qualifies as a bug or not, since the behavior is unspecified in the official documentation. Nevertheless, it is now fixed on trunk. -- 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] write internal blob vs external file
On 09/15/2014 06:19 PM, Dominique Devienne wrote: On Mon, Sep 15, 2014 at 12:29 PM, Dan Kennedy danielk1...@gmail.com wrote: On 09/15/2014 03:18 PM, Dominique Devienne wrote: On Fri, Sep 12, 2014 at 11:48 PM, Eric Rubin-Smith eas@gmail.com wrote: Looking at the sqlite web site and mailing lists shows that the SQLite team has taken a stab at answering the question, is it faster to read a blob out of sqlite or out of a file?. See the links below. Does the team have analogous guidance regarding write speeds? Depends if you care only about the initial write, or also about incremental writes. SQLite is transactional, but does not have a level of indirection between the row data, and the blob data. i.e. the blob is in row. So whenever you want to update a single byte of a blob, you must rewrite the whole row, i.e. the whole blob(s) in that row (and I think this applies to any column of the row, not only blob columns). This is just prohibitive, and unmanageable in practice. I think the only exception to this is if you use the incremental-blob API: http://www.sqlite.org/c3ref/blob_open.html What do you mean Dan? Are you saying that opening an existing blob, writing 1 byte somewhere, and closing it, does not yield the whole row to be updated? Which therefore implies copying the whole old row value somewhere for undo? (or writing the new row value to the WAL, old value + bytes changed). Is this somehow not transactional? or SQLite supports sub-row or sub-page deltas to implement its transactions? I'm confused by your remark. SQLite always writes entire pages to disk. But if your blob is large enough it will be spread over multiple overflow pages. In this case if you update a single byte of the blob using the incremental-blob API, only the single modified page is updated on disk - not all the other pages that store data from the same row. Dan. I'm not saying SQLite lacks an API to do incremental updates to blobs, but that its implementation and current file format lacks a way to implement them efficiently as of now, like a file system can. Basically it want to use SQLite as a mini transactional filesystem (w/o any need for directory operations, just file ones, like SQLite own VFL more or less), in libraries that like SQLite support a VFL abstraction. I'd implement those libraries VFLs on top of SQLite blob incremental APIs. It's possible, but nowhere near efficient enough ATM, for the partial update case which is required by those libraries. Just imagine how slow SQLite itself would be, if you based its own VFL on the SQLite's blob APIs. Nest an inner SQLite DB inside a blob inside the outer SQLite, and you'll get what I mean I think, which is that doing any SQL update on the inner DB implies doing copies of the whole inner DB. --DD ___ 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] SET (x,y) = (x1,y1)?
This construct does not work in postgresql 9.3.5 (unless I have a typo). However, I would love for it to work in both Postgresql and Sqlite. djenkins@ostara ~ $ psql -Upostgres -dcapybara_regtest psql (9.3.5) Type help for help. capybara_regtest=# create table test1 (col1 integer, col2 integer, col3 text); CREATE TABLE capybara_regtest=# insert into test1 values (1, 2, 'hello'); INSERT 0 1 capybara_regtest=# update test1 set (col1, col2, col3) = (select 4, 5, 'bye'); ERROR: syntax error at or near select LINE 1: update test1 set (col1, col2, col3) = (select 4, 5, 'bye'); ^ capybara_regtest=# \q On Mon, Sep 15, 2014 at 3:51 AM, Mark Lawrence no...@null.net wrote: I occasionally have the need to update two columns based on complex sub queries, which are often very similar UPDATE t SET x = ( SELECT 1...), y = ( SELECT 2...)-- nearly the same as SELECT 1 ; Normally one could use a CTE to do the work once: WITH cte AS ( SELECT 1 AS x, 2 AS y ) UPDATE t SET x = cte.x, y = cte.y ; However CTEs don't work within triggers. I was wondering hard it would be to support the SET syntax as shown in the subject line. I believe something like that works in PostgreSQL and I could use it in SQLite for performance reasons. UPDATE t SET (x,y) = (SELECT 1,2) ; Alternatively, is there any effort underway to make CTEs work inside triggers? -- Mark Lawrence ___ 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] SET (x,y) = (x1,y1)?
On Mon, Sep 15, 2014 at 10:21 AM, Dennis Jenkins dennis.jenkins...@gmail.com wrote: This construct does not work in postgresql 9.3.5 (unless I have a typo). However, I would love for it to work in both Postgresql and Sqlite. djenkins@ostara ~ $ psql -Upostgres -dcapybara_regtest psql (9.3.5) Type help for help. capybara_regtest=# create table test1 (col1 integer, col2 integer, col3 text); CREATE TABLE capybara_regtest=# insert into test1 values (1, 2, 'hello'); INSERT 0 1 capybara_regtest=# update test1 set (col1, col2, col3) = (select 4, 5, 'bye'); ERROR: syntax error at or near select LINE 1: update test1 set (col1, col2, col3) = (select 4, 5, 'bye'); ^ capybara_regtest=# \q Hum, my test transcript: $ psql psql (9.3.5) Type help for help. tsh009=# create table test1 (col1 integer, col2 integer, col3 text); CREATE TABLE tsh009=# insert into test1 values(1,2,'hello'); INSERT 0 1 tsh009=# update test1 set (col1, col2, col3) = (4,5,'bye'); UPDATE 1 tsh009=# update test1 set (col1, col2, col3) = (select 6,7,'what'); ERROR: syntax error at or near select LINE 1: update test1 set (col1, col2, col3) = (select 6,7,'what'); ^ tsh009=# So I can't use SELECT, but I could use just plain values. This seems to verify what is said on: http://www.postgresql.org/docs/9.3/interactive/sql-update.html quote According to the standard, the column-list syntax should allow a list of columns to be assigned from a single row-valued expression, such as a sub-select: ... This is not currently implemented — the source must be a list of independent expressions. /quote I would like the SET of multiple fields in a single UPDATE command, like what worked in the above transcript. -- There is nothing more pleasant than traveling and meeting new people! Genghis Khan Maranatha! John McKown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SET (x,y) = (x1,y1)?
Maybe you can reformulate the query to fit INSERT OR UPDATE INTO t SELECT t.a,t.b,...,s.x,s.y FROM t, s ... -Ursprüngliche Nachricht- Von: Mark Lawrence [mailto:no...@null.net] Gesendet: Montag, 15. September 2014 10:51 An: sqlite-users@sqlite.org Betreff: [sqlite] SET (x,y) = (x1,y1)? I occasionally have the need to update two columns based on complex sub queries, which are often very similar UPDATE t SET x = ( SELECT 1...), y = ( SELECT 2...)-- nearly the same as SELECT 1 ; Normally one could use a CTE to do the work once: WITH cte AS ( SELECT 1 AS x, 2 AS y ) UPDATE t SET x = cte.x, y = cte.y ; However CTEs don't work within triggers. I was wondering hard it would be to support the SET syntax as shown in the subject line. I believe something like that works in PostgreSQL and I could use it in SQLite for performance reasons. UPDATE t SET (x,y) = (SELECT 1,2) ; Alternatively, is there any effort underway to make CTEs work inside triggers? -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/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@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SET (x,y) = (x1,y1)?
On Sep 15, 2014, at 7:08 PM, Hick Gunter h...@scigames.at wrote: Maybe you can reformulate the query to fit INSERT OR UPDATE INTO t SELECT t.a,t.b,...,s.x,s.y FROM t, s … There is no such a thing as 'INSERT OR UPDATE’ in SQLite. There is a ‘REPLACE’, but it’s definitively not the same as an update. Anyway, what the OP would benefit from is a straightforward MERGE statement: http://en.wikipedia.org/wiki/Merge_(SQL) But there is no such functionality in SQLite either. Sigh... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] BUG: Aggregate functions in subqueries
On Sep 15, 2014, at 4:48 PM, Richard Hipp d...@sqlite.org wrote: On Sun, Sep 14, 2014 at 12:18 AM, Lea Verou l...@verou.me wrote: Per the 3.7.11 changelog [1], queries of the form SELECT max(x), y FROM table return the value of y from the same row that contains the maximum x value. However, this: select y from (SELECT max(x), y FROM table); would not return the same y rows. This would work as expected: select m, y from (SELECT max(x) as m, y FROM table); I'm not sure if this qualifies as a bug or not, since the behavior is unspecified in the official documentation. Nevertheless, it is now fixed on trunk. Considering that the original query is non-sensical to start with, not quite sure what’s there to fix in the first place. Aside, of course, from raising an exception. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Divide by 0 not giving error
Hi all,
I've found that an sql request that I expected to fail, but it didn't. On
the face of it that is good news but there is a potential downside. I wonder
if my expectation is wrong or if this is a bug which so far hasn't been
caught.
The problem deals with dividing by 0. As far as I can remember, in every
programming language that I have ever used and in all databases that I've
used, if you try and divide by 0 the process will fail with a 'divide by
zero' error. Sqlite doesn't seem to do that, it instead returns NULL.
Here is my test script.
select sqlite_version();
drop table t1;
create table t1
(col1 text not null
,col2 real not null
,col3 real not null);
insert into t1 values('A',1,0);
insert into t1 values('B',2,0);
insert into t1 values('C',3,0);
select * from t1;
select dtl.col1
,dtl.col2
,dtl.col2 / tots.tot_value as col_pct
from t1 as dtl
cross join (select sum(col2) as tot_value from t1) as tots;
select dtl.col1
,dtl.col2
,dtl.col2 / tots.tot_value as col_pct
from t1 as dtl
cross join (select sum(col3) as tot_value from t1) as tots;
select col1,col2 / col3
from t1;
And the output from running this using the sqlite shell program (v3.8.6.0)
is:
3.8.6
A|1.0|0.0
B|2.0|0.0
C|3.0|0.0
A|1.0|0.167
B|2.0|0.333
C|3.0|0.5
A|1.0|
B|2.0|
C|3.0|
A|
B|
C|
I also get the same behaviour under 3.8.4.3 and 3.17.6.2.
I've changed my original SQL where I found this to use NULLIF(col3,0) -
which is what I'd normally do if I might encounter this situation in SQL.
My concern is that this is a bug and if it should get fixed in a later build
then any code that I've got which inadvertently relies on this will then
fail.
Should trying to divide by 0 result in an error?
Many thanks,
Dave
Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com http://www.ward-analytics.com
Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Divide by 0 not giving error
On Mon, Sep 15, 2014 at 2:50 PM, Dave Wellman dwell...@ward-analytics.com wrote: The problem deals with dividing by 0. As far as I can remember, in every programming language that I have ever used and in all databases that I've used, if you try and divide by 0 the process will fail with a 'divide by zero' error. Sqlite doesn't seem to do that, it instead returns NULL. SQLite has returned NULL for division by zero for time out of mind. Maybe you are right and that was a bad design decision. But it is not something we can change now, without risk of breaking some fraction of the multiple millions of applications that use SQLite. Bummer. -- 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] Divide by 0 not giving error
On 15 Sep 2014, at 7:50pm, Dave Wellman dwell...@ward-analytics.com wrote: Should trying to divide by 0 result in an error? No. There's no mechanism for reporting a mathematical error in SQL. You can report malformed commands, references to entities (tables, columns, etc.) which don't exist, and complete failure (database corrupt) but you can't report a calculation which failed because of the values found. This means programmers don't have to test their error trapping for an unusual unexpected special case. In the SQL language, NULL is a special value which means 'unknown' or 'missing'. I would expect to see the answer to anything involving division by zero to be NULL. Your solution ... NULLIF(col3,0) is fine for your purposes. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] System.Data.SQLite version 1.0.94.0 released
System.Data.SQLite version 1.0.94.0 (with SQLite 3.8.6) is now available on the System.Data.SQLite website: https://system.data.sqlite.org/ Further information about this release can be seen at https://system.data.sqlite.org/index.html/doc/trunk/www/news.wiki Please post on the SQLite mailing list (sqlite-users at sqlite.org) if you encounter any problems with this release. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] query REAL without trailing decimal and zero?
When I query a field defined with type 'real', I get '.0' appended to the results for whole numbers. For example if the value in the field is 1, it appears as 1.0 in the query results. Is there some way for me to change this? I poked around in the code and it appeared that the format string being used was something like sqlite3_snprintf(nByte, pMem-z, %!.15g, pMem-r); I didn't find any documentation on what the ! character does, but the g format specifier seems to indicate that the decimal point and trailing zero ought not to be included. http://en.wikipedia.org/wiki/Printf_format_string#Type Any tips would be appreciated! Erik -- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] query REAL without trailing decimal and zero?
On Mon, Sep 15, 2014 at 3:02 PM, Nelson, Erik - 2
erik.l.nel...@bankofamerica.com wrote:
When I query a field defined with type 'real', I get '.0' appended to the
results for whole numbers. For example if the value in the field is 1, it
appears as 1.0 in the query results.
Is there some way for me to change this?
Instead of SELECT x FROM... you could enter SELECT printf('%g',x) FROM
--
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] query REAL without trailing decimal and zero?
Richard Hipp wrote on Monday, September 15, 2014 3:16 PM
On Mon, Sep 15, 2014 at 3:02 PM, Nelson, Erik - 2
erik.l.nel...@bankofamerica.com wrote:
When I query a field defined with type 'real', I get '.0' appended to
the results for whole numbers. For example if the value in the field
is 1, it appears as 1.0 in the query results.
Is there some way for me to change this?
Instead of SELECT x FROM... you could enter SELECT printf('%g',x)
FROM
Yes, understood. I was hoping to affect the default behavior and don't mind
maintaining the patch.
--
This message, and any attachments, is for the intended recipient(s) only, may
contain information that is privileged, confidential and/or proprietary and
subject to important terms and conditions available at
http://www.bankofamerica.com/emaildisclaimer. If you are not the intended
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Divide by 0 not giving error
Richard, Thanks for that, at least I know that what I'm seeing is going to continue. Simon, I'm really surprised at that. Effectively what this means is that the answer that Sqlite returns may or may not be the correct result. I realise this may only be in a single circumstance but that is still what it means. I suppose we then get into a discussion of what is the 'correct result'. I completely understand that NULL is unknown, but I've always thought that there is a difference between unknown and 'error'. I was always taught in maths that dividing by 0 is not possible (certainly at school, I don't know what happens if you study maths at degree level), the closest that I ever got to an answer for that calculation was 'infinity'. Maybe this is one downside of my working with the same dbms for 20+ years, I've just got used to the way that it works. I realise that there will be differences between dbms's, but now that I'm starting to use sqlite I'm surprised by some of the differences. Thanks for the info. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 Registered in England and Wales. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: 15 September 2014 20:02 To: General Discussion of SQLite Database Subject: Re: [sqlite] Divide by 0 not giving error On 15 Sep 2014, at 7:50pm, Dave Wellman dwell...@ward-analytics.com wrote: Should trying to divide by 0 result in an error? No. There's no mechanism for reporting a mathematical error in SQL. You can report malformed commands, references to entities (tables, columns, etc.) which don't exist, and complete failure (database corrupt) but you can't report a calculation which failed because of the values found. This means programmers don't have to test their error trapping for an unusual unexpected special case. In the SQL language, NULL is a special value which means 'unknown' or 'missing'. I would expect to see the answer to anything involving division by zero to be NULL. Your solution ... NULLIF(col3,0) is fine for your purposes. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Divide by 0 not giving error
On 15 Sep 2014, at 8:33pm, Dave Wellman dwell...@ward-analytics.com wrote: Simon, I'm really surprised at that. Effectively what this means is that the answer that Sqlite returns may or may not be the correct result. What ? No. It's correct. The answer is not known, and NULL means I don't know. Given that anything divided by 0 is infinity, and anything divided by itself is 1 what is 0/0 ? I realise this may only be in a single circumstance but that is still what it means. I suppose we then get into a discussion of what is the 'correct result'. I completely understand that NULL is unknown, but I've always thought that there is a difference between unknown and 'error'. It is not an error to divide things by zero as long as that's what you meant to do. An error would be to divide by 6 when you meant to divide by zero. You may be used to thinking the answer is error because you see calculators and spreadsheets showing error on their display. But that's just your calculator refusing to get into a philosophical discussion about the nature of transfinite numbers. Because it hasn't had enough beer yet. I was always taught in maths that dividing by 0 is not possible (certainly at school, I don't know what happens if you study maths at degree level), the closest that I ever got to an answer for that calculation was 'infinity'. That's because you stopped at school. Ask a mathematician. They didn't stop at school but they still don't have a good answer. It's a way of avoiding the problem. It's as correct to say Not A Number or I don't know. And since SQL conveniently has the NULL value for unknown that's what we use. You might find it interesting to read the beginning of this: http://en.wikipedia.org/wiki/NaN By the way, if you run SELECT max(x) FROM myTable and myTable has no rows, what answer would you expect ? I don't mean What would you expect having read the documentation ? I'm interested in what your first thought was. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Divide by 0 not giving error
On 2014/09/15 20:50, Dave Wellman wrote: Hi all, I've found that an sql request that I expected to fail, but it didn't. On the face of it that is good news but there is a potential downside. I wonder if my expectation is wrong or if this is a bug which so far hasn't been caught. The problem deals with dividing by 0. As far as I can remember, in every programming language that I have ever used and in all databases that I've used, if you try and divide by 0 the process will fail with a 'divide by zero' error. Sqlite doesn't seem to do that, it instead returns NULL. This is not the first time this issue has come up and the answers are usually the same - NULL is a very good indicator that the math did not result in a successful calculation, and SQLite has been like this forever, cannot change it now - both valid points btw. but not really conformist. I'd like to propose the NULL return as a superior solution though - If I may illustrate why I think so: A lot of times I use division in queries, in one example system I need to measure the ratio of cost of material issued vs. bill of materials cost in some report, a simple (100*A/B) AS 'CostFactor' makes my query work like a charm. Every now and again some system editor or costing clerk might register a bill of Materials wrongly so that the total cost is 0 or such, an easy fix, but until it is fixed my queries simply show a NULL in the 'CostFact' Column and in fact, this indicates that there is a problem to whomever is reading the report, but most importantly, the entire report doesn't fail, and much less forces a rollback or something horrible until someone sorts out the glitch. Having said that, the other side of the coin needs consideration too... Sometimes an INSERT query populates new data to a transacted job registry or such, in here I need the fields to be explicitly correct and fail very hard on a DIV/0 error and force the rollback with appropriate error message, because financials will be affected the previously harmless error becomes a serious error when channeled into a monetary value journal. I am however well-aware of this problem, as I imagine any system designer should be, so when making these queries, I add fail-safes. The reason I prefer this method is that I have the choice of adding fail-safe code for important queries/functions (which is anyway only 10% or less of the codebase) and no need to add ludicrous amounts of fail-safes to protect the other 90% quick data views or reports from not falling over/rolling back every time a zero value appears. I understand that this assumes I know about the DIV/0 thing and that it does not work the same as the other DBs, but I vote for documenting it well and keeping it like this, even in upcoming SQLite4. Regards, Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Divide by 0 not giving error
On 2014/09/15 22:13, Simon Slavin wrote: On 15 Sep 2014, at 8:33pm, Dave Wellman dwell...@ward-analytics.com wrote: Simon, I'm really surprised at that. Effectively what this means is that the answer that Sqlite returns may or may not be the correct result. What ? No. It's correct. The answer is not known, and NULL means I don't know. Given that anything divided by 0 is infinity, and anything divided by itself is 1 what is 0/0 ? x/0 is of course unknown or, more technically, undefined and not simply infinity as Simon points out later, and mathematicians have reasons for this. If anyone has an interest in understanding the real reasons - one of the most succint descriptions of problems concerning division by zero, 0/0, 0^0, etc. can be found in this youtube video where Dr. James Grime and kie explain some of the oddities: http://www.youtube.com/watch?v=BRRolKTlF6Q And some more interesting information on the History of the number Zero and whether it is even or not - for those interested: http://www.youtube.com/watch?v=8t1TC-5OLdM Happy viewing! Ryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] presentation about ordering and atomicity of filesystems
On Fri, Sep 12, 2014 at 6:47 PM, James K. Lowden jklow...@schemamania.org wrote: On Fri, 12 Sep 2014 19:38:53 +0100 Simon Slavin slav...@bigfraud.org wrote: I don't think it can be done by trying to build it on top of an existing file system. I think we need a file system (volume format, drivers, etc.) built from the ground up with atomicity/ACID/transactions in mind. Since the greatest of these is transactions, I want a transactional file system. Funny you should mention that. About 6 years ago Mike McKusick gave a presentation on then-recent updates to FFS in FreeBSD, including the birthdate. Among other things, I remember he explored using a tree instead of an array for a directory file, but found that because the vast majority of directories hold a small number of names, the overall performance is better with a simple array. ZFS uses a hash table for this. I asked your question: why not add transactions to FFS? His answer: that's the province of a database. I agree, but the filesystem ought to provide a write barrier, and it ought to provide an async fsync() with event completion notification. That should be enough to implement high-performance ACID at the application layer. ZFS provides a write barrier: but it's fsync(), so if you want it to go fast you must either disable sync writes (oof) or use a fast intent log device (oof). With a first-class write barrier we could have both, it and synchronous writes. That's what the proposed osync() is all about, and I say godspeed to them! Of course, the biggest problem with new filesystem interfaces is adoption, but here a handful of apps (e.g., SQLite3) could adopt osync() very quickly, vastly improving safety and performance for a great many users. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] presentation about ordering and atomicity of filesystems
On Fri, Sep 12, 2014 at 7:21 PM, Richard Hipp d...@sqlite.org wrote: On Fri, Sep 12, 2014 at 8:07 PM, Simon Slavin slav...@bigfraud.org wrote: one thing that annoys me about SQLite is that it needs to make a journal file which isn't part of the database file. Why ? Why can't it just write the journal to the database file it already has open ? This would reduce the problems where the OS prevents an application from creating a new file because of permissions or sandboxing. Where in the database does the journal information get stored? At the end? What happens then if the transaction is an INSERT and the size of the content has to grow? Does that leave a big hole in the middle of the file when the journal is removed? During recovery after a crash, where does the recovery process go to look for the journal information? If the journal is at some arbitrary point in the file, where does it look. Note that we cannot write the journal location in the file header because the header cannot be (safely) changed without first journaling it but we cannot journal the header without first writing the journal location into the header. One answer is to use a COW patter, with two or more ubberblocks that store the previous and current/next root of the DB; each ubberblock would also reference a free space map. When you write you just allocate currently unused space from the previous ubberblock's free space map -or grow the file if necessary-, then when all writes for a transaction reach stable storage you write a new ubberblock, with a new free space map. That's a fairly standard COW model (e.g., ZFS does it). I believe you can find prior art going back a long time. E.g., the 4.4BSD LFS was a design sort of like this. ZFS is quite similar to the 4.4BSD LFS, in fact, mostly differing in that a handful of very obvious ways: it doesn't use fixed-sized log chunks, doesn't insist on writing contiguous blocks, and doesn't need a cleaner (the trade-off is framentation); other differences (snapshots, clones, ...) are just icing on the cake that come from reifying ubberblocks. This way you have no log as such because the file is written in a log-like manner anyways: the file *is* the log! Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] presentation about ordering and atomicity of filesystems
On Sat, Sep 13, 2014 at 10:39 AM, Richard Hipp d...@sqlite.org wrote:
I say that a filesystem is an eventually-consistent key/value database.
Yes!
The keys are the filenames and the values are all big BLOBs, specifically
the file content. Filesystems also have a hierarchical keyspace, which is
an extension from the usual key/value concept, but it is still key/value.
POSIX semantics are such that it's easiest for me to think of the
filesystem namespace as a set of {parent_inode_number, name,
inode_number} rows, with a typical hierarchical self-join relation.
Nico
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Divide by 0 not giving error
On Mon, 15 Sep 2014 21:13:01 +0100 Simon Slavin slav...@bigfraud.org wrote: I suppose we then get into a discussion of what is the 'correct result'. I completely understand that NULL is unknown, but I've always thought that there is a difference between unknown and 'error'. It is not an error to divide things by zero as long as that's what you meant to do. An error would be to divide by 6 when you meant to divide by zero. Whether or not something is an error is a matter of definition. SQLite defines division by zero to be NULL. It's very unusual in that regard. It's also unhelpful because the NULL can mask an error in the data or logic. I hope SQL 4.0 will define it as an error instead. Ask a mathematician. They didn't stop at school but they still don't have a good answer. It's a way of avoiding the problem. It's not a problem in any sense. It's known to be undefined. http://mathworld.wolfram.com/DivisionbyZero.html Some operations in math are undefined. It's not cop-out; it's an example of a meaningless expression that the syntax happens to permit. Colorless green ideas sleep furiously. George Carlin's line, Hand me the piano, might be a better example because piano is not among the things that can be handed by most people. Functions in math are defined on a domain and -- what else? -- *undefined* outside that domain. You can't get the circumference of a cube or the slope of a curve. You can't multiply nonconformable matrices. Division by zero stands out because it's so primitive that the arithmetic unit has to cope with it. Part of SQLite's success derives from the care it takes with backward compatibility, and I appreciate it has always been thus will sometimes mean, thus it will always be. Progress requires change, though. One useful guideline for introducing new errors might be, are the function's preconditions met? Also known as, do the inputs belong to the domain on which the function is defined? --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Divide by 0 not giving error
On 16 Sep 2014, at 1:23am, James K. Lowden jklow...@schemamania.org wrote: Whether or not something is an error is a matter of definition. SQLite defines division by zero to be NULL. It's very unusual in that regard. MySQL does it too unless ERROR_FOR_DIVISION_BY_ZERO mode is enabled: http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html SQL Server does it too unless SET ARITHABORT is ON: http://technet.microsoft.com/en-us/library/aa259212(v=sql.80).aspx PostgreSQL doesn't do it, and that annoys some people: http://www.postgresql.org/message-id/a7342e85-e1df-4411-8d0c-0b76a1b3a...@gtwm.co.uk It's also unhelpful because the NULL can mask an error in the data or logic. I hope SQL 4.0 will define it as an error instead. I would rather it didn't because it's yet another thing to look for and trap in your code, and it's one that most people will not think of most of the time. It expands the test suite. It expands your software. NULLs propagate harmlessly downstream. You display or print them and they appear as something blank or NULL or otherwise weird. If a user finds a field unexpectedly NULL, they can figure out what the source of the problem is and correct the data at their leisure. The rest of the program continues to function. Errors crash the program. Start it up again and it might just crash again. Or it might crash again some unpredictable but inconvenient time in the future. And as a user you can't put it right because each time you open the window where you can type the correct data in, the program crashes. You need the help of the developer. At 5:30pm the day before The Big Report is due. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL Query to Vdbe Instructions
Hi, Can you please tell me which function/set of functions convert the SQL query to Vdbe program ? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
