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 report (INSTR() ignores NOCASE on columns)

[Jose Isaias Cabrera]

Stephan Senzel, on Sunday, February 2, 2020 08:12 AM, wrote...
>
> INSTR() ignores NOCASE on columns
>
> ---
>
> example:
>
> SELECT * FROM table WHERE INSTR(column, ' castle ') > 0
>
> returns datasets with 'castle' only, without 'Castle', even if the
> column is set to NOCASE

True statement with v3.31.0:
12:25:41.10>sqlite3
SQLite version 3.31.0 2020-01-22 18:38:59
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t0(a string collate nocase);
sqlite> insert into t0 values ('In my castle I have...');
sqlite> insert into t0 values ('In my castle I have had...');
sqlite> insert into t0 values ('In my castle I''ve never had...');
sqlite> insert into t0 values ('In my Castle I have...');
sqlite> select a from t0 where INSTR(a,' castle') > 0;
In my castle I have...
In my castle I have had...
In my castle I've never had...
sqlite>


> LIKE doesn't have this problem, works well
>
> SELECT * FROM table WHERE column LIKE '% castle %'
>
> returns 'castle' and 'Castle' when column is set to NOCASE

Also true with v3.31.0:
sqlite> select a from t0 where a LIKE '% castle%';
In my castle I have...
In my castle I have had...
In my castle I've never had...
In my Castle I have...
sqlite>

Just making sure... :-)

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

Re: [sqlite] Bug report: Potential thread safety issues in sqlite3_initialize

[Oystein Eftevaag]

As I understand it, the barrier in that patch ensures that for whichever
thread executes the if(!sqlite3GlobalConfig.mutex.xMutexAlloc codepath)
{...}, the write to pTo->xMutexAlloc will be stored after the rest of the
xMutex* field writes. But there's nothing preventing another thread
*loading* them out of order; e.g. loading an uninitialized
sqlite3GlobalConfig.mutex.xMutexInit into a register prior to loading a now
initialized sqlite3GlobalConfig.mutex.xMutexAlloc (hence skipping the if()
block  in sqlite3MutexInit()), which could effectively result in a
sqlite3MutexInit() call with sqlite3GlobalConfig.mutex.xMutexInit
still being null after (for some number of cycles).

https://gist.github.com/vinterstum/ff4bc1ea715cc1d4c5da45864c9de4af should
help I think.

On Tue, Jan 28, 2020 at 4:57 PM Richard Hipp  wrote:

> On 1/28/20, Oystein Eftevaag  wrote:
> > in sqlite3MutexInit() sqlite3GlobalConfig.mutex.xMutexAlloc
> > can be read as being set on a core, while the rest of the initialization
> > done in sqlite3MutexInit() still is being read as unset.
>
> Doesn't the memory barrier at
> https://www.sqlite.org/src/artifact/bae36f8af32c22ad?ln=247 prevent
> that?  Do you have a suggested patch to make it work?
>
>
> --
> 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 report: Potential thread safety issues in sqlite3_initialize

[Richard Hipp]

On 1/28/20, Oystein Eftevaag  wrote:
> in sqlite3MutexInit() sqlite3GlobalConfig.mutex.xMutexAlloc
> can be read as being set on a core, while the rest of the initialization
> done in sqlite3MutexInit() still is being read as unset.

Doesn't the memory barrier at
https://www.sqlite.org/src/artifact/bae36f8af32c22ad?ln=247 prevent
that?  Do you have a suggested patch to make it work?


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

[mailing lists]

Hi Jens,

the MATCH operator is not inside an OR expression. The MATCH operator is in an 
AND expression, only the rowid request is in an OR expression.

Regards,
Hartwig

PS: In FTS5 since version 3.30.1 also the MATCH operator is allowed in OR 
statements (try SELECT PlayersFTS.rowid FROM PlayersFTS WHERE (PlayersFTS MATCH 
'LastName:B') OR (PlayersFTS MATCH 'FirstNames:2');)

> Am 2020-01-23 um 17:51 schrieb Jens Alfke :
> 
> 
>> On Jan 23, 2020, at 6:47 AM, mailing lists  wrote:
>> 
>> The following SELECT statement fails with the error "unable to use function 
>> MATCH in the requested context":
> 
> This is an annoying but documented limitation of FTS, not a bug. The MATCH 
> operator can’t be used inside an OR expression. It has to be at top-level or 
> in an AND.
> 
> —Jens
> ___
> 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 report

[Warren Young]

On Jan 23, 2020, at 7:02 AM, Mark Benningfield  wrote:
> 
> ...whenever I do a Fossil pull of the latest
> version takes a grand total of about 2 seconds, but it would be nice not to
> have to remember to do it every time :)

If you’re having to reapply the change on every Fossil update, you’re probably 
making the change to the wrong place in the code: you’re changing a generated 
file rather than a proper source file.

Saying “fossil up” or “fossil up release” should merge your local edits into 
the new release automatically unless upstream changes something nearby or on 
those same lines.

I don’t say this expecting that these problems will remain unfixed upstream, 
just as general forward-looking advice.  Fossil can be a useful aide in 
carrying local changes from one release to the next.

There are more advanced methods beyond that, such as private branches and 
autosync=0, but at that point we should take it up on the Fossil forum.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG(?) in FTS5

[Jens Alfke]

> On Jan 23, 2020, at 6:47 AM, mailing lists  wrote:
> 
> The following SELECT statement fails with the error "unable to use function 
> MATCH in the requested context":

This is an annoying but documented limitation of FTS, not a bug. The MATCH 
operator can’t be used inside an OR expression. It has to be at top-level or in 
an AND.

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

Re: [sqlite] Bug report

[Mark Benningfield]

Well, I kinda thought that this would be fixed on the next release. The
"value_frombind" typo in particular prevents FTS3/4 from being built as a
loadable extension. I only have one legacy application that uses FTS3/4 that
way, and fixing these typos whenever I do a Fossil pull of the latest
version takes a grand total of about 2 seconds, but it would be nice not to
have to remember to do it every time :)



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug on zPath length

[Richard Hipp]

On 1/23/20, Ondrej Dubaj  wrote:
> I discovered an issue found by coverity scan.

Thanks for the report.  This was previously fixed here:
https://www.sqlite.org/src/info/465a15c5c2077011


-- 
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 fixes only branch.

[Richard Hipp]

On 1/13/20, Syed Ahmad  wrote:
> We are at 3.14.2
>
> Current version = 3.14.2 Date : 2016-09-12
>
> https://www.sqlite.org/changes.html
>
> how can i take latest stable branch which include only bug fixes . no new
> features.
>
> Is there any way?

We sometimes do things like that for paid support customers.  But
maintaining bug-fix branches of historical versions is time-consuming,
so we do not do it routinely.  It is also risky, as actual releases
are better tested and more reliable than backported patches.

-- 
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 fixes only branch.

[Keith Medcalf]

On Monday, 13 January, 2020 15:00, Donald Griggs  wrote:

>On Mon, Jan 13, 2020 at 11:34 AM Syed Ahmad  
>wrote:

>> We are at 3.14.2   Date : 2016-09-12

>> how can i take latest stable branch which include only bug fixes . no
>> new features.

>> Is there any way?

> I may well not be understanding properly, but what motivates you to ask
> for this?

I would suspect that the motivation is a periodic risk re-assessment policy 
that has been either badly written or is being badly interpreted in the belief 
that the addition of "new features" that are unused to a component that is 
subject to risk assessment requires an assessment of the risk associated with 
the unused "new features".  In other words, the risk assessment is based on the 
version of something rather than the utilized functionality of something.

This is quite common and in my previous job (before retirement) significant 
resources were spent on unnecessarily re-assessing things just because the 
version number changed (which often meant that things were not updated in order 
to prevent this expensive process), rather than simply reviewing the existing 
Risk Assessment and determining that nothing had changed, and that the addition 
of new unused "features" was immaterial to the overall assessment.

That is, someone had generated a Risk Assessment based (for example) on the use 
of SQLite version 3.14.2 and that the mere act of updating the version triggers 
the process for the re-evaluation of the Risk of the new version in toto, 
including the Risk associated with "features available" rather than "features 
used", when in fact the update of the version (and the addition of new unused 
and inaccessible features) was quite irrelevant.

A significant amount of effort was expended globally (probably on the order of 
hundreds of thousands of man-hours at not insignificant engineering cost per 
hour) to remove "version numbers" from Risk Assessments and to make sure that 
they were based on functionality used/exposed rather than the version number 
itself.

In this example, the difference is that someone believes that (for example) 
because the current version of SQLite supports CTE's and the old one didn't, 
requires an assessment of the risk associated with CTEs, even though the 
specific use being assessed does not and cannot use CTE's, thus triggering a 
full assessment of Risk (including the unused CTE feature) rather than merely a 
review to determine whether or not there been any significant change to the 
risk profile which would require re-assessment.

In other words, if the "old" version of something only supported "red" and 
"blue", and the system only used "red", and a subsequent version added "green" 
without affecting the functionality of "red" (and that "blue" and "green" are 
not used and cannot be accessed) then the mere fact of the addition of the 
feature "green" is irrelevant (until such time as the feature "green" is used, 
of course).  The fact that the new thing "green" is available is merely a 
quaint observation of zero significance if (a) it is not used and (b) cannot be 
meaningfully accessed, and its addition is not a significant change to the risk 
of that something.

-- 
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 fixes only branch.

[Donald Shepherd]

On Tue, 14 Jan 2020 at 7:00 am, Donald Griggs  wrote:

> Hi, Syed,
>
> ===
> On Mon, Jan 13, 2020 at 11:34 AM Syed Ahmad 
> wrote:
>
> > We are at 3.14.2   Date : 2016-09-12
> >
> > how can i take latest stable branch which include only bug fixes . no new
> > features.
> >
> > Is there any way?
> > ==
>
>
> I may well not be understanding properly, but what motivates you to ask for
> this?
> Since the sqlite team spends so much effort to ensure backward
> compatibility, how bad would things be if you simply updated to the current
> stable release?
>
> The team does allow many features to be eliminated through conditional
> compilation if you are severely constrained in RAM.   Was RAM size the
> motivation?
>
> To provide versions which include only bug fixes from any arbitrary
> releasee, I should think the developers would, for every stable release,
> have to maintain a new fixes-only branch indefinitely -- and thus have to
> maintain dozens of branches.   Am I missing something?
>
> Kind regards,
>Donald
> ___


I can't speak to his exact scenario but having spent time in a very risk
averse work environment, I've experienced this kind of thinking.

The logic is almost always as a result of "we must have low bug counts
(true) so we need bug fixes (true) but new features introduce bugs (in
general true) therefore we don't want any new features".

In other words it's a result of the environment rather than a reflection of
SQLite.

Regards,
Donald Shepherd.

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

Re: [sqlite] Bug fixes only branch.

[Donald Griggs]

Hi, Syed,

===
On Mon, Jan 13, 2020 at 11:34 AM Syed Ahmad 
wrote:

> We are at 3.14.2   Date : 2016-09-12
>
> how can i take latest stable branch which include only bug fixes . no new
> features.
>
> Is there any way?
> ==


I may well not be understanding properly, but what motivates you to ask for
this?
Since the sqlite team spends so much effort to ensure backward
compatibility, how bad would things be if you simply updated to the current
stable release?

The team does allow many features to be eliminated through conditional
compilation if you are severely constrained in RAM.   Was RAM size the
motivation?

To provide versions which include only bug fixes from any arbitrary
releasee, I should think the developers would, for every stable release,
have to maintain a new fixes-only branch indefinitely -- and thus have to
maintain dozens of branches.   Am I missing something?

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

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

[Igor Korot]

Hi,

On Tue, Jan 7, 2020 at 2:01 AM Clemens Ladisch  wrote:
>
> Richard Hipp wrote:
> > On 1/5/20, Keith Medcalf  wrote:
> >> select * from a, b, c using (id); -- very strange result
> >
> > PostgreSQL and MySQL process the query as follows:
> >
> >SELECT * FROM a, (b JOIN c USING(id));
> >
> > SQLite processes the query like this:
> >
> >SELECT * FROM (a,b) JOIN c USING (id);
> >
> > I don't know which is correct.  Perhaps the result is undefined.
>
> Assuming the following query:
>
>   SELECT * FROM a, b JOIN c USING (id);
>
> SQL-92 says:
> |7.4  
> |
> | ::= FROM  [ {   reference> }... ]
> |
> |6.3  
> |
> | ::=
> |[ [ AS ] 
> |   [] ]
> | |  [ AS ] 
> |   []
> | | 
> |
> |7.5  
> |
> | ::=
> |   
> | | 
> | |   
> |
> | ::=
> |  CROSS JOIN 
> |
> | ::=
> |  [ NATURAL ] [  ] JOIN
> |[  ]
>
> It is not possible to have such a  inside a , so
> b and c must be joined first.
>
> SQLite actually parses the comma as a join:
>
>   SELECT * FROM a CROSS JOIN b JOIN c USING (id);
>
> If the query were written like this, joining a and b first would be
> correct.  (As far as I can see, the standard does not say how to handle
> ambiguous parts of the grammar, so it would also be allowed to produce
> "b JOIN c" first.)

That's why one should never use that "MS JOIN extension" and should
simply write:

SELECT ... FROM a,b,c WHERE a.x = b.x AND b.x = c.y AND...;

Thank you.

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

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

[Clemens Ladisch]

Richard Hipp wrote:
> On 1/5/20, Keith Medcalf  wrote:
>> select * from a, b, c using (id); -- very strange result
>
> PostgreSQL and MySQL process the query as follows:
>
>SELECT * FROM a, (b JOIN c USING(id));
>
> SQLite processes the query like this:
>
>SELECT * FROM (a,b) JOIN c USING (id);
>
> I don't know which is correct.  Perhaps the result is undefined.

Assuming the following query:

  SELECT * FROM a, b JOIN c USING (id);

SQL-92 says:
|7.4  
|
| ::= FROM  [ {   
}... ]
|
|6.3  
|
| ::=
|[ [ AS ] 
|   [] ]
| |  [ AS ] 
|   []
| | 
|
|7.5  
|
| ::=
|   
| | 
| |   
|
| ::=
|  CROSS JOIN 
|
| ::=
|  [ NATURAL ] [  ] JOIN
|[  ]

It is not possible to have such a  inside a , so
b and c must be joined first.

SQLite actually parses the comma as a join:

  SELECT * FROM a CROSS JOIN b JOIN c USING (id);

If the query were written like this, joining a and b first would be
correct.  (As far as I can see, the standard does not say how to handle
ambiguous parts of the grammar, so it would also be allowed to produce
"b JOIN c" first.)


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

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

[Keith Medcalf]

On Sunday, 5 January, 2020 04:42, Richard Hipp  wrote:

>On 1/5/20, Keith Medcalf  wrote:

>> Hrm.  Inconsistent/incorrect results.  Consider:

>> create table a(id integer primary key, a);
>> insert into a values (1,1), (2,1), (3,1);
>> create table b(id integer primary key, b);
>> insert into b values (1,2), (3,2), (4,2);
>> create table c(id integer primary key, c);
>> insert into c values (1,3), (4,3), (5,3);

>> select * from a, b, c using (id); -- very strange result

>> id  a   id  b   c
>> --  --  --  --  --
>> 1   1   1   2   3
>> 1   1   3   2   3
>> 1   1   4   2   3

>PostgreSQL and MySQL process the query as follows:

>   SELECT * FROM a, (b JOIN c USING(id));

>SQLite processes the query like this:

>   SELECT * FROM (a,b) JOIN c USING (id);

>I don't know which is correct.  Perhaps the result is undefined.

>Note that both MySQL and SQLite do allow you to use parentheses, as
>shown in my examples, to define the order of evaluation.  PostgreSQL
>does not, sadly.

>MS-SQL does not (as far as I can tell) support the USING syntax on a
>join.

Aha!  So as far as SQLite is concerned the syntax "... JOIN  USING ()" is 
effectively binding the using expression for the nested nested loop descent 
into into table  only and does not bind against the immediately preceeding 
LHS JOIN table.  

By adding some indexes and order by that cause the nesting order to change it 
appear that "a, b join c using (id)" is always processed as selecting the first 
lexically named id column irrepective of nesting order (that is "a, b join c 
using (id)" always becomes "a, b, c where a.id == c.id" and that "b, a join c 
using (id)" always becomes "a, b, c where b.id == c.id" even when the optimizer 
chooses to re-arrange the nesting order (such as by additional indexes and 
order by's)).

Since the "id" column to use is ambiguous for descent into "c" should not an 
"ambiguous column name" error be thrown?  The ambiguity only does not exist if 
ALL columns named "id" (for all tables that could be in an outer loop 
respective to "c") are constrained equal -- that is a,b,c using (id) -> a, b, c 
where a.id == b.id and a.id == c.id and b.id == c.id.

Since "natural join" devolves into a using, does not the same problem exist 
there as well?

Since any change is likely to have an effect on already existing and functional 
applications, could the behaviour be documented somewhere perhaps?

-- 
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? SQLite command-line result is different from Perl DBI::Sqlite result

[Amer Neely]

> On 1/5/20, Keith Medcalf  wrote:
>>
>> Hrm.  Inconsistent/incorrect results.  Consider:
>>
>> create table a(id integer primary key, a);
>> insert into a values (1,1), (2,1), (3,1);
>> create table b(id integer primary key, b);
>> insert into b values (1,2), (3,2), (4,2);
>> create table c(id integer primary key, c);
>> insert into c values (1,3), (4,3), (5,3);
>>
>> select * from a, b, c using (id); -- very strange result
>>
>> id  a   id  b   c
>> --  --  --  --  --
>> 1   1   1   2   3
>> 1   1   3   2   3
>> 1   1   4   2   3
> 
> PostgreSQL and MySQL process the query as follows:
> 
>SELECT * FROM a, (b JOIN c USING(id));
> 
> SQLite processes the query like this:
> 
>SELECT * FROM (a,b) JOIN c USING (id);
> 
> I don't know which is correct.  Perhaps the result is undefined.
> 
> Note that both MySQL and SQLite do allow you to use parentheses, as
> shown in my examples, to define the order of evaluation.  PostgreSQL
> does not, sadly.
> 
> MS-SQL does not (as far as I can tell) support the USING syntax on a join.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org

Ahh. More to learn. Thank you for the use of parentheses, I will have to
check my queries for that.

i did manage to get the query working by grouping on artists.artistid.
-- 
Amer Neely
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug? SQLite command-line result is different from Perl DBI::Sqlite result

[Richard Hipp]

On 1/5/20, Richard Hipp  wrote:
>
> Note that both MySQL and SQLite do allow you to use parentheses, as
> shown in my examples, to define the order of evaluation.  PostgreSQL
> does not, sadly.
>

Apparently, in PostgreSQL you have to say:

   SELECT * FROM (SELECT * FROM a, b) AS x JOIN c USING(id);

-- 
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? SQLite command-line result is different from Perl DBI::Sqlite result

[Richard Hipp]

On 1/5/20, Keith Medcalf  wrote:
>
> Hrm.  Inconsistent/incorrect results.  Consider:
>
> create table a(id integer primary key, a);
> insert into a values (1,1), (2,1), (3,1);
> create table b(id integer primary key, b);
> insert into b values (1,2), (3,2), (4,2);
> create table c(id integer primary key, c);
> insert into c values (1,3), (4,3), (5,3);
>
> select * from a, b, c using (id); -- very strange result
>
> id  a   id  b   c
> --  --  --  --  --
> 1   1   1   2   3
> 1   1   3   2   3
> 1   1   4   2   3

PostgreSQL and MySQL process the query as follows:

   SELECT * FROM a, (b JOIN c USING(id));

SQLite processes the query like this:

   SELECT * FROM (a,b) JOIN c USING (id);

I don't know which is correct.  Perhaps the result is undefined.

Note that both MySQL and SQLite do allow you to use parentheses, as
shown in my examples, to define the order of evaluation.  PostgreSQL
does not, sadly.

MS-SQL does not (as far as I can tell) support the USING syntax on a join.

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

[Jose Isaias Cabrera]

Bigthing Do, on Friday, December 27, 2019 01:56 PM, wrote...
>
> Dear sqlite developers:
>
> We met an accidental crash in sqlite with the following sample:
>
> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1
> ) SELECT col2 FROM table1 ORDER BY 1 ;
> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY
> col1 DESC ) FROM table1 ;
>
>
> We are using release version of sqlite: `SQLite version 3.30.1 2019-10-10
> 20:19:45`

Also with 3.30.0...

16:41:27.70>sqlite3
SQLite version 3.30.0 2019-10-04 15:03:17
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM 
table1 ) SELECT col2 FROM table1 ORDER BY 1 ;
sqlite> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER 
BY col1 DESC ) FROM table1 ;

16:42:07.53>

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

Re: [sqlite] Bug Report

[Bigthing Do]

Hi,

We tried debugging a little bit with the core dump, it crashes with a null 
reference actually:

`
Program received signal SIGSEGV, Segmentation fault.
[--registers---]
RAX: 0x74 ('t')
RBX: 0x782550 --> 0x76b088 --> 0x1
RCX: 0x61 ('a')
RDX: 0x0
RSI: 0x0
RDI: 0x782098 --> 0x31656c626174 ('table1')
RBP: 0x782548 --> 0x10001
RSP: 0x7fffb6b0 --> 0x78d1b0 --> 0x78d1e8 --> 0x50804496
RIP: 0x4b4237 (:movzx  ecx,BYTE PTR [rdx+rsi*1])
R8 : 0x77d0e8 --> 0x1
R9 : 0x0
R10: 0x77d0f8 --> 0x0
R11: 0x0
R12: 0x1
R13: 0x7fffc680 --> 0x76a9b8 --> 0x73c300 --> 0x780003
R14: 0x7fffc680 --> 0x76a9b8 --> 0x73c300 --> 0x780003
R15: 0x0
EFLAGS: 0x10246 (carry PARITY adjust ZERO sign trap INTERRUPT direction 
overflow)
[-code-]
   0x4b422d :   jne0x4b4270 
   0x4b422f :   addrsi,0x1
   0x4b4233 :   movzx  eax,BYTE PTR [rdi+rsi*1]
=> 0x4b4237 :   movzx  ecx,BYTE PTR [rdx+rsi*1]
`

We got the same result if we debug with address sanitizer, not an out of memory 
error.


Thanks,
Ming Jia

> On Dec 27, 2019, at 2:56 PM, Keith Medcalf  wrote:
> 
> 
> On Friday, 27 December, 2019 12:50, Igor Korot  wrote:
> 
>> On Fri, Dec 27, 2019 at 12:57 PM Bigthing Do  wrote:
> 
>>> We met an accidental crash in sqlite with the following sample:
> 
>>> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 ) 
>>> SELECT col2 FROM table1 ORDER BY 1 ;
>>> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY 
>>> col1 DESC ) FROM table1 ;
> 
>> Could you please provide the schema for table1?
> 
> table1 is a circular view ... that is table1 is a view that tries to select 
> from table1 which is a view which selects from table1 which is a view which 
> selects from table1 ... until eventually all memory and stack is consumed and 
> sqlite crashes.
> 
> -- 
> 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

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

Re: [sqlite] Bug Report

[Keith Medcalf]

On Friday, 27 December, 2019 12:50, Igor Korot  wrote:

>On Fri, Dec 27, 2019 at 12:57 PM Bigthing Do  wrote:

>> We met an accidental crash in sqlite with the following sample:

>> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 ) 
>> SELECT col2 FROM table1 ORDER BY 1 ;
>> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY 
>> col1 DESC ) FROM table1 ;

>Could you please provide the schema for table1?

table1 is a circular view ... that is table1 is a view that tries to select 
from table1 which is a view which selects from table1 which is a view which 
selects from table1 ... until eventually all memory and stack is consumed and 
sqlite crashes.

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

[Igor Korot]

Hi,

On Fri, Dec 27, 2019 at 12:57 PM Bigthing Do  wrote:
>
> Dear sqlite developers:
>
> We met an accidental crash in sqlite with the following sample:
>
> CREATE VIEW table1 ( col1 , col2 ) AS WITH aaa AS ( SELECT * FROM table1 ) 
> SELECT col2 FROM table1 ORDER BY 1 ;
> WITH aaa AS ( SELECT * FROM table1 ) SELECT col1 , rank () OVER( ORDER BY 
> col1 DESC ) FROM table1 ;

Could you please provide the schema for table1?

Thank you.

>
>
> We are using release version of sqlite: `SQLite version 3.30.1 2019-10-10 
> 20:19:45`
>
> Thanks
>
> Ming Jia
> ___
> 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: Table contains two records for the same primary key in .dump output but not in SELECT output

[Tim Streater]

On 21 Dec 2019, at 21:42, Michael Walker (barrucadu)  
wrote:

> I'm not sure the attachment to my first email got through ...

Correct. The list strips them.



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

Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Shawn Wagner]

The mailing list strips attachments, btw.

Anyways, looking at that, yeah, they're all text values:

sqlite> SELECT bookIsbn, typeof(bookIsbn) FROM books WHERE bookAuthor LIKE
'%Ambrose%';
bookIsbntypeof(bookIsbn)
--  
0486280381  text
9781496030  text
9781496030  text

There are other problems with the database too:

sqlite> PRAGMA integrity_check;
integrity_check
---
row 649 missing from index sqlite_autoindex_books_1
row 659 missing from index sqlite_autoindex_books_1
row 665 missing from index sqlite_autoindex_books_1
row 667 missing from index sqlite_autoindex_books_1
row 674 missing from index sqlite_autoindex_books_1
row 676 missing from index sqlite_autoindex_books_1

I'd start going through https://www.sqlite.org/howtocorrupt.html and trying
to figure out if anything there might have happened.


On Sat, Dec 21, 2019 at 1:43 PM Michael Walker (barrucadu) <
m...@barrucadu.co.uk> wrote:

> Hi Shawn,
>
> Thanks for your response.  Though that doesn't seem to be the case:
>
> sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn =
> "9781496030825";
> 9781496030825|text
> sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn =
> "9780099477310";
> 9780099477310|text
>
> The column is a VARCHAR:
>
> CREATE TABLE `books` (
>`bookIsbn` VARCHAR NOT NULL PRIMARY KEY,
>`bookTitle` VARCHAR NOT NULL,
>`bookSubtitle` VARCHAR NOT NULL,
>`bookCover` VARCHAR NULL,
>`bookVolume` VARCHAR NOT NULL,
>`bookFascicle` VARCHAR NOT NULL,
>`bookVoltitle` VARCHAR NOT NULL,
>`bookAuthor` VARCHAR NOT NULL,
>`bookTranslator` VARCHAR NULL,
>`bookEditor` VARCHAR NULL,
>`bookSorting` VARCHAR NULL,
>`bookRead` BOOLEAN NOT NULL,
>`bookLastRead` TIMESTAMP NULL,
>`bookNowReading` BOOLEAN NOT NULL,
>`bookLocation` VARCHAR NOT NULL,
>`bookBorrower` VARCHAR NOT NULL,
>`bookCategoryCode` VARCHAR NOT NULL,
>FOREIGN KEY(`bookCategoryCode`) REFERENCES
> `book_categories`(`categoryCode`)
> );
>
> I'm not sure the attachment to my first email got through, so here's the
> database:
> https://misc.barrucadu.co.uk/forever/82e5584a-e4a8-4804-8abe-8f00be73f725/bookdb.sqlite
>
>
> ‐‐‐ Original Message ‐‐‐
> On Saturday, 21 December 2019 21:37, Shawn Wagner 
> wrote:
>
> > Without seeing your table definition, this is just a guess, but maybe the
> > duplicate keys are stored as different types, with the primary key column
> > having an affinity that doesn't force one particular storage class:
> >
> > sqlite> CREATE TABLE test(id PRIMARY KEY);
> > sqlite> INSERT INTO test VALUES('12345');
> > sqlite> INSERT INTO test VALUES(12345);
> > sqlite> SELECT id, typeof(id) FROM test;
> > id typeof(id)
> >
> > 12345 text
> > 12345 integer
> > sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
> > id typeof(id)
> >
> > 12345 text
> >
> > On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
> > m...@barrucadu.co.uk> wrote:
> >
> > > Hi,
> > > I've somehow ended up with a table which contains two records for the
> same
> > > primary key - well actually I've got two primary keys like that, so I
> have
> > > four records with two primary keys between them.
> > > I've been unable to reproduce this from a clean database, so I attach
> my
> > > database file to this email.
> > > Here are some oddities:
> > >
> > > $ sqlite3 bookdb.sqlite
> > > SQLite version 3.28.0 2019-04-16 19:49:53
> > > Enter ".help" for usage hints.
> > > sqlite> select * from books where bookIsbn = "9781496030825";
> > > 9781496030825|Can Such Things Be?||9781496030825.jpgBierce,
> > > Ambrose0||0|London||F
> > > sqlite> select * from books where bookIsbn = "9780099477310";
> > > 9780099477310|Catch-22||9780099477310.jpgHeller,
> > > Joseph0||0|London||F
> > > sqlite> .output books_issue
> > > sqlite> .dump books
> > > sqlite> .quit
> > >
> > > $ grep "9781496030825" < books_issue
> > > INSERT INTO books VALUES('9781496030825','Can Such Things
> > > Be?','','9781496030825.jpg','','','','Bierce,
> > > Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > > INSERT INTO books VALUES('9781496030825','Can Such Things
> > > Be?','','9781496030825.jpg','','','','Bierce,
> > > Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > >
> > > $ grep "9780099477310" < books_issue
> > > INSERT INTO books
> > >
>  VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> > > Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > > INSERT INTO books
> > >
>  VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> > > Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > >
> > > $ sqlite3 bookdb.sqlite
> > > SQLite version 3.28.0 2019-04-16 19:49:53
> > > Enter ".help" for usage 

Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Michael Walker (barrucadu)]

Hi Shawn,

Thanks for your response.  Though that doesn't seem to be the case:

sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn = 
"9781496030825";
9781496030825|text
sqlite> select bookIsbn, typeof(bookIsbn) from books where bookIsbn = 
"9780099477310";
9780099477310|text

The column is a VARCHAR:

CREATE TABLE `books` (
   `bookIsbn` VARCHAR NOT NULL PRIMARY KEY,
   `bookTitle` VARCHAR NOT NULL,
   `bookSubtitle` VARCHAR NOT NULL,
   `bookCover` VARCHAR NULL,
   `bookVolume` VARCHAR NOT NULL,
   `bookFascicle` VARCHAR NOT NULL,
   `bookVoltitle` VARCHAR NOT NULL,
   `bookAuthor` VARCHAR NOT NULL,
   `bookTranslator` VARCHAR NULL,
   `bookEditor` VARCHAR NULL,
   `bookSorting` VARCHAR NULL,
   `bookRead` BOOLEAN NOT NULL,
   `bookLastRead` TIMESTAMP NULL,
   `bookNowReading` BOOLEAN NOT NULL,
   `bookLocation` VARCHAR NOT NULL,
   `bookBorrower` VARCHAR NOT NULL,
   `bookCategoryCode` VARCHAR NOT NULL,
   FOREIGN KEY(`bookCategoryCode`) REFERENCES 
`book_categories`(`categoryCode`)
);

I'm not sure the attachment to my first email got through, so here's the 
database: 
https://misc.barrucadu.co.uk/forever/82e5584a-e4a8-4804-8abe-8f00be73f725/bookdb.sqlite


‐‐‐ Original Message ‐‐‐
On Saturday, 21 December 2019 21:37, Shawn Wagner  
wrote:

> Without seeing your table definition, this is just a guess, but maybe the
> duplicate keys are stored as different types, with the primary key column
> having an affinity that doesn't force one particular storage class:
>
> sqlite> CREATE TABLE test(id PRIMARY KEY);
> sqlite> INSERT INTO test VALUES('12345');
> sqlite> INSERT INTO test VALUES(12345);
> sqlite> SELECT id, typeof(id) FROM test;
> id typeof(id)
>
> 12345 text
> 12345 integer
> sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
> id typeof(id)
>
> 12345 text
>
> On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
> m...@barrucadu.co.uk> wrote:
>
> > Hi,
> > I've somehow ended up with a table which contains two records for the same
> > primary key - well actually I've got two primary keys like that, so I have
> > four records with two primary keys between them.
> > I've been unable to reproduce this from a clean database, so I attach my
> > database file to this email.
> > Here are some oddities:
> >
> > $ sqlite3 bookdb.sqlite
> > SQLite version 3.28.0 2019-04-16 19:49:53
> > Enter ".help" for usage hints.
> > sqlite> select * from books where bookIsbn = "9781496030825";
> > 9781496030825|Can Such Things Be?||9781496030825.jpgBierce,
> > Ambrose0||0|London||F
> > sqlite> select * from books where bookIsbn = "9780099477310";
> > 9780099477310|Catch-22||9780099477310.jpgHeller,
> > Joseph0||0|London||F
> > sqlite> .output books_issue
> > sqlite> .dump books
> > sqlite> .quit
> >
> > $ grep "9781496030825" < books_issue
> > INSERT INTO books VALUES('9781496030825','Can Such Things
> > Be?','','9781496030825.jpg','','','','Bierce,
> > Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > INSERT INTO books VALUES('9781496030825','Can Such Things
> > Be?','','9781496030825.jpg','','','','Bierce,
> > Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> >
> > $ grep "9780099477310" < books_issue
> > INSERT INTO books
> > 
> > VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> > Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> > INSERT INTO books
> > 
> > VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> > Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> >
> > $ sqlite3 bookdb.sqlite
> > SQLite version 3.28.0 2019-04-16 19:49:53
> > Enter ".help" for usage hints.
> > sqlite> drop table books;
> > sqlite>
> >
> > $ sqlite3 bookdb.sqlite < books_issue
> > Error: near line 697: UNIQUE constraint failed: books.bookIsbn
> > Error: near line 698: UNIQUE constraint failed: books.bookIsbn
> >
> >
> > Updating either affected record results in the second copy in the .dump
> > output being updated, the first copy has the original state.
> > The table has always had a primary key constraint, so I'm not sure how
> > it's ended up in its current state. However, even if there were not a
> > primary key constraint, there do seem to be two very real bugs here: SELECT
> > gives different results to .dump, and .dump is producing output which can't
> > be restored.
> > I'm not sure if you'll be able to make anything of this, as I say I
> > haven't been able to reproduce it from a blank database, but I figure
> > you'll be better at debugging this than me.
> > Thanks
> > --
> > Michael Walker (http://www.barrucadu.co.uk)
> >
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> sqlite-users mailing list
> 

Re: [sqlite] Bug: Table contains two records for the same primary key in .dump output but not in SELECT output

[Shawn Wagner]

Without seeing your table definition, this is just a guess, but maybe the
duplicate keys are stored as different types, with the primary key column
having an affinity that doesn't force one particular storage class:

sqlite> CREATE TABLE test(id PRIMARY KEY);
sqlite> INSERT INTO test VALUES('12345');
sqlite> INSERT INTO test VALUES(12345);
sqlite> SELECT id, typeof(id) FROM test;
id  typeof(id)
--  --
12345   text
12345   integer
sqlite> SELECT id, typeof(id) FROM test WHERE id = '12345';
id  typeof(id)
--  --
12345   text



On Sat, Dec 21, 2019 at 1:26 PM Michael Walker (barrucadu) <
m...@barrucadu.co.uk> wrote:

> Hi,
>
> I've somehow ended up with a table which contains two records for the same
> primary key - well actually I've got two primary keys like that, so I have
> four records with two primary keys between them.
>
> I've been unable to reproduce this from a clean database, so I attach my
> database file to this email.
>
> Here are some oddities:
>
> ```
> $ sqlite3 bookdb.sqlite
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> sqlite> select * from books where bookIsbn = "9781496030825";
> 9781496030825|Can Such Things Be?||9781496030825.jpgBierce,
> Ambrose0||0|London||F
> sqlite> select * from books where bookIsbn = "9780099477310";
> 9780099477310|Catch-22||9780099477310.jpgHeller,
> Joseph0||0|London||F
> sqlite> .output books_issue
> sqlite> .dump books
> sqlite> .quit
>
> $ grep "9781496030825" < books_issue
> INSERT INTO books VALUES('9781496030825','Can Such Things
> Be?','','9781496030825.jpg','','','','Bierce,
> Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
> INSERT INTO books VALUES('9781496030825','Can Such Things
> Be?','','9781496030825.jpg','','','','Bierce,
> Ambrose',NULL,NULL,NULL,0,NULL,0,'London','','F');
>
> $ grep "9780099477310" < books_issue
> INSERT INTO books
> VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
> INSERT INTO books
> VALUES('9780099477310','Catch-22','','9780099477310.jpg','','','','Heller,
> Joseph',NULL,NULL,NULL,0,NULL,0,'London','','F');
>
> $ sqlite3 bookdb.sqlite
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> sqlite> drop table books;
> sqlite>
>
> $ sqlite3 bookdb.sqlite < books_issue
> Error: near line 697: UNIQUE constraint failed: books.bookIsbn
> Error: near line 698: UNIQUE constraint failed: books.bookIsbn
> ```
>
> Updating either affected record results in the second copy in the .dump
> output being updated, the first copy has the original state.
>
> The table has always had a primary key constraint, so I'm not sure how
> it's ended up in its current state.  However, even if there were not a
> primary key constraint, there do seem to be two very real bugs here: SELECT
> gives different results to .dump, and .dump is producing output which can't
> be restored.
>
> I'm not sure if you'll be able to make anything of this, as I say I
> haven't been able to reproduce it from a blank database, but I figure
> you'll be better at debugging this than me.
>
> Thanks
>
> --
> Michael Walker (http://www.barrucadu.co.uk)
> ___
> 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 report: cppcheck memory leak

[Simon Slavin]

On 2 Dec 2019, at 1:16am, Richard Hipp  wrote:

> Telling us that the
> "return" from malloc() is a memory leak is not helpful information,
> even if it were true.

Oh, someone needs to write a story about a manager who doesn't understand 
computers but relies on test suites, and programmers trying to convince them 
that there are good reasons to fail tests.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: cppcheck memory leak

[Richard Hipp]

On 12/1/19, David Brouwer  wrote:
> While playing around with static code analysis with cppcheck, I ran into
> the error "[modules/sqlite3_omit.c:22845]: (error) Memory leak: p". I can't
> tell whether it's significant or not, but I figured I'd report it anyway.

Thanks for taking the time to report it.  But this is not a helpful bug report.

(1) Static analyzers are notorious for giving false-positive
indications in SQLite.  Furthermore, SQLite is very intensely tested
for memory leaks, and memory leaks are rare.  The chance of a static
analyzer finding a memory leak, even if one were in the code, is very
small.  Given the high proportion of false-positives coming from
static analyzers, one can safely disbelieve any reports of memory
leaks that lack corroborating evidence.

(2) The line number listed is the "return" from an internal SQLite
routine that is a wrapper around malloc().  Telling us that the
"return" from malloc() is a memory leak is not helpful information,
even if it were true.

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

[Simon Slavin]

CVE will not record this bug if it doesn't affect a /released/ version of any 
product.  One hopes that none of the products which incorporate SQLite would 
incorporate a version of SQLite which never received a release number.

In other words, the reporters told the developer team before the bug became a 
problem.  Very good.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report

[Kees Nuyt]

On Thu, 21 Nov 2019 21:02:57 +, Jose Isaias Cabrera wrote:

>Kees Nuyt, on Thursday, November 21, 2019 03:48 PM, wrote...
[...]
>>
>> I see no CVE entered by the OP, but maybe I missed something.
>
> Yes, you are right.  After pasting it, I went through the top 5
> and none of these aren't/weren't the one. Apologies. 
> I thought that by searching on sqlite the top 5 or so
> would be the one that was just opened, but for some reason,
> it was not.  Sorry about that.  Fast fingers Jose.

No problem!
We'll wait for more input from the OP.

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

Re: [sqlite] Bug report

[Jose Isaias Cabrera]

Kees Nuyt, on Thursday, November 21, 2019 03:48 PM, wrote...
>
>
> Thanks, Jose.
>
> I see no CVE entered by the OP, but maybe I missed something.

Yes, you are right.  After pasting it, I went through the top 5 and none of 
these aren't/weren't the one. Apologies.  I thought that by searching on sqlite 
the top 5 or so would be the one that was just opened, but for some reason, it 
was not.  Sorry about that.  Fast fingers Jose.

josé

> A quick look to your list :
>
> > NameDescription
> > CVE-2019-9937, on
> > In SQLite 3.27.2, interleaving reads and writes in a single transaction with
> > an fts5 virtual table will lead to a NULL Pointer Dereference in
> > fts5ChunkIterate in sqlite3.c. This is related to ext/fts5/fts5_hash.c and
> > ext/fts5/fts5_index.c.
>
> Resolved 2019-03-18
>
>
> > CVE-2019-9936, on
> > In SQLite 3.27.2, running fts5 prefix queries inside a transaction could
> > trigger a heap-based buffer over-read in fts5HashEntrySort in sqlite3.c, 
> > which
> > may lead to an information leak. This is related to ext/fts5/fts5_hash.c.
>
> Resolved 2019-03-18
>
>
> > CVE-2019-5827, on
> > Integer overflow in SQLite via WebSQL in Google Chrome prior to 
> > 74.0.3729.131
> > allowed a remote attacker to potentially exploit heap corruption via a 
> > crafted
> > HTML page.
>
> Resolved 2019-04-13
>
>
> > CVE-2019-3784, on
> > Cloud Foundry Stratos, versions prior to 2.3.0, contains an insecure session
> > that can be spoofed. When deployed on cloud foundry with multiple instances
> > using the default embedded SQLite database, a remote authenticated malicious
> > user can switch sessions to another user with the same session id.
>
> Application error
>
>
> > CVE-2019-1616 
> > 8
> > In SQLite through 3.29.0, whereLoopAddBtreeIndex in sqlite3.c can crash a
> > browser or other application because of missing validation of a sqlite_stat1
> > sz field, aka a "severe division by zero in the query planner."
>
> Resolved 2019-08-15
>
>
> > CVE-2019-1075 
> > 2
> > Sequelize, all versions prior to version 4.44.3 and 5.15.1, is vulnerable to
> > SQL Injection due to sequelize.json() helper function not escaping values
> > properly when formatting sub paths for JSON queries for MySQL, MariaDB and
> > SQLite.
>
> Application error
>
>
> > CVE-2018-8740, on
> > In SQLite through 3.22.0, databases whose schema is corrupted using a CREATE
> > TABLE AS statement could cause a NULL pointer dereference, related to 
> > build.c
> > and prepare.c.
>
> Resolved 2018-03-16
>
>
> > CVE-2018-7774, on
> > The vulnerability exists within processing of localize.php in Schneider
> > Electric U.motion Builder software versions prior to v1.3.4. The underlying
> > SQLite database query is subject to SQL injection on the username input
> > parameter.
>
> Application error
>
>
> --
> Regards,
> Kees Nuyt
> ___
> 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 report

[Kees Nuyt]

Thanks, Jose.

I see no CVE entered by the OP, but maybe I missed something.

A quick look to your list :

> NameDescription
> CVE-2019-9937 
> In SQLite 3.27.2, interleaving reads and writes in a single transaction with
> an fts5 virtual table will lead to a NULL Pointer Dereference in
> fts5ChunkIterate in sqlite3.c. This is related to ext/fts5/fts5_hash.c and
> ext/fts5/fts5_index.c.

Resolved 2019-03-18


> CVE-2019-9936 
> In SQLite 3.27.2, running fts5 prefix queries inside a transaction could
> trigger a heap-based buffer over-read in fts5HashEntrySort in sqlite3.c, which
> may lead to an information leak. This is related to ext/fts5/fts5_hash.c.

Resolved 2019-03-18


> CVE-2019-5827 
> Integer overflow in SQLite via WebSQL in Google Chrome prior to 74.0.3729.131
> allowed a remote attacker to potentially exploit heap corruption via a crafted
> HTML page.

Resolved 2019-04-13


> CVE-2019-3784 
> Cloud Foundry Stratos, versions prior to 2.3.0, contains an insecure session
> that can be spoofed. When deployed on cloud foundry with multiple instances
> using the default embedded SQLite database, a remote authenticated malicious
> user can switch sessions to another user with the same session id.

Application error


> CVE-2019-1616 8
> In SQLite through 3.29.0, whereLoopAddBtreeIndex in sqlite3.c can crash a
> browser or other application because of missing validation of a sqlite_stat1
> sz field, aka a "severe division by zero in the query planner."

Resolved 2019-08-15


> CVE-2019-1075 2
> Sequelize, all versions prior to version 4.44.3 and 5.15.1, is vulnerable to
> SQL Injection due to sequelize.json() helper function not escaping values
> properly when formatting sub paths for JSON queries for MySQL, MariaDB and
> SQLite.

Application error


> CVE-2018-8740 
> In SQLite through 3.22.0, databases whose schema is corrupted using a CREATE
> TABLE AS statement could cause a NULL pointer dereference, related to build.c
> and prepare.c.

Resolved 2018-03-16


> CVE-2018-7774 
> The vulnerability exists within processing of localize.php in Schneider
> Electric U.motion Builder software versions prior to v1.3.4. The underlying
> SQLite database query is subject to SQL injection on the username input
> parameter.

Application error


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

Re: [sqlite] Bug report

[Richard Hipp]

On 11/19/19, Yongheng Chen  wrote:
> Hi,
>
> This is Yongheng Chen from Gatech and Rui Zhong from PSU. We found 7 crashes
> for sqlite of  the newest commit 3842e8f166e23a1ed6e6094105e7a23502d414da.
> We have attached the samples that crash sqlite in the email. FYI, we have
> also reported the bugs for CVE at cve.mitre.org .

There were just two bugs, both related to the new (unreleased)
generated column feature.  Both have now been fixed on trunk.  Thank
you for the bug reports.

In as much as these problems have never appeared in a released version
of SQLite, I think a CVE would be inappropriate.  But I don't really
understand CVEs so perhaps I am wrong.

Please consider following SQLite development on the official
source-code repository.  You can see the latest changes here:

https://sqlite.org/src/timeline

If you click on any of the check-in hashes, that will take you to a
page that contains links to download tarballs and/or ZIP archives of
the latest code.  Or you can use Fossil to clone the repository.  See
https://www.sqlite.org/getthecode.html for additional information
about how to get the official SQLite source code.

The filenames of your test cases suggest that they were generated by
AFL.  How did you find these issues?  Do you have new and enhanced AFL
fuzzer, perhaps one in which you have replaced the default mutator
with an SQL-language generator?  Can you tell us more about your new
fuzzer?

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

[Jose Isaias Cabrera]

NameDescription
CVE-2019-9937<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-9937>  
In SQLite 3.27.2, interleaving reads and writes in a single transaction with an 
fts5 virtual table will lead to a NULL Pointer Dereference in fts5ChunkIterate 
in sqlite3.c. This is related to ext/fts5/fts5_hash.c and ext/fts5/fts5_index.c.
CVE-2019-9936<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-9936>  
In SQLite 3.27.2, running fts5 prefix queries inside a transaction could 
trigger a heap-based buffer over-read in fts5HashEntrySort in sqlite3.c, which 
may lead to an information leak. This is related to ext/fts5/fts5_hash.c.
CVE-2019-5827<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-5827>  
Integer overflow in SQLite via WebSQL in Google Chrome prior to 74.0.3729.131 
allowed a remote attacker to potentially exploit heap corruption via a crafted 
HTML page.
CVE-2019-3784<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-3784>  
Cloud Foundry Stratos, versions prior to 2.3.0, contains an insecure session 
that can be spoofed. When deployed on cloud foundry with multiple instances 
using the default embedded SQLite database, a remote authenticated malicious 
user can switch sessions to another user with the same session id.
CVE-2019-16168<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-16168>
In SQLite through 3.29.0, whereLoopAddBtreeIndex in sqlite3.c can crash a 
browser or other application because of missing validation of a sqlite_stat1 sz 
field, aka a "severe division by zero in the query planner."
CVE-2019-10752<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2019-10752>
Sequelize, all versions prior to version 4.44.3 and 5.15.1, is vulnerable to 
SQL Injection due to sequelize.json() helper function not escaping values 
properly when formatting sub paths for JSON queries for MySQL, MariaDB and 
SQLite.
CVE-2018-8740<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2018-8740>  
In SQLite through 3.22.0, databases whose schema is corrupted using a CREATE 
TABLE AS statement could cause a NULL pointer dereference, related to build.c 
and prepare.c.
CVE-2018-7774<http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2018-7774>  
The vulnerability exists within processing of localize.php in Schneider 
Electric U.motion Builder software versions prior to v1.3.4. The underlying 
SQLite database query is subject to SQL injection on the username input 
parameter.



From: sqlite-users  on behalf of 
Kees Nuyt 
Sent: Thursday, November 21, 2019 09:51 AM
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] Bug report

On Tue, 19 Nov 2019 00:19:13 -0500, you wrote:

> Hi,
>
> This is Yongheng Chen from Gatech and Rui Zhong from PSU.
> We found 7 crashes for sqlite of  the newest commit
> 3842e8f166e23a1ed6e6094105e7a23502d414da.
> We have attached the samples that crash sqlite in the email.

The mailing list strips attachemnts. Please insert them in the body text of your
message, or mail them to Richard Hipp.

> FYI, we have also reported the bugs for CVE
> at cve.mitre.org <http://cve.mitre.org/>.

Can you tell us the CVE nunber?


--
Regards,

Kees Nuyt

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

[Kees Nuyt]

On Tue, 19 Nov 2019 00:19:13 -0500, you wrote:

> Hi,
>
> This is Yongheng Chen from Gatech and Rui Zhong from PSU.
> We found 7 crashes for sqlite of  the newest commit
> 3842e8f166e23a1ed6e6094105e7a23502d414da. 
> We have attached the samples that crash sqlite in the email. 

The mailing list strips attachemnts. Please insert them in the body text of your
message, or mail them to Richard Hipp.

> FYI, we have also reported the bugs for CVE
> at cve.mitre.org . 

Can you tell us the CVE nunber?


-- 
Regards,

Kees Nuyt

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

Re: [sqlite] BUG and WORKAROUND sqlite3 shell .parameter command evaluating arguments when it shouldn't.

[Keith Medcalf]

I think this is a bug.  However, looking at the code the way to achieve that is 
to surround the string in double quotes which will cause exactly what appears 
between the double-quotes to be stored.  I think it is because of the way the 
parsing and mprintf function works ...

sqlite> .param init
sqlite> .parameter set :date "'2019-11-15'"
sqlite> .param list
:date '2019-11-15'
sqlite> select :date;
2019-11-15
sqlite> select datetime(:date);
2019-11-15 00:00:00


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

>-Original Message-
>From: sqlite-users  On
>Behalf Of Shawn Wagner
>Sent: Friday, 15 November, 2019 10:15
>To: SQLite mailing list 
>Subject: [sqlite] sqlite3 shell .parameter command evaluating arguments
>when it shouldn't.
>
>Consider:
>
>sqlite> .parameter init
>sqlite> .parameter set :date '2019-11-15'
>sqlite> .parameter list
>:date 1993
>
>How do I make it treat the value argument as a string and not as a
>numeric
>expression that gets evaluated?
>___
>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 submission: left join filter on negated expression including NOTNULL

[Keith Medcalf]

On Friday, 1 November, 2019 10:20, Simon Slavin  wrote:

>On 1 Nov 2019, at 4:17pm, Simon Slavin  wrote:

>WHERE (c1 IS NULL) OR (C1 != 2)

> which could quite reasonably return rows.  However, the NULL possibility
> may be redundant.  I can't tell without tests.

The expression NOT (c1 IS NOT NULL AND c1 == 2)
is equivalent to c1 IS NULL OR c1 != 2
is equivalent to (c1 IS NOT 2)
or the original proper expression NOT (C1 IS 2)

and arises from the use of the '==' and '!=' rather that IS and IS NOT, and 
generating work-arounds to handle NULLs.  Work-arounds for handling NULLs are 
only required for non-equality tests since there is no standard operators 
handling nulls for the other comparison operators > < >= <=

-- 
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 submission: left join filter on negated expression including NOTNULL

[Keith Medcalf]

On Friday, 1 November, 2019 09:12, And Clover  wrote:

> CREATE TABLE t0 (c0);
> CREATE TABLE t1 (c1);
> INSERT INTO t0 VALUES (1);
> SELECT c0
> FROM t0 LEFT JOIN t1 ON c1=c0
> WHERE NOT (c1 IS NOT NULL AND c1=2);

>Expected result: (1)
>Actual result: no rows returned
>
>This appears to be a regression in 3.30; 3.29 and earlier give the
>expected result.

SQLite version 3.31.0 2019-11-01 16:38:18
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>  CREATE TABLE t0 (c0);
sqlite>  CREATE TABLE t1 (c1);
sqlite>  INSERT INTO t0 VALUES (1);
sqlite>  SELECT c0
   ...>  FROM t0 LEFT JOIN t1 ON c1=c0
   ...>  WHERE NOT (c1 IS NOT NULL AND c1=2);
1

Appears to be fixed in the current tip ...

-- 
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 submission: left join filter on negated expression including NOTNULL

[And Clover]

On 01/11/2019 16:20, Simon Slavin wrote:

It is actually
WHERE (c1 IS NULL) OR (C1 != 2)
which could quite reasonably return rows.


Yes, and with this OR filter the quoted example does indeed return rows. 
The version with:


WHERE NOT (c1 IS NOT NULL AND c1=2)

*should* be equivalent to your version, but in 3.30 does not return the 
NULL values.


> You can't possibly mean to do that WHERE clause in production code

I might not spell it like that myself, but a code generator would do it 
(and much worse!). This example was simplified from a query generated by 
a Django ORM queryset using `.exclude(nullable_joined_table__column=1)`, 
for instance.


But yeah, expressions written in a less-than-tasteful style should 
probably still work as specified by SQL92. ;-)


--
And Clover
mailto:a...@doxdesk.com
https://www.doxdesk.com/
gtalk:chat?jid=bobi...@gmail.com

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

Re: [sqlite] Bug submission: left join filter on negated expression including NOTNULL

[Simon Slavin]

On 1 Nov 2019, at 4:17pm, Simon Slavin  wrote:

> This is a cut-down example, right ?  You can't possibly mean to do that WHERE 
> clause in production code.  It amounts to
> 
>WHERE (c1 IS NULL) AND (C1 != 2)

I'm so sorry.  It is actually

WHERE (c1 IS NULL) OR (C1 != 2)

which could quite reasonably return rows.  However, the NULL possibility may be 
redundant.  I can't tell without tests.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug submission: left join filter on negated expression including NOTNULL

[Simon Slavin]

On 1 Nov 2019, at 3:12pm, And Clover  wrote:

>WHERE NOT (c1 IS NOT NULL AND c1=2);
> 
> Expected result: (1)
> Actual result: no rows returned

This is a cut-down example, right ?  You can't possibly mean to do that WHERE 
clause in production code.  It amounts to

WHERE (c1 IS NULL) AND (C1 != 2)

I don't know how SQLite will evaluate that for all cases but I wouldn't be 
surprised to find zero rows returned.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: Infite loop on SELECT with .explain on

[Keith Medcalf]

While on errata for the shell, there is a little cosmetic bugaboo with the 
output of .eqp full:

(1) When .mode col is in effect the "explain" column widths are used, not the 
.width or the defaults used when .eqp full is not in effect.
(2) .head on is ignored -- table output column headers are not output.

Also, if you give .eqp or .explain a nonsense argument they report that the 
argument must be a boolean.  .explain also appears to take auto and .eqp also 
takes more than just a boolean (eg full).

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

>-Original Message-
>From: sqlite-users  On
>Behalf Of Richard Hipp
>Sent: Saturday, 26 October, 2019 10:08
>To: SQLite mailing list 
>Subject: Re: [sqlite] Bug: Infite loop on SELECT with .explain on
>
>Thanks for the report.  This problem is fixed on trunk.
>
>To be clear, this is an issue in the "sqlite3" shell command, not in
>the SQLite core.
>
>Also, it is an issue in the deprecated and undocumented ".explain on"
>command of the shell.  Years ago, it used to be necessary to run
>".explain on" prior to running an EXPLAIN query in order to set up the
>output formatting correctly so that the EXPLAIN output was readable.
>But that setup is now done automatically.  There is no need to run
>".explain on" any more and that dot-command is now deprecated and
>undocumented.  It exists only to prevent legacy scripts from blowing
>up.  Perhaps I should make the ".explain" command into a no-op?
>--
>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] Bug: Infite loop on SELECT with .explain on

[Richard Hipp]

Thanks for the report.  This problem is fixed on trunk.

To be clear, this is an issue in the "sqlite3" shell command, not in
the SQLite core.

Also, it is an issue in the deprecated and undocumented ".explain on"
command of the shell.  Years ago, it used to be necessary to run
".explain on" prior to running an EXPLAIN query in order to set up the
output formatting correctly so that the EXPLAIN output was readable.
But that setup is now done automatically.  There is no need to run
".explain on" any more and that dot-command is now deprecated and
undocumented.  It exists only to prevent legacy scripts from blowing
up.  Perhaps I should make the ".explain" command into a no-op?
-- 
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: WINDOW clause within a CTE

[Dan Kennedy]

On 15/9/62 11:57, Jake Thaw wrote:

The following examples demonstrate a possible bug when using a WINDOW
clause within a CTE:

SQLite version 3.30.0 2019-09-14 16:44:51
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE x AS SELECT 1 a UNION SELECT 2;
sqlite>
sqlite> -- Unexpected result - expect 1,1
sqlite> WITH y AS (
...> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a))
...> SELECT * FROM y;
1
2
sqlite> -- Unexpected result - expected "Error: no such column: fake_column"
sqlite> WITH y AS (
...> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION
BY fake_column))
...> SELECT * FROM y;
1
2



Thanks for tracking down and reporting these. Now fixed here:

  https://sqlite.org/src/info/ca564d4b5b19fe56


sqlite> -- Possible unexpected result - expected "Error: no such
column: fake_column"
sqlite> SELECT 1 WINDOW win AS (PARTITION BY fake_column);
1


I think we'll leave this one as is. SQLite only resolves the references 
in the WINDOW clause if it is used, so this doesn't produce an error. 
There are few other scenarios SQLite does this too. The statement 
"SELECT (0 AND fake_column);", for example.


Dan.



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

Re: [sqlite] Bug: LSM1 lsm1BestIndex() always chooses table scan

[Richard Hipp]

On 8/16/19, James Kafader  wrote:
>
> We think, after trying out a fix that line 845 of lsm_vtab.c contains a
> bug. This line
>   for(i=0; inConstraint && idxNum<16; i++, pConstraint++){
> should perhaps be replaced by this line:
>for(i=0; inConstraint && idxNum>16; i++, pConstraint++){
>

Thanks for debugging this.  We think the actual fix is:

   for(i=0; inConstraint; i++, pConstraint++){

In other words, take out the idxNum term completely.  I'm not sure
what that was about - probably some cruft left over from whatever
virtual table I used as a template when throwing together the LSM1
vtab.

The fix has now been checked into the SQLite source tree.
-- 
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 report: crash when close blob handle after close_v2 db

[Dan Kennedy]

On 14/7/62 17:18, Chaoji Li wrote:

This problem is only present for 3.28+. A sample test case is attached.


Thanks for reporting this. We think it's fixed here:

  https://sqlite.org/src/info/52f463d29407fad6

The mailing list stripped off your test case, so if you could either run 
it with the latest SQLite from fossil or else post it inline here so 
that we can run it, that would be very helpful.


Thanks,

Dan.





Basically, the flow is:

1. Open  in-memory db A (we don't do anything about it).
2. Open db B  from file test.db
3. Create a blob handle from B
4. close_v2 A
5. close_v2 B
6. close blob handle -> Segmentation fault

The problem seems to go away if A is not created.
___
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 when creating a table via select?

[Dominique Devienne]

On Mon, Jul 15, 2019 at 6:01 AM J. King  wrote:

> On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd <
> donald.sheph...@gmail.com> wrote:
> >sqlite> create table x(a int, b text, c real, d blob, e vartext,
> fgarbage);
> >sqlite> pragma table_info(x);
> >0|a|int|0||0
> >1|b|text|0||0
> >2|c|real|0||0
> >3|d|blob|0||0
> >4|e|vartext|0||0
> >5|f|garbage|0||0
> >sqlite> create table y as select * from x;
> >sqlite> pragma table_info(y);
> >0|a|INT|0||0
> >1|b|TEXT|0||0
> >2|c|REAL|0||0
> >3|d||0||0
> >4|e|TEXT|0||0
> >5|f|NUM|0||0
>
> Blobs have no affinity. The result you're seeing is correct, just
> represented in a surprising way.
>

Still. The fact garbage is mapped to NUM, and BLOB to nothing, is at the
very least "surprising",
despite being "correct" as per SQLite's "flexible typing" as DRH puts it.
Definitely worthy of the "new"
quirks.html page though IMHO. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug when creating a table via select?

[J. King]

On July 15, 2019 12:01:00 a.m. EDT, "J. King"  wrote:
>On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd
> wrote:
>>Somewhat bizarrely only "BLOB" affinity doesn't make it from the
>>original
>>table to the new table when using the "select" syntax to create the
>new
>>table.  Even items with aliased affinities (VARTEXT, or something that
>>defaults to NUMERIC) comes across as the base affinity but at least
>>have an
>>affinity.
>>
>>This is simple to reproduce:
>>
>>sqlite> .version
>>SQLite 3.29.0 2019-07-10 17:32:03
>>fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6
>>zlib version 1.2.11
>>gcc-5.2.0
>>sqlite> create table x(a int, b text, c real, d blob, e vartext, f
>>garbage);
>>sqlite> pragma table_info(x);
>>0|a|int|0||0
>>1|b|text|0||0
>>2|c|real|0||0
>>3|d|blob|0||0
>>4|e|vartext|0||0
>>5|f|garbage|0||0
>>sqlite> create table y as select * from x;
>>sqlite> pragma table_info(y);
>>0|a|INT|0||0
>>1|b|TEXT|0||0
>>2|c|REAL|0||0
>>3|d||0||0
>>4|e|TEXT|0||0
>>5|f|NUM|0||0
>>sqlite> select * from sqlite_master;
>>table|x|x|2|CREATE TABLE x(a int, b text, c real, d blob, e vartext, f
>>garbage)
>>table|y|y|3|CREATE TABLE y(
>>  a INT,
>>  b TEXT,
>>  c REAL,
>>  d,
>>  e TEXT,
>>  f NUM
>>)
>>___
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>Blobs have no affinity. The result you're seeing is correct, just
>represented in a surprising way. 
>-- 
>J. King
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

I misspoke. Blobs have an affinity historically called NONE (which is distinct 
from no affinity, but that's not relevant here). Presumably SQLite represents 
the BLOB affinity as null as a means of saying "NONE", again for historical 
reasons. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug when creating a table via select?

[J. King]

On July 14, 2019 11:56:15 p.m. EDT, Donald Shepherd  
wrote:
>Somewhat bizarrely only "BLOB" affinity doesn't make it from the
>original
>table to the new table when using the "select" syntax to create the new
>table.  Even items with aliased affinities (VARTEXT, or something that
>defaults to NUMERIC) comes across as the base affinity but at least
>have an
>affinity.
>
>This is simple to reproduce:
>
>sqlite> .version
>SQLite 3.29.0 2019-07-10 17:32:03
>fc82b73eaac8b36950e527f12c4b5dc1e147e6f4ad2217ae43ad82882a88bfa6
>zlib version 1.2.11
>gcc-5.2.0
>sqlite> create table x(a int, b text, c real, d blob, e vartext, f
>garbage);
>sqlite> pragma table_info(x);
>0|a|int|0||0
>1|b|text|0||0
>2|c|real|0||0
>3|d|blob|0||0
>4|e|vartext|0||0
>5|f|garbage|0||0
>sqlite> create table y as select * from x;
>sqlite> pragma table_info(y);
>0|a|INT|0||0
>1|b|TEXT|0||0
>2|c|REAL|0||0
>3|d||0||0
>4|e|TEXT|0||0
>5|f|NUM|0||0
>sqlite> select * from sqlite_master;
>table|x|x|2|CREATE TABLE x(a int, b text, c real, d blob, e vartext, f
>garbage)
>table|y|y|3|CREATE TABLE y(
>  a INT,
>  b TEXT,
>  c REAL,
>  d,
>  e TEXT,
>  f NUM
>)
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Blobs have no affinity. The result you're seeing is correct, just represented 
in a surprising way. 
-- 
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: crash when close blob handle after close_v2 db

[Simon Slavin]

On 14 Jul 2019, at 11:18am, Chaoji Li  wrote:

> This problem is only present for 3.28+. A sample test case is attached.

Thank you for identifying this behaviour.  I'm sure the development team will 
reply to your post.

Attachments are automatically ignored by the mailing list.  You can include 
your code in your message, or post it on a server and include a pointer.  
However, in this case you have included a good clear description of how to 
reproduce the problem and this should not be necessary.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in all-bnf.html documentation?

[George King]

Fantastic, thank you!


> On Jul 13, 2019, at 1:48 PM, Richard Hipp  wrote:
> 
> On 7/13/19, George King  wrote:
>> Is there any written description of the Sqlite grammar or is it
>> now only described by the images? If nothing else, I'd be interested to peek
>> at any sort of grammar representation in the source code.
> 
> The images are the definitive description of the language.
> 
> The implementation is described by a LALR(1) grammar at
> https://sqlite.org/src/file/src/parse.y if that is of any help to you.
> -- 
> 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 all-bnf.html documentation?

[Richard Hipp]

On 7/13/19, George King  wrote:
> Is there any written description of the Sqlite grammar or is it
> now only described by the images? If nothing else, I'd be interested to peek
> at any sort of grammar representation in the source code.

The images are the definitive description of the language.

The implementation is described by a LALR(1) grammar at
https://sqlite.org/src/file/src/parse.y if that is of any help to you.
-- 
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 all-bnf.html documentation?

[George King]

Thank you. Is there any written description of the Sqlite grammar or is it now 
only described by the images? If nothing else, I'd be interested to peek at any 
sort of grammar representation in the source code.


> On Jul 13, 2019, at 12:28 PM, Richard Hipp  wrote:
> 
> On 7/13/19, George King  wrote:
>> I found
>> the all-bnf.html page after a quick google search.
> 
> That file is obsolete cruft in the Fossil repository that hosts the
> SQLite documentation.  The BNF representation has not been supported
> for years.  Shane added that support for us
> almost 10 years ago, but the generator script has not been keep
> up-to-date and no longer
> functions.
> 
> The file has been removed from the trunk check-in and should no longer
> appear when you attempt to load it.
> 
> -- 
> 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] Bug in all-bnf.html documentation?

[Richard Hipp]

On 7/13/19, George King  wrote:
> I found
> the all-bnf.html page after a quick google search.

That file is obsolete cruft in the Fossil repository that hosts the
SQLite documentation.  The BNF representation has not been supported
for years.  Shane added that support for us
almost 10 years ago, but the generator script has not been keep
up-to-date and no longer
functions.

The file has been removed from the trunk check-in and should no longer
appear when you attempt to load it.

-- 
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 sqlite3.c

[bhandari_nikhil]

I tried "INSERT INTO ft(ft) VALUES('integrity-check')" and it also did not
give any error. But the rebuild command helped solve my problem as the
crashes stopped, otherwise my application was crashing at every commit
operation (thanks a lot for that).

Is there any command/API which can bypass FTS5 when the db itself has been
instructed to use the FTS5 extension ?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in sqlite3.c

[Dan Kennedy]

On 4/6/62 12:11, bhandari_nikhil wrote:

Thanks Dan. I had checked the database integrity using the following command:

sqlite3 myfile.db "PRAGMA integrity_check;"



Try "INSERT INTO ft(ft) VALUES('integrity-check')", where "ft" is the 
name of the fts5 table.



And it had reported ok. I will see if I can share the database file here.
Can you let me know how to check the db file (in case I am not able to share
the db file here) ? And how the fts5 can get corrupted ?


The easiest explanation is that the fts5 tables were modified directly, 
bypassing fts5. Or there could be a bug in fts5 - a bug that may or may not 
still be present; there have been fixes since 3.14. A memory related bug in the 
application could also cause this.
 


To run the rebuild command, the ft refers to the db name ?


The fts5 table name.

Dan.



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

Re: [sqlite] Bug in sqlite3.c

[bhandari_nikhil]

Thanks Dan. I had checked the database integrity using the following command:

sqlite3 myfile.db "PRAGMA integrity_check;"

And it had reported ok. I will see if I can share the database file here.
Can you let me know how to check the db file (in case I am not able to share
the db file here) ? And how the fts5 can get corrupted ?

To run the rebuild command, the ft refers to the db name ?

BTW, I just looked at the code, not used the latest version.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in sqlite3.c

[Dan Kennedy]

On 3/6/62 12:51, bhandari_nikhil wrote:

  I am facing a crash in sqlite3fts5BufferAppendBlob. Following is the
backtrace:

#0  sqlite3Fts5BufferAppendBlob (pRc=pRc@entry=0xf54139c8,
pBuf=pBuf@entry=0xf5afeb90, nData=4294967295, pData=0xf49fff76 "90246ture")
at sqlite3.c:180474

#1  0xf717b6f8 in fts5WriteAppendTerm (p=p@entry=0xf54139a8,
pWriter=pWriter@entry=0xf5afeb84, nTerm=5, pTerm=0xf49fff70
"06280290246ture") at sqlite3.c:188868

#2  0xf717bf29 in fts5IndexMergeLevel (p=p@entry=0xf54139a8,
ppStruct=ppStruct@entry=0xf5afec3c, iLvl=3, pnRem=0xf5afec38) at
sqlite3.c:189176

There is an apparent bug in sqlite3fts5BufferAppendBlob where it is
asserting for check on nData < 0 but nData is actually u32. The nData should
be int, not u32. I am using version 3.14.0.100 but the bug is present in the
latest version as well.

Also, if you notice in frame #0, the nData passed is 0x which is -1.
It was calculated to be -1 in frame #1 where it did nTerm - nPrefix. The
nPrefix value came out to be 6 and nTerm was 5. I want to know when this
nPrefix becomes > nTerm ?


Thanks for reporting this.

I think that can only happen if the FTS5 records stored in the database 
are corrupt. If you are able to share the database I can check for you. 
You can probably repair the index using the following:


  https://sqlite.org/fts5.html#the_rebuild_command

Also, I would have thought this crash would have been fixed by this 
change, which is in 3.28.0:


  https://sqlite.org/src/info/673a7dd698

Have you demonstrated the crash with the latest version, or just 
eyeballed the code?


Cheers then,

Dan.







Regards
Nikhil Bhandari



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
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: "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 sqlite3??

[Stephen Chrzanowski]

 Let's not forget that the size of the database is going to grow above and
beyond the "number of rows" due to pages that aren't reserved for your
data, such index pages, etc.

On Tue, May 28, 2019 at 1:49 PM Jens Alfke  wrote:

>
>
> > On May 26, 2019, at 7:21 PM, John Brigham  wrote:
> >
> > The size of the file reflects the number of rows that should be
> present.  And furthermore, the size of the file nicely reflects the number
> of days. […]  Trust me when I say that this file is way to big for the
> number of rows.
>
> SQLite files can contain free space after rows are deleted. (The free
> space will eventually be reused for new data, or it can be reclaimed using
> the VACUUM pragma.) So the size of the file does not necessarily reflect
> the amount of data it currently contains.
>
> Try opening a copy of the database with the `sqlite3` CLI tool and
> entering “PRAGMA vacuum;”. Then exit and look at the file size.
>
> —Jens
> ___
> 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 sqlite3??

[Simon Slavin]

On 28 May 2019, at 6:49pm, Jens Alfke  wrote:

> Try opening a copy of the database with the `sqlite3` CLI tool and entering 
> “PRAGMA vacuum;”. Then exit and look at the file size.

Alternatively use the sqlite3_analyze tool and read the "Pages on the freelist" 
figures.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bug in sqlite3??

[Jens Alfke]

> On May 26, 2019, at 7:21 PM, John Brigham  wrote:
> 
> The size of the file reflects the number of rows that should be present.  And 
> furthermore, the size of the file nicely reflects the number of days. […]  
> Trust me when I say that this file is way to big for the number of rows.

SQLite files can contain free space after rows are deleted. (The free space 
will eventually be reused for new data, or it can be reclaimed using the VACUUM 
pragma.) So the size of the file does not necessarily reflect the amount of 
data it currently contains.

Try opening a copy of the database with the `sqlite3` CLI tool and entering 
“PRAGMA vacuum;”. Then exit and look at the file size.

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

Re: [sqlite] bug in sqlite3??

[Richard Hipp]

On 5/26/19, John Brigham  wrote:
>   I have an Arduino/Python experiment that generates lots of simple
> numerical data; about 7000 records a day.  I run it through Python into an
> sqlite3 database.I have been running this for 8 months.  I start a new
> database about every six weeks so every database has about 260k rows.   I
> have one database that is missing the first three weeks.

The "dec3_database" contains 168601 records from 2018-12-03
10:50:07.64 to 2018-12-26 21:46:53.346000 in the "first_table"
table, but then just 9 more record in the "dec26_table" table and 341
more records in "dec26_table_cont".

> There is evidence
> that the records are in the file but not appearing:  The size of the file
> reflects the number of rows that should be present.  And furthermore, the
> size of the file nicely reflects the number of days.

How many rows were you expecting, and covering what dates?


> My objectives are
> twofold:  first, can you fix this faulty file

The file looks to be intact to me.  I'm not sure what about it needs fixing?


> and second, this appears to be
> a bug in your software and I want you informed.

What makes you think this bug is in SQLite and not in your python
script?  Do you have an example of SQLite doing something other than
what your script specifically asked it to do?  And do you know for
certain that some other agent (another script, or perhaps a human
using a database inspection tool) didn't connect to the database in
the middle of the session and change the table names around on you?


> I use DBrowser and am
> satisfied with it. I have knowledge about CLI SQL, but am not presently
> using it. The software can be seen at
> https://github.com/mrphysh?tab=repositories I quickly check the link and see
> that the example data-base is the exact one.Notice that the file name is
> dec3.  And notice that the database starts with dec 26.

I didn't notice that.  The first record in the "first_table" table
seems to be dated "2018-12-03 10:50:07.64"  On the other hand, it
is unclear to me why you have three separate tables "first_table",
"dec26_table", and "dec26_table_cont", all with the same schema and
similar data.  So perhaps I am misinterpreting the data.

I also notice that your columns are named "column_one", "column_two",
"column_three", and so forth.  Is that deliberate, or have you
post-processed the database to obfuscate these column names?  If the
latter, might the missing rows have been deleted by the
post-processing and obfuscation step?

-- 
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: CTE name leaking through views

[Stephen Hunt]

I tried it out in trunk and it works for me as well. Thanks for your help!

Steve

On Wed, May 22, 2019 at 3:50 PM Richard Hipp  wrote:

> Thanks for the test case.  This problem should now be fixed on trunk.
> Please try it out and let us know if you encounter any additional
> probglems.
>
> --
> D. Richard Hipp
> d...@sqlite.org
>


-- 
Stephen Hunt
Zaber Technologies Inc.
#2 - 605 West Kent Ave. N.
Vancouver, British Columbia
Canada, V6P 6T7
Toll free (Canada and USA): 1-888-276-8033
Phone: +1-604-569-3780 ext. 134
Fax: +1-604-648-8033
www.zaber.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: sqlite ARM endian problem

[Sebastian Kemper]

On Wed, May 22, 2019 at 03:43:13PM -0400, Richard Hipp wrote:
> On 5/22/19, Sebastian Kemper  wrote:
> >
> > Hello Richard,
> >
> > I have run-tested this with qemu on two targets until now:
> >
> > arm_arm1176jzf-s_vfp
> > armeb_xscale
> >
> > Both worked fine. Unfortunately I still don't have feedback from the
> > user. He didn't receive my mail. I've sent it again and will get back to
> > you then. I'll also try out aarch64 in qemu in the meantime.
>
> Thanks for verifying the fix for us!

Hello again Richard,

The user got back to me confirming that the patched sqlite works on his
armeb xscale device. I also confirmed that it is as well working on
aarch64_cortex-a53 in qemu.

I think that's it for now. Have a nice day!

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

Re: [sqlite] Bug: CTE name leaking through views

[Stephen Hunt]

Hi Richard,

I just realized I didn't include the mailing list in my previous response
to you, so I'm doing that now with the contents of the SQL file I sent you
previously. I've also included a copy of my original email in plaintext as
the HTML formatted SQL queries got mangled when it was processed for the
mailing list. Sorry for any confusion.

As always, let me know if you need any more information with regard to this.

Regards,

Steve

 demo.sql START 

DROP TABLE Data_Demo;
CREATE TABLE Data_Demo(
-- Synthetic key
Id INTEGER NOT NULL PRIMARY KEY,
-- A Name
Name TEXT NOT NULL);
INSERT INTO Data_Demo VALUES (1, 'john');
INSERT INTO Data_Demo VALUES (2, 'james');
INSERT INTO Data_Demo VALUES (3, 'jingle');
INSERT INTO Data_Demo VALUES (4, 'himer');
INSERT INTO Data_Demo VALUES (5, 'smith');

DROP VIEW IF EXISTS View_Proto;
CREATE VIEW View_Proto
AS
  WITH Temp(Name) AS (VALUES ('A'), ('B'))
  SELECT Name Name FROM Temp;

DROP VIEW IF EXISTS View_Demo;
CREATE VIEW View_Demo
AS
  WITH Temp(Att, Val, Act) AS (VALUES
('C', 'D', 'E'),
('F', 'G', 'H')
  )
  SELECT D.Id Id, P.Name Protocol, T.Att Att, T.Val Val, T.Act Act
  FROM Data_Demo D
  CROSS JOIN View_Proto P
  CROSS JOIN Temp T;

 demo.sql END 

On 5/1/19, Stephen Hunt wrote:
> Hi,
>
> We use SQLite extensively here at Zaber and are quite pleased with it.
> However, we recently added a view that incorrectly returns incorrect/NULL
> data. It appears to be cause by CTE names leaking outside of the view and
> being confused with another CTE with the same name but only if both CTEs
> produce the same number of rows.
>
> I have attached some simple SQL and a procedure to reproduce the issue.
>
>1. Create a new database
>2. Read the attached SQL in demo.sql (which creates one table,
Data_Demo,
>and two views, View_Proto and View_Demo, each of which have a CTE named
>Temp that produces 2 rows)
>3. Run SELECT * FROM View_Demo; which incorrectly produces (note the
>incorrect/NULL values for columns Att, Val, and Act):
>
>Id  ProtocolAtt Val Act
>--  --  --  --  --
>1   A   A
>1   A   B
>1   B   A
>1   B   B
>2   ...
>
>4. Run SELECT * FROM View_Demo WHERE Val IS NOT NULL; which correctly
>produces:
>
>Id  ProtocolAtt Val Act
>--  --  --  --  --
>1   A   C   D   E
>1   A   F   G   H
>1   B   C   D   E
>1   B   F   G   H
>2   ...
>
>
> Note that this problem disappears if the two Temp CTEs are given different
> names:
>
>   CREATE VIEW View_Proto
>   AS
> WITH Temp2(Name) AS (VALUES ('A'), ('B'))
> SELECT Name Name FROM Temp2;
>
> OR if they have a different number of rows:
>
>   CREATE VIEW View_Proto
>   AS
> WITH Temp(Name) AS (VALUES ('A'), ('B'), ('X'))
> SELECT Name Name FROM Temp;
>
> Also if one of the CTEs is a SELECT statement instead of a VALUES
> statement, the problem also disappears, even if the CTE names and number
of
> rows returned are the same:
>
>   CREATE VIEW View_Proto
>   AS
> WITH Temp(Name) AS (SELECT Name FROM Data_Demo LIMIT 2)
> SELECT Name Name FROM Temp;
>
> I have confirmed this on both versions 3.22 and 3.28. I’m running Ubuntu
> 18.04.
>
> Thanks for looking into this. I really appreciate it! Please let me know
if
> you need any more information.
>
> Regards,
>
> Steve
-- 
Stephen Hunt
Zaber Technologies Inc.
#2 - 605 West Kent Ave. N.
Vancouver, British Columbia
Canada, V6P 6T7
Toll free (Canada and USA): 1-888-276-8033
Phone: +1-604-569-3780 ext. 134
Fax: +1-604-648-8033
www.zaber.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: CTE name leaking through views

[Richard Hipp]

Thanks for the test case.  This problem should now be fixed on trunk.
Please try it out and let us know if you encounter any additional
probglems.

-- 
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: sqlite ARM endian problem

[Richard Hipp]

On 5/22/19, Sebastian Kemper  wrote:
>
> Hello Richard,
>
> I have run-tested this with qemu on two targets until now:
>
> arm_arm1176jzf-s_vfp
> armeb_xscale
>
> Both worked fine. Unfortunately I still don't have feedback from the
> user. He didn't receive my mail. I've sent it again and will get back to
> you then. I'll also try out aarch64 in qemu in the meantime.

Thanks for verifying the fix for us!
-- 
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: sqlite ARM endian problem

[Sebastian Kemper]

On Mon, May 20, 2019 at 09:10:20PM +0200, Sebastian Kemper wrote:
> On Mon, May 20, 2019 at 02:46:47PM -0400, Richard Hipp wrote:
> > On 5/20/19, Sebastian Kemper  wrote:
> > > In OpenWrt's forum a user raised a topic about being unable to use
> > > Asterisk on his armeb xscale device.
> >
> > Please download a tarball of the latest trunk version of SQLite
> > (check-in https://www.sqlite.org/src/info/b7aad929619f7043 or later)
> > and verify that it now works on OpenWrt armeb xscale devices and
> > report back, as we have no way of verifying this ourselves.  Thanks.
>
> Will do. Please allow a day or two to get back to you.

Hello Richard,

I have run-tested this with qemu on two targets until now:

arm_arm1176jzf-s_vfp
armeb_xscale

Both worked fine. Unfortunately I still don't have feedback from the
user. He didn't receive my mail. I've sent it again and will get back to
you then. I'll also try out aarch64 in qemu in the meantime.

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

Re: [sqlite] Bug: sqlite ARM endian problem

[Sebastian Kemper]

On Mon, May 20, 2019 at 02:46:47PM -0400, Richard Hipp wrote:
> On 5/20/19, Sebastian Kemper  wrote:
> > In OpenWrt's forum a user raised a topic about being unable to use
> > Asterisk on his armeb xscale device.
>
> Please download a tarball of the latest trunk version of SQLite
> (check-in https://www.sqlite.org/src/info/b7aad929619f7043 or later)
> and verify that it now works on OpenWrt armeb xscale devices and
> report back, as we have no way of verifying this ourselves.  Thanks.

Will do. Please allow a day or two to get back to you.

Kind regards,
Seb

> --
> 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: sqlite ARM endian problem

[Richard Hipp]

On 5/20/19, Sebastian Kemper  wrote:
> In OpenWrt's forum a user raised a topic about being unable to use
> Asterisk on his armeb xscale device.

Please download a tarball of the latest trunk version of SQLite
(check-in https://www.sqlite.org/src/info/b7aad929619f7043 or later)
and verify that it now works on OpenWrt armeb xscale devices and
report back, as we have no way of verifying this ourselves.  Thanks.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in table_info pragma

[Jose Isaias Cabrera]

Jose Isaias Cabrera, on Friday, May 17, 2019 08:28 AM, wrote...
>J. King, on Friday, May 17, 2019 07:19 AM, wrote...
>>Perhaps I should have been clearer that this is a regression?
>>

I know, overkill, but here is another look at it,

SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t0
   ...> (
   ...> a text,
   ...> b text default 'bye'
   ...> /* comment */
   ...> );
sqlite> create table t1
   ...> (
   ...> a text default 'hi', -- this is a comment for a
   ...> b text default 'bye', /* this is a comment for b */
   ...> c int default 0
   ...> /* this is a comment for c */
   ...> );
sqlite>
sqlite> create table t2
   ...> (
   ...> a text default 'hi', /* this is a comment for c */
   ...> b text default 'bye', /* this is a comment for b */
   ...> c INTEGER default 0
   ...> -- this is a comment for c
   ...> );
sqlite> .schema
CREATE TABLE t0
(
a text,
b text default 'bye'
/* comment */
);
CREATE TABLE t1
(
a text default 'hi', -- this is a comment for a
b text default 'bye', /* this is a comment for b */
c int default 0
/* this is a comment for c */
);
CREATE TABLE t2
(
a text default 'hi', /* this is a comment for c */
b text default 'bye', /* this is a comment for b */
c INTEGER default 0
-- this is a comment for c
);
sqlite> insert into t0 (a) values ('Hi');
sqlite> select * from t0;
Hi|bye
sqlite> insert into t1 (a) values ('Hi');
sqlite> select * from t1;
Hi|bye|0
sqlite> insert into t2 (a) values ('Hi');
sqlite> select * from t2;
Hi|bye|0
sqlite>
sqlite> select dflt_value from pragma_table_info('t1') where name = 'a';
'hi'
sqlite> select dflt_value from pragma_table_info('t1') where name = 'b';
'bye'
sqlite> select dflt_value from pragma_table_info('t1') where name = 'c';
0
/* this is a comment for c */
sqlite> select dflt_value from pragma_table_info('t2') where name = 'a';
'hi'
sqlite> select dflt_value from pragma_table_info('t2') where name = 'b';
'bye'
sqlite> select dflt_value from pragma_table_info('t2') where name = 'c';
0
-- this is a comment for c
sqlite>

Just found it interesting that in t1 and t2, column c, even though it is an 
INT, or INTEGER with default 0, and a new line, it still shows the comment. 
Yes, I know that they are all treated as text. :-)  Thanks.

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

Re: [sqlite] Bug in table_info pragma

[Clemens Ladisch]

Simon Slavin wrote:
> On 17 May 2019, at 1:33pm, Clemens Ladisch  wrote:
>> This keyword behaves magically.

... as far as the SQL standard is concerned.

> Mmmm.  In that case, to implement this properly you need to store
> a default-type flag alongside the default value.

The SQLite syntax diagrams treat it as "literal-value":


SQLite's actual in-memory representation of default values is an expression
tree; _all_ DEFAULT expressions are evaluated lazily.

(SQL-92 does not allow arbitrary expressions as default values.)


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

Re: [sqlite] Bug in table_info pragma

[Simon Slavin]

On 17 May 2019, at 1:33pm, Clemens Ladisch  wrote:

> This keyword behaves magically.

Mmmm.  In that case, to implement this properly you need to store a 
default-type flag alongside the default value.  Proposed values might be

0) No default specified, so use …
1) Fixed default specified, so use …
2) Magic keyword supplied, so evaluate …
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in table_info pragma

[Clemens Ladisch]

Simon Slavin wrote:
> If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to
> evaluate CURRENT_TIMESTAMP, find a string value like
> '2019-05-17 12:10:43', and store that string in the schema.

This keyword behaves magically.  ANSI SQL-92 says:
| The default value inserted in the column descriptor ... is as
| follows:
| Case:
| a) If the  contains NULL, then the null value.
| b) If the  contains a , then
|Case:
|i) If the subject data type is numeric, then the numeric value
|   of the .
| [...]
| d) If the  contains a ,
|then the value of an implicit reference to the .


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

Re: [sqlite] Bug in table_info pragma

[Jose Isaias Cabrera]

J. King, on Friday, May 17, 2019 07:19 AM, wrote...
>Perhaps I should have been clearer that this is a regression?
>
>SQLite version 3.13.0 2016-05-18 10:57:30
>Enter ".help" for usage hints.
>Connected to a transient in-memory database.
>Use ".open FILENAME" to reopen on a persistent database.
>sqlite> create table t(a text default '' /* comment */ );
>sqlite> pragma table_info(t);
>0|a|text|0|''|0

I am not disagreeing with you about the bug.  That is a bug.  The default value 
should be an empty string ('').  But, it does works on the original intension,

SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>  create table t(a text, b text default '' /* comment */ );
sqlite> insert into t (a) values ('hi');
sqlite> select * from t;
hi|

and further more,

sqlite> insert into t (a,b) values ('hi');
Error: 1 values for 2 columns
sqlite> create table t0(a text, b text default 'bye' /* comment */ );
sqlite> insert into t0 (a) values ('hi');
sqlite> select * from t0;
hi|bye

But you are right, it should not display,

sqlite> select dflt_value from pragma_table_info('t') where name = 'b';
'' /* comment */

and furthermore,

sqlite> select dflt_value from pragma_table_info('t0') where name = 'b';
'bye' /* comment */

Thanks.

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

Re: [sqlite] Bug in table_info pragma

[Nelson, Erik - 2]

Please disregard, apologies for the noise.

From: "Nelson, Erik - 2" 
Sent: May 17, 2019 8:17 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [External email from sqlite-users-boun...@mailinglists.sqlite.org] Re: 
[sqlite] Bug in table_info pragma

We need to get these into the hive database, if that's helpful

From: Simon Slavin 
Sent: May 17, 2019 8:16 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Bug in table_info pragma

On 17 May 2019, at 12:06pm, J. King  wrote:

> Then there would be no differentiating "default CURRENT_TIMESTAMP" from 
> "default 'CURRENT_TIMESTAMP'".

That interesting.

If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to evaluate 
CURRENT_TIMESTAMP, find a string value like '2019-05-17 12:10:43', and store 
that string in the schema.

If you supply "default 'CURRENT_TIMESTAMP'" I would expect SQLite to evaluate 
'CURRENT_TIMESTAMP', arrive at the string value of mostly upper-case letters, 
and store that string in the schema.

Neither of those are the result that programmers would normally want. I 
withdraw my suggestion.  Thanks for the heads-up.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY=JsYCXwKMIwvuiUtswqJf70eWsb7VKv5Zho6ACWgH43c=6CF5uBksQbSaeUGsAgOF2wpKvmXxwo_o6rG9YhRl0qw=

--
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@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY=PJUZ-wgC3TvfU5DSVDpHM4-k2nn_x52ElGT8LAh017k=E1KU-A0zRaYOwwt9DyisbuiP0m-f_SaufevUhT-XhNo=

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

Re: [sqlite] Bug in table_info pragma

[Nelson, Erik - 2]

We need to get these into the hive database, if that's helpful

From: Simon Slavin 
Sent: May 17, 2019 8:16 AM
To: SQLite mailing list 
Subject: Re: [sqlite] Bug in table_info pragma

On 17 May 2019, at 12:06pm, J. King  wrote:

> Then there would be no differentiating "default CURRENT_TIMESTAMP" from 
> "default 'CURRENT_TIMESTAMP'".

That interesting.

If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to evaluate 
CURRENT_TIMESTAMP, find a string value like '2019-05-17 12:10:43', and store 
that string in the schema.

If you supply "default 'CURRENT_TIMESTAMP'" I would expect SQLite to evaluate 
'CURRENT_TIMESTAMP', arrive at the string value of mostly upper-case letters, 
and store that string in the schema.

Neither of those are the result that programmers would normally want. I 
withdraw my suggestion.  Thanks for the heads-up.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=DwIGaQ=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY=JsYCXwKMIwvuiUtswqJf70eWsb7VKv5Zho6ACWgH43c=6CF5uBksQbSaeUGsAgOF2wpKvmXxwo_o6rG9YhRl0qw=

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

Re: [sqlite] Bug in table_info pragma

[Simon Slavin]

On 17 May 2019, at 12:06pm, J. King  wrote:

> Then there would be no differentiating "default CURRENT_TIMESTAMP" from 
> "default 'CURRENT_TIMESTAMP'".

That interesting.

If you supply "default CURRENT_TIMESTAMP" I would expect SQLite to evaluate 
CURRENT_TIMESTAMP, find a string value like '2019-05-17 12:10:43', and store 
that string in the schema.

If you supply "default 'CURRENT_TIMESTAMP'" I would expect SQLite to evaluate 
'CURRENT_TIMESTAMP', arrive at the string value of mostly upper-case letters, 
and store that string in the schema.

Neither of those are the result that programmers would normally want. I 
withdraw my suggestion.  Thanks for the heads-up.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in table_info pragma

[J. King]

On 2019-05-17 07:08:27, "Warren Young"  wrote:


On May 17, 2019, at 4:55 AM, J. King  wrote:


 SQLite version 3.28.0 2019-04-16 19:49:53
 Enter ".help" for usage hints.
 Connected to a transient in-memory database.
 Use ".open FILENAME" to reopen on a persistent database.
 sqlite> create table t(a text default '' /* comment */ );
 sqlite> select dflt_value from pragma_table_info('t') where name = 'a';
 '' /* comment */

 I would expect it to print only the string delimiters.


Isn’t this the mechanism behind the .schema shell command, which just gives a 
textual dump of the schema as input, including whitespace, commas, and no 
interpretation made on the types you give?

In other words, SQLite doesn’t “digest” your schema and spit out a clean 
version, it just tells you what you told it.


Perhaps I should have been clearer that this is a regression?

SQLite version 3.13.0 2016-05-18 10:57:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table t(a text default '' /* comment */ );
sqlite> pragma table_info(t);
0|a|text|0|''|0

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

Re: [sqlite] Bug in table_info pragma

[Warren Young]

On May 17, 2019, at 4:55 AM, J. King  wrote:
> 
> SQLite version 3.28.0 2019-04-16 19:49:53
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> create table t(a text default '' /* comment */ );
> sqlite> select dflt_value from pragma_table_info('t') where name = 'a';
> '' /* comment */
> 
> I would expect it to print only the string delimiters.

Isn’t this the mechanism behind the .schema shell command, which just gives a 
textual dump of the schema as input, including whitespace, commas, and no 
interpretation made on the types you give?

In other words, SQLite doesn’t “digest” your schema and spit out a clean 
version, it just tells you what you told it.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in table_info pragma

[J. King]

On 2019-05-17 06:59:58, "Simon Slavin"  wrote:


On 17 May 2019, at 11:55am, J. King  wrote:


 I would expect it to print only the string delimiters.


I might expect it to print only the thing inside the delimiters, i.e. nothing.


Then there would be no differentiating "default CURRENT_TIMESTAMP" from 
"default 'CURRENT_TIMESTAMP'".


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

Re: [sqlite] Bug in table_info pragma

[Simon Slavin]

On 17 May 2019, at 11:55am, J. King  wrote:

> I would expect it to print only the string delimiters.

I might expect it to print only the thing inside the delimiters, i.e. nothing.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: Segfault in sqlite3_clear_bindings when statement is nullptr

[Christof Arnosti]

Hi,

Since the last mail I sent was not really readable due to problems with
my mail settings I resend the possible bug report below.


I want to report a (possible) bug in sqlite3. When
sqlite3_clear_bindings is called with a nullptr argument, then a
SEGFAULT occurs.

From the behavior of the other methods which use statement as a
parameter I expected the behaviour that sqlite3_clear_bindings with a
nullptr argument is a no-op, since all of sqlite3_bind_*, sqlite3_step,
sqlite3_reset and sqlite3_finalize (that's the ones I checked) don't crash.

Please have a look at the example below.

#include 
#include "sqlite3.h"

sqlite3* db;

int main()
{
    // DB Setup
    sqlite3_open(":memory:", );
    sqlite3_exec(db, "CREATE TABLE test (id INTEGER PRIMARY KEY);",
nullptr, nullptr, nullptr);

    // Working example
    sqlite3_stmt* workingStatement;
    sqlite3_prepare(db, "SELECT * from test where id = ?", -1,
, nullptr);
    sqlite3_bind_int(workingStatement, 0, 0);
    sqlite3_step(workingStatement);
    sqlite3_clear_bindings(workingStatement);
    sqlite3_reset(workingStatement);
    sqlite3_finalize(workingStatement);

    // Crashing example. The Statement can't be created because of the
nonexisting table.
    sqlite3_stmt* nonWorkingStatement;
    sqlite3_prepare(db, "SELECT * from nonexisting where id = ?", -1,
, nullptr);
    sqlite3_bind_int(nonWorkingStatement, 0, 0);
    sqlite3_step(nonWorkingStatement);
    sqlite3_clear_bindings(nonWorkingStatement); // SEGFAULT
    sqlite3_reset(nonWorkingStatement);
    sqlite3_finalize(nonWorkingStatement);
}

Thanks for your great work!

Best regards
Christof Arnosti

-
This e-mail is confidential and may contain privileged information. It is 
intended only for the addressees. If you have received this e-mail in error, 
kindly notify us immediately by telephone or e-mail and delete the message from 
your system. 
-
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug: CTE name leaking through views

[Richard Hipp]

This mailing list disallows attachments as a anti-spam measure.  You
can send attachments directly to me, if you like.

On 5/1/19, Stephen Hunt  wrote:
> Hi,
>
> We use SQLite extensively here at Zaber and are quite pleased with it.
> However, we recently added a view that incorrectly returns incorrect/NULL
> data. It appears to be cause by CTE names leaking outside of the view and
> being confused with another CTE with the same name but only if both CTEs
> produce the same number of rows.
>
> I have attached some simple SQL and a procedure to reproduce the issue.
>
>1. Create a new database
>2. Read the attached SQL in demo.sql (which creates one table, Data_Demo,
>and two views, View_Proto and View_Demo, each of which have a CTE named
>Temp that produces 2 rows)
>3. Run SELECT * FROM View_Demo; which incorrectly produces (note the
>incorrect/NULL values for columns Att, Val, and Act):
>
>Id  ProtocolAtt Val Act
>--  --  --  --  --
>1   A   A
>1   A   B
>1   B   A
>1   B   B
>2   ...
>
>4. Run SELECT * FROM View_Demo WHERE Val IS NOT NULL; which correctly
>produces:
>
>Id  ProtocolAtt Val Act
>--  --  --  --  --
>1   A   C   D   E
>1   A   F   G   H
>1   B   C   D   E
>1   B   F   G   H
>2   ...
>
>
> Note that this problem disappears if the two Temp CTEs are given different
> names:
>
> CREATE VIEW View_ProtoASWITH Temp2(Name) AS (VALUES ('A'),
> ('B'))SELECT Name Name FROM Temp2;
>
> OR if they have a different number of rows:
>
> CREATE VIEW View_ProtoASWITH Temp(Name) AS (VALUES ('A'), ('B'),
> ('X'))SELECT Name Name FROM Temp;
>
> Also if one of the CTEs is a SELECT statement instead of a VALUES
> statement, the problem also disappears, even if the CTE names and number of
> rows returned are the same:
>
> CREATE VIEW View_ProtoASWITH Temp(Name) AS (SELECT Name FROM Data_Demo
> LIMIT 2)SELECT Name Name FROM Temp;
>
> I have confirmed this on both versions 3.22 and 3.28. I’m running Ubuntu
> 18.04.
>
> Thanks for looking into this. I really appreciate it! Please let me know if
> you need any more information.
>
> Regards,
>
> Steve
> --
> Stephen Hunt
> Zaber Technologies Inc.
> #2 - 605 West Kent Ave. N.
> Vancouver, British Columbia
> Canada, V6P 6T7
> Toll free (Canada and USA): 1-888-276-8033
> Phone: +1-604-569-3780 ext. 134
> Fax: +1-604-648-8033
> www.zaber.com
> ___
> 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 sqlite3 (CLI, linux/Ubuntu)

[Keith Medcalf]

Works fine for me on Centos ... using the default (ancient) version of SQLite3

# sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test VALUES(1);"
# touch /tmp/test.init
# sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null
1
# sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 2>/dev/null
1
[root@mail ~]# sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;"
-- Loading resources from /tmp/test.init
1
# sqlite3 --version
3.6.20
#

and with the current tip of trunk

# ./sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;"
-- Loading resources from /tmp/test.init
1
# ./sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 2>/dev/null
1
# ./sqlite3 --version
3.28.0 2019-03-31 18:17:00 
d03b611302f68483770d49b113b4ed685ba03526d2007647c306f8ec7ae697d2

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


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of
>softw...@quantentunnel.de
>Sent: Saturday, 30 March, 2019 05:28
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Bug in sqlite3 (CLI, linux/Ubuntu)
>
>Dear colleagues
>
>I detected an unexpected behaviour in sqlite3 (CLI) that I consider a
>bug as it seems not documented.
>
>When using an init file (even if an empty file), sqlite3 outputs an
>extra empty line to stdout. This messes up parsing of the sqlite3
>output, as this line is not present in the absence of an init file:
>
>buero:~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO
>test VALUES(1);"
>buero:~$ touch /tmp/test.init
>buero:~$ ls -l /tmp/test.*
>-rw-r- 1 abc abc 2048 Mär 30 12:17 /tmp/test.db
>-rw-r- 1 abc abc0 Mär 30 12:17 /tmp/test.init
>buero:~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null
>1
>buero:~$ sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM
>test;" 2>/dev/null
>
>1
>buero:~$
>
>
>My configuration
>buero:~$ sqlite3 --version
>3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
>buero:~$ uname -a
>Linux buero 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12
>17:45:24 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
>___
>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 sqlite3 (CLI, linux/Ubuntu)

[Software]

> Dear colleagues 
> 
> I detected an unexpected behaviour in sqlite3 (CLI) that I consider a bug as 
> it seems not documented. 
> 
> When using an init file (even if an empty file), sqlite3 outputs an extra 
> empty line to stdout. This messes up parsing of the sqlite3 output, as this 
> line is not present in the absence of an init file: 
> 
> buero:~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test 
> VALUES(1);" 
> buero:~$ touch /tmp/test.init 
> buero:~$ ls -l /tmp/test.* 
> -rw-r- 1 abc abc 2048 Mär 30 12:17 /tmp/test.db 
> -rw-r- 1 abc abc0 Mär 30 12:17 /tmp/test.init 
> buero:~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null 
> 1 
> buero:~$ sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 
> 2>/dev/null 
> 
> 1 
> buero:~$ 
> 
> 
> My configuration 
> buero:~$ sqlite3 --version 
> 3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f 
> buero:~$ uname -a 
> Linux buero 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12 17:45:24 UTC 
> 2019 x86_64 x86_64 x86_64 GNU/Linux 
> ___
... [show rest of quote]

upgrade ? 

~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test 
VALUES(1);" 
~$ 
~$ touch /tmp/test.init 
~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null 
1 
~$ 
~$ 
~$ sqlite3 -int /tmp/test.init /tmp/test.db "SELECT * FROM test;" 
2>/dev/null 
~$ ls -l /tmp 
total 16 
-rw-r--r-- 1 luuk luuk 8192 Mar 30 17:18 test.db 
-rw-rw-rw- 1 luuk luuk0 Mar 30 17:18 test.init 
~$ sqlite3 -version 
3.22.0 2018-01-22 18:45:57 
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1 
~$ 


Hi Luuk

Thanks for looking into it. There is a typo in your example (-int instead of 
-init); the error message ("sqlite3: Error: unknown option: -int") went to 
/dev/null. Thus, there is no output to stdout; also the expected query result 
'1' is lacking.

Before I manually upgrade (I use currently the latest automatically uogradable 
version in Ubuntu xenial): would it be possible that you re-run the example 
(with -init) to check whether the leading empty line is indeed no longer 
present in version 3.22?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in sqlite3 (CLI, linux/Ubuntu)

[Luuk]

On 30-3-2019 12:28, softw...@quantentunnel.de wrote:

Dear colleagues

I detected an unexpected behaviour in sqlite3 (CLI) that I consider a bug as it 
seems not documented.

When using an init file (even if an empty file), sqlite3 outputs an extra empty 
line to stdout. This messes up parsing of the sqlite3 output, as this line is 
not present in the absence of an init file:

buero:~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test 
VALUES(1);"
buero:~$ touch /tmp/test.init
buero:~$ ls -l /tmp/test.*
-rw-r- 1 abc abc 2048 Mär 30 12:17 /tmp/test.db
-rw-r- 1 abc abc0 Mär 30 12:17 /tmp/test.init
buero:~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null
1
buero:~$ sqlite3 -init /tmp/test.init /tmp/test.db "SELECT * FROM test;" 
2>/dev/null

1
buero:~$


My configuration
buero:~$ sqlite3 --version
3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
buero:~$ uname -a
Linux buero 4.15.0-46-generic #49~16.04.1-Ubuntu SMP Tue Feb 12 17:45:24 UTC 
2019 x86_64 x86_64 x86_64 GNU/Linux
___


upgrade ?

~$ sqlite3 /tmp/test.db "CREATE TABLE test (col1); INSERT INTO test 
VALUES(1);"

~$
~$ touch /tmp/test.init
~$ sqlite3 /tmp/test.db "SELECT * FROM test;" 2>/dev/null
1
~$
~$
~$ sqlite3 -int /tmp/test.init /tmp/test.db "SELECT * FROM test;" 
2>/dev/null

~$ ls -l /tmp
total 16
-rw-r--r-- 1 luuk luuk 8192 Mar 30 17:18 test.db
-rw-rw-rw- 1 luuk luuk    0 Mar 30 17:18 test.init
~$ sqlite3 -version
3.22.0 2018-01-22 18:45:57 
0c55d179733b46d8d0ba4d88e01a25e10677046ee3da1d5b1581e86726f2alt1

~$

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