[sqlite] UPSERT and overlapping UNIQUE indices

Hello, A minor observation: UPSERT makes indices to be strictly and sparingly endowed with UNIQUEness. For example: uniqueness + partitioning: CREATE TABLE t(num UNIQUE, cnt DEFAULT 1); CREATE UNIQUE INDEX idx_neg ON t(num) WHERE num < 0; CREATE UNIQUE INDEX idx_pos ON t(num) WHERE num >= 0;

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Hi Richard, Is there any issue with this compile option ? Thanks, Sathish On Wed 16 May, 2018, 5:30 PM Sathish Kumar, wrote: > sqlite> SELECT sqlite_source_id(); > > 2017-06-08 14:26:16 > 0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b > > >

Re: [sqlite] Weird query plan when sorting by a joined column

On 16.05.2018 12:32, Richard Hipp wrote: > On 5/16/18, Torsten Landschoff wrote: >> >> Actually it is: SQLite will generate the same query plan for both >> queries as long as no statistics data is available. Adding the >> statistics information will lead to the more

Re: [sqlite] Is this really the best way to do this?

SELECT coalsce((select action from blocked where mail='...'), 'OK') as action; --- 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

Re: [sqlite] Lots of enhancements coming to version 3.24.0 - please test

Hey On Wed, 9 May 2018, 13:12 Seiji Amalai, wrote: > Nonono lol > > On Tue, 8 May 2018, 15:42 Richard Hipp, wrote: > >> There are a lot of important changes in the SQLite code base since the >> previous release. See >>

Re: [sqlite] Is this really the best way to do this?

On 2018/05/16 6:21 PM, Dominique Devienne wrote: On Wed, May 16, 2018 at 6:12 PM Clemens Ladisch wrote: Stephen Chrzanowski wrote: On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch wrote: SELECT action FROM blocked WHERE email = ? UNION ALL SELECT

Re: [sqlite] Is this really the best way to do this?

On Wed, May 16, 2018 at 6:12 PM Clemens Ladisch wrote: > Stephen Chrzanowski wrote: > > On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch > wrote: > >> SELECT action FROM blocked WHERE email = ? > >> UNION ALL > >> SELECT 'OK' > >> LIMIT 1; > > > > Out of

Re: [sqlite] Is this really the best way to do this?

Stephen Chrzanowski wrote: > On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch wrote: >> SELECT action FROM blocked WHERE email = ? >> UNION ALL >> SELECT 'OK' >> LIMIT 1; > > Out of curiosity, where's the guarantee that the OK won't be displayed if > an email is found? The

Re: [sqlite] Is this really the best way to do this?

Clemens, Paul S, Abroży, Thank you for taking the time to reply. I'll look at each of them and see what makes the most sense to me. It's always good to ask the people here as there is a wealth of experience. As suggestions popped in, I kept thinking go the unix competition which was to

Re: [sqlite] Is this really the best way to do this?

Out of curiosity, where's the guarantee that the OK won't be displayed if an email is found? Do all unions show up in the return result based on where in the query they're called? On Wed, May 16, 2018 at 6:25 AM, Clemens Ladisch wrote: > > SELECT action FROM blocked WHERE

Re: [sqlite] LIMIT with OFFSET is not working in android platform

sqlite> SELECT sqlite_source_id(); 2017-06-08 14:26:16 0ee482a1e0eae22e08edc8978c9733a96603d4509645f348ebf55b579e89636b sqlite> PRAGMA compile_options; COMPILER=clang-5.0.300080 DEFAULT_SYNCHRONOUS=2 DEFAULT_WAL_SYNCHRONOUS=2 ENABLE_FTS3 ENABLE_FTS5 ENABLE_JSON1 ENABLE_RTREE

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On 05/16/2018 06:20 PM, Sathish Kumar wrote: Hi, Sorry, Please find the full sequence below, it is taken from S9 device running with Android 8.0. This is a bit strange. The VM code looks correct. Try running a [make clean] to ensure you're getting a clean build. If that doesn't work,

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On 5/16/18, Sathish Kumar wrote: > Samsung S9 device with Android 8.0 What output do you see from the following commands: SELECT sqlite_source_id(); PRAGMA compile_options; -- D. Richard Hipp d...@sqlite.org ___

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Samsung S9 device with Android 8.0 On Wed, May 16, 2018 at 4:50 PM, Sathish Kumar wrote: > Hi, > Sorry, Please find the full sequence below, it is taken from S9 device > running with Android 8.0. > > sqlite> create table test (id integer primary key autoincrement, name

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Hi, Sorry, Please find the full sequence below, it is taken from S9 device running with Android 8.0. sqlite> create table test (id integer primary key autoincrement, name text); sqlite> insert into test (name) values('a'); sqlite> insert into test (name) values('a'); sqlite> insert into test

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On 5/16/18, Sathish Kumar wrote: > I just skipped those statements in mail, but i inserted 7 rows. Please send *exactly* the sequence of commands that you think are producing an incorrect answer. Please leave nothing to chance, or to interpretation. -- D. Richard Hipp

Re: [sqlite] query planning - covering indices

On 5/15/18, Peter Johnson wrote: > > My understanding is that covering indices are more efficient, as the table > itself does not need to be scanned when all the required columns exist in > the covering index? That is often the case, but there are exceptions. > > Is it

Re: [sqlite] LIMIT with OFFSET is not working in android platform

I just skipped those statements in mail, but i inserted 7 rows. On Wed 16 May, 2018, 4:06 PM Richard Hipp, wrote: > On 5/16/18, Sathish Kumar wrote: > > Hi, > > Please find the below example. > > > > sqlite> create table test (id integer primary key

Re: [sqlite] LIMIT with OFFSET is not working in android platform

Hi, Please find the below example. sqlite> create table test (id integer primary key autoincrement, name text); sqlite> insert into test (name) values('a'); sqlite> insert into test (name) values('a'); sqlite> select count(*) from test; 7 sqlite> select * from test limit 10 offset 2; 2|a 3|a

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On 5/16/18, Sathish Kumar wrote: > Hi, > Please find the below example. > > sqlite> create table test (id integer primary key autoincrement, name > text); > sqlite> insert into test (name) values('a'); > sqlite> insert into test (name) values('a'); > sqlite> select

Re: [sqlite] Weird query plan when sorting by a joined column

On 5/16/18, Torsten Landschoff wrote: > > Actually it is: SQLite will generate the same query plan for both > queries as long as no statistics data is available. Adding the > statistics information will lead to the more complex and slow query plan > which is why I

Re: [sqlite] Is this really the best way to do this?

Rob Willett wrote: > select CASE > WHEN EXISTS (select 1 from blocked where email = > 'rwillett.dr...@example.com') > THEN (select action from blocked where email = > 'rwillett.dr...@example.com') > ELSE 'OK' > END SELECT action FROM blocked WHERE email = ? UNION ALL SELECT 'OK'

Re: [sqlite] LIMIT with OFFSET is not working in android platform

On 5/16/18, Sathish Kumar wrote: > Hi All, > OFFSET clause is not working as expected in android platform. > > [ ISSUE ] : eg. select * from test limit 2 offset 5; > > [ Expected Result ] : This query should return two rows starting from index > 6. > > [ Actual Result

Re: [sqlite] Is this really the best way to do this?

or more in tune with the default value in the schema SELECT CASE count(*) WHEN 0 THEN 'Ok' ELSE action END FROM blocked WHERE email = 'rwillett.dr...@example.com'; Paul www.sandersonforensics.com SQLite Forensics Book On 16 May 2018 at 09:22, Rob

[sqlite] LIMIT with OFFSET is not working in android platform

Hi All, OFFSET clause is not working as expected in android platform. [ ISSUE ] : eg. select * from test limit 2 offset 5; [ Expected Result ] : This query should return two rows starting from index 6. [ Actual Result ] : It always returns rows starting from index 2, even though it had

[sqlite] query planning - covering indices

I noticed that the query planner favours the primary index when a covering index exists which can satisfy the same query. My understanding is that covering indices are more efficient, as the table itself does not need to be scanned when all the required columns exist in the covering index? Is it

Re: [sqlite] Is this really the best way to do this?

How about something like SELECT CASE count(*) WHEN 0 THEN 'Discard' ELSE 'Ok' END FROM blocked WHERE email = 'rwillett.dr...@example.com'; Paul www.sandersonforensics.com SQLite Forensics Book On 16 May 2018 at 10:35, Abroży Nieprzełoży <

Re: [sqlite] Is this really the best way to do this?

I would make something like this: CREATE TABLE blocked( mail TEXT PRIMARY KEY, action TEXT NOT NULL DEFAULT 'DISCARD' ) WITHOUT ROWID; INSERT INTO blocked(mail) VALUES('badm...@example.com'); SELECT coalesce(action, 'OK') AS action FROM (SELECT 'goodm...@example.com' AS mail)

Re: [sqlite] Weird query plan when sorting by a joined column

On 16.05.2018 01:48, Keith Medcalf wrote: > > There is no LEFT join visible to anyone except someone who is in love > with LEFT joins without knowing what one is. Sorry, I corrupted my text while trying to simplify it. I originally wrote "since this is not a left outer join", but tried to remove

Re: [sqlite] Weird query plan when sorting by a joined column

On 16.05.2018 01:24, Simon Slavin wrote: >> I would have expected that b.id is basically an alias for d.id as > > Obvious to a good programmer. Not obvious to SQLite. Your SELECT is > selecting from the "base" table, but trying to order by a table > JOINed to it. The fact that d.id is always

[sqlite] Is this really the best way to do this?

Hi, I'm experimenting with an email server, Mail In a Box. It's a free product and gives you a mail server in a box with SSL certificates, multiple domains and seems to work well. One thing it doesn't do is allow people to specify emails to block. It uses SQLite to store the underlying data