Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Joe Mucchiello]

 I just want to point something out that might help the original poster.

On Saturday, March 7, 2020, 7:00:21 AM EST, 
sqlite-users-requ...@mailinglists.sqlite.org 
 wrote:  > 
> 1.  NULL is NULL = Yes, True,
> 2.  NULL is FALSE = Nope, False.
> 3.  NULL is TRUE  = Nope, False.
> 4.  NULL is NOT NULL = Nope, False,
> 5.  NULL is NOT FALSE = Yep, True.
> 6.  NULL is NOT TRUE = Yep, True.
> 7. TRUE is FALSE  = Nope, False.
> 8. TRUE is NOT FALSE = Yep, True.
> 9. FALSE is NOT TRUE = Yep, True.
This explanation 100% correct and probably 80% confusing without the following, 
especially because "is" is not capitalized:IS and IS NOT are logical operators 
in SQL. NOT is not a unary operator when preceded by IS.

In most non-relational languages "NULL IS NOT TRUE" is parsed as:

value(NULL) operator(IS) (operator(NOT) value(TRUE)). 

That is NOT how SQL works. In SQL, it is:

value(NULL) operator(IS NOT) value(TRUE).


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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[R.Smith]

On 2020/03/07 03:52, Xinyue Chen wrote:

Hi,

If I change IS NOT FALSE to IS TRUE, the results will be different. I
assume they should perform in the same way?
if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should
also be always true. Then why doesn't that query also return 4 rows?


Some excellent answers were already given, but in case you still wonder...

In Boolean logic, a value can only be TRUE or FALSE. But in SQL (which 
has to model the real World Algebraically) there is also the possibility 
that the state is simply NOT KNOWN (or indeed that a 
variable/placeholder/identifier can represent no value at all).


The expression (Null = Null) is always NULL - it's like asking if an 
unknown person is exactly the same person as another unknown person? The 
answer is obviously "We don't know. It might be, so we cannot say 
definitively it ISN'T the case, but it might also NOT be the same 
person, so the only correct answer is: We don't know".
Further, "We don't know" in logic terms is undefined, which in SQL we 
write as "NULL".


While (Null = Null) in mathematical terms is always unknown, we can 
however test if two values are of the same kind with "is", and more 
specifically, test if they are both unknown, so the expression (NULL is 
NULL) correctly returns True.


This whole "Three possible states" thing is no longer simply Boolean 
logic, but indeed Trivalent logic with the possible values being 
NULL/TRUE/FALSE.


Writing the matrix of states of (x IS [NOT] y) down and numbering them 
we get 9 symantically distinct evaluations (there are more, like "FALSE 
is TRUE", but they can be rearranged as one of these):


1.  NULL is NULL = Yes, True,
2.  NULL is FALSE = Nope, False.
3.  NULL is TRUE  = Nope, False.
4.  NULL is NOT NULL = Nope, False,
5.  NULL is NOT FALSE = Yep, True.
6.  NULL is NOT TRUE = Yep, True.
7. TRUE is FALSE  = Nope, False.
8. TRUE is NOT FALSE = Yep, True.
9. FALSE is NOT TRUE = Yep, True.

Thus when you ask:
"I assume they should perform in the same way?
if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' 
should also be always true. Then why doesn't that query also return 4 rows?"


You assume that state 5 (NULL IS NOT FALSE) and state 3 (NULL IS TRUE) 
would mean the same thing, but as you can see from the list, in 
Trivalent logic it clealy doesn't - one is False and the other is True.



The stuff of nightmares to a purist, I know. In the real World though, 
some stuff simply isn't known and therefore cannot fit into the simple 
Boolean logic of TRUE and FALSE.


Best of luck!
Ryan


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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Richard Damon]

On 3/6/20 9:54 PM, Keith Medcalf wrote:

On Friday, 6 March, 2020 19:25, Richard Damon  wrote:


It is sort of like NaN, where a Nan is neither less than, greater than
or equal to any value, including itself.

NULL (as in SQL NULL) means "missing value" or "unknown".  NULL represents any value 
within the domain, we simply do not know what that value is.  That is, the value "NULL" for colour 
of a car means that we do not know the colour -- however, it still has one.

NaN, on the other hand, means that the value is outside the domain and that 
there is no possible value of the domain which well render the proposition true.

For example, the state of Schroedingers Cat is NULL.  It has a state, either 
dead or alive.  That state is merely unknown until one looks in the box.  
However, if when you looked in the box there was no cat, then the cat would be 
a NaN since its state was outside of the domain of states for a cat in a box 
with a time release poison after the release of the poison, that is, the 
non-existance of a cat in the box precludes the possibility of the state of the 
cat in the box being either either dead or alive.


It may have a different meaning, but similar effects on logic.

As an aside, for the quantum effect Schrodinger's Cat is designed to 
demonstrate, the cat ISN'T just one of dead or alive but not know which, 
but exists as a probability wave between the two states. This is why the 
photon which goes through one of two slits generates an interference 
pattern unless you detect which slit it goes through, if you measure the 
slit it went through, you get a different pattern of light, as the lack 
of knowledge allows it to be less precise in its position and the 
probability of going through the left slit interferes with the 
probability of that same particle going through the right slit, so the 
pattern implies it sort of went through both at once.


Detecting the state of Schrodinger's Cat actually changes its state, 
collapsing the wave into one of the definitive states.


--
Richard Damon

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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Keith Medcalf]

On Friday, 6 March, 2020 19:25, Richard Damon  wrote:

>It is sort of like NaN, where a Nan is neither less than, greater than
>or equal to any value, including itself.

NULL (as in SQL NULL) means "missing value" or "unknown".  NULL represents any 
value within the domain, we simply do not know what that value is.  That is, 
the value "NULL" for colour of a car means that we do not know the colour -- 
however, it still has one.

NaN, on the other hand, means that the value is outside the domain and that 
there is no possible value of the domain which well render the proposition true.

For example, the state of Schroedingers Cat is NULL.  It has a state, either 
dead or alive.  That state is merely unknown until one looks in the box.  
However, if when you looked in the box there was no cat, then the cat would be 
a NaN since its state was outside of the domain of states for a cat in a box 
with a time release poison after the release of the poison, that is, the 
non-existance of a cat in the box precludes the possibility of the state of the 
cat in the box being either either dead or alive.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Richard Damon]

On 3/6/20 9:00 PM, Simon Slavin wrote:

On 7 Mar 2020, at 1:52am, Xinyue Chen  wrote:


If I change IS NOT FALSE to IS TRUE, the results will be different. I
assume they should perform in the same way?

No.  Because NULL is not TRUE and is not FALSE.

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT NULL IS TRUE;
0
sqlite> SELECT NULL IS FALSE;
0
sqlite> SELECT NULL IS NOT TRUE;
1
sqlite> SELECT NULL IS NOT FALSE;
1

Once you can have NULL values, you have to know the rules very well when you 
apply logic.  Other values make sense, but NULL is not logical.
It is sort of like NaN, where a Nan is neither less than, greater than 
or equal to any value, including itself.


--
Richard Damon

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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Igor Tandetnik]

On 3/6/2020 8:52 PM, Xinyue Chen wrote:

If I change IS NOT FALSE to IS TRUE, the results will be different.


NULL IS TRUE is always false. NULL IS NOT FALSE is always true. So it's not 
surprising that the results are different.

SQL uses trivalent logic. NULL is neither FALSE nor TRUE.


I assume they should perform in the same way?


You assume incorrectly.


if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should
also be always true.


False.
--
Igor Tandetnik

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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Simon Slavin]

On 7 Mar 2020, at 1:52am, Xinyue Chen  wrote:

> If I change IS NOT FALSE to IS TRUE, the results will be different. I
> assume they should perform in the same way?

No.  Because NULL is not TRUE and is not FALSE.

SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> SELECT NULL IS TRUE;
0
sqlite> SELECT NULL IS FALSE;
0
sqlite> SELECT NULL IS NOT TRUE;
1
sqlite> SELECT NULL IS NOT FALSE;
1

Once you can have NULL values, you have to know the rules very well when you 
apply logic.  Other values make sense, but NULL is not logical.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Peter da Silva]

> If I change IS NOT FALSE to IS TRUE, the results will be different. I
> assume they should perform in the same way?
> if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should
> also be always true.

"NULL IS NOT FALSE" is true because NULL is not a value therefor it is not 
FALSE, because FALSE is a value.
"NULL IS TRUE" is false because NULL is not a value so it's not TRUE.

You can't compare NULL with anything. All you can do is tell if it "IS NULL" or 
"IS NOT NULL".

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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Xinyue Chen]

Hi,

If I change IS NOT FALSE to IS TRUE, the results will be different. I
assume they should perform in the same way?
if you say "NULL IS NOT FALSE" is always true, then 'NULL IS TRUE' should
also be always true. Then why doesn't that query also return 4 rows?
Thanks!

On Fri, Mar 6, 2020 at 5:45 PM Peter da Silva  wrote:

> Change the "(t1.textid = null)" to "(t1.textid IS NULL)". Null has no
> value, you have to check for it explicitly.
>
> ___
> 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] Bug in SQLite version 3.31.1 and 3.32?

[Keith Medcalf]

On Friday, 6 March, 2020 17:48 Xinyue Chen  wrote:

...

>select t1.textid a, i.intid b
>  from t t1,
>   i i
> where (t1.textid = i.intid and t1.textid in (12) and t1.textid = i.intid) 
>or (t1.textid = null IS NOT FALSE)
>group by i.intid, t1.textid;

I got rid of all the extra brackets to make this easier to read.

The where clause wants either (a bunch of and joined conditions) to be true OR 
(another condition to be true).  We will ignore the first set of AND joined 
conditions since they appear to do what you want and instead deal with the 
handling of NULL values and tri-state logic from the second OR joined 
condition, which appears to be the one causing problems.

The expression (t1.textid = null) is always null (it is neither True nor 
False), no matter what the value of t1.textid because any value compared to 
NULL is NULL.  If you want to know whether t1.textid is null then you write 
"t1.textid is null" or (conversely) "t1.textid is not null"

NULL is FALSE -> False (NULL is not False)
NULL is TRUE  -> False (NULL is not True either)
NULL is not FALSE -> True  (it is True that NULL is not FALSE)
NULL is not TRUE  -> True  (it is True that NULL is not TRUE)

Since the condition that you have specified (t1.textid = NULL) IS NOT FALSE 
will always be true, then the logic value of condition on the "other side" of 
the OR is irrelevant -- the WHERE clause will always be TRUE.  This condition 
holds for any not null value you use in place of TRUE or FALSE.  That is:

NULL is 1 -> False (NULL is not 1)
NULL is 0 -> False (NULL is not 0 either)
NULL is not 1 -> True  (NULL is indeed not 1)
NULL is not 2 -> True  (NULL is indeed not 2)

This result will be the same if you change the IS NOT FALSE to IS NOT TRUE.  
However, if you specify IS TRUE or IS FALSE then this expression will always be 
FALSE and the value of the WHERE clause will depend on the result of the first 
set of AND joined conditions.

So your original query must and always devolves to:

select t1.textid a, i.intid b
from t t1,
 i i
group by i.intid, t1.textid;

for which the correct results are produced.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Peter da Silva]

Change the "(t1.textid = null)" to "(t1.textid IS NULL)". Null has no value, 
you have to check for it explicitly.

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

Re: [sqlite] Bug in SQLite version 3.31.1 and 3.32?

[Richard Hipp]

On 3/6/20, Xinyue Chen  wrote:
> -- Buggy query
> select t1.textid a, i.intid b
> from t t1,
>  i i
> where (((t1.textid = i.intid) and (t1.textid in (12)) and (t1.textid =
> i.intid)) or ((t1.textid = null) IS NOT FALSE))
> group by i.intid, t1.textid;


(1) The expression "t1.textid=null" is always NULL.
(2) The expression "NULL IS NOT FALSE" is always true.
(3) The WHERE clause expression "... OR true" is always true.

Hence, the query above simplifies to just "SELECT * FROM t, i;".  That
query should return 4 rows, just as you show.  I think it is working
correctly.

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

Re: [sqlite] Bug in sqlite: "View with UNION ALL and limit in subquery" (v 3.28.0)

[Richard Hipp]

Thank you for the report.  The problem is now fixed on trunk.

Ticket: https://www.sqlite.org/src/info/c41afac34f15781f
Fix: https://www.sqlite.org/src/info/523b42371122d9e1

On 5/29/19, Marco Foit  wrote:
> Dear SQLite Developers,
>
> I just noticed the following bug in SQLite version 3.28.0:
>
> 
>
> create table t AS values (1), (2);
>
> .print "select with correct output  ..."
> select * from ( select * from t limit 1 )
> union all
> select * from t
> ;
>
> .print "same select leads to incorrect result when used inside view ..."
> create view v as
> select * from ( select * from t limit 1 )
> union all
> select * from t
> ;
>
> select * from v;
>
> 
>
>
> * How to reproduce:
> Run the attached SQL code from a shell with:
>
>   sqlite3 < sqlite-bug.sql
>
>
> * Expected result:
> Both queries should yield the following output:
> 1
> 1
> 2
>
> * What did go wrong:
> The second query yields to the output:
> 1
>
>
> It seems that the limit clause in the compound select when used inside a
> view is used for the overall result set and not for the subquery.
>
>
> In the hope that this might help others.
> Thank you very much for your hard work!
>
>
> Cheers,
> Marco
>
>
> ___
> 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

Re: [sqlite] bug in sqlite when

[Richard Hipp]

On 8/24/16, Sergey Shamshyn  wrote:
> - *PRAGMA journal_mode=OFF* (when not execute this pragma, all is ok).

With journal_mode=OFF, the database may go corrupt if:

(1) The program is ever terminated (ex: using SIGKILL) while the
database is open.
(2) If a COMMIT statement ever fails due to an I/O or out-of-memory error.
(3) Failure of a constraint during an UPDATE (depending on the schema).
(4) Calling sqlite3_close() while a transaction is active.

In other words, without a journal, there are many ways to corrupt the
database file that do not involve power failures or I/O errors.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug in sqlite when

[Igor Korot]

Hi, Sergey,

On Wed, Aug 24, 2016 at 8:55 AM, Sergey Shamshyn
 wrote:
> Hi.
> I have a big trouble using SQLite: got an error SQLITE_CORRUPT (11), I think
> this is a 100% SQLite bug, because:
> - only ONE THREAD of my process is writing periodically to db file
> - onlt ONE ANOTHER THREAD of my another process reads from this db file
> - version 3.14.1
> - PRAGMA synchronous=OFF
> - *PRAGMA journal_mode=OFF* (when not execute this pragma, all is ok).
> Looks like bug in PRAGMA journal_mode=OFF, because *only one thread is
> writing to DB*!
> - No power crashes, or chkdsk errors on this HDD.
> - In attachment example of corrupted DB

This list does not accept attachments.
Can you put the DB somewhere on the file sharing service and put the link here?

Thank you.

>
>
>
> ___
> 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] Bug in SQLite FLOAT values

[Keith Medcalf]

A double precision floating point value contains about 14.5 to 16 digits of 
precision WHEN CONVERTED FROM BINARY TO DECIMAL TEXT.  However, the double 
precision number is merely the closest binary approximation of a value as can 
be encoded in BINARY (base 2) format.  Exact DEMAL (base 10) values cannot be 
represented in binary (base 2) floating point.  When you "round off" a floating 
point value to contain, as you put it, merely 6 digits of precision, what you 
are doing is throwing away some approximation data and making the value a less 
precise approximation of your diddled value -- you make it impossible to access 
the original value or to repeat the rerounding and achieve the same result.  
You are converting a value which is the best approximation of the value to a 
worse approximation of a value.  That you only want to see 6 digits of 
displayed resolution is a display problem, not a value storage and retrieval 
problem.

You are doing the equivalent of taking a beautiful 4096x2048 32-bit CMYK image 
and compressing it to a 16 KByte JPEG of postage stamp size (which it does by 
throwing away information such that it becomes permanently inaccessible and 
permanently irretrievable).  When you then "blow up" the JPEG back to the 
original size you are greeted by pixelated eye-bleed causing crap that bears no 
resemblance whatsoever to the original data.  The same applies to non-lossless 
audio compression formats as well (nay, all digital audio formats, truth be 
told).  Compressing an analogue signal into a 128kbit mpeg stream (or any other 
non-lossless format) permanently destroys data content, making it impossible to 
retrieve the original data (and in the case of audio, leaving only crap that a 
tone deaf moron using "cheap tinkle" audio equipment could stand -- it makes 
the rest of us bleed out our ears).

So too is "diddling" with double precision floating point numbers causing 
irretrievable and irreperable damage to the data values contained in them.

You are confusing the "data value" (in this case the floating point value) with 
the display representation for biots (text strings in base-10 decimal 
representation).  Once converted, you are throwing away information which you 
will never be able to recover ever again.  Store and work with ONLY the double 
value that is provided to you without any tinkering.  If you BIOTs require 
displaying only six digits of precision, then display it to them that way.  Do 
not confuse BIOT display and BIOT input requirements with the internal 
representation of the data inside a digital computer system.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.

>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Abdul Aziz
>Sent: Saturday, 7 February, 2015 07:22
>To: General Discussion of SQLite Database
>Subject: Re: [sqlite] Bug in SQLite FLOAT values
>
>OK, understood, thanks but can anyone explain me I was creating db of
>different sensors, I used same methods, but in fields which were FLOAT
>were
>filling with junk values (after 6 decimal places, see in SENSOR_1) why?
>even I was cutting it to 6 decimal places (as shown previously, was then
>again converting into FLOAT before insertion), when I used VARCHAR(TEXT),
>in different table with same methods, then was getting correct
>(formatted,
>upto 6 decimal places, see in latitude, longitude) values in SENSOR_99,
>please have a look...
>
>Thank you very much :)
>
>On Sat, Feb 7, 2015 at 7:39 PM, Igor Tandetnik <i...@tandetnik.org>
>wrote:
>
>> On 2/7/2015 8:47 AM, Abdul Aziz wrote:
>>
>>> Thanks for replybut I am now using VARCHARS, then how this is
>working?
>>> not generating any errors?
>>>
>>
>> When Tim said "Read this", he meant it. http://www.sqlite.org/
>> datatype3.html answers your questions (but only if you read it).
>> --
>> Igor Tandetnik
>>
>>
>> ___
>> 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] Bug in SQLite FLOAT values

[Simon Slavin]

On 7 Feb 2015, at 2:22pm, Abdul Aziz  wrote:

> but in fields which were FLOAT were
> filling with junk values (after 6 decimal places, see in SENSOR_1) why?

Take a look at what happens when you try to write 1/13th in decimal:

0.076923076923076923076923076923076923076923076923076923.

What about 7/9ths:

0.77.

And PI:

3.1415926535897932384626443323279502. (I forget the rest)

There's no way to write those numbers precisely in decimal.  The first repeats 
after the sixth place.  The second repeats after the first place.  The third 
never repeats, but also never stops.  Similarly there's no way to write some 
numbers precisely in binary.  So if I tell you you had to write the number 
using just digits and a decimal point, you can't do it.  Similarly, if you 
convert some numbers into binary format and back (just 0 and 1 and a decimal 
point) you don't get back precisely the number you put in.

Fields defined as FLOAT (in your Android API) have their values stored in 
binary format.  So storing a number in that field involves converting it to 
binary.  If, instead, you define your fields as text no conversion takes place 
because there's no need to turn the number into binary format.

(Above explanation simplified with respect to affinities and deep maths for 
simplicity.)

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

Re: [sqlite] Bug in SQLite FLOAT values

[Abdul Aziz]

OK, understood, thanks but can anyone explain me I was creating db of
different sensors, I used same methods, but in fields which were FLOAT were
filling with junk values (after 6 decimal places, see in SENSOR_1) why?
even I was cutting it to 6 decimal places (as shown previously, was then
again converting into FLOAT before insertion), when I used VARCHAR(TEXT),
in different table with same methods, then was getting correct (formatted,
upto 6 decimal places, see in latitude, longitude) values in SENSOR_99,
please have a look...

Thank you very much :)

On Sat, Feb 7, 2015 at 7:39 PM, Igor Tandetnik  wrote:

> On 2/7/2015 8:47 AM, Abdul Aziz wrote:
>
>> Thanks for replybut I am now using VARCHARS, then how this is working?
>> not generating any errors?
>>
>
> When Tim said "Read this", he meant it. http://www.sqlite.org/
> datatype3.html answers your questions (but only if you read it).
> --
> Igor Tandetnik
>
>
> ___
> 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] Bug in SQLite FLOAT values

[Keith Medcalf]

Why would an application need to use the SQLite printf function to convert 
doubles to formatted text?  The application ought to store and retrieve the raw 
doubles completely unadulterated (with no diddling, using the value_double and 
bind_double interfaces), and "format the value for display" when it is 
displayed.

---
Theory is when you know everything but nothing works.  Practice is when 
everything works but no one knows why.  Sometimes theory and practice are 
combined:  nothing works and no one knows why.


>-Original Message-
>From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>boun...@sqlite.org] On Behalf Of Kees Nuyt
>Sent: Saturday, 7 February, 2015 18:14
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Bug in SQLite FLOAT values
>
>On Sat, 7 Feb 2015 11:27:59 +0530, Abdul Aziz <abduldblog...@gmail.com>
>wrote:
>
>> Hi there!
>> I hope you are well!
>>
>> Recently I was working on project based on Android Sensors and
>encountered
>> a bug in sqlite db, situation was this:
>> I was setting there three values x,y,z as FLOAT, android inbuilt
>sensors
>> were receiving values as float upto 8 decimal places, but I wanted to
>store
>> value only upto 6 decimal place, so in android this is the way that
>first
>> you will have to convert that value into String , as* String sLongitude
>=
>> String.format("%.6f", x);*
>
>As others have said, you shouldn't confuse the storage
>format (how a value is stored in the database) with
>the presentation (how data is displayed on output).
>
>Luckily, recently sqlite got a printf() function.
>
>Demo:
>
>$ sqlite3 test.db
>SQLite version 3.8.8 2015-01-30 20:59:27
>Enter ".help" for usage hints.
>sqlite> create table t3 (id INTEGER PRIMARY KEY, lat REAL, lon REAL);
>sqlite> insert into t3 (id,lat,lon) VALUES
>(1,1.234567890123,5.6789012345678);
>sqlite> select printf('id:%3d, latitude: %9.6f, longitude:
>%9.6f',id,lat,lon) from t3;
>id:  1, latitude:  1.234568, longitude:  5.678901
>sqlite>
>
>Hope this helps.
>
>--
>Regards,
>
>Kees Nuyt
>
>___
>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] Bug in SQLite FLOAT values

[Kees Nuyt]

On Sat, 7 Feb 2015 11:27:59 +0530, Abdul Aziz  wrote:

> Hi there!
> I hope you are well!
>
> Recently I was working on project based on Android Sensors and encountered
> a bug in sqlite db, situation was this:
> I was setting there three values x,y,z as FLOAT, android inbuilt sensors
> were receiving values as float upto 8 decimal places, but I wanted to store
> value only upto 6 decimal place, so in android this is the way that first
> you will have to convert that value into String , as* String sLongitude =
> String.format("%.6f", x);*

As others have said, you shouldn't confuse the storage 
format (how a value is stored in the database) with
the presentation (how data is displayed on output).

Luckily, recently sqlite got a printf() function.

Demo:

$ sqlite3 test.db
SQLite version 3.8.8 2015-01-30 20:59:27
Enter ".help" for usage hints.
sqlite> create table t3 (id INTEGER PRIMARY KEY, lat REAL, lon REAL);
sqlite> insert into t3 (id,lat,lon) VALUES (1,1.234567890123,5.6789012345678);
sqlite> select printf('id:%3d, latitude: %9.6f, longitude: %9.6f',id,lat,lon) 
from t3;
id:  1, latitude:  1.234568, longitude:  5.678901
sqlite>

Hope this helps.

-- 
Regards, 

Kees Nuyt

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

Re: [sqlite] Bug in SQLite FLOAT values

[Simon Slavin]

On 7 Feb 2015, at 5:57am, Abdul Aziz  wrote:

> *again I converted back to float this string formatted value, and printed
> into Log, I was clearly seeing values upto 6 decimal places , but after
> insertion into sqlite db, when after generation of sqlite db file, was
> getting values upto 11-12 decimal places!*

SQLite itself would have converted your text (to six digits) and stored the 
resulting number. When you asked to retrieve your value, SQLite would have 
retrieved that number -- still at six digits.

However if you ask for the retrieved figure as a number (as opposed to a 
string) the programming language you use has to put the resulting number into a 
'float' variable.  And in doing this it would have to turn the number back into 
float format, which would introduce the extra 'garbage' digits.  So yes, you 
can argue that there is a bug somewhere, but if there is one it's in the 
Android interface to SQLite, not in SQLite itself.

You can avoid this by asking for the retrieved figure as a string, not a 
number.  Or by storing the value as a TEXT column instead of a REAL column, 
which is the solution you came up with.  So I'm glad you found a solution.

It might be worth asking yourself why you are trimming your values to six 
digits and then saving the result as a number.  It would make more sense to 
trim your values and then handle the number as a string from then onwards.  Or 
to handle all the digits you have and to convert to text as six digits just 
before you put the number on the display.  Both of these would be more 
mathematically 'correct' than what you are doing.

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

Re: [sqlite] Bug in SQLite FLOAT values

[RSmith]

On 2015/02/07 15:47, Abdul Aziz wrote:

Thanks for replybut I am now using VARCHARS, then how this is working?
not generating any errors?

Please elaborate, my query to create DB is:
mSQLiteDatabase.execSQL("CREATE TABLE " + tableName
+ " ( "
+ EVENT_TIME + " INTEGER, " + SYSTEM_TIME + " INTEGER PRIMARY KEY, "
+ ACCURACY + " INTEGER," + X + " VARCHAR, " + Y + " VARCHAR, " + Z
+ " VARCHAR );");


It translates Varchar to Text, that's why no errors are generated, it understands what you mean by Varchar, which is really just 
some text, so it translates it to the internal type TEXT.


As for your question about decimal points and floats, no float in any language stores values up to a certain length... lengths are 
the domain of Strings and text, not Floating point numbers. any floating point number is an approximate number with a representation 
as close as is possible to the actual number. That representation includes many significant digits in the significand and an 
exponent. You may need to read up on floats some more to see how it works - my point is just that it doesn't  store numbers up to a 
certain length, for that you need a formatter.


Many DB engines offer formatted types, such as Decimal (in PG, Oracle, MSSQL, etc) where you can say you need the number with so 
many decimals after the point.  In SQLite you can format the output (much like your C solution) by doing  SELECT 
printf('%.6f',somevalue); etc.


Read the pages offered by the other posters and maye check out the Wikipedia pages on floating point storage and representation to 
understand WHY all the above happens, but to solve your immediate problem, use the output formatting or store as strings - there is 
no way to tell a true floating number to keep itself short.


Other interesting things you can see about this floating point problem (it's a mathematical problem too), look on youtube for "Why 
is 0.9... equal to 1?" or "How do we know two numbers are distinct?" - The Numberphile videos in general do a good job of 
explaining it.


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

Re: [sqlite] Bug in SQLite FLOAT values

[Igor Tandetnik]

On 2/7/2015 8:47 AM, Abdul Aziz wrote:

Thanks for replybut I am now using VARCHARS, then how this is working?
not generating any errors?


When Tim said "Read this", he meant it. 
http://www.sqlite.org/datatype3.html answers your questions (but only if 
you read it).

--
Igor Tandetnik

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

Re: [sqlite] Bug in SQLite FLOAT values

[Abdul Aziz]

Thanks for replybut I am now using VARCHARS, then how this is working?
not generating any errors?

Please elaborate, my query to create DB is:
mSQLiteDatabase.execSQL("CREATE TABLE " + tableName
+ " ( "
+ EVENT_TIME + " INTEGER, " + SYSTEM_TIME + " INTEGER PRIMARY KEY, "
+ ACCURACY + " INTEGER," + X + " VARCHAR, " + Y + " VARCHAR, " + Z
+ " VARCHAR );");

On Sat, Feb 7, 2015 at 7:08 PM, Tim Streater  wrote:

> On 07 Feb 2015 at 05:57, Abdul Aziz  wrote:
>
> > *I think this is a bug, this means float values in sqlite will always be
> > filled upto 11-12 decimal places, in any case, you will have to fill it,
> or
> > sqlite will fill it itself with junk values, **this may create lot of
> > consumption of memory while working on larger projects...*
>
> Read this:
>
>   http://www.sqlite.org/datatype3.html
>
> Note that:
>
> 1) There are no varchars in SQLite
>
> 2) Floats always occupy 8 bytes
>
> --
> Cheers  --  Tim
>
> ___
> 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] Bug in SQLite FLOAT values

[Tim Streater]

On 07 Feb 2015 at 05:57, Abdul Aziz  wrote: 

> *I think this is a bug, this means float values in sqlite will always be
> filled upto 11-12 decimal places, in any case, you will have to fill it, or
> sqlite will fill it itself with junk values, **this may create lot of
> consumption of memory while working on larger projects...*

Read this:

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

Note that:

1) There are no varchars in SQLite

2) Floats always occupy 8 bytes

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

Re: [sqlite] Bug 993556 - SQLite crash in walIndexTryHdr due to Windows EXCEPTION_IN_PAGE_ERROR exception

[Dan Kennedy]

On 10/18/2014 05:45 AM, Deon Brewis wrote:

I'm trying to follow Richard's advise to work around this issue, which is:

"Is that database ever used by more than a single process.  (Use by multiple
threads using separate connections does not count - I mean really used by
multiple processes with their own address space.)  If not (and I think the
answer is "no") then FF could set "PRAGMA locking_mode=EXCLUSIVE"
immediately after opening the database and before doing anything else.  If
that is done, then SQLite will use heap memory for the WAL-index, instead of
mmapped shared memory, and this problem will never come up."


However, I'm unable to do so. I'm using multiple threads using separate
connections, like mentioned, but when I try to use PRAGMA
locking_mode=EXCLUSIVE, the next thread that tries to open a connection will
block indefinitely on the open.

So how can I go about using PRAGMA locking_mode=EXCLUSIVE while still using
multiple threads with connections?


I think the quoted paragraph above assumes that the application is using 
shared-cache mode, which is probably not what you want to do.


If you're on unix, using the VFS "unix-excl" causes a similar effect. 
Second section here:


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

Dan.









--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Bug-993556-SQLite-crash-in-walIndexTryHdr-due-to-Windows-EXCEPTION-IN-PAGE-ERROR-exception-tp78695.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] Bug in sqlite? Can't read tables just after creating them

[Simon Slavin]

On 11 Mar 2014, at 11:31am, Fabrice Triboix  wrote:

> The problem is actually elsewhere. I changed the filesystem and it works 
> fine. 
> 
> So the problem is not with sqlite but with our special filesystem. 

Make sure your code tests the results returned by all API calls to see that 
they are returning SQLITE_OK where appropriate.  Many times, the call which you 
realise is returning the wrong thing is after the call which created the 
problem.

But I'm glad to see you have it working.

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

Re: [sqlite] Bug in sqlite? Can't read tables just after creating them

[Fabrice Triboix]

Hi Richard,

The problem is actually elsewhere. I changed the filesystem and it works fine. 

So the problem is not with sqlite but with our special filesystem. 

Sorry for having raised the alarm too quickly!

Best regards,

  Fabrice

-Original Message-
From: Richard Hipp <d...@sqlite.org>
Sender: sqlite-users-bounces@sqlite.orgDate: Tue, 11 Mar 2014 07:27:37 
To: General Discussion of SQLite Database<sqlite-users@sqlite.org>
Reply-To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Subject: Re: [sqlite] Bug in sqlite? Can't read tables just after creating
them

On Tue, Mar 11, 2014 at 6:35 AM, <ftrib...@falcon-one.com> wrote:

> Hi,
>
> I found a problem in sqlite.
>
> In essence, here is what my code does:
>  1 - It opens a database file
>  2 - If it doesn't find certain tables, it assumes this is a new one and
> creates the necessary tables and add a few entries in one of them (let's
> call it mytable)
>  3 - It queries mytable by doing a 'SELECT xyz, abc FROM mytable;'
>
> When I run this code on Debian, I don't have any problem.
>
> When I run it on my evaluation kit (ARM9), step 3 fails saying 'no such
> table: mytable'. I found out that closing and then re-opening the database
> file works. So I do steps 1 and 2, and close and re-open the database
> file, and now step 3 works fine!
>
> For the time being, I have this workaround of closing/re-opening the
> database file, but that's really a kludge.
>
> Any idea about from where this could come from?
>

No ideas. Please enable the error and warning log (
http://www.sqlite.org/errlog.html) and see if that provides any further
information.


>
> Many thanks for any ideas!
>
>   Fabrice
>
>
> ___
> 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] Bug in sqlite? Can't read tables just after creating them

[Richard Hipp]

On Tue, Mar 11, 2014 at 6:35 AM,  wrote:

> Hi,
>
> I found a problem in sqlite.
>
> In essence, here is what my code does:
>  1 - It opens a database file
>  2 - If it doesn't find certain tables, it assumes this is a new one and
> creates the necessary tables and add a few entries in one of them (let's
> call it mytable)
>  3 - It queries mytable by doing a 'SELECT xyz, abc FROM mytable;'
>
> When I run this code on Debian, I don't have any problem.
>
> When I run it on my evaluation kit (ARM9), step 3 fails saying 'no such
> table: mytable'. I found out that closing and then re-opening the database
> file works. So I do steps 1 and 2, and close and re-open the database
> file, and now step 3 works fine!
>
> For the time being, I have this workaround of closing/re-opening the
> database file, but that's really a kludge.
>
> Any idea about from where this could come from?
>

No ideas. Please enable the error and warning log (
http://www.sqlite.org/errlog.html) and see if that provides any further
information.


>
> Many thanks for any ideas!
>
>   Fabrice
>
>
> ___
> 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

Re: [sqlite] Bug in SQLite 3.8.2

[Richard Hipp]

Thanks for the bug report.

Ticket: http://www.sqlite.org/src/info/c34d0557f740c45070
Fixed here: http://www.sqlite.org/src/info/5d01426ddf


On Wed, Feb 12, 2014 at 5:31 AM, Paweł Salawa  wrote:

> Hi,
>
> The bug affects 3.8.2 and 3.8.3.1, I haven't tested other versions.
>
> *Preconditions:*
>
> - 2 databases: A and B.
>
> - database A has table "test":
> CREATE TABLE test (id integer PRIMARY KEY, val text) WITHOUT ROWID
>
> - database B has table "test2":
> CREATE TABLE test2 (EID INTEGER, node1 INTEGER, node2 INTEGER)
>
>
> *To reproduce bug:*
>
> - open database A and attach database B:
> ATTACH 'database_b.db' AS 'attached';
>
> - execute query:
> select test.*, t2.ROWID from attached.test2 t2, test
>
> SQLite says: *no such column: t2.ROWID*
>
> Weird thing is that when you switch test2 and test table positions with
> each other, the same query will work:
> select test.*, t2.ROWID from test, attached.test2 t2
>
> *^^^ this works just fine.*
>
> Problem occurres only if following conditions are met:
> - table in local database is WITHOUT ROWID
> - table in attached database is a regular table with ROWID
> - query selects ROWID from the regular table
> - both tables must be mentioned in the FROM clause
> - the WITHOUT ROWID table must be mentioned as the second one
>
> Regards,
> --
> Paweł Salawa
> pawelsal...@gmail.com
> ___
> 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

Re: [sqlite] Bug in SQLite 3.8.2

[Kees Nuyt]

On Wed, 12 Feb 2014 11:31:05 +0100, Pawe? Salawa
 wrote:

> - open database A and attach database B:
> ATTACH 'database_b.db' AS 'attached';

This is not the main cause, but that should be:

ATTACH 'database_b.db' AS attached;

(attached should not be a literal but an identifier, just like
table names and column names.)

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

Re: [sqlite] Bug in SQLITE regarding HAVING?

[Tobias Steinmann]

Hi Simon,
no its OK -- did not take anything personal. I think I stay with my 
SQL-statements...


Thanks again for your hint,
   Tobias
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLITE regarding HAVING?

[Simon Slavin]

On 25 Dec 2013, at 8:54am, Tobias Steinmann  wrote:

> thanks for your hint. I needed some time to figure out, how to use this 
> mailing-list and I hope, you will get my answer.
> 
> I tried the same im MySQL and it worked -- I know, that is no reason I just 
> wanted to say.
> 
> I also tried, to rename one of the depth's to depth1 -- and then it also 
> worked in SQLite so thanks for the hint.

No problem.  An easy mistake to make.  And since you got your answer, you are 
using this mailing list well.

> > Argh. An example where doing the work in your software rather
> > than a huge SQL statement might be more understandable,
> > both in your debugging and if anyone else ever has to read
> > your code. Another way to simplify things might be to
> > define your sub-select as a VIEW.
> 
> This comment of your I did not understand what you mean, maybe because of my 
> bad english? Sorry. If I got you right, you suggested not to use such big 
> SQL-Statements? I see that they are not ideally to debug but on the other 
> hand I also dont understand, why I should make thinks simpler just to have 
> afterwards some things to do in my program? I thought, the SQL-System might 
> be faster in data-filtering than my application...?

Sorry.  It was nothing personal.  I have made that comment many times to many 
people.

Your SELECT was extremely complicated because you had to phrase your 
requirements in SQL.  Sometimes doing the same work in your programming 
language leads to simpler code -- code which is easier to debug and sometimes 
even runs faster.  Because a programming language can do things differently.  
But sometimes it doesn't, and the SQL phrasing is as good as you can get.

Don't worry about it.  Just something that annoys me personally.

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

Re: [sqlite] Bug in SQLITE regarding HAVING?

[Tobias Steinmann]

Hi Simon,
thanks for your hint. I needed some time to figure out, how to use this 
mailing-list and I hope, you will get my answer.


I tried the same im MySQL and it worked -- I know, that is no reason I 
just wanted to say.


I also tried, to rename one of the depth's to depth1 -- and then it also 
worked in SQLite so thanks for the hint.


> Argh. An example where doing the work in your software rather
> than a huge SQL statement might be more understandable,
> both in your debugging and if anyone else ever has to read
> your code. Another way to simplify things might be to
> define your sub-select as a VIEW.

This comment of your I did not understand what you mean, maybe because 
of my bad english? Sorry. If I got you right, you suggested not to use 
such big SQL-Statements? I see that they are not ideally to debug but on 
the other hand I also dont understand, why I should make thinks simpler 
just to have afterwards some things to do in my program? I thought, the 
SQL-System might be faster in data-filtering than my application...?


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

Re: [sqlite] Bug in SQLITE regarding HAVING?

[James K. Lowden]

On Mon, 23 Dec 2013 23:50:30 +0100
"E.Pasma"  wrote:

> > .  See if you can make the simplest possible SELECT that comes up  
> > with unexpected results.
> 
> select 0 as depth
> from(select 1 as depth)
> group by null
> having depth < 1
> ;
> This returns no rows. Thus the HAVING clause refers to the depth
> from the FROM part, not that in the SELECT part. May this be what is  
> causing the unexpected result?

Another good use for strict = ON!  

Column name aliases in the SELECT clause are properly unavailable to the
rest of the query, including the GROUP BY clause. More simply, 

select 0 as depth
from  (select 1 as foo) as T
where depth < 1;

should be a syntax error but 

sqlite> select 0 as depth from (select 1 as foo) as T where depth < 1;
depth 
--
0

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

Re: [sqlite] Bug in SQLITE regarding HAVING?

[E.Pasma]

Op 23 dec 2013, om 14:32 heeft Simon Slavin het volgende geschreven:


General note: when making up a name for a calculation like 'depth',  
try to make sure it's not the name of any of the columns in the  
tables mentioned in your SELECT.  This avoids ambiguity.


.  See if you can make the simplest possible SELECT that comes up  
with unexpected results.


select 0 as depth
from(select 1 as depth)
group by null
having depth < 1
;
This returns no rows. Thus the HAVING clause refers to the depth from  
the FROM part, not that in the SELECT part. May this be what is  
causing the unexpected result?

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

Re: [sqlite] Bug in SQLITE regarding HAVING?

[Simon Slavin]

On 22 Dec 2013, at 11:12pm, Tobias Steinmann  wrote:

> SELECT node.id,node.lft, node.rgt, (COUNT(parent.id) - (sub_tree.depth + 1)) 
> AS depth FROM target_directory AS node, target_directory AS parent, 
> target_directory AS sub_parent, (SELECT node.id, (COUNT(parent.id) - 1) AS 
> depth FROM target_directory AS node, target_directory AS parent WHERE 
> node.lft BETWEEN parent.lft AND parent.rgt AND node.id = 1 GROUP BY node.id 
> ORDER BY node.lft )AS sub_tree WHERE node.lft BETWEEN parent.lft AND 
> parent.rgt AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt AND 
> sub_parent.id = sub_tree.id  GROUP BY node.id HAVING depth***<=* 1 ORDER BY 
> node.lft ;

Argh.  An example where doing the work in your software rather than a huge SQL 
statement might be more understandable, both in your debugging and if anyone 
else ever has to read your code.  Another way to simplify things might be to 
define your sub-select as a VIEW.

General note: when making up a name for a calculation like 'depth', try to make 
sure it's not the name of any of the columns in the tables mentioned in your 
SELECT.  This avoids ambiguity.

> See the pic1_working.PNG for the result with the given Database.

Sorry, attachments don't work on this list.  We don't want everyone sending us 
their homework.

> Problem: The result contains the parent-node also. So I changed last 
> statement "HAVING depth <= 1" to "HAVING depth<1" and the result of the query 
> is now empty (see pic2_not_working.png) -- expected would be one result set.

Nothing obvious but try these:

(A) remove the ORDER BY clauses and everything else you can think of and see 
whether you still get weird results.  See if you can make the simplest possible 
SELECT that comes up with unexpected results.  See if you can get weird results 
by substituting a specific value for the sub-SELECT.  The smaller the SELECT 
the easier it is to see what's wrong.

(B) check to see if you have NULLs anywhere.  They tend to make things look 
weird.

Hope some of that helps.

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

Re: [sqlite] Bug report: Sqlite seg fault, probably after database gets corrupt

[Richard Hipp]

On Thu, Aug 1, 2013 at 3:30 PM, Brian Vincent  wrote:

> if( d1>=(u32)nKey1 && sqlite3VdbeSerialTypeLen(serial_type1)>0 ) break;
>
> The next line will likely segfault if d1>=nKey1, right?  What if d1>=nKey1,
> but it's not true that sqlite3VdbeSerialTypeLen(serial_type1)>0 ?  Wouldn't
> this still cause a segfault?  Is that a valid concern?
>

Not a concern.

The [d1] is just an address.  And it never gets dereferenced if the
SerialTypeLen is zero.



>
> -Brian Vincent
>
>
>
> On Thu, Aug 1, 2013 at 2:19 PM, Richard Hipp  wrote:
>
> > On Thu, Aug 1, 2013 at 2:20 PM, Brian Vincent  wrote:
> >
> > > I think I can describe, is a
> > > possibly way that a corrupt database is causing sqlite to segfault.
> > >
> >
> > Thanks.  Fixed in http://www.sqlite.org/src/info/c3baca99f4 including a
> > test 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
> >
> ___
> 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

Re: [sqlite] Bug report: Sqlite seg fault, probably after database gets corrupt

[Brian Vincent]

if( d1>=(u32)nKey1 && sqlite3VdbeSerialTypeLen(serial_type1)>0 ) break;

The next line will likely segfault if d1>=nKey1, right?  What if d1>=nKey1,
but it's not true that sqlite3VdbeSerialTypeLen(serial_type1)>0 ?  Wouldn't
this still cause a segfault?  Is that a valid concern?

-Brian Vincent



On Thu, Aug 1, 2013 at 2:19 PM, Richard Hipp  wrote:

> On Thu, Aug 1, 2013 at 2:20 PM, Brian Vincent  wrote:
>
> > I think I can describe, is a
> > possibly way that a corrupt database is causing sqlite to segfault.
> >
>
> Thanks.  Fixed in http://www.sqlite.org/src/info/c3baca99f4 including a
> test 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
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: Sqlite seg fault, probably after database gets corrupt

[Richard Hipp]

On Thu, Aug 1, 2013 at 2:20 PM, Brian Vincent  wrote:

> I think I can describe, is a
> possibly way that a corrupt database is causing sqlite to segfault.
>

Thanks.  Fixed in http://www.sqlite.org/src/info/c3baca99f4 including a
test 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

Re: [sqlite] Bug report: Sqlite seg fault, probably after database gets corrupt

[Stephan Beal]

On Thu, Aug 1, 2013 at 8:20 PM, Brian Vincent  wrote:

> next line assigns it to d1, which is a signed integer, so d1 gets a
> negative value.


To be strictly pedantic, overflow/underflow are undefined for _signed_
types in C. Here are some details:
http://en.wikipedia.org/wiki/Integer_overflow
The "Origin" section.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: Sqlite seg fault, probably after database gets corrupt

[Warren Young]

On 8/1/2013 12:20, Brian Vincent wrote:

Let me first say that we sometimes see databases that go corrupt.  I
haven't pinpointed the cause yet,


This may be enlightening: "How to Corrupt an SQLite Database File"

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

Re: [sqlite] Bug in SQLite 3.7.15-3.7.17 regarding group by query after joining two table with primary key index

[Chen, Mi]

Thanks very much for the prompt action!

One other simple workaround, if performance is not a concern,

SELECT T2.A, T2.B, T1.D, T1.E, T1.F, T1.G, T1.H, MAX(T1.C) FROM T1, T2 WHERE 
T1.B = T2.B AND T1.C = T2.C GROUP BY T2.A || " " || T2.B || " " || T1.D || " " 
|| T1.E || " " || T1.F || " " || T1.G || " " || T1.H;

Performance is one of our big concerns so we will evaluate NGQP as soon as it 
comes out.

Thanks again for the great work!

Mi Chen
mi.c...@echostar.com

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Wednesday, June 05, 2013 6:21 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Bug in SQLite 3.7.15-3.7.17 regarding group by query 
after joining two table with primary key index

On Tue, Jun 4, 2013 at 2:42 PM, Chen, Mi <mi.c...@echostar.com> wrote:

> All, I encountered a likely bug during development with latest SQL
> versions (3.7.17)... It appears to be affecting the result of queries with
> GROUP BY clause with partial join over two primary keys.
>

Your test case has been added here:

http://www.sqlite.org/src/info/96afe50866

Your test works with the next-generation query planner (NGQP) but (as you
observe) fails in 3.7.17.  Probably this is due to one of the existing bug
reports written against the ORDER BY optimizer in 3.7.17.  The problem
should be fixed when we cut over to the NGQP.

-- 
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] Bug in SQLite 3.7.15-3.7.17 regarding group by query after joining two table with primary key index

[Richard Hipp]

On Tue, Jun 4, 2013 at 2:42 PM, Chen, Mi  wrote:

> All, I encountered a likely bug during development with latest SQL
> versions (3.7.17)... It appears to be affecting the result of queries with
> GROUP BY clause with partial join over two primary keys.
>

Your test case has been added here:

http://www.sqlite.org/src/info/96afe50866

Your test works with the next-generation query planner (NGQP) but (as you
observe) fails in 3.7.17.  Probably this is due to one of the existing bug
reports written against the ORDER BY optimizer in 3.7.17.  The problem
should be fixed when we cut over to the NGQP.

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

Re: [sqlite] Bug in SQlite .NET Version 1.0.82.0

[Joe Mistachkin]

Michael Endres wrote:
>
> After performing Database Actions, we used 
> SQLiteConnection Close() to close the database connection.
> 
> Afterwards, I use File.ReadAllBytes() on the Database File, which lead to
a
> Access Violation because the file was still opened in a different process.
> When we switched back to Version 1.0.80.0,and it worked again.
> 
> It seems like the filestream is not closed, when using SqliteConnection
Close(). 
> 

As of version 1.0.82.0, the underlying native database connection is not
completely
closed until *ALL* outstanding statements are finalized and all backups are
finished.

--
Joe Mistachkin

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

Re: [sqlite] bug with sqlite

[Black, Michael (IS)]

Just to help clarify (hopefully) the Unix/Windows "reserved filename".

CON: is similar to Unix's /dev/zero or /dev/null for example -- Files that 
already exist and have OS meaning.

stdout is NOT a reserved filename...it's a predefined variable of FILE *.  You 
cannot say "cp file stdout" on Unix like you can say "copy file con:" on 
Windows.  CON: is a reserve filename.

This program will give you a warning compling about using stdout the variable
#include 
main()
{
FILE *fp1,*fp2;
fp1=fopen(stdout,"w");
if (fp1 == NULL) perror("stdout reserved");
else fputs("stdout funky here",fp1);
fputs("stdout console here\n",stdout);
fp2=fopen("stdout","w");
if (fp2 == NULL) perror("stdout file");
else fputs("stdout file here",fp2);
#ifdef _WIN32
fp1=fopen("CON","w");
if (fp1 == NULL) perror("stdout reserved");
else fputs("stdout windows console here",fp1);
#endif
}

On Unix there are no errors.  Unix allows you to create funky filenames.
Visual Studio Express 2010 produces an error on the open to CON
stdout reserved: Invalid argument
stdout console here
stdout windows console here

Make sure you run this in a temporary working directoy as you'll get a really 
funky filename for fp1.
This will run, and produce two files and the console output (fp1 is actually an 
error in my book but it won't tell you that without more checking like 
"isprint").
fp1 ends up being a funky filname based on *stdout and contains "stdout 
reserved here"
fp2 ends up being a file called "stdout" in the directory and contains "stdout 
file here".



Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Tuesday, March 29, 2011 6:48 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] bug with sqlite

On 29 Mar 2011, at 12:38pm, Arjen Markus wrote:

> is this under Windows? con is one of the reserved file names, dating
> from the DOS days (or even before that).

Bah.  Arjen beat me to it.  Yes 'con' is the filename you used to use when you 
wanted to talk to the CONSOLE: the terminal connected to the front of the 
mainframe rather than one being served using the timesharing system.  It is 
something like what Unix means by 'stdin' and 'stdout'.

>  Other reserved names are aux,
> nul and prn (I think there is a fifth, but I cannot remember that one).

Depends which company's mainframes you used to use.  But I believe some 
versions of DOS carried over LPT1 to LPT9 (line printer), CLOCK$ (a 
pseudo-device which outputs a timer count), and COM1 to COM9 channels (serial 
communications).

> So, that has nothing to do with SQLite itself.

Agreed.  Other operating systems may have no trouble with a file called 'con' 
but have problems with other filenames, for example 'stdout'.

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

Re: [sqlite] bug with sqlite

[Simon Slavin]

On 29 Mar 2011, at 12:38pm, Arjen Markus wrote:

> is this under Windows? con is one of the reserved file names, dating
> from the DOS days (or even before that).

Bah.  Arjen beat me to it.  Yes 'con' is the filename you used to use when you 
wanted to talk to the CONSOLE: the terminal connected to the front of the 
mainframe rather than one being served using the timesharing system.  It is 
something like what Unix means by 'stdin' and 'stdout'.

>  Other reserved names are aux, 
> nul and prn (I think there is a fifth, but I cannot remember that one).

Depends which company's mainframes you used to use.  But I believe some 
versions of DOS carried over LPT1 to LPT9 (line printer), CLOCK$ (a 
pseudo-device which outputs a timer count), and COM1 to COM9 channels (serial 
communications).

> So, that has nothing to do with SQLite itself.

Agreed.  Other operating systems may have no trouble with a file called 'con' 
but have problems with other filenames, for example 'stdout'.

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

Re: [sqlite] bug with sqlite

[Marian Cascaval]

Windows doesn't allow "con" named fodlers or files.



Marian Cascaval





From: Felix Zimmermann 
To: sqlite-users@sqlite.org
Sent: Mon, March 28, 2011 10:26:59 PM
Subject: [sqlite] bug with sqlite

hi
why isnt it possible to create a database file with the name "con" ? i just 
doesnt work. no matter what file extension im taking.

regrets
Felix
___
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] bug with sqlite

[Arjen Markus]

Hi,

is this under Windows? con is one of the reserved file names, dating
from the DOS days (or even before that). Other reserved names are aux, 
nul and prn (I think there is a fifth, but I cannot remember that one).

So, that has nothing to do with SQLite itself.

Regards,

Arjen


On 2011-03-28 21:26, Felix Zimmermann wrote:
> hi
> why isnt it possible to create a database file with the name "con" ? i just 
> doesnt work. no matter what file extension im taking.
> 
> regrets
> Felix
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
 

DISCLAIMER: This message is intended exclusively for the addressee(s) and may 
contain confidential and privileged information. If you are not the intended 
recipient please notify the sender immediately and destroy this message. 
Unauthorized use, disclosure or copying of this message is strictly prohibited.
The foundation 'Stichting Deltares', which has its seat at Delft, The 
Netherlands, Commercial Registration Number 41146461, is not liable in any way 
whatsoever for consequences and/or damages resulting from the improper, 
incomplete and untimely dispatch, receipt and/or content of this e-mail.




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

Re: [sqlite] BUG in SQLite? Still the rowid question

[Wanadoo Hartwig]

Am 30.08.2009 um 17:56 schrieb Kit:

> 2009/8/29 Wanadoo Hartwig :
>> The largest and last row id is 4. Why is SQLite returning 5? I think
>> it has to do with the FTS3 module but still the trigger statement
>> should shield the row ids from the trigger statement, or?
>> Hartwig
>
> CREATE TABLE Simple (ID integer primary key, Name text);
> CREATE TABLE SimpleFTS (Name);
> CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH ROW
>   BEGIN
>   DELETE FROM SimpleFTS WHERE (rowid=OLD.ID);
>   END;
> CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH ROW
>   BEGIN
>   INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name);
>   END;
> INSERT INTO Simple (Name) VALUES('one');
> INSERT INTO Simple (Name) VALUES('two');
> DELETE FROM Simple WHERE (ID = 1);
> INSERT INTO Simple (Name) VALUES('three');
> SELECT * FROM Simple;
> 2|two
> 3|three
> SELECT last_insert_rowid() FROM Simple;
> 3
> 3
>
> Perfect.
>

I know. Therefore, you have to use FTS3.

BTW: RTree seems to work.

> sqlite> CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);
> SQL error: no such module: FTS3
>
> What's FTS3? http://dotnetperls.com/sqlite-fts3 ?
>

FTS3 is the built-in full-text search engine. You have to compile  
SQLite with SQLITE_ENABLE_FTS3.

> Virtual tables are a new feature in SQLite (currently still only
> available from the development version on CVS)
> -- 
> Kit
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Hartwig


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

Re: [sqlite] BUG in SQLite? Still the rowid question

[Kit]

2009/8/29 Wanadoo Hartwig :
> The largest and last row id is 4. Why is SQLite returning 5? I think
> it has to do with the FTS3 module but still the trigger statement
> should shield the row ids from the trigger statement, or?
> Hartwig

CREATE TABLE Simple (ID integer primary key, Name text);
CREATE TABLE SimpleFTS (Name);
CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple FOR EACH ROW
   BEGIN
   DELETE FROM SimpleFTS WHERE (rowid=OLD.ID);
   END;
CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple FOR EACH ROW
   BEGIN
   INSERT INTO SimpleFTS (rowid,Name) VALUES(NEW.ID,NEW.Name);
   END;
INSERT INTO Simple (Name) VALUES('one');
INSERT INTO Simple (Name) VALUES('two');
DELETE FROM Simple WHERE (ID = 1);
INSERT INTO Simple (Name) VALUES('three');
SELECT * FROM Simple;
2|two
3|three
SELECT last_insert_rowid() FROM Simple;
3
3

Perfect.

sqlite> CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);
SQL error: no such module: FTS3

What's FTS3? http://dotnetperls.com/sqlite-fts3 ?

Virtual tables are a new feature in SQLite (currently still only
available from the development version on CVS)
-- 
Kit
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite 3.6.14 / wrong rowid returned

[Wanadoo Hartwig]

Hi Simon,

I am not complaining about the autoincrement's result but that  
sqlite3_insert_rowid returns the wrong rowid.

Hartwig

Am 29.06.2009 um 23:09 schrieb Simon Slavin:

>
> On 29 Jun 2009, at 8:54pm, Wanadoo Hartwig wrote:
>
>> I have posted a while ago a bug (at least I think that it is a bug)
>> but only in a very abstract form. Now, I have written a C-program
>> showing the bug.
>
> I assume that if you type those commands into the sqlite3 command-line
> tool, you get the same result.  As to the use of rowid, do your
> results agree with
>
> http://www.sqlite.org/autoinc.html
>
> ?
>
> 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

Re: [sqlite] Bug in SQLite 3.6.14 / wrong rowid returned

[Simon Slavin]

On 29 Jun 2009, at 8:54pm, Wanadoo Hartwig wrote:

> I have posted a while ago a bug (at least I think that it is a bug)
> but only in a very abstract form. Now, I have written a C-program
> showing the bug.

I assume that if you type those commands into the sqlite3 command-line  
tool, you get the same result.  As to the use of rowid, do your  
results agree with

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

?

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

Re: [sqlite] Bug in SQLite 3.6.14 / wrong rowid returned

[Wanadoo Hartwig]

Hi,

actually, I also tested it with 3.6.16 and the bug still exists.

Hartwig

Am 29.06.2009 um 21:54 schrieb Wanadoo Hartwig:

> Hello,
>
> I have posted a while ago a bug (at least I think that it is a bug)
> but only in a very abstract form. Now, I have written a C-program
> showing the bug.
>
> Brief description:
>
> An FTS related trigger combination leads to a wrong determination of
> the last inserted row id. Triggers that are not related to FTS are
> working fine.
>
>
> #include 
> #include 
>
> #include "sqlite3.h"
>
> void OnErrorExit(sqlite3* database, int rc, char* errorMessage)
> {
>   if (rc != SQLITE_OK)
>   {
> if (errorMessage != NULL)
> {
>   fprintf(stderr,"SQL error: %s\n",errorMessage);
>   sqlite3_free(errorMessage);
> } /* if */
> sqlite3_close(database);
> exit(1);
>   } /* if */
> }
>
> void ShowDatabaseContents(sqlite3* database)
> {
>   char* errorMessage;
>   char** result;
>   char* sqlStatement;
>
>   int noOfColumns, noOfRows;
>   int rc;
>
>   sqlite3_int64 lastInsertedID;
>
>
>   lastInsertedID = sqlite3_last_insert_rowid(database);
>   printf("Last inserted ID: %d\n",(int) lastInsertedID);
>   sqlStatement = "SELECT * FROM Simple;";
>   rc =
> sqlite3_get_table
> (database,sqlStatement);
>   OnErrorExit(database,rc,errorMessage);
>
>   printf("Number of rows: %d\n",noOfRows);
>   printf("Number of columns: %d\n",noOfColumns);
>   for (int i=0; i   {
> printf("Row: %2d ",i);
> for (int j=0; j   printf("  Column[%d]: %s",j,result[i*noOfColumns+j]);
> printf("\n");
>   } /* for */
> }
>
> int main (int argc, const char * argv[])
> {
>   char* errorMessage;
>   char* sqlStatement;
>
>   int rc;
>
>   sqlite3* database;
>
>
>  // prepare database
>   rc = sqlite3_open(NULL,);
>   if (rc != SQLITE_OK)
>   {
> fprintf(stderr,"Can't open database in memory: %s
> \n",sqlite3_errmsg(database));
> exit(1);
>   } /* if */
>   sqlStatement = "CREATE TABLE Simple (ID integer primary key, Name
> text);";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,);
>   OnErrorExit(database,rc,errorMessage);
>   sqlStatement = "CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,);
>   OnErrorExit(database,rc,errorMessage);
>   sqlStatement = "CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple
> FOR EACH ROW BEGIN DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END;";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,);
>   OnErrorExit(database,rc,errorMessage);
>   sqlStatement = "CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple
> FOR EACH ROW BEGIN INSERT INTO SimpleFTS (rowid,Name) VALUES
> (NEW.ID,NEW.Name); END;";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,);
>   OnErrorExit(database,rc,errorMessage);
>  // insert and delete items
>   sqlStatement = "INSERT INTO Simple (Name) VALUES('one');";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,);
>   OnErrorExit(database,rc,errorMessage);
>   sqlStatement = "INSERT INTO Simple (Name) VALUES('two');";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,);
>   OnErrorExit(database,rc,errorMessage);
>   ShowDatabaseContents(database);
>   sqlStatement = "DELETE FROM Simple WHERE (ID = 1);";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,);
>   OnErrorExit(database,rc,errorMessage);
>   ShowDatabaseContents(database);
>   sqlStatement = "INSERT INTO Simple (Name) VALUES('one');";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,);
>   OnErrorExit(database,rc,errorMessage);
>   ShowDatabaseContents(database);
>   sqlStatement = "INSERT INTO Simple (Name) VALUES('one');";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,);
>   OnErrorExit(database,rc,errorMessage);
>   ShowDatabaseContents(database);
>
>   sqlite3_close(database);
>
>   printf("Done!\n");
>   return 0;
> }
>
> This is the output after the last insert:
>
> Last inserted ID: 5
> Number of rows: 3
> Number of columns: 2
> Row:  0   Column[0]: ID  Column[1]: Name
> Row:  1   Column[0]: 2  Column[1]: two
> Row:  2   Column[0]: 3  Column[1]: one
> Row:  3   Column[0]: 4  Column[1]: one
>
> Actually, I would expect this:
>
> Last inserted ID: 4
> Number of rows: 3
> Number of columns: 2
> Row:  0   Column[0]: ID  Column[1]: Name
> Row:  1   Column[0]: 2  Column[1]: two
> Row:  2   Column[0]: 3  Column[1]: one
> Row:  3   Column[0]: 4  Column[1]: one
>
> Interestingly the output is like a like to have it but then I have to
> use any trigger but not an FTS related trigger!
>
> Hartwig
>
>
>
> ___
> 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] Bug in SQLITE? "Joins + Order By" Changing row count!?!

[Dan]

Create an sql dump using the ".dump" command of the sqlite3 shell
tool:

   $ echo .dump | sqlite3 database_file.db > dump.sql

Or just put the database file up for download somewhere. Or if you
prefer, send it to me by email. If this bug is present in current
versions, we need to fix it. But it's much more difficult to figure
out if it is still present or not without a database to run your
queries against.

Thanks,
Dan.



On Mar 4, 2009, at 9:17 PM, Jonathon wrote:

> Thanks Dan for the reply,
>
> How would I go about creating a sql dump?
>
> As for the ORDER BY clause, I do this:
>
> SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER  
> JOIN
> tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND  
> a.d_id =
> d.id AND c.e_id = e.id ORDER BY a.some_other_value ASC;
>
> Although a.some_other_value is not displayed in the tableA below, I  
> forgot
> to add it.  So, I add the ORDER BY clause on a.some_other_value and it
> returns a ton of records (mostly duplicates).  I am pretty sure this  
> is a
> bug because if I wrap this query inside of a subquery and perform  
> the ORDER
> BY on the subquery, everything works.
>
> This is what WORKS:
>
> SELECT * FROM (
> SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER  
> JOIN
> tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND  
> a.d_id =
> d.id AND c.e_id = e.id ) ORDER BY a.some_other_value ASC;
>
> So for now, I am just leaving my original query inside the subquery  
> to fix
> it.  However, I wouldn't mind posting some debugging information if  
> it will
> help fix the bug.
>
> Thanks,
> J
>
>
> On Tue, Mar 3, 2009 at 8:23 PM, Dan  wrote:
>
>>
>> On Mar 4, 2009, at 4:41 AM, Jonathon wrote:
>>
>>> Hello,
>>>
>>> I am executing a query such as:
>>
>> Can you post a database (or sql dump thereof) to run your queries
>> against? Also say exactly what ORDER BY clause you are adding to
>> the end of the query that causes it to return incorrect results?
>>
>> Thanks,
>> Dan.
>>
>>
>>
>>>  1. SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c
>>> INNER
>>>  JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id
>>> AND
>>>  a.d_id = d.id AND c.e_id = e.id;
>>>  2.
>>>  3. CREATE TABLE tableA (
>>>  4. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
>>>  5. b_idINTEGER NOT NULL,
>>>  6. c_idINTEGER NOT NULL,
>>>  7. d_idINTEGER NOT NULL
>>>  8. )
>>>  9.
>>>  10. CREATE TABLE tableB (
>>>  11. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
>>>  12. );
>>>  13.
>>>  14. CREATE TABLE tableC (
>>>  15. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
>>>  16. e_idINTEGER NOT NULL
>>>  17. );
>>>  18.
>>>  19. CREATE TABLE tableD (
>>>  20. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
>>>  21. );
>>>  22.
>>>  23. CREATE TABLE tableE (
>>>  24. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
>>>  25. );
>>>
>>>
>>> This seems to be returning the correct records.   However, when I
>>> append an
>>> ORDER BY at the end of the query, it seems to be changing the number
>>> of
>>> records that are returned back to me.  From my understanding, an
>>> ORDER BY
>>> can not change the number of records correct?  If I do not use the
>>> ORDER BY,
>>> I get around 150 records.   If I do an ORDER BY on any column that
>>> is not in
>>> tableA, the number of records blows up (~4k) and there are  
>>> duplicates.
>>>
>>> Any ideas?
>>>
>>> I thought that it was because of a buggy parser, so I wrote the
>>> query again
>>> as:
>>>
>>>
>>>  1. SELECT * FROM tableA a
>>>  2. INNER JOIN tableB b
>>>  3. ON a.b_id = b.id
>>>  4. INNER JOIN tableD d
>>>  5. ON a.d_id = d.id
>>>  6. INNER JOIN tableC c
>>>  7. LEFT JOIN tableE e ON c.e_id = e.id
>>>  8. ON ON a.c_id = c.id
>>>
>>> and it still gave me the same results...
>>>
>>> Thanks,
>>> J
>>> ___
>>> 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-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] Bug in SQLITE? "Joins + Order By" Changing row count!?!

[Jonathon]

Thanks Dan for the reply,

How would I go about creating a sql dump?

As for the ORDER BY clause, I do this:

SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER JOIN
tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND a.d_id =
d.id AND c.e_id = e.id ORDER BY a.some_other_value ASC;

Although a.some_other_value is not displayed in the tableA below, I forgot
to add it.  So, I add the ORDER BY clause on a.some_other_value and it
returns a ton of records (mostly duplicates).  I am pretty sure this is a
bug because if I wrap this query inside of a subquery and perform the ORDER
BY on the subquery, everything works.

This is what WORKS:

SELECT * FROM (
SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c INNER JOIN
tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id AND a.d_id =
d.id AND c.e_id = e.id ) ORDER BY a.some_other_value ASC;

So for now, I am just leaving my original query inside the subquery to fix
it.  However, I wouldn't mind posting some debugging information if it will
help fix the bug.

Thanks,
J


On Tue, Mar 3, 2009 at 8:23 PM, Dan  wrote:

>
> On Mar 4, 2009, at 4:41 AM, Jonathon wrote:
>
> > Hello,
> >
> > I am executing a query such as:
>
> Can you post a database (or sql dump thereof) to run your queries
> against? Also say exactly what ORDER BY clause you are adding to
> the end of the query that causes it to return incorrect results?
>
> Thanks,
> Dan.
>
>
>
> >   1. SELECT * FROM tableA a INNER JOIN tableB b INNER JOIN tableC c
> > INNER
> >   JOIN tableD d LEFT JOIN tableE ON a.b_id = b.id AND a.c_id = c.id
> > AND
> >   a.d_id = d.id AND c.e_id = e.id;
> >   2.
> >   3. CREATE TABLE tableA (
> >   4. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
> >   5. b_idINTEGER NOT NULL,
> >   6. c_idINTEGER NOT NULL,
> >   7. d_idINTEGER NOT NULL
> >   8. )
> >   9.
> >   10. CREATE TABLE tableB (
> >   11. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
> >   12. );
> >   13.
> >   14. CREATE TABLE tableC (
> >   15. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
> >   16. e_idINTEGER NOT NULL
> >   17. );
> >   18.
> >   19. CREATE TABLE tableD (
> >   20. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
> >   21. );
> >   22.
> >   23. CREATE TABLE tableE (
> >   24. id  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT
> >   25. );
> >
> >
> > This seems to be returning the correct records.   However, when I
> > append an
> > ORDER BY at the end of the query, it seems to be changing the number
> > of
> > records that are returned back to me.  From my understanding, an
> > ORDER BY
> > can not change the number of records correct?  If I do not use the
> > ORDER BY,
> > I get around 150 records.   If I do an ORDER BY on any column that
> > is not in
> > tableA, the number of records blows up (~4k) and there are duplicates.
> >
> > Any ideas?
> >
> > I thought that it was because of a buggy parser, so I wrote the
> > query again
> > as:
> >
> >
> >   1. SELECT * FROM tableA a
> >   2. INNER JOIN tableB b
> >   3. ON a.b_id = b.id
> >   4. INNER JOIN tableD d
> >   5. ON a.d_id = d.id
> >   6. INNER JOIN tableC c
> >   7. LEFT JOIN tableE e ON c.e_id = e.id
> >   8. ON ON a.c_id = c.id
> >
> > and it still gave me the same results...
> >
> > Thanks,
> > J
> > ___
> > 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

RE: [sqlite] Bug in SQlite ?

[Rob Richardson]

What assertion failure are you seeing?  What is the exact message?  Can
you use a debugger to step into the code where the assertion failure
happens?

RobR

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Bug in SQlite ?

[Jay Sprenkle]

On 9/6/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

I am seeing an assert crash on my system here. I think it is caused by the
following code,

--select.c  (line 88)--

/*
** Delete the given Select structure and all of its substructures.
*/
void sqlite3SelectDelete(Select *p){
  if( p ){
clearSelect(p);
sqliteFree(p);
  }
}
--

I think it should be:

if ( *p )


Why do you think so? The other way seems a good way to check for a non null
pointer. "*p" is a structure. What does testing a structure for non-zero do?

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE

[Cariotoglou Mike]

ok, I pinned it down. it is a genuine bug, and has nothing to do with
dlls and wrappers.

to reproduce, do this:

1. open the command-line utility on a database (or even with no
database, it does not matter)
2. type this:


SQLite version 3.2.5
Enter ".help" for instructions
sqlite> pragma empty_Result_callbacks=1;
sqlite> analyze; 

this will crash immediately. 
in general, if empty_Result_callbacks is set, AND the database is
analyzed, sqlite crashes consistently. 

the reason it appeared in sqlite3Explorer is that it sets the pragma by
default.

DRH, pls check this.

> -Original Message-
> From: Miha Vrhovnik [mailto:[EMAIL PROTECTED] 
> Sent: Monday, September 05, 2005 7:14 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
> 
> "Cariotoglou Mike" <[EMAIL PROTECTED]> je ob 5.9.2005 
> 14:49:54 napisal(a):
> 
> >ok.
> >I use sqlite3Explorer (which should not matter), I create a new 
> >database, then I run :
> >
> >create table test1(id integer);
> >analyze
> >
> >this crashes immediately, with dll 3.2.5. it works fine from the 
> >command-line.
> > 
> I can confirm that. But it seems that the problem is in 
> Sqlite explorer or your sqlite3.dll wrapper implementation. 
> When I try that with my own Delphi wrapper implementation it 
> works. But opening database with sqlite explorer results in crash.
> 
> Regards,
> Miha
> 
> 
> 

RE: [sqlite] bug in sqlite 3.2.5 compila tion and ANALYZE

[Miha Vrhovnik]

"Cariotoglou Mike" <[EMAIL PROTECTED]> je ob 5.9.2005 14:49:54 napisal(a):

>ok.
>I use sqlite3Explorer (which should not matter), I create a new
>database, then I run :
>
>create table test1(id integer);
>analyze
>
>this crashes immediately, with dll 3.2.5. it works fine from the
>command-line.
>
I can confirm that. But it seems that the problem is in Sqlite explorer or your 
sqlite3.dll wrapper implementation.
When I try that with my own Delphi wrapper implementation it works. But opening 
database with sqlite explorer results in crash.

Regards,
Miha

RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE

[Cariotoglou Mike]

ok.
I use sqlite3Explorer (which should not matter), I create a new
database, then I run :

create table test1(id integer);
analyze

this crashes immediately, with dll 3.2.5. it works fine from the
command-line.
 

> -Original Message-
> From: Ned Batchelder [mailto:[EMAIL PROTECTED] 
> Sent: Monday, September 05, 2005 3:10 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
> 
> Perhaps you could provide the exact SQL that crashed.  The 
> code seems to be very well tested automatically, so it is 
> very unlikely that all ANALYZE executions fail.
> 
> --Ned.
> http://nedbatchelder.com
>  
> -Original Message-
> From: Cariotoglou Mike [mailto:[EMAIL PROTECTED]
> Sent: Monday, 05 September, 2005 5:46 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
> 
> I tried the ANALYZE statement with sqlite 3.2.5 in dll form, 
> (both the pre-compiled version downloaded from the site, AND 
> a local compilation).
> in both
> cases, the statement fails with an ACCESS VIOLATION. however, 
> the same statement, when run from the pre-compiled 
> sqlite3.exe, works. 
> further, having succesfully ANALYZED a database with the 
> command-line program, I can no longer work with the database 
> using the DLL api. 
> I think there is something very wrong with the compilation 
> defines of the DLL. 
> 
> DRH: can you pls check and fix ?
> ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS,
> obviously)
> 
> 
> 
> 
> 
> 
> 

RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE

[Ned Batchelder]

Perhaps you could provide the exact SQL that crashed.  The code seems to be
very well tested automatically, so it is very unlikely that all ANALYZE
executions fail.

--Ned.
http://nedbatchelder.com
 
-Original Message-
From: Cariotoglou Mike [mailto:[EMAIL PROTECTED] 
Sent: Monday, 05 September, 2005 5:46 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE

I tried the ANALYZE statement with sqlite 3.2.5 in dll form, (both the
pre-compiled version downloaded from the site, AND a local compilation).
in both
cases, the statement fails with an ACCESS VIOLATION. however, the same
statement, when run from the pre-compiled sqlite3.exe, works. 
further, having succesfully ANALYZED a database with the command-line
program, I can no longer work with the database using the DLL api. 
I think there is something very wrong with the compilation defines of
the DLL. 

DRH: can you pls check and fix ?
ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS,
obviously)

RE: [sqlite] Bug in SQLite C++ Wrapper?

[Brown, Dave]

Aha, thanks! 

-Original Message-
From: Cory Nelson [mailto:[EMAIL PROTECTED] 
Sent: Saturday, June 25, 2005 11:30 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Bug in SQLite C++ Wrapper?

cmd.executenonquery() creates a reader, which calls reset when destructed :)

On 6/25/05, Brown, Dave <[EMAIL PROTECTED]> wrote:
> 
> In looking at the SQLite C++ Wrapper 
> (http://dev.int64.org/sqlite.html) I noticed the example insert code has a
loop like:
> 
>  for(int i=0; i<1; i++) {
> cmd.bind(1, i);
> cmd.executenonquery();
>  }
> 
> but sqlite3_reset() isn't being called each time after the statement 
> is executed (executenonquery() just ends up calling sqlite3_step() ). 
> Doesn't
> sqlite3_reset() have to be called in order to reuse a prepared query?
> 
> -Dave
> 


--
Cory Nelson
http://www.int64.org

Re: [sqlite] Bug in SQLite C++ Wrapper?

[Cory Nelson]

cmd.executenonquery() creates a reader, which calls reset when destructed :)

On 6/25/05, Brown, Dave <[EMAIL PROTECTED]> wrote:
> 
> In looking at the SQLite C++ Wrapper (http://dev.int64.org/sqlite.html) I
> noticed the example insert code has a loop like:
> 
>  for(int i=0; i<1; i++) {
> cmd.bind(1, i);
> cmd.executenonquery();
>  }
> 
> but sqlite3_reset() isn't being called each time after the statement is
> executed (executenonquery() just ends up calling sqlite3_step() ). Doesn't
> sqlite3_reset() have to be called in order to reuse a prepared query?
> 
> -Dave
> 


-- 
Cory Nelson
http://www.int64.org