[sqlite] Email bounce

[Marc L. Allen]

Everyone,

I apologize for the email bounce notifications. My company was acquired a few 
months back and they’re trying to move us to their email system, so they’re 
trying to support two email addresses.

I hope it’s only for the day, but if it becomes a burden, I can unsubscribe and 
come back when it’s done.

Thanks

Marc


Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Index help...

[Marc L. Allen]

I’m not the expert here, but it appears that the cause is that your looking for 
things greater than some confidence. This forces an index scan. There’s nothing 
that gives a list of different confidences greater than, in this case .8, but 
even if it did, an index scan might be faster than individual lookups. 

The DB has no way of knowing that, after it reaches your high timestamp that it 
should skip to the next confidence value, since it doesn’t know what that next 
value is. So, it has to read all the index records, making the timestamp part 
of the index only used for the value. 

As an example, perform your lookup use confidence = .8 and you may see ta being 
used. 

> On Oct 23, 2018, at 6:48 PM, Hamesh Shah  wrote:
> 
> I need a little help with some strange indexing behaviour.
> 
> I have a table called detected.
> 
> i create a index for:
> id integer, confidence ASC, timestamp ASC
> 
> Then when I query with a simple select from where with integer, then
> confidence, then timestamp in order, for some reason the timestamp index
> isn't used ?
> 
> 
> SEARCH TABLE detected USING COVERING INDEX detected_model_id_confidence_ts
> (model_id=? AND confidence>?)
> 
> 
> I read the website, I tried it many times around and still no joy. I can't
> see why it's not using the timestamp that is already ordered for my sql ts
>> and ts < statement.
> 
> 
> 
> Python versions:
> 
> sqlite3.version 2.6.0 / python api version.
> 
> *sqlite3.sqlite_version 3.24.0*
> 
> 
> table standalone:
> 
> CREATE TABLE detected ( id INTEGER PRIMARY KEY, model_id integer NOT NULL,
> state_id integer NOT NULL, dataset_id integer NOT NULL, class_id integer
> NOT NULL, confidence REAL NOT NULL, ts DATETIME NOT NULL, x0 INTEGER NOT
> NULL, y0 INTEGER NOT NULL, x1 INTEGER NOT NULL, y1 INTEGER NOT NULL,
> file_id INTEGER NOT NULL )
> 
> index creation:
> 
> CREATE INDEX `detected_model_id_confidence_ts` ON `detected` (
> `model_id`,
> `confidence` ASC,
> `ts` ASC
> );
> 
> 
> I can't see the timestamp being used:
> 
> explain query plan
> select distinct ts
> from detected
> where
> model_id = 1
> and
> confidence > 0.8
> and
> ts >  '2018-10-10 01:25:25'
> and
> ts < '2018-10-23 08:10:17'
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] unsubscribe

[Marc L. Allen]

Because it knows you don't really want to leave.

-Original Message-
From: sqlite-users  On Behalf Of 
Rob Dixon
Sent: Tuesday, October 23, 2018 12:41 PM
To: SQLite mailing list 
Subject: Re: [sqlite] unsubscribe

I did that yesterday 3 times, got 3 confirmations and yet..

On Tue, Oct 23, 2018 at 10:38 AM Tim Streater  wrote:

> On 22 Oct 2018, at 20:08, thomgrayr...@printeasy.net wrote:
>
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> Go to that web site, which is shown in every mail you have received 
> from this list.
>
>
>
>
>
> --
> Cheers  --  Tim
> ___
> 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


Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can this be done with SQLite

[Marc L. Allen]

Two options  (one based on Igor's answer)

update playYouTubVideo 
set speed = case when key = 1 then (select speed from playYouTubeVIdeo when key 
= 2) 
  when key = 2 then (select ... when key = 3)
 ...
 when key = 5 then (select ... when key = 1)

I can't recall if the update will actually do that atomically (so that the 
speed for key = 1 is still available).

Option 2:

Since I believe SQLite doesn't support UPDATE FROM, you'll need temporary 
variables somewhere.

If the table is small enough, copy it off and do the updates using the copy as 
a source.  If the table is large, then maybe add in an extra staging field in 
the record?

CREATE TABLE playYouTubeVideo (
key TEXTNOT NULL,
speed   FLOAT   NOT NULL,
tmpSpeed FLOAT NULL,
CONSTRAINT key   CHECK(length(key)  == 1),
CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
PRIMARY KEY(key)
);

Then before doing the cycle,

update playYouTubeVideo set tmpSpeed = speed

Then perform the update as David suggested, but using the tmpSpeed variable.

You can probably optimize that by using just tmpSpeed variable from the start 
or end of the cycle.

Marc


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Cecil Westerhof
Sent: Monday, January 22, 2018 4:37 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Can this be done with SQLite

2018-01-22 21:38 GMT+01:00 David Raymond :

> Unless I'm reading you wrong then just do the normal
>
> begin transaction;
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '2')
> where key = '1';
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '3')
> where key = '2';
> ...
> update playYouTubeVideo set speed = (
> select speed from playYouTubeVideo where key = '5')
> where key = '1';
> commit;
>

​Nope. By the way I see that I did not write it correctly. :'-(

When I do this, I get:
sqlite> SELECT *
   ...> FROM   playYouTubeVideo
   ...> WHERE  key BETWEEN '1' AND '5'
   ...> ;
1|1.0
2|2.0
3|3.0
4|4.0
5|5.0
sqlite> begin transaction;
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '2')
   ...> where key = '1';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '3')
   ...> where key = '2';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '4')
   ...> where key = '3';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '5')
   ...> where key = '4';
sqlite> update playYouTubeVideo set speed = (
   ...> select speed from playYouTubeVideo where key = '1')
   ...> where key = '5';
sqlite> commit;
sqlite> SELECT *
   ...> FROM   playYouTubeVideo
   ...> WHERE  key BETWEEN '1' AND '5'
   ...> ;
1|2.0
2|3.0
3|4.0
4|5.0
5|2.0

But I want the last one needs to be 1.0.
Also, when the range becomes big, it will be a lot of code.


I was hoping I overlooked a smart trick, but I probably need to do it 
programmatically.


-Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Cecil Westerhof
> Sent: Monday, January 22, 2018 3:30 PM
> To: SQLite mailing list
> Subject: [sqlite] Can this be done with SQLite
>
> I have the following table:
> CREATE TABLE playYouTubeVideo (
> key TEXTNOT NULL,
> speed   FLOAT   NOT NULL,
>
> CONSTRAINT key   CHECK(length(key)  == 1),
> CONSTRAINT speed CHECK(TYPEOF(speed) = "real"),
>
> PRIMARY KEY(key)
> );
>
>
> Say I want to rotate a part:
> - The value by key '1' becomes the value by key '2'.
> ​- The value by key '2' becomes the value by key '3'.
> ​- The value by key '3' becomes the value by key '4'.
> - The value by key '4' becomes the value by key '5'.
> - The value by key '5' becomes the value by key '6'.
>
> ​I suppose that I need to do this programmatically, or can this be 
> done with SQL?
>
> And optionally also:
> ​- The value by key '1' becomes the value by key '5'.
> ​
> --
> Cecil Westerhof
> ___
> 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
>



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


Confidentiality notice: This e-mail is intended solely for use of the 
individual or 

Re: [sqlite] Is this a bug with expression evaluation?

[Marc L. Allen]

I just multiply by 1.0

Select column1*(column2 * 1.0 / column3)...

Removing the parentheses only provide the correct results in your example.  
It's still using integer math, it's just performing the multiply first, as per 
order of operations.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Tony Papadimitriou
Sent: Thursday, December 14, 2017 11:36 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Is this a bug with expression evaluation?

I’ve noticed this (very annoying) behavior:

select column1*(24/100) wrong from (values(100));

Removing the parentheses yields the correct result:

select column1*24/100 correct from (values(100));

This obviously behaves like integer math is used and (24/100) gets truncated to 
zero.

If I add a dot to either number (e.g., 24. or 100.) I get the correct result.
But, with named fields, it’s not as easy as adding a dot:

select column1*(column2/column3) wrong from (values(100,24,100)); select 
column1*column2/column3 correct from (values(100,24,100));

So, to get correct answer I have to use a cast for either field?

select column1*(cast(column2 as float)/column3) correct from 
(values(100,24,100));

In this example removing the parentheses is a simple solution.
But if the expression was column1*(1+column2/column3) a cast is the only way?  
(Hope not!)

Anyway, if all this happens to be so by design, is there at least some way to 
default to float math rather than integer?

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


Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] finding the number of records until a value is different

[Marc L. Allen]

Ok I'm on this list because I love reading all the comments and discussion 
about sqlite and DBs in general.  I haven't used sqlite in quite awhile, so I 
don't know how well this will work, but...

Assuming you have a row number as well, such as

CREATE TABLE x
(
row_number int,
bc int
)

Then you can use something like:

SELECT a.last_rn - (SELECT MAX(row_number) FROM x WHERE bc != a.last_bc)
FROM (SELECT TOP 1 bc AS last_bc, row_number AS last_rn FROM x ORDER BY 
row_number DESC) a

I know this works on MS SQL SERVER.  There may be a more efficient way, and 
you'll need to modify it based on size of table and available indexes.  I think 
the above should work fairly well if you have indexes on both the row_number 
and bc.

Marc

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of nitpi...@arcor.de
Sent: Thursday, December 7, 2017 1:46 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] finding the number of records until a value is different

Hi all,

I have a DB i.e. like this:

table values
bc  temp
35  123
35  124
35  123
20  123
12  123
12  123
16  123
35  123
35  123
35  123
35  123
35  123

The value in temp (or all the other columns) is not of interest.

Now I a looking for the basic concept how to count the number of rows where 
bc=bc_of_last_row after the last different bc.

SELECT COUNT(bc) FROM values WHERE bc=35;

gives me the number of all rows with bc=35, so here 8.
But I am looking for 5, the number of rows after the last change of bc. 

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


Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

[Marc L. Allen]

Yes... that's true. Since I don't know enough about  how SQLite looks up a 
single record referenced by an index, I'm not in a position to discuss further. 

Naturally, there is always a trade off and, given the right percentage of 
matches, or position of matches, one way will be more efficient that the other. 

I didn't realize (or had forgotten) that integers could be stored with variable 
length. Naturally, that has additional impact. 

> On Jul 14, 2017, at 9:37 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
> 
> On Friday, 14 July, 2017 18:20, Marc L. Allen <mlal...@outsitenetworks.com> 
> said:
> 
>> I don't know... since each match would then require a lookup in the main
>> table to pull all the fields. If X was a covering index for the query, I
>> would agree.
> 
> So, if you did a table scan, you would have to scan all the rows in the table 
> and unpack at least 24 columns of integers to be able to execute the where 
> condition, and if there is a match, unpack the remaining two columns.  
> 
> Contrast this with scanning an index containing ONLY the integer required (so 
> 24 times less data to unpack) before executing the where condition which if 
> and only if the condition is matched requires finding the table row and 
> unpacking 26 columns of values.
> 
> There is obviously a point at which the % of returned vs scanned rows (and 
> total rows) makes one more efficient than the other.
> 
> Of course,  one could always force the index scan method:
> 
> select * from x indexed by xy where expression(y);
> 
> if you know this is going to be more efficient.
> 
>>> On Jul 14, 2017, at 7:54 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
>>> 
>>> 
>>> Not necessarily.  Your table may be defined as:
>>> 
>>> create table x(a int, b int, c int, d int, ... y int, z int);
>>> create index x_y on x (y);
>>> 
>>> in that case a select * from x where expression(y)
>>> 
>>> that scans the index (even without any skip-scan optimization) would be
>> far more efficient than scanning the table, assuming that the where
>> condition does not match all rows.
>>> 
>>> --
>>> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>>> 
>>>> -Original Message-
>>>> From: sqlite-users [mailto:sqlite-users-
>> boun...@mailinglists.sqlite.org]
>>>> On Behalf Of Marc L. Allen
>>>> Sent: Friday, 14 July, 2017 14:50
>>>> To: SQLite mailing list
>>>> Subject: Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an
>>>> index
>>>> 
>>>> Generally, but not always, WHERE elements using a column in an
>> expression
>>>> are unsuitable to using an index.
>>>> 
>>>> For instance:
>>>> 
>>>> WHERE y + 2 = 4 generally can't use an index, whereas WHERE y = 2 can.
>>>> 
>>>> That's not to say a smart optimizer won't convert the former to the
>>>> latter.
>>>> 
>>>> But, what's the equivalent of y & 2 != 0?
>>>> 
>>>> That converts to y IN (0, 2, 4, ... MAX(y) & 2)
>>>> 
>>>> Ignoring the MAX(y), that's just a series of multiple indexed lookups
>> and
>>>> a table scan is almost certainly more efficient.
>>>> 
>>>> Marc
>>>> 
>>>>> On Jul 14, 2017, at 1:55 PM, Howard Kapustein
>>>> <howard.kapust...@microsoft.com> wrote:
>>>>> 
>>>>> sqlite> create table blah(id integer primary key not null, x text, y
>>>> integer not null);
>>>>> sqlite> create index blahindex on blah (y);
>>>>> sqlite> explain query plan select * from blah where y & ? != 0;
>>>>> 0|0|0|SCAN TABLE blah
>>>>> 
>>>>> But other numeric expressions do e.g.
>>>>> 
>>>>> sqlite> explain query plan select * from blah where y = ?;
>>>>> 0|0|0|SEARCH TABLE blah USING INDEX blahindex (y=?)
>>>>> sqlite> explain query plan select * from blah where y > ?;
>>>>> 0|0|0|SEARCH TABLE blah USING INDEX blahindex (y>?)
>>>>> 
>>>>> Is this a known issue?
>>>>> ___
>>>>> sqlite-users mailing list
>>>>> sqlite-users@mailinglists.sqlite.org
>>>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>> 
>>>> 
>>>> Confidentiality notice: T

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

[Marc L. Allen]

I don't know... since each match would then require a lookup in the main table 
to pull all the fields. If X was a covering index for the query, I would agree. 

> On Jul 14, 2017, at 7:54 PM, Keith Medcalf <kmedc...@dessus.com> wrote:
> 
> 
> Not necessarily.  Your table may be defined as:
> 
> create table x(a int, b int, c int, d int, ... y int, z int);
> create index x_y on x (y);
> 
> in that case a select * from x where expression(y)
> 
> that scans the index (even without any skip-scan optimization) would be far 
> more efficient than scanning the table, assuming that the where condition 
> does not match all rows.
> 
> -- 
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> 
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Marc L. Allen
>> Sent: Friday, 14 July, 2017 14:50
>> To: SQLite mailing list
>> Subject: Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an
>> index
>> 
>> Generally, but not always, WHERE elements using a column in an expression
>> are unsuitable to using an index.
>> 
>> For instance:
>> 
>> WHERE y + 2 = 4 generally can't use an index, whereas WHERE y = 2 can.
>> 
>> That's not to say a smart optimizer won't convert the former to the
>> latter.
>> 
>> But, what's the equivalent of y & 2 != 0?
>> 
>> That converts to y IN (0, 2, 4, ... MAX(y) & 2)
>> 
>> Ignoring the MAX(y), that's just a series of multiple indexed lookups and
>> a table scan is almost certainly more efficient.
>> 
>> Marc
>> 
>>> On Jul 14, 2017, at 1:55 PM, Howard Kapustein
>> <howard.kapust...@microsoft.com> wrote:
>>> 
>>> sqlite> create table blah(id integer primary key not null, x text, y
>> integer not null);
>>> sqlite> create index blahindex on blah (y);
>>> sqlite> explain query plan select * from blah where y & ? != 0;
>>> 0|0|0|SCAN TABLE blah
>>> 
>>> But other numeric expressions do e.g.
>>> 
>>> sqlite> explain query plan select * from blah where y = ?;
>>> 0|0|0|SEARCH TABLE blah USING INDEX blahindex (y=?)
>>> sqlite> explain query plan select * from blah where y > ?;
>>> 0|0|0|SEARCH TABLE blah USING INDEX blahindex (y>?)
>>> 
>>> Is this a known issue?
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
>> Confidentiality notice: This e-mail is intended solely for use of the
>> individual or entity to which it is addressed and may contain information
>> that is proprietary, privileged, company confidential and/or exempt from
>> disclosure under applicable law. If the reader is not the intended
>> recipient or agent responsible for delivering the message to the intended
>> recipient, you are hereby notified that any dissemination, distribution or
>> copying of this communication is strictly prohibited. If you have received
>> this communication in error, please notify the sender by reply e-mail or
>> collect telephone call and delete or destroy all copies of this e-mail
>> message, any physical copies made of this e-mail message and/or any file
>> attachment(s).
>> ___
>> 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



Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 3.18.0 SELECT..WHERE x & ? != 0; doesn't use an index

[Marc L. Allen]

Generally, but not always, WHERE elements using a column in an expression are 
unsuitable to using an index. 

For instance:

WHERE y + 2 = 4 generally can't use an index, whereas WHERE y = 2 can. 

That's not to say a smart optimizer won't convert the former to the latter. 

But, what's the equivalent of y & 2 != 0?

That converts to y IN (0, 2, 4, ... MAX(y) & 2)

Ignoring the MAX(y), that's just a series of multiple indexed lookups and a 
table scan is almost certainly more efficient. 

Marc

> On Jul 14, 2017, at 1:55 PM, Howard Kapustein 
>  wrote:
> 
> sqlite> create table blah(id integer primary key not null, x text, y integer 
> not null);
> sqlite> create index blahindex on blah (y);
> sqlite> explain query plan select * from blah where y & ? != 0;
> 0|0|0|SCAN TABLE blah
> 
> But other numeric expressions do e.g.
> 
> sqlite> explain query plan select * from blah where y = ?;
> 0|0|0|SEARCH TABLE blah USING INDEX blahindex (y=?)
> sqlite> explain query plan select * from blah where y > ?;
> 0|0|0|SEARCH TABLE blah USING INDEX blahindex (y>?)
> 
> Is this a known issue?
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Version 3.20.0 coming soon...

[Marc L. Allen]

If it's going to change in future releases, then perhaps your "key point" is 
the main takeaway that should be documented?

Something like:

SQLITE_PREPARE_PERSISTENT is a hint to SQLite that the prepared statement is 
going to linger for a long time and that SQLite should take that into account 
for internal performance optimizations.  At present the only optimization is to 
avoid using lookaside... etc.

Marc


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Thursday, July 13, 2017 4:22 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Version 3.20.0 coming soon...

On 7/13/17, Dominique Pellé  wrote:
>
> Looking at the description of the new SQLITE_PREPARE_PERSISTENT at 
> https://sqlite.org/draft/c3ref/c_prepare_persistent.html
> it's not clear to me what are the benefits.
> Shouldn't the description say why it can be beneficial to use it or 
> not?
>

That documentation is the contract of what  SQLITE_PREPARE_PERSISTENT is 
suppose to do, so it needs to be a little vague in order to allow for changes 
in the future.

In the current implementation, SQLITE_PREPARE_PERSISTENT avoids using lookaside 
memory to store pieces of the prepared statement, to avoid soaking up all of 
the fast lookaside memory and preventing it from being used for more useful 
things.  But the effect of SQLITE_PREPARE_PERSISTENT might change in future 
releases.  The key point (and the part of this interface that will not change) 
is that SQLITE_PREPARE_PERSISTENT is a hint to SQLite that the prepared 
statement is going to linger for a long time.

How would you recommend that be stated more clearly?

--
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



Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing the output of a query

[Marc L. Allen]

Assuming each column is either NULL or a distinct item for each group, just use 
MAX() of each column adding the necessary group bys.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Joseph L. Casale
Sent: Tuesday, May 2, 2017 3:55 PM
To: SQLite mailing list 
Subject: [sqlite] Reducing the output of a query

I have a query produced from several left joins which follows the format:

XXX ItemA NULL NULL
XXX ItemA ItemB NULL
XXX ItemA NULL ItemC

I need to group the data by all columns, column 0 is trivial, however columns
1:3 can collapse when any non null field matches. In the above case this could 
collapse into a single line.

How can such a query be constructed?

Thanks,
jlc

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



Confidentiality notice: This e-mail is intended solely for use of the 
individual or entity to which it is addressed and may contain information that 
is proprietary, privileged, company confidential and/or exempt from disclosure 
under applicable law. If the reader is not the intended recipient or agent 
responsible for delivering the message to the intended recipient, you are 
hereby notified that any dissemination, distribution or copying of this 
communication is strictly prohibited. If you have received this communication 
in error, please notify the sender by reply e-mail or collect telephone call 
and delete or destroy all copies of this e-mail message, any physical copies 
made of this e-mail message and/or any file attachment(s).
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem compiling 3.17.0 in MSVS 12

[Marc L. Allen]

This is a total shot in the dark... but something with line endings (\r, \n, 
\r\n, etc.) messing up the

#define blah \

Or other things?

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Drago, William @ CSG - NARDA-MITEQ
Sent: Tuesday, February 14, 2017 4:49 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Problem compiling 3.17.0 in MSVS 12

> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of 
> Bart Smissaert
> Sent: Tuesday, February 14, 2017 4:24 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Problem compiling 3.17.0 in MSVS 12
>
> I tried compiling from the MSVS IDE with 3.17 as the source on a 
> different machine (also Windows 7, same MSVS version) and compiled fine there.
>
> No idea what the problem is.

Maybe the source file is corrupt on the machine with the errors.
--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / william.dr...@l3t.com


CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
attachments are solely for the use of the addressee and may contain information 
that is privileged or confidential. Any disclosure, use or distribution of the 
information contained herein is prohibited. In the event this e-mail contains 
technical data within the definition of the International Traffic in Arms 
Regulations or Export Administration Regulations, it is subject to the export 
control laws of the U.S.Government. The recipient should check this e-mail and 
any attachments for the presence of viruses as L-3 does not accept any 
liability associated with the transmission of this e-mail. If you have received 
this communication in error, please notify the sender by reply e-mail and 
immediately delete this message and any attachments.

Effective immediately my new email address is william.dr...@l3t.com. Please 
update your records.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

�߉ק�&��ܧ�؜y8b�隊X��{^�ם��^�'�����az)݊hܭ�br��ƝvZ�ٚ��'w��f���텫b��覸���港�W�y�(�'~'^�ؚ���驵���جrZ,׫j�e�ƛ��Z��i׫�^�{^�ם��"�'���ځ)�{"nW���^�+ޮ)���z���h���קu�kyȩ���ʋ���^�b~'���j|����)ڶ*'v+-�&��*'��(�(�+�i��'�*'��-�'-�*k���ם!����z��z+�v�b��&���q�b�x�z�讙^jǧ�؟��^���ze�隊Z+r�ey�mzWq�ejw]zW�z��z�k�&���)��(~�b�隊Y��Ơy�����ƥr�bzɚu���zf��g���jw��y�mi�fz{l___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

[Marc L. Allen]

  Yeah.. I know that.  Missed the PRIMARY KEY. :(

Back to lurking. ;)

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Thursday, November 17, 2016 9:54 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when 
'=' condition gives a strong hint

On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.com> wrote:
> I'm not sure that's a valid trick, as bar.foo can be NULL, in which 
> case the LEFT join still returns it, but an INNER join does not.  
> Unless sqlite infers a NOT NULL on bar.foo?

The bar.foo column is an INTEGER PRIMARY KEY, which can never be NULL.

Standard SQL requires an implied NOT NULL on all PRIMARY KEY columns.
But due to a coding error, early versions of SQLite did not enforce that, and 
so we have taken care not to enforce it on all subsequent versions of SQLite to 
preserve backwards compatibility.

WITHOUT ROWID tables were added later, and so NOT NULL is properly enforced on 
all PRIMARY KEY columns in WITHOUT ROWID tables.

>
> -Original Message-
> From: sqlite-users 
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of 
> Richard Hipp
> Sent: Thursday, November 17, 2016 9:32 AM
> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Subject: Re: [sqlite] Query Planner fails to recognise efficient 
> strategy when '=' condition gives a strong hint
>
> On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.com> wrote:
>> Maybe I'm missing something, but...
>>
>> ORDER BY id
>>
>> Is ordering by the ID the right-hand side of a LEFT join.  As such, 
>> it depends on how NULL factors into an ORDER BY.  If NULL comes 
>> first, it has to find enough records where the LEFT join fails.
>>
>> Yeah.. I'm probably missing something.
>
> No, you are correct.  Since the ID column is on the RHS of a LEFT 
> JOIN, it cannot use an index for sorting.
>
> The foreign key constraint could, in theory, be used by the query 
> planner to simplify the LEFT JOIN into an ordinary INNER JOIN.  But 
> the query planner in SQLite does not currently know that trick.
>
> So, one solution is to remove the LEFT keyword from the query in the 
> application
> --
> 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
>


--
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] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

[Marc L. Allen]

I'm not sure that's a valid trick, as bar.foo can be NULL, in which case the 
LEFT join still returns it, but an INNER join does not.  Unless sqlite infers a 
NOT NULL on bar.foo?

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Thursday, November 17, 2016 9:32 AM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when 
'=' condition gives a strong hint

On 11/17/16, Marc L. Allen <mlal...@outsitenetworks.com> wrote:
> Maybe I'm missing something, but...
>
> ORDER BY id
>
> Is ordering by the ID the right-hand side of a LEFT join.  As such, it 
> depends on how NULL factors into an ORDER BY.  If NULL comes first, it 
> has to find enough records where the LEFT join fails.
>
> Yeah.. I'm probably missing something.

No, you are correct.  Since the ID column is on the RHS of a LEFT JOIN, it 
cannot use an index for sorting.

The foreign key constraint could, in theory, be used by the query planner to 
simplify the LEFT JOIN into an ordinary INNER JOIN.  But the query planner in 
SQLite does not currently know that trick.

So, one solution is to remove the LEFT keyword from the query in the 
application
--
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] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

[Marc L. Allen]

Maybe I'm missing something, but...

ORDER BY id 

Is ordering by the ID the right-hand side of a LEFT join.  As such, it depends 
on how NULL factors into an ORDER BY.  If NULL comes first, it has to find 
enough records where the LEFT join fails.

Yeah.. I'm probably missing something.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Hick Gunter
Sent: Thursday, November 17, 2016 8:53 AM
To: 'SQLite mailing list' 
Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when 
'=' condition gives a strong hint

Maybe you are looking for

SELECT id,baz from foo where exists( select 1 from bar where foo = id) ...

which has the effect of easily extending to an arbitrary number of bar tables 
via additional exists subqueries that may be connected by logical operators

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Paul
Gesendet: Donnerstag, 17. November 2016 13:58
An: General Discussion of SQLite Database 
Betreff: [sqlite] Query Planner fails to recognise efficient strategy when '=' 
condition gives a strong hint

These are the queries:

CREATE TABLE foo(
idINTEGER,
baz INTEGER,
PRIMARY KEY(id)
);

CREATE TABLE bar(
foo   INTEGER,
PRIMARY KEY(foo),
FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE );

EXPLAIN QUERY PLAN SELECT foo.id, baz FROM bar LEFT JOIN foo ON bar.foo = 
foo.id ORDER BY id LIMIT 10, 10;

selectidorder   fromdetail
--  --  --  
---
0   0   0   SCAN TABLE bar
0   1   1   SEARCH TABLE foo USING INTEGER PRIMARY KEY 
(rowid=?)
0   0   0   USE TEMP B-TREE FOR ORDER BY

'bar.foo = foo.id' gives a strong hint that primary key index of 'bar' can be 
used for 'ORDER BY id' ordering.
But it does not happen, hence the plan includes full scan of 'bar' and  TEMP 
B-TREE construction.


The reason behind ordering being 'ORDER BY id' instead of 'ORDER BY bar.foo'  
is that bar pays a role of partial index.
Database was created just a year before partial indexes were released. Bar is 
not a single such table, there are more.
The query is being constructed dynamically and in the end  'ORDER BY id' is 
appended to ensure that  query is correct no matter how many 
'partial-index-tables'  the foo is LEFT JOIN-ed with.


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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


___
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] Illegal SQL not rejected

[Marc L. Allen]

That error is saying that you can't using HAVING on a column unless it's in a 
group by or it's referenced in an aggregate in the HAVING clause

You could say, HAVING SUM(A) <> 0 or something.

The query as stated

SELECT SUM(A)
...
HAVING A<>0

makes no sense because A is not in the select list.  Only SUM(A) is.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Igor Korot
Sent: Friday, April 29, 2016 1:42 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Illegal SQL not rejected

Hi,

On Fri, Apr 29, 2016 at 1:28 PM, John McKown  
wrote:
> On Fri, Apr 29, 2016 at 12:00 PM, Jann Roder 
> 
> wrote:
>
>> Hi,
>> It seems like a too obvious omission to not be intentional. But I 
>> wonder why a query like
>>
>> SELECT SUM(A)
>> FROM TABLE
>> GROUP BY B
>> HAVING A <> 0
>>
>> Is not rejected. MS SQL server gives you this error message in this case:
>>
>> Column ?A? is invalid in the HAVING clause because it is not 
>> contained in either an aggregate function or the GROUP BY clause.
>>
>
> I agree that it just looks _wrong_. And PostgreSQL certainly complains 
> about it.

This is weird because A is part of sum(A), which IS aggregate function call.
Or am I missing something?

Thank you.

>
>
>
>>
>> It's not even clear to me what SQLite does with a query like that.
>>
>
> I executed the above both with the HAVING clause and without it. I 
> also did an EXPLAIN on both. Judging by the EXPLAIN output and the 
> actual output, what it seems to do is exclude rows which have SUM(A) 
> equal to zero. I.e. the HAVING is acting on the SUM(A).
>
>
>>
>> Jann
>>
>
>
> --
> The unfacts, did we have them, are too imprecisely few to warrant our 
> certitude.
>
> Maranatha! <><
> John McKown
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Article about pointer abuse in SQLite

[Marc L. Allen]

With everything that routine does, the extra initialization negatively impacts 
operation?

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Thursday, March 24, 2016 10:50 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Article about pointer abuse in SQLite

On 3/24/16, Simon Slavin  wrote:
>
> I want the next generation of compilers to require that the programmer 
> specify an initial value (a constant, not a calculated
> value) for every variable they define, including every array element 
> when they define an array.

At https://www.sqlite.org/src/info/03b2a622?ln=3553-3560 is just one example 
where unnecessary local variable initialization has a large performance impact. 
 There are *many* other such examples in SQLite.
This one just has the best comment.
--
D. Richard Hipp
drh at sqlite.org
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Article about pointer abuse in SQLite

[Marc L. Allen]

I don't think compilers "run" your code.  When looking for uninitialized 
variables, it simply looks for a potential path through the code that uses a 
variable without it being initialized.

The fact that the code never actually allows that path to occur is beyond the 
scope of most compilers, isn't it?

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of J Decker
Sent: Tuesday, March 22, 2016 11:43 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Article about pointer abuse in SQLite

On Tue, Mar 22, 2016 at 6:58 AM, Adam Devita  wrote:
> It may be pedantic, but VS2016 will stop complaining if you edit your 
> definition of s to large_struct s=new large_struct();  //set s to an 
> actual instance of large_struct. c people can think of s as a pointer, 
> and in c# the members are set to their default values.
>
The point was, the structure had some 20 members, and 90% of the time the 
conditions don't exist for it to be initialized.  So rather than initialize it 
90% of the time for no use, I added checks to optimize the object's creation.

> J Decker's point could also have been made by using int x in place of 
> large_struct s . and sub x for s.x  , since it is a contrived example 
> anyway.  The only way to use x is if another conditional on another 
> variable that follows it in code and it is initialized.
>
> if one writes
> const bool arbitrary_true_false = true;   //note the const as Scott
> Doctor implied, makes the error go away.
>

It's not a const though, it's a variable the changes during runtime and allows 
for the creation of such an object. It's not 'contrived' it was an example that 
I ran into while developing (several times in fact).

similarly soemthing like (I haven't run it though compilers, so don't know if 
this is nested enough to cause the same issue... but it's easy to see how a 
compiler/error checker would similarly be confused.

void f() { int a, b;
   for( a = 0; a < 2; a++ ) {
   if(  a == 0 ) b = 1234;
   }
   printf( "b is never uniniialized here : %d", b ); }

> -
> This discussion on the nature of undefined behaviour code is 
> interesting.  I don't know the reasoning, but it seems that VS6 often 
> initialized things to 0xcd in debug mode and (usually) had memory 
> uninitialized to 0x00 when complied in Release (perhaps 0x00 just 
> happens to be what was on the stack or heap).  I presume this wasn't 
> just to make people suffer  when things don't work the same in debug 
> vs release mode.
>
> Does the tool help (in the sqlite in practice) point out things that 
> could be problematic?  Is it a compiler's variant of  "hay,  you are 
> depending on implemented, not documented behaviour" ?
>
> regards,
> Adam DeVita
>
>
> On Tue, Mar 22, 2016 at 7:27 AM, Scott Doctor  
> wrote:
>>
>> It is uninitialized. you are setting an initial value within an if 
>> statement. For the compiler, the code has NOT actually executed. so 
>> it does not use the value of the variable arbitrary_true_false. If it 
>> was a #define then it would use the value but still give an error 
>> because it is not a compiler directive #if but a code if.
>>
>> The logic is that the first instance of assignment is within a conditional.
>> That is a particularly nasty kind of bug and should be reported as an error.
>> because if later you decide to change arbitrary_true_false to false, 
>> then s.x would not be initialized before use. the compiler is correct 
>> to issue the warning. Give s.x a value after/at initialization, but 
>> before the if statement to give it a desired initial value then 
>> recompile, that should fix the error.
>>
>> Compilers only set the code to initialize the variable at 
>> declaration, not actually use the values during compile. If it was 
>> declared as a constant using a compiler directive such as #define, 
>> then the compiler would use the value in the logic and still give an 
>> error, but a different one because the conditional would always 
>> evaluate true (or false depending on what it was set to)
>>
>>
>> On 03/21/2016 21:31, J Decker wrote:
>>>
>>> On Mon, Mar 21, 2016 at 8:40 PM, Scott Doctor 
>>> 
>>> wrote:

 you are missing

 using System;
>>>
>>> whatever.  It still fails because it says the variable is 
>>> uninitilalized.  THe only thing that doesn't is actually running it.
>>>
>>> That same pattern not matter what the language triggers 
>>> warning/error checkers

 
 Scott Doctor
 scott at scottdoctor.com
 --


 On 3/21/2016 5:21 PM, J Decker wrote:
>
> So far I just see analysis tools fail for the same sorts of valid 
> code...
>
> this is a bit of C# but the same idea causes the same warnings and 
> there's nothign tecniclally wrong with this.
>
>
>
> class test
> {
>  struct large_struct { public int x; }
>  

[sqlite] Article about pointer abuse in SQLite

[Marc L. Allen]

I had a long response to this, but it occurs to me that you're just being 
pedantic for fun.  Am I wrong?

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf
Sent: Friday, March 18, 2016 4:41 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Article about pointer abuse in SQLite


There is no such thing as "undefined behaviour".  The machine code does exactly 
what it is told to do in exactly the manner in which it has been told to do it 
and obtains exactly the correct answer every time.

That the computation is "advanced beyond the realm of understanding of the 
observer" does not make the behaviour undefined.  It is perfectly defined, 
however, it is occasionally necessary to describe things as "undefined", 
oftentimes because it is too complicated to explain.  Just because someone says 
something as "undefined" does not mean that is so.  It is simply a euphemism 
for "I don't understand how it did that/what it is supposed to be doing (or, 
more often an appeal to self-proclaimed authority which said that such 
behaviour was undefined" without  having to admit fault, much in the same way 
that "supported" is a euphemism for "make money from".

Things will only be non-deterministic and perhaps undefined when run on Quantum 
Computers using Heisenberg registers for intermediate results.

> 

> "SQLite is a carefully engineered and thoroughly tested piece of software.
> Even so, it contains undefined behaviors because, until recently, no 
> good checker for these behaviors existed. If anything is going to save 
> us from UB hell, it?s tools combined with developers who care to listen to 
> them. "

> Simon.




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



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] SQLite Pronunciation

[Marc L. Allen]

I don't think anyone's making a fuss.  I certainly wasn't and apologize if it 
appeared differently. 

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Stephen 
Chrzanowski
Sent: Wednesday, March 16, 2016 3:09 PM
To: SQLite mailing list 
Subject: Re: [sqlite] SQLite Pronunciation

Standards, official, or not, I've always pronounced it as Sequel.
Ehm-Ess-Sequel, My-Sequel, Sequel-Lite, etc.

IMO, S-Q-L is an acronym, as everyone knows, and since RADAR(1) is also an 
Acronym, why the fuss?  To me, it falls off the tongue easier to say Sequel 
instead of Ess-Queue-Ell.

1- http://acronyms.thefreedictionary.com/RADAR



On Wed, Mar 16, 2016 at 2:55 PM, Marc L. Allen 
wrote:

> That sounds like someone that comes from the land of Sequel. ;)
>
> I realize there *is* an official pronunciation, but I will probably 
> forever pronounce it as S-Q-L-light, regardless of what it really is. 
> :)
>
> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:
> sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of J Decker
> Sent: Wednesday, March 16, 2016 2:48 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] SQLite Pronunciation
>
> more like sequel-ite
>
> On Wed, Mar 16, 2016 at 11:38 AM,   wrote:
> > Hello,
> >
> > Please grant me some leeway here since as someone who has not been 
> > in an academic school for years and is mainly self taught. I have 
> > Mainly deriving information from reading manuals and occasionally 
> > viewing some videos.
> >
> > Maybe I'm wrong, but according to Wikepedia SQLite appears to be 
> > pronounced the same has it is spelled, 
> > (sikwl.lat).
> > Maybe not a long A there perhaps.
> >
> > Where as I first heard Microsoft's MSSQL pronounce (sequent), which 
> > I have also heard in academic videos by professors.
> > Following that logic, SQLite, (sequent.light)?
> >
> > Dana Proctor
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> This email and any attachments are only for use by the intended
> recipient(s) and may contain legally privileged, confidential, 
> proprietary or otherwise private information. Any unauthorized use, 
> reproduction, dissemination, distribution or other disclosure of the 
> contents of this e-mail or its attachments is strictly prohibited. If 
> you have received this email in error, please notify the sender 
> immediately and delete the original.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] SQLite Pronunciation

[Marc L. Allen]

That sounds like someone that comes from the land of Sequel. ;)

I realize there *is* an official pronunciation, but I will probably forever 
pronounce it as S-Q-L-light, regardless of what it really is. :)

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of J Decker
Sent: Wednesday, March 16, 2016 2:48 PM
To: SQLite mailing list 
Subject: Re: [sqlite] SQLite Pronunciation

more like sequel-ite

On Wed, Mar 16, 2016 at 11:38 AM,   wrote:
> Hello,
>
> Please grant me some leeway here since as someone who has not been in 
> an academic school for years and is mainly self taught. I have Mainly 
> deriving information from reading manuals and occasionally viewing 
> some videos.
>
> Maybe I'm wrong, but according to Wikepedia SQLite appears to be 
> pronounced the same has it is spelled, 
> (sikwl.lat).
> Maybe not a long A there perhaps.
>
> Where as I first heard Microsoft's MSSQL pronounce (sequent), which I 
> have also heard in academic videos by professors.
> Following that logic, SQLite, (sequent.light)?
>
> Dana Proctor
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] i think this is a bug in sqlite? thanks

[Marc L. Allen]

I was about the suggest the same thing that was suggest in that thread.  Verify 
the types of those values.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Anthony 
Damico
Sent: Thursday, December 10, 2015 10:56 AM
To: sqlite-users at mailinglists.sqlite.org
Subject: [sqlite] i think this is a bug in sqlite? thanks

https://github.com/rstats-db/RSQLite/issues/125#event-488337588
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Alexa has been banished. Was: Mailing list policy change

[Marc L. Allen]

I save one. I can forward it to you, if you'd like. 

> On Oct 29, 2015, at 5:37 PM, Nicolas J?ger  wrote:
> 
> damn! I wanted a date!
> 
>>> On 10/29/15, Simon Slavin  wrote:
>>> 
>>> You don't even need to mess with the genuine list server.  Just grab all the
>>> addresses it sends to and use another computer to send messages directly to
>>> those addresses using the binary-chop thing.  
>> 
>> Dan wrote a scribe to do this, which identified the offender.  That
>> individual has been banished.  The mailing list has been reverted back
>> to show the senders email address.
>> 
>> Please let me know if you get any further emails from Alexa.
> 
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Simple Math Question

[Marc L. Allen]

I didn't see this go through, so I'm sending it again. I got an Alexa spam, so 
maybe that's part of it. 

I ran the following code on my ARM processor:

 double c25 = 0.0;
 c25 += 9.2;
 c25 += 7.9;
 c25 += 0.0;
 c25 += 4.0;
 c25 += 2.6;
 c25 += 1.3;
 double n25 = 25.0;

 double c23 = 0.0;
 c23 += 9.2;
 c23 += 7.8;
 c23 += 0.0;
 c23 += 3.0;
 c23 += 1.3;
 c23 += 1.7;
 double n23 = 23.0;

 double c21 = 0.0;
 c21 += 9.2;
 c21 += 7.9;
 c21 += 0.0;
 c21 += 1.0;
 c21 += 1.3;
 c21 += 1.6;
 double n21 = 21.0;

My debugger shows the following:

C25: 2.5003E+1 (0x40390001)
N25: 25.0 (0x4039)
C23: 23.0
N23: 23.0
C21: 2.1003E+1 (0x40350001)
N21: 21 (0x4035)

The error is down in the least significant bit.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jim Callahan
Sent: Friday, October 23, 2015 11:19 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Simple Math Question

Pocket calculators and COBOL used binary coded decimal (bcd) numbers to avoid 
the representation/round off issues. But this meant another entire number type 
(supported with addition, subtraction and having to be type checked in 
functions) in addition to integer and floating point; most found it easier to 
use integers to keep track on pennies...

> On Fri, Oct 23, 2015 at 11:05 AM, Scott Hess  wrote:
> 
> On Fri, Oct 23, 2015 at 7:39 AM, Dominique Devienne 
> 
> wrote:
> 
>> On Fri, Oct 23, 2015 at 4:16 PM, Rousselot, Richard A < 
>> Richard.A.Rousselot at centurylink.com> wrote:
>>> So I decided to output 1000 digits, because why not?  So now I am 
>>> more perplexed with all these digits showing it is working the 
>>> opposite of
>> how I
>>> expected it.  Why is the second set of equations evaluating to a "yes"
>> when
>>> it is the only one that is obviously NOT equal to the expression???
>> 
>> Indeed, that's puzzling :)
> 
> 
> Just to be clear, though, how floating-point numbers work is breaking 
> your expectations because your expectations are wrong when applied to 
> floating-point numbers.  Internally, they are base-2 scientific 
> notation, so asking for more significant digits in the base-10 
> representation won't help - base-10 fractional numbers cannot always 
> be represented precisely in base-2, ALSO base-2 fractional numbers 
> cannot always be represented precisely in base-10, so it's like a game 
> of telephone where you can end up slightly removed from where you 
> started out, even though it seems like it's a simple round trip.  
> Since each individual digit cannot be represented perfectly, it 
> doesn't matter how many digits of precision you ask for, you'll always 
> be able to find cases where it doesn't line up like you expect.
> 
> Think of it this way: Find an English sentence, and find an English to 
> Japanese translator.  Translate each individual word of the sentence 
> from English to Japanese, then concatenate the results together.  Then 
> translate the entire original sentence to Japanese.  The results will 
> almost never be the same.  Then do the same process translating the 
> Japanese back to English.  Again, the two routes will provide 
> different results, _and_ both of those results will almost certainly 
> not match the original English sentence.  This isn't a reflection of the 
> translator's abilities at all.
> 
> I'm not saying the computer is always right, just that the computer is 
> following a very strict recipe with reproducible results.  I don't 
> mean reproducible like your three examples make logical sense to you, 
> the user, I mean reproducible like my Intel box gives the same results 
> as my AMD box as my ARM box.  If you want to be able to deal with 
> fractional decimal values with high fidelity, you either need to 
> arrange for base-10 representation (slow, because computers have to 
> simulate it), or you have to do your math in shifted fashion (fast, but can 
> be error prone).
> 
> -scott
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Simple Math Question

[Marc L. Allen]

I ram the following code on my ARM processor:

  double c25 = 0.0;
  c25 += 9.2;
  c25 += 7.9;
  c25 += 0.0;
  c25 += 4.0;
  c25 += 2.6;
  c25 += 1.3;
  double n25 = 25.0;

  double c23 = 0.0;
  c23 += 9.2;
  c23 += 7.8;
  c23 += 0.0;
  c23 += 3.0;
  c23 += 1.3;
  c23 += 1.7;
  double n23 = 23.0;

  double c21 = 0.0;
  c21 += 9.2;
  c21 += 7.9;
  c21 += 0.0;
  c21 += 1.0;
  c21 += 1.3;
  c21 += 1.6;
  double n21 = 21.0;

My debugger shows the following:

C25: 2.5003E+1 (0x40390001)
N25: 25.0 (0x4039)
C23: 23.0
N23: 23.0
C21: 2.1003E+1 (0x40350001)
N21: 21 (0x4035)

The error is down in the least significant bit.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jim Callahan
Sent: Friday, October 23, 2015 11:19 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Simple Math Question

Pocket calculators and COBOL used binary coded decimal (bcd) numbers to avoid 
the representation/round off issues. But this meant another entire number type 
(supported with addition, subtraction and having to be type checked in 
functions) in addition to integer and floating point; most found it easier to 
use integers to keep track on pennies...

On Fri, Oct 23, 2015 at 11:05 AM, Scott Hess  wrote:

> On Fri, Oct 23, 2015 at 7:39 AM, Dominique Devienne 
> 
> wrote:
>
> > On Fri, Oct 23, 2015 at 4:16 PM, Rousselot, Richard A < 
> > Richard.A.Rousselot at centurylink.com> wrote:
> > > So I decided to output 1000 digits, because why not?  So now I am 
> > > more perplexed with all these digits showing it is working the 
> > > opposite of
> > how I
> > > expected it.  Why is the second set of equations evaluating to a "yes"
> > when
> > > it is the only one that is obviously NOT equal to the expression???
> >
> > Indeed, that's puzzling :)
>
>
> Just to be clear, though, how floating-point numbers work is breaking 
> your expectations because your expectations are wrong when applied to 
> floating-point numbers.  Internally, they are base-2 scientific 
> notation, so asking for more significant digits in the base-10 
> representation won't help - base-10 fractional numbers cannot always 
> be represented precisely in base-2, ALSO base-2 fractional numbers 
> cannot always be represented precisely in base-10, so it's like a game 
> of telephone where you can end up slightly removed from where you 
> started out, even though it seems like it's a simple round trip.  
> Since each individual digit cannot be represented perfectly, it 
> doesn't matter how many digits of precision you ask for, you'll always 
> be able to find cases where it doesn't line up like you expect.
>
> Think of it this way: Find an English sentence, and find an English to 
> Japanese translator.  Translate each individual word of the sentence 
> from English to Japanese, then concatenate the results together.  Then 
> translate the entire original sentence to Japanese.  The results will 
> almost never be the same.  Then do the same process translating the 
> Japanese back to English.  Again, the two routes will provide 
> different results, _and_ both of those results will almost certainly 
> not match the original English sentence.  This isn't a reflection of the 
> translator's abilities at all.
>
> I'm not saying the computer is always right, just that the computer is 
> following a very strict recipe with reproducible results.  I don't 
> mean reproducible like your three examples make logical sense to you, 
> the user, I mean reproducible like my Intel box gives the same results 
> as my AMD box as my ARM box.  If you want to be able to deal with 
> fractional decimal values with high fidelity, you either need to 
> arrange for base-10 representation (slow, because computers have to 
> simulate it), or you have to do your math in shifted fashion (fast, but can 
> be error prone).
>
> -scott
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Simple Math Question

[Marc L. Allen]

If I recall, he had a printout of the state at each time unit. The state was 
represented as a set of numbers out to 6 decimal points. 

He wanted to rerun part of the simulation, so he entered in those super-precise 
numbers and let them run, but the model quick diverged because those numbers 
just weren't precise enough. 

> On Oct 22, 2015, at 10:56 PM, Nicolas J?ger  
> wrote:
> 
> Le Thu, 22 Oct 2015 19:45:09 +,
> "Rousselot, Richard A"  a ?crit :
> 
>> Doing the following math, why is it that the results are not all returning 
>> "yes"?
>> 
>> SELECT
>>(9.2+7.9+0+4.0+2.6+1.3),
>>case when (9.2+7.9+0+4.0+2.6+1.3)=25.0 then "yes" else "no" 
>> end,
>>(9.2+7.8+0+3.0+1.3+1.7),
>>case when (9.2+7.8+0+3.0+1.3+1.7)=23.0 then "yes" else "no" 
>> end,
>>(9.2+7.9+0+1.0+1.3+1.6),
>>case when (9.2+7.9+0+1.0+1.3+1.6)=21.0 then "yes" else "no" 
>> end
>> FROM
>>sometable;
>> 
>> Result
>> 25.0 no 23.0 yes 21.0 no
>> 
>> I'm sure this has an obvious answer but it isn't jumping out at me.
>> 
>> Richard
>> 
>> This communication is the property of CenturyLink and may contain 
>> confidential or privileged
>> information. Unauthorized use of this communication is strictly prohibited 
>> and may be unlawful.
>> If you have received this communication in error, please immediately notify 
>> the sender by reply
>> e-mail and destroy all copies of the communication and any attachments.
>> ___ sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
> just for the fun: round-off error...
> 
> 1/3 has an infinite number of decimal number. meanwhile a computer can only 
> store a finite
> number of decimal...
> 
> this is the story who leads Lorenz to talk about his butterfly and the 
> chaos... I do not remember
> the whole story but iirc that was when he worked on some numerical weather 
> prediction. Data
> stored in some magnetic device (sound like science-fiction for someone young 
> like me...) and the
> data in the RAM haven't the same number of decimal, so the calculus after 
> several iterations
> gave totaly different results...
> 
> regards,
> Nicolas J.
> 
> n.b. : "meanwhile a computer can only store a finite number of decimal..." 
> same in groceries when
> you see 98c and you have to pay 1$... thiefs!
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

[Marc L. Allen]

Ah.  Of course.

Thanks for waking me up.. both you and Igor.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp
Sent: Thursday, October 08, 2015 12:12 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] [sqlite-dev] Proposed new version numbering scheme for 
SQLite - Feedback requested

On 10/8/15, Marc L. Allen  wrote:
> However, CTE is a functionality enhancement that, I believe, does not 
> impact the ability of previous SQLite versions to work with the 
> database.  The thing is, I don't believe CTE is simply a "performance 
> enhancement."  To me, a "performance enhancement" provides no new 
> functionality, but just works better.
>

If someone does (for example):

   CREATE VIEW digits AS
  WITH RECURSIVE c(x) AS (VALUES(0) UNION ALL SELECT x+1 FROM c WHERE x<9)
  SELECT x FROM c;

Then the CTE becomes part of the schema, and the database cannot be opened by 
an earlier version of SQLite that lacks support for CTEs.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

[Marc L. Allen]

More thoughts...

My quandary is with compatibility in regards to file formats only.  If you 
extend it to API, the CTE would require a Y change.  Code that uses SQLite and 
runs with Y may not operate with Y-1 API.

And, on a side note... the new versioning scheme also means that changes, such 
as the new Query Analyzer done a few years back (which was a huge boost in 
performance) would be relegated to a Z change, which makes me sad. ;)

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Marc L. Allen
Sent: Thursday, October 08, 2015 11:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] [sqlite-dev] Proposed new version numbering scheme for 
SQLite - Feedback requested

Just my $0.02...

In the proposed new versioning system:

Partial Indexes is clearly something that requires Y to be incremented as Y-1 
won't be able to handle a database with partial indexes.

However, CTE is a functionality enhancement that, I believe, does not impact 
the ability of previous SQLite versions to work with the database.  The thing 
is, I don't believe CTE is simply a "performance enhancement."  To me, a 
"performance enhancement" provides no new functionality, but just works better.

So, I question the exact definition for 'Z'.  I think it's pretty much any 
change that doesn't mandate X or Y changing.  Maybe change it to:

"The third number Z is incremented for all other changes, such as performance 
enhancements and bug fixes."

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jaroslaw 
Staniek
Sent: Thursday, October 08, 2015 11:33 AM
To: sqlite-dev at mailinglists.sqlite.org
Cc: General Discussion of SQLite Database
Subject: Re: [sqlite] [sqlite-dev] Proposed new version numbering scheme for 
SQLite - Feedback requested

On 8 October 2015 at 15:38, Richard Hipp  wrote:

> Several users have proposed that SQLite adopt a new version numbering 
> scheme.  The proposed change is currently uploaded on the "draft"
> website:
>
> https://www.sqlite.org/draft/versionnumbers.html
> https://www.sqlite.org/draft/releaselog/3_9_0.html
> https://www.sqlite.org/draft/
>
> If accepted, the new policy will cause the next release to be 3.9.0 
> instead of 3.8.12.  And the second number in the version will be 
> increased much more aggressively in future releases.
>
> Your feedback on the proposed policy change is appreciated.  We will 
> delay the next release until there is a semblance of consensus on the 
> new policy.
>

?Thanks, looks solid for me.

PS: For cmake users I am committing myself to update the FindSqlite.cmake 
detection script in areas where it's needed.
Even for the current versioning approach I introduced SQLITE_MIN_VERSION_PATCH 
variable among others.[1] Its semantics can be easily made compatible with the 
proposed new versioning approach by making the variable optional. I welcome any 
further suggestions, also contributing the file to cmake itself since SQLite is 
so popular.

[1]
https://phabricator.kde.org/diffusion/KDB/browse/master/cmake/modules/FindSqlite.cmake

--
regards, Jaroslaw Staniek

KDE:
: A world-wide network of software engineers, artists, writers, translators
: and facilitators committed to Free Software development - http://kde.org 
Calligra Suite:
: A graphic art and office suite - http://calligra.org
Kexi:
: A visual database apps builder - http://calligra.org/kexi Qt Certified 
Specialist:
: http://www.linkedin.com/in/jstaniek
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] [sqlite-dev] Proposed new version numbering scheme for SQLite - Feedback requested

[Marc L. Allen]

Just my $0.02...

In the proposed new versioning system:

Partial Indexes is clearly something that requires Y to be incremented as Y-1 
won't be able to handle a database with partial indexes.

However, CTE is a functionality enhancement that, I believe, does not impact 
the ability of previous SQLite versions to work with the database.  The thing 
is, I don't believe CTE is simply a "performance enhancement."  To me, a 
"performance enhancement" provides no new functionality, but just works better.

So, I question the exact definition for 'Z'.  I think it's pretty much any 
change that doesn't mandate X or Y changing.  Maybe change it to:

"The third number Z is incremented for all other changes, such as performance 
enhancements and bug fixes."

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jaroslaw 
Staniek
Sent: Thursday, October 08, 2015 11:33 AM
To: sqlite-dev at mailinglists.sqlite.org
Cc: General Discussion of SQLite Database
Subject: Re: [sqlite] [sqlite-dev] Proposed new version numbering scheme for 
SQLite - Feedback requested

On 8 October 2015 at 15:38, Richard Hipp  wrote:

> Several users have proposed that SQLite adopt a new version numbering 
> scheme.  The proposed change is currently uploaded on the "draft"
> website:
>
> https://www.sqlite.org/draft/versionnumbers.html
> https://www.sqlite.org/draft/releaselog/3_9_0.html
> https://www.sqlite.org/draft/
>
> If accepted, the new policy will cause the next release to be 3.9.0 
> instead of 3.8.12.  And the second number in the version will be 
> increased much more aggressively in future releases.
>
> Your feedback on the proposed policy change is appreciated.  We will 
> delay the next release until there is a semblance of consensus on the 
> new policy.
>

?Thanks, looks solid for me.

PS: For cmake users I am committing myself to update the FindSqlite.cmake 
detection script in areas where it's needed.
Even for the current versioning approach I introduced SQLITE_MIN_VERSION_PATCH 
variable among others.[1] Its semantics can be easily made compatible with the 
proposed new versioning approach by making the variable optional. I welcome any 
further suggestions, also contributing the file to cmake itself since SQLite is 
so popular.

[1]
https://phabricator.kde.org/diffusion/KDB/browse/master/cmake/modules/FindSqlite.cmake

--
regards, Jaroslaw Staniek

KDE:
: A world-wide network of software engineers, artists, writers, translators
: and facilitators committed to Free Software development - http://kde.org 
Calligra Suite:
: A graphic art and office suite - http://calligra.org
Kexi:
: A visual database apps builder - http://calligra.org/kexi Qt Certified 
Specialist:
: http://www.linkedin.com/in/jstaniek
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Marc L. Allen]

Oh, jeeze.  Wow.

And I know all that... I cleverly managed to ignore the "WHERE" and think it 
was an AND because it was one the same line.

Duh.

Thanks.


-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Reinhard Max
Sent: Monday, July 27, 2015 12:04 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query takes 8 seconds on one machine but I abort after 
40+ minutes on other machines


On Mon, 27 Jul 2015 at 17:48, Marc L. Allen wrote:

> When would that specific LEFT JOIN ever do anything except return 
> NULLs for the right table?  It only accepts rows from work where fpath 
> is null, and only joins those rows where fpath = home.fpath.
> Since fpath must be null, home.fpath should never be equal.

You're assuming that "LEFT JOIN ... ON ..." behaves the same as "JOIN ... WHERE 
...", which is not the case as it would defeat the purpose of a left join.

sqlite> create table home (fpath text);
sqlite> create table work (fpath text);
sqlite> insert into home values ('path1'); insert into home values 
sqlite> ('path2'); insert into work values ('path2'); insert into work 
sqlite> values ('path3'); SELECT home.fpath
...> FROM home
...> LEFT JOIN work ON work.fpath = home.fpath
...> WHERE work.fpath IS NULL;
path1

See, it returns the rows of home.fpath that don't have a match in work.fpath.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Marc L. Allen]

When would that specific LEFT JOIN ever do anything except return NULLs for the 
right table?  It only accepts rows from work where fpath is null, and only 
joins those rows where fpath = home.fpath.  Since fpath must be null, 
home.fpath should never be equal.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Reinhard Max
Sent: Monday, July 27, 2015 11:40 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query takes 8 seconds on one machine but I abort after 
40+ minutes on other machines



On Mon, 27 Jul 2015 at 17:34, Marc L. Allen wrote:

> Sorry.. confused does sqlite allow comparison between NULLs?

No.

> LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL
>
> Should never match anything... it checks to see if work.fpath IS NULL 
> and if the same work.fpath is equal to something.

It is a LEFT (OUTER) join, which NULLs the columns of the right thable in the 
result set for rows in the left table that don't have a match.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Query takes 8 seconds on one machine but I abort after 40+ minutes on other machines

[Marc L. Allen]

Sorry.. confused does sqlite allow comparison between NULLs?

I mean...

LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL

Should never match anything... it checks to see if work.fpath IS NULL and if 
the same work.fpath is equal to something.  

I get the weird feeling that I'm missing something stupid, so massive apologies 
in advance.  Maybe I came in late and this is some clever way of using an index?

Marc

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, July 27, 2015 10:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query takes 8 seconds on one machine but I abort after 
40+ minutes on other machines


On 27 Jul 2015, at 3:04pm, Igor Tandetnik  wrote:

> That's a common technique with LEFT JOIN

Thanks, Igor.


On 27 Jul 2015, at 1:44pm, rotaiv  wrote:

> SELECT home.fpath
> FROM home
> LEFT JOIN work ON work.fpath = home.fpath WHERE work.fpath IS NULL AND 
> home.ftype = 'f?;

Can you verify that you have an index on work.fpath ?  16 seconds is still a 
little slower than it should be.

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



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Mozilla wiki 'avoid SQLite'

[Marc L. Allen]

Frankly, I've avoided taking a close look at Andl because of its massive use of 
special characters.  I can usually work out a rough meaning on your examples 
because I know the SQL it came from.  If I became 'Andl proficient' it probably 
wouldn't be a problem, but I gotta ask.. what's the issue with just using 
keywords?  I feel like I'm looking at APL.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
da...@andl.org
Sent: Thursday, June 18, 2015 10:59 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

Yes, yours is the kind of situation Andl is aimed at. It does most of that 
stuff, but it's not ready for prime time yet. It's amazingly good at doing 
complex queries in just a few lines of code, but it lacks the external 
connections for it to be used for real applications.

I don't really understand the 'row access' or 'multiple row sets' in terms of 
the need being filled, and what Andl has to do to meet it. Hopefully this will 
come out if and when people try to use it to do real work.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Marc L. Allen
Sent: Thursday, 18 June 2015 11:51 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

I don't understand all the issues with SQL as it is today.  I doubt I'm at the 
level of most of the posters in this group.  However, I do write a mess of it 
so I thought I'd give my two cents as a programmer.

For what it does, SQL does it really well.  That is, it takes a couple of sets 
of data, links them together and returns a set of data with given relations.  
But, that's just not enough.  

Recursive SQL is one of the best things to happen to SQL, though the syntax is 
rather wonky.  But, I think it's done that way because SQL is NOT a programming 
language and we keep trying to turn it into one.  Ok.. not turn it into one, 
per say, but we want to, quite often, do programming-like work on the data as 
part of the 'linking' process.  This is especially prevalent in the systems 
supporting stored procedures.  Personally, I find it a pain to combine SQL and 
programming in code.  First, I have to generate the SQL, then I have to do 
whatever is necessary to iterate over the result set, often requiring 
additional SQL and additional iteration.  So, if SPs are available, I tend to 
put a lot of logic in them and, frankly, SQL and it's variants really aren't 
good at that.  Furthermore, the SQL environment is not part of the controlling 
programming environment and that causes additional problems.  It's also slow to 
go back and forth, especially when the application and database are separated.

I guess, at the end, is that I  need a data language that has both set 
operations and iteration operations.  I would want a language or construct that 
gives me row access during the operations so that I can finely tune the 
actions.  Sort of an OnRow() function that works for all SQL operations.  Also, 
the ability to split the incoming row set into multiple row sets for different 
purposes. Also... (insert weird data flow operation that doesn't exist 
natively).

As I read back through this, I realize how poorly stated it is.  But, I'm going 
to post it anyhow.  Maybe someone will say, "Hey, stupid... *this* exists.. try 
it."  Heck, maybe Andl does it.  I haven't looked.

Marc

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
da...@andl.org
Sent: Thursday, June 18, 2015 6:36 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

I agree. It would be relatively easy to produce a new language with a syntax 
based on SQL, which was superficially familiar, but there are many necessary 
differences at the lower levels. One issue where bullet biting is needed is 
nulls and three-valued logic; another is duplicate rows and nameless or 
duplicate columns. The type system needs a complete overhaul too. C was a 
clean, polished and quite small language; SQL is none of those.

And the biggest thing? Most SQL is used as a data sub-language, but the need is 
for a complete database programming language and a way out of the ORM mess. You 
can't get that from putting a layer on top of SQL.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan
Sent: Thursday, 18 June 2015 5:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

I disagree wit

[sqlite] Mozilla wiki 'avoid SQLite'

[Marc L. Allen]

I don't understand all the issues with SQL as it is today.  I doubt I'm at the 
level of most of the posters in this group.  However, I do write a mess of it 
so I thought I'd give my two cents as a programmer.

For what it does, SQL does it really well.  That is, it takes a couple of sets 
of data, links them together and returns a set of data with given relations.  
But, that's just not enough.  

Recursive SQL is one of the best things to happen to SQL, though the syntax is 
rather wonky.  But, I think it's done that way because SQL is NOT a programming 
language and we keep trying to turn it into one.  Ok.. not turn it into one, 
per say, but we want to, quite often, do programming-like work on the data as 
part of the 'linking' process.  This is especially prevalent in the systems 
supporting stored procedures.  Personally, I find it a pain to combine SQL and 
programming in code.  First, I have to generate the SQL, then I have to do 
whatever is necessary to iterate over the result set, often requiring 
additional SQL and additional iteration.  So, if SPs are available, I tend to 
put a lot of logic in them and, frankly, SQL and it's variants really aren't 
good at that.  Furthermore, the SQL environment is not part of the controlling 
programming environment and that causes additional problems.  It's also slow to 
go back and forth, especially when the application and database are separated.

I guess, at the end, is that I  need a data language that has both set 
operations and iteration operations.  I would want a language or construct that 
gives me row access during the operations so that I can finely tune the 
actions.  Sort of an OnRow() function that works for all SQL operations.  Also, 
the ability to split the incoming row set into multiple row sets for different 
purposes. Also... (insert weird data flow operation that doesn't exist 
natively).

As I read back through this, I realize how poorly stated it is.  But, I'm going 
to post it anyhow.  Maybe someone will say, "Hey, stupid... *this* exists.. try 
it."  Heck, maybe Andl does it.  I haven't looked.

Marc

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of 
da...@andl.org
Sent: Thursday, June 18, 2015 6:36 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

I agree. It would be relatively easy to produce a new language with a syntax 
based on SQL, which was superficially familiar, but there are many necessary 
differences at the lower levels. One issue where bullet biting is needed is 
nulls and three-valued logic; another is duplicate rows and nameless or 
duplicate columns. The type system needs a complete overhaul too. C was a 
clean, polished and quite small language; SQL is none of those.

And the biggest thing? Most SQL is used as a data sub-language, but the need is 
for a complete database programming language and a way out of the ORM mess. You 
can't get that from putting a layer on top of SQL.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Darren Duncan
Sent: Thursday, 18 June 2015 5:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'

I disagree with the idea that a good SQL alternative would just be a superset 
of SQL as you propose.

That has already been done numerous times, the principal manifestations being 
each SQL DBMS that has its own small or large differences in syntax and 
features from each other.

SQL is already a very complex language due in part to most of its features each 
having their own custom syntax, often several variations per feature to boot, 
as well as lots of arbitrary limitations or specified inconsistent behaviors, a 
lot of these for keeping backwards compatibility with various old or 
vendor-specific ways of doing things.

What a good SQL alternative would actually be is a much more self-consistent 
and less redundant than SQL.  It would still have all of SQL's expressive power 
and features so that any SQL code can be translated to it, including 
automatically, without too much circumlocution.  That is how you would simplify 
the transition and re-utilization of existing code.  The good alternative would 
actually be easier for a DBMS to implement also without losing any power.

-- Darren Duncan

On 2015-06-17 11:52 PM, ajm at zator.com wrote:
> Indeed, I'm agree with Darren, and continuing its thought, perhaps that 
> hypothetical new language would be a clean extensi?n of SQL in the same way 
> that C++ was respect to C, simplifying the transition and reutilization of 
> legacy code.
>
> Cheers.
>
> --
> A.J. Millan
>>
>>  Mensaje original 
>> De: 
>> Para:  "'General Discussion of SQLite 
>> Database'"
>> Fecha:  Thu, 

[sqlite] Mozilla wiki 'avoid SQLite'

[Marc L. Allen]

I don't know. Back in the day, assembly was low-level because it was directly 
converted to machine code. C was high level because you could express more 
complex structures without worrying about the underlying architecture. 

I still like that distinction. I think people are trying to call C low level 
simply because there are even higher level languages. It's not just 'high' or 
'low'. It's a spectrum.

C might be lower level to other languages, but higher level to assembler. 

> On Jun 16, 2015, at 4:40 PM, Simon Slavin  wrote:
> 
> Since this thread has ... become what it is ... I may as well add a couple of 
> details.
> 
> The widely-used compiler LLVM uses an intermediate representation of your 
> code, called 'Bitcode'.  In other words, it's not a simple case of compiling 
> straight to object code.  This feeds into some distinctions made between high 
> level and low level languages in previous posts.
> 
> I also wanted to comment on various things said about C by saying that there 
> is no industry agreement about whether C is a high level or low level 
> language.  Mostly because those terms were never defined very clearly because 
> it was obvious to every programmer what they were looking at.  If the 
> language included words which looked like human languages, it was a high 
> level language.  If it was based around acronyms and very short words like 
> MOVE is was low level.  C messed up the distinction and since then we've been 
> running to keep up.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] Mozilla wiki 'avoid SQLite'

[Marc L. Allen]

Perhaps you might want to consider an apology and check your headers next time.

I'm pretty sure the comment you quoted from this morning was written by Scott 
Doctor, not Scott Robinson.

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Alex Bowden
Sent: Monday, June 15, 2015 11:00 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite'


> On 15 Jun 2015, at 15:44, Scott Robison  wrote:
> 
> Too many of these technology based discussions (whether languages or 
> operating systems or text editors or database engines or whatever) 
> break down into almost a religious fervor of "this is the one true

You mean like your comment from this morning?

"The use of languages higher than C result in slow bloated code. A language 
that calls a language that calls a language. Simple programs become 
multi-megabyte resource hogs.?

Frame it.  Put it over your bed.  And use it to remind you to put your brain in 
gear, before opening your mouth.
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

[sqlite] saving :memory:database to disk

[Marc L. Allen]

Don't you already have to have a DB initialization script to create the 
in-memory database?  Couldn't you use that to create the on-disk database, and 
then have a separate script to copy all the data over?

Or is this for more of a dynamic database whereby the users can add/remove 
tables and indexes?

-Original Message-
From: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Paul 
Sanderson
Sent: Thursday, May 07, 2015 1:53 PM
To: General Discussion of SQLite Database
Subject: [sqlite] saving :memory:database to disk

I am contemplating a change to a program whereby a database is initailly 
created in memory and then later if my users choose they can save it to disk 
and then switch to using the disk based DB.

I can obviously create a new disk based db, iterate through sqlite_master and 
then populate each table. But I just wondered whether anyone had any 
suggestions of a better way of doing this?

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.

Re: [sqlite] Select count(*)

[Marc L. Allen]

I believe that when NULLs are allowed as PKs, they are all distinct.  So, you 
can multiple rows with a NULL value as the PK.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dominique Devienne
Sent: Thursday, December 11, 2014 11:27 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Select count(*)

On Thu, Dec 11, 2014 at 5:03 PM, Gabriel Corneanu  wrote:

> I asked a similar question some time ago...
> See here:
> https://www.mail-archive.com/sqlite-users@sqlite.org/msg77488.html



>> not a problem for rowid/pk (which are not allowed to be NULL), but it 
>> matters a lot in the general case.

> DRH write:

> PRIMARY KEYs (except for INTEGER PRIMARY KEYs) are allowed to be NULL 
> in SQLite.  This goes back to a bug in the code from many years ago.  
> By the time the bug was discovered, SQLite was already in wide-spread 
> use and so the decision was made to not fix the bug since doing so 
> would cause

> compatibility problems.

it's a bit sad to carry "fundamental" baggage like, in all cases.

I can see why it would stay the default, but surely a pragma could remedy the 
situation to get the best possible conformance?
I know this was discussed before on list, but I for one would welcome this new 
pragma.

To come back on the subject, even if SQLite allows a null PK, there
(hopefully) can be a single row using it, and it (this null row) is still 
indexed, no?
So why couldn't sqlite using the PK index to reduce the IO when doing a "select 
count(*) from t_with_non_int_pk", to avoid scanning the table?

Perhaps it's a naive question, but I don't see why ATM, and would welcome an 
explanation. Thanks, --DD ___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Marc L. Allen]

Doesn't that code risk being broken in a later version that doesn't update in 
the order provided by the sub-query?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of J T
Sent: Monday, December 08, 2014 9:23 AM
To: rsm...@rsweb.co.za; sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to 
fail

Might have another work around.

update page set position=position + 1 where designation=(select designation 
from page where book='1' order by position desc)

and then insert your page.

Please see if that'll work. I tested it, but your results may differ.



 

 

 

-Original Message-
From: RSmith 
To: General Discussion of SQLite Database 
Sent: Mon, Dec 8, 2014 9:15 am
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to 
fail



On 2014/12/08 15:58, Gwendal Roué wrote:
> I'm new to this mailing list, and I won't try to push my opinion, which is : 
yes this is a bug, and this bug could be fixed 
> without introducing any regression (since fixing it would cause 
> failing code
to suddenly run, and this has never been a 
> compatibility issue). Thank you all for your support and explanations. 
> The
root cause has been found, and lies in the constraint 
> checking algorithm of sqlite. I have been able to find a work around 
> that is
good enough for me. Now the subject deserves a rest, 
> until, maybe, someday, one sqlite maintainer who his not attached to 
> the
constraint-checking algorithm fixes it. Have a nice day, 
> Gwendal Roué

Your new-ness is irrelevant, if you have a worthy argument it deserves being 
heard. To that end, let me just clarify that nobody was saying the idea of 
deferring the constraint checking is invalid or ludicrous (at least I had no 
such intention) and you make a valid point, especially since most other DB 
engines do work as you suggest - and this will be fixed in SQLite4 I believe, 
where backward-compatibility is not an issue.

The reason I (and others) will say it isn't a bug is because it isn't working 
different than is intended, or more specifically, than is documented. It works 
exactly like described - whether you or I agree with that paradigm or not is up 
to discussion but does not make it a "bug" as long as it works as described.

I hope the work-around you found works great!



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


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



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Marc L. Allen]

I'm not sure I'd even consider it broken.

SQLite is wonderful.  Simply wonderful.  Code size and amount of features 
forced into it impresses me no end.  But, it was never intended to run with the 
big dogs.  The fact that, quite often, it can is a tribute to the people that 
work on it.

When making a 'lite' version of something, it's normal to eliminate difficult 
or intensive features that can be lived without.  I think this is one of them.

Marc

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of John McKown
Sent: Monday, December 08, 2014 9:18 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] sqlite bugreport : unique index causes valid updates to 
fail

On Mon, Dec 8, 2014 at 8:15 AM, Marc L. Allen <mlal...@outsitenetworks.com>
wrote:

> I am like you, Gwendal, in that I don't like that behavior in SQLite; 
> however, not liking it doesn't make it a bug.
>

​On another of my forums, this is called a BAD - Broken, As Designed.​ As 
opposed to the normal WAD - Working As Designed.

--
The temperature of the aqueous content of an unremittingly ogled culinary 
vessel will not achieve 100 degrees on the Celsius scale.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite bugreport : unique index causes valid updates to fail

[Marc L. Allen]

I am like you, Gwendal, in that I don't like that behavior in SQLite; however, 
not liking it doesn't make it a bug.

The constraint-checking algorithm was defined to work exactly the way it's 
working.  When designed, the fact that your type of insert would fail was known 
and understood.  Hence, it cannot be considered a bug.

Changing it at this date might be a problem.  While unlikely, there is a 
possibility that code exists out there that takes advantage of that particular 
design attribute.  Then you get into pragmas and options and the like.  I don't 
do any of the development, but I suspect that's a serious pain when there are 
other features that are more useful to work on.

So, in short... not a bug, but a design feature that you don't care for.  I'm 
sure there's a way to make suggestions or requests to change the design.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] insert or ignore with foreign keys

[Marc L. Allen]

I think INSERT OR IGNORE is designed to insert a record into a table if a 
record with its primary key doesn't already exist.  It's not an INSERT AND 
IGNORE ON ANY ERROR.

So:

INSERT OR IGNORE INTO t2 VALUES (1,1)
INSERT OR IGNORE INTO t2 VALUES (1,1)

The above would not cause an error where,

INSERT INTO t2 VALUES (1,1)
INSERT INTO t2 VALUES (1,1)

Would.

As for the best way to do what you want, I'm not sure.  Obviously, you could 
precheck the foreign key yourself, but I suspect your actual code is more 
sophisticated.

Marc

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Daniel Polski
Sent: Tuesday, November 25, 2014 10:23 AM
To: General Discussion of SQLite Database
Subject: [sqlite] insert or ignore with foreign keys


I guess the example below shows the intended behaviour for Sqlite?

PRAGMA FOREIGN_KEYS=1;
CREATE TABLE t1 (
 idINTEGER PRIMARY KEY
);

CREATE TABLE t2(
 idINTEGER PRIMARY KEY,
 t1_id INT NOT NULL,
 CONSTRAINT fk FOREIGN KEY(t1_id) REFERENCES t1(id) );

INSERT INTO t1 VALUES(2);

BEGIN TRANSACTION;
INSERT OR IGNORE INTO t2 VALUES(1, 1);
INSERT OR IGNORE INTO t2 VALUES(2, 2);
INSERT OR IGNORE INTO t2 VALUES(3, 3);
COMMIT;

Error: FOREIGN KEY constraint failed

I thought row id 1 & 3 simply would get ignored (due to the foreign key
mismatch) when specifying INSERT OR IGNORE, but instead the whole transaction 
gets aborted. Is there any functionality availible to achieve what I want 
instead (get row 2,2 added to table t2)?

Thanks!
/D
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug

[Marc L. Allen]

I think attachments are dropped. If the SQL is reasonable size, just post it. 

Otherwise, you'll need to host the screen shot somewhere and link to it. 


> On Nov 19, 2014, at 10:00 PM, Josef Handsuch  wrote:
> 
> Dear developer, I'd like to thank you for you brilliant software. There
> just seems to be one little bug to report. If I filter a field that I have
> dedicated to date, the result is always wrong. Please take a look at the
> screenshot (attached) to see what I mean. What you can see there is that I
> was trying to view records that were made on November 19th and there should
> only be 19 records. The progam says there have been made over 150 thousand
> records that day... Whatever date I try to filter, the result is always
> wrong like this.
> Would you please fix this issue?
> Kind regards, Josef
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select 1 where 1 - 1;

[Marc L. Allen]

I think everyone agrees that SQLite does not strictly follow the SQL standards 
for WHERE clause expressions.

The question is... should it?  One must ask, "what makes SQLite lite?"  I think 
this kind of simplification is of them.  However, I can understand that it 
might rankle some people.  Coming from a C background, when I see that SELECT 1 
WHERE 1-1 compiles and treats 1-1 as FALSE, I can work with that.  

Heavens knows the number of times I've been caught by using a single '=' or 
tacking on a spare ';' on the end of while statement.  And it irritated me that 
the language even allowed it.  (Of course, the language DID allow it, so I have 
no right to complain.)

So, since this is an SQL system designed to cut corners but still get the basic 
job done, I really have to ask why there's an argument at all on this topic?  
Both sides are right.   Yes, SQLite accepts queries that go against SQL 
standard.  Yes, if your query is formatted correctly, it executes correctly, so 
the fact that it takes a non-standard query is still an error on the 
programmer's part, even if SQLite didn't flag it.  At least it does have rules 
on how those are handled.

Marc

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Petite Abeille
Sent: Monday, May 05, 2014 2:46 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] select 1 where 1 - 1;


On May 5, 2014, at 8:21 PM, RSmith  wrote:

> the idea that introducing more complication will make erros/bugs less is just 
> false.

Straw man argument, unrelated to the topic at hand.

This is solely about the SQL parser failing short of reporting syntax errors 
for nonsensical queries.

select 1 where null;

select 1 where is null;
Error: near "is": syntax error

select 1 where not null;

select 1 where is not null;
Error: near "is": syntax error

select 1 where not( not null);

elect 1 where not( is null );
Error: near "is": syntax error

select 1 where not( 1 );

select 1 where 'When I use a word, Humpty Dumpty said, in rather a scornful 
tone, it means just what I choose it to mean - neither more nor less.';

select 1 where not 'The question is, said Alice, whether you can make words 
mean so many different things.';

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] select 1 where 1 - 1;

[Marc L. Allen]

Really?  Interesting.

So...

Select 1 Where 1 < inf; ? 

Or is it just when taking inf by itself?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Monday, May 05, 2014 11:32 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] select 1 where 1 - 1;

On Mon, May 5, 2014 at 11:28 AM, mm.w <0xcafef...@gmail.com> wrote:

>  select inf; should be true or false? :)
>

Neither true nor false.  SQLite considers infinity to be the same as NULL.


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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Marc L. Allen]

Not an error. Int/Int uses integer division and results in an integer number. 
When one number is a float, the result becomes a float. 

I don't know about all SQL varieties, but MSSQL is the same. 

> On Apr 30, 2014, at 8:04 AM, "Gene Connor"  wrote:
> 
> 
> SELECT DISTINCT 2/4 AS RESULT FROM TABLE;returns 0
> SELECT DISTINCT 2/4.0 AS RESULT FROM TABLE;returns 0.5 (correct)
> SELECT DISTINCT 2.0/4 AS RESULT FROM TABLE;returns 0.5 (correct)
> SELECT DISTINCT 2.0/4.0 AS RESULT FROM TABLE;returns 0.5 (correct)
> SELECT DISTINCT 2.0/4.01 AS RESULT FROM TABLE;returns 0.49875 (correct)
> As long as one or both numbers has at least one decimal place, it calcs 
> correctly.
> Verified that it also happens in queries using tables and real data.
> Windows SQLite version 3.8.4.3 2014-04-03 16:53:12
> 
> Thanks 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

[Marc L. Allen]

So it does!  And, for those of us lucky enough to be on a system that isn't 8 
years old, enjoy! ;)

And now that I see that, I will renew push for upgrades. 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Petite Abeille
Sent: Tuesday, September 24, 2013 2:14 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement


On Sep 24, 2013, at 8:09 PM, Marc L. Allen <mlal...@outsitenetworks.com> wrote:

> Not complaining, mind you.  MS SQL doesn't have it, and I've long learned to 
> deal with it.

MS SQL Server sports a MERGE statement if I'm not mistaken:

http://msdn.microsoft.com/en-us/library/bb510625.aspx
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

[Marc L. Allen]

INSERT OR IGNORE? 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Tuesday, September 24, 2013 2:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement


On 24 Sep 2013, at 7:09pm, Marc L. Allen <mlal...@outsitenetworks.com> wrote:

> Also, there are times when you do a bulk insert, so you have to structure the 
> query to not fail on records that are already present.

Yeah.  Actually I got what I posted wrong.  I should have written

Which is why you do an INSERT OR FAIL, then do the UPDATE.
Or do an UPDATE first and iff that fails, do an INSERT.

The first version (INSERT OR FAIL, then UPDATE) won't lead to any SQLite errors 
if one of the rows already exists. So you can do a whole lot of both lines in 
one transaction and the transaction will still succeed.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

[Marc L. Allen]

Yes, of course.  But, it seems a waste of time, since you're obviously already 
at the record (or at least done the initial index search) to not be able to 
simply update it.

Not complaining, mind you.  MS SQL doesn't have it, and I've long learned to 
deal with it.

Also, there are times when you do a bulk insert, so you have to structure the 
query to not fail on records that are already present.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Tuesday, September 24, 2013 2:06 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement


On 24 Sep 2013, at 6:58pm, Petite Abeille <petite.abei...@gmail.com> wrote:

> On Sep 24, 2013, at 7:54 PM, "Marc L. Allen" <mlal...@outsitenetworks.com> 
> wrote:
> 
>> Yep.  What most people want is an INSERT OR UPDATE.  
> 
> Yep. Which is what one usually calls 'MERGE':
> 
> http://en.wikipedia.org/wiki/Merge_(SQL)
> 
> And sadly, SQLite doesn't provide anything like that at all.

Which is why you do an INSERT first, and allow it to fail, then do the UPDATE.
Or do an UPDATE first and iff that fails, do an INSERT.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

[Marc L. Allen]

Considered harmful?  How so?  I wouldn't mind a version of ON CONFLICT UPDATE 
fieldlist.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Petite Abeille
Sent: Tuesday, September 24, 2013 1:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement


On Sep 24, 2013, at 7:54 PM, "Marc L. Allen" <mlal...@outsitenetworks.com> 
wrote:

> Yep.  What most people want is an INSERT OR UPDATE.  

Yep. Which is what one usually calls 'MERGE':

http://en.wikipedia.org/wiki/Merge_(SQL)

And sadly, SQLite doesn't provide anything like that at all. Oh, well... 'ON 
CONFLICT clause' considered harmful. There you have it.



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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement

[Marc L. Allen]

Yep.  What most people want is an INSERT OR UPDATE.  

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Petite Abeille
Sent: Tuesday, September 24, 2013 1:48 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug : Tcl interface + "INSERT OR REPLACE" statement


On Sep 23, 2013, at 6:19 PM, Jean-Marie CUAZ  wrote:

> -> no exception is raised to host langage Tcl for 2 rows not inserted 
> -> because a UNIQUE constraint is not respected partial execution : 2 
> -> rows are definitely deleted from the table ...(ouch !)

Yep. Looks like it does exactly what it says on the tin: 

"When a UNIQUE constraint violation occurs, the REPLACE algorithm deletes 
pre-existing rows that are causing the constraint violation prior to inserting 
or updating the current row and the command continues executing normally. If a 
NOT NULL constraint violation occurs, the REPLACE conflict resolution replaces 
the NULL value with the default value for that column, or if the column has no 
default value, then the ABORT algorithm is used. If a CHECK constraint 
violation occurs, the REPLACE conflict resolution algorithm always works like 
ABORT."

http://www.sqlite.org/lang_conflict.html

Usually... with insight... 'replace' is rarely what one really wants.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hints for the query planner

[Marc L. Allen]

As I was reading this, I said to myself, "what they really need is a confidence 
value."  Then I read the end and, there it was!  A confidence value.  Ok.. not 
exactly confidence, but I think you get my meaning.

It seems to me that you're allowing the query writer to substitute personal 
knowledge of the DB for knowledge based on ANALYZE or other statistical 
indexes.  So, I'm all in favor of allowing that second argument.

If so, I would suggest "confidence(exp, confidence_value)".  Or, perhaps, 
"likelihood(..)"  Likely is fine, or you might even establish several names 
with built-in defaults... e.g. "likely(xxx)" might be "confidence(xxx, .75)" 
and "unlikely(xxx)" might be "confidence(xxx, .25)"  You've got "rarely," 
"mostly," and a whole suite of other synonyms.




This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Marc L. Allen]

Yes, thanks. I was mistaken. 

On Sep 6, 2013, at 9:27 PM, "James K. Lowden" <jklow...@schemamania.org> wrote:

> On Fri, 6 Sep 2013 07:56:53 -0500
> "Marc L. Allen" <mlal...@outsitenetworks.com> wrote:
> 
>> I don't think it's a bug.  
> 
> It is a bug as long as the behavior is in exception to the
> documentation. 
> 
>> I don't believe there's any defined rule for how SQL should behave,
>> is there?  
> 
> Of course there is.  Hundreds of pages describe SQL.  
> 
>> The updates are done serially not atomically.  
> 
> They're not *supposed* to be.  Updates are absolutely atomic.  Every
> SQL statement is atomic.  
> 
> --jkl
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Marc L. Allen]

MySQL also uses this implementation.  They acknowledge that it is not SQL 
compliant and that (I never thought of this), you cannot delete a record that 
has a foreign key link to itself.

Postgres apparently has the ability to have deferred checking as of V9, but not 
before then.

Please see:

http://stackoverflow.com/questions/5014700/in-mysql-can-i-defer-referential-integrity-checks-until-commit

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Friday, September 06, 2013 11:07 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question

On Fri, Sep 6, 2013 at 11:00 AM, Dominique Devienne wrote:

> Myself, if I'm "thinking in sets", all implementation details aside, 
> the UPDATE statement looks fine and correct, and I'd have expected 
> SQLite to support it.
>
> But I'm just waiting to read Dr. Hipp's own read on this now. --DD



I'm busy with a different problem and don't have time to study your thread, so 
I'm guessing at the answer:

The UPDATE statement in SQLite operates row-by-row.  The effect of early row 
updates might be visible in later row updates if you contrive a sufficiently 
complex example.  But you really have to go out of your way to do that.  If a 
constraint error happens, the entire UPDATE statement is rolled back (except if 
OR FAIL is specified - see the docs).

Yes, I know this is not "relational".  No, I do not intend to fix it.


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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Marc L. Allen]

Nice, but that still requires extra work.

1) Determine if row is already in table.
2) Determine next lower value.
3) Split difference and insert.

There's also the possibility that the higher level APP expects the new row to 
have a sequence number of 3.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of kyan
Sent: Friday, September 06, 2013 10:41 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question

On Thu, Sep 5, 2013 at 9:20 PM, Peter Haworth  wrote:

> I have a table with the following (simplified) structure
>
> PKeyINTEGER PRIMARY KEY
> NameTEXT
> Sequence INTEGER
>
> The Name/Sequence pair of columns is defined as UNIQUE
>
> I need to insert a new row into a point between two sequence numbers.  
> For example, if the existing rows are:
>
> Name   Sequence
> ---
> Blue   1
> Blue   2
> Blue3
> Blue   4
> Blue   5
>
> ... I might need to insert a new Blue/3.
>
> If the Sequence column needs not be consecutive integers but just
specifies order, consider turning it to a float. Then you can insert a row 
between two existing rows with Sequence a and b by using their median (a +
b) / 2 as the new row's Sequence:

Blue   1
Blue   2
--> Blue(2 + 3) / 2 = 2.5
Blue3
Blue   4
Blue   5

and then:

Blue   1
Blue   2
--> Blue2.25
Blue2.5
 Blue3
Blue   4
Blue   5

and so on. This way you avoid having to modify following rows on each insertion.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Marc L. Allen]

I don't think it's a bug.  I don't believe there's any defined rule for how SQL 
should behave, is there?  The updates are done serially not atomically.  If the 
rows happen to be processed in reverse order, then no constraint is violated.  
In fact, if there was a way to define the order the update was performed, it 
might be useful as a tool to solve this kind of problem.  

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dominique Devienne
Sent: Friday, September 06, 2013 3:28 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question


By forcing the "physical order" of the rows to be reversed, the UPDATE succeeds.
I suspect this is just a bug, and Dr Hipp will fix it (and if not document it 
somehow).




This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Marc L. Allen]

No one commented on my second thread (written after I actually understood the 
problem!).

But, I proposed a two update sequence to do it.  

UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND 
Name = name_to_insert

UPDATE table SET Sequence = -Sequence WHERE Sequence < 0  AND Name = 
name_to_insert

I've used this system many times to avoid conflicts, but it may not work where 
the table needs to be accessed concurrently, as rows will sort of disappear 
temporarily (or at least change to an unusable state).

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dominique Devienne
Sent: Friday, September 06, 2013 3:28 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] UPDATE question

On Fri, Sep 6, 2013 at 6:21 AM, James K. Lowden wrote:

> Here's how Marc L. Allen's query should work (different DBMS):
>
> $ bsqldb  < /tmp/sql
>PKey  Name   Sequence
> ---  --  ---
>   1  Blue  1
>   2  Blue  2
>   3  Blue  4
>   4  Blue  5
>   5  Blue  6
>   6  Blue  3
> 6 rows affected
>
> Note that the final insert is assigned the next auto-generated PKey 
> (6), and the old 3 is now 4, etc.
>

FWIW, a repro, taking the unrelated PKey out of the picture:

C:\Users\DDevienne>sqlite3
SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions 
Enter SQL statements terminated with a ";"
sqlite> create table jkl (name text, seq number, unique (name, seq)); 
sqlite> insert into jkl values ('blue', 1), ('blue', 2), ('blue', 3),
('blue', 4), ('blue', 5);
sqlite> select * from jkl;
blue|1
blue|2
blue|3
blue|4
blue|5
sqlite> update jkl set seq = seq + 1 where seq >= 3 and name = 'blue';
Error: columns name, seq are not unique
sqlite>

I tried to "ruse" and use

sqlite> update jkl set seq = seq + 1 where name = 'blue' and seq in 
sqlite> (select
seq from jkl where seq >= 3 order by seq desc);
Error: columns name, seq are not unique

but of course you cannot influence the processing order SQLite uses. OK, you 
can in a way, see below:

sqlite> create table jk2 as select * from jkl order by seq desc; select 
sqlite> * from jk2;
blue|5
blue|4
blue|3
blue|2
blue|1
sqlite> update jk2 set seq = seq + 1 where seq >= 3 and name = 'blue'; 
sqlite> select * from jk2;
blue|6
blue|5
blue|4
blue|2
blue|1
sqlite> insert into jk2 values ('blue', 3); select * from jk2;
blue|6
blue|5
blue|4
blue|2
blue|1
blue|3
sqlite>

By forcing the "physical order" of the rows to be reversed, the UPDATE succeeds.
I suspect this is just a bug, and Dr Hipp will fix it (and if not document it 
somehow).

For reference, the same in Oracle:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table jkl
  2  ( name varchar2(64)
  3  , seq  number
  4  , unique (name, seq)
  5  );

Table created.

SQL> insert into jkl values ('blue', 1);

1 row created.

SQL> insert into jkl values ('blue', 2);

1 row created.

SQL> insert into jkl values ('blue', 3);

1 row created.

SQL> insert into jkl values ('blue', 4);

1 row created.

SQL> insert into jkl values ('blue', 5);

1 row created.

SQL> commit;

Commit complete.

SQL> column name format a16;
SQL> select * from jkl;

NAMESEQ
 --
blue  1
blue  2
blue  3
blue  4
blue  5

SQL> update jkl set seq = seq + 1 where seq >= 3 and name = 'blue';

3 rows updated.

SQL> select * from jkl;

NAMESEQ
 --
blue  1
blue  2
blue  4
blue  5
blue  6

SQL> insert into jkl values ('blue', 3);

1 row created.

SQL> select * from jkl;

NAMESEQ
 --
blue  1
blue  2
blue  4
blue  5
blue  6
blue  3

6 rows selected.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly 

Re: [sqlite] UPDATE question

[Marc L. Allen]

Oops.. sorry.. I missed the last paragraph.  

If you're essentially single threaded.. I can do it in two updates...

UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND 
Name = name_to_insert
UPDATE table SET Sequence = -Sequence WHERE Sequence < 0  AND Name = 
name_to_insert

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Peter Haworth
Sent: Thursday, September 05, 2013 2:21 PM
To: sqlite-users
Subject: [sqlite] UPDATE question

I have a table with the following (simplified) structure

PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER

The Name/Sequence pair of columns is defined as UNIQUE

I need to insert a new row into a point between two sequence numbers.  For 
example, if the existing rows are:

Name   Sequence
---
Blue   1
Blue   2
Blue3
Blue   4
Blue   5

... I might need to insert a new Blue/3.

Currently I'm doing this by selecting the Pkey values of the entries with 
sequence >=3, sorting them by descending sequence, then a loop with an UPDATE 
to set the sequence to sequence+1 for each PKey value so 5 becomes 6, 4 becomes 
5, 3 becomes 4.  Then I INSERT a new Blue/3 row.

That works fine but wondering if there might be a single UPDATE statement that 
could do this for me.  I can use the WHERE clause to select sequence 3,4, and 5 
but  the UPDATE has to process the rows in descending sequence order to avoid 
UNIQUE conflicts.


Pete
lcSQL Software 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Marc L. Allen]

How about... ?

UPDATE table SET Sequence = Sequence + 1 WHERE Sequence >= seq_to_insert AND 
Name = name_to_insert

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Peter Haworth
Sent: Thursday, September 05, 2013 2:21 PM
To: sqlite-users
Subject: [sqlite] UPDATE question

I have a table with the following (simplified) structure

PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER

The Name/Sequence pair of columns is defined as UNIQUE

I need to insert a new row into a point between two sequence numbers.  For 
example, if the existing rows are:

Name   Sequence
---
Blue   1
Blue   2
Blue3
Blue   4
Blue   5

... I might need to insert a new Blue/3.

Currently I'm doing this by selecting the Pkey values of the entries with 
sequence >=3, sorting them by descending sequence, then a loop with an UPDATE 
to set the sequence to sequence+1 for each PKey value so 5 becomes 6, 4 becomes 
5, 3 becomes 4.  Then I INSERT a new Blue/3 row.

That works fine but wondering if there might be a single UPDATE statement that 
could do this for me.  I can use the WHERE clause to select sequence 3,4, and 5 
but  the UPDATE has to process the rows in descending sequence order to avoid 
UNIQUE conflicts.


Pete
lcSQL Software 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collating sequences. Was: Query problems

[Marc L. Allen]

The left-most of the first select?  Or the second?  Maybe I don't understand 
'left-most?'

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Thursday, September 05, 2013 9:36 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Collating sequences. Was: Query problems

On Thu, Sep 5, 2013 at 9:31 AM, Igor Tandetnik  wrote:

> On 9/5/2013 7:31 AM, Richard Hipp wrote:
>
>> There seems to be some misinformation circulating in this thread.  
>> Please let me try to clear up a few points.
>>
>
> While we are at it, an interesting question was raised upthread. What 
> happens here:
>
> create table t1(x text collate c1);
> create table t2(x text collate c2);
>
> select x from t1
> union
> select x from t2
> order by x;
>
> Which collation is used by UNION to deduplicate? Which collation is 
> used by ORDER BY to sort?
>


The left-most.  http://www.sqlite.org/src/artifact/8b148eb851f?ln=1582-1602

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Table name length in sqlite affects performance. Why?

[Marc L. Allen]

Not to mention having to check each new table to see if it's already in the 
database and the associated physical reads that might be associated with that.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jay A. Kreibich
Sent: Wednesday, September 04, 2013 8:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Table name length in sqlite affects performance. Why?

On Tue, Sep 03, 2013 at 06:43:52PM -0600, Jared Albers scratched on the wall:

> On my machine, when using relatively short table names like 
> `TABLE_{table #}`, creation of a database with 10,000 tables takes 
> approximately 14 seconds. These table names vary from 7 to a max of 11 
> characters.
> 
> When using relatively long table names like `TABLE_{table #}_{some 
> unique identifying name that adds 120 or so characters}`, creation of 
> a database with 10,000 tables takes approximately 60 seconds.
> 
> Creating the database with long table names took over 4 times longer!
> 
> Why is this the case? Is this expected behavior or a bug?

  You're asking SQLite to deal with 10x the data, and it takes 4x
  longer.  That seems like a win to me.

  Table names are stored as plain-text strings in the sqlite_master
  table.  Making the names much, much bigger is going to add more data
  to the table, including more database pages.  While sqlite_master is
  not ordered, so the insertion shouldn't require shuffling the
  internal B-tree, you're still dealing with a lot more pages, and
  syncing all those pages to disk is going to take longer.

  Like any other large insert, try wrapping the whole thing in a
  transaction.  SQLite is one of the few databases that allows
  transactions on DDL.

   -j

--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,  but showing 
it to the wrong people has the tendency to make them  feel uncomfortable." -- 
Angela Johnson ___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inefficient covering index used for Subversion with SQLite 3.8.0

[Marc L. Allen]

Am I understanding that, in this example, the I_NODES_PARENT is being chosen as 
the search index because... it's smaller and therefore faster to find initial 
qualifying rows that you can then use in some sort of ordered lookup in another 
index/table?

I'm always in awe of some of the plans a good QA comes up with, and it blows me 
away that there are cases when using a less-covering index would be better than 
a more-covering index.


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] segmentation fault with 3.8.0

[Marc L. Allen]

Thanks... that certainly clarifies it.  Also, thanks to Dan who responded 
similarly.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Thursday, August 29, 2013 11:58 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] segmentation fault with 3.8.0

On Thu, Aug 29, 2013 at 11:47 AM, Marc L. Allen <mlal...@outsitenetworks.com
> wrote:

> Silly question.. I looked at the fix.  Why ignore indexes with greater 
> than 4 fields?  Isn't that a bit risky?  Wouldn't it be better to 
> ignore the fields after the 4th one for planning?
>

The whereShortCut() routine is merely an optimization.  It provides a quick 
plan for the common case of a single-table query using an equality constraint 
on a PRIMARY KEY or UNIQUE index.  Anything that falls through
whereShortCut() goes into the regular query planner and will get analyzed 
thoroughly there.

You could omit the whereShortCut() routine entirely and SQLite would still give 
the correct answer.  The only downside is that sqlite3_prepare_v2() would run 
slightly slower in the common case.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] segmentation fault with 3.8.0

[Marc L. Allen]

Silly question.. I looked at the fix.  Why ignore indexes with greater than 4 
fields?  Isn't that a bit risky?  Wouldn't it be better to ignore the fields 
after the 4th one for planning? 

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Thursday, August 29, 2013 11:38 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] segmentation fault with 3.8.0

On Thu, Aug 29, 2013 at 11:32 AM, Stephen Chrzanowski
wrote:

> My guess is single quotes instead of double quotes.  Where exactly is 
> the seg fault?  Untested but other than the quotes, everything looks fine.
>

The problem is described here: http://www.sqlite.org/src/info/9f2eb3abac

The fix is here: http://www.sqlite.org/src/info/c1152bdcbb

A patch release 3.8.0.1 that includes this fix and fixes for two other equally 
obscure corner cases is currently in test and is expected to go out later today.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Different result from experimental query

[Marc L. Allen]

Looks like that should return one row, yes?  I wonder if operator precedence is 
broken for that query and the OR is binding higher than the AND.  Also possible 
is that the NOT NULL for id in table t is messing up some query optimization 
with t2.id NOT NULL.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of E.Pasma
Sent: Wednesday, August 28, 2013 10:20 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Different result from experimental query

An experimantal query, involving OUTER JOIN with BETWEEN and JOIN with a 
combined OR and AND expression, does not return all expected rows. I tried this 
just after SQLite 3.8.0. was released and found that the issue is particular to 
this version. At least it is alright in version 3.7.17.
Below is a simplified case. This is still complex but if you leave out anything 
further, the problem no longer occurs. Hope it is useful to report this.

.echo on
.version
SQLite 3.8.0 2013-08-26 04:50:08
f64cd21e2e23ed7cff48f7dafa5e76adde9321c2
CREATE TABLE t (id INTEGER PRIMARY KEY NOT NULL) ; INSERT INTO t VALUES (1) ; 
SELECT  *
FROMt t1
LEFT OUTER JOIN t t2 ON t2.id BETWEEN 10 AND 20
JOINt t3 ON
 (
 t3.id = t1.id
 OR  t2.id IS NOT NULL AND t3.id = t2.id
 )
;

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolutionn in GROUP BY

[Marc L. Allen]

Good point.  Ok.. I'm convinced.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of James K. Lowden
Sent: Thursday, August 15, 2013 12:27 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] name resolutionn in GROUP BY

On Wed, 14 Aug 2013 14:57:19 -0500
"Marc L. Allen" <mlal...@outsitenetworks.com> wrote:

> I'd actually like a compromise.  Allow GROUP BY to accept a derived
> name if no base name exists.   I realize that's against spec, but
> there's no ambiguity (as it otherwise errors out),

It would also mean the query's meaning could change if the underlying DDL 
changed.  If the column were later added (say, to a view) that happened to have 
the same name as that of the alias in the query, the DML would silently start 
using the "base" name instead of the derived one.  

That's not normally the case in SQL.  Normally, adding a column to a table/view 
cannot change the query's meaning.  Either the query is unaffected, or the new 
column introduces ambiguity (in the presence of a join) that causes it to 
return an error.  

The only safe and proper thing is for GROUP BY to refer to the column names as 
known to the database, not to aliases mentioned in the query. 

> and does make it much nicer when the derived column is a hairy 
> expression that I end up needing to replicate in the GROUP BY clause.

We do need a better language, yes.  

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolutionn in GROUP BY

[Marc L. Allen]

I'd actually like a compromise.  Allow GROUP BY to accept a derived name if no 
base name exists.   I realize that's against spec, but there's no ambiguity (as 
it otherwise errors out), and does make it much nicer when the derived column 
is a hairy expression that I end up needing to replicate in the GROUP BY clause.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, August 14, 2013 3:40 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 3:08 PM, Igor Tandetnik  wrote:

>
> Most DBMS allow sorting (and grouping) by arbitrary expressions, which 
> means that the standard is not directly applicable. One has to extrapolate.
>

PostgreSQL, MS-SQL, and SQLite 3.7.15 work one way.  Oracle and SQLite
3.7.17 work the other.  I think I'm going to revert SQLite to working as does 
PostgreSQL.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolution in GROUP BY

[Marc L. Allen]

Peter,

I'm sorry.. you're correct.  I missed that.

Marc

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Peter Aronson
Sent: Wednesday, August 14, 2013 2:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] name resolution in GROUP BY

Except the quote I provided said nothing about ORDER BY, just WHERE, GROUP BY 
or HAVING clauses.  So I'm not sure what all tests with ORDER BY are 
demonstrating, since the original question was about GROUP BY, which is a 
different thing, since ORDER BY operates strictly on the derived table.
 
Peter

From: Marc L. Allen <mlal...@outsitenetworks.com>
>To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>Sent: Wednesday, August 14, 2013 11:28 AM
>Subject: Re: [sqlite] name resolution in GROUP BY
>
>
>I understand.  My previous email had the values of your original request. This 
>email was in response to Peter who found a reference that you could not use 
>derived names in a ORDER BY clause.
>
>-Original Message-
>From: sqlite-users-boun...@sqlite.org 
>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp
>Sent: Wednesday, August 14, 2013 2:26 PM
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] name resolution in GROUP BY
>
>On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen
><mlal...@outsitenetworks.com>wrote:
>
>> Heh... I forgot.. both selects below are identical, as 'lower(m1)' is 
>> incorrect.  MS SQL does not permit further operations on the derived value.
>>
>
>I think you also missed the name ambiguity issue.  The queries are these:
>
>SELECT '1', substr(m,2) AS m
>  FROM t1
>ORDER BY m;
>
>SELECT '2', substr(m,2) AS m
>  FROM t1
>ORDER BY lower(m);
>
>Notice that the "m" in the ORDER BY clause might refer to column t1.m or it 
>might refer to the result set column labeled "AS m".  The question is which 
>one.  PostgreSQL answers t1.m for the first case and "AS m" for the second.  
>SQLite used to do that, but now it answers "t1.m" in both cases, which seems 
>to be a better fit to the SQL standard that Peter reports.
>
>Marc, if you can also try the query below on SQL Server, that would be 
>most
>helpful:
>
>SELECT '3', substr(m,2) AS m
>  FROM t1
>ORDER BY m COLLATE Latin1_General_CS_AS;
>
>
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>This email and any attachments are only for use by the intended recipient(s) 
>and may contain legally privileged, confidential, proprietary or otherwise 
>private information. Any unauthorized use, reproduction, dissemination, 
>distribution or other disclosure of the contents of this e-mail or its 
>attachments is strictly prohibited. If you have received this email in error, 
>please notify the sender immediately and delete the original.
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolutionn in GROUP BY

[Marc L. Allen]

This appears to be how MS SQL handles it... looking at the definitions below, 
MS SQL uses the base value in GROUP BY and the derived value in ORDER BY.  

That said, 'lower(m)' referenced the base m, not the derived m in the ORDER BY. 
 I'm afraid I don't understand enough about COLLATE to get why that changed the 
result.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Wednesday, August 14, 2013 2:27 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] name resolutionn in GROUP BY

On 8/14/2013 12:59 PM, Richard Hipp wrote:
> On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn
> wrote:
>
>> create table test(name);
>> insert into test values (NULL);
>> insert into test values ('abc');
>>
>> select count(),
>> NULLIF(name,'abc') AS name
>> from test
>> group by lower(name);
>>
>
> So the question is, should the "name" symbol in the GROUP BY clause 
> refer to the original column name in the TEST table, or should it 
> refer to the result column called "name".  SQLite version 3.7.15 picks 
> the TEST table column.  Version 3.7.17 picks the result column.
>
> Anybody know which is correct?

For what it's worth, my reading of SQL-92 suggests that 3.7.15 is correct.

1) If no  is specified, then let T be the result of
 the preceding ; otherwise, let T be the result of
 the preceding .
2) Each  in the  shall unambigu-
 ously reference a column of T.

ORDER BY clause is different - column names there resolve in the context of the 
whole preceding SELECT statement, not just its FROM part:

  3) Let T be the table specified by the .
  4) If ORDER BY is specified, then each  in the
  shall identify a column of T.

--
Igor Tandetnik

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolution in GROUP BY

[Marc L. Allen]

Ok... looks like MSSQL 2008 R2 picks a different value of 'm' for cases 1 and 
2.  I'm not sure why 3 isn't the same as 1, though.

CREATE TABLE #t1(m VARCHAR(4));
INSERT INTO #t1 VALUES('az');
INSERT INTO #t1 VALUES('by');
INSERT INTO #t1 VALUES('cx');

SELECT '1', right(m,1) AS m
  FROM #t1
 ORDER BY m;

SELECT '2', right(m,1) AS m
  FROM #t1
 ORDER BY LOWER(m)

SELECT '3', right(m,1) AS m
  FROM #t1
 ORDER BY m COLLATE Latin1_General_CS_AS;

Untitled1  m
-  -
1  x
1  y
1  z


Untitled1  m
-  -
2  z
2  y
2  x


Untitled1  m
-  -
3  z
3  y
3  x




-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, August 14, 2013 2:26 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] name resolution in GROUP BY

On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen
<mlal...@outsitenetworks.com>wrote:

> Heh... I forgot.. both selects below are identical, as 'lower(m1)' is 
> incorrect.  MS SQL does not permit further operations on the derived value.
>

I think you also missed the name ambiguity issue.  The queries are these:

SELECT '1', substr(m,2) AS m
  FROM t1
 ORDER BY m;

SELECT '2', substr(m,2) AS m
  FROM t1
 ORDER BY lower(m);

Notice that the "m" in the ORDER BY clause might refer to column t1.m or it 
might refer to the result set column labeled "AS m".  The question is which 
one.  PostgreSQL answers t1.m for the first case and "AS m" for the second.  
SQLite used to do that, but now it answers "t1.m" in both cases, which seems to 
be a better fit to the SQL standard that Peter reports.

Marc, if you can also try the query below on SQL Server, that would be most
helpful:

SELECT '3', substr(m,2) AS m
  FROM t1
 ORDER BY m COLLATE Latin1_General_CS_AS;


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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolution in GROUP BY

[Marc L. Allen]

I understand.  My previous email had the values of your original request. This 
email was in response to Peter who found a reference that you could not use 
derived names in a ORDER BY clause.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, August 14, 2013 2:26 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] name resolution in GROUP BY

On Wed, Aug 14, 2013 at 2:18 PM, Marc L. Allen
<mlal...@outsitenetworks.com>wrote:

> Heh... I forgot.. both selects below are identical, as 'lower(m1)' is 
> incorrect.  MS SQL does not permit further operations on the derived value.
>

I think you also missed the name ambiguity issue.  The queries are these:

SELECT '1', substr(m,2) AS m
  FROM t1
 ORDER BY m;

SELECT '2', substr(m,2) AS m
  FROM t1
 ORDER BY lower(m);

Notice that the "m" in the ORDER BY clause might refer to column t1.m or it 
might refer to the result set column labeled "AS m".  The question is which 
one.  PostgreSQL answers t1.m for the first case and "AS m" for the second.  
SQLite used to do that, but now it answers "t1.m" in both cases, which seems to 
be a better fit to the SQL standard that Peter reports.

Marc, if you can also try the query below on SQL Server, that would be most
helpful:

SELECT '3', substr(m,2) AS m
  FROM t1
 ORDER BY m COLLATE Latin1_General_CS_AS;


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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolution in GROUP BY

[Marc L. Allen]

Heh... I forgot.. both selects below are identical, as 'lower(m1)' is 
incorrect.  MS SQL does not permit further operations on the derived value.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marc L. Allen
Sent: Wednesday, August 14, 2013 2:17 PM
To: Peter Aronson; General Discussion of SQLite Database
Subject: Re: [sqlite] name resolution in GROUP BY

I seem to recall having read that as well.  I believe, however, that MySQL does 
allow it,  but I think it defaults to base table when available.

Also, a modified form of the test case:

DROP TABLE #t1
CREATE TABLE #t1(m VARCHAR(4));
INSERT INTO #t1 VALUES('az');
INSERT INTO #t1 VALUES('by');
INSERT INTO #t1 VALUES('cx');

SELECT '1', right(m,1) AS m1
  FROM #t1
 ORDER BY m1;

SELECT '2', right(m,1) AS m1
  FROM #t1
 ORDER BY m1

Works, returning:

Untitled1  m1
-  --
1  x
1  y
1  z


Untitled1  m1
-  --
2  x
2  y
2  z



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Peter Aronson
Sent: Wednesday, August 14, 2013 2:13 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] name resolution in GROUP BY

I dug out my copy of THE GUIDE TO THE SQL STANDARD, 4th Edition, by Date and 
Darwen, and it states (in a footnote on page 151) that name specified for a 
scalar-expression in a SELECT clause can not be used in a WHERE, GROUP BY or 
HAVING clause as it is a column in the derived table, not the base table.
 
Peter

From: Richard Hipp <d...@sqlite.org>
>To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>Sent: Wednesday, August 14, 2013 9:59 AM
>Subject: Re: [sqlite] name resolutionn in GROUP BY
>
>
>On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn
><rob.golste...@mapscape.eu>wrote:
>
>> create table test(name);
>> insert into test values (NULL);
>> insert into test values ('abc');
>>
>> select count(),
>>        NULLIF(name,'abc') AS name
>> from test
>> group by lower(name);
>>
>
>So the question is, should the "name" symbol in the GROUP BY clause 
>refer to the original column name in the TEST table, or should it refer 
>to the result column called "name".  SQLite version 3.7.15 picks the 
>TEST table column.  Version 3.7.17 picks the result column.
>
>Anybody know which is correct?
>
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolution in GROUP BY

[Marc L. Allen]

I seem to recall having read that as well.  I believe, however, that MySQL does 
allow it,  but I think it defaults to base table when available.

Also, a modified form of the test case:

DROP TABLE #t1
CREATE TABLE #t1(m VARCHAR(4));
INSERT INTO #t1 VALUES('az');
INSERT INTO #t1 VALUES('by');
INSERT INTO #t1 VALUES('cx');

SELECT '1', right(m,1) AS m1
  FROM #t1
 ORDER BY m1;

SELECT '2', right(m,1) AS m1
  FROM #t1
 ORDER BY m1

Works, returning:

Untitled1  m1
-  --
1  x
1  y
1  z


Untitled1  m1
-  --
2  x
2  y
2  z



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Peter Aronson
Sent: Wednesday, August 14, 2013 2:13 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] name resolution in GROUP BY

I dug out my copy of THE GUIDE TO THE SQL STANDARD, 4th Edition, by Date and 
Darwen, and it states (in a footnote on page 151) that name specified for a 
scalar-expression in a SELECT clause can not be used in a WHERE, GROUP BY or 
HAVING clause as it is a column in the derived table, not the base table.
 
Peter

From: Richard Hipp 
>To: General Discussion of SQLite Database 
>Sent: Wednesday, August 14, 2013 9:59 AM
>Subject: Re: [sqlite] name resolutionn in GROUP BY
>
>
>On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn
>wrote:
>
>> create table test(name);
>> insert into test values (NULL);
>> insert into test values ('abc');
>>
>> select count(),
>>        NULLIF(name,'abc') AS name
>> from test
>> group by lower(name);
>>
>
>So the question is, should the "name" symbol in the GROUP BY clause 
>refer to the original column name in the TEST table, or should it refer 
>to the result column called "name".  SQLite version 3.7.15 picks the 
>TEST table column.  Version 3.7.17 picks the result column.
>
>Anybody know which is correct?
>
>--
>D. Richard Hipp
>d...@sqlite.org
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] name resolutionn in GROUP BY

[Marc L. Allen]

As does MS SQL 2008 R2

DROP TABLE #Test
CREATE TABLE #Test ( Val int )
INSERT INTO [#Test] ([Val]) VALUES (-2), (2)
SELECT Val FROM #Test GROUP BY Val
SELECT ABS(Val) AS Val FROM #Test GROUP BY Val

Val
---
-2
2


Val
---
2
2

Your requested test case:

Untitled1  m
-  -
1  x
1  y
1  z


Untitled1  m
-  -
2  z
2  y
2  x

(I used the following code)

CREATE TABLE #t1(m VARCHAR(4));
INSERT INTO #t1 VALUES('az');
INSERT INTO #t1 VALUES('by');
INSERT INTO #t1 VALUES('cx');

SELECT '1', right(m,1) AS m
  FROM #t1
 ORDER BY m;

SELECT '2', right(m,1) AS m
  FROM #t1
 ORDER BY lower(m);

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dominique Devienne
Sent: Wednesday, August 14, 2013 2:00 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] name resolutionn in GROUP BY

On Wed, Aug 14, 2013 at 6:59 PM, Richard Hipp  wrote:

> On Wed, Aug 14, 2013 at 12:22 PM, Rob Golsteijn
> wrote:
>
> > create table test(name);
> > insert into test values (NULL);
> > insert into test values ('abc');
> >
> > select count(),
> >NULLIF(name,'abc') AS name
> > from test
> > group by lower(name);
> >
>
> So the question is, should the "name" symbol in the GROUP BY clause 
> refer to the original column name in the TEST table, or should it 
> refer to the result column called "name".  SQLite version 3.7.15 picks 
> the TEST table column.  Version 3.7.17 picks the result column.
>
> Anybody know which is correct?
>

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ...
SQL> create table test (name varchar2(64));
Table created.
SQL> insert into test values (NULL);
1 row created.
SQL> insert into test values ('abc');
1 row created.
SQL> select count(*), max(nullif(name, 'abc')) as name from test group 
SQL> by
lower(name);
  COUNT(*) NAME
-- 
 1
 1
 SQL> select count(*), nullif(lower(name), 'abc') as name from test group by 
lower(name);
  COUNT(*) NAME
-- 
 1
 1

Not authoritative of course, but Oracle seems to agree with the previous 
behavior. --DD ___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] attaching databases programmatically

[Marc L. Allen]

Perhaps I misunderstood the question.  It sounds like he has the sqlite* 
objects for the databases, but wants to be able to determine the 
database/filename associated with them so he can construct an ATTACH statement 
in another query.

So.. the question is.. given an sqlite*, can you determine the underlying 
database/filename?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dušan Paulovic
Sent: Monday, July 22, 2013 10:35 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] attaching databases programmatically

Perhaps you can create new connection with multiple virtual tables reading from 
all connections. But it is hard work...


2013/7/22 Nelson, Erik - 2 

> I've got an application that allows the user to create an arbitrary 
> number of databases, either in memory or not.  In my C++ program, I 
> have the handles and I'd like to attach them all together so the user 
> can execute queries against them.  However, the only way that I've 
> found to do that is to use the "ATTACH" sql.  The problem is that the 
> sqlite3* exist, but the databases aren't in any namespace accessible to the 
> "ATTACH" query.
>
> Is there some way to programmatically attach databases when all you 
> have are the sqlite3 handles?
>
> I've read the list history, most of the conversations about this are a 
> few years back.
>
> Thanks
>
> Erik
>
> --
> This message, and any attachments, is for the intended recipient(s) 
> only, may contain information that is privileged, confidential and/or 
> proprietary and subject to important terms and conditions available at
> http://www.bankofamerica.com/emaildisclaimer.   If you are not the
> intended recipient, please delete this message.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] attaching databases programmatically

[Marc L. Allen]

I see.  Sorry about that!

I guess the real problem is the in-memory ones.  The other ones must have a 
database file associated with them, right?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Nelson, Erik - 2
Sent: Monday, July 22, 2013 10:51 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] attaching databases programmatically

Marc L. Allen wrote on Monday, July 22, 2013 10:47 AM
> Nelson, Erik wrote:
> > I've got an application that allows the user to create an arbitrary 
> > number of databases, either in memory or not.  In my C++ program, I 
> > have the handles and I'd like to attach them all together so the 
> > user can execute queries against them.  However, the only way that 
> > I've found to do that is to use the "ATTACH" sql.  The problem is 
> > that the
> > sqlite3* exist, but the databases aren't in any namespace accessible
> to the "ATTACH" query.
> >
> > Is there some way to programmatically attach databases when all you 
> > have are the sqlite3 handles?

> Perhaps I misunderstood the question.  It sounds like he has the
> sqlite* objects for the databases, but wants to be able to determine 
> the database/filename associated with them so he can construct an 
> ATTACH statement in another query.
> 
> So.. the question is.. given an sqlite*, can you determine the 
> underlying database/filename?

Yes, you misunderstood it the databases are programmatically generated- 
there is no file name.


Erik

--
This message, and any attachments, is for the intended recipient(s) only, may 
contain information that is privileged, confidential and/or proprietary and 
subject to important terms and conditions available at 
http://www.bankofamerica.com/emaildisclaimer.   If you are not the intended 
recipient, please delete this message.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is pragma foreign_key_check fast enough to be used in normal application flow?

[Marc L. Allen]

It's exhaustive in that it absolutely verifies if the key exists or not.  
However, it doesn't necessarily do a full database scan.  I assume it uses 
available indexes and does a standard lookup on the key.

So, it still might be fast enough for what you want (though I missed the 
beginning of the thread).

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Friday, May 24, 2013 9:40 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Is pragma foreign_key_check fast enough to be used in 
normal application flow?

On Fri, May 24, 2013 at 7:07 AM, kyan  wrote:

>
> So my question is, is [the foreign_key_check] pragma exhaustive or is 
> it somehow optimised so that it does not always perform a full 
> database scan -e.g. by means of some internal per-transaction FK 
> violation counter or list? Because if it is optimised and thus fast 
> enough then I suppose I can try to use it for the purpose I have 
> described.
>


It is exhaustive


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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite NULL or 0-Length

[Marc L. Allen]

No.  All SQL functions can safely take NULL as an argument.

LENGTH(NULL) returns NULL, so LENGTH(NULL) = 0 is always false.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Rob Richardson
Sent: Thursday, May 23, 2013 3:34 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite NULL or 0-Length

Simon,

Is there a danger here if firstname is NULL and the LENGTH() function is called 
first?  

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Thursday, May 23, 2013 2:54 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite NULL or 0-Length


On 23 May 2013, at 7:06pm, Jill Rabinowitz  wrote:

> Does anyone know how I can check the column and set it to a value if 
> it has nothing in it?

UPDATE myTable SET firstname='xxx' WHERE LENGTH(firstname) = 0 OR firstname IS 
NULL

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max of 63 columns for a covering index to work?

[Marc L. Allen]

I'm not familiar with that.  It's a "view" where Oracle actually stores the 
view data as a physical table?  And updates these tables as the main table 
updates?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Dominique Devienne
Sent: Thursday, May 23, 2013 8:57 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

On Wed, May 22, 2013 at 9:11 PM, Marc L. Allen
<mlal...@outsitenetworks.com>wrote:

> [...]. It makes me think you might be better off using triggers to 
> maintain separate tables with covered data instead of indexes.  [...].
>

This sounds like Oracle's materialized views to me, which come in synchronous 
(trigger-based) or asynchronous (log-mining-based) variants.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Potential bug in crash-recovery code: unlink() and friends are not synchronous

[Marc L. Allen]

Just to throw in my $0.02 as a user

Given the SQL stream of...


COMMIT


Vs.





Except in cases where, in the first example, I have time to inform someone 
about the COMMIT before the power loss, there's no functional difference 
between the two events.  I would hate to think I would ever demand that SQLite 
guarantee the commit occurs regardless of how quickly the power loss happens 
after the commit statement.

For a huge majority of the applications I've dealt with (and I say huge instead 
of all only in case there's one I've forgotten about), the timing difference 
doesn't matter.  What matters is that when I come back up I have an intact 
database.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of thanumalayan mad
Sent: Wednesday, May 22, 2013 8:31 AM
To: Richard Hipp
Cc: General Discussion of SQLite Database
Subject: Re: [sqlite] Potential bug in crash-recovery code: unlink() and 
friends are not synchronous

I do not observe any loss in durability in WAL mode: it works totally fine.

As for the documentation, http://www.sqlite.org/transactional.html and 
http://www.sqlite.org/features.html claim that SQLite is durable during power 
failures; and DELETE is the default journal_mode. Also, other pages, 
http://www.sqlite.org/pragma.html#pragma_synchronous,
http://www.sqlite.org/atomiccommit.html, and http://www.sqlite.org/faq.html, 
made me think that rollback journaling ensures durability (given a honest disk 
and a "honest" OS), although those pages do not "legally" say that; I'm not 
sure if others would understand it this way though. The usual opinion in 
blogosphere (and forums) also seems to be that SQLite is, by default, durable 
across power failures, though they might be meaning only about a "5 second 
eventual durability". Finally, (I hope I do not come across here as being 
authoritative), a quick fix might be explicitly mentioning somewhere in the 
documentation that DELETE and TRUNCATE modes do not ensure immediate durability 
after a power loss; this would combat any wrongly-understood claims in the rest 
of the documentation.

Also, not to spam, but it would be great if you could answer these questions 
for my research (you might send me a reply directly without going through the 
mailing list): [a] Was it always understood that unlink() and
ftruncate() are not synchronous, and that SQLite transactions in DELETE mode 
are not immediately-durable in Linux; or had you initially misunderstood the 
semantics of those calls, or left-off the fsync() because of a typo error? [b] 
While designing the crash-tests, were the semantics of the calls in Unix 
understood? What if ftruncate() not being synchronous did lead to a 
consistency-loss? Was it reasoned-out that the non-synchronous ftruncate would 
not produce corruption? [c] How much of a loss in durability (what other than 5 
seconds) would be "good enough" in most cases? ... Again, sorry for the 
spam; my research is trying to make sense of the flushing-mess in the entire 
storage stack, and feedback would be extremely useful.

PS: @Richard Hipp and other developers - thanks for SQLite! Amazing piece of 
software.

--
Thanumalayan Sankaranarayana Pillai
(Graduate student at the University of Wisconsin-Madison)


On Wed, May 22, 2013 at 5:49 AM, Richard Hipp  wrote:

>
>
> On Sat, May 18, 2013 at 4:41 AM, thanumalayan mad wrote:
>
>>
>> Expected result: You always find that the transaction had been executed.
>> Observed result: You sometimes find that the transaction did not execute.
>>
>
> The core team has discussed this.  In order to avoid a substantial 
> performance hit against transaction COMMIT, we have chosen to not do 
> fsyncs on the directory when a file is unlinked, and thus to allow 
> loss of durability following a power loss event.  ACI without the D is 
> still guaranteed.  But not the D.  The overwhelming majority of 
> applications care not one wit about durability following power loss.  
> For most applications, it is sufficient that the file is uncorrupted.  
> If recovery gives you a snapshot of the file as it existed 5 seconds 
> prior to the power loss, that's fine.
>
> WAL-mode transactions should be durable across power-loss events.  So 
> if durability is vitally important to you, you can always set PRAGMA 
> journal_mode=WAL.  Are you observing loss of durability following 
> power loss in WAL mode?
>
> Is there any place in the documentation that we have overlooked where 
> SQLite claims to be durable across a power loss in rollback mode?
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 

Re: [sqlite] Max of 63 columns for a covering index to work?

[Marc L. Allen]

I haven't had a table that large, but I have had big ones... the disadvantage 
is the number of records you can scan in a single disk read, but an advantage 
is that you don't have to take the time to join tables, especially when you 
need to do it ALL THE TIME.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Wednesday, May 22, 2013 3:11 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?


On 22 May 2013, at 7:58pm, David de Regt  wrote:

>  if I have a 300 column table

I'm going to sound my customary note of caution here.  Do you really have a 300 
column table or is it several thinner tables which have the same primary key ?  
Or do you really have a property list which should be one thinner table with a 
two-column primary key ?

Generally in database design you should be able to hold a table schema in your 
head.  When you find yourself numbering columns it's usually a sign you're 
doing something wrong.

Not true in every case, of course, and you may have one of the incredibly rare 
cases which really is best represented with a 300 column table.  In which case, 
please excuse me.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Max of 63 columns for a covering index to work?

[Marc L. Allen]

I was just thinking of that... if you have needs to dozens of covering indexes, 
then the index maintenance anytime you modify the table must be enormous. It 
makes me think you might be better off using triggers to maintain separate 
tables with covered data instead of indexes.  The only downside to that, I 
suppose, is that you have to pick the right table when doing the select.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David de Regt
Sent: Wednesday, May 22, 2013 3:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

Correct.  However, we have a pile of different uses on this table.  I'm trying 
to evaluate if we can move all covering index columns into the first 63, but 
I'm not sure it's going to work, especially long term as we continue to grow 
the data.  We'll see...

In the medical industry I used to work in, there were commonly huge denorm 
"event" tables in multiple massive information systems, with covering indexes 
on several dozen different sets of large numbers of columns.  With 100+ million 
records in the table, it was the only way to read from it in a performant 
fashion.

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marc L. Allen
Sent: Wednesday, May 22, 2013 12:02 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

I think there might be a disconnect.

You can have a covering index on a 300 column table... it just can't cover any 
column past the 63rd (or 64th?).

It's not perfect, but not as bad as not being able to have a covering index at 
all.

At least, that's how I read some of the answers.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David de Regt
Sent: Wednesday, May 22, 2013 2:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

Hm.  That's a wee bit of an issue for us, then.  May want to stick that on the 
limitations page... :)

It seems like covering indexes become increasingly useful the more columns you 
have on a table.  When I have a 4-column table, if my covering index uses 3 
columns, that's not as big a read savings as if I have a 300 column table that 
I only need to handle 3 columns from in a WHERE, and it otherwise needs to pull 
the row/page from the original table to get the value on.

Back to the trenches to rearchitect this...

Thanks for the quick clarification. :)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, May 22, 2013 11:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

On Wed, May 22, 2013 at 2:37 PM, David de Regt <dav...@mylollc.com> wrote:

> I'm experimenting with covering indices on one of our larger tables.
>
> *[many words expressing concern that SQLlite does not use covering 
> indices on tables with more than 63 colums]...*
>
>
Your observations are correct.  If a query uses any column of a table past the 
63rd column, then that query cannot use a covering index on that table.  This 
is due to the use of 64-bit unsigned integer bitmasks to keep track of which 
columns have been used in order to discover whether or not a covering index 
will work.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of 

Re: [sqlite] Max of 63 columns for a covering index to work?

[Marc L. Allen]

I think there might be a disconnect.

You can have a covering index on a 300 column table... it just can't cover any 
column past the 63rd (or 64th?).

It's not perfect, but not as bad as not being able to have a covering index at 
all.

At least, that's how I read some of the answers.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of David de Regt
Sent: Wednesday, May 22, 2013 2:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

Hm.  That's a wee bit of an issue for us, then.  May want to stick that on the 
limitations page... :)

It seems like covering indexes become increasingly useful the more columns you 
have on a table.  When I have a 4-column table, if my covering index uses 3 
columns, that's not as big a read savings as if I have a 300 column table that 
I only need to handle 3 columns from in a WHERE, and it otherwise needs to pull 
the row/page from the original table to get the value on.

Back to the trenches to rearchitect this...

Thanks for the quick clarification. :)

-David

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, May 22, 2013 11:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Max of 63 columns for a covering index to work?

On Wed, May 22, 2013 at 2:37 PM, David de Regt  wrote:

> I'm experimenting with covering indices on one of our larger tables.
>
> *[many words expressing concern that SQLlite does not use covering 
> indices on tables with more than 63 colums]...*
>
>
Your observations are correct.  If a query uses any column of a table past the 
63rd column, then that query cannot use a covering index on that table.  This 
is due to the use of 64-bit unsigned integer bitmasks to keep track of which 
columns have been used in order to discover whether or not a covering index 
will work.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[Marc L. Allen]

Oops.. nevermind.  You already had that covered. ;)

That'll teach me to answer on my phone before reading the revised draft.


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Richard Hipp [d...@sqlite.org]
Sent: Tuesday, April 30, 2013 6:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query optimizer bug?

On Tue, Apr 30, 2013 at 5:46 PM, Marc L. Allen
<mlal...@outsitenetworks.com>wrote:

> In looking at the draft plan... am I right in assuming that at any 'stop'
> you can eliminate paths which have consumed the identical set of nodes but
> are more expensive?
>

Yes.  Good idea.  I have updated the document accordingly.

Note that by merging equivalent plans, the optimal plan for TPC-H Q8 is
found with N==10 instead of N==21.


>
> For instance, at stop 2, the draft shows:
>
> R-N1 (cost: 7.03)
> N1-R (cost: 7.31)
> R-N2 (cost: 9.08)
> N2-R (cost: 9.08)
>
> Since the first two consume both R and N1, will N1-R ever be a better path
> than R-N1?  If not, then it can be removed from the set, reducing the
> amount of work later.  Similarly with R-N2 and N2-R.  Since they consume
> the same nodes and have identical costs, does it matter which one is used?
>  Won't any path R-N2-x-y-z.. be identical to N2-R-x-y-z?
>
> Maybe I'm over simplifying?
>
> Marc
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Tuesday, April 30, 2013 5:34 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Query optimizer bug?
>
> On 4/30/2013 5:26 PM, Ryan Johnson wrote:
> > Being able to force certain access methods (use *this* index, not that
> > one) would be helpful, though (does "+" do that or just suggest it?).
>
> Unary plus turns a simple column reference (for which an index can be
> used) into an expression (which cannot be indexed). This may be used to
> suppress the choice of a particular index, at which point the query planner
> is likely to choose a different index, often rearranging the joins. So the
> unary plus does often influence the query plan, but in a rather roundabout
> way.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> This email and any attachments are only for use by the intended
> recipient(s) and may contain legally privileged, confidential, proprietary
> or otherwise private information. Any unauthorized use, reproduction,
> dissemination, distribution or other disclosure of the contents of this
> e-mail or its attachments is strictly prohibited. If you have received this
> email in error, please notify the sender immediately and delete the
> original.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[Marc L. Allen]

Another thought... since you are limiting yourself to a maximum number of paths 
at any given time, if you're willing to take the full hit for maintaining the 
full N=30 (or whatever) paths, instead of simply eliminating worse paths with 
identical nodes, allow yourself to continue finding more paths to bring your 
total back up to 30.

For instance, in the example with N=4, and N1-R would be eliminated, you could 
take the '5th best' path to maintain your count of 4.  So, you'd be taking the 
best N paths that have a unique set of nodes.

I don't know if that will give enough bang for your buck, as the extra lower 
paths might rarely yield a final better result.

Marc


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On 
Behalf Of Richard Hipp [d...@sqlite.org]
Sent: Tuesday, April 30, 2013 6:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Query optimizer bug?

On Tue, Apr 30, 2013 at 5:46 PM, Marc L. Allen
<mlal...@outsitenetworks.com>wrote:

> In looking at the draft plan... am I right in assuming that at any 'stop'
> you can eliminate paths which have consumed the identical set of nodes but
> are more expensive?
>

Yes.  Good idea.  I have updated the document accordingly.

Note that by merging equivalent plans, the optimal plan for TPC-H Q8 is
found with N==10 instead of N==21.


>
> For instance, at stop 2, the draft shows:
>
> R-N1 (cost: 7.03)
> N1-R (cost: 7.31)
> R-N2 (cost: 9.08)
> N2-R (cost: 9.08)
>
> Since the first two consume both R and N1, will N1-R ever be a better path
> than R-N1?  If not, then it can be removed from the set, reducing the
> amount of work later.  Similarly with R-N2 and N2-R.  Since they consume
> the same nodes and have identical costs, does it matter which one is used?
>  Won't any path R-N2-x-y-z.. be identical to N2-R-x-y-z?
>
> Maybe I'm over simplifying?
>
> Marc
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:
> sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
> Sent: Tuesday, April 30, 2013 5:34 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Query optimizer bug?
>
> On 4/30/2013 5:26 PM, Ryan Johnson wrote:
> > Being able to force certain access methods (use *this* index, not that
> > one) would be helpful, though (does "+" do that or just suggest it?).
>
> Unary plus turns a simple column reference (for which an index can be
> used) into an expression (which cannot be indexed). This may be used to
> suppress the choice of a particular index, at which point the query planner
> is likely to choose a different index, often rearranging the joins. So the
> unary plus does often influence the query plan, but in a rather roundabout
> way.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> This email and any attachments are only for use by the intended
> recipient(s) and may contain legally privileged, confidential, proprietary
> or otherwise private information. Any unauthorized use, reproduction,
> dissemination, distribution or other disclosure of the contents of this
> e-mail or its attachments is strictly prohibited. If you have received this
> email in error, please notify the sender immediately and delete the
> original.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query optimizer bug?

[Marc L. Allen]

In looking at the draft plan... am I right in assuming that at any 'stop' you 
can eliminate paths which have consumed the identical set of nodes but are more 
expensive?

For instance, at stop 2, the draft shows:

R-N1 (cost: 7.03)
N1-R (cost: 7.31)
R-N2 (cost: 9.08)
N2-R (cost: 9.08)

Since the first two consume both R and N1, will N1-R ever be a better path than 
R-N1?  If not, then it can be removed from the set, reducing the amount of work 
later.  Similarly with R-N2 and N2-R.  Since they consume the same nodes and 
have identical costs, does it matter which one is used?  Won't any path 
R-N2-x-y-z.. be identical to N2-R-x-y-z?

Maybe I'm over simplifying? 

Marc

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Tuesday, April 30, 2013 5:34 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Query optimizer bug?

On 4/30/2013 5:26 PM, Ryan Johnson wrote:
> Being able to force certain access methods (use *this* index, not that 
> one) would be helpful, though (does "+" do that or just suggest it?).

Unary plus turns a simple column reference (for which an index can be
used) into an expression (which cannot be indexed). This may be used to 
suppress the choice of a particular index, at which point the query planner is 
likely to choose a different index, often rearranging the joins. So the unary 
plus does often influence the query plan, but in a rather roundabout way.
--
Igor Tandetnik

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?

[Marc L. Allen]

Sorry... replied to the wrong message. :(

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marc L. Allen
Sent: Thursday, April 18, 2013 1:40 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?

It has around 500 context switches per second.. so I'm thinking MosYield.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of markus diersbock
Sent: Thursday, April 18, 2013 1:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?

Put SQLite.Interop.dll in the same folder as your VB assembly (ie /debug or 
/release)


On Thu, Apr 18, 2013 at 9:22 AM, Gilles Ganault <gilles.gana...@free.fr> wrote:
> Hello,
>
> I'm having a problem getting VB Express to find SQLite .Net:
>
> 1. I went to...
> http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
> ... to download sqlite-netFx45-binary-Win32-2012-1.0.84.0.zip
>
> 2. Unzipped file in C:\Downloads\SQLite\.Net\1.0.84.0\
>
> 3. Launched VB Express 2010 to create new Windows Form project
>
> 4. Project > Add Reference: Navigated to 
> "C:\Downloads\SQLite\.Net\1.0.84.0\" and pointed to 
> System.Data.SQLite.dll
>
> 5. Wrote SQLite test code:
>
> Dim SQLconnect As New SQLite.SQLiteConnection()
> Dim SQLcommand As SQLiteCommand
>
> 'Note: Non-admin app cannot write to c:\
> SQLconnect.ConnectionString = "Data 
> Source=c:\users\fred\test.sqlite;"
> SQLconnect.Open()
>
> 'SQLcommand = SQLconnect.CreateCommand
>
> 'SQLcommand.CommandText = "CREATE TABLE IF NOT EXISTS Item 
> (type)"
> 'SQLcommand.ExecuteNonQuery()
>
> 'SQLcommand.CommandText = "INSERT INTO Item (type) VALUES 
> ('something')"
> 'SQLcommand.ExecuteNonQuery()
>
> 'SQLcommand.Dispose()
> SQLconnect.Close()
>
> 6. Launched app: "Unable to load DLL 'SQLite.Interop.dll': The 
> specified module could not be found. (Exception from HRESULT:
> 0x8007007E)"
>
> What is the right way to install SQLite.Net so that applications can 
> find it?
>
> Thank you.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 

Markus Diersbock
SwingNote
Marblehead, MA

C 781.929.0693
F 888.654.6068

This e-mail is intended solely for the person or entity to which it is 
addressed and may contain confidential information. If you have received this 
e-mail in error, please contact the sender immediately and delete the material 
from any computer. Any review, dissemination, copying, printing or other use of 
this e-mail by persons or entities other than the addressee is prohibited.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?

[Marc L. Allen]

It has around 500 context switches per second.. so I'm thinking MosYield.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of markus diersbock
Sent: Thursday, April 18, 2013 1:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] [SQLite.Net] Right way to install precompiled binaries?

Put SQLite.Interop.dll in the same folder as your VB assembly (ie /debug or 
/release)


On Thu, Apr 18, 2013 at 9:22 AM, Gilles Ganault  wrote:
> Hello,
>
> I'm having a problem getting VB Express to find SQLite .Net:
>
> 1. I went to...
> http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
> ... to download sqlite-netFx45-binary-Win32-2012-1.0.84.0.zip
>
> 2. Unzipped file in C:\Downloads\SQLite\.Net\1.0.84.0\
>
> 3. Launched VB Express 2010 to create new Windows Form project
>
> 4. Project > Add Reference: Navigated to 
> "C:\Downloads\SQLite\.Net\1.0.84.0\" and pointed to 
> System.Data.SQLite.dll
>
> 5. Wrote SQLite test code:
>
> Dim SQLconnect As New SQLite.SQLiteConnection()
> Dim SQLcommand As SQLiteCommand
>
> 'Note: Non-admin app cannot write to c:\
> SQLconnect.ConnectionString = "Data 
> Source=c:\users\fred\test.sqlite;"
> SQLconnect.Open()
>
> 'SQLcommand = SQLconnect.CreateCommand
>
> 'SQLcommand.CommandText = "CREATE TABLE IF NOT EXISTS Item 
> (type)"
> 'SQLcommand.ExecuteNonQuery()
>
> 'SQLcommand.CommandText = "INSERT INTO Item (type) VALUES 
> ('something')"
> 'SQLcommand.ExecuteNonQuery()
>
> 'SQLcommand.Dispose()
> SQLconnect.Close()
>
> 6. Launched app: "Unable to load DLL 'SQLite.Interop.dll': The 
> specified module could not be found. (Exception from HRESULT:
> 0x8007007E)"
>
> What is the right way to install SQLite.Net so that applications can 
> find it?
>
> Thank you.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



-- 

Markus Diersbock
SwingNote
Marblehead, MA

C 781.929.0693
F 888.654.6068

This e-mail is intended solely for the person or entity to which it is 
addressed and may contain confidential information. If you have received this 
e-mail in error, please contact the sender immediately and delete the material 
from any computer. Any review, dissemination, copying, printing or other use of 
this e-mail by persons or entities other than the addressee is prohibited.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join inconsistency]

[Marc L. Allen]

If I have any doubt, I add .5 (or .05, .005, whatever) before the operation.  I 
know that breaks algebraic rounding, but that's one I live with.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of James K. Lowden
Sent: Friday, March 08, 2013 2:45 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite strong-typing [WAS: inner vs. outer join 
inconsistency]

On Thu, 7 Mar 2013 18:45:23 +
Simon Slavin  wrote:

> what do you think the desired behaviour would be for
> 
> CAST('0.9' AS INTEGER)
> 
> I know what I want.  Perhaps this can be fixed in SQLite4.  

Sorry, but CAST is not a math function.  There's probably a language somewhere 
out there that rounds floating point when assigned to integer, but every 
language I've ever used that supported those types truncated the fractional 
portion.  It's also what the SQL standard specifies.  

Consider, do you want 

CAST(0.5 as integer)

to be 1?  What about 0.49?  What about 0.499?  

These issues are why round() exists.  

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug on real operations

[Marc L. Allen]

Yes.. for what it's worth, I've had this very same problem on MS SQL 2008.  
Comparing floating point values in their raw form is always dangerous.  It just 
works so much more often than not that it's easy to forget until you get that 
one number that doesn't work.

The solution for MS SQL was conversion to smallmoney.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Friday, March 08, 2013 1:37 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug on real operations


On 8 Mar 2013, at 6:24pm, Israel Lins Albuquerque  
wrote:

> I don't know how postgres handle this, may be I can check

Postgres has special datatypes used especially to handle problems like this.  
It has both artbitrary precision and monetary datatypes.  If you present your 
problem to postgres and let it use floating point numbers it has the same 
problem SQLite has.

However, I used to work with banks and other financial institutions for a 
living and I can assure you that the systems I wrote and used used integer 
datatypes to handle amounts of money.  For historical reasons they do sometimes 
multiply by 10,000 instead of 100, but either way all amounts of money are 
stored as integers.  This speeds up calculations, reduces storage space, and 
reduces the complexity of testing required.

Just to underline what Richard wrote, this is not a bug in the way SQLite 
handles floating point.  The bug is in thinking you can express decimal 
fractions as binary floating point numbers and it is very familiar to computer 
scientists.

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


This email and any attachments are only for use by the intended recipient(s) 
and may contain legally privileged, confidential, proprietary or otherwise 
private information. Any unauthorized use, reproduction, dissemination, 
distribution or other disclosure of the contents of this e-mail or its 
attachments is strictly prohibited. If you have received this email in error, 
please notify the sender immediately and delete the original.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory DB - Query does not return all records after Update

[Marc L. Allen]

Are you finalizing the UPDATE statement?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of mike.akers
Sent: Monday, February 25, 2013 4:48 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Memory DB - Query does not return all records after Update

I am using an in-memory database with a singleton containing a single 
connection. I have multiple threads (3) using this same connection, but I have 
ensured that only 1 thread uses the connection at a time.

If I do an UPDATE on a record then immediately preform a SELECT, the query will 
return all rows that match the WHERE clause except for the record that was 
being updated. The sqlite3_prepare_v2 and sqlite3_step both return SQLITE_OK 
and do not return a SQLITE_LOCKED or SQLITE_BUSY so I would assume that my 
write was completed.

I would like to be able to either
1. read the old data (as the quieres happen frequently a couple old data 
queries are fine) I have tried read_uncommited with no noticable difference.
or 2. Have the database return LOCKED or BUSY if the write has not completed so 
that the query will return all of the results.

Example, If I insert 10 records and loop a query I receive 10, 10, 10, 10,  
results But, if I change one of the records during the loop I get 10, 10, 
update record, 9, 10, 

Any advice on what I am missing?

Thanks in advance,
Mike



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Memory-DB-Query-does-not-return-all-records-after-Update-tp67267.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Would anyone know how to use Sqlite to calculate the streakedness of data? The definition of streakedness is show below. Thank you for your help

[Marc L. Allen]

I haven't done SQLite coding in several months, and it's quite rusty, so I'll 
paraphrase.  I haven't tested and if this is bogus, I'm sorry in advance.  But 
maybe it'll give someone the right idea.

You might be better off with a custom function, though.

It would be something like this:

CREATE TABLE D
(
Pos INTEGER PRIMARY KEY AUTOINCREMENT,
Value INTEGER,
Streak INTEGER
)

(insert data into table D, with Streak set to 0.)
Insert invalid data point at the end (e.g. -1)

Update Streak such that it's equal to 1 if it is higher or equal to the value 
at POS - 1.
(This should result in the first POS, streak staying 0 and the final invalid 
point also having streak = 0)

UPDATE D SET Streak = 1
WHERE Pos IN (SELECT d2.POS FROM D d1 INNER JOIN D d2 WHERE d1.POS = d2.POS - 1 
AND d2.value >= d1.value)

Now, D should contain zero's for the beginning positions for each streak.  
Simply examine the distance between consecutive zeros. 

CREATE TABLE Streak
(
First INTEGER,
Last INTEGER,
Length INTEGER
)

INSERT INTO Streak
SELECT d1.pos, d2.pos, d2.pos - d1.pos 
FROM D d1
INNER JOIN D d2 ON d2.pos > d1.pos
WHERE d1.value = 0 and d2.value = 0 and 
not exists (SELECT d3.pos FROM D d3 where d3.value = 0 and 
d3.pos > d1.pos and d3.pos < d2.pos)

That should give you a list of streaks, including streaks of 1.  Aggregate any 
way you want.  

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Frank Chang
Sent: Tuesday, February 19, 2013 8:38 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Would anyone know how to use Sqlite to calculate the 
streakedness of data? The definition of streakedness is show below. Thank you 
for your help

Would anyone know how to use Sqlite to calculate the streakedness of data?
The definition of streakedness is show below. Thank you for your help.

[EDIT] From our company's chief software architect, here is the requirement for 
a statistical measure. Could someone please define a statistical formula based 
onour architect's definition of data streakedness? -- February 19th 2013 8:45 AM

Equal numbers are a streak. 1,2,3,3,3,4,5 has a streak of 7.

Case A: 1,2,3,4,5,6,7,8,9,10,11,12,13 has a longest streak of 13.

Case B: 1,2,3,4,5,6,7,3,8,9,10,11,12 has a longest streak of 7, a second 
smaller streak of 6.

Case C: 1,2,3,4,5,6,7,1,2,3,4,5,6 has a longest streak of 7, and a second 
smaller streak of 6.

Case D: 1,2,3,4,5,6,7,1,2,3,1,2,1 has a longest streak of 7, a second smaller 
streak of 3, and a third smallest streak of 2

Case E: 1,2,3,4,5,6,7,6,5,4,1,2,3 has a longest streak of 7, and a second 
smaller streak of 3.

Case F: 1,2,3,4,5,6,7,6,5,4,3,2,1 has a longest streak of 7, and no smaller 
streaks.

The cases A - F are ordered in 'most sorted to least sorted', but all have the 
same length longest streak. Using the averages of streak length is not
appropriate:

A: Average = 13/1 = 13

B: Average = (7+6)/2 = 6.5

C: Average = (7+6)/2 = 6.5

D: Average = (7+3+2)/3 = 4

E: Average = (7+3)/2 = 5

F: Average = 7/1 = 7

Factoring in non-streaks (counting them as 1's):

A: Average = 13/1 = 13

B: Average = (7+6)/3 = 4.3

C: Average = (7+6)/2 = 6.5

D: Average = (7+3+2+1)/4 = 3.25

E: Average = (7+1+1+1+3)/5 = 2.6

F: Average = (7+1+1+1+1+1+1)/7 = 1.85
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite 4

[Marc L. Allen]

Thanks.  Any clue on whether we'll use an amalgamation as with SQLite4 or 
direct sources?

Marc

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, February 12, 2013 11:32 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite 4

On Tue, Feb 12, 2013 at 11:26 AM, Marc L. Allen <mlal...@outsitenetworks.com
> wrote:

> I'm sorry if this isn't the right group, but an earlier message 
> mentioned it, and I found some stuff on the SQLite website.
>
> Although I've had a long-standing project to incorporate SQLite into 
> our product (and have done so), it has never been deployed.  I like 
> the looks of SQLite 4, but have not been able to find any proposed release 
> schedules.
>
> Can someone point me to an appropriate page or let me know if an 
> official release is planned anytime soon?  Our platform is 
> proprietary, so I'll need to be incorporating source, not binaries.
>

There is no planned release schedule for SQLite4 at this time.  It seems to be 
working pretty well, but it is not nearly as well-tested as is SQLite3.
And there are more interface changes that we want to make before it goes into 
any kind of official release.
--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite 4

[Marc L. Allen]

I'm sorry if this isn't the right group, but an earlier message mentioned it, 
and I found some stuff on the SQLite website.

Although I've had a long-standing project to incorporate SQLite into our 
product (and have done so), it has never been deployed.  I like the looks of 
SQLite 4, but have not been able to find any proposed release schedules.

Can someone point me to an appropriate page or let me know if an official 
release is planned anytime soon?  Our platform is proprietary, so I'll need to 
be incorporating source, not binaries.

Thanks,

Marc

--
**
* *  *
* Marc L. Allen   *  "... so many things are *
* *  possible just as long as you*
* Outsite Networks, Inc.  *  don't know they're impossible." *
* (757) 853-3000 #215 *  *
* *  *
* mlal...@outsitenetworks.com<mailto:mlal...@outsitenetworks.com> *   -- 
The Phantom Tollbooth   *
* *  *
**

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

Re: [sqlite] SQL query

[Marc L. Allen]

Actually... with that requirement, I wonder if it's even easier/better to use:

Select name, min(setid), hash
>From rtable 
Group by name, hash
Having min(setid) > 0

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Paul Sanderson
Sent: Thursday, January 31, 2013 5:08 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL query

Cool that seems to work - thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL query

[Marc L. Allen]

Add a group by name, hash and change the select to be name, min(setid), hash?

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Paul Sanderson
Sent: Thursday, January 31, 2013 4:48 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL query

Still playing with this

I have the following table and I run the following query - the results of which 
are what I expect

name, num, md5

sqlite> select * from rtable;
$RmMetadata|0|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
$RmMetadata|1|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
$RmMetadata|2|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279
$RmMetadata|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
$RmMetadata|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62
$RmMetadata|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62
$RmMetadata|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62
$RmMetadata|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
$RmMetadata|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29

sqlite> select * from rtable where num > 0 and md5 not in (select md5 
sqlite> from
rtable where num = 0);
$RmMetadata|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
$RmMetadata|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62
$RmMetadata|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62
$RmMetadata|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62
$RmMetadata|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29
$RmMetadata|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29

What I want to do is return only one unique row for each MD5 - so the return 
set would be, this bit is failing me

$RmMetadata|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5
$RmMetadata|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62
$RmMetadata|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29

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

Re: [sqlite] Running on windows 98

[Marc L. Allen]

Might I suggest you include his patch so it at least runs?  That way, if he's 
willing to test each new version, he doesn't need to modify the official source 
to do it.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Thursday, January 24, 2013 1:20 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Running on windows 98

On Wed, Jan 9, 2013 at 2:36 PM, Jose F. Gimenez wrote:

> Hi all,
>
> I've recently noticed that sqlite doesn't work on windows 98 since 
> version 3.7.12, due to the change to use OVERLAPPED when read and 
> write files. I've made a small modification to take care of it, and 
> avoid to use that way on win 98 (by using the function isNT() to check 
> that condition). I've attached a patch in this message.
>
> I've tested it and it seems to work fine (tested on win98, xp and 
> win7), but I'd like to know if it's correct or if there is any problem 
> which I can't see.
>

We have no way of testing SQLite on Win9x and so we do not intend to support 
Win9x moving forward.  Some older versions of SQLite are known to work on 
Win9x.  If you are still supporting Win9x applications, I suggest you use those 
older versions of SQLite.


>
> TIA,
>
> Jose F. Gimenez
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>


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

Re: [sqlite] SQL query

[Marc L. Allen]

If you simply want a list of all files that are present  and are not also 
present in set 0 (I'm not sure how 'duplicated' means anything different...)

SELECT f.name, f.set, f.hash
FROM files f
LEFT OUTER JOIN files f2 ON f2.name = f.name and f2.set = 0
WHERE f.set != 0 and f2.name is null


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Paul Sanderson
Sent: Wednesday, January 23, 2013 12:18 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQL query

I have a database with many million rows with in it each representing a file. 
There are many duplicate files in the database and all files are hashed.

The files are sub categorised into a number of sets, numbered 0 to 10 for 
example. Files do not need to be in every set.

I need to select all files that are in any set other than 0 that are not 
duplicated/present in set 0

So a sample database might contain columns


name set hash

with sample data

file10ABCD
file11ABCD
file13EF01
file20BCE2
file22BCE2
file35EE34
file40EE22

My query would return

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

Re: [sqlite] order by "a huge number" does not work, but "random" well why ?

[Marc L. Allen]

If you literally used "ORDER BY 1234567892" then there's nothing in the record 
being sorted.

I can't recall is SQLite allows order by aliases, but something like..

Select ..., random() as X
Order by X

Might work, as long as random() is executed for each row.

(Sorry.. don't have a quick SQLite engine available to test)

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of François-xavier Jacobs
Sent: Monday, January 14, 2013 9:48 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] order by "a huge number" does not work, but "random" well why 
?

Hi everyone

i would like to "seed random" a request, so i could do use some pagination 
system with a order by random(), is this possible sqlite ? when a tried to use 
"order by 1234567892" it always return the same order 
___

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

Re: [sqlite] PERSIST Journal Mode

[Marc L. Allen]

I believe only WAL mode is sticky. 

Not using the pragma seems to generate deletions as normal. 

The missing data was from after the file was closed. I am still looking into 
rollback, but why would journal mode matter?

Marc

On Dec 18, 2012, at 12:02 PM, "Simon Slavin" <slav...@bigfraud.org> wrote:

> 
> On 18 Dec 2012, at 3:04pm, Marc L. Allen <mlal...@outsitenetworks.com> wrote:
> 
>> I also have no additional information as to why having PERSIST mode on 
>> prevents the database from being updated/correct.  I did check the 
>> sqlite3_close command, and I'm passing it the connection received from 
>> sqlite3_open.  It returns SQLITE_OK.
> 
> Well, it seems you're doing it right.  It's possible that your operating 
> system doesn't show updates until the file is closed.
> 
> The other thing is that journal mode is meant to be sticky with each database 
> file.  It may be that when you issue 'PRAGMA journal_mode', even though 
> you're setting the same mode that's already active, SQLite reinitialises the 
> journal.  Now you have the correct journal mode set for your database files 
> you shouldn't need to use that PRAGMA again.  Does that fix things ?
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users