Re: [sqlite] Detecting erroneous multi-row subqueries

2018-11-29 Thread J. King
On 2018-11-29 21:17:59, "Keith Medcalf" wrote: while I cannot comment on the "reverse_unordered_select" pragma, you are getting the result you are because you are using union rather than union all. The actual query didn't use a union and wasn't affected by the pragma either (presumably for s

Re: [sqlite] Detecting erroneous multi-row subqueries

2018-11-29 Thread Keith Medcalf
On Thursday, 29 November, 2018 18:24, J. King wrote: >select (select 'foo' union select 'bar') || 'bar'; >SQLite 3.25.3 returns 'barbar' (regardless of the value of the >reverse_unordered_select pragma) while PostgreSQL 11 refuses to >process the query unless the subquery is reduced to a single

Re: [sqlite] Detecting erroneous multi-row subqueries

2018-11-29 Thread J. King
On 2018-11-29 20:56:13, "Richard Hipp" wrote: On 11/29/18, J. King wrote: Is it possible to make SQLite fail like PostgreSQL does? That is possible in theory, but how many of the millions of existing applications would that break? I was wondering more if it's -currently- possible via

Re: [sqlite] Detecting erroneous multi-row subqueries

2018-11-29 Thread Richard Hipp
On 11/29/18, J. King wrote: > Is it possible to make SQLite fail like > PostgreSQL does? That is possible in theory, but how many of the millions of existing applications would that break? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing li

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

2018-11-29 Thread AJ Miles
Ah, this tool seems very handy. For those curious, I'll paste the results below. The index approximately doubles the storage size, but I am intentionally making that tradeoff to avoid the slow down when enforcing a unique/primary key on the Reference table while inserting. -AJ /** Disk-Space Util

[sqlite] Detecting erroneous multi-row subqueries

2018-11-29 Thread J. King
Good people of sqlite-users, I've been porting an application originally designed for SQLite to PostgreSQL, and the latter failed on a test case where the former does not. While I've encountered many more such failures during the process than I'd like, in this case I think PostgreSQL's behaviou

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

2018-11-29 Thread Richard Hipp
On 11/29/18, AJ Miles wrote: > > I misspoke when I said it was 200-300gb for just the integers -- my brain > was a little fuzzy. Right now, the integer table has 3 columns: rowid, > integer, and foreign row id to a second table (so 8byte int, 8 byte int, > variable byte int I believe, unless the r

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

2018-11-29 Thread AJ Miles
Simon, Dominique, and Arun - Thank you for the feedback. I'll leave the threading off for inserts since I've seen DB contention issues with other multithreaded/multiprocessed attempts. The indexing improvement is nice though. I misspoke when I said it was 200-300gb for just the integers -- my bra

Re: [sqlite] Getting SQLITE_LOCKED

2018-11-29 Thread Keith Medcalf
Ya must be using shared cache as well? --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Prajeesh Prakash >

Re: [sqlite] function named geopolyCosine is a misnomer

2018-11-29 Thread Richard Hipp
On 11/29/18, Thomas Kurz wrote: > Could it be that the one angle is north-based, the other one east-based? Ha Ha. No, Graham is right. I started out writing a Cosine function, then I switched it over to be a Sine function but failed to change the name. A rename has now been committed to trunk,

Re: [sqlite] function named geopolyCosine is a misnomer

2018-11-29 Thread Thomas Kurz
Could it be that the one angle is north-based, the other one east-based? - Original Message - From: Graham Hardman To: SQLite mailing list Sent: Thursday, November 29, 2018, 12:46:05 Subject: [sqlite] function named geopolyCosine is a misnomer Hi, I was very interested in the numeri

Re: [sqlite] SQLITE_LOCKED and SQLITE_BUSY

2018-11-29 Thread Simon Slavin
On 29 Nov 2018, at 11:35am, Prajeesh Prakash wrote: > I am writing to a table 1 and reading from table 2 both operation are from > different DB connection i am getting SQLITE_LOCKED SQLite locks the entire database. It does not lock each table independently. > and when i try to read and writ

[sqlite] Strange query result

2018-11-29 Thread Maurice van der Stee
Thanks , that explains it. Leanerd something today. Sorry to reply out of thread, but I am subscribed to the digest, so haven't yet gotten the original messages. -- === Maurice van der Stee (s...@planet.nl) ___ sqlite-users m

Re: [sqlite] [EXTERNAL] Strange query results.

2018-11-29 Thread Hick Gunter
Just as expected. You are comparing INTEGER to TEXT values. '4' is a text value, not an integer. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Maurice van der Stee Gesendet: Donnerstag, 29. November 2018 14:46 An: sqlite-

Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

2018-11-29 Thread Dan Kennedy
On 11/29/2018 07:10 PM, szmate1618 wrote: I'm afraid I still didn't make any progress on this. It looks to me that PRAGMA foreign_key_check; and the deferred foreign key mechanism sometimes contradict each other, consequently at least one of these features must have a bug, but I don't know for s

Re: [sqlite] Strange query results.

2018-11-29 Thread Olivier Mascia
> Le 29 nov. 2018 à 14:45, Maurice van der Stee a écrit : > > This reproduces the issue for me: > > create table config (config_package integer, config_flags integer); > insert into config (config_package, config_flags) values (1, 2); > insert into config (config_package, config_flags) values (2

Re: [sqlite] Strange query results.

2018-11-29 Thread Jay Kreibich
> On Nov 29, 2018, at 7:45 AM, Maurice van der Stee wrote: > > This reproduces the issue for me: > > create table config (config_package integer, config_flags integer); > insert into config (config_package, config_flags) values (1, 2); > insert into config (config_package, config_flags) values

Re: [sqlite] [EXTERNAL] Getting SQLITE_LOCKED

2018-11-29 Thread Prajeesh Prakash
Hi Team, Actually this is the case. From my main application i created two thread (One thread is for read from table 1 on the DB and another thread is for write into a separate table (Say table 2) on the DB). Similary i created two separate connection to DB (For these two thread to operate)usin

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

2018-11-29 Thread Arun - Siara Logics (cc)
Without going into details of what your structure is, I suggest you look into "without rowid" option when creating the table. It reduces the overhead drastically. On Thu, 29 Nov 2018 18:59:26 +0530 Dominique Devienne wrote > On Wed, Nov 28, 2018 at 6:03 PM AJ M wrote: > > > [.

[sqlite] Strange query results.

2018-11-29 Thread Maurice van der Stee
This reproduces the issue for me: create table config (config_package integer, config_flags integer); insert into config (config_package, config_flags) values (1, 2); insert into config (config_package, config_flags) values (2, 4); insert into config (config_package, config_flags) values (3, 6); s

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

2018-11-29 Thread Dominique Devienne
On Wed, Nov 28, 2018 at 6:03 PM AJ M wrote: > [...] The data comes out to 10 billion rows of an 8 byte signed integer (~200-300 gb pre-index), and while insertion takes ~6 hours, indexing takes > 8 hours by > itself. [...] query speed is fine as-is. [...] > Hi AJ. Your message is quite intrigui

Re: [sqlite] [EXTERNAL] Strange query result

2018-11-29 Thread Hick Gunter
Works as expected. Maybe you are not declaring the correct types and end up comparing text to integer (1 is not equal to '1', unless an implicit or explicit cast is involved). asql> create temp table flags( flag integer ); asql> insert into flags values (0),(1),(2),(3),(4),(5),(6),(7); rows inse

Re: [sqlite] Strange query result

2018-11-29 Thread Richard Hipp
On 11/29/18, Maurice van der Stee wrote: > This returns 0 rows: > > select config_flags from config where (config_flags & > WANTED_FLAG) = WANTED_FLAG. Can you provide a concrete example - something we can actually run in the sqlite3 command-line shell? -- D. Richard Hipp d...@sqlite.org

[sqlite] Strange query result

2018-11-29 Thread Maurice van der Stee
Hello, I have a sqlite database containing a table config with amongst others the column conf_flags. I want to select entries from this table which have one or more specific flags set. If do select config_flags from config where config_flags & WANTED_FLAG != 0. I get the correct result. Now th

Re: [sqlite] [EXTERNAL] Getting SQLITE_LOCKED

2018-11-29 Thread Hick Gunter
Locking (errors SQLITE_LOCKED and SQLITE_BUSY) are about **WHAT** you are doing to the DB. There can be at most 1 thread (same process or different process, it does not matter) with a write transaction on any DB file at any time. Typically, a transaction involves multiple calls to sqlite functio

Re: [sqlite] Getting SQLITE_LOCKED

2018-11-29 Thread Prajeesh Prakash
Sorry i was a wrong attempt. > > On November 29, 2018 at 5:07 PM Prajeesh Prakash > wrote: > > Hi Team, > > I am writing to a table 1 and reading from table 2 both operation are > from different DB connection i am getting SQLITE_LOCKED > > and when i try to read and write th

Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

2018-11-29 Thread szmate1618
I'm afraid I still didn't make any progress on this. It looks to me that PRAGMA foreign_key_check; and the deferred foreign key mechanism sometimes contradict each other, consequently at least one of these features must have a bug, but I don't know for sure which one, so I can't risk using any of

[sqlite] function named geopolyCosine is a misnomer

2018-11-29 Thread Graham Hardman
Hi, I was very interested in the numerical approximation used in the function named geopolyCosine and after a bit of on-line research decided to test it's accuracy myself. What I discovered was that the function in fact returns the sin value rather than the cosine value. This is quickly noticed

[sqlite] Getting SQLITE_LOCKED

2018-11-29 Thread Prajeesh Prakash
Hi Team, I am writing to a table 1 and reading from table 2 both operation are from different DB connection i am getting SQLITE_LOCKED and when i try to read and write the same table from different connection i am getting same error. (I know read and write are the incompatible at same time). W

Re: [sqlite] SQLITE_LOCKED and SQLITE_BUSY

2018-11-29 Thread Prajeesh Prakash
Hi Team, I am writing to a table 1 and reading from table 2 both operation are from different DB connection i am getting SQLITE_LOCKED and when i try to read and write the same table from different connection i am getting same error. (I know read and write are the incompatible at same time). W