RE: [sqlite] CROSS keyword disables certain join optimizations
--- "Mrs. Brisby" <[EMAIL PROTECTED]> wrote: > That all said, it seems like this problem is already solved- SQLite does > the right thing after ANALYZE is called. Perhaps it wouldn't be too > difficult to update the statistics that ANALYZE collects in after > COMMIT, or perhaps after a COMMIT that runs longer than a particular > amount of time. > > This would obsolete the need for a control, and as I've said, controls > are bad. I do agree that controls generally suck. But we live in an imperfect world, and sometimes we know better than the tools we are using and have to intervene and steer them in a better direction. The auto ANALYZE might work fine on small databases, but not all of us would want to incur the cost of using ANALYZE when it is not required. (I could tell you a related story about Sun Java engineers who thought mutex locking all API accesses to collections classes in Java 1.0 was a brilliant idea. This mis-feature turned out to be brilliantly stupid when people realized that the locking had to be done at a higer level - in the application level - in order for it to be useful. All the automatic locking served to do was slow down the general use of the class, but provided little or no benefit). The Sqlite user now has the choice to go with the Cadillac approach (ANALYZE) - a smooth ride, not always so good on gas, but very comfortable. Or the user may choose to drive with a manual transmission (CROSS JOIN) and obtain better and more predictable control of the vehicle. Choice is a good thing. On a related topic, it seems that Postgres 8.x has added a pragma "join_collapse_limit" to the CROSS JOIN mix to deal with this manual join order thing: http://www.postgresql.org/docs/current/interactive/explicit-joins.html SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...; With join_collapse_limit = 1, this forces the planner to join A to B before joining them to other tables... Even more controls... __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Re: Thread safety guarantees
On Sep 4, 2005, at 5:53 AM, Igor Tandetnik wrote: Christopher R. Palmer wrote: Unfortunately, that's not the case. The code that was being used when I created ticket 1272 was very simple. In the main thread, I opened a handle for each thread. Each thread then worked independently using only their own handle. This broke the locking because the low-level Linux file locks are tied to the thread that created them. That is, the thread that opened the handle.. So SQLite locks the DB file immediately after opening a connection in sqlite_open? If all activity on the connection must be restricted to a single thread, how is one to properly use sqlite3_interrupt? Hi Igor, I hope someone will correct me if I'm wrong, but it looks like calling sqlite3_interrupt from another thread is okay-ish. It only checks the sqlite3 struct's magic value to make sure it's legitimate (closed, open, or busy), and then sets the SQLITE_Interrupt bit on the flags field. I don't know if bit set and clear are atomic or not; if not, and two threads happen to write into the flags field at the same time, bad things can happen. I just fixed my code to work with the new thread restrictions and haven't had any problems calling sqlite3_interrupt from the UI thread so far. -D
Re: [sqlite] CROSS keyword disables certain join optimizations
> People who visit a website that looks like junk _NEVER_ say "oh my web > browser is being a piece of shit." ...unless they're the same people who designed it ;)
RE: [sqlite] CROSS keyword disables certain join optimizations
On Mon, 2005-09-12 at 07:15 -0400, Ned Batchelder wrote: > I'm not sure if we agree or disagree. You say: > > > I don't think the number of optimizer controls should grow. > > It should be kept as close to zero as possible. > > But then you say: > > > If [the optimizer] can make a mistake, I want a simple > > and _unobtrusive_ way to correct _just_that_mistake_ for > > _just_that_query_. > > As the optimizer grows, the number of mistakes it could make will grow. You > seem to want a control for each mistake. The number of controls will grow. I don't want a control. A control is a conscious device that I want no part of. Perhaps swapping the order of entries, or performing some bogus event, etc. These are things that the SQLite list can mention, that can always be considered SQLite oddities, but that users shouldn't be concerned with trying "on their own". These controls _are_bugs_. Period. Comments and pragmas have this nasty tendency to stick around longer than necessary. I realize that if the Pg people have decided _this_particular_ control is necessary- that is, the bug cannot be fixed, then pragmas are not the way to go, but instead SQL itself needs to be fixed. I like, however, to follow the conventions set up by others, so I think CROSS isn't such a bad idea as a result. That all said, it seems like this problem is already solved- SQLite does the right thing after ANALYZE is called. Perhaps it wouldn't be too difficult to update the statistics that ANALYZE collects in after COMMIT, or perhaps after a COMMIT that runs longer than a particular amount of time. This would obsolete the need for a control, and as I've said, controls are bad. > As a few of us have mentioned, other database engines have used inline > pragmas in the form of specially formatted comments: Other database engines are wrong so...? Other database engines support [] for attribute boundaries or backslash as an escaping character. I suppose these should be used too? After all, they solve a very specific non-problem at a horrible expense to the user, so they must be good right? Note what I am saying: _optimizer_ controls are bad. They make the user miserable because they lead the user to believe that their SQL is invalid or incorrect instead of Pg or DB2 or MSSQL or MySQL or SQLite being incapable. gcc -O2 makes faster code as long as it's not smaller. Why isn't it default? find -noleaf on the converse works around a bug in the kernel. Why not fix the kernel? -noleaf should do NOTHING on current systems. Why do I need to intermix -O0 on some non-speed sensitive code because of gcc bugs, and why should I the programmer bother? I am much more willing to accept and workaround the failures of gcc, some unixes, and SQLite than I am to have by own intelligence insulted by a program I know very little about. ... after all, if I try a new program and it tells me I performed some kind of syntax invocation error, I tend to believe it. I think _I_ must have done something wrong. Controls simply reinforce this mind-think. > select /* sqlite_stupid */ A.parent, A.child from /* sqlite_stupid */ > revision_ancestry ... > > New keywords will also work. The comments have the advantage that they are > "portable": you can pass this exact query to any database. Only SQLite will > interpret the comments. If SQLite invents a new STUPID keyword (or > SQLITE_STUPID), the query will only be parsable by SQLite. Agreed. That's exactly why I recommend them over code-hiding. Code-hiding has the distinct advantage of keeping bugs around. People love the fact that they can hide CSS from MSIE so that they can pretend to have fully conforming XHTML1.1+CSS web sites that look absolutely stunning in some latest version of Firefox, but they don't change the fact that MSIE had a bug in it. People who visit a website that looks like junk _NEVER_ say "oh my web browser is being a piece of shit." Instead, by forcing these bugs to be deliberate and visible, we prevent people from deploying broken code to broken systems, but STILL allow people who "have no other option" to proceed with the full knowledge that a future SQLite release will require they change their code. > Granted, using pragma comments in queries is unlikely in a system where > queries are fed to multiple DBMS's, but at least it would be possible. Which brings me back to PostgreSQL - which was commented as not having a special pragma for this but instead a sly little hack identical to the one SQLite is using. People who target PostgreSQL will already have had the fix. > -Original Message- > From: Mrs. Brisby [mailto:[EMAIL PROTECTED] > Sent: Sunday, 11 September, 2005 10:07 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] CROSS keyword disables certain join optimizations > > On Sat, 2005-09-10 at 21:38 -0400, Ned Batchelder wrote: > > Rather than overload an existing SQL keyword, would it be possible to > > provide pragmas to control the optimizer?
Re: [sqlite] SQL assistance with selecting the last n records inserted
> and so it does. Gracias. ;-) > > Now I am off happily experimenting with applying other functions to > ORDER BY before ordering them. Thanks Derrell. You've used your super powered intellect well today! ;) --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
Re: [sqlite] SQL assistance with selecting the last n records inserted
On Sep 12, 2005, at 8:54 AM, [EMAIL PROTECTED] wrote: Jay Sprenkle <[EMAIL PROTECTED]> writes: If you just want 10 records with the highest modified or created time I think this will do it: select * from tbl order by max(created_on,modified_on) desc limit 10 except that if modified_on is null, you won't get that record. This variation should fix that problem: SELECT * FROM tbl ORDER BY MAX(created_on, COALESCE(modified_on, 0)) DESC LIMIT 10; and so it does. Gracias. ;-) Now I am off happily experimenting with applying other functions to ORDER BY before ordering them. -- Puneet Kishor
Re: [sqlite] SQL assistance with selecting the last n records inserted
> > > > If you just want 10 records with the highest modified or created time I > > think this will do it: > > > > select * > > from tbl > > order by max(created_on,modified_on) desc > > I didn't realize MAX could be used in ORDER BY. > > However, the above does not work. It seems to overlook all the records > which have a created_on date but no modified_on date. For example, even > the most latest record created but not yet modified doesn't show up in > the above query result. It seems to pull only those records which have > a valid value for both created_on and modified_on columns. Oh. Sorry, forgot about NULLS :( You could either wrap the modified_on field with a function to return the creation date if it's null, or assign a modified date to every record equal to the create date when the record is created.
Re: [sqlite] SQL assistance with selecting the last n records inserted
Jay Sprenkle <[EMAIL PROTECTED]> writes: > If you just want 10 records with the highest modified or created time I > think this will do it: > > select * > from tbl > order by max(created_on,modified_on) desc > limit 10 except that if modified_on is null, you won't get that record. This variation should fix that problem: SELECT * FROM tbl ORDER BY MAX(created_on, COALESCE(modified_on, 0)) DESC LIMIT 10; Derrell
Re: [sqlite] SQL assistance with selecting the last n records inserted
On Sep 12, 2005, at 8:43 AM, Jay Sprenkle wrote: On 9/12/05, Puneet Kishor <[EMAIL PROTECTED]> wrote: my table is name (VARCHAR), created_on (DATETIME DEFAULT CURRENT_TIMESTAMP), modified_on (DATETIME) When a new record is created, it gets a value in the created_on col, which is then not changed subsequently, but the modified_on col is empty. Whenever the record is updated, the modified_on col is changed. --I want the last 10 records If you just want 10 records with the highest modified or created time I think this will do it: select * from tbl order by max(created_on,modified_on) desc I didn't realize MAX could be used in ORDER BY. However, the above does not work. It seems to overlook all the records which have a created_on date but no modified_on date. For example, even the most latest record created but not yet modified doesn't show up in the above query result. It seems to pull only those records which have a valid value for both created_on and modified_on columns. -- Puneet Kishor
Re: [sqlite] SQL assistance with selecting the last n records inserted
On 9/12/05, Puneet Kishor <[EMAIL PROTECTED]> wrote: > > my table is > > name (VARCHAR), created_on (DATETIME DEFAULT CURRENT_TIMESTAMP), > modified_on (DATETIME) > > When a new record is created, it gets a value in the created_on col, > which is then not changed subsequently, but the modified_on col is > empty. Whenever the record is updated, the modified_on col is changed. > > --I want the last 10 records If you just want 10 records with the highest modified or created time I think this will do it: select * from tbl order by max(created_on,modified_on) desc limit 10 --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
[sqlite] SQL assistance with selecting the last n records inserted
my table is name (VARCHAR), created_on (DATETIME DEFAULT CURRENT_TIMESTAMP), modified_on (DATETIME) When a new record is created, it gets a value in the created_on col, which is then not changed subsequently, but the modified_on col is empty. Whenever the record is updated, the modified_on col is changed. --I want the last 10 records --query: SELECT name, created_on, modified_on FROM ( --created --sub-query1: SELECT name, created_on, modified_on FROM table ORDER BY created_on DESC LIMIT 0, 10 --but, also include UNION --records in the table modified later than any of the records found in subquery1 --sub-query2: SELECT name, created_on, modified_on FROM table ORDER BY modified_on DESC LIMIT 0, 10 ) ORDER BY modified_on DESC LIMIT 0, 10 Well, besides the fact that the above attempt is clumsy, it doesn't work... SQLite scolds me that "ORDER BY should come after UNION" except if I do that I won't get the last 10 records in each sub-query. There must be a better way to query, or perhaps, even modding my table structure to better accomplish such a query. Any suggestions? -- Puneet Kishor
Re: [sqlite] sqlite.org organization...
Jay Siegel wrote: For example, using google I accidentally found http://www.sqlite.org/php2004/slides-all.html which is Yeah, I found this also accidentally while browsing the older news section(because I use 2.8.16), and found it immediately very interesting. It deserves to be on a better spot :) regards, Tom,
[sqlite] sqlite.org organization...
I'm still pretty new to sqlite but have written a few test programs over the last month and gotten everthing working well. For small-to-medium sized tables, the performance is great - this is a wonderful tool to use for large and complex application files, especially ones where there is variable sized blocks of data burried within the structure. I'm really happy. I'm about to start porting the code to an embedded platform and remember seeing some information about porting issues somewhere but can't find it again. This seems to happen a lot with sqlite.org. I'm not complaining, honest, but I'm wondering if I'm missing some organization tool to the web site that will allow me to find things. For example, using google I accidentally found http://www.sqlite.org/php2004/slides-all.html which is a wonderful set of slides about sqlite. I don't think that this page is available as a link from any of the top links on the sqlite.org web pages. Am I missing something? Is there a way to get a directory of everything that is out there? I tried using ftp on sqlite.org but couldn't access it. Are there any more hints, ideas, secrets to porting this to another C environment other than the page of "Compilation Options" in the Documentation section? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[sqlite] sqlite3_errCode and error handling
when a sqlite3_Step call fails, say on a unique key violation of an INSERT statement, the returned error code is SQLITE_ERROR, which is documented. calling sqlite3_errcode at this point, however, also gives the same error, which is not. the correct error, which is SQLITE_CONSTRAINT, is only returned when calling sqlite3_finalize, which may happen at a much later stage in the code. I believe the error code should be returned right at the point where the error happens, because if it is not, the structure of the code suffers, since the finalization code for a statement needs to be modal.
RE: [sqlite] CROSS keyword disables certain join optimizations
I'm not sure if we agree or disagree. You say: > I don't think the number of optimizer controls should grow. > It should be kept as close to zero as possible. But then you say: > If [the optimizer] can make a mistake, I want a simple > and _unobtrusive_ way to correct _just_that_mistake_ for > _just_that_query_. As the optimizer grows, the number of mistakes it could make will grow. You seem to want a control for each mistake. The number of controls will grow. As a few of us have mentioned, other database engines have used inline pragmas in the form of specially formatted comments: select /* sqlite_stupid */ A.parent, A.child from /* sqlite_stupid */ revision_ancestry ... New keywords will also work. The comments have the advantage that they are "portable": you can pass this exact query to any database. Only SQLite will interpret the comments. If SQLite invents a new STUPID keyword (or SQLITE_STUPID), the query will only be parsable by SQLite. Granted, using pragma comments in queries is unlikely in a system where queries are fed to multiple DBMS's, but at least it would be possible. --Ned. http://nedbatchelder.com -Original Message- From: Mrs. Brisby [mailto:[EMAIL PROTECTED] Sent: Sunday, 11 September, 2005 10:07 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] CROSS keyword disables certain join optimizations On Sat, 2005-09-10 at 21:38 -0400, Ned Batchelder wrote: > Rather than overload an existing SQL keyword, would it be possible to > provide pragmas to control the optimizer? Assigning meanings to particular > combinations of SQL queries won't scale as the number of optimizer controls > grows. I don't think the number of optimizer controls should grow. It should be kept as close to zero as possible. The query optimizer isn't (and shouldn't be) designed to help out people who don't know SQL, but should be for people who know SQL but don't know SQLite. When SQLite does this optimization of table reorganization, it's doing so because it doesn't have a real optimal way to answer the query-as-stated but if the query is transformed into something seemingly (and sometimes algebraically) equivalent, then it does so it can. Consider a mythical SQL engine that doesn't reorder where terms before searching an index of the form "a,b". It can answer queries like: WHERE a='abc' AND b='def'; but give it: WHERE b='def' AND a='abc'; and it could take forever. Anyone reasonably aware of the various SQL engines out there would undoubtedly be aware of engines that DO treat them as identical, but here is one that isn't. Given that this is indeed a _query_optimization_ do you really want a control for this? While I'm sure many SQL engines don't put this nonsense in the block of code that looks for query optimizations, I want the query optimizer to do the best job that it can. If it can make a mistake, I want a simple and _unobtrusive_ way to correct _just_that_mistake_ for _just_that_query_. I think anyone close to the various SQL working groups-that-be should consider a "STUPID" operator that takes the next term (whatever that might mean to the SQL engine) and stops any query optimization on that term. The STUPID keyword would be allowed everywhere. It'll improve the readability of SQL as it is: SELECT STUPID A.parent, STUPID A.child STUPID FROM STUPID revision_ancestry STUPID AS STUPID A ... I don't like CROSS, but it is the closest thing to what I might find acceptable. :)
Re: [sqlite] SQLITE Documentation
On 9/12/05, ShepherdHill DB Subscriptions <[EMAIL PROTECTED]> wrote: > Hi, > > Can anyone point me to how I can get the full download of the SQLITE > documentation in html? I will like to compile and maintain the CHM > version of the document so that users can reference the documentation > offline. > Dunno, I never found it. Might be the SQLite official team have it. Currently I downloaded by myself all documentation, faqs and syntax pages from sqlite.org, then fix the links and now it available online on my box. > Best regards. > > Chris. > -- Firman Wandayandi Never Dreamt Before (http://php.hm/~firman/)
[sqlite] SQLITE Documentation
Hi, Can anyone point me to how I can get the full download of the SQLITE documentation in html? I will like to compile and maintain the CHM version of the document so that users can reference the documentation offline. Best regards. Chris.
