Re: [sqlite] Database locking problems

On 2019/01/19 4:55 PM, Richard Hipp wrote: On 1/19/19, R Smith wrote: Hi RIchard, any chance this BEGIN CONCURRENT branch will make it into a next SQLite standard release? You can always pull it from the branch. The branch will not go away. Thank you kindly, this much is known. Allow me

Re: [sqlite] Database locking problems

On 2019/01/19 5:20 PM, Richard Hipp wrote: On 1/19/19, R Smith wrote: "merged at some stage" and "merged into the next release" are different things. The latter will likely not happen, but I cannot say about the former, just yet. Thank you - I believe my original mail

Re: [sqlite] SQLite slow when lots of tables

On 2019/01/28 6:17 PM, mzz...@libero.it wrote: I developed an application that need to create 1 table with thousand of rows every time when a certain event occours. This works in a good way, but when the number of the tables become huge (about 15000/2 tables) the first DataBase reading que

Re: [sqlite] SQLite slow when lots of tables

When the same solution is offered in rapid intervals, it must be the correct one then! :) To elaborate on what Chris mentioned regarding relational database methodology and related tables, here is why and a good description of how to do that correctly: https://en.wikipedia.org/wiki/First_norm

Re: [sqlite] Bug due to left join strength reduction optimization?

On 2019/02/05 10:13 AM, Dominique Devienne wrote: On Tue, Feb 5, 2019 at 7:47 AM Keith Medcalf wrote: sqlite> select ... from tab left join tab as tab2 on 0 ... Can someone please educate me on this {{ on 0 }} join "condition" ? I'm not following what the intended meaning is... Must have

Re: [sqlite] Min/Max and skip-scan optimizations

On 2019/02/05 4:46 PM, Simon Slavin wrote: On 5 Feb 2019, at 8:59am, Rowan Worth wrote: What is stopping sqlite's query planner from taking advantage of the index, which it has chosen to use for the query, to also satisfy the ORDER BY? I suspect that, given the data in the table, the inde

Re: [sqlite] Min/Max and skip-scan optimizations

On 2019/02/06 12:12 AM, Gerlando Falauto wrote: The use case involves retaining as much data as the storage can possibly hold (so a bunch of gigabytes). I could've just used directories and logfiles instead of abusing a relational database but I just thought it would more convenient to issue a

Re: [sqlite] Choosing one value from the group with priority

On 2019/02/12 1:13 PM, Ivan Krylov wrote: Hi! I have a table of some values obtained from different sources: create table test ( id, source_id, value, primary key(id, source_id) ); insert into test values (1, 1, 11), (1, 2, 12), (1, 3, 13), (2, 1, 21),

Re: [sqlite] Choosing one value from the group with priority

On 2019/02/12 7:29 PM, Ivan Krylov wrote: I can supply a list of source_ids in order of decreasing priority from an array in my application, though I lose cross-database portability (is there a portable way to pass an array to a parameterized query, though?)... There is no fully portable wa

Re: [sqlite] Tips for index creation

On 2019/02/13 2:39 PM, Thomas Kurz wrote: Hello, I apologize right at the beginning, because this is a real noob question. But I don't have much experience with performance optimization and indexes, so I'm hoping for some useful hints what indexes to create. I have queries like this: SELECT

Re: [sqlite] How to get aggregate without reducing number of rows (repeats are ok)?

On 2019/02/18 11:24 AM, Clemens Ladisch wrote: Rocky Ji wrote: But everyone advices against nested select statements. Who? I've heard rumors that older version of the Oracle query optimizer did worse with subqueries than with joins, but such advice is not necessarily correct for SQLite. +1

Re: [sqlite] Reading a table from inside a scalar function

On 2019/02/18 12:06 PM, Arun - Siara Logics (cc) wrote: Yes it is possible, but then your UDF is unlikely to be *Deterministic*. By design, I intend to make sure that for any given input the function always returns the same value. If the dependent rows are missing or change over time, then it w

Re: [sqlite] Reading a table from inside a scalar function

On 2019/02/18 1:02 PM, Dominique Devienne wrote: On Mon, Feb 18, 2019 at 11:48 AM R Smith wrote: Note that in a transaction without WAL mode ... your query may or may not "see" data that is older, Hmmm, I don't think so. Journal mode, WAL or not, doesn't matter.

Re: [sqlite] Equiv stmts, different explain plans

On 2019/03/05 4:06 AM, kk wrote: On 05/03/2019 01:33, Richard Hipp wrote: create table t1(c,d); create table t2(c,d); explain select * from t1     where c=1 and d in (select d from t2 where c=1); explain select * from t1     where c=1 and d in (select d from t2 where t2.c=t1.c); DRH, many

Re: [sqlite] Corruption of database when renaming a table

For a start, do not use single quotes for table names. In SQL, DB object identifiers can either be unquoted or contained in double-quotes. SQLite specifically even allows backticks or square brackets for compatibility, but nobody likes single quotes. I.e. change this: drop table 'fs_params_20

Re: [sqlite] INSERTing from another table data

I see the "ignore this" retraction, but I thought to mention the following any way, for future reference: Three ways in SQL to create and fill a table with data from another: 1. CREATE ... AS Example: CREATE TABLE newTable AS SELECT a,b,c FROM oldTable; (This method has the advantage of being f

Re: [sqlite] Incorrect result from INNER JOIN when column has a value of "WHITE"

On 2019/03/21 2:31 AM, Steve Horvath wrote: I found an issue with SQLite 3.27.2 and is also existent in 3.20.1. Of the four queries below, the second query should have returned the same result set as the first query. As a side note, I also tried creating the tables with no primary keys and got t

Re: [sqlite] Incorrect result from INNER JOIN when column has a value of "WHITE"

On 2019/03/22 5:30 PM, Richard Hipp wrote: More recent versions of SQLite do issue a warning on the sqlite3_log interface if you use a double-quoted string literal. But not many people look at warnings, it turns out. I don't see these in my logs from the standard sqlite3_log interface using

Re: [sqlite] What is the most commonly used file extension for sqlite3 database files?

On 2019/03/23 6:06 PM, Peng Yu wrote: Hi, I see a variety of extensions for sqlite3 database files, such as .db, .db3, .sqlite3 and .sq3. Is there a most commonly used/accepted convention for the extension? Thanks. It's a convention-less thing, as others have mentioned. However, I can answer

Re: [sqlite] Feature request, sqlite3_stmt_action

On 2019/03/28 9:07 AM, Olivier Mascia wrote: Le 27 mars 2019 à 18:04, siscia a écrit : I would like to propose a function (named `sqlite3_stmt_action` for the sake of discussion) that allow to understand if a specific statement is either a SELECT, UPDATE, DELETE or INSERT. There is probably a

Re: [sqlite] Why no persistent user settings in a database file?

Hi Tom, The settings you mention, such as .headers and .mode (along with a slew of others) are usability settings contained in the Command-Line facility (the CLI) offered by the makers of SQLite. It is however by far not the only such interface, nor is it in any way related to the data in th

Re: [sqlite] export tables

On 2019/04/08 3:03 PM, Mohsen Pahlevanzadeh wrote: Hello, I need to export some tables with dot command, How I do it? That depends on what you mean by "export"? Do you wish to export the table structure (schema)? - in which case the dot commands can be used easily, or do you wish to export

Re: [sqlite] Option to control implicit casting

On 2019/04/09 5:08 AM, Joshua Thomas Wise wrote: SQLite3 uses manifest typing, which is great and provides a ton of flexibility. However, due to implicit casting rules, many operations can accidentally result in a different value than what was desired. If programmers don’t guard against every

Re: [sqlite] slow join, fast subselect

On 2019/04/17 10:55 AM, Poor Yorick wrote: I've used the following two test queries in a version of sqlite built against a recent checkout of trunk, and also another recent version of sqlite. a.ref is indexed. The subselect query is faster than the join query -- two orders of magnitude faster o

Re: [sqlite] slow join, fast subselect

On 2019/04/17 1:23 PM, Poor Yorick wrote: That's an apt and accessible description of the issue, but at the denotational level the meanings of the queries are in fact identical under the conditions you enumerated. Ideally sqlite would notice and adjust its query plan accordingly. Ideally yes,

Re: [sqlite] slow join, fast subselect

Also, let me just add (in case it sounded different) - We definitely do not wish to document the "why an optimization opportunity might not be feasible" in any way that would discourage anyone from submitting such a possible optimization opportunity. That would work against the axiomatic premis

Re: [sqlite] Custom collation of blobs

To add to Dominique's suggestion, we use this approach a lot and have sort of standardized it internally. Of course 1NF dictates that this is not the real RDBMS way, but sometimes you need blobs because you just do. I'm sure you already have figured out how to do it sans blob collations, and t

Re: [sqlite] Does SQLITE ever optimize index creation based on another index?

On 2019/04/30 2:10 AM, Deon Brewis wrote: Given the SQL below, FooX is a covered index for x on Foo. I want to create FooXB as a second index on x in Foo. Since 'x' is covered on FooX it should be cheaper to build FooXB from index FooX, than from table Foo. However, as far as I can tell from t

Re: [sqlite] Getting the week of the month from strftime or date functions

On 2019/05/07 3:07 PM, Jose Isaias Cabrera wrote: Warren Young, on Monday, May 6, 2019 09:15 PM, wrote... On May 6, 2019, at 11:58 AM, Jose Isaias Cabrera wrote: someday, as John Lennon sang, "...the world will live as one." ;-) Okay, but one *what*? Serious question. Yeah, if I have to expla

Re: [sqlite] Limiting the result set size at each recursive step in a CTE

On 2019/05/07 7:57 PM, Thomas Zimmermann wrote: Hi! Sometimes it is desirable to limit the size of the queue¹ in a recursive CTE//... CREATE TABLE comment (     comment_id INTEGER PRIMARY KEY,     parent_comment_id INTEGER REFERENCES comment (comment_id),     created_at INTEGER NOT NULL -- t

Re: [sqlite] Mixing main and temp databases in foreign keys is not supported

On 2019/05/13 12:56 AM, Manuel Rigger wrote: Hi everyone, It seems that a table created in the temp database cannot have a parent table that is created in the main database and vice versa: PRAGMA foreign_keys=true; CREATE TABLE t0 (c0 PRIMARY KEY); CREATE TEMP TABLE t1 (c0, FOREIGN KEY(c0) REFE

Re: [sqlite] Mixing main and temp databases in foreign keys is not supported

On 2019/05/13 11:42 AM, Manuel Rigger wrote: Hi Ryan, I hope my question did not offend you. I didn't expect that the answer to this question would be considered to be that obvious. Goodness, I was not offended and apologies if my reply read in that way - it was just an honest account - and -

Re: [sqlite] SETting a value to a field with multiple conditions

Table-valued syntax is your friend:   -- SQLite version 3.27.2  [ Release: 2019-02-25 ]  on SQLitespeed version 2.1.2.47.   -- create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate); insert

Re: [sqlite] SETting a value to a field with multiple conditions

I of course forgot to remove 'p005' from the list (luckily David didn't!), so the query should have been: UPDATE t AS tx SET b = 'z' WHERE (a,idate) = (SELECT ty.a,MAX(ty.idate) FROM t AS ty WHERE ty.a = tx.a GROUP BY ty.a) AND a < 'p005'; which works well here (picking the minimal route), bu

Re: [sqlite] SQL Features That SQLite Does Not Implement

How we can anyone document functions that do not exist?, they are infinite and many of them (most) are not currently known to humanity. Also, if somewhere it said "SQLite cannot do sqr() it would lie - sure vanilla sqlite might not, but there are many ways in which it does. !. Add-ons - there

Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

Ok, but you can't have it both ways. Either you want to write generic use-everywhere SQL, in which case you need to stick to the generic included-everywhere functions, OR You want to write specialized specific queries that use either your own or other UDF's, in which case you must be able to

Re: [sqlite] [EXTERNAL] Re: SQL Features That SQLite Does Not Implement

On 2019/05/23 1:26 PM, J. King wrote: On May 23, 2019 6:46:52 a.m. EDT, R Smith wrote: This is SQLite. Perhaps some of us could collaborate on a fork called SQLbloat //.. I find this a little condescending. There's a lot of reasons to like SQLite, and the aspect that sways me more

Re: [sqlite] SQLITE Return Exponential value in the latest version(3.28.0) rather than integer number .

Hi Bhavesh, This is a common thing with 3rd party systems and engines (like SQlite) etc. One day, you made a simple query which did not specify the output specifically (or the format, or the order, or such), BUT you liked the output as it was on that day, and then possibly made your program ex

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

On 2019/06/12 3:35 PM, Richard Hipp wrote: IEEE754 floating point numbers have separate representations for +0.0 and -0.0. As currently implemented, SQLite always display both quantities as just "0.0". Question: Should SQLite be enhanced to show -0.0 as "-0.0"? Or, would that create unnecessa

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

On 2019/06/12 4:39 PM, Jonathan Brandmeyer wrote: For an SQL engine, the next-best-thing to strict binary IEEE754 is not sloppy binary IEEE754, its probably strict decimal IEEE754. I very much agree on this point, however, unlike the -0.0/+0.0 change, changing to strict full IEEE754 conversion

Re: [sqlite] Should SQLite distinguish between +0.0 and -0.0 on output?

On 2019/06/12 6:04 PM, Simon Slavin wrote: On 12 Jun 2019, at 4:35pm, R Smith wrote: (maybe a sort of CAST target or collation rather than a column "Type") That's an interesting idea. With a REAL value you get the same things when you print -0.0 and +0.0. But if you CAST i

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

On 2019/06/13 4:44 PM, Doug Currie wrote: Except by the rules of IEEE (as I understand them) -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero" Except that 0.0 is also an approximation to zero, not "true zero." Consider that 1/-0.0 is -inf whereas 1/0.0 is +int I do not kn

Re: [sqlite] [SPAM?] Re: [EXTERNAL] Re: Should SQLite distinguish between +0.0 and -0.0 on output?

On 2019/06/14 4:23 AM, Richard Damon wrote: On 6/13/19 10:51 AM, R Smith wrote: On 2019/06/13 4:44 PM, Doug Currie wrote: Except by the rules of IEEE (as I understand them) -0.0 < 0.0 is FALSE, so -0.0 is NOT "definitely left of true zero" Except that 0.0 is also an approxim

Re: [sqlite] the sqlite3 documentation would be pretty good if it wasn't tragic...

On 2019/06/23 8:14 AM, Ben Earhart wrote: ...that the person(s) that has no problem writing small, but solid, walls of technical detail and drawing intricate circularly recursive syntax diagrams which require multiple levels of detail to coherently represent, can't be bothered to write example sq

Re: [sqlite] the sqlite3 documentation would be pretty good if itwasn't tragic...

On 2019/06/27 10:57 AM, Adolfo J. Millan wrote: I must apologize, because I feel that muy comment has been misinterpreted, proably because my weak english. ... If this had been a technical or theoretical issue, I would probably have refrained from expressing my opinion, due to my lack of ade

Re: [sqlite] hex and char functions

On 2017/08/07 5:29 PM, x wrote: Apologies, I should have said I was using c++ builder Berlin on windows 10 and that UnicodeString was UTF16. I thought I had learned enough about this string lunacy to get by but finding out that the UTF8 code for the UTF16 code \u0085 is in fact \uc285 has tip

Re: [sqlite] hex and char functions

On 2017/08/07 9:01 PM, x wrote: Thanks Ryan for going to the trouble of typing that out. Hope you’re not a one fingered typewriter like myself. The borland related stuff is welcome but I still can’t say I’m any less confused by it all. I’m having a bad day today. I’ve spent most of it trying

Re: [sqlite] Packing integer primary key with field bits

The "anyone" that does this already is called "SQLite". It doesn't amalgamate the INT fields as such, but it stores INTEGER values in only as much bits as is needed, so it achieves the goal of being more memory-efficient. So a 0, 1 or 2 takes up much less space than say a 786587626 or other hi

Re: [sqlite] Packing integer primary key with field bits

On 2017/08/10 1:19 PM, x wrote: Thanks for the replies. I’m not sure I agree with Gunter and Ryan though. I’m thinking about this more from the gain in speed rather than saving space. To clarify, I’m suggesting replacing a compound key (made up of several integer cols) with an integer primary

Re: [sqlite] Packing integer primary key with field bits

On 2017/08/10 4:51 PM, x wrote: Valid point on the intended range Gunter. I don’t know enough about sqlite to fully understand your index cell paragraph. I thought the way sqlite worked was e.g. to get the value of the 3rd column it had to read the lengths of col1 & col2 so it knows where co

Re: [sqlite] Packing integer primary key with field bits

On 2017/08/10 5:54 PM, x wrote: Ryan, I know what you’re saying about the list of bytes but I’d like it confirmed that the indexes are not stored in a format similar to the data. Oh it's similar, just not the same. I looked it up: http://sqlite.org/fileformat.html Fascinating reading. Take spe

Re: [sqlite] Packing integer primary key with field bits

On 2017/08/11 10:21 AM, x wrote: I’m wishing I had kept my mouth shut now 😊 Never do that. We do see some questions asked ad-nauseum (or silly ones which could have been answered by the fleetest of Google searches) and some people probably find some irritation from it, but most of us don't

Re: [sqlite] What's the level of B+-Tree ?

On 2017/08/11 11:08 AM, Clemens Ladisch wrote: james ni wrote: As in the example that I provided, there are 4 cells in a single btree page. So there must be some mechanism to determine hoe many keys that one cell can own. I want to know exactly the very value and just how to change the value t

Re: [sqlite] transfer records with foreign key

On 2017/08/11 7:50 PM, Roman Fleysher wrote: Dear SQLiters, I have two tables linked by a foreign key, linkID. I need to transfer content of these two tables into two corresponding tables in another database preserving the link. However, the second database already has records and numeric va

Re: [sqlite] Fwd: How can I make this faster?

On 2017/08/14 9:29 AM, Simon Slavin wrote: On 14 Aug 2017, at 7:52am, Clemens Ladisch wrote: Wrap everything into a single transaction. and CREATE the INDEXes after doing the INSERTs: BEGIN CREATE TABLE … INSERT … CREATE INDEX … COMMIT B

Re: [sqlite] Explain results help...

On 2017/08/17 2:40 PM, jose isaias cabrera wrote: Jens Alfke wrote... On Aug 16, 2017, at 8:36 PM, jose isaias cabrera wrote: The following query is taking a bit too long for my taste. I did a quick query with explain and I got this… The output of EXPLAIN QUERY PLAN is a lot easier to

Re: [sqlite] Error trying to do inner joins

On 2017/08/17 8:54 PM, Lars Frederiksen wrote: Hello, I have a small db with 3 tables: CREATE TABLE IF NOT EXISTS "Verb_Saetn"( VS_ID INT PRIMARY KEY NOT NULL, VERBUM_ID INT NOT NULL, SAETNING_ID INT NOT NULL, FOREIGN KEY(VERBUM_ID) REFERENCES Verbum(VERBUM_ID), FOREIGN KEY

Re: [sqlite] Enforcing uniqueness from multiple indexes

On 2017/08/18 1:47 PM, Richard Hipp wrote: On 8/18/17, Wout Mertens wrote: So, bottom line, is there a way to insert or replace a row so that first the id constraint is observed (replacing a previous row with the same id), and then the k constraint is verified (failing to replace if k is alrea

Re: [sqlite] Enforcing uniqueness from multiple indexes

On 2017/08/18 6:08 PM, R Smith wrote: Isn't this what conflict clauses on constraints are for? Apologies, I usually add the test-case scripts in case anyone else wish to test it or similar, the case in question herewith added below: -- SQLite version 3.17.0 [ Release: 2017-

Re: [sqlite] Do you really need an ORDER BY after a GROUP BY

On 2017/08/24 4:48 PM, Cecil Westerhof wrote: ​Yes, and the EXPLAIN QUERY PLAN shows that. But that leads to my next question: With a scan the whole table is used. In the first situation I can understand that the index is used, because that saves the sort, which can be significant. But why is

Re: [sqlite] Date time functions not working

Hi Matthew, Do not wish to sound like a forum troll, but your descriptions are a bit loosely worded making it difficult to follow, but we'd really like to help, so... Could you give us a small bit of real sample data from your table(s), and the REAL query you use, and perhaps the output you

Re: [sqlite] Why is Sqlite mediatype not registered at iana

On 2017/08/25 4:57 PM, BRUCE KAPITO wrote: please take me of your list anything about me wishing to be a member is fraudulent Did someone subscribe poor Bruce as a prank? I find it hard to fathom a less awful prank than a subscription to this list... Either way, Bruce, there is a link at t

Re: [sqlite] Issue with updating database content (C++)

Perhaps: DELETE FROM BC WHERE PK = '%q'; Also note, the DB size may or may not decrease when deleting, it clears data, not space. You can use this SQL: VACUUM; to get rid of empty space in the DB file and re-pack it correctly. On 2017/08/30 1:19 PM, Ali Dorri wrote: Hi, Thanks, now it

Re: [sqlite] Determine SQLite data type after UDF conversion

Not 100% sure I follow what you mean to achieve, but would: Select tbl, CAST(BlobAsText(sample) AS TEXT) AS SampleText from sqlite_stat4 work for you? On 2017/08/30 1:20 PM, Bart Smissaert wrote: Say I have a query like this: Select tbl, BlobAsText(sample) from sqlite_stat4 Where BlobAsText i

Re: [sqlite] Issue with updating database content (C++)

Clarification: When I said: You can use this SQL: VACUUM; to get rid of empty space in the DB file and re-pack it correctly. by "correctly" I really meant "tightly". There is nothing incorrect about the data before the vacuum, of course. ___

Re: [sqlite] Amalgamation compilation with SQLITE_THREADSAFE=0

On 2017/09/01 6:53 PM, Jacky Lam wrote: What I am understood from the answer is explicit code must be used during creating db, for example, auto_vacuum=FULL. If no, the file size will not reduce even deleting a number of records and this is normal. Yes. If you create the database and run the S

Re: [sqlite] SQLITE bug

I think this has been answered already in that it is not a bug, but I would like to mention why it works the way it works. Imagine you have some tables, like a list of clients (table: client) and a list of contacts which are foreign-keyed (or simply used in conjunction) with the client parent

Re: [sqlite] SQLITE bug

On 2017/09/03 4:16 PM, Joseph L. Casale wrote: -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: Sunday, September 3, 2017 7:51 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] SQLITE bug Lastly, a

Re: [sqlite] Syntax error using CTE and UPDATE

You are essentially trying to use a CTE (which for the intents and purposes of the UPDATE SQL is just the same as using any other table) inside an UPDATE statement as if it is the main referenced table. In an UPDATE or INSERT however, there can only ever be 1 single main referenced table (i.e.

Re: [sqlite] Syntax error using CTE and UPDATE

On 2017/09/04 12:46 PM, David Wellman wrote: Hi Ryan, Thanks for that. It is certainly valid syntax and I'll do some testing to check that it gives me the correct answer. It's always a pleasure. Your email has 'crossed in the post' with my second one and you've answered something that I

Re: [sqlite] Syntax error using CTE and UPDATE

One last thing I forgot to mention, on the topic of making INSERT and UPDATE easy - If you are using SQLite 3.15 or later, you can use Row-value functionality to UPDATE several fields in one go from a sub query. An example of how such an update query might look: WITH CTE(ID, ta, tb, tc) AS

Re: [sqlite] Getting number of rows with NULL

On 2017/09/05 10:00 PM, Stephen Chrzanowski wrote: On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: Your query will either return 1, or, 0. Perhaps this is the opportune moment to learn. Test the theo

Re: [sqlite] Is there a performance difference between COUNT(*) and COUNT(name)

On 2017/09/05 10:21 PM, Cecil Westerhof wrote: I want to know the number of teas I have in stock. For this I use: SELECT COUNT(Tea) FROM teaInStock Tea cannot be NULL, so this is the same as: SELECT COUNT(*) FROM teaInStock ​But I find the first more clear. I almost always see the second va

Re: [sqlite] Getting number of rows with NULL

On 2017/09/05 10:13 PM, John McKown wrote: On Tue, Sep 5, 2017 at 3:00 PM, Stephen Chrzanowski wrote: On behalf of Cecil, the fault in that logic is that count(*) returns the number of rows in that table, not whether there is a hole "somewhere: Your query will either return 1, or, 0. ​I

Re: [sqlite] Join, Union, Subquery or what?

These suggestions from Clemens will work exactly as you want, but I need to add that it assumes the records all perfectly match between the tables, even flags, ID column etc. This means, if it doesn't work as you expect, you can still use the exact same methods but you can of course include th

[sqlite] JOIN vs. INTERSECT speed and efficiency

Hi all, For those interested, after a recent thread from a poster called Joe asking about the most efficient way to find values that coincide from two separate tables, a response from Clemens Ladisch and a further elaboration from myself suggested the following: SELECT v FROM t1 INTERSECT SE

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/06 8:26 PM, Nico Williams wrote: On Wed, Sep 06, 2017 at 08:05:57PM +0200, R Smith wrote: -- Another interesting thing to note: The INTERSECT test produces ORDERED -- output, which suggests that an ORDER-BY addition to the query would -- favour the INTERSECT method. Nothing about

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/06 11:17 PM, Nico Williams wrote: If you'll redo this I'd urge you to use WITHOUT ROWIDS. First, that's almost always the right thing to do anyways. Second, it won't perform worse but likely will perform better. Third, write performance definitely should improve with WITHOUT ROWID

Re: [sqlite] Should the INTEGER not be cast to a REAL

On 2017/09/06 11:37 PM, Cecil Westerhof wrote: But should in the first case the 0 not be cast to a 0.0? What makes you believe SQLite should massage the data into specific types for you without you requesting it explicitly? In fact, that would consume valuable extra CPU cycles and would def

Re: [sqlite] Should the INTEGER not be cast to a REAL

On 2017/09/06 11:58 PM, R Smith wrote: Your CHECK constraint should really find that the value is acceptable when it is either a REAL, OR an INT, because both those types of data satisfies your requirement. To be specific, this should work for you: CREATE TABLE weights( float REAL

Re: [sqlite] Should the INTEGER not be cast to a REAL

On 2017/09/07 12:35 AM, Cecil Westerhof wrote: ​It does not, but this does: CREATE TABLE weights( float REAL, CONSTRAINT float CHECK(TYPEOF(float) IN ("real","integer")) ); Instead of "int" you need "integer". yes of course... My bad, sorry, but at least you've solved it :) _

Re: [sqlite] Fwd: Problem on Windows 10 machines

On 2017/09/07 11:09 AM, Bart Smissaert wrote: SQLite is a third party product, and would not be pre-installed by Microsoft. I thought this was standard now on Win10. Not that I think it is relevant with my problem. SQLite is actually found on Windows installations, but only in support to

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/07 3:43 AM, Keith Medcalf wrote: Try the same test using 147 columns in each table. Exactly the plan for this weekend :) 1 column is rather trivial. Even a kindergarten kid could do it in no time using crayons and the wall. So? That is non-sequitur, I am sure given enough cray

Re: [sqlite] JOIN vs. INTERSECT speed and efficiency

On 2017/09/07 6:31 PM, David Raymond wrote: Although it may not translate as well to the more complex examples, would you also consider adding the IN operator to your tests? I found for example that "select v from t1 where v in t2;" did even better than the join or the intersect. Will do. The

[sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

Full tests completed with findings ranging from less interesting to exposing a rather significant inefficiency in SQLite. I won't post all the tests because that would take far too much space, in stead I will simply discuss the experiment and findings and post the test script so that anyone ca

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

On 2017/09/09 9:20 PM, Nico Williams wrote: On Sat, Sep 09, 2017 at 05:56:03PM +0200, R Smith wrote: *Findings* pertaining to JOIN, INTERSECT and WHERE IN (...) type queries on tables *WITHOUT Row_ids*: (This is the full test posted below because it is the one that matters most) INTERSECT AND

Re: [sqlite] JOIN vs. INTERSECT vs. WHERE IN (...) - speed and efficiency differences

g the fuel octane rating. I'm saying there is something wrong under the hood. Cheers, Ryan On 2017/09/10 11:28 AM, Clemens Ladisch wrote: R Smith wrote: I am using 151 columns for both tests. The only thing that changes between the two scripts are the words "WITHOUT ROWID" being adde

Re: [sqlite] Need help with SQL query

On 2017/09/11 5:35 AM, Vikas Aditya wrote: Hi All, I need some help in figuring our right query syntax for querying items from two tables. We have two tables. One of the table has list of items. And Second table has additional attributes. CREATE TABLE ITEM ( ID INTEGER, FIELD0 TEXT FIELD1 T

Re: [sqlite] Need help with SQL query

Correction: On 2017/09/11 6:43 AM, R Smith wrote: SELECT I.ID, ISNULL(A.VALUE,'[No Value]')   FROM ITEM AS I   LEFT JOIN ATTRIBUTES AS A ON A.ITEM_ID = I.ID  WHERE A.key='abc' OR A.key IS NULL  ORDER BY A.VALUE; There is of course no such thing as SORT BY in SQL, it

Re: [sqlite] SQL prepare statement - help

On 2017/09/14 3:20 AM, Papa wrote: I think the problem with this function is my lack of good understanding of the SQL. What I intend to do is to write one int64_t to the database, but I don't know how to write the proper sql statement, making sqlite3_prepare_v2 return a non SQLITE_OK value.

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

Richard, wouldn't it be possible to supply a wrapping function (perhaps a hint function, like the likelihood() function), that takes another function as a parameter and then ensuring that THAT gets calculated only once? SELECT calc_once(slow(10))   FROM xxx Note that if the same function

Re: [sqlite] [EXTERNAL] Common subexpression optimization of deterministic functions

On 2017/09/14 7:10 PM, Simon Slavin wrote: On 14 Sep 2017, at 5:55pm, R Smith wrote: Richard, wouldn't it be possible to supply a wrapping function (perhaps a hint function, like the likelihood() function), that takes another function as a parameter and then ensuring that THAT

Re: [sqlite] [EXTERNAL] Re: sqlite3_stmt limitations

On Wed, Sep 20, 2017 at 1:41 PM, heribert wrote: Do i have to open a database connection for each threat? Only if you work at the NSA. (It's the answer everyone else wanted to post, but maturely refrained from doing... I was weak, sorry!) ___ s

Re: [sqlite] Create table - Error code: 21

On 2017/09/21 10:07 AM, Papa wrote: The code shows what I have done to create a table, but ::sqlite3_prepare_v2 tells me: --- Exception --- Error message from SQLite3 - bad parameter or other API misuse Error code: 21 Perhaps show us the entire

Re: [sqlite] How does SQLite handle \r and \n in values?

On 2017/09/21 3:56 PM, heribert wrote: Hello, i attempt to make a INSERT with a TEXT field containing a string with \r and \n characters, but it fails (Strings without \r and \n characters are inserted correct). The sqlite3_exec results with error... i think the reason are the special charact

Re: [sqlite] Data types for date and time functions

On 2017/09/25 10:12 AM, David Wellman wrote: Hi, We're designing a new feature which will involve a lot of date/time calculations which we intend to do within the SQLite engine// The question has come up as to how we should store date/time values in our tables? Basically how should we

Re: [sqlite] Data types for date and time functions

On 2017/09/25 2:23 PM, Stephan Buchert wrote: I was just going to write that you can easily convert an MS serial date value stored in Sqlite to a date string (using 40777 as example): sqlite> select date('1899-12-31', 40777||' days'); 2011-08-23 However, according to https://support.office.com

Re: [sqlite] Data types for date and time functions

On 2017/09/25 3:46 PM, R Smith wrote: PS: I refer to "Excel" only, but the problem probably persists in all of MS Office, though I didn't check. Thanks to Igor's post and some quick testing, I can confirm that it seems to only affect Excel,

Re: [sqlite] All of a sudden I must commit after every single change and I don't know what happened.

Web App you say I imagine you are using some wrapper, possibly a JAVA one, PHP or a PEARL one, I think your web service may have updated so the supported wrapper updated with possibly a new default setting or such. Those wrappers usually have a setting/property called "AutoCommit" which /

Re: [sqlite] All of a sudden I must commit after every single change and I don't know what happened.

On 2017/09/27 8:35 AM, bensonbear wrote: I didn't even know that outside transactions, SQLite by default itself commits automatically after each statement, which I would not have wanted. However, the python module by default implicitly inserts "begin"s so that statements are grouped into a tra

<    1   2   3   4   5   6   7   8   >