Re: [sqlite] I keep getting seg faults building my database using python sqlite3

On Fri, 18 Nov 2016 08:55:11 -0800 "Kevin O'Gorman" wrote: > All of the python code is a single thread. The closest I come > is a few times where I use subprocess.Popen to create what amounts to > a pipeline, and one place where I start a number of copies of a C >

Re: [sqlite] Changing ID's to UUID

On Wed, 16 Nov 2016 08:59:03 -0600 Paul Egli wrote: > > Using the systemid sequence and the recordid sequence directly > > however, has a 0% probability of collision, so any rational person > > would use that directly and forgo entirely the introduction of > > uncertainty

Re: [sqlite] sqlite3 crashes mysteriously on 3.6.20-1

On Mon, 14 Nov 2016 20:30:57 -0500 "pisymbol ." wrote: > One last thing: This is during initialization and I access the > database through that query several times before hitting this crash. > > I thought it was memory corruption but it always the same line. Trying to be

Re: [sqlite] Autoincrement sequence not updated by UPDATE

On Tue, 1 Nov 2016 11:01:24 + Simon Slavin wrote: > attempts to change a value in that column using UPDATE always > generate an error. I didn't know that. I looked it up. Apparently > Microsoft's SQLSERVER blocks it Blocks but does not prevent.

Re: [sqlite] Default ordering of SELECT query

by the strict semantics of the statements you're executing; i.e., trust it to fulfil its contract, but nothing else. James Elsevier Limited. Registered Office: The Boulevard, Langford Lane, Kidlington, Oxford, OX5 1GB, United Kingdom, Registration No.

Re: [sqlite] Why is this query much slower when I index the columns?

On Sat, 2016-10-01 at 13:57 +0200, Clemens Ladisch wrote: Heather, James (ELS-LON) wrote: I have ... ... asked this question elsewhere: http://dba.stackexchange.com/questions/150858/why-is-this-sqlite-query-much-slower-when-i-index-the-columns Yes, I didn't mean this to be subterfuge

Re: [sqlite] Why is this query much slower when I index the columns?

a web site that generates them, and stuffed them into a database. The column names were generated by the web site. It's not used for anything real. James Elsevier Limited. Registered Office: The Boulevard, Langford Lane, Kidlington, Oxford, OX5 1GB, United Kingdom

[sqlite] Why is this query much slower when I index the columns?

fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial; When there are no indexes except on the primary keys (irrelevant to this query), it runs quickly: [james@marlon Downloads

Re: [sqlite] SQLite 3.15.0 scheduled for 2016-10-14

On Fri, 23 Sep 2016 16:35:07 + Quan Yong Zhai wrote: > Quote << > A "row value" is an ordered list of two or more scalar values. In > other words, a "row value" is a vector.>> > > A ?row value? is a tuple, not a vector. When your using a tuple, you > know how many items in it,

Re: [sqlite] SQLite 3.15.0 scheduled for 2016-10-14

On Thu, 22 Sep 2016 12:43:29 -0700 Darren Duncan wrote: > single-element row could be done with say a trailing comma; eg > "(42,)" All hail the Python tuple! "Tuples of two or more items are formed by comma-separated lists of expressions. A tuple of one item

Re: [sqlite] Complicated join

On Thu, 15 Sep 2016 15:53:10 + (UTC) David Bicking wrote: > (1) The CombinedKeyFields must always match in each table(2) Match > using the EvtNbr, but if no match, use the lowest M.EvtNbr that > matches the CombinedKeyFields > > (3) Match using the TransDate but if no

Re: [sqlite] When is data committed on one connection seen on another?

On Wed, 14 Sep 2016 18:29:36 + (UTC) Alex Ward wrote: > Perhaps our schema needs a rework, would one table with a million > rows be better than 500 tables with 2000 rows each? 500 tables isn't right or wrong, but *counting* tables is. Table count is not a design-quality

Re: [sqlite] how is "pragma threads = 4" working

On Fri, 16 Sep 2016 07:29:28 -0400 Richard Hipp wrote: > The algorithm used for "ORDER BY ... LIMIT N" uses much less memory > than a full-up "ORDER BY" because is only keeps track of the top N > entries seen so far, discarding the rest. But it also only uses a > single thread.

Re: [sqlite] Slow Sqlite3 Select Query while Insert/Update/Delete has no issue

On Wed, 14 Sep 2016 16:27:37 +0530 SinhaK wrote: > strlen(MyString.str().c_str()) BTW, as a matter of style, MyString.str().size() gets you to the same place sooner. > MyString<<"select TokenNo,Price ,sum(QTY) from 'Stream0' where > TokenNo=?1 and

Re: [sqlite] WHERE col IN tab

On Fri, 16 Sep 2016 16:59:17 +0200 Dominique Devienne wrote: > Is that <> SQL standard? No. The two most frequently used pointless words in SQL are "select *". The SELECT clause (not statement) chooses columns; in relational algebra terms, it's a project operator. If

Re: [sqlite] Bug in CREATE INDEX

On Mon, 8 Aug 2016 10:48:58 -0700 "Kevin O'Gorman" wrote: > Very cool. But 4? I will be running this on machines with 8 and 16 > cores. Does going beyond 4 not help much? Four doesn't seem like a bad starting point. I don't have any information specific to SQLite,

Re: [sqlite] C API - Parameterized Atomic Transactions

On Tue, 9 Aug 2016 17:09:39 -0300 Paulo Roberto wrote: > I would like something like this: > > "BEGIN EXCLUSIVE TRANSACTION;" > "SELECT counter FROM mytable WHERE counterid = ?;" > "UPDATE mytable SET counter=? WHERE counterid = ?;" > "COMMIT TRANSACTION;"

Re: [sqlite] REFERENCES from TEMPORARY table to main database table?

On Sat, 23 Jul 2016 01:06:23 + "Smith, Randall" wrote: > Using "REFERENCES main.Symbols" appears to be a syntax error. I think you got caught by a special case. Any kind of DRI would be hard to enforce across database boundaries. In general, if we have two

Re: [sqlite] insert or replace on PK and UNIQUE INDEX

On Tue, 12 Jul 2016 15:35:20 +0200 Dominique Devienne wrote: > Now we know OR REPLACE is never what we want (in our use cases), Besides being nonstandard, REPLACE is not atomic. I haven't seen the use case that benefits from those characteristics but, judging from this

Re: [sqlite] builtin functions and strings with embedded nul characters

On Mon, 4 Jul 2016 13:07:18 +0200 R Smith wrote: > I think you are missing an important bit in all of this - the strings > in C is the problem, they think a Null character indicates > termination. It has nothing to do with how SQL stores data - SQLite > will store it with all

Re: [sqlite] UNIQUE constraint violation

On Tue, 28 Jun 2016 19:19:43 -0700 J Decker wrote: > Duplication can also result as part of the - in process - moving of > rows. To change the order of [1,2,3,4] to > [1,3,2,4] there is(can be) a state that is [1,2,2,4] before the > second part that sets three back into 2.

Re: [sqlite] sqlite3_column_origin_name for INSERT and UPDATE

On Fri, 17 Jun 2016 19:59:56 +0200 Rapin Patrick <rapin.patr...@gmail.com> wrote: > 2016-06-17 18:24 GMT+02:00 James K. Lowden <jklow...@schemamania.org>: > > > You are encoding type information in the name. If you move the type > > information into the da

Re: [sqlite] Correct, best, or generally accepted database structure for groups of things

On Fri, 17 Jun 2016 07:37:16 +0100 Chris Locke wrote: > I fail to see what any of this has to do with sqlite. I thought this > was a mailing list for sqlite? Seeing queries (no pun intended) on > sql statements is very subjective, especially with the limited data >

Re: [sqlite] Correct, best, or generally accepted database structure for groups of things

On Thu, 16 Jun 2016 20:53:25 + "Drago, William @ CSG - NARDA-MITEQ" wrote: > CREATE TABLE Apples ( > ID INTEGER PRIMARY KEY, > Color TEXT COLLATE NOCASE, --Could be Red, Green, or Yellow check Color in ( 'Red', 'Green', 'Yellow' ), -- FTFY > Height REAL,

Re: [sqlite] Trouble coding conditional UNIQUE

On Thu, 16 Jun 2016 23:23:33 +0200 Dominique Devienne <ddevie...@gmail.com> wrote: > > > On Behalf Of James K. Lowden > > > > > > create view vParts as > > > select 1 as Matched, * from Parts > > > UNION > > >

Re: [sqlite] sqlite3_column_origin_name for INSERT and UPDATE

On Fri, 17 Jun 2016 10:56:32 +0200 Rapin Patrick wrote: > And my C++ wrapper then knows that this column is a speed expressed > in meters per second. So when making a SELECT on t1 table, the > wrapper will output number objects with unit dimension of Speed > expressed in

Re: [sqlite] Trouble coding conditional UNIQUE

On Tue, 14 Jun 2016 16:27:29 + "Drago, William @ CSG - NARDA-MITEQ" wrote: > Once the part has been grouped into a set (Matched=1) it receives a > unique permanent serial number and the temporary serial number can be > reused, so (Model, TemporarySerialNumber)

Re: [sqlite] Apple announces new File System with better ACID support

On Tue, 14 Jun 2016 10:49:05 +0900 ?? wrote: > > On 13 Jun 2016, at 10:13pm, Richard Hipp wrote: > > > > The rename-is-atomic assumption is so wide-spread in the Linux > > world, that the linux kernel was modified to make renames closer to > > being

Re: [sqlite] Trouble coding conditional UNIQUE

On Mon, 13 Jun 2016 19:11:29 + "Drago, William @ CSG - NARDA-MITEQ" wrote: > I need UNIQUE(B, C) only when E=0. A conditional constraint is evidence that you have two kinds of things represented in one table: those E=0 types that are identified by {B,C}, and the

Re: [sqlite] Managing SQLite indices.

On Tue, 14 Jun 2016 01:04:27 +0100 Simon Slavin wrote: > When your application runs fast enough not to annoy you, you're > done. If you're not willing to do step (1), don't bother with > anything else. Simon's entire post is excellent advice. To the OP: print it, and

Re: [sqlite] UPDATE statement without FROM clause

On Sat, 4 Jun 2016 18:18:36 +0200 skywind mailing lists wrote: > At the moment I have to run something like: > > UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... > itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID); > > Using a FROM clause I just

Re: [sqlite] SQL / SQLite for Beginners

On Thu, 26 May 2016 10:54:30 -0400 r.a.n...@gmail.com wrote: > FWIW, since it's inception, S.Q.L has been pronounced allot like > CICS. This may be more true than you know. It's not too hard to find old-timers who pronounce it "kicks". --jkl ___

[sqlite] Sqlite incompatibility with Postgres

On Fri, 20 May 2016 14:17:25 +1000 "dandl" wrote: > Every aggregation function is at least second order: a function that > applies a function to the set. So for MIN the function is 'less > than', for SUM() the function is 'plus' and so on. In Andl > aggregation functions are provided by fold(),

[sqlite] Sqlite incompatibility with Postgres

On Thu, 19 May 2016 10:29:48 +1000 "dandl" wrote: > > Restriction is applied to the values of the tuple. The number of > > tuples is not a value of the tuple. > > No, I can't agree. Restriction is a membership test, a function on > members: should this tuple be included in the result set or

[sqlite] foreign_key_check mystery

On Wed, 18 May 2016 19:06:30 +0200 R Smith wrote: > > I'm not convinced the requirement that the referenced columns be > > unique is justified > > How do you see a parent-child relationship possible where the parent > is not Unique? I think I can convince you that uniqueness is a good rule of

[sqlite] Sqlite incompatibility with Postgres

On Wed, 18 May 2016 20:29:26 +1000 "dandl" wrote: > > 2. Otherwise, if exactly the number of specified rows must be > > returned without other restrictions, then the result is possibly > > indeterminate. > > I agree, with one tiny tweak. The SQL standard already notes that > certain queries of

[sqlite] Sqlite incompatibility with Postgres

On Wed, 18 May 2016 10:41:21 +1000 "dandl" wrote: > > You lost me at "subset S of N tuples". Which relational operator > > takes N as an argument? > > Restriction determines whether a tuple should be included or not; you > also need cardinality and less than (for comparing members).

[sqlite] foreign_key_check mystery

On Wed, 18 May 2016 08:32:24 +0200 Clemens Ladisch wrote: > You get "foreign key mismatch" if you do not have the required > indexes, i.e., according to a comment in the source, > 1) The named parent key columns do not exist, or > 2) The named parent key columns do exist, but are not subject to

[sqlite] foreign_key_check mystery

I seem to be getting a foreign key check anomaly. I've checked the constraint mentioned in the error message (and the other one, just in case). Am I overlooking something, or has this been fixed since 3.8.4.1? sqlite> pragma foreign_key_check; Error: foreign key mismatch - "Field"

[sqlite] Sqlite incompatibility with Postgres

On Tue, 17 May 2016 11:09:53 +1000 "dandl" wrote: > Any disagreement so far? Full agreement; your description is perfectly sound. I am quite certain nevertheless that LIMIT has no relational basis. Nothing based on Order By could. And I'll try to clear up what I meant by a cursor. > So

[sqlite] Sqlite incompatibility with Postgres

On Tue, 17 May 2016 11:09:53 +1000 "dandl" wrote: > > I'll invent here and now to replace LIMIT: nth(). > > The issue is find the "top N". This does not solve the problem. nth() does find "top N". For any query, nth(c, N) returns N rows. It also exposes the arbitrariness of LIMIT. To use

[sqlite] Sqlite incompatibility with Postgres

On Mon, 16 May 2016 16:17:35 +1000 "dandl" wrote: > > > All true. But it brings up a question. Suppose the following: > > > > > > first second > > > - -- > > > Mark Spark > > > Emily Spark > > > Mary Soper > > > Brian Soper > > > > > > SELECT first,second FROM

[sqlite] Any way to do inexpensive SQLite database/table versioning? (I.e. cheap snapshotting & jumping between)

On Sun, 15 May 2016 10:42:37 -0500 mikeegg1 wrote: > I was once told of an idea (decades ago) of versioning data within a > table where one column has a real/float value that is the version > number. You can have a point-in-time database if: * each transaction has an id * DELETE is

[sqlite] 64bit DLL vs 32bit

On Thu, 12 May 2016 00:36:31 +1000 "dandl" wrote: > But I think if you compile code for the x64 processor chip and call > it from x86 or vice versa then either it doesn't work or you pay a > high price for thunking from one to the other. I think that's > unavoidable regardless of OS. Right:

[sqlite] 64bit DLL vs 32bit

On Wed, 11 May 2016 11:30:34 +1000 "dandl" wrote: > > more about DLLs than it is about SQLite. > > Actually, it's everyone using a language other than C/C++, plus a > proportion of those too. I use C#, but if you want to call Sqlite > from Java, Python, etc or even some generic C/C++ app that

[sqlite] Sqlite incompatibility with Postgres

On Fri, 13 May 2016 15:13:01 +0100 Simon Slavin wrote: > On 13 May 2016, at 3:07pm, dandl wrote: > > > I have no deep knowledge of standard SQL. > > I used to know SQL92 very well. There's no facility for doing > anything like LIMIT or OFFSET in it. You had to use your programming >

[sqlite] Working with blob

On Thu, 28 Apr 2016 20:27:17 +0200 "deltagamma1 at gmx.net" wrote: > If I store the blob directly in the sqlite, is there a way to open the > blob directly with the respective programm (e.g. irfanview or a pdf > with acroread) ? I have heard of a FUSE filesystem implemented with SQLite. I

[sqlite] No datasize field - why?

On Mon, 25 Apr 2016 02:31:25 +0100 Simon Slavin wrote: > > These are different concerns, and they don't really pose any > > difficulty. Given an encoding, a column of N characters can take > > up to x * N bytes. Back in the day, "x" was 1. Now it's something > > else. No big deal. > > No.

[sqlite] No datasize field - why?

On Sun, 24 Apr 2016 08:51:09 -0400 Carlos wrote: > But, with very fast CPUs and RAM memory buffers for the directory > entries in the disks, the variable length records would probably > result in gain for much less I/O for the data.

[sqlite] No datasize field - why?

On Sun, 24 Apr 2016 14:09:50 +0100 Simon Slavin wrote: > > On 24 Apr 2016, at 1:51pm, Carlos wrote: > > > But, with very fast CPUs and RAM memory buffers for the directory > > entries in the disks, the variable length records would probably > > result in gain for much less I/O for the data. >

[sqlite] No datasize field - why?

On Sat, 23 Apr 2016 19:22:04 -0600 Scott Robison wrote: > So if you could make your table up of integers, floats, and text > with character limits on them you could get fixed-length rows, which > might reduce your access time by 60% or more. Such a decrease in > access time could mean the

[sqlite] No datasize field - why?

On Sat, 23 Apr 2016 14:50:45 -0400 "Keith Medcalf" wrote: > > On Sat, 23 Apr 2016 08:56:14 -0400 > > "Keith Medcalf" wrote: > > > > > Those things that those other DBMSes do are holdovers to maintain > > > backwards compatibility with the good old days when dinosaurs > > > ruled the earth > >

[sqlite] No datasize field - why?

On Sat, 23 Apr 2016 08:56:14 -0400 "Keith Medcalf" wrote: > Those things that those other DBMSes do are holdovers to maintain > backwards compatibility with the good old days when dinosaurs ruled > the earth As amusing as your rant is, it's not accurate. Treating columns as types is a

[sqlite] No datasize field - why?

On Fri, 22 Apr 2016 19:11:46 -0700 Darren Duncan wrote: > The general case of a data type definition is an arbitrarily complex > predicate expression whose parts vary on the base type and other > factors. Given this, if component details of type definitions were > split out into their own

[sqlite] Caveat entry

On Fri, 15 Apr 2016 22:53:57 +0100 Simon Slavin wrote: > On 15 Apr 2016, at 10:05pm, Cecil Westerhof > wrote: > > * SQLite datatypes and how SQLite decides which datatype you want > * SQLite uses affinities not column types > * thinking you should index each column instead of indexes for

[sqlite] Avoid duplicate sets with join table

On Sat, 16 Apr 2016 01:20:55 +0200 Ketil Froyn wrote: > I have two tables and a join table, in principle like this: > > CREATE TABLE records (id INTEGER PRIMARY KEY, data TEXT); > CREATE TABLE features (id INTEGER PRIMARY KEY, data TEXT UNIQUE); > CREATE TABLE records_features (id_r INTEGER,

[sqlite] Working with booleans

On Thu, 14 Apr 2016 20:10:08 -0400 "Keith Medcalf" wrote: > select from where isActive; vs. > select from where isActive = 'T'; AFAIK, the SQL standard requires the second form. You can't simply say "WHERE variable"; you must say "WHERE expression". OP: I personally usually

[sqlite] Primary key values can be NULL

On Fri, 15 Apr 2016 14:13:12 +0200 Cecil Westerhof wrote: > 2016-04-15 1:19 GMT+02:00 J Decker : > > > I would total expect any column I created without NOT NULL (double > > negative) to allow NULL whether INDEX or UNIQUE or PRIMARY is > > applied additionallywhat database does otherwise?

[sqlite] FOREIGN KEY constraint failed

On Tue, 5 Apr 2016 23:56:53 +0200 R Smith wrote: > On 2016/04/05 11:15 PM, Keith Medcalf wrote: > > Are we confusing immediate constraints (checked per statement) with > > DEFERRED constraints (checked at COMMIT time) again? In SQLite some constraints are checked per row, not per statement.

[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing

On Wed, 6 Apr 2016 06:13:01 + Hick Gunter wrote: > You are hopefully aware of the fact that SQLite associates type with > the actual values and not the containers(columns) used to hold these > values? This means that a data object of any type may be > held/returned in a column, irrespective

[sqlite] regression in 3.12.0 vs. 3.11.0, column type information in PRAGMA missing

On Tue, 5 Apr 2016 13:19:50 -0400 Richard Hipp wrote: > CREATE TABLE t2(w SHORT INT, x DECIMAL, y BIGINT, z REAL); > CREATE VIEW v3 AS SELECT w+x+y+z FROM t2; > > What should "PRAGMA table_info('v3')" report as the column type? It should report it as for a table, with values consistent

[sqlite] C API reference manpages

On Thu, 31 Mar 2016 10:21:53 -0400 Richard Hipp wrote: > On 3/31/16, Kristaps Dzonsons wrote: > > > > Is there any interest in integrating this tool to have manpages in > > the doc distribution without downstream bits? > > > > I think that would be cool. Integrating your tool into the source

[sqlite] Help needed for COPY Command.

On Fri, 25 Mar 2016 17:18:16 +0100 "Domingo Alvarez Duarte" wrote: > Why not have direct command ".export table_name" and internally it > does all commands you mention in one go, simple and intuitively. Importing has unique requirements. .import filename tablename is quite

[sqlite] sqlite fixed data loading extension

On Fri, 25 Mar 2016 06:49:22 -0500 Don V Nielsen wrote: > I have a need for something that can parse and load into sqlite tables > fixed length data. Insert your own separators. $ cat input 12345678910111213141516171819202122232425 Print two 5-byte ranges separated by ", ". $ awk '{

[sqlite] Minor documentation improvement

On Fri, 25 Mar 2016 15:06:46 + Simon Slavin wrote: > Could something be added to indicate that it pays attention to > '.separator' ? If you don't already know, you might think it is > fixed to one file format. It could be as simple as > > ".import FILE TABLE Import data from FILE into

[sqlite] Article about pointer abuse in SQLite

On Tue, 22 Mar 2016 11:00:24 -0500 "Marc L. Allen" wrote: > I don't think compilers "run" your code. Provided we're talking about a C compiler, you're right. Optimizers don't run the code, they reason about it. > The fact that the code never actually allows that path to occur is > beyond

[sqlite] Article about pointer abuse in SQLite

On Tue, 22 Mar 2016 09:58:52 -0400 Adam Devita wrote: > I don't know the reasoning, but it seems that VS6 often > initialized things to 0xcd in debug mode and (usually) had memory > uninitialized to 0x00 when complied in Release (perhaps 0x00 just > happens to be what was on the stack or heap).

[sqlite] Article about pointer abuse in SQLite

On Tue, 22 Mar 2016 09:56:57 +0100 "Cezary H. Noweta" wrote: > On 2016-03-22 00:35, James K. Lowden wrote: > >[...] An example from Clang's discussion is > > > > int i = 10 << 31; > > Could you provide a link for that discussion? (Or google's p

[sqlite] Reserved column names

On Mon, 21 Mar 2016 11:32:28 +0100 Dominique Devienne wrote: > > Explicitly documented by SQLite: > > > > And? That's still non-SQL standard. > > SQLite tries to be compatible with non-standard extensions from > various popular RDBMS', but when a standard alternative exists, it > should be

[sqlite] Article about pointer abuse in SQLite

On Mon, 21 Mar 2016 13:48:06 -0700 Scott Perry wrote: > Compilers allow you to choose your standard; --std=c11 means > something very specific (and unchanging) They do. And that covers what the standard covers. The standard also has limits. It includes constructs that are syntactically

[sqlite] Article about pointer abuse in SQLite

On Sat, 19 Mar 2016 02:04:35 -0600 Scott Robison wrote: > As he says, there's not real choice between fast and > > correct > > Except that testing can verify something is correct for a given > environment. That's actually not true, on a couple of levels. "[T]esting can be used

[sqlite] Article about pointer abuse in SQLite

On Fri, 18 Mar 2016 16:33:56 -0600 Scott Robison wrote: > I'd rather have code that might use some "undefined behavior" and > generates the right answer than code that always conformed to defined > behavior yet was logically flawed. Code that falls under undefined behavior *is* logically

[sqlite] CAST STRING => INTEGER

On Wed, 16 Mar 2016 01:53:59 -0600 Scott Robison wrote: > > For example, even the operation "select cast(pow(2,65) as integer)" > > and > "select cast(-pow(2,65) as integer)" should return NULL rather than > MAXINT and MININT respectively. > > The $64 bit question ;) is how much existing code

[sqlite] CAST STRING => INTEGER

On Tue, 15 Mar 2016 19:33:32 -0600 "Keith Medcalf" wrote: > > Yes, if the string cannot be represented as an integer, CAST should > > raise a range error. That spares the application from applying the > > same test in an ad hoc and inconsistent way. > > Since there is no way to "trap" such

[sqlite] SQLite Pronunciation

On Wed, 16 Mar 2016 14:09:08 -0500 Jay Kreibich wrote: > although if you trace SQL back to the IBM days of SEQUEL, there is a > strong argument that the term ?sequel? makes more sense. IBM insisted "SQL" be pronounced as three letters for exactly that reason: to distinguish it from its

[sqlite] CAST STRING => INTEGER

On Tue, 15 Mar 2016 01:02:17 +0100 "Cezary H. Noweta" wrote: > 2nd row: why REALs can have trailing spaces, while INTEGERs cannot? > 3rd row: why REALs can have trailing trash, while INTEGERs cannot? I think we know now that string->integer conversion is pathologically broken for inputs that

[sqlite] CAST STRING => INTEGER

On Mon, 14 Mar 2016 13:25:09 +0100 Clemens Ladisch wrote: > > that ``SELECT CAST(col AS INTEGER);'' should return (not so) random > > result set, and receiving any INTEGER should mean that a source > > string could have trillion or more possible values? > > The documentation does not specify

[sqlite] Creating system table

On Sat, 12 Mar 2016 13:07:01 -0500 Igor Korot wrote: > My question is: what should I do if I want to create a system table? Change the source code? A system table differs from a user table in how it's created. User tables are defined with CREATE TABLE of course, but system tables are

[sqlite] "Circular" order by

On Thu, 10 Mar 2016 21:16:28 +0200 R Smith wrote: > > Hmm, does this work any better? > > > > SELECT id FROM t > > ORDER BY id < 'pen' desc, id; > > It works, but not better. I think it was Igor who proposed similar > (if not, apologies) which of course produces the correct result, but

[sqlite] "Circular" order by

On Thu, 10 Mar 2016 10:17:57 +0100 Alberto Wu wrote: > On 03/09/16 23:30, James K. Lowden wrote: > >> SELECT P.id FROM ( > >> SELECT 0 AS sect, id FROM t WHERE id >= 'pen' > >> UNION ALL > >> SELECT 1, id FROM t WHERE id <

[sqlite] "Circular" order by

On Wed, 9 Mar 2016 20:43:14 +0200 R Smith wrote: > SELECT P.id FROM ( > SELECT 0 AS sect, id FROM t WHERE id >= 'pen' > UNION ALL > SELECT 1, id FROM t WHERE id < 'pen' > ) AS P > ORDER BY P.sect, P.id > ; This is the correct answer. I'm not sure what you meant by "axiom" in your

[sqlite] Correlated subquery refers to wrong rowid - bug?

On Wed, 9 Mar 2016 10:13:28 -0500 Richard Hipp wrote: > > which outputs one result (2), although the expected result would be > > empty. Sorry for my "what bug?" post. I forgot that the output was wrong! --jkl

[sqlite] Correlated subquery refers to wrong rowid - bug?

On Wed, 09 Mar 2016 15:32:01 +0100 Jean-Christophe Deschamps wrote: > > select id from a where id not in (select a.id from b); > As I understand it, there is no more an a.id column in table b. It > looks like SQLite is trying to get clever ignoring the "a." qualifier. It's not ignoring the

[sqlite] Correlated subquery refers to wrong rowid - bug?

On Wed, 9 Mar 2016 10:13:28 -0500 Richard Hipp wrote: > > select id from a where id not in (select a.id from b); > > > > which outputs one result (2), although the expected result would be > > empty. > > > > Thanks for the bug report. What bug? The query is valid SQL, and produces the

[sqlite] Fastest way to find whether at least one row has a certain column value

On Sun, 6 Mar 2016 11:39:38 + Paul Sanderson wrote: > I understand this - but, there always a but, I still would like to do > something. Applying the limit anyway and then telling them the query > has been limited might be a solution. > > Time is usually not an issue but as the results are

[sqlite] How does your sqlite script binding handle aggregate UDFs?

On Sat, 5 Mar 2016 21:22:23 +0100 Stephan Beal wrote: > i'm not aware of any aggregates which (in normal use) take no > arguments Nondeterministic functions need not take any arguments. Built-in examples include NOW(). Your UDF could implement the Dilbert RNG:

[sqlite] Can I implement a scrolling window using LIMIT and OFFSET ?

On Sat, 05 Mar 2016 17:16:52 -0700 "Keith Medcalf" wrote: > > Sometimes it's faster to recompute something than to cache it for > > later re-use. That's rare where I/O is involved, and vanishing rare > > where SQL is involved. > > The only thing worse is retrieving the entire result set and

[sqlite] Can I implement a scrolling window using LIMIT and OFFSET ?

On Fri, 04 Mar 2016 00:35:47 -0800 Darren Duncan wrote: > > How exactly is the first way "easiest"? > > If these are pages displayed to the user, they may want to scroll > backwards at some point; They might, and if you say it's easier to go back to the database than to keep track of

[sqlite] Can I implement a scrolling window using LIMIT and OFFSET ?

On Thu, 3 Mar 2016 10:43:26 +0800 (CST) ?? wrote: > > Can anyone describe a situation for which this style of LIMIT & > > OFFSET is advisable from the application's point of view? (The > > DBMS costs are obvious enough.) > > For me this is the easiest way to implement a scrolling cursor. >

[sqlite] Can I implement a scrolling window using LIMIT and OFFSET ?

On Wed, 2 Mar 2016 14:12:04 +0100 Clemens Ladisch wrote: > > https://www.sqlite.org/lang_select.html talks about LIMIT & OFFSET, > > without mentioning that is a bad idea. > > Neither does it mention that it is a good idea. > > > can I do that or not (will it become sluggish if I do that) ? >

[sqlite] How to check if connection to main database is still open or closed before new query ?

On Tue, 1 Mar 2016 17:13:29 + a a wrote: > I want to check after a while if the connection is allready closed or > not for the simple reason not to reopen the database but if is open > to run a query or if it is closed to reopen the database and then run > the query. I don't blame you for

[sqlite] Random-access sequences

On Tue, 1 Mar 2016 08:15:25 -0500 Richard Damon wrote: > > The theoretical maximum number of rows in a table is 264 > > (18446744073709551616 or about 1.8e+19). This limit is unreachable > > since the maximum database size of 140 terabytes will be reached > > first. A 140 terabytes database can

[sqlite] Encrypt the SQL query

On Thu, 25 Feb 2016 14:01:31 +0800 wrote: > Does SQLite provide a good way to encrypt the SQL query strings while > does not affect the performance when executing the queries? If you're worried about the user examining your program image statically, you could encrypt your SQL by whatever means,

[sqlite] Correlated subquery throwing an error

On Mon, 15 Feb 2016 14:19:12 -0700 Scott Robison wrote: > Each job will take some amount of time to process. The order doesn't > matter as long as all jobs are eventually processed and you have a > single process running the jobs. Limit 1 is a reasonable way to grab > a single job. Reasonable,

[sqlite] Correlated subquery throwing an error

On Mon, 15 Feb 2016 14:55:34 -0700 "Keith Medcalf" wrote: > Pretty sure you meant: > > select * from (select min(t) as t from T) as T; Yes, thanks. :-) --jkl

[sqlite] Performance comparison between SQLite and SQL Server?

On Mon, 15 Feb 2016 11:21:06 +0800 wrote: > I am just curious whether there is a performance comparison between > SQLite and SQL Server? Odds are you will never see a such a comparison published. If you read your SQL Server EULA, you'll see it specifically prohibits publishing benchmark

[sqlite] Correlated subquery throwing an error

On Mon, 15 Feb 2016 08:56:35 +0100 Clemens Ladisch wrote: > I don't know why correlated subqueries cannot use values from the > outer query in the ORDER BY or LIMIT clauses; ORDER BY is not part of SELECT! It's not a relational operator. Per the SQL standard -- ORDER BY cannot appear in a

[sqlite] Correlated subquery throwing an error

On Mon, 15 Feb 2016 10:39:31 +0100 Clemens Ladisch wrote: > > you need to explicitly limit a subquery that is a field and must > > only ever return 1 result if the where clause is ambiguous about it > > Not in SQLite. (It ignores superfluous rows, and returns NULL if > there are no rows.)

[sqlite] IS a SQLite db of small size as good as reliable cache?

On Mon, 01 Feb 2016 06:39:05 -0700 "Keith Medcalf" wrote: > OS/2 had IBM cache technology in it which worked properly. I remember OS/2. I remember that, like VMS, you could back up the whole OS to ... well, floppies, I suppose, and later restore them to brand new drive, with nothing else

[sqlite] Is the first column of a composite primary key, special?

On Tue, 2 Feb 2016 16:19:07 +0100 Yannick Duch?ne wrote: > There are also representations. Sometimes there is not really a > value, just an identity which is the only thing offering > sense/meaning, and what may be erroneously seen as a value is rather > a representation. Representation is

<    1   2   3   4   5   6   7   8   9   10   >