Re: [sqlite] SQlite Memory Leakage with 65536 Page Size

On 28/10/2013 3:57 PM, Richard Hipp wrote: On Mon, Oct 28, 2013 at 3:52 PM, Raheel Gupta wrote: Sir, is there any way to not allow malloc to hold memory ? I mean shouldnt free(), be freeing the memory ? You'lll need to speak with the developers of your libc

Re: [sqlite] Row Level Locking as in InnoDB

On 08/11/2013 5:07 AM, Raheel Gupta wrote: No. It's not even feature-frozen yet, as far as we know. And whenever it is, it's incredibly unlikely to have row level locking. Please add row-level locking if possible. I can't think of any other single feature that would remove the "lite" from

Re: [sqlite] Newbie question, how multiple joins are executed

On 19/06/2011 8:03 PM, Simon Slavin wrote: > On 20 Jun 2011, at 12:58am, Lucas Cotta wrote: > >> I understand that for a query with a two tables join, SQLite will do a >> nested loop join with these two tables. >> But what about a query joining 5 tables? >> It would be like this?: >> >> for(table1

Re: [sqlite] Newbie question, how multiple joins are executed

On 20/06/2011 6:59 AM, Richard Hipp wrote: > On Mon, Jun 20, 2011 at 6:55 AM, Ryan Johnson<ryanj...@ece.cmu.edu> wrote: >> IIRC sqlite does *not* do any join ordering optimizations and simply >> runs them in whatever order the query specifies. This can have >> unf

[sqlite] Problem importing integers from csv

Hi all, I'm a new user to sqlite (I just compiled sqlite-autoconf-3070400 on cygwin), and am running into what seems to be a bad interaction between type affinity and integer comparisons. I'm importing a csv file full of single-digit integer values into a table which .schema reports as

Re: [sqlite] Problem importing integers from csv

On 12/9/2010 4:10 PM, Simon Slavin wrote: > On 9 Dec 2010, at 11:04pm, Ryan Johnson wrote >> I'm a new user to sqlite (I just compiled sqlite-autoconf-3070400 on >> ), and am running into what seems to be a bad interaction between >> type affinity and integer comparisons

Re: [sqlite] Problem importing integers from csv

On 12/9/2010 4:56 PM, Igor Tandetnik wrote: > On 12/9/2010 6:42 PM, Ryan Johnson wrote: >> create table a(x,y INTEGER); > This creates a table with column x having no affinity, and column y > having integer affinity. Is this what you meant? Doh! That explains why I couldn't

Re: [sqlite] Problem importing integers from csv

On 12/9/2010 6:46 PM, Simon Slavin wrote: > On 10 Dec 2010, at 12:20am, Ryan Johnson wrote: > >> On 12/9/2010 4:56 PM, Igor Tandetnik wrote: >>> On 12/9/2010 6:42 PM, Ryan Johnson wrote: >>>> create table a(x,y INTEGER); >>> This creates a table with

Re: [sqlite] How to write Query to do this?

On 12/13/2010 11:17 AM, Stefano Mtangoo wrote: > Thanks Dr. and I'm checking the zip file. But to be frank, I don't > understand the calculation done below. How do I come to such claculation > (excuse my ignorance)? also how do I query that simple BETWEEN? > That seems to be easiest way but I

Re: [sqlite] Find first non-NULL values of several columns

On 19/10/2012 8:55 AM, Igor Tandetnik wrote: Steinar Midtskogen wrote: Ok, so let's say the table v (with "a" as the primary key) is: a|b|c|d|e|f 0| | |2| |9 1|1| |3| |8 2|1| |4|4|7 3| |5|5|4|6 4|1|6|6| |5 The the question becomes, is there a more convenient way to do:

Re: [sqlite] Find first non-NULL values of several columns

On 19/10/2012 9:17 AM, Igor Tandetnik wrote: Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: I'd go for a user-defined aggregate taking two args: the key (to identify "first") and the value to coalesce. Sure, it would never stop the scan early, but the benefit of doing

Re: [sqlite] Find first non-NULL values of several columns

On 19/10/2012 3:09 PM, Steinar Midtskogen wrote: Thank you for all suggestions. I will need to do such queries often, so it's just a matter of saving the typing. Unfortunately, views aren't going to be very practical either, because there are a lot of tables and columns (100+), and new ones

Re: [sqlite] transaction commit is successful despite I/O error

On 19/10/2012 4:40 PM, Efim Dyadkin wrote: Hi Richard, You are right about the purpose of unlink but it is out of context. There are a transaction in progress and hot journal on disk. If journal can't be deleted by the end of transaction, the transaction can't be considered to be

Re: [sqlite] "natrual" join (not "natural")

On 31/10/2012 9:54 AM, Samuel Neff wrote: We wrote a query and intended to use a "natural" join but had a typo and wrote "natrual" join instead. We were surprised this query was processed without error and performed a cross join. That must have been a delightful one to track down... It's due

Re: [sqlite] Bug: Inconsistency wrt. indirectly defined columns

On 07/11/2012 7:58 PM, Simon Davies wrote: On 7 November 2012 20:36, wrote: Quoting Simon Davies : . . . I think this is the documented behaviour: http://www.sqlite.org/datatype3.html tab1.id has integer affinity, and '42' is

Re: [sqlite] light weight write barriers

On 14/11/2012 8:17 PM, Vladislav Bolkhovitin wrote: Nico Williams, on 11/13/2012 02:13 PM wrote: declaring groups of internally-unordered writes where the groups are ordered with respect to each other... is practically the same as barriers. Which barriers? Barriers meaning cache flush or

Re: [sqlite] Tracing latencies

On 05/12/2012 1:25 PM, Dan Kennedy wrote: On 12/06/2012 12:47 AM, Black, Michael (IS) wrote: I'm a bit confused with "always aligned". None of the lseeks in this log are 1024 aligned. And I just ran a test with the 3.7.15.1 latest amalgamation and most of these seeks are not aligned. Once in

Re: [sqlite] just a test

Clearly, Igor is too helpful and responds to too many messages... *rolls eyes* I'm not on gmail, so I didn't know this was even a problem, but hopefully it gets sorted out soon. Ryan On 09/12/2012 2:01 AM, dd wrote: Yes. Igor Tandetnik mails marked as a spam nowadays. I marked it as a NOT

Re: [sqlite] SQLite - Dropping TEMP table doesn't free allocated memory

On 28/12/2012 4:04 PM, Krzysztof wrote: I don't understand :/ So what is solution in my case? What is the problem you need to solve? If I understand correctly, your app repeatedly creates and deletes ~80MB of temp data. If so, it's actually a good thing that sqlite doesn't release the memory,

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

On 14/01/2013 9:48 AM, François-xavier Jacobs wrote: 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 That's because you

Re: [sqlite] SQLite3 Bug Report: The shell accepts some dot commands ending in semicolons while rejecting others.

On 28/01/2013 12:08 PM, Larry Brasfield wrote: Nathan Chung wrote: SQLite version: 3.6.12 OS: Mac OS X 10.6.8 *Summary: The SQLite3 shell accepts some dot commands ending in semicolons while rejecting others without displaying proper error messages. The behavior of the dot commands could be

[sqlite] Unable to use table alias inside parenthesis on right side of join

Hi all, Strange one here... consider the following schema: R(a,b) S(b,c) These queries all work fine: select R1.* from R R1 join S S1 using(b); select S1.* from R R1 join S S1 using(b); select R1.* from (R R1 join S S1 using(b)); select S1.* from (R R1 join S S1 using(b)); select R1.* from (R

Re: [sqlite] Unable to use table alias inside parenthesis on right side of join

at 12:04 PM, Ryan Johnson <ryan.john...@cs.utoronto.ca>wrote: Hi all, Strange one here... consider the following schema: R(a,b) S(b,c) These queries all work fine: select R1.* from R R1 join S S1 using(b); select S1.* from R R1 join S S1 using(b); select R1.* from (R R1 join S S1 using(b));

Re: [sqlite] I suspect not - but is the database separator configurable?

On 01/02/2013 12:28 PM, Mohit Sindhwani wrote: Hi Marc, On 1/2/2013 10:42 PM, message adams wrote: My applications actually run against sybase, but I'd love to use a connection to an in-memory sqlite to carry out my testing. As part of the unit-test, I'd pass the sqlite conenction into my

Re: [sqlite] select max(x), y from table

On 03/02/2013 10:31 AM, Gabor Grothendieck wrote: On Sun, Feb 3, 2013 at 5:12 AM, E.Pasma wrote: Op 3 feb 2013, om 02:59 heeft Igor Tandetnik het volgende geschreven: On 2/2/2013 6:46 PM, Gabor Grothendieck wrote: In 3.7.11 there was a change to support the feature in

Re: [sqlite] I suspect not - but is the database separator configurable?

On 03/02/2013 10:16 AM, Stephen Chrzanowski wrote: Just had a thought; You could do a few things, unfortunately all at the code base level; 1> I don't know if Python will handle it, but I know most other languages have a string-replace function, or, more specifically, in Delphi, there is a

Re: [sqlite] Required sqlite binaries for DOS urgently

On 19/02/2013 8:08 AM, Mahesh Chavan wrote: My IDE bus is refusing to recognise my HDD or CDROM, I am compelled to access my PC using floppy only. I intend to run all my database software on floppy using sqlite3. I need to develop the software on warfooting. I have already compiled some programs

Re: [sqlite] experimental (better?) usage based sqlite cache

On 25/02/2013 7:24 AM, Simon Slavin wrote: On 25 Feb 2013, at 11:33am, Howard Chu wrote: Gabriel Corneanu wrote: Following a few other discussions, I had the feeling that sqlite should benefit from a cache which discards cached pages in a least frequently used order. Just

Re: [sqlite] Is there a way to create a foreign key in existing table?

On 01/03/2013 11:10 AM, Stephen Chrzanowski wrote: ***I'm waiting for the repair man to show up to fix my waterheater... so... I'm bored. This is going to be to the point at the beginning, but get wordy and technical near the end. ;) Super over kill. ahem Nice explanation... just a

Re: [sqlite] Is there a way to create a foreign key in existing table?

On 01/03/2013 2:23 PM, Igor Korot wrote: Hi, guys, On Fri, Mar 1, 2013 at 8:48 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: On 01/03/2013 11:10 AM, Stephen Chrzanowski wrote: ***I'm waiting for the repair man to show up to fix my waterheater... so... I'm bored. This is

Re: [sqlite] Is there a way to create a foreign key in existing table?

On 01/03/2013 4:09 PM, Igor Korot wrote: Ryan, On Fri, Mar 1, 2013 at 12:48 PM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: On 01/03/2013 2:23 PM, Igor Korot wrote: Hi, guys, On Fri, Mar 1, 2013 at 8:48 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: On 01/03/2

Re: [sqlite] Is there a way to create a foreign key in existing table?

On 01/03/2013 8:08 PM, Stephen Chrzanowski wrote: On Fri, Mar 1, 2013 at 11:48 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca>wrote: On 01/03/2013 11:10 AM, Stephen Chrzanowski wrote: ***I'm waiting for the repair man to show up to fix my waterheater... so... I'm bored. This is

Re: [sqlite] Break on cumulative sum

On 04/03/2013 2:20 PM, Petite Abeille wrote: On Mar 4, 2013, at 1:32 AM, James K. Lowden wrote: What do you have in mind? I've benn adding some user defined functions and am thinking of creating a repository for them. All the so-called window functions from

Re: [sqlite] inner vs. outer join inconsistency

On 06/03/2013 4:50 AM, Tom Matrix wrote: Richard Hipp writes: On Sun, Mar 3, 2013 at 2:48 PM, Tom Matrix wrote: I’ve encountered a problem, which is hardly reproducable on arbitrary databases, therefore I attached one. A simple, reproducible test case for (what

Re: [sqlite] inner vs. outer join inconsistency

I would agree that no warning is needed for for columns that don't state any affinity, or for a non-affinity FK that refers to some PK with affinity. I tend to agree with OP that an explicitly text foreign key referring to an explicitly int primary key is probably worth a warning (perhaps

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On 06/03/2013 10:30 AM, Dominique Devienne wrote: On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: Off topic, I'd love a way to request strong typing for a column (so that attempts to store 'abc' into an int column would fail). You can emulate it with

Re: [sqlite] Is there a way to create a foreign key in existing table?

<ikoro...@gmail.com> wrote: Hi, Ryan, On Fri, Mar 1, 2013 at 12:48 PM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: On 01/03/2013 2:23 PM, Igor Korot wrote: Hi, guys, On Fri, Mar 1, 2013 at 8:48 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: On 01/03/2013 11:10 AM, S

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On 07/03/2013 9:28 AM, Simon Slavin wrote: On 7 Mar 2013, at 1:36pm, "Michael Black" wrote: New: select cast('2' as integer); 2 select cast('2a' as integer); 0 Sorry, but that's very bad. There is no way that the string '2a' could represent 0. I agree that

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On 06/03/2013 10:30 AM, Dominique Devienne wrote: On Wed, Mar 6, 2013 at 3:29 PM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: Off topic, I'd love a way to request strong typing for a column (so that attempts to store 'abc' into an int column would fail). You can emulate it with

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On 07/03/2013 11:14 AM, Doug Currie wrote: On Mar 7, 2013, at 11:07 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: That does leave the question of what to do with cast ('1.0' as integer), though. Without the prefix-based matching that would now return NULL rather than 1, even

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On 07/03/2013 12:27 PM, Nico Williams wrote: On Thu, Mar 7, 2013 at 11:12 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: I would argue that, if a column has type affinity, CHECK should work with the value that would actually get stored, not the one that was assigned. But th

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On 07/03/2013 12:18 PM, Ryan Johnson wrote: On 07/03/2013 11:14 AM, Doug Currie wrote: On Mar 7, 2013, at 11:07 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: That does leave the question of what to do with cast ('1.0' as integer), though. Without the prefix-based matching that

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On 07/03/2013 1:07 PM, Nico Williams wrote: On Thu, Mar 7, 2013 at 11:44 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: On 07/03/2013 12:27 PM, Nico Williams wrote: On Thu, Mar 7, 2013 at 11:12 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: I would argue that, if

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On 07/03/2013 1:15 PM, Simon Slavin wrote: On 7 Mar 2013, at 4:07pm, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: That does leave the question of what to do with cast ('1.0' as integer), though. Without the prefix-based matching that would now return NULL rather than 1, even thoug

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On 07/03/2013 1:45 PM, Simon Slavin wrote: On 7 Mar 2013, at 6:27pm, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: The problem is sqlite3 doesn't cast to REAL first. It just parses the string until it hits '.' (which isn't a valid part of an integer) and then returns whatever

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On 07/03/2013 1:48 PM, Nico Williams wrote: On Thu, Mar 7, 2013 at 12:20 PM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: On 07/03/2013 1:07 PM, Nico Williams wrote: You might defer checks, but not type conversions. In any case, I see no value in deferring check constraints. An

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

On 07/03/2013 3:14 PM, Nico Williams wrote: On Thu, Mar 7, 2013 at 12:53 PM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: Meanwhile, though, I'd be delighted if column affinity, cast(), implicit conversions performed by arithmetic operations, check(), and triggers all behaved the sa

Re: [sqlite] Is there a way to create a foreign key in existing table?

On 07/03/2013 5:59 PM, Igor Korot wrote: Ryan, On Thu, Mar 7, 2013 at 5:26 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: Well, you *do* want an index for the target side of the join, but for FK joins the existing PK index already takes care of that. OK, At this point you onl

Re: [sqlite] per-context resources for user-defined function

On 11/03/2013 7:47 AM, Clemens Ladisch wrote: James K. Lowden wrote: I'm not sure how to manage the lifetime of ancillary data for a user-defined function added by sqlite3_create_function (). [...] Suppose xStep doesn't fail, but another query is executing simultaneously, also using the

[sqlite] Query optimizer bug?

Hi all, I'm running sqlite-3.7.13 on cygwin. Playing around with various TPC-H queries with my class recently, I hit a strangely slow query and don't understand why it's so slow. The schema and dataset generator are available at tpc.org, and end of this message has instructions to replicate

Re: [sqlite] Query optimizer bug?

On 14/03/2013 3:09 PM, Richard Hipp wrote: Nitpick: A "bug" means it gets the wrong answer, which is not the case here. What you are reporting here is not a bug but an optimization opportunity. Oops... you're right. Sorry about that. On Thu, Mar 14, 2013 at 2:07 PM, Ryan Johnson

Re: [sqlite] How to install sqlite 3.7.11

On 21/03/2013 11:47 AM, Simon Slavin wrote: On 21 Mar 2013, at 4:43am, Pratik Patodi wrote: I want to install sqlite 3.7.11 in my ubuntu 10.04. Got the source code but no the makefile/config file. From where can ,I Download the set-up and how to install it.

Re: [sqlite] Timeline for full ALTER TABLE support?

On 27/03/2013 12:14 PM, Tim Gustafson wrote: Clemens' analysis of the likelihood of seeing ALTER TABLE anytime soon is correct. Might I suggest that the "omitted" page then be updated to unambiguously state that there is no plan to even implement the missing features, so that people aren't left

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

On 04/04/2013 8:02 AM, Richard Hipp wrote: By making use of memory-mapped I/O, the current trunk of SQLite (which will eventually become version 3.7.17 after much more refinement and testing) can be as much as twice as fast, on some platforms and under some workloads. Nice! Some quick

Re: [sqlite] Query optimizer bug?

On 30/04/2013 12:59 PM, Richard Hipp wrote: On Thu, Mar 14, 2013 at 2:07 PM, Ryan Johnson <ryan.john...@cs.utoronto.ca>wrote: Hi all, I'm running sqlite-3.7.13 on cygwin. Playing around with various TPC-H queries with my class recently, I hit a strangely slow query and don't understa

Re: [sqlite] Query optimizer bug?

On 30/04/2013 5:20 PM, Nico Williams wrote: On Tue, Apr 30, 2013 at 11:59 AM, Richard Hipp wrote: http://www.sqlite.org/draft/queryplanner-ng.html That's quite interesting. Should the user have a way to influence the query planner? Perhaps by indicating a cost for each

Re: [sqlite] Problem with index on multiple columns

On 17/05/2013 11:06 AM, Richard Hipp wrote: On Fri, May 17, 2013 at 11:02 AM, GB wrote: Richard Hipp schrieb am 17.05.2013 16:37: Collating orders and affinities might be disqualifying the constraint on "id" from being used with the index. It just came to my mind that

Re: [sqlite] Database Corrupted!

On 27/05/2013 9:40 PM, Woody Wu wrote: On Mon, May 27, 2013 at 04:31:25PM +0100, Simon Slavin wrote: On 27 May 2013, at 4:22pm, Woody Wu wrote: If Yaffs2 is the cause, how can I write an effective test to exposure it? Do you have an opportunity to format the same drive

Re: [sqlite] select count(*) does not use primary key??

On 06/06/2013 10:52 AM, Gabriel Corneanu wrote: In my opinion, count(*) is the same as count(rowid) (I see that even count() is accepted); I could say it's even the same as count(x) (any other field). Not quite... count(x) only counts rows having non-NULL x. Granted, that's not a problem for

Re: [sqlite] How to interrupt a long running update without roll back?

On 19/06/2013 1:41 AM, jhnlmn wrote: Thank you for your response Simon Slavin writes: UPDATE T SET C1 = calculation(C2) WHERE C1 IS NULL AND rowid < 1 This is the best solution when the table is freshly created and max(rowid) == number of rows. But after many deletes and

Re: [sqlite] How to interrupt a long running update without roll back?

On 21/06/2013 2:55 AM, jhnlmn wrote: Ryan Johnson <ryan.johnson@...> writes: histo = list(conn.execute('select min(rowid) lo, max(rowid) hi, count(*) n from lineitem group by rowid/1' order by lo)) ... a,b,n = buckets[-1] Thank you for your reply. You code is not very c

Re: [sqlite] How to interrupt a long running update without roll back?

On 21/06/2013 2:29 PM, jhnlmn wrote: Ryan Johnson <ryan.johnson@...> writes: Q1: Is C1 *always* NULL in a newly-inserted row, or does the application sometimes insert some arbitrary value? Q2: Does the transition from NULL to calculation(C2) mean something special to the appli

Re: [sqlite] How to interrupt a long running update without roll back?

On 21/06/2013 8:41 PM, Keith Medcalf wrote: No, in my case user does not touch the DB - he can only add or delete items, but all DB modifications are done by my app and I have a full control over all the values. All I need is to find the most efficient way to do that. Therefore, I do not

Re: [sqlite] Hints for the query planner

On 12/09/2013 7:12 PM, Simon Slavin wrote: On 12 Sep 2013, at 11:35pm, Roger Binns wrote: On 12/09/13 05:03, Gabor Grothendieck wrote: Perhaps indexing the expression in question would be an alternative that would keep the performance info separate from the select.

Re: [sqlite] racing with date('now') (was: Select with dates)

On 15/09/2013 3:36 AM, Petite Abeille wrote: On Sep 15, 2013, at 12:53 AM, Kees Nuyt wrote: 3) If an SQL-statement generally contains more than one reference to one or more s, then all such ref- erences are effectively evaluated simultaneously. FWIW, Oracle concurs:

Re: [sqlite] racing with date('now') (was: Select with dates)

On 15/09/2013 2:23 PM, Yuriy Kaminskiy wrote: Stephan Beal wrote: On Sun, Sep 15, 2013 at 1:58 PM, Yuriy Kaminskiy wrote: Sure, there can be several way to interpret CURRENT_* and *('now'). However, some of them can be useful (transaction, statement), and others (step) -

Re: [sqlite] Hints for the query planner

On 02/10/2013 2:19 AM, Baruch Burstein wrote: On Fri, Sep 13, 2013 at 5:29 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca>wrote: -- Join cardinality: Bach was a *very* prolific composer whose output likely dwarfs the (surviving) output of his contemporaries select p.title, c.name, p.yea

Re: [sqlite] Version 3.8.1 beta

On 02/10/2013 5:13 AM, Joe Mistachkin wrote: Jan Nijtmans wrote: Well, I did some digging as well, and it looks like the libtool upgrade is the coolpit: See: Classic. The error message is completely inscrutable to someone who's

Re: [sqlite] How to use my own python function in a SQLite WHERE clause?

On 05/10/2013 6:01 AM, Clemens Ladisch wrote: Bao Niu wrote: SELECT * FROM myTable WHERE MyDate > MyModule.ChineseDate("兔年八月十五") You cannot use Python function directly in SQL. ... but you can register it with sqlite3 easily enough and use it from SQL afterward:

Re: [sqlite] Bug: analyze.c does not honor SQLITE_64BIT_STATS

On 09/10/2013 10:07 AM, Ralf Junker wrote: On 09.10.2013 15:50, Eric Minbiole wrote: With this change, tests pass again: #if sizeof(p->nRow) == sizeof(long long) sqlite3_snprintf(24, zRet, "%lld", p->nRow); #elseif sizeof(p->Row) = sizeof(long) sqlite3_snprintf(24, zRet,

Re: [sqlite] Bug: analyze.c does not honor SQLITE_64BIT_STATS

On 09/10/2013 9:53 PM, Richard Hipp wrote: On Wed, Oct 9, 2013 at 9:49 PM, James K. Lowden wrote: It's difficult to do portably because you have to account for every combination of standard C library and integer size Remember that SQLite does not use the

Re: [sqlite] Segmentation Fault With Trigger

I'm not an sqlite3 dev, but I don't think you'll get much help until you provide enough information for somebody to see what is going wrong. You were already asked to provide a backtrace from a debug-compiled sqlite3 library (the backtrace you sent is all but useless). A self-contained .c

[sqlite] Automatic join reordering doesn't seem to work?

Hi all, I'm playing around with a small TPC-H dataset (scale factor 100) in sqlite-3.7.3, and have noticed that several of the optimizations described at http://www.sqlite.org/optoverview.html don't seem to take effect, even after running ANALYZE. In one case the optimizer seems to make a

Re: [sqlite] Automatic join reordering doesn't seem to work?

On 21/01/2012 1:01 PM, Simon Slavin wrote: On 21 Jan 2012, at 5:49pm, Ryan Johnson wrote: In one case the optimizer seems to make a different decision depending on which order I write the join in; in the other case, the join ordering chosen is bad and compounded by an expensive subquery

Re: [sqlite] Linking a "C" program with SQlite3.DLL

On 21/01/2012 1:46 PM, Bruce Steele wrote: I suspect this is a very basic question that is answered somewhere but I have done lots of searches and have been able to find a good answer. I am trying to compile a C program using Sqlite3 APIs. the program contains an include sqlite3.h line. I am

Re: [sqlite] Automatic join reordering doesn't seem to work?

On 21/01/2012 2:44 PM, Simon Slavin wrote: On 21 Jan 2012, at 7:23pm, Ryan Johnson wrote: It's a SQL89 join. Sqlite docs say SQL89 and SQL92 joins perform identically [1], which I confirmed before sending the OP. Oh. Okay. If it spits out the same EXPLAIN QUERY PLAN then SQLite

Re: [sqlite] Automatic join reordering doesn't seem to work?

Bump? On 21/01/2012 2:47 PM, Ryan Johnson wrote: On 21/01/2012 2:44 PM, Simon Slavin wrote: On 21 Jan 2012, at 7:23pm, Ryan Johnson wrote: It's a SQL89 join. Sqlite docs say SQL89 and SQL92 joins perform identically [1], which I confirmed before sending the OP. Oh. Okay. If it spits out

Re: [sqlite] Automatic join reordering doesn't seem to work?

On 23/01/2012 12:48 PM, Simon Slavin wrote: On 23 Jan 2012, at 4:30pm, Ryan Johnson wrote: Bump? On 21/01/2012 2:47 PM, Ryan Johnson wrote: On 21/01/2012 2:44 PM, Simon Slavin wrote: On 21 Jan 2012, at 7:23pm, Ryan Johnson wrote: It's a SQL89 join. Sqlite docs say SQL89 and SQL92 joins

Re: [sqlite] Automatic join reordering doesn't seem to work?

On 23/01/2012 12:51 PM, Richard Hipp wrote: On Mon, Jan 23, 2012 at 12:48 PM, Simon Slavin<slav...@bigfraud.org> wrote: On 23 Jan 2012, at 4:30pm, Ryan Johnson wrote: Bump? I don't know if Dr Hipp is pursuing this privately or expecting it to be solved collaboratively on thi

Re: [sqlite] Automatic join reordering doesn't seem to work?

On 23/01/2012 3:09 PM, Ryan Johnson wrote: On 23/01/2012 12:51 PM, Richard Hipp wrote: On Mon, Jan 23, 2012 at 12:48 PM, Simon Slavin<slav...@bigfraud.org> wrote: I don't know if Dr Hipp is pursuing this privately or expecting it to be solved collaboratively on this list. I don'

Re: [sqlite] Automatic join reordering doesn't seem to work?

On 23/01/2012 7:24 PM, Ryan Johnson wrote: On 23/01/2012 3:09 PM, Ryan Johnson wrote: On 23/01/2012 12:51 PM, Richard Hipp wrote: On Mon, Jan 23, 2012 at 12:48 PM, Simon Slavin<slav...@bigfraud.org> wrote: I don't know if Dr Hipp is pursuing this privately or expecting it to be

Re: [sqlite] ALTER TABLE

On 06/02/2012 1:59 PM, Bill McCormick wrote: Nico Williams wrote, On 2/6/2012 12:44 PM: On Mon, Feb 6, 2012 at 9:36 AM, Bill McCormick wrote: Is there no way to force columns added to a table with alter table to be added at certain column positions? Alternatively, if

Re: [sqlite] Surprising INTEGER PRIMARY KEY Behavior

On 16/02/2012 1:26 AM, Pete wrote: When creating a new table, I accidentally assigned a default value of CURRENT_DATE to an INTEGER PRIMARY KEY AUTOINCREMENT field and was surprised that I didn't get an error on CREATE TABLE as a result. It seems that the default is ignored as an INSERT with

Re: [sqlite] INSERT OR REPLACE

On 08/03/2012 6:15 PM, Nico Williams wrote: On Thu, Mar 8, 2012 at 4:37 PM, Simon Slavin wrote: That's why you don't make a DBMS (SQL) do the job of a programming language. Use your programming language to to retrieve the values you need to make your calculations.

Re: [sqlite] Join-source issue when using sub '(' join-source ')'

On 19/03/2012 12:07 PM, TAUZIN Mathieu wrote: Thanks for your support ! SQL Ansi (and every major DB SqlServer,

Re: [sqlite] 64-bit precompiled command-line shell binary for Windows 7

The list strips all attachments... you might want to PM it. On 30/04/2012 12:23 PM, Baruch Burstein wrote: I haven't tested it (I only have a 32-bit system), but here you go. Just change the extension to .exe On Mon, Apr 30, 2012 at 4:28 PM, Udi Karni wrote: Dear Sqlite

Re: [sqlite] [PATCH] add malloc and alloc_size attributes to allocation functions

The list strips attachments... you might try sqlite-dev? On 01/06/2012 5:31 PM, Nuno Lopes wrote: Hi, Please find in attach a patch that adds support for the malloc and alloc_size function attributes. The malloc attribute specifies that a function behaves like malloc (i.e., it returns a

Re: [sqlite] 100% CPU utilization sqlite running in VMWare

On 06/06/2012 7:45 AM, Simon Slavin wrote: On 6 Jun 2012, at 12:00pm, IQuant wrote: We need to be able to run 1000's of extractors concurrently processing different tick tapes and symbol sets. aka service bureau. The Daily tick tapes are approx 20gb each.. 30TB repository

Re: [sqlite] SQLite4 (don't scream)

On 28/06/2012 12:30 PM, Cory Nelson wrote: On Thu, Jun 28, 2012 at 11:20 AM, Stephan Beal wrote: On Thu, Jun 28, 2012 at 5:57 PM, Simon Slavin wrote: Now the URL: Just thought some people

Re: [sqlite] SQLite4 key encoding bug

On 10/07/2012 9:58 AM, bardzotajneko...@interia.pl wrote: SQLite version 4.0.0 2012-07-07 12:21:48 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t(a); sqlite> insert into t values(123); sqlite> insert into t values(123.0); sqlite> insert into t

Re: [sqlite] Initial read speed greater than subsequent

On 13/07/2012 5:37 PM, Udi Karni wrote: Hello, Running on Windows 7 - I am noticing that tables in :memory: DBs are read (SELECTED) at a constant rate. However - conventional DBs on disk - even on SSD - are read fast the first time, and much slower subsequently. Closing and reopening a DB for

Re: [sqlite] index has higher cache priority than data?

On 21/09/2012 7:54 AM, Clemens Ladisch wrote: John Bachir wrote: i've read other posts on this list that say that we can't guess what sqlite will do with cache. It uses a simple LRU algorithm to determine which pages to kick out of the page cache first (so at least it's somewhat

Re: [sqlite] Combining user-defined functions with aggregate functions

On 10/10/2012 10:49 AM, Dan Kennedy wrote: On 10/10/2012 08:14 PM, Hamish Allan wrote: Short form question: Working: SELECT a, userfunc(systemfunc) FROM t; Working: SELECT a, sum(systemfunc) FROM t GROUP BY a; Not working: SELECT a, sum(userfunc(systemfunc)) FROM t GROUP BY a; Long form

Re: [sqlite] Combining user-defined functions with aggregate functions

On 10/10/2012 11:07 AM, Dan Kennedy wrote: On 10/10/2012 10:01 PM, Ryan Johnson wrote: On 10/10/2012 10:49 AM, Dan Kennedy wrote: On 10/10/2012 08:14 PM, Hamish Allan wrote: Short form question: Working: SELECT a, userfunc(systemfunc) FROM t; Working: SELECT a, sum(systemfunc) FROM t GROUP

Re: [sqlite] SQLite VM questions

On 14/10/2012 2:26 PM, Pavel Ivanov wrote: What kind of interpreter does the query executor uses? How important is the interpreter's speed, to SQLite's speed ? SQLite doesn't have interpreter, it has parser. I guess this makes the rest of your email inapplicable. Umm... yes it does.

Re: [sqlite] transaction commit is successful despite I/O error

On 18/10/2012 8:45 AM, Richard Hipp wrote: On Wed, Oct 17, 2012 at 4:11 PM, Efim Dyadkin wrote: Hi, I am testing loss of data in Sqlite database correlated to auto-mounter malfunction. I am running Sqlite on Linux and my database file is located on network disk. For a