[sqlite] UPSERT and overlapping UNIQUE indices

[Cezary H. Noweta]

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;

Now ``INSERT INTO t(num) VALUES(10) ON CONFLICT(num) DO UPDATE SET 
cnt=cnt+1;'' fails due to a fact that ``idx_pos'' will not pass. It is a 
frequent and common habit to put ``UNIQUE'' everywhere an entity is 
unique. From SQLite 3.24, above--mentioned, partial indices (and 
everything what is not strictly designed to enforce an uniqueness) 
should be non-UNIQUE ones. IMHO, this fact is worth of recalling in the 
doc of UPSERT clause as the fact can become a source of a confusion.


-- best regards

Cezary H. Noweta
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Sathish Kumar]

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
>
>
> sqlite> PRAGMA compile_options;
>
> COMPILER=clang-5.0.300080
>
> DEFAULT_SYNCHRONOUS=2
>
> DEFAULT_WAL_SYNCHRONOUS=2
>
> ENABLE_FTS3
>
> ENABLE_FTS5
>
> ENABLE_JSON1
>
> ENABLE_RTREE
>
> SYSTEM_MALLOC
>
> TEMP_STORE=3
>
> THREADSAFE=1
>
> sqlite>
>
>
> On Wed, May 16, 2018 at 5:29 PM, Richard Hipp  wrote:
>
>> 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
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Torsten Landschoff]

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 complex and slow query plan
>> which is why I kept the statistics in the example SQL.
> 
> Can you please send the output of ".fullschema" so that I can try to
> reproduce your problem?
> 

Here you go:

---
torsten.landschoff@horatio:~$ /opt/sqlite3/bin/sqlite3 demo.db
SQLite version 3.23.2 2018-05-09 02:23:29
Enter ".help" for usage hints.
sqlite> .fullschema
CREATE TABLE base (
id INTEGER NOT NULL PRIMARY KEY,
modification_time DATETIME NOT NULL
);
CREATE UNIQUE INDEX ix_base_mtime ON base (modification_time);
CREATE TABLE derived (id INTEGER NOT NULL PRIMARY KEY);
ANALYZE sqlite_master;
ANALYZE sqlite_master;
ANALYZE sqlite_master;
---

Please note that I included a full example to reproduce the problem just
by running

---
sqlite3 < demo.sql
---

It's in my own reply to the original email.

Thank you for looking at this!

Greetings, Torsten

-- 
$---+1+2+3+4+5+6+

SCALE GmbH
Niederlassung Dresden
Torsten Landschoff
Pohlandstraße 19
01309 Dresden

Tel: +49-351-312002-10
Fax: +49-351-312002-29

SCALE GmbH
Registergericht und Sitz: Ingolstadt, HRB 6384
Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

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 [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Rob Willett
>Sent: Wednesday, 16 May, 2018 02:22
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [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 necessary to power
>Postfix.
>Dr Richard Hipp, add another new application to your list :)
>
>I've worked out how Postfix calls SQLite (pretty easy), worked out
>what
>I want to do, and am trying to write a single line of SQL that does
>it.
>
>The need is that the SQL must generate a single string answer
>depending
>on the email address that is fed in through the query. There are no
>options here, it must return a value even if there is nothing in the
>table. It is not practical to add a table with every email address
>that
>returns OK.
>
>For this example the only strings it can return are 'OK' and
>'DISCARD',
>though the RFC allows other strings. If a query is done on an email
>and
>it is blocked then it must return DISCARD (or an action in the action
>column. If no email is in the table matching then it must return
>'OK'.
>
>As a piece of pseudo code this would ne
>
>function GetMailStatus(emailAddress)
>{
>   IF emailAddress is present in blocked THEN
>   return action associated with emailAddress -- Action is
>normally
>DISCARD
>
>  return 'OK'
>}
>
>I've created the table
>
>CREATE TABLE blocked (id INTEGER PRIMARY KEY AUTOINCREMENT,
>  email TEXT NOT NULL UNIQUE ,
>   action TEXT NOT NULL DEFAULT 'DISCARD')
>
>I can insert values
>
>insert into blocked (email) values('rwillett.dr...@example.com')
>insert into blocked (email) values('rwillett+dr...@example.com')
>
>And this is the code that I have written that works but it looks poor
>to
>my untutored eyes
>
>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
>
>
>In the Postfix query I'll replace
>
>email = 'rwillett.dr...@example.com'
>
>with email = '%s'
>
>so that the email address is passed in.
>
>My worry is that the query has two selects and 'feels' bad. It works
>but
>feels dirty...
>
>Is there a better way under SQLite?
>
>Any suggestions welcomed.
>
>Rob
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Seiji Amalai]

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
>> https://www.sqlite.org/draft/releaselog/current.html for the latest
>> summary.
>>
>> All of our legacy tests pass and many new test cases have been added.
>> Nevertheless, your testing of SQLite in real-world applications is
>> greatly appreciated.  Please consider downloading the latest
>> pre-release snapshot (https://www.sqlite.org/download.html) and giving
>> it a whirl.
>>
>> Report any problems or concerns to this mailing list, or directly to me.
>>
>> Thanks.
>> --
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[R Smith]

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 'OK'
LIMIT 1;

Out of curiosity, where's the guarantee that the OK won't be displayed if
an email is found?

The "LIMIT 1" makes the DB stop after the first returned row.
It applies to the entire compound query, not only to the second SELECT.


But does SQL guarantee the first result-set (above the UNION ALL)
must be before the second one (below UNION ALL)? --DD


It always does for a UNION ALL query (not necessarily for UNION only) as 
long as there is no ORDER BY clause, BUT, this is not guaranteed 
behaviour, just happy accident.


To quote the COMPOUND SELECT documentation:
"When three or more simple SELECTs are connected into a compound SELECT, 
they group from left to right. In other words, if "A", "B" and "C" are 
all simple SELECT statements, (A op B op C) is processed as ((A op B) op 
C)."


But then the Order-By documentation states:
"If a SELECT statement that returns more than one row does not have an 
ORDER BY clause, the order in which the rows are returned is undefined. 
Or, if a SELECT statement does have an ORDER BY clause, then the list of 
expressions attached to the ORDER BY determine the order in which rows 
are returned to the user."


The way I do these kinds of queries is force the order, like so:


SELECT 1 AS Idx, action FROM blocked WHERE email = ?
UNION ALL
SELECT 2, 'OK'
ORDER BY 1 LIMIT 1;


This is guaranteed to work always.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Dominique Devienne]

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 curiosity, where's the guarantee that the OK won't be displayed if
> > an email is found?
>
> The "LIMIT 1" makes the DB stop after the first returned row.
> It applies to the entire compound query, not only to the second SELECT.
>

But does SQL guarantee the first result-set (above the UNION ALL)
must be before the second one (below UNION ALL)? --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

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 "LIMIT 1" makes the DB stop after the first returned row.
It applies to the entire compound query, not only to the second SELECT.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Rob Willett]

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 list all the ways to accomplish printing something out, there's 
always another to do it. I will learn here as well.


Many thanks to everybody,

Rob

On 16 May 2018, at 11:25, Clemens Ladisch wrote:


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'
LIMIT 1;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Stephen Chrzanowski]

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 email = ?
> UNION ALL
> SELECT 'OK'
> LIMIT 1;
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Sathish Kumar]

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

SYSTEM_MALLOC

TEMP_STORE=3

THREADSAFE=1

sqlite>


On Wed, May 16, 2018 at 5:29 PM, Richard Hipp  wrote:

> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Dan Kennedy]

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, please try building with SQLITE_DEBUG defined and 
post the results of running the script below. It's working on Android 7 
here.


Dan.



create table test (id integer primary key autoincrement, name text);

insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');
insert into test (name) values('a');

.print "--- select count(*) from test;"
select count(*) from test;
.print "--- select * from test;"
select * from test;
.print "--- select * from test limit 10 offset 2;"
select * from test limit 10 offset 2;
.print "--- select * from test limit 10 offset 5;"
select * from test limit 10 offset 5;
.print "--- select * from test limit 2 offset 3;"
select * from test limit 2 offset 3;
.print "--- EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;"
EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;
.print "--- SELECT * FROM test LIMIT 2 OFFSET 5;"
SELECT * FROM test LIMIT 2 OFFSET 5;
.print "--- with vdbe_trace"
PRAGMA vdbe_trace = 1;
SELECT * FROM test LIMIT 2 OFFSET 5;






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 (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
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
4|a
5|a
6|a
7|a
sqlite> select * from test limit 10 offset 5;
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> select * from test limit 2 offset 3;
2|a
3|a
sqlite> select * from test limit 2 offset 5;
2|a
3|a
sqlite> select * from test limit 2,5;
2|a
3|a
4|a
5|a
6|a
sqlite> select * from test limit 5,2;
2|a
3|a
sqlite> select * from test limit 5,3;
2|a
3|a
4|a
sqlite> select * from test;
1|a
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 14000
1 Integer2 1 000
2 Integer5 2 000
3 MustBeInt  2 0 000
4 OffsetLimit1 3 200
5 OpenRead   0 383   0 2  00
6 Rewind 0 13000
7   IfPos  2 12100
8   Rowid  0 4 000
9   Column 0 1 500
10  ResultRow  4 2 000
11  DecrJumpZero   1 13000
12Next   0 7 001
13Halt   0 0 000
14Transaction0 0 440  01
15Goto   0 1 000

sqlite> .schema test
CREATE TABLE test (id integer primary key autoincrement, name text);

sqlite> PRAGMA table_info(test);
0|id|integer|0||1
1|name|text|0||0

Thanks,
Sathish

On Wed, May 16, 2018 at 4:19 PM, Richard Hipp  wrote:


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
d...@sqlite.org


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Sathish Kumar]

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
> text);
>
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> sqlite> insert into test (name) values('a');
> 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
> 4|a
> 5|a
> 6|a
> 7|a
> sqlite> select * from test limit 10 offset 5;
> 2|a
> 3|a
> 4|a
> 5|a
> 6|a
> 7|a
> sqlite> select * from test limit 2 offset 3;
> 2|a
> 3|a
> sqlite> select * from test limit 2 offset 5;
> 2|a
> 3|a
> sqlite> select * from test limit 2,5;
> 2|a
> 3|a
> 4|a
> 5|a
> 6|a
> sqlite> select * from test limit 5,2;
> 2|a
> 3|a
> sqlite> select * from test limit 5,3;
> 2|a
> 3|a
> 4|a
> sqlite> select * from test;
> 1|a
> 2|a
> 3|a
> 4|a
> 5|a
> 6|a
> 7|a
> sqlite> EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 14000
> 1 Integer2 1 000
> 2 Integer5 2 000
> 3 MustBeInt  2 0 000
> 4 OffsetLimit1 3 200
> 5 OpenRead   0 383   0 2  00
> 6 Rewind 0 13000
> 7   IfPos  2 12100
> 8   Rowid  0 4 000
> 9   Column 0 1 500
> 10  ResultRow  4 2 000
> 11  DecrJumpZero   1 13000
> 12Next   0 7 001
> 13Halt   0 0 000
> 14Transaction0 0 440  01
> 15Goto   0 1 000
>
> sqlite> .schema test
> CREATE TABLE test (id integer primary key autoincrement, name text);
>
> sqlite> PRAGMA table_info(test);
> 0|id|integer|0||1
> 1|name|text|0||0
>
> Thanks,
> Sathish
>
> On Wed, May 16, 2018 at 4:19 PM, Richard Hipp  wrote:
>
>> 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
>> d...@sqlite.org
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Sathish Kumar]

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 (name) values('a');
sqlite> insert into test (name) values('a');
sqlite> insert into test (name) values('a');
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
4|a
5|a
6|a
7|a
sqlite> select * from test limit 10 offset 5;
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> select * from test limit 2 offset 3;
2|a
3|a
sqlite> select * from test limit 2 offset 5;
2|a
3|a
sqlite> select * from test limit 2,5;
2|a
3|a
4|a
5|a
6|a
sqlite> select * from test limit 5,2;
2|a
3|a
sqlite> select * from test limit 5,3;
2|a
3|a
4|a
sqlite> select * from test;
1|a
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 14000
1 Integer2 1 000
2 Integer5 2 000
3 MustBeInt  2 0 000
4 OffsetLimit1 3 200
5 OpenRead   0 383   0 2  00
6 Rewind 0 13000
7   IfPos  2 12100
8   Rowid  0 4 000
9   Column 0 1 500
10  ResultRow  4 2 000
11  DecrJumpZero   1 13000
12Next   0 7 001
13Halt   0 0 000
14Transaction0 0 440  01
15Goto   0 1 000

sqlite> .schema test
CREATE TABLE test (id integer primary key autoincrement, name text);

sqlite> PRAGMA table_info(test);
0|id|integer|0||1
1|name|text|0||0

Thanks,
Sathish

On Wed, May 16, 2018 at 4:19 PM, Richard Hipp  wrote:

> 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
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] query planning - covering indices

[Richard Hipp]

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 correct to say that example 1 is more efficient than the other two
> examples, particularly when there are many columns, all of which are
> covered by the covering index?

No.

>
> *example 1 - using covering index*
>
> CREATE TABLE example (a INTEGER, b INTEGER, c INTEGER);
> CREATE INDEX idx_covering ON example( a, b, c );
> EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1;
>
> 0|0|0|SEARCH TABLE example USING COVERING INDEX idx_covering (a=? AND b=?)
>
> *example2 - using primary key & covering index*
>
> CREATE TABLE example (a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);
> CREATE INDEX idx_covering ON example( a, b, c );
> EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1;
>
> 0|0|0|SEARCH TABLE example USING INTEGER PRIMARY KEY (rowid=?)

An INTEGER PRIMARY KEY *is* a covering index, even though the EXPLAIN
QUERY PLAN output does not identify it as such.

>
> *example3 - using composite primary key & covering index*
>
> CREATE TABLE example (a INTEGER, b INTEGER, c INTEGER, PRIMARY KEY(a, b));
> CREATE INDEX idx_covering ON example( a, b, c );
> EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1;
>
> 0|0|0|SEARCH TABLE example USING INDEX sqlite_autoindex_example_1 (a=? AND
> b=?)

In this case, you have faked out the query planner.  The query planner
prefers to use the index created to implement the PRIMARY KEY since it
is a UNIQUE index that will only return a single row.  The covering
index, on the other hand, might return multiple rows as far as the
query planner knows.  The query planner fails to deduce that the first
two columns of the idx_covering index will be unique due to the
existence of the first index.

A better approach here would be to make the PRIMARY KEY a true PRIMARY
KEY, and not just an alias for a UNIQUE constraint, but adding WITHOUT
ROWID to the end of the CREATE TABLE statement.  The PRIMARY KEY on a
WITHOUT ROWID table is always a covering index (even though the
EXPLAIN QUERY PLAN output does not say so) so you will always end up
using a covering index.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Sathish Kumar]

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 autoincrement, name
> > text);
> > sqlite> insert into test (name) values('a');
> > sqlite> insert into test (name) values('a');
> > sqlite> select count(*) from test;
> > 7
>
> How is it that you have 7 rows in the table after only doing 2 INSERTs?
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Sathish Kumar]

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
4|a
5|a
6|a
7|a
sqlite> select * from test limit 10 offset 5;
2|a
3|a
4|a
5|a
6|a
7|a
sqlite> select * from test limit 2 offset 5;
2|a
3|a
sqlite> select * from test limit 5,3;
2|a
3|a
4|a

sqlite> EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;
addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 14000
1 Integer2 1 000
2 Integer5 2 000
3 MustBeInt  2 0 000
4 OffsetLimit1 3 200
5 OpenRead   0 383   0 2  00
6 Rewind 0 13000
7   IfPos  2 12100
8   Rowid  0 4 000
9   Column 0 1 500
10  ResultRow  4 2 000
11  DecrJumpZero   1 13000
12Next   0 7 001
13Halt   0 0 000
14Transaction0 0 440  01
15Goto   0 1 000
sqlite>

sqlite> .schema test
CREATE TABLE test (id integer primary key autoincrement, name text);

sqlite> PRAGMA table_info(test);
0|id|integer|0||1
1|name|text|0||0
sqlite>

Thanks,
Sathish

On Wed, May 16, 2018 at 3:43 PM, Richard Hipp  wrote:

> 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 ] : It always returns rows starting from index 2, even
> > though it had enough rows in the table. (Please see the below example, it
> > has all the informations).
>
> Unable to reproduce the problem.  The example text came through garbled.
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 count(*) from test;
> 7

How is it that you have 7 rows in the table after only doing 2 INSERTs?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 kept the statistics in the example SQL.

Can you please send the output of ".fullschema" so that I can try to
reproduce your problem?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Clemens Ladisch]

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'
LIMIT 1;


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 ] : It always returns rows starting from index 2, even
> though it had enough rows in the table. (Please see the below example, it
> has all the informations).

Unable to reproduce the problem.  The example text came through garbled.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 Willett  wrote:

> 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 necessary to power Postfix. Dr
> Richard Hipp, add another new application to your list :)
>
> I've worked out how Postfix calls SQLite (pretty easy), worked out what I
> want to do, and am trying to write a single line of SQL that does it.
>
> The need is that the SQL must generate a single string answer depending on
> the email address that is fed in through the query. There are no options
> here, it must return a value even if there is nothing in the table. It is
> not practical to add a table with every email address that returns OK.
>
> For this example the only strings it can return are 'OK' and 'DISCARD',
> though the RFC allows other strings. If a query is done on an email and it
> is blocked then it must return DISCARD (or an action in the action column.
> If no email is in the table matching then it must return 'OK'.
>
> As a piece of pseudo code this would ne
>
> function GetMailStatus(emailAddress)
> {
> IF emailAddress is present in blocked THEN
> return action associated with emailAddress -- Action is
> normally DISCARD
>
>  return 'OK'
> }
>
> I've created the table
>
> CREATE TABLE blocked (id INTEGER PRIMARY KEY AUTOINCREMENT,
>email TEXT NOT NULL UNIQUE ,
>   action TEXT NOT NULL DEFAULT 'DISCARD')
>
> I can insert values
>
> insert into blocked (email) values('rwillett.dr...@example.com')
> insert into blocked (email) values('rwillett+dr...@example.com')
>
> And this is the code that I have written that works but it looks poor to
> my untutored eyes
>
> 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
>
>
> In the Postfix query I'll replace
>
> email = 'rwillett.dr...@example.com'
>
> with email = '%s'
>
> so that the email address is passed in.
>
> My worry is that the query has two selects and 'feels' bad. It works but
> feels dirty...
>
> Is there a better way under SQLite?
>
> Any suggestions welcomed.
>
> Rob
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Sathish Kumar]

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 enough rows in the table. (Please see the below example, it
has all the informations). I cross checked with Mac os behaviour to confirm
and there this query is working as expected.

I took the latest code from sqlite site and compiled for android
platform using below compilation flags.

# If using SEE, uncomment the following:

# LOCAL_CFLAGS += -DSQLITE_HAS_CODEC


#Define HAVE_USLEEP, otherwise ALL sleep() calls take at least 1000ms

LOCAL_CFLAGS += -DHAVE_USLEEP=1


# Enable SQLite extensions.

LOCAL_CFLAGS += -DSQLITE_ENABLE_FTS5

LOCAL_CFLAGS += -DSQLITE_ENABLE_RTREE

LOCAL_CFLAGS += -DSQLITE_ENABLE_JSON1

LOCAL_CFLAGS += -DSQLITE_ENABLE_FTS3


# This is important - it causes SQLite to use memory for temp files. Since

# Android has no globally writable temp directory, if this is not defined
the

# application throws an exception when it tries to create a temp file.

#

LOCAL_CFLAGS += -DSQLITE_TEMP_STORE=3


LOCAL_CFLAGS += -DHAVE_CONFIG_H -DKHTML_NO_EXCEPTIONS -DGKWQ_NO_JAVA

LOCAL_CFLAGS += -DNO_SUPPORT_JS_BINDING -DQT_NO_WHEELEVENT -DKHTML_NO_XBL

LOCAL_CFLAGS += -U__APPLE__

LOCAL_CFLAGS += -DHAVE_STRCHRNUL=0

LOCAL_CFLAGS += -DSQLITE_USE_URI=1

LOCAL_CFLAGS += -Wno-unused-parameter -Wno-int-to-pointer-cast

LOCAL_CFLAGS += -Wno-uninitialized -Wno-parentheses

LOCAL_CPPFLAGS += -Wno-conversion-null



ifeq ($(TARGET_ARCH), arm)

LOCAL_CFLAGS += -DPACKED="__attribute__ ((packed))"

else

LOCAL_CFLAGS += -DPACKED=""

endif

   I verified with *3.19.3, 3.23 and 3.23.1* versions and i am able to
reproduce this issue with all these versions. Please let me know if i need
to enable any flags to make OFFSET clause work in android.

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> ...
sqlite> select * from test limit 10 offset 2;2|a 3|a 4|a 5|a 6|a 7|a
sqlite> select * from test limit 10 offset 5;2|a 3|a 4|a 5|a 6|a 7|a
sqlite> select * from test limit 2 offset 5;2|a 3|a
sqlite> select * from test limit 5,3;2|a 3|a 4|a

sqlite> EXPLAIN SELECT * FROM test LIMIT 2 OFFSET 5;
addr  opcode p1p2p3p4 p5  comment
  -        -  --
-0 Init   0 14000
 1 Integer2 1 000
 2 Integer5 2 000
 3 MustBeInt  2 0 000
 4 OffsetLimit1 3 200
 5 OpenRead   0 383   0 2  00
 6 Rewind 0 13000
 7   IfPos  2 121
00   8   Rowid  0 4 0
  00   9   Column 0 1 5
00   10  ResultRow  4 2 0
  00   11  DecrJumpZero   1 130
00   12Next   0 7 0
01   13Halt   0 0 0
00   14Transaction0 0 440
01   15Goto   0 1 0
00
sqlite>

sqlite> .schema testCREATE TABLE test (id integer primary key
autoincrement, name text);

sqlite> PRAGMA table_info(test);  0|id|integer|0||11|name|text|0||0
sqlite>

Thanks,
Sathish
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] query planning - covering indices

[Peter Johnson]

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 correct to say that example 1 is more efficient than the other two
examples, particularly when there are many columns, all of which are
covered by the covering index?

*example 1 - using covering index*

CREATE TABLE example (a INTEGER, b INTEGER, c INTEGER);
CREATE INDEX idx_covering ON example( a, b, c );
EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1;

0|0|0|SEARCH TABLE example USING COVERING INDEX idx_covering (a=? AND b=?)

*example2 - using primary key & covering index*

CREATE TABLE example (a INTEGER PRIMARY KEY, b INTEGER, c INTEGER);
CREATE INDEX idx_covering ON example( a, b, c );
EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1;

0|0|0|SEARCH TABLE example USING INTEGER PRIMARY KEY (rowid=?)

*example3 - using composite primary key & covering index*

CREATE TABLE example (a INTEGER, b INTEGER, c INTEGER, PRIMARY KEY(a, b));
CREATE INDEX idx_covering ON example( a, b, c );
EXPLAIN QUERY PLAN SELECT a, b, c FROM example WHERE a = 1 AND b = 1;

0|0|0|SEARCH TABLE example USING INDEX sqlite_autoindex_example_1 (a=? AND
b=?)

I also noticed that when using > or < instead of = that the covering index
is used instead of the primary index.

Could someone please help me to understand why it works like this?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Paul Sanderson]

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 <
abrozynieprzelozy314...@gmail.com> wrote:

> 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)
> LEFT JOIN blocked USING(mail);
>
> SELECT coalesce(action, 'OK') AS action
> FROM (SELECT 'badm...@example.com' AS mail)
> LEFT JOIN blocked USING(mail);
>
>
> 2018-05-16 10:22 GMT+02:00, Rob Willett :
> > 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 necessary to power Postfix.
> > Dr Richard Hipp, add another new application to your list :)
> >
> > I've worked out how Postfix calls SQLite (pretty easy), worked out what
> > I want to do, and am trying to write a single line of SQL that does it.
> >
> > The need is that the SQL must generate a single string answer depending
> > on the email address that is fed in through the query. There are no
> > options here, it must return a value even if there is nothing in the
> > table. It is not practical to add a table with every email address that
> > returns OK.
> >
> > For this example the only strings it can return are 'OK' and 'DISCARD',
> > though the RFC allows other strings. If a query is done on an email and
> > it is blocked then it must return DISCARD (or an action in the action
> > column. If no email is in the table matching then it must return 'OK'.
> >
> > As a piece of pseudo code this would ne
> >
> > function GetMailStatus(emailAddress)
> > {
> >   IF emailAddress is present in blocked THEN
> >   return action associated with emailAddress -- Action is
> normally
> > DISCARD
> >
> >   return 'OK'
> > }
> >
> > I've created the table
> >
> > CREATE TABLE blocked (id INTEGER PRIMARY KEY AUTOINCREMENT,
> >  email TEXT NOT NULL UNIQUE ,
> >action TEXT NOT NULL DEFAULT 'DISCARD')
> >
> > I can insert values
> >
> > insert into blocked (email) values('rwillett.dr...@example.com')
> > insert into blocked (email) values('rwillett+dr...@example.com')
> >
> > And this is the code that I have written that works but it looks poor to
> > my untutored eyes
> >
> > 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
> >
> >
> > In the Postfix query I'll replace
> >
> > email = 'rwillett.dr...@example.com'
> >
> > with email = '%s'
> >
> > so that the email address is passed in.
> >
> > My worry is that the query has two selects and 'feels' bad. It works but
> > feels dirty...
> >
> > Is there a better way under SQLite?
> >
> > Any suggestions welcomed.
> >
> > Rob
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Abroży Nieprzełoży]

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)
LEFT JOIN blocked USING(mail);

SELECT coalesce(action, 'OK') AS action
FROM (SELECT 'badm...@example.com' AS mail)
LEFT JOIN blocked USING(mail);


2018-05-16 10:22 GMT+02:00, Rob Willett :
> 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 necessary to power Postfix.
> Dr Richard Hipp, add another new application to your list :)
>
> I've worked out how Postfix calls SQLite (pretty easy), worked out what
> I want to do, and am trying to write a single line of SQL that does it.
>
> The need is that the SQL must generate a single string answer depending
> on the email address that is fed in through the query. There are no
> options here, it must return a value even if there is nothing in the
> table. It is not practical to add a table with every email address that
> returns OK.
>
> For this example the only strings it can return are 'OK' and 'DISCARD',
> though the RFC allows other strings. If a query is done on an email and
> it is blocked then it must return DISCARD (or an action in the action
> column. If no email is in the table matching then it must return 'OK'.
>
> As a piece of pseudo code this would ne
>
> function GetMailStatus(emailAddress)
> {
>   IF emailAddress is present in blocked THEN
>   return action associated with emailAddress -- Action is normally
> DISCARD
>
>   return 'OK'
> }
>
> I've created the table
>
> CREATE TABLE blocked (id INTEGER PRIMARY KEY AUTOINCREMENT,
>  email TEXT NOT NULL UNIQUE ,
>action TEXT NOT NULL DEFAULT 'DISCARD')
>
> I can insert values
>
> insert into blocked (email) values('rwillett.dr...@example.com')
> insert into blocked (email) values('rwillett+dr...@example.com')
>
> And this is the code that I have written that works but it looks poor to
> my untutored eyes
>
> 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
>
>
> In the Postfix query I'll replace
>
> email = 'rwillett.dr...@example.com'
>
> with email = '%s'
>
> so that the email address is passed in.
>
> My worry is that the query has two selects and 'feels' bad. It works but
> feels dirty...
>
> Is there a better way under SQLite?
>
> Any suggestions welcomed.
>
> Rob
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Torsten Landschoff]

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 the
negation.

Actually this is an inner join. Sorry.

> Your query is misformed.  You proper query ought to be:
> 
> select * from base b join derived d using (id) order by id;

If I were in the position to just write this query than this is what I
would write. But sitting behind an ORM this is taken out of my hands.

Or rather it isn't - I just replaced the code with hand-crafted SQL
which is fine and dandy but means that I have code to update if the
schema ever changes.

> There is no b.id nor d.id in the output row.  It is a figment of your
> imagination.  The output row contains an "id".  Of course the problem
> arises because of all the other people who also like to write broken
> SQL and expect it it work.

Yeah. The code above is incredibly broken by having two identical id
columns in the result.

Still it is a mystery to me how this leads the optimizer to come up with
a much worse query plan after the data is analyzed.

> Why do you expect that your brokenness
> should win out over someone else's brokenness?

I don't.

I only wanted to express my surprise how an otherwise really stable
database engine like SQLite is caught off guard when confronted with
less than optimal SQL.


Greetings, Torsten

-- 
$---+1+2+3+4+5+6+

SCALE GmbH
Niederlassung Dresden
Torsten Landschoff
Pohlandstraße 19
01309 Dresden

Tel: +49-351-312002-10
Fax: +49-351-312002-29

SCALE GmbH
Registergericht und Sitz: Ingolstadt, HRB 6384
Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Torsten Landschoff]

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 equal to b.id isn't
> spotted by SQLite.

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 kept the statistics in the example SQL.

> It's a possible enhancement opportunity for SQLite, but the way the
> query is phrased is counter-intuitive and I doubt many people do it.

I wouldn't do it myself actually, it happens to be the way that
SQLAlchemy creates queries for joined-table inheritance. I assume
Hibernate would do the same.

> The processing to spot the opportunity might slow down every JOIN
> operation SQLite performs, so it might not be the right thing for the
> average user.

Again, SQLite does already do this but somehow lets the optimizer spoil
the otherwise perfect original query.


I should have made that more explicit in my original mail but it was
late and I spent the 2 hours before with stripping down our schema and
database to the small reproducing example.

Greetings, Torsten

-- 
$---+1+2+3+4+5+6+

SCALE GmbH
Niederlassung Dresden
Torsten Landschoff
Pohlandstraße 19
01309 Dresden

Tel: +49-351-312002-10
Fax: +49-351-312002-29

SCALE GmbH
Registergericht und Sitz: Ingolstadt, HRB 6384
Geschäftsführer: Dr.-Ing. Heiner Müllerschön, Dipl.-Math. Ulrich Franz
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Rob Willett]

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 necessary to power Postfix. 
Dr Richard Hipp, add another new application to your list :)


I've worked out how Postfix calls SQLite (pretty easy), worked out what 
I want to do, and am trying to write a single line of SQL that does it.


The need is that the SQL must generate a single string answer depending 
on the email address that is fed in through the query. There are no 
options here, it must return a value even if there is nothing in the 
table. It is not practical to add a table with every email address that 
returns OK.


For this example the only strings it can return are 'OK' and 'DISCARD', 
though the RFC allows other strings. If a query is done on an email and 
it is blocked then it must return DISCARD (or an action in the action 
column. If no email is in the table matching then it must return 'OK'.


As a piece of pseudo code this would ne

function GetMailStatus(emailAddress)
{
IF emailAddress is present in blocked THEN
		return action associated with emailAddress -- Action is normally 
DISCARD


 return 'OK'
}

I've created the table

CREATE TABLE blocked (id INTEGER PRIMARY KEY AUTOINCREMENT,
   email TEXT NOT NULL UNIQUE ,
  action TEXT NOT NULL DEFAULT 'DISCARD')

I can insert values

insert into blocked (email) values('rwillett.dr...@example.com')
insert into blocked (email) values('rwillett+dr...@example.com')

And this is the code that I have written that works but it looks poor to 
my untutored eyes


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


In the Postfix query I'll replace

email = 'rwillett.dr...@example.com'

with email = '%s'

so that the email address is passed in.

My worry is that the query has two selects and 'feels' bad. It works but 
feels dirty...


Is there a better way under SQLite?

Any suggestions welcomed.

Rob
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users