Re: [sqlite] Select count(*)
I believe that when NULLs are allowed as PKs, they are all distinct. So, you can multiple rows with a NULL value as the PK. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, December 11, 2014 11:27 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Select count(*) On Thu, Dec 11, 2014 at 5:03 PM, Gabriel Corneanu gabrielcorne...@gmail.com wrote: I asked a similar question some time ago... See here: https://www.mail-archive.com/sqlite-users@sqlite.org/msg77488.html not a problem for rowid/pk (which are not allowed to be NULL), but it matters a lot in the general case. DRH write: PRIMARY KEYs (except for INTEGER PRIMARY KEYs) are allowed to be NULL in SQLite. This goes back to a bug in the code from many years ago. By the time the bug was discovered, SQLite was already in wide-spread use and so the decision was made to not fix the bug since doing so would cause compatibility problems. it's a bit sad to carry fundamental baggage like, in all cases. I can see why it would stay the default, but surely a pragma could remedy the situation to get the best possible conformance? I know this was discussed before on list, but I for one would welcome this new pragma. To come back on the subject, even if SQLite allows a null PK, there (hopefully) can be a single row using it, and it (this null row) is still indexed, no? So why couldn't sqlite using the PK index to reduce the IO when doing a select count(*) from t_with_non_int_pk, to avoid scanning the table? Perhaps it's a naive question, but I don't see why ATM, and would welcome an explanation. Thanks, --DD ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
I am like you, Gwendal, in that I don't like that behavior in SQLite; however, not liking it doesn't make it a bug. The constraint-checking algorithm was defined to work exactly the way it's working. When designed, the fact that your type of insert would fail was known and understood. Hence, it cannot be considered a bug. Changing it at this date might be a problem. While unlikely, there is a possibility that code exists out there that takes advantage of that particular design attribute. Then you get into pragmas and options and the like. I don't do any of the development, but I suspect that's a serious pain when there are other features that are more useful to work on. So, in short... not a bug, but a design feature that you don't care for. I'm sure there's a way to make suggestions or requests to change the design. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
I'm not sure I'd even consider it broken. SQLite is wonderful. Simply wonderful. Code size and amount of features forced into it impresses me no end. But, it was never intended to run with the big dogs. The fact that, quite often, it can is a tribute to the people that work on it. When making a 'lite' version of something, it's normal to eliminate difficult or intensive features that can be lived without. I think this is one of them. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of John McKown Sent: Monday, December 08, 2014 9:18 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen mlal...@outsitenetworks.com wrote: I am like you, Gwendal, in that I don't like that behavior in SQLite; however, not liking it doesn't make it a bug. On another of my forums, this is called a BAD - Broken, As Designed. As opposed to the normal WAD - Working As Designed. -- The temperature of the aqueous content of an unremittingly ogled culinary vessel will not achieve 100 degrees on the Celsius scale. Maranatha! John McKown ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail
Doesn't that code risk being broken in a later version that doesn't update in the order provided by the sub-query? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of J T Sent: Monday, December 08, 2014 9:23 AM To: rsm...@rsweb.co.za; sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail Might have another work around. update page set position=position + 1 where designation=(select designation from page where book='1' order by position desc) and then insert your page. Please see if that'll work. I tested it, but your results may differ. -Original Message- From: RSmith rsm...@rsweb.co.za To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Mon, Dec 8, 2014 9:15 am Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail On 2014/12/08 15:58, Gwendal Roué wrote: I'm new to this mailing list, and I won't try to push my opinion, which is : yes this is a bug, and this bug could be fixed without introducing any regression (since fixing it would cause failing code to suddenly run, and this has never been a compatibility issue). Thank you all for your support and explanations. The root cause has been found, and lies in the constraint checking algorithm of sqlite. I have been able to find a work around that is good enough for me. Now the subject deserves a rest, until, maybe, someday, one sqlite maintainer who his not attached to the constraint-checking algorithm fixes it. Have a nice day, Gwendal Roué Your new-ness is irrelevant, if you have a worthy argument it deserves being heard. To that end, let me just clarify that nobody was saying the idea of deferring the constraint checking is invalid or ludicrous (at least I had no such intention) and you make a valid point, especially since most other DB engines do work as you suggest - and this will be fixed in SQLite4 I believe, where backward-compatibility is not an issue. The reason I (and others) will say it isn't a bug is because it isn't working different than is intended, or more specifically, than is documented. It works exactly like described - whether you or I agree with that paradigm or not is up to discussion but does not make it a bug as long as it works as described. I hope the work-around you found works great! ___ 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] insert or ignore with foreign keys
I think INSERT OR IGNORE is designed to insert a record into a table if a record with its primary key doesn't already exist. It's not an INSERT AND IGNORE ON ANY ERROR. So: INSERT OR IGNORE INTO t2 VALUES (1,1) INSERT OR IGNORE INTO t2 VALUES (1,1) The above would not cause an error where, INSERT INTO t2 VALUES (1,1) INSERT INTO t2 VALUES (1,1) Would. As for the best way to do what you want, I'm not sure. Obviously, you could precheck the foreign key yourself, but I suspect your actual code is more sophisticated. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Daniel Polski Sent: Tuesday, November 25, 2014 10:23 AM To: General Discussion of SQLite Database Subject: [sqlite] insert or ignore with foreign keys I guess the example below shows the intended behaviour for Sqlite? PRAGMA FOREIGN_KEYS=1; CREATE TABLE t1 ( idINTEGER PRIMARY KEY ); CREATE TABLE t2( idINTEGER PRIMARY KEY, t1_id INT NOT NULL, CONSTRAINT fk FOREIGN KEY(t1_id) REFERENCES t1(id) ); INSERT INTO t1 VALUES(2); BEGIN TRANSACTION; INSERT OR IGNORE INTO t2 VALUES(1, 1); INSERT OR IGNORE INTO t2 VALUES(2, 2); INSERT OR IGNORE INTO t2 VALUES(3, 3); COMMIT; Error: FOREIGN KEY constraint failed I thought row id 1 3 simply would get ignored (due to the foreign key mismatch) when specifying INSERT OR IGNORE, but instead the whole transaction gets aborted. Is there any functionality availible to achieve what I want instead (get row 2,2 added to table t2)? Thanks! /D ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug
I think attachments are dropped. If the SQL is reasonable size, just post it. Otherwise, you'll need to host the screen shot somewhere and link to it. On Nov 19, 2014, at 10:00 PM, Josef Handsuch josef.hands...@gmail.com wrote: Dear developer, I'd like to thank you for you brilliant software. There just seems to be one little bug to report. If I filter a field that I have dedicated to date, the result is always wrong. Please take a look at the screenshot (attached) to see what I mean. What you can see there is that I was trying to view records that were made on November 19th and there should only be 19 records. The progam says there have been made over 150 thousand records that day... Whatever date I try to filter, the result is always wrong like this. Would you please fix this issue? Kind regards, Josef ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
Really? Interesting. So... Select 1 Where 1 inf; ? Or is it just when taking inf by itself? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Monday, May 05, 2014 11:32 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] select 1 where 1 - 1; On Mon, May 5, 2014 at 11:28 AM, mm.w 0xcafef...@gmail.com wrote: select inf; should be true or false? :) Neither true nor false. SQLite considers infinity to be the same as NULL. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select 1 where 1 - 1;
I think everyone agrees that SQLite does not strictly follow the SQL standards for WHERE clause expressions. The question is... should it? One must ask, what makes SQLite lite? I think this kind of simplification is of them. However, I can understand that it might rankle some people. Coming from a C background, when I see that SELECT 1 WHERE 1-1 compiles and treats 1-1 as FALSE, I can work with that. Heavens knows the number of times I've been caught by using a single '=' or tacking on a spare ';' on the end of while statement. And it irritated me that the language even allowed it. (Of course, the language DID allow it, so I have no right to complain.) So, since this is an SQL system designed to cut corners but still get the basic job done, I really have to ask why there's an argument at all on this topic? Both sides are right. Yes, SQLite accepts queries that go against SQL standard. Yes, if your query is formatted correctly, it executes correctly, so the fact that it takes a non-standard query is still an error on the programmer's part, even if SQLite didn't flag it. At least it does have rules on how those are handled. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille Sent: Monday, May 05, 2014 2:46 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] select 1 where 1 - 1; On May 5, 2014, at 8:21 PM, RSmith rsm...@rsweb.co.za wrote: the idea that introducing more complication will make erros/bugs less is just false. Straw man argument, unrelated to the topic at hand. This is solely about the SQL parser failing short of reporting syntax errors for nonsensical queries. select 1 where null; select 1 where is null; Error: near is: syntax error select 1 where not null; select 1 where is not null; Error: near is: syntax error select 1 where not( not null); elect 1 where not( is null ); Error: near is: syntax error select 1 where not( 1 ); select 1 where 'When I use a word, Humpty Dumpty said, in rather a scornful tone, it means just what I choose it to mean - neither more nor less.'; select 1 where not 'The question is, said Alice, whether you can make words mean so many different things.'; ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in division?
Not an error. Int/Int uses integer division and results in an integer number. When one number is a float, the result becomes a float. I don't know about all SQL varieties, but MSSQL is the same. On Apr 30, 2014, at 8:04 AM, Gene Connor neothreeei...@hotmail.com wrote: SELECT DISTINCT 2/4 AS RESULT FROM TABLE;returns 0 SELECT DISTINCT 2/4.0 AS RESULT FROM TABLE;returns 0.5 (correct) SELECT DISTINCT 2.0/4 AS RESULT FROM TABLE;returns 0.5 (correct) SELECT DISTINCT 2.0/4.0 AS RESULT FROM TABLE;returns 0.5 (correct) SELECT DISTINCT 2.0/4.01 AS RESULT FROM TABLE;returns 0.49875 (correct) As long as one or both numbers has at least one decimal place, it calcs correctly. Verified that it also happens in queries using tables and real data. Windows SQLite version 3.8.4.3 2014-04-03 16:53:12 Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug : Tcl interface + INSERT OR REPLACE statement
Yep. What most people want is an INSERT OR UPDATE. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille Sent: Tuesday, September 24, 2013 1:48 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Bug : Tcl interface + INSERT OR REPLACE statement On Sep 23, 2013, at 6:19 PM, Jean-Marie CUAZ jm.c...@orens.fr wrote: - no exception is raised to host langage Tcl for 2 rows not inserted - because a UNIQUE constraint is not respected partial execution : 2 - rows are definitely deleted from the table ...(ouch !) Yep. Looks like it does exactly what it says on the tin: When a UNIQUE constraint violation occurs, the REPLACE algorithm deletes pre-existing rows that are causing the constraint violation prior to inserting or updating the current row and the command continues executing normally. If a NOT NULL constraint violation occurs, the REPLACE conflict resolution replaces the NULL value with the default value for that column, or if the column has no default value, then the ABORT algorithm is used. If a CHECK constraint violation occurs, the REPLACE conflict resolution algorithm always works like ABORT. http://www.sqlite.org/lang_conflict.html Usually... with insight... 'replace' is rarely what one really wants. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug : Tcl interface + INSERT OR REPLACE statement
Considered harmful? How so? I wouldn't mind a version of ON CONFLICT UPDATE fieldlist. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille Sent: Tuesday, September 24, 2013 1:59 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Bug : Tcl interface + INSERT OR REPLACE statement On Sep 24, 2013, at 7:54 PM, Marc L. Allen mlal...@outsitenetworks.com wrote: Yep. What most people want is an INSERT OR UPDATE. Yep. Which is what one usually calls 'MERGE': http://en.wikipedia.org/wiki/Merge_(SQL) And sadly, SQLite doesn't provide anything like that at all. Oh, well... 'ON CONFLICT clause' considered harmful. There you have it. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug : Tcl interface + INSERT OR REPLACE statement
Yes, of course. But, it seems a waste of time, since you're obviously already at the record (or at least done the initial index search) to not be able to simply update it. Not complaining, mind you. MS SQL doesn't have it, and I've long learned to deal with it. Also, there are times when you do a bulk insert, so you have to structure the query to not fail on records that are already present. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, September 24, 2013 2:06 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Bug : Tcl interface + INSERT OR REPLACE statement On 24 Sep 2013, at 6:58pm, Petite Abeille petite.abei...@gmail.com wrote: On Sep 24, 2013, at 7:54 PM, Marc L. Allen mlal...@outsitenetworks.com wrote: Yep. What most people want is an INSERT OR UPDATE. Yep. Which is what one usually calls 'MERGE': http://en.wikipedia.org/wiki/Merge_(SQL) And sadly, SQLite doesn't provide anything like that at all. Which is why you do an INSERT first, and allow it to fail, then do the UPDATE. Or do an UPDATE first and iff that fails, do an INSERT. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug : Tcl interface + INSERT OR REPLACE statement
INSERT OR IGNORE? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, September 24, 2013 2:16 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Bug : Tcl interface + INSERT OR REPLACE statement On 24 Sep 2013, at 7:09pm, Marc L. Allen mlal...@outsitenetworks.com wrote: Also, there are times when you do a bulk insert, so you have to structure the query to not fail on records that are already present. Yeah. Actually I got what I posted wrong. I should have written Which is why you do an INSERT OR FAIL, then do the UPDATE. Or do an UPDATE first and iff that fails, do an INSERT. The first version (INSERT OR FAIL, then UPDATE) won't lead to any SQLite errors if one of the rows already exists. So you can do a whole lot of both lines in one transaction and the transaction will still succeed. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug : Tcl interface + INSERT OR REPLACE statement
So it does! And, for those of us lucky enough to be on a system that isn't 8 years old, enjoy! ;) And now that I see that, I will renew push for upgrades. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille Sent: Tuesday, September 24, 2013 2:14 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Bug : Tcl interface + INSERT OR REPLACE statement On Sep 24, 2013, at 8:09 PM, Marc L. Allen mlal...@outsitenetworks.com wrote: Not complaining, mind you. MS SQL doesn't have it, and I've long learned to deal with it. MS SQL Server sports a MERGE statement if I'm not mistaken: http://msdn.microsoft.com/en-us/library/bb510625.aspx ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Hints for the query planner
As I was reading this, I said to myself, what they really need is a confidence value. Then I read the end and, there it was! A confidence value. Ok.. not exactly confidence, but I think you get my meaning. It seems to me that you're allowing the query writer to substitute personal knowledge of the DB for knowledge based on ANALYZE or other statistical indexes. So, I'm all in favor of allowing that second argument. If so, I would suggest confidence(exp, confidence_value). Or, perhaps, likelihood(..) Likely is fine, or you might even establish several names with built-in defaults... e.g. likely(xxx) might be confidence(xxx, .75) and unlikely(xxx) might be confidence(xxx, .25) You've got rarely, mostly, and a whole suite of other synonyms. This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE question
Yes, thanks. I was mistaken. On Sep 6, 2013, at 9:27 PM, James K. Lowden jklow...@schemamania.org wrote: On Fri, 6 Sep 2013 07:56:53 -0500 Marc L. Allen mlal...@outsitenetworks.com wrote: I don't think it's a bug. It is a bug as long as the behavior is in exception to the documentation. I don't believe there's any defined rule for how SQL should behave, is there? Of course there is. Hundreds of pages describe SQL. The updates are done serially not atomically. They're not *supposed* to be. Updates are absolutely atomic. Every SQL statement is atomic. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE question
No one commented on my second thread (written after I actually understood the
problem!).
But, I proposed a two update sequence to do it.
UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence = seq_to_insert AND
Name = name_to_insert
UPDATE table SET Sequence = -Sequence WHERE Sequence 0 AND Name =
name_to_insert
I've used this system many times to avoid conflicts, but it may not work where
the table needs to be accessed concurrently, as rows will sort of disappear
temporarily (or at least change to an unusable state).
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Dominique Devienne
Sent: Friday, September 06, 2013 3:28 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question
On Fri, Sep 6, 2013 at 6:21 AM, James K. Lowden jklow...@schemamania.orgwrote:
Here's how Marc L. Allen's query should work (different DBMS):
$ bsqldb /tmp/sql
PKey Name Sequence
--- -- ---
1 Blue 1
2 Blue 2
3 Blue 4
4 Blue 5
5 Blue 6
6 Blue 3
6 rows affected
Note that the final insert is assigned the next auto-generated PKey
(6), and the old 3 is now 4, etc.
FWIW, a repro, taking the unrelated PKey out of the picture:
C:\Users\DDeviennesqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table jkl (name text, seq number, unique (name, seq));
sqlite insert into jkl values ('blue', 1), ('blue', 2), ('blue', 3),
('blue', 4), ('blue', 5);
sqlite select * from jkl;
blue|1
blue|2
blue|3
blue|4
blue|5
sqlite update jkl set seq = seq + 1 where seq = 3 and name = 'blue';
Error: columns name, seq are not unique
sqlite
I tried to ruse and use
sqlite update jkl set seq = seq + 1 where name = 'blue' and seq in
sqlite (select
seq from jkl where seq = 3 order by seq desc);
Error: columns name, seq are not unique
but of course you cannot influence the processing order SQLite uses. OK, you
can in a way, see below:
sqlite create table jk2 as select * from jkl order by seq desc; select
sqlite * from jk2;
blue|5
blue|4
blue|3
blue|2
blue|1
sqlite update jk2 set seq = seq + 1 where seq = 3 and name = 'blue';
sqlite select * from jk2;
blue|6
blue|5
blue|4
blue|2
blue|1
sqlite insert into jk2 values ('blue', 3); select * from jk2;
blue|6
blue|5
blue|4
blue|2
blue|1
blue|3
sqlite
By forcing the physical order of the rows to be reversed, the UPDATE succeeds.
I suspect this is just a bug, and Dr Hipp will fix it (and if not document it
somehow).
For reference, the same in Oracle:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL create table jkl
2 ( name varchar2(64)
3 , seq number
4 , unique (name, seq)
5 );
Table created.
SQL insert into jkl values ('blue', 1);
1 row created.
SQL insert into jkl values ('blue', 2);
1 row created.
SQL insert into jkl values ('blue', 3);
1 row created.
SQL insert into jkl values ('blue', 4);
1 row created.
SQL insert into jkl values ('blue', 5);
1 row created.
SQL commit;
Commit complete.
SQL column name format a16;
SQL select * from jkl;
NAMESEQ
--
blue 1
blue 2
blue 3
blue 4
blue 5
SQL update jkl set seq = seq + 1 where seq = 3 and name = 'blue';
3 rows updated.
SQL select * from jkl;
NAMESEQ
--
blue 1
blue 2
blue 4
blue 5
blue 6
SQL insert into jkl values ('blue', 3);
1 row created.
SQL select * from jkl;
NAMESEQ
--
blue 1
blue 2
blue 4
blue 5
blue 6
blue 3
6 rows selected.
SQL
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This email and any attachments are only for use by the intended recipient(s)
and may contain legally privileged, confidential, proprietary or otherwise
private information. Any unauthorized use, reproduction, dissemination,
distribution or other disclosure of the contents of this e-mail or its
attachments is strictly prohibited. If you have received this email in error,
please
Re: [sqlite] UPDATE question
I don't think it's a bug. I don't believe there's any defined rule for how SQL should behave, is there? The updates are done serially not atomically. If the rows happen to be processed in reverse order, then no constraint is violated. In fact, if there was a way to define the order the update was performed, it might be useful as a tool to solve this kind of problem. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dominique Devienne Sent: Friday, September 06, 2013 3:28 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] UPDATE question By forcing the physical order of the rows to be reversed, the UPDATE succeeds. I suspect this is just a bug, and Dr Hipp will fix it (and if not document it somehow). This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE question
Nice, but that still requires extra work. 1) Determine if row is already in table. 2) Determine next lower value. 3) Split difference and insert. There's also the possibility that the higher level APP expects the new row to have a sequence number of 3. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of kyan Sent: Friday, September 06, 2013 10:41 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] UPDATE question On Thu, Sep 5, 2013 at 9:20 PM, Peter Haworth p...@lcsql.com wrote: I have a table with the following (simplified) structure PKeyINTEGER PRIMARY KEY NameTEXT Sequence INTEGER The Name/Sequence pair of columns is defined as UNIQUE I need to insert a new row into a point between two sequence numbers. For example, if the existing rows are: Name Sequence --- Blue 1 Blue 2 Blue3 Blue 4 Blue 5 ... I might need to insert a new Blue/3. If the Sequence column needs not be consecutive integers but just specifies order, consider turning it to a float. Then you can insert a row between two existing rows with Sequence a and b by using their median (a + b) / 2 as the new row's Sequence: Blue 1 Blue 2 -- Blue(2 + 3) / 2 = 2.5 Blue3 Blue 4 Blue 5 and then: Blue 1 Blue 2 -- Blue2.25 Blue2.5 Blue3 Blue 4 Blue 5 and so on. This way you avoid having to modify following rows on each insertion. -- Constantine Yannakopoulos ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE question
MySQL also uses this implementation. They acknowledge that it is not SQL compliant and that (I never thought of this), you cannot delete a record that has a foreign key link to itself. Postgres apparently has the ability to have deferred checking as of V9, but not before then. Please see: http://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-referential-integrity-checks-until-commit -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Friday, September 06, 2013 11:07 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] UPDATE question On Fri, Sep 6, 2013 at 11:00 AM, Dominique Devienne ddevie...@gmail.comwrote: Myself, if I'm thinking in sets, all implementation details aside, the UPDATE statement looks fine and correct, and I'd have expected SQLite to support it. But I'm just waiting to read Dr. Hipp's own read on this now. --DD I'm busy with a different problem and don't have time to study your thread, so I'm guessing at the answer: The UPDATE statement in SQLite operates row-by-row. The effect of early row updates might be visible in later row updates if you contrive a sufficiently complex example. But you really have to go out of your way to do that. If a constraint error happens, the entire UPDATE statement is rolled back (except if OR FAIL is specified - see the docs). Yes, I know this is not relational. No, I do not intend to fix it. - D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Collating sequences. Was: Query problems
The left-most of the first select? Or the second? Maybe I don't understand 'left-most?' -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, September 05, 2013 9:36 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Collating sequences. Was: Query problems On Thu, Sep 5, 2013 at 9:31 AM, Igor Tandetnik i...@tandetnik.org wrote: On 9/5/2013 7:31 AM, Richard Hipp wrote: There seems to be some misinformation circulating in this thread. Please let me try to clear up a few points. While we are at it, an interesting question was raised upthread. What happens here: create table t1(x text collate c1); create table t2(x text collate c2); select x from t1 union select x from t2 order by x; Which collation is used by UNION to deduplicate? Which collation is used by ORDER BY to sort? The left-most. http://www.sqlite.org/src/artifact/8b148eb851f?ln=1582-1602 -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE question
How about... ? UPDATE table SET Sequence = Sequence + 1 WHERE Sequence = seq_to_insert AND Name = name_to_insert -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Haworth Sent: Thursday, September 05, 2013 2:21 PM To: sqlite-users Subject: [sqlite] UPDATE question I have a table with the following (simplified) structure PKeyINTEGER PRIMARY KEY NameTEXT Sequence INTEGER The Name/Sequence pair of columns is defined as UNIQUE I need to insert a new row into a point between two sequence numbers. For example, if the existing rows are: Name Sequence --- Blue 1 Blue 2 Blue3 Blue 4 Blue 5 ... I might need to insert a new Blue/3. Currently I'm doing this by selecting the Pkey values of the entries with sequence =3, sorting them by descending sequence, then a loop with an UPDATE to set the sequence to sequence+1 for each PKey value so 5 becomes 6, 4 becomes 5, 3 becomes 4. Then I INSERT a new Blue/3 row. That works fine but wondering if there might be a single UPDATE statement that could do this for me. I can use the WHERE clause to select sequence 3,4, and 5 but the UPDATE has to process the rows in descending sequence order to avoid UNIQUE conflicts. Pete lcSQL Software http://www.lcsql.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] UPDATE question
Oops.. sorry.. I missed the last paragraph. If you're essentially single threaded.. I can do it in two updates... UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence = seq_to_insert AND Name = name_to_insert UPDATE table SET Sequence = -Sequence WHERE Sequence 0 AND Name = name_to_insert -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Haworth Sent: Thursday, September 05, 2013 2:21 PM To: sqlite-users Subject: [sqlite] UPDATE question I have a table with the following (simplified) structure PKeyINTEGER PRIMARY KEY NameTEXT Sequence INTEGER The Name/Sequence pair of columns is defined as UNIQUE I need to insert a new row into a point between two sequence numbers. For example, if the existing rows are: Name Sequence --- Blue 1 Blue 2 Blue3 Blue 4 Blue 5 ... I might need to insert a new Blue/3. Currently I'm doing this by selecting the Pkey values of the entries with sequence =3, sorting them by descending sequence, then a loop with an UPDATE to set the sequence to sequence+1 for each PKey value so 5 becomes 6, 4 becomes 5, 3 becomes 4. Then I INSERT a new Blue/3 row. That works fine but wondering if there might be a single UPDATE statement that could do this for me. I can use the WHERE clause to select sequence 3,4, and 5 but the UPDATE has to process the rows in descending sequence order to avoid UNIQUE conflicts. Pete lcSQL Software http://www.lcsql.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Table name length in sqlite affects performance. Why?
Not to mention having to check each new table to see if it's already in the
database and the associated physical reads that might be associated with that.
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Jay A. Kreibich
Sent: Wednesday, September 04, 2013 8:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Table name length in sqlite affects performance. Why?
On Tue, Sep 03, 2013 at 06:43:52PM -0600, Jared Albers scratched on the wall:
On my machine, when using relatively short table names like
`TABLE_{table #}`, creation of a database with 10,000 tables takes
approximately 14 seconds. These table names vary from 7 to a max of 11
characters.
When using relatively long table names like `TABLE_{table #}_{some
unique identifying name that adds 120 or so characters}`, creation of
a database with 10,000 tables takes approximately 60 seconds.
Creating the database with long table names took over 4 times longer!
Why is this the case? Is this expected behavior or a bug?
You're asking SQLite to deal with 10x the data, and it takes 4x
longer. That seems like a win to me.
Table names are stored as plain-text strings in the sqlite_master
table. Making the names much, much bigger is going to add more data
to the table, including more database pages. While sqlite_master is
not ordered, so the insertion shouldn't require shuffling the
internal B-tree, you're still dealing with a lot more pages, and
syncing all those pages to disk is going to take longer.
Like any other large insert, try wrapping the whole thing in a
transaction. SQLite is one of the few databases that allows
transactions on DDL.
-j
--
Jay A. Kreibich J A Y @ K R E I B I.C H
Intelligence is like underwear: it is important that you have it, but showing
it to the wrong people has the tendency to make them feel uncomfortable. --
Angela Johnson ___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This email and any attachments are only for use by the intended recipient(s)
and may contain legally privileged, confidential, proprietary or otherwise
private information. Any unauthorized use, reproduction, dissemination,
distribution or other disclosure of the contents of this e-mail or its
attachments is strictly prohibited. If you have received this email in error,
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inefficient covering index used for Subversion with SQLite 3.8.0
Am I understanding that, in this example, the I_NODES_PARENT is being chosen as the search index because... it's smaller and therefore faster to find initial qualifying rows that you can then use in some sort of ordered lookup in another index/table? I'm always in awe of some of the plans a good QA comes up with, and it blows me away that there are cases when using a less-covering index would be better than a more-covering index. This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] segmentation fault with 3.8.0
Silly question.. I looked at the fix. Why ignore indexes with greater than 4 fields? Isn't that a bit risky? Wouldn't it be better to ignore the fields after the 4th one for planning? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, August 29, 2013 11:38 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] segmentation fault with 3.8.0 On Thu, Aug 29, 2013 at 11:32 AM, Stephen Chrzanowski pontia...@gmail.comwrote: My guess is single quotes instead of double quotes. Where exactly is the seg fault? Untested but other than the quotes, everything looks fine. The problem is described here: http://www.sqlite.org/src/info/9f2eb3abac The fix is here: http://www.sqlite.org/src/info/c1152bdcbb A patch release 3.8.0.1 that includes this fix and fixes for two other equally obscure corner cases is currently in test and is expected to go out later today. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] segmentation fault with 3.8.0
Thanks... that certainly clarifies it. Also, thanks to Dan who responded similarly. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, August 29, 2013 11:58 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] segmentation fault with 3.8.0 On Thu, Aug 29, 2013 at 11:47 AM, Marc L. Allen mlal...@outsitenetworks.com wrote: Silly question.. I looked at the fix. Why ignore indexes with greater than 4 fields? Isn't that a bit risky? Wouldn't it be better to ignore the fields after the 4th one for planning? The whereShortCut() routine is merely an optimization. It provides a quick plan for the common case of a single-table query using an equality constraint on a PRIMARY KEY or UNIQUE index. Anything that falls through whereShortCut() goes into the regular query planner and will get analyzed thoroughly there. You could omit the whereShortCut() routine entirely and SQLite would still give the correct answer. The only downside is that sqlite3_prepare_v2() would run slightly slower in the common case. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Different result from experimental query
Looks like that should return one row, yes? I wonder if operator precedence is broken for that query and the OR is binding higher than the AND. Also possible is that the NOT NULL for id in table t is messing up some query optimization with t2.id NOT NULL. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of E.Pasma Sent: Wednesday, August 28, 2013 10:20 AM To: General Discussion of SQLite Database Subject: [sqlite] Different result from experimental query An experimantal query, involving OUTER JOIN with BETWEEN and JOIN with a combined OR and AND expression, does not return all expected rows. I tried this just after SQLite 3.8.0. was released and found that the issue is particular to this version. At least it is alright in version 3.7.17. Below is a simplified case. This is still complex but if you leave out anything further, the problem no longer occurs. Hope it is useful to report this. .echo on .version SQLite 3.8.0 2013-08-26 04:50:08 f64cd21e2e23ed7cff48f7dafa5e76adde9321c2 CREATE TABLE t (id INTEGER PRIMARY KEY NOT NULL) ; INSERT INTO t VALUES (1) ; SELECT * FROMt t1 LEFT OUTER JOIN t t2 ON t2.id BETWEEN 10 AND 20 JOINt t3 ON ( t3.id = t1.id OR t2.id IS NOT NULL AND t3.id = t2.id ) ; -- E Pasma ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] name resolutionn in GROUP BY
Good point. Ok.. I'm convinced. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden Sent: Thursday, August 15, 2013 12:27 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] name resolutionn in GROUP BY On Wed, 14 Aug 2013 14:57:19 -0500 Marc L. Allen mlal...@outsitenetworks.com wrote: I'd actually like a compromise. Allow GROUP BY to accept a derived name if no base name exists. I realize that's against spec, but there's no ambiguity (as it otherwise errors out), It would also mean the query's meaning could change if the underlying DDL changed. If the column were later added (say, to a view) that happened to have the same name as that of the alias in the query, the DML would silently start using the base name instead of the derived one. That's not normally the case in SQL. Normally, adding a column to a table/view cannot change the query's meaning. Either the query is unaffected, or the new column introduces ambiguity (in the presence of a join) that causes it to return an error. The only safe and proper thing is for GROUP BY to refer to the column names as known to the database, not to aliases mentioned in the query. and does make it much nicer when the derived column is a hairy expression that I end up needing to replicate in the GROUP BY clause. We do need a better language, yes. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] name resolutionn in GROUP BY
As does MS SQL 2008 R2
DROP TABLE #Test
CREATE TABLE #Test ( Val int )
INSERT INTO [#Test] ([Val]) VALUES (-2), (2)
SELECT Val FROM #Test GROUP BY Val
SELECT ABS(Val) AS Val FROM #Test GROUP BY Val
Val
---
-2
2
Val
---
2
2
Your requested test case:
Untitled1 m
- -
1 x
1 y
1 z
Untitled1 m
- -
2 z
2 y
2 x
(I used the following code)
CREATE TABLE #t1(m VARCHAR(4));
INSERT INTO #t1 VALUES('az');
INSERT INTO #t1 VALUES('by');
INSERT INTO #t1 VALUES('cx');
SELECT '1', right(m,1) AS m
FROM #t1
ORDER BY m;
SELECT '2', right(m,1) AS m
FROM #t1
ORDER BY lower(m);
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Dominique Devienne
Sent: Wednesday, August 14, 2013 2:00 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] name resolutionn in GROUP BY
On Wed, Aug 14, 2013 at 6:59 PM, Richard Hipp d...@sqlite.org wrote:
On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn
rob.golste...@mapscape.euwrote:
create table test(name);
insert into test values (NULL);
insert into test values ('abc');
select count(),
NULLIF(name,'abc') AS name
from test
group by lower(name);
So the question is, should the name symbol in the GROUP BY clause
refer to the original column name in the TEST table, or should it
refer to the result column called name. SQLite version 3.7.15 picks
the TEST table column. Version 3.7.17 picks the result column.
Anybody know which is correct?
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ...
SQL create table test (name varchar2(64));
Table created.
SQL insert into test values (NULL);
1 row created.
SQL insert into test values ('abc');
1 row created.
SQL select count(*), max(nullif(name, 'abc')) as name from test group
SQL by
lower(name);
COUNT(*) NAME
--
1
1
SQL select count(*), nullif(lower(name), 'abc') as name from test group by
lower(name);
COUNT(*) NAME
--
1
1
Not authoritative of course, but Oracle seems to agree with the previous
behavior. --DD ___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This email and any attachments are only for use by the intended recipient(s)
and may contain legally privileged, confidential, proprietary or otherwise
private information. Any unauthorized use, reproduction, dissemination,
distribution or other disclosure of the contents of this e-mail or its
attachments is strictly prohibited. If you have received this email in error,
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] name resolution in GROUP BY
I seem to recall having read that as well. I believe, however, that MySQL does
allow it, but I think it defaults to base table when available.
Also, a modified form of the test case:
DROP TABLE #t1
CREATE TABLE #t1(m VARCHAR(4));
INSERT INTO #t1 VALUES('az');
INSERT INTO #t1 VALUES('by');
INSERT INTO #t1 VALUES('cx');
SELECT '1', right(m,1) AS m1
FROM #t1
ORDER BY m1;
SELECT '2', right(m,1) AS m1
FROM #t1
ORDER BY m1
Works, returning:
Untitled1 m1
- --
1 x
1 y
1 z
Untitled1 m1
- --
2 x
2 y
2 z
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Peter Aronson
Sent: Wednesday, August 14, 2013 2:13 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] name resolution in GROUP BY
I dug out my copy of THE GUIDE TO THE SQL STANDARD, 4th Edition, by Date and
Darwen, and it states (in a footnote on page 151) that name specified for a
scalar-expression in a SELECT clause can not be used in a WHERE, GROUP BY or
HAVING clause as it is a column in the derived table, not the base table.
Peter
From: Richard Hipp d...@sqlite.org
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Wednesday, August 14, 2013 9:59 AM
Subject: Re: [sqlite] name resolutionn in GROUP BY
On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn
rob.golste...@mapscape.euwrote:
create table test(name);
insert into test values (NULL);
insert into test values ('abc');
select count(),
NULLIF(name,'abc') AS name
from test
group by lower(name);
So the question is, should the name symbol in the GROUP BY clause
refer to the original column name in the TEST table, or should it refer
to the result column called name. SQLite version 3.7.15 picks the
TEST table column. Version 3.7.17 picks the result column.
Anybody know which is correct?
--
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
This email and any attachments are only for use by the intended recipient(s)
and may contain legally privileged, confidential, proprietary or otherwise
private information. Any unauthorized use, reproduction, dissemination,
distribution or other disclosure of the contents of this e-mail or its
attachments is strictly prohibited. If you have received this email in error,
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] name resolution in GROUP BY
Heh... I forgot.. both selects below are identical, as 'lower(m1)' is
incorrect. MS SQL does not permit further operations on the derived value.
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Marc L. Allen
Sent: Wednesday, August 14, 2013 2:17 PM
To: Peter Aronson; General Discussion of SQLite Database
Subject: Re: [sqlite] name resolution in GROUP BY
I seem to recall having read that as well. I believe, however, that MySQL does
allow it, but I think it defaults to base table when available.
Also, a modified form of the test case:
DROP TABLE #t1
CREATE TABLE #t1(m VARCHAR(4));
INSERT INTO #t1 VALUES('az');
INSERT INTO #t1 VALUES('by');
INSERT INTO #t1 VALUES('cx');
SELECT '1', right(m,1) AS m1
FROM #t1
ORDER BY m1;
SELECT '2', right(m,1) AS m1
FROM #t1
ORDER BY m1
Works, returning:
Untitled1 m1
- --
1 x
1 y
1 z
Untitled1 m1
- --
2 x
2 y
2 z
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Peter Aronson
Sent: Wednesday, August 14, 2013 2:13 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] name resolution in GROUP BY
I dug out my copy of THE GUIDE TO THE SQL STANDARD, 4th Edition, by Date and
Darwen, and it states (in a footnote on page 151) that name specified for a
scalar-expression in a SELECT clause can not be used in a WHERE, GROUP BY or
HAVING clause as it is a column in the derived table, not the base table.
Peter
From: Richard Hipp d...@sqlite.org
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Sent: Wednesday, August 14, 2013 9:59 AM
Subject: Re: [sqlite] name resolutionn in GROUP BY
On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn
rob.golste...@mapscape.euwrote:
create table test(name);
insert into test values (NULL);
insert into test values ('abc');
select count(),
NULLIF(name,'abc') AS name
from test
group by lower(name);
So the question is, should the name symbol in the GROUP BY clause
refer to the original column name in the TEST table, or should it refer
to the result column called name. SQLite version 3.7.15 picks the
TEST table column. Version 3.7.17 picks the result column.
Anybody know which is correct?
--
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
This email and any attachments are only for use by the intended recipient(s)
and may contain legally privileged, confidential, proprietary or otherwise
private information. Any unauthorized use, reproduction, dissemination,
distribution or other disclosure of the contents of this e-mail or its
attachments is strictly prohibited. If you have received this email in error,
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This email and any attachments are only for use by the intended recipient(s)
and may contain legally privileged, confidential, proprietary or otherwise
private information. Any unauthorized use, reproduction, dissemination,
distribution or other disclosure of the contents of this e-mail or its
attachments is strictly prohibited. If you have received this email in error,
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] name resolution in GROUP BY
I understand. My previous email had the values of your original request. This email was in response to Peter who found a reference that you could not use derived names in a ORDER BY clause. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, August 14, 2013 2:26 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] name resolution in GROUP BY On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen mlal...@outsitenetworks.comwrote: Heh... I forgot.. both selects below are identical, as 'lower(m1)' is incorrect. MS SQL does not permit further operations on the derived value. I think you also missed the name ambiguity issue. The queries are these: SELECT '1', substr(m,2) AS m FROM t1 ORDER BY m; SELECT '2', substr(m,2) AS m FROM t1 ORDER BY lower(m); Notice that the m in the ORDER BY clause might refer to column t1.m or it might refer to the result set column labeled AS m. The question is which one. PostgreSQL answers t1.m for the first case and AS m for the second. SQLite used to do that, but now it answers t1.m in both cases, which seems to be a better fit to the SQL standard that Peter reports. Marc, if you can also try the query below on SQL Server, that would be most helpful: SELECT '3', substr(m,2) AS m FROM t1 ORDER BY m COLLATE Latin1_General_CS_AS; -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] name resolution in GROUP BY
Ok... looks like MSSQL 2008 R2 picks a different value of 'm' for cases 1 and
2. I'm not sure why 3 isn't the same as 1, though.
CREATE TABLE #t1(m VARCHAR(4));
INSERT INTO #t1 VALUES('az');
INSERT INTO #t1 VALUES('by');
INSERT INTO #t1 VALUES('cx');
SELECT '1', right(m,1) AS m
FROM #t1
ORDER BY m;
SELECT '2', right(m,1) AS m
FROM #t1
ORDER BY LOWER(m)
SELECT '3', right(m,1) AS m
FROM #t1
ORDER BY m COLLATE Latin1_General_CS_AS;
Untitled1 m
- -
1 x
1 y
1 z
Untitled1 m
- -
2 z
2 y
2 x
Untitled1 m
- -
3 z
3 y
3 x
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Richard Hipp
Sent: Wednesday, August 14, 2013 2:26 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] name resolution in GROUP BY
On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen
mlal...@outsitenetworks.comwrote:
Heh... I forgot.. both selects below are identical, as 'lower(m1)' is
incorrect. MS SQL does not permit further operations on the derived value.
I think you also missed the name ambiguity issue. The queries are these:
SELECT '1', substr(m,2) AS m
FROM t1
ORDER BY m;
SELECT '2', substr(m,2) AS m
FROM t1
ORDER BY lower(m);
Notice that the m in the ORDER BY clause might refer to column t1.m or it
might refer to the result set column labeled AS m. The question is which
one. PostgreSQL answers t1.m for the first case and AS m for the second.
SQLite used to do that, but now it answers t1.m in both cases, which seems to
be a better fit to the SQL standard that Peter reports.
Marc, if you can also try the query below on SQL Server, that would be most
helpful:
SELECT '3', substr(m,2) AS m
FROM t1
ORDER BY m COLLATE Latin1_General_CS_AS;
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This email and any attachments are only for use by the intended recipient(s)
and may contain legally privileged, confidential, proprietary or otherwise
private information. Any unauthorized use, reproduction, dissemination,
distribution or other disclosure of the contents of this e-mail or its
attachments is strictly prohibited. If you have received this email in error,
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] name resolutionn in GROUP BY
This appears to be how MS SQL handles it... looking at the definitions below,
MS SQL uses the base value in GROUP BY and the derived value in ORDER BY.
That said, 'lower(m)' referenced the base m, not the derived m in the ORDER BY.
I'm afraid I don't understand enough about COLLATE to get why that changed the
result.
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Igor Tandetnik
Sent: Wednesday, August 14, 2013 2:27 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] name resolutionn in GROUP BY
On 8/14/2013 12:59 PM, Richard Hipp wrote:
On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn
rob.golste...@mapscape.euwrote:
create table test(name);
insert into test values (NULL);
insert into test values ('abc');
select count(),
NULLIF(name,'abc') AS name
from test
group by lower(name);
So the question is, should the name symbol in the GROUP BY clause
refer to the original column name in the TEST table, or should it
refer to the result column called name. SQLite version 3.7.15 picks
the TEST table column. Version 3.7.17 picks the result column.
Anybody know which is correct?
For what it's worth, my reading of SQL-92 suggests that 3.7.15 is correct.
1) If no where clause is specified, then let T be the result of
the preceding from clause; otherwise, let T be the result of
the preceding where clause.
2) Each column reference in the group by clause shall unambigu-
ously reference a column of T.
ORDER BY clause is different - column names there resolve in the context of the
whole preceding SELECT statement, not just its FROM part:
3) Let T be the table specified by the query expression.
4) If ORDER BY is specified, then each sort specification in the
order by clause shall identify a column of T.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This email and any attachments are only for use by the intended recipient(s)
and may contain legally privileged, confidential, proprietary or otherwise
private information. Any unauthorized use, reproduction, dissemination,
distribution or other disclosure of the contents of this e-mail or its
attachments is strictly prohibited. If you have received this email in error,
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] name resolution in GROUP BY
Peter, I'm sorry.. you're correct. I missed that. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Peter Aronson Sent: Wednesday, August 14, 2013 2:53 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] name resolution in GROUP BY Except the quote I provided said nothing about ORDER BY, just WHERE, GROUP BY or HAVING clauses. So I'm not sure what all tests with ORDER BY are demonstrating, since the original question was about GROUP BY, which is a different thing, since ORDER BY operates strictly on the derived table. Peter From: Marc L. Allen mlal...@outsitenetworks.com To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Wednesday, August 14, 2013 11:28 AM Subject: Re: [sqlite] name resolution in GROUP BY I understand. My previous email had the values of your original request. This email was in response to Peter who found a reference that you could not use derived names in a ORDER BY clause. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, August 14, 2013 2:26 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] name resolution in GROUP BY On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen mlal...@outsitenetworks.comwrote: Heh... I forgot.. both selects below are identical, as 'lower(m1)' is incorrect. MS SQL does not permit further operations on the derived value. I think you also missed the name ambiguity issue. The queries are these: SELECT '1', substr(m,2) AS m FROM t1 ORDER BY m; SELECT '2', substr(m,2) AS m FROM t1 ORDER BY lower(m); Notice that the m in the ORDER BY clause might refer to column t1.m or it might refer to the result set column labeled AS m. The question is which one. PostgreSQL answers t1.m for the first case and AS m for the second. SQLite used to do that, but now it answers t1.m in both cases, which seems to be a better fit to the SQL standard that Peter reports. Marc, if you can also try the query below on SQL Server, that would be most helpful: SELECT '3', substr(m,2) AS m FROM t1 ORDER BY m COLLATE Latin1_General_CS_AS; -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] name resolutionn in GROUP BY
I'd actually like a compromise. Allow GROUP BY to accept a derived name if no base name exists. I realize that's against spec, but there's no ambiguity (as it otherwise errors out), and does make it much nicer when the derived column is a hairy expression that I end up needing to replicate in the GROUP BY clause. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, August 14, 2013 3:40 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] name resolutionn in GROUP BY On Wed, Aug 14, 2013 at 3:08 PM, Igor Tandetnik i...@tandetnik.org wrote: Most DBMS allow sorting (and grouping) by arbitrary expressions, which means that the standard is not directly applicable. One has to extrapolate. PostgreSQL, MS-SQL, and SQLite 3.7.15 work one way. Oracle and SQLite 3.7.17 work the other. I think I'm going to revert SQLite to working as does PostgreSQL. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] attaching databases programmatically
I see. Sorry about that! I guess the real problem is the in-memory ones. The other ones must have a database file associated with them, right? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nelson, Erik - 2 Sent: Monday, July 22, 2013 10:51 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] attaching databases programmatically Marc L. Allen wrote on Monday, July 22, 2013 10:47 AM Nelson, Erik wrote: I've got an application that allows the user to create an arbitrary number of databases, either in memory or not. In my C++ program, I have the handles and I'd like to attach them all together so the user can execute queries against them. However, the only way that I've found to do that is to use the ATTACH sql. The problem is that the sqlite3* exist, but the databases aren't in any namespace accessible to the ATTACH query. Is there some way to programmatically attach databases when all you have are the sqlite3 handles? Perhaps I misunderstood the question. It sounds like he has the sqlite* objects for the databases, but wants to be able to determine the database/filename associated with them so he can construct an ATTACH statement in another query. So.. the question is.. given an sqlite*, can you determine the underlying database/filename? Yes, you misunderstood it the databases are programmatically generated- there is no file name. 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] attaching databases programmatically
Perhaps I misunderstood the question. It sounds like he has the sqlite* objects for the databases, but wants to be able to determine the database/filename associated with them so he can construct an ATTACH statement in another query. So.. the question is.. given an sqlite*, can you determine the underlying database/filename? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dušan Paulovic Sent: Monday, July 22, 2013 10:35 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] attaching databases programmatically Perhaps you can create new connection with multiple virtual tables reading from all connections. But it is hard work... 2013/7/22 Nelson, Erik - 2 erik.l.nel...@bankofamerica.com I've got an application that allows the user to create an arbitrary number of databases, either in memory or not. In my C++ program, I have the handles and I'd like to attach them all together so the user can execute queries against them. However, the only way that I've found to do that is to use the ATTACH sql. The problem is that the sqlite3* exist, but the databases aren't in any namespace accessible to the ATTACH query. Is there some way to programmatically attach databases when all you have are the sqlite3 handles? I've read the list history, most of the conversations about this are a few years back. Thanks 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?
It's exhaustive in that it absolutely verifies if the key exists or not. However, it doesn't necessarily do a full database scan. I assume it uses available indexes and does a standard lookup on the key. So, it still might be fast enough for what you want (though I missed the beginning of the thread). -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Friday, May 24, 2013 9:40 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow? On Fri, May 24, 2013 at 7:07 AM, kyan alfasud...@gmail.com wrote: So my question is, is [the foreign_key_check] pragma exhaustive or is it somehow optimised so that it does not always perform a full database scan -e.g. by means of some internal per-transaction FK violation counter or list? Because if it is optimised and thus fast enough then I suppose I can try to use it for the purpose I have described. It is exhaustive -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous
Just to throw in my $0.02 as a user Given the SQL stream of... misc SQL in transaction COMMIT power loss Vs. misc SQL in transaction power loss unexecuted COMMIT Except in cases where, in the first example, I have time to inform someone about the COMMIT before the power loss, there's no functional difference between the two events. I would hate to think I would ever demand that SQLite guarantee the commit occurs regardless of how quickly the power loss happens after the commit statement. For a huge majority of the applications I've dealt with (and I say huge instead of all only in case there's one I've forgotten about), the timing difference doesn't matter. What matters is that when I come back up I have an intact database. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of thanumalayan mad Sent: Wednesday, May 22, 2013 8:31 AM To: Richard Hipp Cc: General Discussion of SQLite Database Subject: Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous I do not observe any loss in durability in WAL mode: it works totally fine. As for the documentation, http://www.sqlite.org/transactional.html and http://www.sqlite.org/features.html claim that SQLite is durable during power failures; and DELETE is the default journal_mode. Also, other pages, http://www.sqlite.org/pragma.html#pragma_synchronous, http://www.sqlite.org/atomiccommit.html, and http://www.sqlite.org/faq.html, made me think that rollback journaling ensures durability (given a honest disk and a honest OS), although those pages do not legally say that; I'm not sure if others would understand it this way though. The usual opinion in blogosphere (and forums) also seems to be that SQLite is, by default, durable across power failures, though they might be meaning only about a 5 second eventual durability. Finally, (I hope I do not come across here as being authoritative), a quick fix might be explicitly mentioning somewhere in the documentation that DELETE and TRUNCATE modes do not ensure immediate durability after a power loss; this would combat any wrongly-understood claims in the rest of the documentation. Also, not to spam, but it would be great if you could answer these questions for my research (you might send me a reply directly without going through the mailing list): [a] Was it always understood that unlink() and ftruncate() are not synchronous, and that SQLite transactions in DELETE mode are not immediately-durable in Linux; or had you initially misunderstood the semantics of those calls, or left-off the fsync() because of a typo error? [b] While designing the crash-tests, were the semantics of the calls in Unix understood? What if ftruncate() not being synchronous did lead to a consistency-loss? Was it reasoned-out that the non-synchronous ftruncate would not produce corruption? [c] How much of a loss in durability (what other than 5 seconds) would be good enough in most cases? ... Again, sorry for the spam; my research is trying to make sense of the flushing-mess in the entire storage stack, and feedback would be extremely useful. PS: @Richard Hipp and other developers - thanks for SQLite! Amazing piece of software. -- Thanumalayan Sankaranarayana Pillai (Graduate student at the University of Wisconsin-Madison) On Wed, May 22, 2013 at 5:49 AM, Richard Hipp d...@sqlite.org wrote: On Sat, May 18, 2013 at 4:41 AM, thanumalayan mad madth...@gmail.comwrote: Expected result: You always find that the transaction had been executed. Observed result: You sometimes find that the transaction did not execute. The core team has discussed this. In order to avoid a substantial performance hit against transaction COMMIT, we have chosen to not do fsyncs on the directory when a file is unlinked, and thus to allow loss of durability following a power loss event. ACI without the D is still guaranteed. But not the D. The overwhelming majority of applications care not one wit about durability following power loss. For most applications, it is sufficient that the file is uncorrupted. If recovery gives you a snapshot of the file as it existed 5 seconds prior to the power loss, that's fine. WAL-mode transactions should be durable across power-loss events. So if durability is vitally important to you, you can always set PRAGMA journal_mode=WAL. Are you observing loss of durability following power loss in WAL mode? Is there any place in the documentation that we have overlooked where SQLite claims to be durable across a power loss in rollback mode? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged,
Re: [sqlite] Max of 63 columns for a covering index to work?
I'm not familiar with that. It's a view where Oracle actually stores the view data as a physical table? And updates these tables as the main table updates? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dominique Devienne Sent: Thursday, May 23, 2013 8:57 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? On Wed, May 22, 2013 at 9:11 PM, Marc L. Allen mlal...@outsitenetworks.comwrote: [...]. It makes me think you might be better off using triggers to maintain separate tables with covered data instead of indexes. [...]. This sounds like Oracle's materialized views to me, which come in synchronous (trigger-based) or asynchronous (log-mining-based) variants. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite NULL or 0-Length
No. All SQL functions can safely take NULL as an argument. LENGTH(NULL) returns NULL, so LENGTH(NULL) = 0 is always false. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Richardson Sent: Thursday, May 23, 2013 3:34 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite NULL or 0-Length Simon, Is there a danger here if firstname is NULL and the LENGTH() function is called first? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, May 23, 2013 2:54 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite NULL or 0-Length On 23 May 2013, at 7:06pm, Jill Rabinowitz jillrabinow...@gmail.com wrote: Does anyone know how I can check the column and set it to a value if it has nothing in it? UPDATE myTable SET firstname='xxx' WHERE LENGTH(firstname) = 0 OR firstname IS NULL 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Max of 63 columns for a covering index to work?
I think there might be a disconnect. You can have a covering index on a 300 column table... it just can't cover any column past the 63rd (or 64th?). It's not perfect, but not as bad as not being able to have a covering index at all. At least, that's how I read some of the answers. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of David de Regt Sent: Wednesday, May 22, 2013 2:59 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? Hm. That's a wee bit of an issue for us, then. May want to stick that on the limitations page... :) It seems like covering indexes become increasingly useful the more columns you have on a table. When I have a 4-column table, if my covering index uses 3 columns, that's not as big a read savings as if I have a 300 column table that I only need to handle 3 columns from in a WHERE, and it otherwise needs to pull the row/page from the original table to get the value on. Back to the trenches to rearchitect this... Thanks for the quick clarification. :) -David -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, May 22, 2013 11:53 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? On Wed, May 22, 2013 at 2:37 PM, David de Regt dav...@mylollc.com wrote: I'm experimenting with covering indices on one of our larger tables. *[many words expressing concern that SQLlite does not use covering indices on tables with more than 63 colums]...* Your observations are correct. If a query uses any column of a table past the 63rd column, then that query cannot use a covering index on that table. This is due to the use of 64-bit unsigned integer bitmasks to keep track of which columns have been used in order to discover whether or not a covering index will work. -- 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Max of 63 columns for a covering index to work?
I was just thinking of that... if you have needs to dozens of covering indexes, then the index maintenance anytime you modify the table must be enormous. It makes me think you might be better off using triggers to maintain separate tables with covered data instead of indexes. The only downside to that, I suppose, is that you have to pick the right table when doing the select. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of David de Regt Sent: Wednesday, May 22, 2013 3:07 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? Correct. However, we have a pile of different uses on this table. I'm trying to evaluate if we can move all covering index columns into the first 63, but I'm not sure it's going to work, especially long term as we continue to grow the data. We'll see... In the medical industry I used to work in, there were commonly huge denorm event tables in multiple massive information systems, with covering indexes on several dozen different sets of large numbers of columns. With 100+ million records in the table, it was the only way to read from it in a performant fashion. -David -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marc L. Allen Sent: Wednesday, May 22, 2013 12:02 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? I think there might be a disconnect. You can have a covering index on a 300 column table... it just can't cover any column past the 63rd (or 64th?). It's not perfect, but not as bad as not being able to have a covering index at all. At least, that's how I read some of the answers. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of David de Regt Sent: Wednesday, May 22, 2013 2:59 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? Hm. That's a wee bit of an issue for us, then. May want to stick that on the limitations page... :) It seems like covering indexes become increasingly useful the more columns you have on a table. When I have a 4-column table, if my covering index uses 3 columns, that's not as big a read savings as if I have a 300 column table that I only need to handle 3 columns from in a WHERE, and it otherwise needs to pull the row/page from the original table to get the value on. Back to the trenches to rearchitect this... Thanks for the quick clarification. :) -David -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Wednesday, May 22, 2013 11:53 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? On Wed, May 22, 2013 at 2:37 PM, David de Regt dav...@mylollc.com wrote: I'm experimenting with covering indices on one of our larger tables. *[many words expressing concern that SQLlite does not use covering indices on tables with more than 63 colums]...* Your observations are correct. If a query uses any column of a table past the 63rd column, then that query cannot use a covering index on that table. This is due to the use of 64-bit unsigned integer bitmasks to keep track of which columns have been used in order to discover whether or not a covering index will work. -- 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments
Re: [sqlite] Max of 63 columns for a covering index to work?
I haven't had a table that large, but I have had big ones... the disadvantage is the number of records you can scan in a single disk read, but an advantage is that you don't have to take the time to join tables, especially when you need to do it ALL THE TIME. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, May 22, 2013 3:11 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Max of 63 columns for a covering index to work? On 22 May 2013, at 7:58pm, David de Regt dav...@mylollc.com wrote: if I have a 300 column table I'm going to sound my customary note of caution here. Do you really have a 300 column table or is it several thinner tables which have the same primary key ? Or do you really have a property list which should be one thinner table with a two-column primary key ? Generally in database design you should be able to hold a table schema in your head. When you find yourself numbering columns it's usually a sign you're doing something wrong. Not true in every case, of course, and you may have one of the incredibly rare cases which really is best represented with a 300 column table. In which case, please excuse me. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer bug?
In looking at the draft plan... am I right in assuming that at any 'stop' you can eliminate paths which have consumed the identical set of nodes but are more expensive? For instance, at stop 2, the draft shows: R-N1 (cost: 7.03) N1-R (cost: 7.31) R-N2 (cost: 9.08) N2-R (cost: 9.08) Since the first two consume both R and N1, will N1-R ever be a better path than R-N1? If not, then it can be removed from the set, reducing the amount of work later. Similarly with R-N2 and N2-R. Since they consume the same nodes and have identical costs, does it matter which one is used? Won't any path R-N2-x-y-z.. be identical to N2-R-x-y-z? Maybe I'm over simplifying? Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Tuesday, April 30, 2013 5:34 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Query optimizer bug? On 4/30/2013 5:26 PM, Ryan Johnson wrote: Being able to force certain access methods (use *this* index, not that one) would be helpful, though (does + do that or just suggest it?). Unary plus turns a simple column reference (for which an index can be used) into an expression (which cannot be indexed). This may be used to suppress the choice of a particular index, at which point the query planner is likely to choose a different index, often rearranging the joins. So the unary plus does often influence the query plan, but in a rather roundabout way. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer bug?
Another thought... since you are limiting yourself to a maximum number of paths at any given time, if you're willing to take the full hit for maintaining the full N=30 (or whatever) paths, instead of simply eliminating worse paths with identical nodes, allow yourself to continue finding more paths to bring your total back up to 30. For instance, in the example with N=4, and N1-R would be eliminated, you could take the '5th best' path to maintain your count of 4. So, you'd be taking the best N paths that have a unique set of nodes. I don't know if that will give enough bang for your buck, as the extra lower paths might rarely yield a final better result. Marc From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp [d...@sqlite.org] Sent: Tuesday, April 30, 2013 6:20 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Query optimizer bug? On Tue, Apr 30, 2013 at 5:46 PM, Marc L. Allen mlal...@outsitenetworks.comwrote: In looking at the draft plan... am I right in assuming that at any 'stop' you can eliminate paths which have consumed the identical set of nodes but are more expensive? Yes. Good idea. I have updated the document accordingly. Note that by merging equivalent plans, the optimal plan for TPC-H Q8 is found with N==10 instead of N==21. For instance, at stop 2, the draft shows: R-N1 (cost: 7.03) N1-R (cost: 7.31) R-N2 (cost: 9.08) N2-R (cost: 9.08) Since the first two consume both R and N1, will N1-R ever be a better path than R-N1? If not, then it can be removed from the set, reducing the amount of work later. Similarly with R-N2 and N2-R. Since they consume the same nodes and have identical costs, does it matter which one is used? Won't any path R-N2-x-y-z.. be identical to N2-R-x-y-z? Maybe I'm over simplifying? Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto: sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Tuesday, April 30, 2013 5:34 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Query optimizer bug? On 4/30/2013 5:26 PM, Ryan Johnson wrote: Being able to force certain access methods (use *this* index, not that one) would be helpful, though (does + do that or just suggest it?). Unary plus turns a simple column reference (for which an index can be used) into an expression (which cannot be indexed). This may be used to suppress the choice of a particular index, at which point the query planner is likely to choose a different index, often rearranging the joins. So the unary plus does often influence the query plan, but in a rather roundabout way. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer bug?
Oops.. nevermind. You already had that covered. ;) That'll teach me to answer on my phone before reading the revised draft. From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp [d...@sqlite.org] Sent: Tuesday, April 30, 2013 6:20 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Query optimizer bug? On Tue, Apr 30, 2013 at 5:46 PM, Marc L. Allen mlal...@outsitenetworks.comwrote: In looking at the draft plan... am I right in assuming that at any 'stop' you can eliminate paths which have consumed the identical set of nodes but are more expensive? Yes. Good idea. I have updated the document accordingly. Note that by merging equivalent plans, the optimal plan for TPC-H Q8 is found with N==10 instead of N==21. For instance, at stop 2, the draft shows: R-N1 (cost: 7.03) N1-R (cost: 7.31) R-N2 (cost: 9.08) N2-R (cost: 9.08) Since the first two consume both R and N1, will N1-R ever be a better path than R-N1? If not, then it can be removed from the set, reducing the amount of work later. Similarly with R-N2 and N2-R. Since they consume the same nodes and have identical costs, does it matter which one is used? Won't any path R-N2-x-y-z.. be identical to N2-R-x-y-z? Maybe I'm over simplifying? Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto: sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Tuesday, April 30, 2013 5:34 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Query optimizer bug? On 4/30/2013 5:26 PM, Ryan Johnson wrote: Being able to force certain access methods (use *this* index, not that one) would be helpful, though (does + do that or just suggest it?). Unary plus turns a simple column reference (for which an index can be used) into an expression (which cannot be indexed). This may be used to suppress the choice of a particular index, at which point the query planner is likely to choose a different index, often rearranging the joins. So the unary plus does often influence the query plan, but in a rather roundabout way. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ 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 This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?
It has around 500 context switches per second.. so I'm thinking MosYield.
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of markus diersbock
Sent: Thursday, April 18, 2013 1:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?
Put SQLite.Interop.dll in the same folder as your VB assembly (ie /debug or
/release)
On Thu, Apr 18, 2013 at 9:22 AM, Gilles Ganault gilles.gana...@free.fr wrote:
Hello,
I'm having a problem getting VB Express to find SQLite .Net:
1. I went to...
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
... to download sqlite-netFx45-binary-Win32-2012-1.0.84.0.zip
2. Unzipped file in C:\Downloads\SQLite\.Net\1.0.84.0\
3. Launched VB Express 2010 to create new Windows Form project
4. Project Add Reference: Navigated to
C:\Downloads\SQLite\.Net\1.0.84.0\ and pointed to
System.Data.SQLite.dll
5. Wrote SQLite test code:
Dim SQLconnect As New SQLite.SQLiteConnection()
Dim SQLcommand As SQLiteCommand
'Note: Non-admin app cannot write to c:\
SQLconnect.ConnectionString = Data
Source=c:\users\fred\test.sqlite;
SQLconnect.Open()
'SQLcommand = SQLconnect.CreateCommand
'SQLcommand.CommandText = CREATE TABLE IF NOT EXISTS Item
(type)
'SQLcommand.ExecuteNonQuery()
'SQLcommand.CommandText = INSERT INTO Item (type) VALUES
('something')
'SQLcommand.ExecuteNonQuery()
'SQLcommand.Dispose()
SQLconnect.Close()
6. Launched app: Unable to load DLL 'SQLite.Interop.dll': The
specified module could not be found. (Exception from HRESULT:
0x8007007E)
What is the right way to install SQLite.Net so that applications can
find it?
Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Markus Diersbock
SwingNote
Marblehead, MA
C 781.929.0693
F 888.654.6068
This e-mail is intended solely for the person or entity to which it is
addressed and may contain confidential information. If you have received this
e-mail in error, please contact the sender immediately and delete the material
from any computer. Any review, dissemination, copying, printing or other use of
this e-mail by persons or entities other than the addressee is prohibited.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This email and any attachments are only for use by the intended recipient(s)
and may contain legally privileged, confidential, proprietary or otherwise
private information. Any unauthorized use, reproduction, dissemination,
distribution or other disclosure of the contents of this e-mail or its
attachments is strictly prohibited. If you have received this email in error,
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?
Sorry... replied to the wrong message. :(
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Marc L. Allen
Sent: Thursday, April 18, 2013 1:40 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?
It has around 500 context switches per second.. so I'm thinking MosYield.
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of markus diersbock
Sent: Thursday, April 18, 2013 1:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?
Put SQLite.Interop.dll in the same folder as your VB assembly (ie /debug or
/release)
On Thu, Apr 18, 2013 at 9:22 AM, Gilles Ganault gilles.gana...@free.fr wrote:
Hello,
I'm having a problem getting VB Express to find SQLite .Net:
1. I went to...
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
... to download sqlite-netFx45-binary-Win32-2012-1.0.84.0.zip
2. Unzipped file in C:\Downloads\SQLite\.Net\1.0.84.0\
3. Launched VB Express 2010 to create new Windows Form project
4. Project Add Reference: Navigated to
C:\Downloads\SQLite\.Net\1.0.84.0\ and pointed to
System.Data.SQLite.dll
5. Wrote SQLite test code:
Dim SQLconnect As New SQLite.SQLiteConnection()
Dim SQLcommand As SQLiteCommand
'Note: Non-admin app cannot write to c:\
SQLconnect.ConnectionString = Data
Source=c:\users\fred\test.sqlite;
SQLconnect.Open()
'SQLcommand = SQLconnect.CreateCommand
'SQLcommand.CommandText = CREATE TABLE IF NOT EXISTS Item
(type)
'SQLcommand.ExecuteNonQuery()
'SQLcommand.CommandText = INSERT INTO Item (type) VALUES
('something')
'SQLcommand.ExecuteNonQuery()
'SQLcommand.Dispose()
SQLconnect.Close()
6. Launched app: Unable to load DLL 'SQLite.Interop.dll': The
specified module could not be found. (Exception from HRESULT:
0x8007007E)
What is the right way to install SQLite.Net so that applications can
find it?
Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
Markus Diersbock
SwingNote
Marblehead, MA
C 781.929.0693
F 888.654.6068
This e-mail is intended solely for the person or entity to which it is
addressed and may contain confidential information. If you have received this
e-mail in error, please contact the sender immediately and delete the material
from any computer. Any review, dissemination, copying, printing or other use of
this e-mail by persons or entities other than the addressee is prohibited.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This email and any attachments are only for use by the intended recipient(s)
and may contain legally privileged, confidential, proprietary or otherwise
private information. Any unauthorized use, reproduction, dissemination,
distribution or other disclosure of the contents of this e-mail or its
attachments is strictly prohibited. If you have received this email in error,
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This email and any attachments are only for use by the intended recipient(s)
and may contain legally privileged, confidential, proprietary or otherwise
private information. Any unauthorized use, reproduction, dissemination,
distribution or other disclosure of the contents of this e-mail or its
attachments is strictly prohibited. If you have received this email in error,
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug on real operations
Yes.. for what it's worth, I've had this very same problem on MS SQL 2008. Comparing floating point values in their raw form is always dangerous. It just works so much more often than not that it's easy to forget until you get that one number that doesn't work. The solution for MS SQL was conversion to smallmoney. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Friday, March 08, 2013 1:37 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Bug on real operations On 8 Mar 2013, at 6:24pm, Israel Lins Albuquerque israelin...@yahoo.com.br wrote: I don't know how postgres handle this, may be I can check Postgres has special datatypes used especially to handle problems like this. It has both artbitrary precision and monetary datatypes. If you present your problem to postgres and let it use floating point numbers it has the same problem SQLite has. However, I used to work with banks and other financial institutions for a living and I can assure you that the systems I wrote and used used integer datatypes to handle amounts of money. For historical reasons they do sometimes multiply by 10,000 instead of 100, but either way all amounts of money are stored as integers. This speeds up calculations, reduces storage space, and reduces the complexity of testing required. Just to underline what Richard wrote, this is not a bug in the way SQLite handles floating point. The bug is in thinking you can express decimal fractions as binary floating point numbers and it is very familiar to computer scientists. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]
If I have any doubt, I add .5 (or .05, .005, whatever) before the operation. I
know that breaks algebraic rounding, but that's one I live with.
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of James K. Lowden
Sent: Friday, March 08, 2013 2:45 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join
inconsistency]
On Thu, 7 Mar 2013 18:45:23 +
Simon Slavin slav...@bigfraud.org wrote:
what do you think the desired behaviour would be for
CAST('0.9' AS INTEGER)
I know what I want. Perhaps this can be fixed in SQLite4.
Sorry, but CAST is not a math function. There's probably a language somewhere
out there that rounds floating point when assigned to integer, but every
language I've ever used that supported those types truncated the fractional
portion. It's also what the SQL standard specifies.
Consider, do you want
CAST(0.5 as integer)
to be 1? What about 0.49? What about 0.499?
These issues are why round() exists.
--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
This email and any attachments are only for use by the intended recipient(s)
and may contain legally privileged, confidential, proprietary or otherwise
private information. Any unauthorized use, reproduction, dissemination,
distribution or other disclosure of the contents of this e-mail or its
attachments is strictly prohibited. If you have received this email in error,
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory DB - Query does not return all records after Update
Are you finalizing the UPDATE statement? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of mike.akers Sent: Monday, February 25, 2013 4:48 PM To: sqlite-users@sqlite.org Subject: [sqlite] Memory DB - Query does not return all records after Update I am using an in-memory database with a singleton containing a single connection. I have multiple threads (3) using this same connection, but I have ensured that only 1 thread uses the connection at a time. If I do an UPDATE on a record then immediately preform a SELECT, the query will return all rows that match the WHERE clause except for the record that was being updated. The sqlite3_prepare_v2 and sqlite3_step both return SQLITE_OK and do not return a SQLITE_LOCKED or SQLITE_BUSY so I would assume that my write was completed. I would like to be able to either 1. read the old data (as the quieres happen frequently a couple old data queries are fine) I have tried read_uncommited with no noticable difference. or 2. Have the database return LOCKED or BUSY if the write has not completed so that the query will return all of the results. Example, If I insert 10 records and loop a query I receive 10, 10, 10, 10, results But, if I change one of the records during the loop I get 10, 10, update record, 9, 10, Any advice on what I am missing? Thanks in advance, Mike -- View this message in context: http://sqlite.1065341.n5.nabble.com/Memory-DB-Query-does-not-return-all-records-after-Update-tp67267.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] Would anyone know how to use Sqlite to calculate the streakedness of data? The definition of streakedness is show below. Thank you for your help
I haven't done SQLite coding in several months, and it's quite rusty, so I'll paraphrase. I haven't tested and if this is bogus, I'm sorry in advance. But maybe it'll give someone the right idea. You might be better off with a custom function, though. It would be something like this: CREATE TABLE D ( Pos INTEGER PRIMARY KEY AUTOINCREMENT, Value INTEGER, Streak INTEGER ) (insert data into table D, with Streak set to 0.) Insert invalid data point at the end (e.g. -1) Update Streak such that it's equal to 1 if it is higher or equal to the value at POS - 1. (This should result in the first POS, streak staying 0 and the final invalid point also having streak = 0) UPDATE D SET Streak = 1 WHERE Pos IN (SELECT d2.POS FROM D d1 INNER JOIN D d2 WHERE d1.POS = d2.POS - 1 AND d2.value = d1.value) Now, D should contain zero's for the beginning positions for each streak. Simply examine the distance between consecutive zeros. CREATE TABLE Streak ( First INTEGER, Last INTEGER, Length INTEGER ) INSERT INTO Streak SELECT d1.pos, d2.pos, d2.pos - d1.pos FROM D d1 INNER JOIN D d2 ON d2.pos d1.pos WHERE d1.value = 0 and d2.value = 0 and not exists (SELECT d3.pos FROM D d3 where d3.value = 0 and d3.pos d1.pos and d3.pos d2.pos) That should give you a list of streaks, including streaks of 1. Aggregate any way you want. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Frank Chang Sent: Tuesday, February 19, 2013 8:38 AM To: sqlite-users@sqlite.org Subject: [sqlite] Would anyone know how to use Sqlite to calculate the streakedness of data? The definition of streakedness is show below. Thank you for your help Would anyone know how to use Sqlite to calculate the streakedness of data? The definition of streakedness is show below. Thank you for your help. [EDIT] From our company's chief software architect, here is the requirement for a statistical measure. Could someone please define a statistical formula based onour architect's definition of data streakedness? -- February 19th 2013 8:45 AM Equal numbers are a streak. 1,2,3,3,3,4,5 has a streak of 7. Case A: 1,2,3,4,5,6,7,8,9,10,11,12,13 has a longest streak of 13. Case B: 1,2,3,4,5,6,7,3,8,9,10,11,12 has a longest streak of 7, a second smaller streak of 6. Case C: 1,2,3,4,5,6,7,1,2,3,4,5,6 has a longest streak of 7, and a second smaller streak of 6. Case D: 1,2,3,4,5,6,7,1,2,3,1,2,1 has a longest streak of 7, a second smaller streak of 3, and a third smallest streak of 2 Case E: 1,2,3,4,5,6,7,6,5,4,1,2,3 has a longest streak of 7, and a second smaller streak of 3. Case F: 1,2,3,4,5,6,7,6,5,4,3,2,1 has a longest streak of 7, and no smaller streaks. The cases A - F are ordered in 'most sorted to least sorted', but all have the same length longest streak. Using the averages of streak length is not appropriate: A: Average = 13/1 = 13 B: Average = (7+6)/2 = 6.5 C: Average = (7+6)/2 = 6.5 D: Average = (7+3+2)/3 = 4 E: Average = (7+3)/2 = 5 F: Average = 7/1 = 7 Factoring in non-streaks (counting them as 1's): A: Average = 13/1 = 13 B: Average = (7+6)/3 = 4.3 C: Average = (7+6)/2 = 6.5 D: Average = (7+3+2+1)/4 = 3.25 E: Average = (7+1+1+1+3)/5 = 2.6 F: Average = (7+1+1+1+1+1+1)/7 = 1.85 ___ 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
[sqlite] SQLite 4
I'm sorry if this isn't the right group, but an earlier message mentioned it, and I found some stuff on the SQLite website. Although I've had a long-standing project to incorporate SQLite into our product (and have done so), it has never been deployed. I like the looks of SQLite 4, but have not been able to find any proposed release schedules. Can someone point me to an appropriate page or let me know if an official release is planned anytime soon? Our platform is proprietary, so I'll need to be incorporating source, not binaries. Thanks, Marc -- ** * * * * Marc L. Allen * ... so many things are * * * possible just as long as you* * Outsite Networks, Inc. * don't know they're impossible. * * (757) 853-3000 #215 * * * * * * mlal...@outsitenetworks.commailto:mlal...@outsitenetworks.com * -- The Phantom Tollbooth * * * * ** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite 4
Thanks. Any clue on whether we'll use an amalgamation as with SQLite4 or direct sources? Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Tuesday, February 12, 2013 11:32 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite 4 On Tue, Feb 12, 2013 at 11:26 AM, Marc L. Allen mlal...@outsitenetworks.com wrote: I'm sorry if this isn't the right group, but an earlier message mentioned it, and I found some stuff on the SQLite website. Although I've had a long-standing project to incorporate SQLite into our product (and have done so), it has never been deployed. I like the looks of SQLite 4, but have not been able to find any proposed release schedules. Can someone point me to an appropriate page or let me know if an official release is planned anytime soon? Our platform is proprietary, so I'll need to be incorporating source, not binaries. There is no planned release schedule for SQLite4 at this time. It seems to be working pretty well, but it is not nearly as well-tested as is SQLite3. And there are more interface changes that we want to make before it goes into any kind of official release. -- 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] SQL query
Add a group by name, hash and change the select to be name, min(setid), hash? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Thursday, January 31, 2013 4:48 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL query Still playing with this I have the following table and I run the following query - the results of which are what I expect name, num, md5 sqlite select * from rtable; $RmMetadata|0|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 $RmMetadata|1|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 $RmMetadata|2|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 $RmMetadata|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5 $RmMetadata|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 $RmMetadata|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 sqlite select * from rtable where num 0 and md5 not in (select md5 sqlite from rtable where num = 0); $RmMetadata|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5 $RmMetadata|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 $RmMetadata|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 What I want to do is return only one unique row for each MD5 - so the return set would be, this bit is failing me $RmMetadata|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5 $RmMetadata|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 Thanks ___ 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] SQL query
Actually... with that requirement, I wonder if it's even easier/better to use: Select name, min(setid), hash From rtable Group by name, hash Having min(setid) 0 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Thursday, January 31, 2013 5:08 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL query Cool that seems to work - thanks ___ 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] Running on windows 98
Might I suggest you include his patch so it at least runs? That way, if he's willing to test each new version, he doesn't need to modify the official source to do it. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, January 24, 2013 1:20 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Running on windows 98 On Wed, Jan 9, 2013 at 2:36 PM, Jose F. Gimenez jfgime...@wanadoo.eswrote: Hi all, I've recently noticed that sqlite doesn't work on windows 98 since version 3.7.12, due to the change to use OVERLAPPED when read and write files. I've made a small modification to take care of it, and avoid to use that way on win 98 (by using the function isNT() to check that condition). I've attached a patch in this message. I've tested it and it seems to work fine (tested on win98, xp and win7), but I'd like to know if it's correct or if there is any problem which I can't see. We have no way of testing SQLite on Win9x and so we do not intend to support Win9x moving forward. Some older versions of SQLite are known to work on Win9x. If you are still supporting Win9x applications, I suggest you use those older versions of SQLite. TIA, Jose F. Gimenez ___ 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] SQL query
If you simply want a list of all files that are present and are not also present in set 0 (I'm not sure how 'duplicated' means anything different...) SELECT f.name, f.set, f.hash FROM files f LEFT OUTER JOIN files f2 ON f2.name = f.name and f2.set = 0 WHERE f.set != 0 and f2.name is null -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Wednesday, January 23, 2013 12:18 PM To: General Discussion of SQLite Database Subject: [sqlite] SQL query I have a database with many million rows with in it each representing a file. There are many duplicate files in the database and all files are hashed. The files are sub categorised into a number of sets, numbered 0 to 10 for example. Files do not need to be in every set. I need to select all files that are in any set other than 0 that are not duplicated/present in set 0 So a sample database might contain columns name set hash with sample data file10ABCD file11ABCD file13EF01 file20BCE2 file22BCE2 file35EE34 file40EE22 My query would return file13EF01 file35EE34 ___ 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] order by a huge number does not work, but random well why ?
If you literally used ORDER BY 1234567892 then there's nothing in the record being sorted. I can't recall is SQLite allows order by aliases, but something like.. Select ..., random() as X Order by X Might work, as long as random() is executed for each row. (Sorry.. don't have a quick SQLite engine available to test) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of François-xavier Jacobs Sent: Monday, January 14, 2013 9:48 AM To: sqlite-users@sqlite.org Subject: [sqlite] order by a huge number does not work, but random well why ? Hi everyone i would like to seed random a request, so i could do use some pagination system with a order by random(), is this possible sqlite ? when a tried to use order by 1234567892 it always return the same order ___ 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] PERSIST Journal Mode
Some additional information. It appears that setting the journal_mode is not inherited by DBs that are attached after the PRAGMA journal_mode is executed. So, I added one after the ATTACH. It also appears that, as part of the PRAGMA journal_mode execution, the current journal file is deleted. My SQL activity tends to be a lot of open DB, do work, close DB. That is, I don't open the database and leave it open. When I run multiple transactions, the journal is not deleted between each one. Am I right in assuming that upon opening a database (or the first real command thereafter), journals are deleted as a matter of course? I also have no additional information as to why having PERSIST mode on prevents the database from being updated/correct. I did check the sqlite3_close command, and I'm passing it the connection received from sqlite3_open. It returns SQLITE_OK. I'll check to see if something is being rolled back when that happens. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Monday, December 17, 2012 5:46 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] PERSIST Journal Mode On 17 Dec 2012, at 8:35pm, Marc L. Allen mlal...@outsitenetworks.com wrote: Another item.. when having Journal Mode = PERSIST, DBA (in the example below) was not being physically updated. DBB was. I can think of a reason you might not be able to see an update until you have executed _close(). So are you looking to see an update before or after your code has executed _close() ? How are you looking for an update ? The file's modification timestamp ? Also, are you sure you are executing _close() with the right parameter, and can you check the result returned from _close() to make sure it returns SQLITE_OK ? Simply taking out the PRAGMA fixed things. Any ideas where I should look? Clip from your earlier post: open(DBA) PRAGMA journal_mode = PERSIST; ATTACH DBB SQL ... close() If the DBA.journal file exists, it's deleted before the attach (though I haven't identified exactly where. I can if important.) During the close, the DBA.mjx is deleted as is the DBB.journal file. The DBA.journal file is not deleted until the next time I open it. One situation I think might cause this is if the database isn't closed properly. When SQLite reopens the database it realises it is corrupt. It restores the database to a usable condition, then (this is just a guess) it deletes the old journal so it can make a new uncorrupted one. However, I can't answer your basic problem. I see no reason why these things should change just because you're using PERSIST mode. But there are people who understand SQLite internals better than I do. 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] PERSIST Journal Mode
I believe only WAL mode is sticky. Not using the pragma seems to generate deletions as normal. The missing data was from after the file was closed. I am still looking into rollback, but why would journal mode matter? Marc On Dec 18, 2012, at 12:02 PM, Simon Slavin slav...@bigfraud.org wrote: On 18 Dec 2012, at 3:04pm, Marc L. Allen mlal...@outsitenetworks.com wrote: I also have no additional information as to why having PERSIST mode on prevents the database from being updated/correct. I did check the sqlite3_close command, and I'm passing it the connection received from sqlite3_open. It returns SQLITE_OK. Well, it seems you're doing it right. It's possible that your operating system doesn't show updates until the file is closed. The other thing is that journal mode is meant to be sticky with each database file. It may be that when you issue 'PRAGMA journal_mode', even though you're setting the same mode that's already active, SQLite reinitialises the journal. Now you have the correct journal mode set for your database files you shouldn't need to use that PRAGMA again. Does that fix things ? 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
[sqlite] PERSIST Journal Mode
I'm having some unexpected results using PRAGMA journal_mode = PERSIST; My general flow runs something like: open(DBA) PRAGMA journal_mode = PERSIST; ATTACH DBB SQL ... close() If the DBA.journal file exists, it's deleted before the attach (though I haven't identified exactly where. I can if important.) During the close, the DBA.mjx is deleted as is the DBB.journal file. The DBA.journal file is not deleted until the next time I open it. Am I misunderstanding something? Thanks, Marc -- ** * * * * Marc L. Allen * ... so many things are * * * possible just as long as you* * Outsite Networks, Inc. * don't know they're impossible. * * (757) 853-3000 #215 * * * * * * mlal...@outsitenetworks.commailto:mlal...@outsitenetworks.com * -- The Phantom Tollbooth * * * * ** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
The shared cache does not know that the table is small nor that there is nothing else to load. When a thread accesses that shared cache, it must protect itself from the data page it's on being modified, either because the page is simply flushed from the cache (if the cache does such things) or replaced by another page because this one happened to be the least recently used (or whatever rule the cache uses). (As with others, I haven't actually looked at the code.) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Boris Kolpackov Sent: Monday, August 13, 2012 8:51 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Multi-Thread Reads to SQLite Database Hi Richard, Richard Hipp d...@sqlite.org writes: In shared-cache mode, the page cache is shared across threads. That means that each thread must acquire a mutex on the page cache in order to read it. Which means that access to the page cache is serialized. I just ran our concurrency test in different configurations and I observer a similar behavior. That is, in the shared-cache mode, read-only transactions on the same table are executed pretty much sequentially. Also, your explanation doesn't feel quite satisfactory to me. In his original email, Eric mentioned that his table contains just 50 rows. Surely all this data would be loaded into the cache the first time it is requested and then accessed concurrently by all the threads. The only way I can see how the sequential performance could be explained here is if the cache mutex did not distinguish between readers and writers (which would seem to be a fairly natural thing to do). In our test, on the other hand, each thread queries its own set of rows from the table. So, based on your explanation, here each thread should end up with its own set of pages (more or less). However, even in this case, I still observe a near sequential performance. Any idea what else might be going on here? Boris -- Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog Compiler-based ORM system for C++ http://codesynthesis.com/products/odb Open-source XML data binding for C++ http://codesynthesis.com/products/xsd XML data binding for embedded systems http://codesynthesis.com/products/xsde ___ 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] Multi-Thread Reads to SQLite Database
I wonder if it would be possible to refine the cache locking mechanism. If I understand the modified DB/Table locking semantics when running under a shared-cache, it appears that the cache page should be protected against readers and writers. Perhaps only the list of pages in the cache need to be protected by a higher-level mutex? That is, if you want access to a page, you grab the mutex, flag the page as 'in-use' with a reference counter, release the mutex, and go on about your business. If you have multiple readers, they would be able to access the physical page concurrently. When access is complete, the reference count would be decremented. Marc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Multi-Thread Reads to SQLite Database
I don't believe I'm suggesting one mutex per page. If I understand correctly, the purpose of the overall mutex is to prevent a page from being removed underneath a user. If the standard DB locking semantics are working properly, I think there is no possibility of a page's data from being modified underneath another user. That would be no different than a physical DB page being modified underneath another user. If the above it true, cache protection semantics are strictly concerned with page management. That is, a page is requested that is not in the cache and needs to be inserted into it. If the cache is full, another page needs to be released. All that is required is protecting pages currently in use from being released. I think, instead of a mutex serializing access to the entire cache, all that is needed is a mutex serializing access to the cache meta-data and the use of reference counts to help the page replacement algorithm make a good choice in which page to remove. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Monday, August 13, 2012 10:23 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Multi-Thread Reads to SQLite Database On 13 Aug 2012, at 3:11pm, Marc L. Allen mlal...@outsitenetworks.com wrote: I wonder if it would be possible to refine the cache locking mechanism. If I understand the modified DB/Table locking semantics when running under a shared-cache, it appears that the cache page should be protected against readers and writers. All shared assets must be protected against readers and writers. A reader needs to know that nothing is going to scramble the data while it's reading. A writer must know that nothing is reading the data it's about to change. (A little handwaving and a few exceptions, but that's the gist of it.) Perhaps only the list of pages in the cache need to be protected by a higher-level mutex? That is, if you want access to a page, you grab the mutex, flag the page as 'in-use' with a reference counter, release the mutex, and go on about your business. You are proposing one mutex per page. This is a system which many DBMSes use but it would /greatly/ slow down SQLite. Also it would complicate the code quite a bit since a write to one page often leaks over to neighbouring pages. If you have multiple readers, they would be able to access the physical page concurrently. When access is complete, the reference count would be decremented. To get the effect of this, simply stop using shared-cache. Let each process have its own cache. That way each process knows nothing is messing with its cache. 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] 1: near ): syntax error
Try removing the comma before the closing parenthesis? And add a semi-Colin after the create table command. On Aug 9, 2012, at 12:04 AM, Brandon Pimenta brandonskypime...@gmail.com wrote: I just found a bug in SQLite. It says 1: near ): syntax error. Here's my SQL query: CREATE TABLE online_status ( username varchar(255) NOT NULL default '', timestamp char(14) NOT NULL default '', ) CREATE INDEX username ON online_status (username); It returns this error. What does this error mean, and where is the error? ___ 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] C++ - sqlite3_extended_result_codes(
I would assume that onoff is either zero or non-zero. Zero turns off the extended codes, non-zero turns them on. The code seems to support that, but I didn't delve too deeply. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Arbol One Sent: Tuesday, July 24, 2012 4:13 PM To: SqLite Subject: [sqlite] C++ - sqlite3_extended_result_codes( I would like to turn on the extended result codes, however, the prototype below does not explain what the value for the second parameter should be. Can anybody help? int sqlite3_extended_result_codes(sqlite3*, int onoff); TIA ___ 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] Not sure how to interrupt this
I think he wants to know why he is receiving what appears to be an error notification via the callback. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita Sent: Thursday, June 28, 2012 9:05 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Not sure how to interrupt this SQLITE DONE is what you get when you successfully run an insert. What is the problem? Adam On Wed, Jun 27, 2012 at 7:02 PM, Jeff Archer jarch...@yahoo.com wrote: I am getting back SQLITE_DONE (101) from sqlite3_step() and the statement is clearly being executed. SQL: INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result); The table has been created as: CREATE TABLE [Scans] (ScanID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,Timestamp DATETIME NOT NULL UNIQUE ,EndTime DATETIME NOT NULL DEFAULT CURRENT_TIME ,Result VARCHAR ); CREATE INDEX Scans_vwScan_Index on Scans(ScanID, Timestamp, EndTime, Result); CREATE INDEX Scans_Timestamp_Index on Scans(Timestamp); I have SQLITE_CONFIG_LOG callback installed: sqlite3_config(SQLITE_CONFIG_LOG, cb_sqlite_config_log, /*pUserData*/NULL); I get the following message through the SQLITE_CONFIG_LOG callback during the sqlite3_step(): errcode: SQLITE_SCHEMA (17) message: statement aborts at 80: [INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result);] database schema has changed SQLITE_VERSION3.7.13 - amalgamation Jeff Archer Nanotronics Imaging jsarc...@nanotronicsimaging.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ 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] Not sure how to interrupt this
How could the schema have changed? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Thursday, June 28, 2012 9:38 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Not sure how to interrupt this Jeff Archer jarch...@yahoo.com wrote: I have SQLITE_CONFIG_LOG callback installed: sqlite3_config(SQLITE_CONFIG_LOG, cb_sqlite_config_log, /*pUserData*/NULL); I get the following message through the SQLITE_CONFIG_LOG callback during the sqlite3_step(): errcode: SQLITE_SCHEMA (17) message: statement aborts at 80: [INSERT INTO [Scans](ScanID, Timestamp, EndTime, Result) VALUES(NULL, @Timestamp, @Timestamp, @Result);] database schema has changed My guess is that a) you have prepared your statement with sqlite3_prepare_v2 (as opposed to sqlite3_prepare), and b) the schema did in fact change between the time the statement was prepared and the time it got executed. In this case, SQLite would internally intercept SQLITE_SCHEMA (but apparently, not before logging it), then finalize, re-prepare and re-execute the statement. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Not sure how to interrupt this
Oh.. you're positing a second party. Ok.. now I'm interested to see if there was one. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Thursday, June 28, 2012 9:55 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Not sure how to interrupt this Marc L. Allen mlal...@outsitenetworks.com wrote: How could the schema have changed? Someone ran CREATE TABLE or VACUUM or similar on the database (possibly via a different connection). -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to build sqlite4 (four)?
Too many SQLite3 apps assume a rowid. But I agree that not having a rowid unless one is defined is the correct thing to do. Darn right they do. I'm relatively new to SQLite, but from what I've seen all across the web, good use of the convenient rowed field is considered a best practice. I'm surprised it's being removed. I think AUTOINCREMENT should imply that the column values a) must be INTEGER, b) tracking the max value seen so far. (b) is tricky because it's tempting to not require an index on that column unless it's constrained to be unique (implied for a primary key), but then, if there is no index then ensuring that an autoincrement value is not used requires a leap of faith -- but again, if not declared unique then I think it's fair to assume that it isn't required to be unique. Why not just keep the maximum value stored in the metadata for the column? It's also possibly a good idea to just not have autoincrement. Let the application implement it, no? After all, it can, including via triggers. Gah! Remove functionality? That also forces the application to maintain the maximum seen, requiring an index or a secondary table. You're just pushing the problem onto the user and I don't think that's the place for it. Marc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite sessions: handling rollbacks
Dr. Hipp responded to an earlier message about this that the session code was fully operational and was only left out of the official release because he took so much flak for trying to include it. I believe that was an answer to a question of why it wasn't rolled into the main release and whether it was being worked on. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, June 13, 2012 5:07 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] sqlite sessions: handling rollbacks On 13 Jun 2012, at 7:57pm, Charles Samuels char...@cariden.com wrote: sqlite's sessions module Can you show us where this appears or is documented ? Session handling is not a part of the SQLite API as defined on http://www.sqlite.org/capi3ref.html 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] 100% CPU utilization sqlite running in VMWare
It shouldn't. It's the same as calling it with NULL, 0, NULL. According to the docs, that should execute fine, even if an error occurs. Now, if NULL != 0 on this system, it's different, but I doubt that's the case. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, June 07, 2012 11:02 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] 100% CPU utilization sqlite running in VMWare On 7 Jun 2012, at 2:24pm, IQuant sql...@iquant.co.cc wrote: Looking through the code I noticed sqlite3_exec(db, zSQL, 0, 0, 0); wrapped inside transaction. Suspect this is causing the CPU load, going to try recoding using prepared statements. There's nothing wrong with sqlite3_exec that will causes excessive CPU usage, but the above usage of it suggests someone put that together quickly. The last three parameters should be more like NULL, 0, zErrMsg showing no callback, a dummy argument for the non-existant callback, and somewhere for SQLite to put an error message if there is one. Even if you burst the _exec() into _prepare(), _step(), _finalize(), you'll still have to provide the equivalent parameters somewhere. I don't know whether using 0, 0, 0 when there is no error will cause any problems. I suspect not. 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] Segmentation Fault when calling sqlite3_finalize
columnNames[i] = malloc(strlen(buffer) + 1);
Need to deal with that pesky '\0'!
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Stephen Wood
Sent: Thursday, May 31, 2012 11:47 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Segmentation Fault when calling sqlite3_finalize
Hello all,
I'm having some memory allocation difficulties while using the SQLite C API.
The goal of the function that is currently giving me issues is just trying to
read the column names from a table. I have been having other memory allocation
faults elsewhere, but I currently have it isolated to this particular section
of code (I say this because I suppose the root problem could be elsewhere in my
code). Also strangely enough, if I do not store the data returned from
sqlite3_column_name, but instead just print it to stdout, I do not get a
segmentation fault, which also leads to me thinking my error may be more
fundamental... This function is also run multiple times and only produces
segmentation faults on the third table I run it on. I am using the database
herehttp://download.codeplex.com/Download/Release?ProjectName=chinookdatabaseDownloadId=167067FileTime=12934269926367Build=18924for
testing.
Please let me know if I can provide any more relevant information.
Any help would be greatly appreciated. Below is the relevant code segment.
Thanks!
char **getColumnNames(char **columnNames, sqlite3 *database, char
*tableName) {
sqlite3_stmt *statement = NULL;
char sqlStatementString[100];
char *sqlErrorMessage = 0;
char buffer[100];
int status = 0;
int i = 0;
int numOfCols = 0;
// Prepare a statement to get the tables from the database
sprintf(sqlStatementString, SELECT * FROM '%s';, tableName);
status = sqlite3_prepare_v2(database, sqlStatementString,
strlen(sqlStatementString), statement, NULL);
if (status != SQLITE_OK) {
fprintf(stderr, Error occured when attempting to prepare an SQL
statement\n
Error:%s\n, sqlErrorMessage);
// Prevents memory leaking from error message:
sqlite3_free(sqlErrorMessage);
exit(1);
} // end error check
// I only want the first row, because I am only getting the names
// for the columns. This is why I only run the step function once
if (sqlite3_step(statement) != SQLITE_ROW) {
fprintf(stderr, Either an error occured or table \%s\ has no
data!\n, tableName);
exit(1);
}
numOfCols = sqlite3_column_count(statement);
if (numOfCols 1) {
perror(No columns in that table!\n);
exit(1);
}
columnNames = (char **) malloc(numOfCols * sizeof(char *));
if (columnNames == NULL) {
perror(Allocation error: );
exit(1);
} // end error check for malloc
for (i = 0; i numOfCols; i++) {
sprintf(buffer, %s, (char *) sqlite3_column_name(statement, i));
columnNames[i] = malloc(strlen(buffer));
if (columnNames[i] == NULL) {
perror(Failed to allocate memory for a column name\n);
exit(1);
} // end malloc error check
strcpy(columnNames[i], buffer);
} // end column loop
// Destroy the statement
// THIS IS WHERE I GET THE SEGMENTATION FAULT **
status = sqlite3_finalize(statement);
if (status != SQLITE_OK) {
fprintf(stderr, Error occured when attempting to destroy a SQL
statement\n
Error:%s\n, sqlErrorMessage);
// Prevents memory leaking from error message:
sqlite3_free(sqlErrorMessage);
exit(1);
} // end error check
return columnNames;
} // end of function
--
Stephen Wood
RMCI, INC.
1525 Perimeter Parkway
Suite 430
Huntsville, AL 35806**
**
___
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] Why can't sqlite disregard unused outer joins?
True, but an optimizer could only intelligently exclude OUTER JOINS in most cases. For instance, if I have a convenience view that combines a series of tables to provide an overall list of something or another: View - SELECT ... FROM a INNER JOIN b ON ... INNER JOIN c ON ... INNER JOIN d ON ... As the schema designer, I might know that all three joins will always, ALWAYS work so that if I use that view and only look at fields in table a, then don't need those joins. However, since SQLite can't really know that, it always has to perform all three joins regardless of which fields are returned. Also, unless you do rewrite the query, how about this view? SELECT ... FROM a LEFT OUTER JOIN b ON a.c1 = b.c1 LEFT OUTER JOIN c ON b.c1 = c.c1 Let's say I only request columns from tables a and c. As it turns out, since I'm using the same linkage columns (c1), the query can be optimized to: SELECT ... FROM a LEFT OUTER JOIN c on a.c1 = c.c1 This was just a simple example. I can only imagine how much work it would take to handle all the possible variations. As this is a lite database, I would much prefer optimization to worry about making the joins I specify as fast as possible and let me worry about the joins in the query. If you don't control how your views are used (as in the Ops example), then the view is truly a convenience function and, as with most conveniences, has a cost. I could, however, imagine a module that compiled precompile views (or even queries) and generate multiple plans based on combinations of desired output fields. Those could then be stored or something for quick lookup. Sort of like preparing and storing the result for later use. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nico Williams Sent: Thursday, May 31, 2012 12:30 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Why can't sqlite disregard unused outer joins? On Thu, May 31, 2012 at 11:25 AM, Jos Groot Lipman donts...@home.nl wrote: Actually, anytime you have VIEWs that join table sources you can benefit from this optimization. No, there is only a benefit if the optimization actually finds a table that can be removed from the join. When you create a VIEW from a JOIN you have no idea if queries on that VIEW will need the JOIN. ___ 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] Why can't sqlite disregard unused outer joins?
Let's say I only request columns from tables a and c. As it turns out, since I'm using the same linkage columns (c1), the query can be optimized to: SELECT ... FROM a LEFT OUTER JOIN c on a.c1 = c.c1 And that's even wrong, since the original query required that a record for table b exist. This makes optimizations even less likely in complex queries. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Marc L. Allen Sent: Thursday, May 31, 2012 1:11 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Why can't sqlite disregard unused outer joins? True, but an optimizer could only intelligently exclude OUTER JOINS in most cases. For instance, if I have a convenience view that combines a series of tables to provide an overall list of something or another: View - SELECT ... FROM a INNER JOIN b ON ... INNER JOIN c ON ... INNER JOIN d ON ... As the schema designer, I might know that all three joins will always, ALWAYS work so that if I use that view and only look at fields in table a, then don't need those joins. However, since SQLite can't really know that, it always has to perform all three joins regardless of which fields are returned. Also, unless you do rewrite the query, how about this view? SELECT ... FROM a LEFT OUTER JOIN b ON a.c1 = b.c1 LEFT OUTER JOIN c ON b.c1 = c.c1 Let's say I only request columns from tables a and c. As it turns out, since I'm using the same linkage columns (c1), the query can be optimized to: SELECT ... FROM a LEFT OUTER JOIN c on a.c1 = c.c1 This was just a simple example. I can only imagine how much work it would take to handle all the possible variations. As this is a lite database, I would much prefer optimization to worry about making the joins I specify as fast as possible and let me worry about the joins in the query. If you don't control how your views are used (as in the Ops example), then the view is truly a convenience function and, as with most conveniences, has a cost. I could, however, imagine a module that compiled precompile views (or even queries) and generate multiple plans based on combinations of desired output fields. Those could then be stored or something for quick lookup. Sort of like preparing and storing the result for later use. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Nico Williams Sent: Thursday, May 31, 2012 12:30 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Why can't sqlite disregard unused outer joins? On Thu, May 31, 2012 at 11:25 AM, Jos Groot Lipman donts...@home.nl wrote: Actually, anytime you have VIEWs that join table sources you can benefit from this optimization. No, there is only a benefit if the optimization actually finds a table that can be removed from the join. When you create a VIEW from a JOIN you have no idea if queries on that VIEW will need the JOIN. ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] The sessions branch
Negative feedback? For what sounds like an optional component? How come? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, May 31, 2012 2:48 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] The sessions branch On Thu, May 31, 2012 at 2:40 PM, Charles Samuels char...@cariden.comwrote: So, I'd like to ask what's wrong with the session extension that it's never been rolled into the main distribution and hasn't been worked on since 2011 July? The sessions branch is stable and fully supported and will continue to be supported. But I got negative feedback from the community when I proposed it, so we've kept it out of 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't sqlite disregard unused outer joins?
So... you're suggesting the optimizer discard a left outer join when: 1) The left outer join table is joined by a unique column AND 2) No other data from the joined table is used in the query. Is that about right? Out of curiosity, why is code being written like the SQL you're providing? Is it automatically generated? How often does this sort of query come up? Marc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why can't sqlite disregard unused outer joins?
Sorry.. I've given up on bottom posting.. it's too much of a pain in Outlook. I see. Wouldn't it be easier to construct a couple of different views and then intelligently decide which view to use based on the user request? You have a specific requirement and you're wanting SQLite to add overhead to every single query it executes. Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Charles Samuels Sent: Wednesday, May 30, 2012 3:14 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Why can't sqlite disregard unused outer joins? On Wednesday, May 30, 2012 12:03:02 e.h. Marc L. Allen wrote: 1) The left outer join table is joined by a unique column AND 2) No other data from the joined table is used in the query. Is that about right? Almost: add recursively: I actually have it nested with *another join* with the same features, both of which can safely be discarded. And the unique column is actually a composite unique constraint. Out of curiosity, why is code being written like the SQL you're providing? Is it automatically generated? It this case, it actually is automatically generated only to keep me from having to redundantly write the join over and over again. A. I have a table of Things. B. I have a table mapping those Things to object IDs in another datastore. C. I have a virtual table module exposing that other datastore. And I have a left outer join between A and B and then another between B and C. And then I put all of those in one giant (almost: only about 15 columns) view that the user can do arbitrary queries on, most of which only touch one or two of those columns from C, and many of which touch zero. There are multiple Bs per A, but one C per B. How often does this sort of query come up? Right now, I want to make this the primary interface to my application, but the queries mostly come from users, so one every few seconds. Charles ___ 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] Why can't sqlite disregard unused outer joins?
All requirements are specific :) How do you pick at what point that overhead is too much? When the overhead outweighs the benefit.If, for example, you were the only person who ever needed that particular optimization, I would suggest that the overhead is too much. So, the user provides a standard SQL query using your composite view? Or are they providing some other construct that you convert into a query using your view? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What does The use of the amalgamation is recommended for all applications. mean, precisely?
That page appears to specifically be in regards to compiling SQLite from sources. It means, don't use the individual files, but use the amalgamation because it's a lot simpler to deal with. How you compile it, or in what form the compiled object is used is not mentioned. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sidney Cadot Sent: Thursday, May 24, 2012 1:56 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] What does The use of the amalgamation is recommended for all applications. mean, precisely? Why risk ending up with an unexpected (possibly old) version by linking at runtime just to save users less than 300K of disk space? But that's an argument against shared linking in general. I am just curious what idea this particular statement on this particular help-page (specific to SQLite) is trying to convey. ___ 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] sqlite Commit C API
Funny!
But, very inefficient. Suggest:
#define fsync(x)
Marc
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Simon Slavin
Sent: Thursday, May 17, 2012 11:52 AM
To: j...@kreibi.ch; General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite Commit C API
On 17 May 2012, at 4:49pm, Jay A. Kreibich j...@kreibi.ch wrote:
I wouldn't be all that shocked to find out an embedded system has an
fsync() call that looks like this:
int fsync( int fd ) { return 0; }
We are best software circle ! Our programmers write many functions a day !
Our functions run fastest ! Our functions never give errors ! Buy us products
now !
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] Bug: Increment unique integer field
Apparently, the update is done a row at a time. Whether a row is deleted depends on whether the row being updated clashes with a current row when adding one. Sometimes it will, sometimes it won't. 1, 2, 3 If the rows are updated (3, 2, 1) it all works. 3 - 4 2 - 3 1 - 2 If the rows are updated (1, 2, 3) one row gets deleted. 1 - 2 2 - (deleted) 3 - 4 Marc -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Petite Abeille Sent: Monday, May 07, 2012 9:06 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Bug: Increment unique integer field On May 7, 2012, at 2:48 PM, Igor Tandetnik wrote: begin; update numbers set num = -num; update numbers set num = -num + 1; end; Right... now... out of curiosity... what happen when you do the following: update or replace numbers set num=num+1; Note the 'or replace'. Did that 'update or replace' just managed to delete a row or two?!??!? Bug or feature? ___ 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] Bug: Increment unique integer field
I don't see the issue with that. Unless you want it to fail anyhow? You have a unique key. You execute an update that sets all rows to have the same unique key. Using UPDATE OR REPLACE implies that you want SQLite to do the right thing, which is end up with a single row. Do you see the 'right thing' as being different? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Petite Abeille Sent: Monday, May 07, 2012 10:05 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Bug: Increment unique integer field On May 7, 2012, at 3:44 PM, Pavel Ivanov wrote: It's a well documented feature: or replace means if you see constraint violations while updating please delete all violating rows and then proceed with update Yep, I'm familiar with the ON CONFLICT clause. Or I thought I was as I haven't fully internalize its dramatic implication in relation to update statements. For example, delete all your rows but one with one easy update statement: sqlite create table numbers(num int unique ); insert into numbers( num sqlite ) values( 1 ); insert into numbers( num ) values( 2 ); insert sqlite into numbers( num ) values( 3 ); insert into numbers( num ) sqlite values( 4 ); insert into numbers( num ) values( 5 ); select sqlite count( * ) from numbers; 5 sqlite update or replace numbers set num = 1; select count( * ) from sqlite numbers; 1 Oh, well... caveat emptor... ___ 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] Details on New Features
The last one it saw. It's not deterministic. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Ralf Junker Sent: Friday, May 04, 2012 5:01 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Details on New Features On 04.05.2012 16:39, Richard Hipp wrote: If a single min() or max() aggregate function appears in a query, then any other columns that are not contained within aggregate functions and that are not elements of the GROUP BY will take values from one of the same rows that satisfied the one min() or max() aggregate function. Given that more than one row satisfies the one min() or max() aggregate function (think of multiple, identical smallest or largest values). Which row will SQLite pick? Ralf ___ 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] Data Import Techniques
Did you try wrapping all your INSERT statements into a single transaction? BEGIN TRANSACTION INSERT... INSERT... ... COMMIT -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Nigel Verity Sent: Wednesday, May 02, 2012 12:05 PM To: sqlite-users@sqlite.org Subject: [sqlite] Data Import Techniques Hi I am writing an application which requires approximately 50,000 items to be imported from a text file into a table. Each item is a single string of 8 characters, and the target table has an auto-incrementing PK and one other field, to hold the 8 character string. Using the Import Table Data function in SQLiteman, the data loads very quickly. However in my application, using either an SQL insert command or a resultset, the import is very much slower. Is there another technique I can use to speed things up? Thanks Nige ___ 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] Re Query planner creating a slow plan
I suppose using a temporary table is out of the question? But, then again, that only solves the specific issue. I guess the more general question is how views with unions interact with aggregates and order by. What happens if you don't use the view, but perform the query using the actual tables? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: Thursday, April 26, 2012 10:11 AM To: General Discussion of SQLite Database Subject: [sqlite] Re Query planner creating a slow plan What happens if you use a subselect? selsect transfer_date from (select transfer_date from transfer_history where regn_no='039540' and transfer_date = '2012-05-01') order by transfer_date; Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Peter [pe...@somborneshetlands.co.uk] Sent: Thursday, April 26, 2012 7:00 AM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Query planner creating a slow plan Hi, I have a view 'transfer_history' which aggregates records from 3 tables using UNION ALL. the aggregate is about 102k records. I have a query: SELECT transfer_date from transfer_history WHERE regn_no = '039540' and transfer_date = '2012-05-01' This returns three records and takes a couple of milliseconds - good. But if I add an 'order by' clause, or an aggregate (max) on transfer_date, the time goes up to 300ms. The reason seems to be that the query planner uses scans for all three sub-queries instead of using indexes on the underlying tables. With the basic query yhe QP says; SEARCH TABLE transfer AS tr USING INDEX sqlite_autoindex_transfer_1 (regn_no=? AND transfer_date?) (~1 rows) SEARCH TABLE sheep AS s USING INDEX sqlite_autoindex_sheep_1 (regn_no=?) (~1 rows) COMPOUND SUBQUERIES 2 AND 3 (UNION ALL) SEARCH TABLE sheep AS s USING INDEX sqlite_autoindex_sheep_1 (regn_no=?) (~1 rows) COMPOUND SUBQUERIES 1 AND 4 (UNION ALL) With the 'order by' clause the QP says: SCAN TABLE transfer AS tr (~49043 rows) SCAN TABLE sheep AS s (~51858 rows) COMPOUND SUBQUERIES 3 AND 4 (UNION ALL) SCAN TABLE sheep AS s (~25929 rows) COMPOUND SUBQUERIES 2 AND 5 (UNION ALL) SCAN SUBQUERY 1 AS t2 (~4227 rows) USE TEMP B-TREE FOR ORDER BY Seems to me it ought to be able to just sort the result of the first plan. ATM it's an order of magnitude quicker at least to do the sort in Python in the application. Pete -- Peter Hardman ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Re Query planner creating a slow plan
What indexes are on the underlying tables? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Peter Sent: Thursday, April 26, 2012 10:55 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Re Query planner creating a slow plan Jim Morris wrote, On 26/04/12 15:36: It is possible using an alias would force better behavior: selsect theDate from (select transfer_date as theDate from transfer_history where regn_no='039540' and transfer_date= '2012-05-01') order by theDate Once again, it makes no difference - the planner still picks the same plan using scans. -- Peter Hardman ___ 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] Re Query planner creating a slow plan
Out of curiosity, try... SELECT transfer_date, regn_no FROM transfer_history_new WHERE regn_no = '039540' and transfer_date = '2012-05-01' order by transfer_date asc Is the problem that combining the order by with having transfer_date as the only returned item make it use the transfer_date index instead of the preferable regn_no index? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Peter Sent: Thursday, April 26, 2012 11:24 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Re Query planner creating a slow plan Marc L. Allen wrote, On 26/04/12 15:57: What indexes are on the underlying tables? There are indexes on all the fields used in the tables of the transfer_history view. While tinkering I have discovered something: If instead of SELECT transfer_date FROM transfer_history_new WHERE regn_no = '039540' and transfer_date = '2012-05-01' order by transfer_date asc I write SELECT * FROM transfer_history_new WHERE regn_no = '039540' and transfer_date = '2012-05-01' order by transfer_date asc then I get an execution time of a couple of milliseconds instead of 300ms or so (times from Sqliteman this time). The planner has reverted to using indexes instead of scans... -- Peter Hardman ___ 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] DEFAULT BOOLEAN NOT NULL not working with entityframework
Found this article and thought of this thread. http://www.drdobbs.com/architecture-and-design/232900836?cid=DDJ_nl_mdev_2012-04-24_helq=d53b813fc9704062bbe2f4d6d6921a9e -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Black, Michael (IS) Sent: Friday, April 20, 2012 6:43 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] DEFAULT BOOLEAN NOT NULL not working with entityframework I should have said everybody (and I meant the languages and silicon) is effectively checking != 0 for true. And the !!condition is a mathematical theorem of why -1 is not a good value to use for truenot that you see that very often (if at all). What you do see is if (!condition == true) which will fail when true is -1 and condition is false. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Nico Williams [n...@cryptonector.com] Sent: Thursday, April 19, 2012 4:47 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] DEFAULT BOOLEAN NOT NULL not working with entityframework On Thu, Apr 19, 2012 at 3:51 PM, Black, Michael (IS) michael.bla...@ngc.com wrote: You are correct that non-zero will be treated as true. That's more an accident then anything else as everybody is checking != 0 for true. No, C treats any non-zero value as true, as in if (condition) ... -- that condition will considered true if it's non-zero. If you ever see !!condition, that's just to normalize true values. Nico -- ___ 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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] DEFAULT BOOLEAN NOT NULL not working with entityframework
Oh, nevermind I see what you're saying.
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Thursday, April 19, 2012 3:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] DEFAULT BOOLEAN NOT NULL not working with
entityframework
Problem with true=-1.
!false=1
So !false == true fails which can bite you in the behind
unexpectedly
true= 1
!true=0
true=-1
!true=0
!false=1
(true= 1 == !false) = 1
(true=-1 == !false) = 0
#include stdio.h
main()
{
int true1=1;
int true2=-1;
int false=0;
printf(true= 1\n!true=%d\n,!true1);
printf(true=-1\n!true=%d\n,!true2);
printf(!false=%d\n,!false);
printf((true= 1 == !false) = %d\n,true1==!false);
printf((true=-1 == !false) = %d\n,true2==!false); }
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
That is interesting to know, I tend to use -1 as true (misspent youth
dabbling in Forth on 8 bit machines).
___
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] DEFAULT BOOLEAN NOT NULL not working with entityframework
I was under the impression that, in C, 0 was false, non-zero was true.
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Thursday, April 19, 2012 3:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] DEFAULT BOOLEAN NOT NULL not working with
entityframework
Problem with true=-1.
!false=1
So !false == true fails which can bite you in the behind
unexpectedly
true= 1
!true=0
true=-1
!true=0
!false=1
(true= 1 == !false) = 1
(true=-1 == !false) = 0
#include stdio.h
main()
{
int true1=1;
int true2=-1;
int false=0;
printf(true= 1\n!true=%d\n,!true1);
printf(true=-1\n!true=%d\n,!true2);
printf(!false=%d\n,!false);
printf((true= 1 == !false) = %d\n,true1==!false);
printf((true=-1 == !false) = %d\n,true2==!false); }
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
That is interesting to know, I tend to use -1 as true (misspent youth
dabbling in Forth on 8 bit machines).
___
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] DEFAULT BOOLEAN NOT NULL not working with entityframework
Yeah. I realized that, but my messages got reversed.
Thanks.
On Apr 19, 2012, at 4:51 PM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
You are correct that non-zero will be treated as true.
That's more an accident then anything else as everybody is checking != 0
for true.
But the values need to be consistent in the not cases too if you want to be
safe about it. As I demonstrated in the code.
You want this to hold true, otherwise you'll get into trouble.
!!a == a
#include stdio.h
main()
{
int true1 = 1;
int true2 = -1;
printf(true = 1 OK? %s\n,(!!true1==true1)?yes:no);
printf(true =-1 OK? %s\n,(!!true2==true2)?yes:no);
}
true = 1 OK? yes
true =-1 OK? no
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Marc L. Allen [mlal...@outsitenetworks.com]
Sent: Thursday, April 19, 2012 2:56 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] DEFAULT BOOLEAN NOT NULL not working with
entityframework
I was under the impression that, in C, 0 was false, non-zero was true.
-Original Message-
From: sqlite-users-boun...@sqlite.org [thismessage:/mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Thursday, April 19, 2012 3:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] DEFAULT BOOLEAN NOT NULL not working with
entityframework
Problem with true=-1.
!false=1
So !false == true fails which can bite you in the behind
unexpectedly
true= 1
!true=0
true=-1
!true=0
!false=1
(true= 1 == !false) = 1
(true=-1 == !false) = 0
#include stdio.h
main()
{
int true1=1;
int true2=-1;
int false=0;
printf(true= 1\n!true=%d\n,!true1);
printf(true=-1\n!true=%d\n,!true2);
printf(!false=%d\n,!false);
printf((true= 1 == !false) = %d\n,true1==!false);
printf((true=-1 == !false) = %d\n,true2==!false); }
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
That is interesting to know, I tend to use -1 as true (misspent youth
dabbling in Forth on 8 bit machines).
___
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
___
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] Why are two select statements 2000 times faster than one?
Maybe the query analyzer isn't smart enough to do two seeks in this case, so it does a scan? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Steinar Midtskogen Sent: Friday, April 13, 2012 3:00 PM To: sqlite-users@sqlite.org Subject: [sqlite] Why are two select statements 2000 times faster than one? Hello, I have a table with unix_time as primary key and I want to get the minimum and maximum values of unix_time. When I do: SELECT min(unix_time), max(unix_time) from table; it is very slow. It takes about 250ms, nearly everything in the step() call. However, if I do: SELECT min(unix_time) FROM table; SELECT max(unix_time) FROM table; to get the same values, it takes a fraction of the time. The speedup is more than 2000x. Why? -- Steinar ___ 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] select null values in python
select * from tablename where field1 IS Null -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Fabio Spadaro Sent: Thursday, March 22, 2012 11:33 AM To: General Discussion of SQLite Database Subject: [sqlite] select null values in python Using sqlite3 python and I have a problem running a query. My table has null values. Now if I do a 'select * from tablename' returns to me as the result: [(datetime.date (2012, 3, 22), buffer ptr read-write 0x03774B90, 0x03774B58 at size 0, None, None, None, None, None, None)] As you can see there are null values that in python are None type. If I want to run a query like select * from tablename where field1 = Null returns no results and even if I run select * from tablename where field1 = None None because there is not in Sqlite. You should use an adapter or something? -- Fabio Spadaro Try Sqlite Root a GUI Admin Tools for manage Sqlite Database: www.sqliteroot.com ___ 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] select null values in python
I did show this very example, except lacking the whitespace in front and differing in capitalization. I assume you feel those distinct characteristics render your example more interesting than mine. Or mine, which was sent minutes before Igor's. Hmph. ;) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Korean SQLite users needs help
Suggest he obtain a gmail account and send from there? -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, March 15, 2012 8:18 AM To: General Discussion of SQLite Database Subject: [sqlite] Korean SQLite users needs help User 영양갱라인 ky_love...@hanmail.net writes that he is unable to contact this mailing list because his email user agent is not sending the emails in a format that our mailing list software (Mailman) can understand. If anybody on this list can help him out, please do so. If you can speak to him in Korean, all the better. His English is actually pretty good and I think we could probably help him if he could figure out how to get emails to this mailing list. But my efforts at getting him on this list have been in vain. Write to him directly if you think you can help. Thanks! -- 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
