Re: [sqlite] Deterministic random sampling via SELECT

On 7 Nov 2019, at 1:56pm, David Raymond wrote: > Others will correct me if I'm wrong on that. No correction, but I wanted to add something. According to the theory of how SQL (not just SQLite, SQL) works, tables have no order. You can, in theory, query a table of 100 rows with SELECT a,b

Re: [sqlite] database disk image is malformed

This line stood out: > The main process opens the databases and then forks the other processes which can then perform database operations using the already opened databases. From https://sqlite.org/howtocorrupt.html#_carrying_an_open_database_connection_across_a_fork_ : > Do not open an SQLite

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

For that matter, is the Solaris box x86 or sparc? If the latter, this particular code path obviously wouldn't ever be used on it. On Thu, Nov 7, 2019, 8:10 AM Dennis Clarke wrote: > On 2019-11-07 11:01, Shawn Wagner wrote: > > Compiling with -std=iso9899:1999 is the culprit. Strict c99 mode

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

Another option (Which the devs might consider) is to replace use of the asm keyword with __asm__, which is a form of the extension understood even in strict mode. (I have no idea why gcc doesn't offer intrinsics for bit rotation, as it's a common task and shouldn't require hacks like this to

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

... Just don't use strict c99 mode when compiling with gcc? Drop the -std argument from your CFLAGS to use the default (gnu11 since gcc 5) or explicitly use gnu99, which gives you that version of the C standard + gcc extensions. (Not that they have anything to do with the problem, but compiling

[sqlite] How to tell sqlite to only use tcl from the system?

I am seeing confusion here on a few systems wherein this : #include "sqliteInt.h" #if defined(INCLUDE_SQLITE_TCL_H) # include "sqlite_tcl.h" #else # include "tcl.h" #endif is in a pile of the test code. For some reasons I am seeing undefined symbols ( sched_yield ) and problems after

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

Compiling with -std=iso9899:1999 is the culprit. Strict c99 mode disables gcc extensions like inline asm. On Thu, Nov 7, 2019, 7:57 AM Dennis Clarke wrote: > > While building sqlite from the zip file sqlite-src-3300100.zip I saw > what seems to be a normal configure and then madness : > > >

Re: [sqlite] SQLite with branching

Hi! I am the creator of LiteTree (also LiteReplica, LiteSync and 3 new products that will be released soon). When I was planning to add branching I discovered many ways to implement it. I selected the one that satisfied performance over disk usage. It can also be implemented the other way

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

On 2019-11-07 11:01, Shawn Wagner wrote: Compiling with -std=iso9899:1999 is the culprit. Strict c99 mode disables gcc extensions like inline asm. OKay but I have no issues doing a compile on a Solaris server using the Oracle Studio 12.6 tools and strict C99. So how is C99 the issue? Dennis

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

Does that toolchain use gcc, or a different compiler? If gcc, are you using the same CFLAGS as on the redhat box (you're turning on a bunch of extra non-default options there)? On Thu, Nov 7, 2019, 8:10 AM Dennis Clarke wrote: > On 2019-11-07 11:01, Shawn Wagner wrote: > > Compiling with

[sqlite] database disk image is malformed

Dear List, I'm developing software which keeps parameters and real-time data in SQLite databases on a x86_64/linux system. I am getting "database disk image is malformed" errors from SQLite when using select. Some select operations succeed, some fail. This happens on multiple systems. I would

Re: [sqlite] Deterministic random sampling via SELECT

Along those lines SQLite includes the reverse_unordered_selects pragma https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects which will flip the order it sends rows in queries that don't explicitly specify an ordering. It's there to assist you in finding spots in your code where

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

On 2019-11-07 11:15, Shawn Wagner wrote: Does that toolchain use gcc, or a different compiler? If gcc, are you using the same CFLAGS as on the redhat box (you're turning on a bunch of extra non-default options there)? I don't see how --with-threads can be a concern. The other options do not

Re: [sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

On 2019-11-07 11:44, Shawn Wagner wrote: ... Just don't use strict c99 mode when compiling with gcc? Drop the -std argument from your CFLAGS to use the default (gnu11 since gcc 5) or explicitly use gnu99, which gives you that version of the C standard + gcc extensions. (Not that they have

Re: [sqlite] SQLite with branching

> On Nov 7, 2019, at 9:02 AM, Bernardo Ramos wrote: > > If you are interested in just the performance without the branching feature, > there are at least 3 options: > > 1. SQLigthning: I was thinking in updating it to the last version of SQLite That would be awesome! I have looked at it a

[sqlite] sqlite-src-3300100 on RHEL 7.4 toss mad errors about 'asm'

While building sqlite from the zip file sqlite-src-3300100.zip I saw what seems to be a normal configure and then madness : boe13$ pwd /opt/bw/build/sqlite-src-3300100_rhel_74_3.10.0-693.el7.x86_64.001 boe13$ ./configure --prefix=/opt/bw --enable-shared --enable-static --enable-readline

[sqlite] Deterministic random sampling via SELECT

I'm trying sample a (deterministically) random subset of a SELECT query, the most common solution on the internet to get random samples seems to be "SELECT * FROM (...) ORDER BY RANDOM() LIMIT n;" (this already has some question marks, since it relies on seeding RANDOM and knowing the RANDOM

Re: [sqlite] Deterministic random sampling via SELECT

On Thursday, 7 November, 2019 04:55, Merijn Verstraaten wrote: >I'm trying sample a (deterministically) random subset of a SELECT query, You cannot have something that is both RANDOM and DETERMINISTIC at the same time. >the most common solution on the internet to get random samples seems to

Re: [sqlite] Deterministic random sampling via SELECT

"So, is this behaviour documented/guaranteed somewhere?" Short version is: Nope. The engine is free to do whatever it wants as long as it gives the correct result in the end. Consider a simple select * from foo where predicate order by non_indexed_field; Since there is no nice ordering of the

Re: [sqlite] Deterministic random sampling via SELECT

> On 7 Nov 2019, at 19:16, David Raymond wrote: > > Along those lines SQLite includes the reverse_unordered_selects pragma > https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects > which will flip the order it sends rows in queries that don't explicitly > specify an ordering.

[sqlite] Feature request: (VALUES (1), (2), (3)) AS t(n)

Good day! It's my first time writing to a mail list for a very long time, so I may not get this right the first time. I would like to request the ability to write: SELECT * FROM (VALUES (1, 1), (2, 2)) AS t(a, b) ; Instead of: SELECT t.column1 AS a, t.column2 AS b FROM (VALUES

Re: [sqlite] Feature request: (VALUES (1), (2), (3)) AS t(n)

How about something like: with t(a, b) as (values (1, 1), (2, 2)) select a, b from t; -- 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 On >Behalf Of Ainar Garipov >Sent:

Re: [sqlite] Deterministic random sampling via SELECT

> > Regarding: "So far the only suggestion was "use some non-deterministic > random sampling method and store the result", but since my samples are > large and I have lots of them, this would balloon my storage by >100x and I > don't have the available storage to make that work." But Keith

Re: [sqlite] Deterministic random sampling via SELECT

In the very old days before computers were common, a random number table appeared at the back of many statistical texts. This was used to select a series of random numbers which would then be used as look-up indices into some other data set. You could do the same: 1. generate a list of

Re: [sqlite] Deterministic random sampling via SELECT

On Thu, Nov 7, 2019 at 4:23 PM Richard Damon wrote: > > One thought would be to generate a ‘hash’ from part of the record, maybe > the record ID, and select records based on that value. The simplest would > be something like id%100 == 0 would get you 1% of the records. That > admittedly isn’t

Re: [sqlite] Deterministic random sampling via SELECT

> On Nov 7, 2019, at 2:15 PM, Merijn Verstraaten wrote: > >  >> On 7 Nov 2019, at 19:16, David Raymond wrote: >> >> Along those lines SQLite includes the reverse_unordered_selects pragma >> https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects >> which will flip the order it

Re: [sqlite] Deterministic random sampling via SELECT

On 11/7/19 5:13 PM, Doug Currie wrote: > On Thu, Nov 7, 2019 at 4:23 PM Richard Damon > wrote: > >> One thought would be to generate a ‘hash’ from part of the record, maybe >> the record ID, and select records based on that value. The simplest would >> be something like id%100 == 0 would get you

Re: [sqlite] database is empty but intact

There are multiple programs working in parallel. 750 of them from several compute nodes. I use command line sqlite3, so, sqlite itself it not a suspect. FLoM is like a file lock: flom argumentsToFLoMIncludingTimeout -- sqlite3 "sqlStatement;" I am using linux, I think this analyzer is

[sqlite] database is empty but intact

Dear SQLiters, I am using command line sqlite3 with -vfs unix-none. This disables locking within SQLite. Instead, locking is provided externally by FLoM (distributed file lock manager). I asked questions in a thread "disable file locking mechanism over the network". It is possible that FLoM

Re: [sqlite] database is empty but intact

On 8 Nov 2019, at 12:40am, Roman Fleysher wrote: > I am using linux, I think this analyzer is windows only. "Precompiled Binaries for Linux" > Maybe I should ask a different question: Would knowing answer to my question > (is it possible for DB to become

Re: [sqlite] Deterministic random sampling via SELECT

We went off on a tangent, apologies. If you have contiguous integer primary keys, you could randomly sample that range of integers, then pull the records with those keys. Or in your external language of choice, sample the integers from 1 to the record count deterministically, select ordered by

Re: [sqlite] database is empty but intact

On 8 Nov 2019, at 12:03am, Roman Fleysher wrote: > I am using command line sqlite3 with -vfs unix-none. This disables locking > within SQLite. Instead, locking is provided externally by FLoM (distributed > file lock manager). I asked questions in a thread "disable file locking > mechanism