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 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 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 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 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 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 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 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:", &db);
    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,
&workingStatement, 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,
&nonWorkingStatement, 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 report: UPSERT / INSERT ON CONFLICT PK Autoincrement

[Clemens Ladisch]

Stanislav Zabka wrote:
> When conflict occurs, no import performs, but PK is incremented nevertheless.

 says:
| Note that "monotonically increasing" does not imply that the ROWID
| always increases by exactly one. One is the usual increment. However,
| if an insert fails due to (for example) a uniqueness constraint, the
| ROWID of the failed insertion attempt might not be reused on
| subsequent inserts, resulting in gaps in the ROWID sequence.
| AUTOINCREMENT guarantees that automatically chosen ROWIDs will be
| increasing but not that they will be sequential.


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

Re: [sqlite] Bug report: Data race in pcache1.c

[Jan Krčál]

Hi Richard,

thanks a million for your check-in!
It indeed fixed our issues with TSan!

Best regards,
Jan

On Wed, Jan 9, 2019 at 3:55 PM Richard Hipp  wrote:

> Check-in https://www.sqlite.org/src/info/383437be276719ac will perhaps
> silence the harmless false-positives reported by TSAN.  Please let us
> know if it does not solve the problem for you.
>
> On 1/7/19, Jan Krčál  wrote:
> > Hi,
> >
> > as a Chrome developer, I've run into a data race flagged by
> ThreadSanitizer
> > in pcache1.c:
> >
> > When two threads write to in-memory sqlite DBs (each to its own separate
> > DB), each thread has its own PCache1 with bPurgeable being false. Thus,
> in
> > both threads, pCache->pnPurgeable points to the static
> > variable dummyCurrentPage.
> >
> > Even if bPurgeable is false, pCache->pnPurgeable gets incremented by
> > pcache1AllocPage()  and decremented by pcache1FreePage(). When each
> pCache
> > belongs to a different pGroup, the writes can happen concurrently on
> those
> > two threads and are not protected by any mutex. This results in a data
> race
> > that makes ThreadSanitizer unhappy.
> >
> > These writes are not needed in the first place because the value of
> > pCache->pnPurgeable is never read if bPurgeable is false. Because the
> value
> > is never read, I can hardly imagine a situation this data race could lead
> > to a real problem. Still, it is technically a data race with undefined
> > behavior. Most importantly, I'd like ThreadSanitizer be happy with this
> > code in this context.
> >
> > This is the first time I look into sqlite source code, does the text
> above
> > make sense to you? :)
> > If so, the fix should be pretty simple. Can you please look into this or
> > should I provide a patch?
> >
> > Best regards,
> > Jan
> > ___
> > 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 Report

[Richard Hipp]

On 2/20/19, William ESCANDE  wrote:
> Hi everyone !
>
> TL;DR:
> with sanitizer
> in func columnName (l.82210)
> calling xFunc(l.82235) trigger a cfi_check (and then ABORT)
>
> Fix to do :
> change prototype of sqlite3_value_text to let him return a `void *`

There are millions and millions of applications that are coded to the
existing API, so we cannot change APIs.

I rewrote columnName() to completely avoid the use of function
pointers.  Perhaps this will appease your compiler.
-- 
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: Data race in pcache1.c

[Richard Hipp]

Check-in https://www.sqlite.org/src/info/383437be276719ac will perhaps
silence the harmless false-positives reported by TSAN.  Please let us
know if it does not solve the problem for you.

On 1/7/19, Jan Krčál  wrote:
> Hi,
>
> as a Chrome developer, I've run into a data race flagged by ThreadSanitizer
> in pcache1.c:
>
> When two threads write to in-memory sqlite DBs (each to its own separate
> DB), each thread has its own PCache1 with bPurgeable being false. Thus, in
> both threads, pCache->pnPurgeable points to the static
> variable dummyCurrentPage.
>
> Even if bPurgeable is false, pCache->pnPurgeable gets incremented by
> pcache1AllocPage()  and decremented by pcache1FreePage(). When each pCache
> belongs to a different pGroup, the writes can happen concurrently on those
> two threads and are not protected by any mutex. This results in a data race
> that makes ThreadSanitizer unhappy.
>
> These writes are not needed in the first place because the value of
> pCache->pnPurgeable is never read if bPurgeable is false. Because the value
> is never read, I can hardly imagine a situation this data race could lead
> to a real problem. Still, it is technically a data race with undefined
> behavior. Most importantly, I'd like ThreadSanitizer be happy with this
> code in this context.
>
> This is the first time I look into sqlite source code, does the text above
> make sense to you? :)
> If so, the fix should be pretty simple. Can you please look into this or
> should I provide a patch?
>
> Best regards,
> Jan
> ___
> 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 report: bug in datetime conversion function

[David Raymond]

You have to look at the original dates. The date you're giving it for the first 
one is Feb 29th on a leap year. So 31 years from Feb 29th goes to Feb 29th on a 
NON-leap year, and thus gets rolled over to March 1st. For the second one 
you're saying 31 years from March 1st, which also lands on March 1st.


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Fábio Pfeifer
Sent: Thursday, October 18, 2018 9:28 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Bug report: bug in datetime conversion function

Hello,

When working with Apple iOS databases, I found something strange when
dealing with dates. Apple iOS databases store dates as seconds from
2001-01-01 (31 years after unix epoch). But from 2015-03-02 to 2016-02-29
there is a offset of one day if '31 years' modifier is used.
To reproduce, execute the following query:

select datetime(446860801, 'unixepoch', '31 years') as date1,
   datetime(446860801+60*60*24, 'unixepoch', '31 years') as date2,
   datetime(446860801+60*60*24*2, 'unixepoch', '31 years') as date3

date2 should be 1 day after date1, but shows as the same date!

Best regards,
Fábio Pfeifer
___
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: bug in datetime conversion function

[Simon Slavin]

On 18 Oct 2018, at 2:28pm, Fábio Pfeifer  wrote:

> When working with Apple iOS databases, I found something strange when dealing 
> with dates.

I suspect you are you are referring to a library routine which is used by lots 
of iOS software, but can you give us a specific App which exhibits this bug, 
and an easy way to find a database file from it ?

> Apple iOS databases store dates as seconds from
> 2001-01-01 (31 years after unix epoch). But from 2015-03-02 to 2016-02-29 
> there is a offset of one day if '31 years' modifier is used.

I get the following:

SQLite version 3.24.0 2018-06-04 14:10:15
sqlite> select datetime(446860801, 'unixepoch', '31 years') as date1;
2015-03-01 00:00:01
sqlite> SELECT datetime(446860801+60*60*24, 'unixepoch', '31 years') as date2;
2015-03-01 00:00:01
sqlite> SELECT datetime(446860801+60*60*24*2, 'unixepoch', '31 years') as date3;
2015-03-02 00:00:01
sqlite> 

Just for reference, 2015 was not a leap year, and 2016 was a leap year.

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

Re: [sqlite] Bug Report: corrupted double-linked list

[Dominique Pellé]

Daniel Espinosa  wrote:

> Thanks for the response.
>
> Until now I just used GDB, cause is easy on Meson.  I will try with
> Valgrind, but I will be slow until I find a way to reproduce the problem.
>
> Just take the desition to report this bug, because just the issue trace the
> fault deeply in the SQLite secuence of destruction, but any way, I will
> check if the issue is in related on multi-threading destruction in GDA.

Alternatively to valgrind, build your app and SQLite by adding
-fsanitize=address (assuming you use gcc or clang) and rerun
the test normally. It's likely that it will then pinpoint to the
problem in your application rather than in SQLite.

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

Re: [sqlite] Bug Report: corrupted double-linked list

[Daniel Espinosa]

Thanks for the response.

Until now I just used GDB, cause is easy on Meson.  I will try with
Valgrind, but I will be slow until I find a way to reproduce the problem.

Just take the desition to report this bug, because just the issue trace the
fault deeply in the SQLite secuence of destruction, but any way, I will
check if the issue is in related on multi-threading destruction in GDA.

El mié., 10 de octubre de 2018 5:46, Richard Hipp  escribió:

> On 10/9/18, Daniel Espinosa  wrote:
> > I'm current maintainer of GDA[1], I've updated embbeded version of SQLite
> > to 3.25.2, but I found an issue with a segfault due to a "corrupted
> > double-linked list".
>
> Heap corruption like this is most often the result of bugs in the
> application and SQLite just happened to be the unlucky library to
> first stumble over it.  Have you tried running your test under
> valgrind, or some other memory validator, to locate the origin of the
> error?  Does the error originate with SQLite, or in some other part of
> the application?
>
>
> >
> > In order to reproduce it:
> >
> > a) Checkout libgda from its respository[1]
> >
> > b) compile using its meson:
> > $ meson _build
> > $ cd _build
> > $ ninja
> > $ meson test Virtual --repeat 100 --gdb
> >
> > Last command runs a unit tests called Virtual, it run it 100 times in a
> GDB
> > session, you may need to repeat the operation or increase number upto 200
> > times.
> >
> > GDB will stop at segfault.
> >
> > This is the latest backtrace:
> >
> > corrupted double-linked list
> >
> > Thread 1 "check_virtual" received signal SIGABRT, Aborted.
> > __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
> > 51../sysdeps/unix/sysv/linux/raise.c: No existe el archivo o el
> > directorio.
> > Una sesión de depuración está activa.
> >
> > Inferior 1 [process 16743] will be killed.
> >
> > ¿Salir de cualquier modo? (y or n) n
> > No confirmado.
> > (gdb) bt
> > #0  0x7701de97 in __GI_raise (sig=sig@entry=6) at
> > ../sysdeps/unix/sysv/linux/raise.c:51
> > #1  0x7701f801 in __GI_abort () at abort.c:79
> > #2  0x77068897 in __libc_message (action=action@entry=do_abort,
> > fmt=fmt@entry=0x77195b9a "%s\n")
> > at ../sysdeps/posix/libc_fatal.c:181
> > #3  0x7706f90a in malloc_printerr (str=str@entry=0x77193cba
> > "corrupted double-linked list")
> > at malloc.c:5350
> > #4  0x7706fac4 in malloc_consolidate (av=av@entry
> =0x7fffe420)
> > at malloc.c:4456
> > #5  0x7707703b in _int_free (have_lock=0, p=,
> > av=0x7fffe420) at malloc.c:4362
> > #6  0x7707703b in __GI___libc_free (mem=0x7fffe404d000) at
> > malloc.c:3124
> > #7  0x77a749d3 in sqlite3MemFree (pPrior=0x7fffe404d008)
> > at ../libgda/sqlite/sqlite-src/sqlite3.c:22612
> > #8  0x77a755b4 in sqlite3_free (p=0x7fffe404d008) at
> > ../libgda/sqlite/sqlite-src/sqlite3.c:26528
> > #9  0x77a84566 in pcache1EnforceMaxPage (pCache=0x7fffe4082f48)
> > at ../libgda/sqlite/sqlite-src/sqlite3.c:48783
> > #10 0x77a84fdc in pcache1Destroy (p=0x7fffe4082f48) at
> > ../libgda/sqlite/sqlite-src/sqlite3.c:49322
> > #11 0x77a83761 in sqlite3PcacheClose (pCache=0x7fffe40085d8)
> > #12 0x77a891ab in sqlite3PagerClose (pPager=0x7fffe40084a8,
> > db=0x7fffe409a408)
> > at ../libgda/sqlite/sqlite-src/sqlite3.c:54278
> > #13 0x77a94fae in sqlite3BtreeClose (p=0x7fffe40775b8) at
> > ../libgda/sqlite/sqlite-src/sqlite3.c:65325
> > #14 0x77b22acb in sqlite3LeaveMutexAndCloseZombie
> > (db=0x7fffe409a408)
> > at ../libgda/sqlite/sqlite-src/sqlite3.c:152945
> > #15 0x77b229eb in sqlite3Close (db=0x7fffe409a408, forceZombie=0)
> > at ../libgda/sqlite/sqlite-src/sqlite3.c:152888
> > #16 0x77b22a0f in sqlite3_close (db=0x7fffe409a408) at
> > ../libgda/sqlite/sqlite-src/sqlite3.c:152901
> > #17 0x77a69ff0 in gda_sqlite_free_cnc_data (cdata=0x7fffe407de20)
> > at ../libgda/sqlite/gda-sqlite-provider.c:4209
> > #18 0x77a23817 in stage2_close_connection (cnc=0x7fffe40089f0,
> > result=0x1)
> > at ../libgda/gda-server-provider.c:2286
> > #19 0x77a23a33 in _gda_server_provider_close_connection
> > (provider=0x7fffe40656d0, cnc=0x7fffe40089f0, error=0x0) at
> > ../libgda/gda-server-provider.c:2343
> > #20 0x779a90be in gda_connection_close (cnc=0x7fffe40089f0,
> > error=0x0)
> > at ../libgda/gda-connection.c:1547
> > #21 0x77b26d4c in gda_vconnection_data_model_dispose
> > (object=0x7fffe40089f0)
> >
> > --
> > This electronic message may contain privileged and confidential
> information
> > intended only for the use of the addressees named above.  If you are not
> > the intended recipient of this email, we kindly ask you to delete this
> > message and any attachment. You are hereby notified that any use,
> > dissemination, distribution, reproduction of this email is prohibited.
> If
> > you have received this email i

Re: [sqlite] Bug Report: corrupted double-linked list

[Richard Hipp]

On 10/9/18, Daniel Espinosa  wrote:
> I'm current maintainer of GDA[1], I've updated embbeded version of SQLite
> to 3.25.2, but I found an issue with a segfault due to a "corrupted
> double-linked list".

Heap corruption like this is most often the result of bugs in the
application and SQLite just happened to be the unlucky library to
first stumble over it.  Have you tried running your test under
valgrind, or some other memory validator, to locate the origin of the
error?  Does the error originate with SQLite, or in some other part of
the application?


>
> In order to reproduce it:
>
> a) Checkout libgda from its respository[1]
>
> b) compile using its meson:
> $ meson _build
> $ cd _build
> $ ninja
> $ meson test Virtual --repeat 100 --gdb
>
> Last command runs a unit tests called Virtual, it run it 100 times in a GDB
> session, you may need to repeat the operation or increase number upto 200
> times.
>
> GDB will stop at segfault.
>
> This is the latest backtrace:
>
> corrupted double-linked list
>
> Thread 1 "check_virtual" received signal SIGABRT, Aborted.
> __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:51
> 51../sysdeps/unix/sysv/linux/raise.c: No existe el archivo o el
> directorio.
> Una sesión de depuración está activa.
>
> Inferior 1 [process 16743] will be killed.
>
> ¿Salir de cualquier modo? (y or n) n
> No confirmado.
> (gdb) bt
> #0  0x7701de97 in __GI_raise (sig=sig@entry=6) at
> ../sysdeps/unix/sysv/linux/raise.c:51
> #1  0x7701f801 in __GI_abort () at abort.c:79
> #2  0x77068897 in __libc_message (action=action@entry=do_abort,
> fmt=fmt@entry=0x77195b9a "%s\n")
> at ../sysdeps/posix/libc_fatal.c:181
> #3  0x7706f90a in malloc_printerr (str=str@entry=0x77193cba
> "corrupted double-linked list")
> at malloc.c:5350
> #4  0x7706fac4 in malloc_consolidate (av=av@entry=0x7fffe420)
> at malloc.c:4456
> #5  0x7707703b in _int_free (have_lock=0, p=,
> av=0x7fffe420) at malloc.c:4362
> #6  0x7707703b in __GI___libc_free (mem=0x7fffe404d000) at
> malloc.c:3124
> #7  0x77a749d3 in sqlite3MemFree (pPrior=0x7fffe404d008)
> at ../libgda/sqlite/sqlite-src/sqlite3.c:22612
> #8  0x77a755b4 in sqlite3_free (p=0x7fffe404d008) at
> ../libgda/sqlite/sqlite-src/sqlite3.c:26528
> #9  0x77a84566 in pcache1EnforceMaxPage (pCache=0x7fffe4082f48)
> at ../libgda/sqlite/sqlite-src/sqlite3.c:48783
> #10 0x77a84fdc in pcache1Destroy (p=0x7fffe4082f48) at
> ../libgda/sqlite/sqlite-src/sqlite3.c:49322
> #11 0x77a83761 in sqlite3PcacheClose (pCache=0x7fffe40085d8)
> #12 0x77a891ab in sqlite3PagerClose (pPager=0x7fffe40084a8,
> db=0x7fffe409a408)
> at ../libgda/sqlite/sqlite-src/sqlite3.c:54278
> #13 0x77a94fae in sqlite3BtreeClose (p=0x7fffe40775b8) at
> ../libgda/sqlite/sqlite-src/sqlite3.c:65325
> #14 0x77b22acb in sqlite3LeaveMutexAndCloseZombie
> (db=0x7fffe409a408)
> at ../libgda/sqlite/sqlite-src/sqlite3.c:152945
> #15 0x77b229eb in sqlite3Close (db=0x7fffe409a408, forceZombie=0)
> at ../libgda/sqlite/sqlite-src/sqlite3.c:152888
> #16 0x77b22a0f in sqlite3_close (db=0x7fffe409a408) at
> ../libgda/sqlite/sqlite-src/sqlite3.c:152901
> #17 0x77a69ff0 in gda_sqlite_free_cnc_data (cdata=0x7fffe407de20)
> at ../libgda/sqlite/gda-sqlite-provider.c:4209
> #18 0x77a23817 in stage2_close_connection (cnc=0x7fffe40089f0,
> result=0x1)
> at ../libgda/gda-server-provider.c:2286
> #19 0x77a23a33 in _gda_server_provider_close_connection
> (provider=0x7fffe40656d0, cnc=0x7fffe40089f0, error=0x0) at
> ../libgda/gda-server-provider.c:2343
> #20 0x779a90be in gda_connection_close (cnc=0x7fffe40089f0,
> error=0x0)
> at ../libgda/gda-connection.c:1547
> #21 0x77b26d4c in gda_vconnection_data_model_dispose
> (object=0x7fffe40089f0)
>
> --
> This electronic message may contain privileged and confidential information
> intended only for the use of the addressees named above.  If you are not
> the intended recipient of this email, we kindly ask you to delete this
> message and any attachment. You are hereby notified that any use,
> dissemination, distribution, reproduction of this email is prohibited.  If
> you have received this email in error, please notify sender immediately.
>
> Any document, image or any other form of electronic representation of any
> work attached to this email, is suitable to be protected by copyright
> enforcement by applicable law in your or sender's Country's and
> International Legislation
>
> Trabajar, la mejor arma para tu superación
> "de grano en grano, se hace la arena" (R) (en trámite, pero para los
> cuates: LIBRE)
> ___
> 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
___

Re: [sqlite] Bug report: Window functions in VIEWs broken in 3.25.1

[Clemens Ladisch]

Bjoern Hoehrmann wrote:
>   Using the sqlite-tools-linux-x86-3250100 Linux binaries I find that
> Window functions in VIEWS behave differently from PostgreSQL 9.6 and
> from what I expect.
>
>   DROP TABLE IF EXISTS example;
>   CREATE TABLE example(t INT, total INT);
>   INSERT INTO example VALUES(0,2);
>   INSERT INTO example VALUES(5,1);
>   INSERT INTO example VALUES(10,1);
>
>   DROP VIEW IF EXISTS view_example;
>   CREATE VIEW view_example AS
>   SELECT
> NTILE(256) OVER (ORDER BY total) - 1 AS nt
>   FROM
> example
>   ;
>
>   SELECT * FROM view_example;
>
> In SQLite 3.25.1 I get 0, 0, 0

The EXPLAIN output shows that the optimizer ended up generating
a program for "SELECT 1 - 1 FROM example".

> while PostgreSQL 9.6 gives 0, 1, 2.

And the same query outside a view gives the correct ouput.


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

Re: [sqlite] BUG REPORT

[Chris Locke]

> I am using a query to check a date field between a range of dates

Can you provide example values of the date in your database?
Are you storing the EXACT date (eg, '2018-02-01 12:21'), or just the date?

> When running this with the  ODBC driver it fails to return all the
appropriate record in the range. I tried the exact same query in a
> DB Browser for Sqlite and it recovers 127 records only.

How many records were returned with the ODBC driver?



On Wed, Aug 15, 2018 at 10:16 AM Mr Max  wrote:

> To whom it may concern,
>
>
>
>
>
> Whilst using an ODBC driver for SQLite acquired from:
>
>
>
> http://www.ch-werner.de/sqliteodbc/
>
>
>
> I came across a potential bug in SQLite.
>
>
>
> I have an application running VB.NET on a Windows 7 32-bit machine and
> have
> installed the sqliteodbc.exe from the website above. I am using a query to
> check a date field between a range of dates, the exact query being:
>
>
>
> SELECT ind.CUSTOMERU, ind.XTRANU, ind.DDATE, SUM(ind.DAMOUNT) as REVENUE,
> MIN(inc.SURNAME) as CNAME
>
> FROM INV_DETAIL ind inner JOIN CUSTOMER inc ON ind.CUSTOMERU=inc.UNIQ
> WHERE
> ind.DDATE BETWEEN '2018-02-01' AND '2018-02-28' AND ind.DTYPE='3'
>
> AND ind.DAMOUNT<0 AND ind.SUBCONTRU<>'666' AND ind.SUBCONTRU<>'555' GROUP
> BY
> ind.CUSTOMERU, ind.XTRANU ORDER BY ind.CUSTOMERU, ind.XTRANU;
>
>
>
>
>
> When running this with the  ODBC driver it fails to return all the
> appropriate record in the range. I tried the exact same query in a DB
> Browser for Sqlite and it recovers 127 records only.
>
> I have run the same query using ODBC and Access (office 2003) and it
> recovers 138 records. Doing a manual filter of the records from the
> INV_DETAIL table I can extract 138 records!!
>
> The records  apparently omitted by SQLite are one with DDATE equal to the
> start date of 2018-02-01. If I make the start date one day earlier the
> SQLite query returns 138 records!!
>
>
>
> I have attached a spreadsheet with the data from the report I am generating
> and with the INV_DETAIL data for the whole month of Feb 2018.
>
>
>
>
>
> Regards
>
> Bob Maxwell
>
>
>
> ___
> 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

[Richard Hipp]

On 8/15/18, Mr Max  wrote:
> When running this with the  ODBC driver it fails to return all the
> appropriate record in the range. I tried the exact same query in a DB
> Browser for Sqlite and it recovers 127 records only.

What answer do you get when you run your query using the
officially-supported "sqlite3.exe" command-line tool available from
(https://www.sqlite.org/download.html)?

The "DB Browser for SQLite" is a third-party tool, about which I know
very little.
-- 
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

[Richard Hipp]

On 2/27/18, Alexander Ananin  wrote:
>
> I've found the strange behavior in the rtree.c file.

Are you trying to compile rtree.c separately, rather than using the
version that is bundled into the sqlite3.c amalgmation?  May I ask why
you want to do that?
-- 
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 Loading Short Journal

[Richard Hipp]

On 9/6/17, Natalie Silvanovich  wrote:
> I'm experiencing a crash when loading a database with a corrupt journal
> file.

The chances of hitting the problem by accident are remote - so much so
that it is impossible in practice.  This problem can only come up if
an adversary deliberately crafts a malicious rollback journal and
tricks an application into using it.

In any event, the problem is now fixed on trunk.  Thanks for the
concise and clear bug report!

-- 
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: Wrong column name in a table in a certain case

[Richard Hipp]

Now fixed on trunk.

On 8/11/17, Jürgen Palm  wrote:
> Hi,
>
> please have a look at the following sequence of statements executed on
> Windows 10 with sqlite3.exe, version 3.19.3 and 3.20.0:
>
> CREATE TABLE test("column with space" TEXT);
> CREATE TABLE test2 AS SELECT "column with space" FROM test;
> CREATE TABLE test3 AS SELECT "column with space" FROM test GROUP BY 1;
> CREATE TABLE test4 AS SELECT "column with space" AS "column with space"
> FROM test GROUP BY 1;
> SELECT * FROM sqlite_master;
> table|test|test|2|CREATE TABLE test("column with space" TEXT)
> table|test2|test2|3|CREATE TABLE test2("column with space" TEXT)
> table|test3|test3|4|CREATE TABLE test3("""column with space""" TEXT)
> table|test4|test4|5|CREATE TABLE test4("column with space" TEXT)
>
> As you can see the table "test3" has extra double quotes around the
> column name "column with space". There should be no extra double quotes
> for this table and table "test3" should look like the tables "test2" and
> "test4".
>
> Regards,
> Jürgen
> ___
> 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 report: Wrong column name in a table in a certain case

[Bernd Lehmkuhl]

Most probably not a bug. I asked something similar a while ago. It's as 
easy as https://sqlite.org/faq.html#q28 .
As long as you don't explicitly assign an alias to a column name, sqlite 
is not guaranteed to return what you might expect.



Am 11.08.2017 um 22:52 schrieb Jürgen Palm:

Hi,

please have a look at the following sequence of statements executed on 
Windows 10 with sqlite3.exe, version 3.19.3 and 3.20.0:


CREATE TABLE test("column with space" TEXT);
CREATE TABLE test2 AS SELECT "column with space" FROM test;
CREATE TABLE test3 AS SELECT "column with space" FROM test GROUP BY 1;
CREATE TABLE test4 AS SELECT "column with space" AS "column with space" 
FROM test GROUP BY 1;

SELECT * FROM sqlite_master;
table|test|test|2|CREATE TABLE test("column with space" TEXT)
table|test2|test2|3|CREATE TABLE test2("column with space" TEXT)
table|test3|test3|4|CREATE TABLE test3("""column with space""" TEXT)
table|test4|test4|5|CREATE TABLE test4("column with space" TEXT)

As you can see the table "test3" has extra double quotes around the 
column name "column with space". There should be no extra double quotes 
for this table and table "test3" should look like the tables "test2" and 
"test4".


Regards,
Jürgen
___
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: https://www.sqlite.org/chronology.html out of date

[Richard Hipp]

I think it depends on what Tom means by the "latest release".  The
chronology shows the "latest" in chronological order.  But the latest
in chronological order is not necessary the same as the latest in
logical order.

In other words, perhaps Tom is assuming that release numbers are
monotonically increasing.  But that is not always case, as is
evidenced by the 3.18.1 and 3.18.2 patch releases that are logically
before 3.19.0, but chronologically after 3.19.0.

On 6/21/17, jungle Boogie  wrote:
> On 20 June 2017 at 07:55, Tom Ritter  wrote:
>> In lieu of an official support RSS feed,
>> https://www.sqlite.org/chronology.html is used to detect new versions
>> of SQLite and how out of date the current one embedded in a product
>> is. It worked for the past several months, but now this page is out of
>> date. =)
>
> You can monitor this:
> https://www.sqlite.org/src/timeline?r=release
>
> I think if you put that in your rss reader, it'll update when a new
> build is available.
>
>>
>> -tom
> ___
> 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 Report: https://www.sqlite.org/chronology.html out of date

[jungle Boogie]

On 20 June 2017 at 07:55, Tom Ritter  wrote:
> In lieu of an official support RSS feed,
> https://www.sqlite.org/chronology.html is used to detect new versions
> of SQLite and how out of date the current one embedded in a product
> is. It worked for the past several months, but now this page is out of
> date. =)

You can monitor this:
https://www.sqlite.org/src/timeline?r=release

I think if you put that in your rss reader, it'll update when a new
build is available.

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

Re: [sqlite] [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

[Scott Robison]

On Mon, Mar 6, 2017 at 3:39 AM, Yuri  wrote:

> On 03/06/2017 01:00, Dominique Devienne wrote:
>
>> This is clearly documented inhttps://www.sqlite.org/c3ref/blob_open.html
>> though, so I'm afraid this is "by design". --DD
>>
>
>
> Even though this is documented, parts of this limitation don't appear to
> be reasonable. Updating an integer field in the same row shouldn't affect
> the blob field. Rows can be very large and shouldn't move when individual
> fields are updated.


The potential difficulty here is that integers are not encoded as fixed
sized fields. Depending on the magnitude of the integer that is being
written, it could change sizes from 0 to 9 bytes. Thus the offset of the
blob in the row may change.

Once the fix for the "any table same rowid" problem is available to you,
your best bet is the separate blob table (which I think I read you've
already tried).
-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

[Clemens Ladisch]

Yuri wrote:
> Updating an integer field in the same row shouldn't affect the blob field.

Integer fields have a variable size (0 to 8 bytes):
http://www.sqlite.org/fileformat2.html#record_format


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

Re: [sqlite] [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

[Hick Gunter]

AFAICT It is not the new value of the integer but rather the new contents of 
the blob field that causes the record image to grow and exceed the previously 
allocated space, which means the row has to move. Rewriting the record and thus 
expiring the blob handle is triggered by the update of the other field.

You might like to read up on SQLite database and record format to gain insight 
into this.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Yuri
Gesendet: Montag, 06. März 2017 11:39
An: SQLite mailing list 
Betreff: Re: [sqlite] [BUG REPORT] Open blob is invalidated by the update to 
unrelated fields in the same row

On 03/06/2017 01:00, Dominique Devienne wrote:
> This is clearly documented
> inhttps://www.sqlite.org/c3ref/blob_open.html
> though, so I'm afraid this is "by design". --DD


Even though this is documented, parts of this limitation don't appear to be 
reasonable. Updating an integer field in the same row shouldn't affect the blob 
field. Rows can be very large and shouldn't move when individual fields are 
updated.


Yuri

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


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

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


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

Re: [sqlite] [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

[Dominique Devienne]

On Mon, Mar 6, 2017 at 11:39 AM, Yuri  wrote:

> On 03/06/2017 01:00, Dominique Devienne wrote:
>
>> This is clearly documented inhttps://www.sqlite.org/c3ref/blob_open.html
>> though, so I'm afraid this is "by design". --DD
>>
>
> Even though this is documented, parts of this limitation don't appear to
> be reasonable. Updating an integer field in the same row shouldn't affect
> the blob field. Rows can be very large and shouldn't move when individual
> fields are updated.


Don't get me wrong, I'm not fond of SQLite blobs as they stand, and I've
said so before on this list.
But modulo the bug you found (mixup of rowids across tables, as mentioned
by Clemens), there's
not much any of us can do, this is in DRH's hands, and from my past threads
here, not much is likely
to happen in this regard.

Changing the way blobs work in SQLite would probably require a format
change (I think),
and that alone is a big enough change that makes it rather unlikely I'm
afraid.

FWIW, there are two main issues with blobs IMHO:
1) they are always stored "in-row". Oracle for example only stores the blob
"index" in-row,
  i.e. which "blob" pages make up the blob. This makes accessing columns
(notably later added columns)
  after the blobs very expensive with large blobs. But makes schema
evolution more difficult than it should be.
  And the advice to putting blobs in separate tables is just a workaround,
requires joins, and makes it more
  difficult to ensure 1-to-1 relationship between the "meta" row and
"bulk-data" row, and associated lifetime management.
2) they can't be updated incrementally generally, except in very limited
cases, and incurring more IO than should be.
  with "dual stage" blobs (or the proverbial level of indirection), if you
want to change a few bytes in the middle, the
  IO cost if updating the blob index and update that one "blob" page that
needs updating. Growing or shrinking can
  similarly be supported transactionally, w/o having to copy the whole blob.

SQLite works well with blobs, as long as they stay small, or they are never
updated.
Outside these use cases, things start the break down and you must either
resort to work-arounds,
and stop using SQLite for those bulk-data. And that's a pity/shame IMHO. My
$0.02 :)  --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

[Yuri]

On 03/06/2017 01:00, Dominique Devienne wrote:

This is clearly documented inhttps://www.sqlite.org/c3ref/blob_open.html
though, so I'm afraid this is "by design". --DD



Even though this is documented, parts of this limitation don't appear to 
be reasonable. Updating an integer field in the same row shouldn't 
affect the blob field. Rows can be very large and shouldn't move when 
individual fields are updated.



Yuri

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

Re: [sqlite] [BUG REPORT] Open blob is invalidated by the update to unrelated fields in the same row

[Dominique Devienne]

On Sat, Mar 4, 2017 at 8:21 AM, Yuri  wrote:

> The write operation using the open sqlite3_blob object fails after some
> other field in the same row is updated.
>

This is clearly documented in https://www.sqlite.org/c3ref/blob_open.html
though, so I'm afraid this is "by design". --DD

If the row that a BLOB handle points to is modified by an UPDATE, DELETE,
> or by ON CONFLICT side-effects then the BLOB handle is marked as "expired".
> This is true if any column of the row is changed, even a column other than
> the one the BLOB handle is open on.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: Incorrect error information if db fails to open due to bad flags

[Dan Kennedy]

On 01/13/2017 06:25 AM, Jens Alfke wrote:

I’ve found a case where incorrect error information gets reported to client C 
code trying to open a SQLite database. (This is with SQLite 3.14 on mac OS 
10.12.)

After the following C code runs (the path here is irrelevant):
sqlite3 *db;
int ret = sqlite3_open_v2("/tmp/foo", &db, SQLITE_OPEN_CREATE | 
SQLITE_OPEN_READONLY, NULL);

the value of `ret` is SQLITE_MISUSE, which makes sense because “create” and 
“readonly” is an illegal combination of flags.
However, the value of `db` is NULL, i.e. no database handle was allocated.
This causes problems when the code continues by trying to get more information 
about the error:

if (ret != SQLITE_OK) {
int extendedCode = sqlite3_extended_errcode(db);
const char *message = sqlite3_errmsg(db);
report_error_to_user(extendedCode, message);
}

The value of extendedCode will be SQLITE_NOMEM, and message will be “out of 
memory”. This naturally causes the problem to be reported as an out-of-memory 
error. This happened to me today, and I thought it seemed unlikely on my 16GB 
laptop; but it took me a while to dig to the source of the problem, which is 
that the wrong flags were being used.

sqlite3_open_v2 normally allocates a db handle even on error, so that the 
caller can use the handle to get more information about the error, as above. 
The docs say that the db handle will not be allocated if there wasn’t enough 
memory for the allocation. So it appears that sqlite3_errmsg and 
sqlite3_extended_errcode accept a NULL parameter, but assume that it’s NULL 
because there wasn’t enough memory to allocate a database … which is not true 
in this case.


Fair point.

Note that technically speaking, when SQLite returns SQLITE_MISUSE the 
behavior is actually undefined. Situations in which the program might 
segfault if the stars were aligned differently. So this is not actually 
a bug - just a situation that could be made easier to debug.


Dan.







The fix would seem to be to allocate a database handle in the situation here 
where an illegal combination of flags is used.

—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 + fix: SQLite 3.11+ broken on EBCDIC systems

[Scott Hess]

On Mon, Dec 12, 2016 at 9:30 PM, Bradford Larsen  wrote:
> An alternative possibility would be to revert to the pre-3.11 tokenizer on
> EBCDIC systems.  If I recall, the old tokenizer used a big switch statement
> with character literals instead of the 'aiClass' table.  I believe this
> would avoid the EBCDIC tokenizing troubles, at the expense of lower
> performance on those systems, and with the maintenance cost of keeping 2
> code paths around.

Or the build pass could compile a switch-based generator.  It would
make cross-compiling more complicated.

Perhaps easier would be to have a set of unrolled table initializers
which used the raw characters as indices, called as part of
sqlite3_init().

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

Re: [sqlite] Bug report + fix: SQLite 3.11+ broken on EBCDIC systems

[Bradford Larsen]

>
> On Sun, Dec 11, 2016 at 20:42 Richard Hipp  wrote:
>
> SQLite only uses the "[" character as a compatibility quoting
>
> mechanism for SQL Server.  Maybe the solution is for [...] quoting to
>
> simply not work on EBCDIC systems?
>
>
>
> --
>
> D. Richard Hipp
>
> d...@sqlite.org
>
> This would be one possibility—you then might be able to simultaneously
support several EBCDIC code pages with a single 'aiClass' definition (cp37
and cp1047 for example).

(For what it's worth, I'm sure my application doesn't use this
compatibility quoting mechanism on the EBCDIC system.)

However, maybe it would be better to pick a specific EBCDIC code page to
provide an 'aiClass' definition for in the SQLite sources, and make a note
about this in the docs—perhaps something like this to start:

"SQLite is designed to compile and run on EBCDIC-based systems that use
code page $CHOSEN_CODEPAGE.  If you wish to build SQLite for a system that
uses a different EBCDIC codepage, you will need to modify the definition of
the 'aiClass' table used by the SQL tokenizer."

An alternative possibility would be to revert to the pre-3.11 tokenizer on
EBCDIC systems.  If I recall, the old tokenizer used a big switch statement
with character literals instead of the 'aiClass' table.  I believe this
would avoid the EBCDIC tokenizing troubles, at the expense of lower
performance on those systems, and with the maintenance cost of keeping 2
code paths around.

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

Re: [sqlite] Bug report + fix: SQLite 3.11+ broken on EBCDIC systems

[Richard Hipp]

SQLite only uses the "[" character as a compatibility quoting
mechanism for SQL Server.  Maybe the solution is for [...] quoting to
simply not work on EBCDIC systems?

-- 
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 + fix: SQLite 3.11+ broken on EBCDIC systems

[Guy Harris]

On Dec 11, 2016, at 4:57 PM, Richard Hipp  wrote:

> I agree with most of your changes.  But I wonder about moving the
> QUOTE2 (the '[' character) value from code 0xba over to 0xad.
> According to EBCDIC chart at https://en.wikipedia.org/wiki/EBCDIC the
> '[' character should be at 0xba.  Is wikipedia wrong?

If so, then some obscure company called the International Business Machines 
Corporation is also wrong:

http://publibfp.dhe.ibm.com/epubs/pdf/dz9zr010.pdf

The EBCDIC/ISO-8 chart in appendix I of version 10 of the z/Architecture 
Principles of Operation has [ at 0xba and ] at 0xbb for EBCDIC; it has an 
accented capital Y at 0xad.

However, it's apparently a bit more complicated; different EBCDIC code pages 
apparently give square brackets different code points:

http://www-01.ibm.com/support/docview.wss?uid=swg21515307

Apparently EBCDIC code page IBM 1047 has [ as 0xad and EBCDIC code page 037 has 
[ as 0xba.  Here's 037:

https://en.wikipedia.org/wiki/EBCDIC_037


ftp://ftp.software.ibm.com/software/globalization/gcoc/attachments/CP00037.pdf

and here's 1047:

https://en.wikipedia.org/wiki/EBCDIC_1047


ftp://ftp.software.ibm.com/software/globalization/gcoc/attachments/CP01047.pdf

The Wikipedia page on EBCDIC code pages:

https://en.wikipedia.org/wiki/EBCDIC_code_pages

says of 1047 "Open Systems (MVS C compiler)", so the issue may be that Brad 
Larsen is using that compiler and it's insisting that '[' is 0xad.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report + fix: SQLite 3.11+ broken on EBCDIC systems

[Bradford Larsen]

> On Dec 11, 2016, at 7:57 PM, Richard Hipp  wrote:
> 
> On 12/11/16, Bradford Larsen  wrote:
> 
>> #endif
>> #ifdef SQLITE_EBCDIC
>> /* x0  x1  x2  x3  x4  x5  x6  x7  x8  x9  xa  xb  xc  xd  xe  xf */
>> /* 0x */   27, 27, 27, 27, 27,  7, 27, 27, 27, 27, 27, 27,  7,  7, 27, 27,
>> /* 1x */   27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27,
>> /* 2x */   27, 27, 27, 27, 27,  7, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27,
>> /* 3x */   27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27,
>> /* 4x */7, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 26, 12, 17, 20, 10,
>> /* 5x */   24, 27, 27, 27, 27, 27, 27, 27, 27, 27, 15,  4, 21, 18, 19, 27,
>> /* 6x */   11, 16, 27, 27, 27, 27, 27, 27, 27, 27, 27, 23, 22,  1, 13,  6,
>> /* 7x */   27, 27, 27, 27, 27, 27, 27, 27, 27,  8,  5,  5,  5,  8, 14,  8,
>> /* 8x */   27,  1,  1,  1,  1,  1,  1,  1,  1,  1, 27, 27, 27, 27, 27, 27,
>> /* 9x */   27,  1,  1,  1,  1,  1,  1,  1,  1,  1, 27, 27, 27, 27, 27, 27,
>> /* Ax */   27, 25,  1,  1,  1,  1,  1,  0,  1,  1, 27, 27, 27,  9, 27, 27,
>> /* Bx */   27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27,
>> /* Cx */   27,  1,  1,  1,  1,  1,  1,  1,  1,  1, 27, 27, 27, 27, 27, 27,
>> /* Dx */   27,  1,  1,  1,  1,  1,  1,  1,  1,  1, 27, 27, 27, 27, 27, 27,
>> /* Ex */   27, 27,  1,  1,  1,  1,  1,  0,  1,  1, 27, 27, 27, 27, 27, 27,
>> /* Fx */3,  3,  3,  3,  3,  3,  3,  3,  3,  3, 27, 27, 27, 27, 27, 27,
>> #endif
>> };
>> 
>> These changes fixed the SQL tokenizer problems I was seeing my EBCDIC-based
>> system, and resulted in a functioning SQLite there.
>> 
>> Please let me know if more is needed to fix this bug.
> 
> Thanks for the suggested fix.  As you probably infer, we do not have
> access to an EBCDIC system for testing.
> 
> I agree with most of your changes.  But I wonder about moving the
> QUOTE2 (the '[' character) value from code 0xba over to 0xad.
> According to EBCDIC chart at https://en.wikipedia.org/wiki/EBCDIC the
> '[' character should be at 0xba.  Is wikipedia wrong?  Is there
> something else about the '[' character in EBCDIC that we should know
> about?


There are many flavors of EBCDIC; the details depend on which code page is 
used.  The mainframes I’ve used have all been configured for code page 1047 
(the “Latin 1/Open System” code page).

The table in the Wikipedia page for EBCDIC 
(https://en.wikipedia.org/wiki/EBCDIC ) 
is for code page 37.  You’re right that the ‘[‘ character has value 0xba in 
code page 37, but in code page 1047, the ‘[‘ character has value 0xad 
(https://en.wikipedia.org/wiki/EBCDIC_1047 
).  See also 
https://en.wikipedia.org/wiki/Code_page#EBCDIC-based_code_pages 
.

I’m afraid the entire EBCDIC situation is complicated, and there isn’t a single 
definition of EBCDIC like there is ASCII — in different EBCDIC code pages, 
characters will have different values.  Code page 1047 may be the most common 
native code page these days — it’s the code page used by all the mainframes 
I’ve touched.  (It’s the default code page for C source code as understood by 
the IBM XL C/C++ compiler, though this does not preclude *running* with a 
different code page than was used for building.)

Either way, it looks like (a) the code page used by SQLite on EBCDIC systems is 
ambiguous, and (b) whatever code page you pick, there seem to be typos in the 
relevant tables in the SQLite source.  The result is a broken SQL tokenizer on 
EBCDIC-based systems.

I think a particular code page should be chosen, and the SQLite sources & docs 
should be clear about which code page is used.  Maybe there's someone else out 
there who has expertise with EBCDIC who will comment.

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

Re: [sqlite] Bug report + fix: SQLite 3.11+ broken on EBCDIC systems

[Richard Hipp]

On 12/11/16, Bradford Larsen  wrote:

> #endif
> #ifdef SQLITE_EBCDIC
> /* x0  x1  x2  x3  x4  x5  x6  x7  x8  x9  xa  xb  xc  xd  xe  xf */
> /* 0x */   27, 27, 27, 27, 27,  7, 27, 27, 27, 27, 27, 27,  7,  7, 27, 27,
> /* 1x */   27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27,
> /* 2x */   27, 27, 27, 27, 27,  7, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27,
> /* 3x */   27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27,
> /* 4x */7, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 26, 12, 17, 20, 10,
> /* 5x */   24, 27, 27, 27, 27, 27, 27, 27, 27, 27, 15,  4, 21, 18, 19, 27,
> /* 6x */   11, 16, 27, 27, 27, 27, 27, 27, 27, 27, 27, 23, 22,  1, 13,  6,
> /* 7x */   27, 27, 27, 27, 27, 27, 27, 27, 27,  8,  5,  5,  5,  8, 14,  8,
> /* 8x */   27,  1,  1,  1,  1,  1,  1,  1,  1,  1, 27, 27, 27, 27, 27, 27,
> /* 9x */   27,  1,  1,  1,  1,  1,  1,  1,  1,  1, 27, 27, 27, 27, 27, 27,
> /* Ax */   27, 25,  1,  1,  1,  1,  1,  0,  1,  1, 27, 27, 27,  9, 27, 27,
> /* Bx */   27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27, 27,
> /* Cx */   27,  1,  1,  1,  1,  1,  1,  1,  1,  1, 27, 27, 27, 27, 27, 27,
> /* Dx */   27,  1,  1,  1,  1,  1,  1,  1,  1,  1, 27, 27, 27, 27, 27, 27,
> /* Ex */   27, 27,  1,  1,  1,  1,  1,  0,  1,  1, 27, 27, 27, 27, 27, 27,
> /* Fx */3,  3,  3,  3,  3,  3,  3,  3,  3,  3, 27, 27, 27, 27, 27, 27,
> #endif
> };
>
> These changes fixed the SQL tokenizer problems I was seeing my EBCDIC-based
> system, and resulted in a functioning SQLite there.
>
> Please let me know if more is needed to fix this bug.

Thanks for the suggested fix.  As you probably infer, we do not have
access to an EBCDIC system for testing.

I agree with most of your changes.  But I wonder about moving the
QUOTE2 (the '[' character) value from code 0xba over to 0xad.
According to EBCDIC chart at https://en.wikipedia.org/wiki/EBCDIC the
'[' character should be at 0xba.  Is wikipedia wrong?  Is there
something else about the '[' character in EBCDIC that we should know
about?

-- 
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: Incorrect output of a LEFT JOIN with row-values in expression

[Richard Hipp]

On 10/26/16, Ján Hric  wrote:
> Hello
> I would like to report a bug in the latest version of SQLite (3.15.0).

Thanks for a very clear and succinct bug report.  Well done!

The ticket is here:
https://www.sqlite.org/src/tktview/fef4bb4bd9185ec8f18d9912abb444da61d02ff2


>
> Description:
> If comparison of row-values is used in the expression of a LEFT JOIN clause
> and no mach is found, instead of one row with values from the left-hand
> table, no rows are returned.
>
> Example code:
>
> CREATE TABLE t1(a, b);
> INSERT INTO t1 VALUES(1, 2);
> CREATE TABLE t2(a, b);
> INSERT INTO t2 VALUES(3, 4);
> SELECT * FROM t1 LEFT JOIN t2 ON t2.a = t1.a AND t2.b = t1.b;
> SELECT * FROM t1 LEFT JOIN t2 ON (t2.a, t2.b) = (t1.a, t1.b);
>
> The first SELECT returns one row, the second returns no rows.
>
>
> Ján Hric
> ___
> 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 REPORT] Transaction that has the key violation is very slow

[Dan Kennedy]

On 09/21/2016 04:34 AM, Yuri wrote:

I import bulk data into SQLite DB. I run 50k records per transaction.


When some bug or data inconsistency occurs and causes the key 
violation, this violation is reported only in the end of the 
transaction (this is okay and has been discussed before).


But I also notice that the transaction (a batch of 50k records) with 
the failed key is much slower compared to when there is no key violation.



I think this is a bug. There is something that is slowing the 
transaction when there is a pending key violation. It should either 
report the violation immediately (which it doesn't do), or keep going 
with the same speed. The way how it is now it just slows the process 
of finding a problem without any apparent reason.


It sounds like you are using deferred FK constraints. To have SQLite 
report the violation at the end of the statement, use an immediate FK 
constraint:


  https://www.sqlite.org/foreignkeys.html#fk_deferred

Dan.


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

Re: [sqlite] Bug Report: All database opening blocked awaiting wal index rebuild

[Brian Vincent]

I've reproduced the issue using both SERIALIZED and MULTITHREADED modes.

The issue here doesn't seem to have anything to do with the threading
modes.  The issue is related to shared caches, and waiting on a b-tree lock
while holding the global SQLITE_MUTEX_STATIC_OPEN lock.

- Brian Vincent

On Sat, Jul 9, 2016 at 5:33 AM Simon Slavin  wrote:

>
> On 9 Jul 2016, at 9:27am, Olivier Mascia  wrote:
>
> > I'm really interested in knowing wether you use the engine in SERIALIZED
> or MULTITHREADED mode during this event reproduction?
>
> In other words, please read the last part of
>
> 
>
> and try to reproduce your problem in "Multi-thread" mode.  If the problem
> still occurs, please try again in "Serialized" mode.
>
> Simon.
> ___
> 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: All database opening blocked awaiting wal index rebuild

[Olivier Mascia]

> Le 11 juil. 2016 à 21:57, Brian Vincent  a écrit :
> 
> Yes, you seem to understand the issue.  The issue only happens when using
> shared caches.
> 
> I've reproduced the issue using both SERIALIZED and MULTITHREADED modes.
> ...
> Being an inherit limitation would seem to imply that there is no solution
> to this problem, that having shared caches and WAL indexes rebuilding
> necessarily should block all unrelated databases opening.  I don't see why
> that should be the case and I'll explain some reasons why.  It's a little
> bit hard for me to talk about it though, because I'm not entirely sure what
> the lock SQLITE_MUTEX_STATIC_OPEN is protecting.  When iterating through
> the list of shared caches, it acquires the lock SQLITE_MUTEX_STATIC_MASTER,
> so the other OPEN lock must be for something else.  The comments say it's
> to prevent a race condition and references "Ticket #3537", but I can't seem
> to find that ticket.

Indeed:

> sqlite3_mutex *mutexOpen = 0;  /* Prevents a race condition. Ticket #3537 */

I couldn't find that ticket either.

> Please let me know if I'm thinking about this problem clearly, or if you
> would like me to test some things or write a simple test case.


As I'm just as you a user of Sqlite, it probably is best to let its developers 
take on this thread from here (or from the beginning).

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om


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

Re: [sqlite] Bug Report: All database opening blocked awaiting wal index rebuild

[Brian Vincent]

Yes, you seem to understand the issue.  The issue only happens when using
shared caches.

I've reproduced the issue using both SERIALIZED and MULTITHREADED modes.

I'm surprised to hear you say that it might be an inherit limitation or
something not necessarily undesirable.  Of course, when using shared
caches, there must be locks to protect the global list of caches.  But
ideally, I would expect these times to be short lived.  If a situation ever
arises where all unrelated database opening, from all threads, is blocked
for long periods of time, that seems to me to be an obvious bug.  That's
the bug that I'm reporting.

Being an inherit limitation would seem to imply that there is no solution
to this problem, that having shared caches and WAL indexes rebuilding
necessarily should block all unrelated databases opening.  I don't see why
that should be the case and I'll explain some reasons why.  It's a little
bit hard for me to talk about it though, because I'm not entirely sure what
the lock SQLITE_MUTEX_STATIC_OPEN is protecting.  When iterating through
the list of shared caches, it acquires the lock SQLITE_MUTEX_STATIC_MASTER,
so the other OPEN lock must be for something else.  The comments say it's
to prevent a race condition and references "Ticket #3537", but I can't seem
to find that ticket.

The problematic line seems to be this one:
https://github.com/mackyle/sqlite/blob/master/src/btree.c#L2405

It seems that the only purpose of this line is to check to see if this is
an already shared pager-cache.  The problem is that this line ends up
acquiring a lock on the btree.  If there was simply a way to check if this
is an already shared pager-cache, without the possibility of waiting on a
lock, then this bug would be solved.

This is the checkin that originally created the open lock
https://www.sqlite.org/src/info/19fa5a29b97f017a

It doesn't appear that the problematic line existed at this time, which
leads me to believe that there were probably versions of sqlite released
that didn't have this bug.

It seems to me that when this open lock was created, it was expected that
the open code would always execute within a very short period of time.
Later on, someone added the call to sqlite3BtreeSchema, not realizing that
it could possibly get hung up on a lock, which could block all opening for
all databases.

Please let me know if I'm thinking about this problem clearly, or if you
would like me to test some things or write a simple test case.

Thanks,
Brian Vincent


On Sat, Jul 9, 2016 at 5:58 AM Olivier Mascia  wrote:

> > Le 9 juil. 2016 à 12:33, Simon Slavin  a écrit :
> >
> >> I'm really interested in knowing wether you use the engine in
> SERIALIZED or MULTITHREADED mode during this event reproduction?
> >
> > In other words, please read the last part of
> >
> > 
> >
> > and try to reproduce your problem in "Multi-thread" mode.  If the
> problem still occurs, please try again in "Serialized" mode.
>
> In between I had a quick look at sqlite3.c code.  It looks like the source
> of the issue is expected. The MULTITHREADED mode relaxes usage of mutexes,
> albeit only on connections and statements structures.  The remaining bits
> of the engine still make uses of multiple mutexes to protect its integrity
> from multiple threads using the engine.
>
> See sqlite3BtreeOpen() and this section of code where the
> SQLITE_MUTEX_STATIC_OPEN is acquired.
> The SERIALIZED and MULTITHREADED modes both have SQLITE_THREADSAFE != 0
> (which is right).
>
> #if !defined(SQLITE_OMIT_SHARED_CACHE) && !defined(SQLITE_OMIT_DISKIO)
>   /*
>   ** If this Btree is a candidate for shared cache, try to find an
>   ** existing BtShared object that we can share with
>   */
>   if( isTempDb==0 && (isMemdb==0 || (vfsFlags&SQLITE_OPEN_URI)!=0) ){
> if( vfsFlags & SQLITE_OPEN_SHAREDCACHE ){
>   int nFilename = sqlite3Strlen30(zFilename)+1;
>   int nFullPathname = pVfs->mxPathname+1;
>   char *zFullPathname = sqlite3Malloc(MAX(nFullPathname,nFilename));
>   MUTEX_LOGIC( sqlite3_mutex *mutexShared; )
>
>   p->sharable = 1;
>   if( !zFullPathname ){
> sqlite3_free(p);
> return SQLITE_NOMEM_BKPT;
>   }
>   if( isMemdb ){
> memcpy(zFullPathname, zFilename, nFilename);
>   }else{
> rc = sqlite3OsFullPathname(pVfs, zFilename,
>nFullPathname, zFullPathname);
> if( rc ){
>   sqlite3_free(zFullPathname);
>   sqlite3_free(p);
>   return rc;
> }
>   }
> #if SQLITE_THREADSAFE
>   mutexOpen = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_OPEN);
>   sqlite3_mutex_enter(mutexOpen);
>   mutexShared = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER);
>   sqlite3_mutex_enter(mutexShared);
> #endif
>
> The whole issue then revolves around the SHARED_CACHE/PRIVATE_CACHE and
> not the SERIALIZED/MULTITHREADED mode. Chances are that if OP Brian tries
> the same scen

Re: [sqlite] Bug Report: All database opening blocked awaiting wal index rebuild

[Olivier Mascia]

> Le 9 juil. 2016 à 12:33, Simon Slavin  a écrit :
> 
>> I'm really interested in knowing wether you use the engine in SERIALIZED or 
>> MULTITHREADED mode during this event reproduction?
> 
> In other words, please read the last part of
> 
> 
> 
> and try to reproduce your problem in "Multi-thread" mode.  If the problem 
> still occurs, please try again in "Serialized" mode.

In between I had a quick look at sqlite3.c code.  It looks like the source of 
the issue is expected. The MULTITHREADED mode relaxes usage of mutexes, albeit 
only on connections and statements structures.  The remaining bits of the 
engine still make uses of multiple mutexes to protect its integrity from 
multiple threads using the engine.

See sqlite3BtreeOpen() and this section of code where the 
SQLITE_MUTEX_STATIC_OPEN is acquired.
The SERIALIZED and MULTITHREADED modes both have SQLITE_THREADSAFE != 0 (which 
is right).

#if !defined(SQLITE_OMIT_SHARED_CACHE) && !defined(SQLITE_OMIT_DISKIO)
  /*
  ** If this Btree is a candidate for shared cache, try to find an
  ** existing BtShared object that we can share with
  */
  if( isTempDb==0 && (isMemdb==0 || (vfsFlags&SQLITE_OPEN_URI)!=0) ){
if( vfsFlags & SQLITE_OPEN_SHAREDCACHE ){
  int nFilename = sqlite3Strlen30(zFilename)+1;
  int nFullPathname = pVfs->mxPathname+1;
  char *zFullPathname = sqlite3Malloc(MAX(nFullPathname,nFilename));
  MUTEX_LOGIC( sqlite3_mutex *mutexShared; )

  p->sharable = 1;
  if( !zFullPathname ){
sqlite3_free(p);
return SQLITE_NOMEM_BKPT;
  }
  if( isMemdb ){
memcpy(zFullPathname, zFilename, nFilename);
  }else{
rc = sqlite3OsFullPathname(pVfs, zFilename,
   nFullPathname, zFullPathname);
if( rc ){
  sqlite3_free(zFullPathname);
  sqlite3_free(p);
  return rc;
}
  }
#if SQLITE_THREADSAFE
  mutexOpen = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_OPEN);
  sqlite3_mutex_enter(mutexOpen);
  mutexShared = sqlite3MutexAlloc(SQLITE_MUTEX_STATIC_MASTER);
  sqlite3_mutex_enter(mutexShared);
#endif

The whole issue then revolves around the SHARED_CACHE/PRIVATE_CACHE and not the 
SERIALIZED/MULTITHREADED mode. Chances are that if OP Brian tries the same 
scenario with PRIVATE_CACHE connections, the issue will not occur.

I think this would probably qualify for an inherent limitation, which might be 
documented, but not something necessarily undesirable.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om



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

Re: [sqlite] Bug Report: All database opening blocked awaiting wal index rebuild

[Simon Slavin]

On 9 Jul 2016, at 9:27am, Olivier Mascia  wrote:

> I'm really interested in knowing wether you use the engine in SERIALIZED or 
> MULTITHREADED mode during this event reproduction?

In other words, please read the last part of



and try to reproduce your problem in "Multi-thread" mode.  If the problem still 
occurs, please try again in "Serialized" mode.

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

Re: [sqlite] Bug Report: All database opening blocked awaiting wal index rebuild

[Olivier Mascia]

> Le 9 juil. 2016 à 00:55, Brian Vincent  a écrit :
> 
> I've managed to reproduce a scenario where one database, database1.db is
> rebuilding a large wal-index.  Meanwhile, all other threads attempting to
> open completely unrelated databases (e.g. database2.db) are blocked until
> the wal-index on database1.db is finished rebuilding.  This is obviously
> undesirable.

It probably is.  *I* can't comment or recommend anything on what you described 
but I'm really interested in knowing wether you use the engine in SERIALIZED or 
MULTITHREADED mode during this event reproduction? And assuming it is the 
default mode SERIALIZED which is used, wether it is different when run in 
MULTITHREADED mode (in which the engine code is supposed to do much less 
contention between threads, in exchange for the app responsibility not to share 
any connection (and dependent structures like statements) between threads.

-- 
Meilleures salutations, Met vriendelijke groeten, Best Regards,
Olivier Mascia, integral.be/om



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

Re: [sqlite] bug report (security)

[Jonathan Brossard]

Hi Richard,

Thanks for getting back to me.
I agree it seems like the same bug.

Best regards,

j-


On Sat, May 28, 2016 at 4:19 AM, Richard Hipp  wrote:

> On 5/27/16, Jonathan Brossard  wrote:
> >
> > I'd like to report a heap overflow, please find an advisory attached to
> > this email.
> >
>
> Thanks of the bug report.
>
> I am unable to reproduce the heap overflow.  Nevertheless, it looks
> like this problem may have been addressed by checkin [e018f4bf1f] on
> 2015-04-15 (https://www.sqlite.org/src/timeline?c=e018f4bf1f).  Note
> that this issue is in the SQLite command-line shell program and not in
> SQLite itself.
>
> --
> 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 (security)

[Richard Hipp]

On 5/27/16, Jonathan Brossard  wrote:
>
> I'd like to report a heap overflow, please find an advisory attached to
> this email.
>

Thanks of the bug report.

I am unable to reproduce the heap overflow.  Nevertheless, it looks
like this problem may have been addressed by checkin [e018f4bf1f] on
2015-04-15 (https://www.sqlite.org/src/timeline?c=e018f4bf1f).  Note
that this issue is in the SQLite command-line shell program and not in
SQLite itself.

-- 
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 (2/2) [security]

[Richard Hipp]

On 5/27/16, Jonathan Brossard  wrote:
>
> Please find attached a second bug report for a use after free() in sqlite.
>

Thank you for the bug report.

This problem (in the command-line shell, not in SQLite itself) has
already been fixed, specifically by check-in [fc4f4d1e] on 2015-06-17
(https://www.sqlite.org/src/timeline?c=fc4f4d1e) and released in
SQLite version 3.8.11 on 2015-07-27.


-- 
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: USBAN failure

[Scott Robison]

On Wed, Dec 3, 2014 at 5:46 PM, Simon Slavin  wrote:

>
> On 4 Dec 2014, at 12:26am, James K. Lowden 
> wrote:
>
> > What to do is another question.  SQLite can surely ignore it.  If I
> > felt strongly about it, I'd submit a bug report to GCC because IIUC
> > the nonnull attribute syntax provides no way to express the constraint
> > defined by the standard, i.e. "__n > 0 || __nonnull ((1, 2))".
>
> But that's not what the standard says.  My understanding of the standard
> is that both source and destination must be legitimate memory locations.
> It says nothing about the value of n.  In other words memcpy() may well
> look at the source and destination locations before discovering that n == 0
> so it doesn't have to do anything. [1]  It's clear even in the first
> standard Clemens quoted.
>

That is the C99 standard. C90 did not have that restriction. A lot of
projects target C90 for maximum portability. In this particular case it
does not make SQLite incompatible with C90 to accommodate C99, so it has
been accommodated. Still, I would maintain that the standard being used was
not being violated in this case.

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

Re: [sqlite] Bug report: USBAN failure

[Simon Slavin]

On 4 Dec 2014, at 12:26am, James K. Lowden  wrote:

> What to do is another question.  SQLite can surely ignore it.  If I
> felt strongly about it, I'd submit a bug report to GCC because IIUC
> the nonnull attribute syntax provides no way to express the constraint
> defined by the standard, i.e. "__n > 0 || __nonnull ((1, 2))".  

But that's not what the standard says.  My understanding of the standard is 
that both source and destination must be legitimate memory locations.  It says 
nothing about the value of n.  In other words memcpy() may well look at the 
source and destination locations before discovering that n == 0 so it doesn't 
have to do anything. [1]  It's clear even in the first standard Clemens quoted. 
 

In this it appears I agree with Clemens' interpretation, and with the notation 
in the function definitions: source and dest .  I don't know what this says 
about SQLite's code.  Is it the custom these days to include clear and visible 
null checks in C code even if the programmer(s) know they can never be 
triggered ?

Simon.

[1] A naïve view of optimization is that memcpy() should check for (n == 0) 
first so it can return quickly, but this check slows down (n != 0) use of the 
function and that makes me ask why your code keeps calling memcpy() for zero 
bytes often enough for the check to give a significant advantage.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: USBAN failure

[James K. Lowden]

On Wed, 03 Dec 2014 08:56:44 +0100
Clemens Ladisch  wrote:

> James K. Lowden wrote:
> > /* Copy N bytes of SRC to DEST.  */
> > extern void *memcpy (void *__restrict __dest,
> >  __const void *__restrict __src, size_t __n)
> >  __THROW __nonnull ((1, 2));
> >
> > IIUC the declaration specifies the pointer cannot be NULL and the
> > compiler generates a diagnostic if it notices that it can be.  But
> > the declaration is strictly stricter than the standard defines.
> 
> Do you have a standard that allows NULL?  The one I quoted does not.

I'm sure you're right about the standard.  I merely observed that my
not-too-recent copy of strings.h includes a nonnull attribute.
Strictly speaking, that attribute may be "wrong" in the sense that the
standard does permit the pointer to be NULL if the length is zero.  A
compiler may nevertheless emit a diagnostic when it comes across one.
For gcc that's triggered with -Wnonnull.  I think that might explain the
message the OP saw.  

What to do is another question.  SQLite can surely ignore it.  If I
felt strongly about it, I'd submit a bug report to GCC because IIUC
the nonnull attribute syntax provides no way to express the constraint
defined by the standard, i.e. "__n > 0 || __nonnull ((1, 2))".  

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

Re: [sqlite] Bug report: USBAN failure

[Scott Robison]

On Wed, Dec 3, 2014 at 9:50 AM, Stephan Beal  wrote:

> On Wed, Dec 3, 2014 at 5:35 PM, Scott Robison 
> wrote:
>
> > standards have all been ISO standards. Pedantic? Yes. Obviously DRH is
> > willing to make the code more portable as long as it doesn't violate
> > ANSI-C, hence his patch early in the thread (see
> > https://www.sqlite.org/src/info/0d04f380e1bd17104b3cf76b64d0cfc79a726606
> ).
> >
>
> Just to harmlessly nitpick: sqlite (necessarily) uses long long for int64,
> which isn't strictly C89: but works on essentially every compiler.
>

Fair enough, though I think the selection of long long is wrapped up in
conditionals, isn't it? Conditional blocks are there for this purpose! :)

{checks} Yes:

#ifdef SQLITE_INT64_TYPE

  typedef SQLITE_INT64_TYPE sqlite_int64;

  typedef unsigned SQLITE_INT64_TYPE sqlite_uint64;

#elif defined(_MSC_VER) || defined(__BORLANDC__)

  typedef __int64 sqlite_int64;

  typedef unsigned __int64 sqlite_uint64;

#else

  typedef long long int sqlite_int64;

  typedef unsigned long long int sqlite_uint64;

#endif

typedef sqlite_int64 sqlite3_int64;

typedef sqlite_uint64 sqlite3_uint64;


It only falls to long long if nothing better has been provided or found.

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

Re: [sqlite] Bug report: USBAN failure

[Stephan Beal]

On Wed, Dec 3, 2014 at 5:35 PM, Scott Robison 
wrote:

> standards have all been ISO standards. Pedantic? Yes. Obviously DRH is
> willing to make the code more portable as long as it doesn't violate
> ANSI-C, hence his patch early in the thread (see
> https://www.sqlite.org/src/info/0d04f380e1bd17104b3cf76b64d0cfc79a726606).
>

Just to harmlessly nitpick: sqlite (necessarily) uses long long for int64,
which isn't strictly C89: but works on essentially every compiler.

gcc -c -pedantic -std=c89 -Wall -Werror sqlite3.c
sqlite3.c:377:16: error: ISO C90 does not support ‘long long’
[-Werror=long-long]
   typedef long long int sqlite_int64;
^
sqlite3.c:378:25: error: ISO C90 does not support ‘long long’
[-Werror=long-long]
   typedef unsigned long long int sqlite_uint64;
 ^
cc1: all warnings being treated as errors


clang -c -pedantic -std=c89 -Wall -Werror sqlite3.c
sqlite3.c:377:11: error: 'long long' is an extension when C99 mode is not
enabled [-Werror,-Wlong-long]
  typedef long long int sqlite_int64;
  ^
sqlite3.c:378:20: error: 'long long' is an extension when C99 mode is not
enabled [-Werror,-Wlong-long]
  typedef unsigned long long int sqlite_uint64;


which can be squelched with:

gcc|clang -c -pedantic -std=c89 -Wall -Werror -Wno-long-long sqlite3.c

(clang now reports an unused var, but that's something else.)


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: USBAN failure

[Scott Robison]

On Wed, Dec 3, 2014 at 2:17 AM, Clemens Ladisch  wrote:

> Scott Robison wrote:
> > On Dec 3, 2014 12:57 AM, "Clemens Ladisch"  wrote:
> >> Do you have a standard that allows NULL?  The one I quoted does not.
> >
> > Note: I'll have to double check my copy of the C90 standard document, but
> > my re-reading of the C99 quote leads me to the conclusion that NULL is a
> > valid pointer by definition.
>
> Then we have to go reference chasing:
>
> C99, 7.21.1:
> | Unless explicitly stated otherwise in the description of a particular
> | function in this subclause, pointer arguments on such a call shall
> | still have valid values, as described in 7.1.4.
>
> 7.1.4:
> | Each of the following statements applies unless explicitly stated
> | otherwise in the detailed descriptions that follow: If an argument to
> | a function has an invalid value (such as ... a null pointer ...) ...,
> | the behavior is undefined.
>

Okay, fair enough. I'm comparing the C90 & C90 (2005) standards at the
moment. The equivalent clause in C90 7.11, but in C90, it omits the second
paragraph (from which the quote was extracted). So C90 did not prohibit
NULL values beyond their implicit undefined behavior on dereference as per
6.3.3.2 (Address and indirection operators: "... If an invalid value has
been assigned to the pointer, the behavior of the unary * operator is
undefined.[42]" and ("[42] ... Among the invalid values for dereferencing a
pointer by the unary * operator are a null pointer ...").

I can certainly appreciate why the committee opted to make the change in
C99, though in this case it seems that (at least) memcpy & memset *should*
(but don't) have exceptions for those functions, since there is never a
need to dereference a pointer if the size of the block is zero. Other
"real" string functions (like strncpy) have the extra requirement of
finding a null terminator, so I can understand why a NULL source pointer
would be undefined behavior there, but not memcpy.

In any case, "SQLite is ANSI-C source code" (as per
http://www.sqlite.org/howtocompile.html). The only C standard that was
developed by ANSI was C89. C90 (almost identical to C89) and subsequent
standards have all been ISO standards. Pedantic? Yes. Obviously DRH is
willing to make the code more portable as long as it doesn't violate
ANSI-C, hence his patch early in the thread (see
https://www.sqlite.org/src/info/0d04f380e1bd17104b3cf76b64d0cfc79a726606).

Perhaps the R language project just needs to pick the "correct" dialect for
GCC (-std=C89 seems appropriate) when compiling the amalgamation? Might not
help if the UBSan markup in the header doesn't change based on the dialect
in use, but it might permit SQLite to compile cleanly. Unless it's already
being done, in which case I guess not. And it's moot, since it has been
accommodated in the source.

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

Re: [sqlite] Bug report: USBAN failure

[Clemens Ladisch]

Scott Robison wrote:
> On Dec 3, 2014 12:57 AM, "Clemens Ladisch"  wrote:
>> Do you have a standard that allows NULL?  The one I quoted does not.
>
> Note: I'll have to double check my copy of the C90 standard document, but
> my re-reading of the C99 quote leads me to the conclusion that NULL is a
> valid pointer by definition.

Then we have to go reference chasing:

C99, 7.21.1:
| Unless explicitly stated otherwise in the description of a particular
| function in this subclause, pointer arguments on such a call shall
| still have valid values, as described in 7.1.4.

7.1.4:
| Each of the following statements applies unless explicitly stated
| otherwise in the detailed descriptions that follow: If an argument to
| a function has an invalid value (such as ... a null pointer ...) ...,
| the behavior is undefined.


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

Re: [sqlite] Bug report: USBAN failure

[Scott Robison]

On Dec 3, 2014 12:57 AM, "Clemens Ladisch"  wrote:
>
> Do you have a standard that allows NULL?  The one I quoted does not.

Note: I'll have to double check my copy of the C90 standard document, but
my re-reading of the C99 quote leads me to the conclusion that NULL is a
valid pointer by definition. Dereference of NULL is undefined, but as a
value for a pointer it is defined.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: USBAN failure

[Scott Robison]

On Dec 3, 2014 12:57 AM, "Clemens Ladisch"  wrote:
>
> James K. Lowden wrote:
> > /* Copy N bytes of SRC to DEST.  */
> > extern void *memcpy (void *__restrict __dest,
> >  __const void *__restrict __src, size_t __n)
> >  __THROW __nonnull ((1, 2));
> >
> > IIUC the declaration specifies the pointer cannot be NULL and the
> > compiler generates a diagnostic if it notices that it can be.  But the
> > declaration is strictly stricter than the standard defines.
>
> Do you have a standard that allows NULL?  The one I quoted does not.

*The* standard is completely silent on passing NULL to memcpy or memset
(other than dereference of NULL is undefined behavior). With a size of 0
there will never be a dereference. The library in use is trying to warn you
about potential errors but in this case isn't smart enough to detect the 0
size value.

I can appreciate why the "extra" cautious warning exists given how often
error checking is ignored, but since the ubsan infrastructure works by
instrumenting the code, an assert would arguably be a better approach that
would be smart enough to detect more than the non null attribute is capable
of. That doesn't help the current mistaken impression that there was a
problem with SQLite that didn't really exist. Which is moot since DRH
already worked around the "broken" warning. :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: USBAN failure

[Clemens Ladisch]

James K. Lowden wrote:
> /* Copy N bytes of SRC to DEST.  */
> extern void *memcpy (void *__restrict __dest,
>  __const void *__restrict __src, size_t __n)
>  __THROW __nonnull ((1, 2));
>
> IIUC the declaration specifies the pointer cannot be NULL and the
> compiler generates a diagnostic if it notices that it can be.  But the
> declaration is strictly stricter than the standard defines.

Do you have a standard that allows NULL?  The one I quoted does not.


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

Re: [sqlite] Bug report: USBAN failure

[James K. Lowden]

On Tue, 02 Dec 2014 15:58:47 +0100
Abramo Bagnara  wrote:

> The point is not about overzealousness, but about the declaration of
> memcpy/memset on your machine.
> 
> If it contains the nonnull attribute then (correctly) UBSan detect
> that such constraint is not respected.

Hmm, I guess you mean this (from my handy LTS Ubuntu box):  

/* Copy N bytes of SRC to DEST.  */
extern void *memcpy (void *__restrict __dest,
 __const void *__restrict __src, size_t __n)
 __THROW __nonnull ((1, 2));

(Documentation at 
https://gcc.gnu.org/onlinedocs/gcc/Function-Attributes.html#index-g_t_0040code_007bnonnull_007d-function-attribute-2263.)

IIUC the declaration specifies the pointer cannot be NULL and the
compiler generates a diagnostic if it notices that it can be.  But the
declaration is strictly stricter than the standard defines.  

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

Re: [sqlite] Bug report: USBAN failure

[Hadley Wickham]

>> The block of code that refers to is:
>>
>>   if( p->azVar ){
>> p->nzVar = pParse->nzVar;
>> memcpy(p->azVar, pParse->azVar, p->nzVar*sizeof(p->azVar[0]));
>> memset(pParse->azVar, 0, pParse->nzVar*sizeof(pParse->azVar[0]));
>>   }
>>
>> So maybe the check should be on (pParse->azVar) ?
>>
>
> No, that would result in NULL pointer dereferences following an
> out-of-memory condition.  The correct work-around (it isn't really a bug
> fix) is to test pParse->nzVar in addition to p->azVar.  See
> https://www.sqlite.org/src/info/0d04f380e1bd17104b3cf76b64d0cfc79a726606
> for the change.

Thanks, it's much appreciated!  (And sorry about the USBAN typo)

Hadley

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

Re: [sqlite] Bug report: USBAN failure

[Abramo Bagnara]

Il 02/12/2014 15:02, Richard Hipp ha scritto:
> On Tue, Dec 2, 2014 at 8:53 AM, Dominique Devienne 
> wrote:
> 
>> On Tue, Dec 2, 2014 at 2:47 PM, Richard Hipp  wrote:
>>
>>> On Mon, Dec 1, 2014 at 5:46 PM, Hadley Wickham 
>>> wrote:
 [...] has started running all R packages with USBAN. This reveals a
>>> problem in sqlite.c
>>>
>>> I'm not sure what USBAN is
>>>
>>
>> Most likely a typo. UBSan, Undefined Behavior Sanitizer (from GCC / Clang).
>>
> 
> OK, that makes sense.
> 
> But we've been running 100% branch coverages tests of SQLite using
> -fsanitize=undefined for a long while now (using clang), and we've never
> encountered the issue that Hadley is having.  So some other kind of
> overzealousness is going on here as well, it seems.

The point is not about overzealousness, but about the declaration of
memcpy/memset on your machine.

If it contains the nonnull attribute then (correctly) UBSan detect that
such constraint is not respected.

-- 
Abramo Bagnara

BUGSENG srl - http://bugseng.com
mailto:abramo.bagn...@bugseng.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: USBAN failure

[Richard Hipp]

On Tue, Dec 2, 2014 at 8:53 AM, Dominique Devienne 
wrote:

> On Tue, Dec 2, 2014 at 2:47 PM, Richard Hipp  wrote:
>
> > On Mon, Dec 1, 2014 at 5:46 PM, Hadley Wickham 
> > wrote:
> > > [...] has started running all R packages with USBAN. This reveals a
> > problem in sqlite.c
> >
> > I'm not sure what USBAN is
> >
>
> Most likely a typo. UBSan, Undefined Behavior Sanitizer (from GCC / Clang).
>

OK, that makes sense.

But we've been running 100% branch coverages tests of SQLite using
-fsanitize=undefined for a long while now (using clang), and we've never
encountered the issue that Hadley is having.  So some other kind of
overzealousness is going on here as well, it seems.

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

Re: [sqlite] Bug report: USBAN failure

[Clemens Ladisch]

Richard Hipp wrote:
> But apparently there is an issue in USBAN in that it does not allow calls
> to memcpy() and memset() with NULL pointers even it the count field (the
> third parameter) is zero.  I couldn't find anything in the memcpy() or
> memset() documentation that disallowed this case.

C99, section 7.21.1 (String function conventions):
| Where an argument declared as size_t n specifies the length of the
| array for a function, n can have the value zero on a call to that
| function. Unless explicitly stated otherwise in the description of
| a particular function in this subclause, pointer arguments on such
| a call shall still have valid values


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

Re: [sqlite] Bug report: USBAN failure

[Dominique Devienne]

On Tue, Dec 2, 2014 at 2:47 PM, Richard Hipp  wrote:

> On Mon, Dec 1, 2014 at 5:46 PM, Hadley Wickham 
> wrote:
> > [...] has started running all R packages with USBAN. This reveals a
> problem in sqlite.c
>
> I'm not sure what USBAN is
>

Most likely a typo. UBSan, Undefined Behavior Sanitizer (from GCC / Clang).
--DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: USBAN failure

[Richard Hipp]

On Mon, Dec 1, 2014 at 5:46 PM, Hadley Wickham  wrote:

> Hi,
>
> I'm the maintainer of RSQLite, the R language binding for SQLite.
> Recently, CRAN (the common R archive network) has started running all
> R packages with USBAN. This reveals a problem in sqlite.c
> (
> http://www.stats.ox.ac.uk/pub/bdr/memtests/UBSAN-gcc/RSQLite/tests/testthat.Rout
> )
>
> > library(RSQLite)
> > con <- dbConnect(SQLite(), dbname = tempfile())
> sqlite/sqlite3.c:63931:5: runtime error: null pointer passed as
> argument 2, which is declared to never be null
> sqlite/sqlite3.c:63932:5: runtime error: null pointer passed as
> argument 1, which is declared to never be null
>

I'm not sure what USBAN is, and a quick search using Firefox (which as of
this morning takes me to Yahoo instead of Google) doesn't provide any clues.

But apparently there is an issue in USBAN in that it does not allow calls
to memcpy() and memset() with NULL pointers even it the count field (the
third parameter) is zero.  I couldn't find anything in the memcpy() or
memset() documentation that disallowed this case.  And SQLite has been
doing that for many years on a huge variety of systems without any
problems.  So I think perhaps USBAN (whatever it is) is being a little
overzealous in its constraints.


>
> The block of code that refers to is:
>
>   if( p->azVar ){
> p->nzVar = pParse->nzVar;
> memcpy(p->azVar, pParse->azVar, p->nzVar*sizeof(p->azVar[0]));
> memset(pParse->azVar, 0, pParse->nzVar*sizeof(pParse->azVar[0]));
>   }
>
> So maybe the check should be on (pParse->azVar) ?
>

No, that would result in NULL pointer dereferences following an
out-of-memory condition.  The correct work-around (it isn't really a bug
fix) is to test pParse->nzVar in addition to p->azVar.  See
https://www.sqlite.org/src/info/0d04f380e1bd17104b3cf76b64d0cfc79a726606
for the change.


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

Re: [sqlite] Bug report: typo in sqlite3.h

[Simon Slavin]

On 7 Nov 2014, at 9:18am, Philip Newton  wrote:

> The amalgamation-3080701 sqlite3.h file has a comment that reads, in part:
> 
> ** These no-op macros are used in front of interfaces to mark those
> ** interfaces as either deprecated or experimental.  New applications
> ** should not use deprecated interfaces - they are support for backwards
> ** compatibility only.  Application writers should be aware that
> ** experimental interfaces are subject to change in point releases.
> 
> In that comment, "they are support" should read "they are supported".

Or possibly a clearer phrasing might be "they support".  I'm not sure which the 
current phrasing actually means.

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

Re: [sqlite] Bug Report: Case Sensitive Like

[Richard Hipp]

On Fri, Jun 6, 2014 at 5:26 PM, Martin Abel  wrote:

>
> select Name from Targets where Name like '%üabc%'; -- real UTF-8 chars "ü"
> or alternatively
> select Name from Targets where Name like '%üabc%'; -- real UTF-8 chars,
> ASCII encoded as "ü"
>
> it does not work - no one of the German ä, ö, ü, Ä, Ö, Ü, ß work case
> insensitive with the LIKE operator.
>
> Please try to fix that.
>

Case insensitivity in SQLite only applies to ASCII characters.  This is
because (up until relatively recently) the definition of Unicode case
insensitivity was a moving target - it changing from one release of the
Unicode definition to the next, but SQLite needed to be 100% backwards
compatible at all times.  Unicode now claims to have a fixed and unchanging
definition of case insensitivity, but that only came about with (IIRC)
unicode 6.1.

If you need unicode case insensitivity for LIKE in SQLite, compile with
SQLITE_ENABLE_ICU and then like it with libicu.


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

Re: [sqlite] Bug report: column name includes table alias when CTE is used

[Kevin Benson]

On Mon, Apr 21, 2014 at 8:29 AM, Richard Hipp  wrote:

> On Mon, Apr 21, 2014 at 1:16 AM, Andre  wrote:
>
> > Hi,
> >
> > Apparently when a CTE is used, the column name includes the table alias.
> > However, when no CTE is used, the alias is not present in the returned
> > column name.
> >
> > SQLite version 3.8.4.3 2014-04-03 16:53:12
> > 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 (columnA int);
> > sqlite> insert into X values (1);
> > sqlite> .header on
> > sqlite> select alias.columnA from X alias;
> > *columnA*
> > 1
> > sqlite> with CTE as (select columnA from X) select alias.columnA from CTE
> > alias;
> > *alias.columnA*
> > 1
> > sqlite>
> >
> > I experienced this when rewriting a query to use CTE in an application
> that
> > based some logic on the column name. I'd expect not to see the alias
> either
> > way. Is this a bug or is it expected for CTEs?
> >
>
> See
> http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fiedfor
> further information.
>
>
Fixed this link for myself and future referrers:

http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fied

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: column name includes table alias when CTE is used

[Richard Hipp]

On Mon, Apr 21, 2014 at 1:16 AM, Andre  wrote:

> Hi,
>
> Apparently when a CTE is used, the column name includes the table alias.
> However, when no CTE is used, the alias is not present in the returned
> column name.
>
> SQLite version 3.8.4.3 2014-04-03 16:53:12
> 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 (columnA int);
> sqlite> insert into X values (1);
> sqlite> .header on
> sqlite> select alias.columnA from X alias;
> *columnA*
> 1
> sqlite> with CTE as (select columnA from X) select alias.columnA from CTE
> alias;
> *alias.columnA*
> 1
> sqlite>
>
> I experienced this when rewriting a query to use CTE in an application that
> based some logic on the column name. I'd expect not to see the alias either
> way. Is this a bug or is it expected for CTEs?
>

See http://www.sqlite.org/c3ref/mark/column_name.html?If+there+is+n*fiedfor
further information.

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

Re: [sqlite] Bug Report: Quotes added to table name after ALTER TABLE ... RENAME TO

[Richard Hipp]

On Mon, Apr 21, 2014 at 4:36 AM, Assen Totin  wrote:

> Hi, everybody,
>
> Found something which seems rather inconsistent and may be a bug, hence
> reporting it here.
>
> Running a query to rename a table succeeds, but the name of the renamed
> table is surrounded by double quotes.
>
> sqlite> .schema
> CREATE TABLE version (version INT);
> sqlite> ALTER TABLE version RENAME TO version2
> sqlite> .schema
> CREATE TABLE "version2" (version INT);
>

That is correct behavior.  Any identifier in SQL can be enclosed in
double-quotes.



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

Re: [sqlite] Bug report: column name includes table alias when CTE is used

[Simon Slavin]

On 21 Apr 2014, at 6:16am, Andre  wrote:

> Apparently when a CTE is used, the column name includes the table alias.
> However, when no CTE is used, the alias is not present in the returned
> column name.

SQLite has no standards at all about column names unless you specifically set a 
column name using "AS" in your SELECT.  If you don't use "AS" then SQLite can 
return all sorts of weird things as the column names, even without using CTE.

If you are expecting a specific column name, you're welcome to tell us what you 
expect and why, but so far nobody has come up with any good reason for a 
specific form.

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

Re: [sqlite] Bug report: ORDER BY ignored in presence of GROUP BY and index

[Richard Hipp]

On Sat, Apr 19, 2014 at 11:14 PM, foxlit  wrote:

> Hi,
>
> I recently noticed something similar to the following behaviour:
>
> sqlite> .version
> SQLite 3.8.4.3 2014-04-03 16:53:12 a611fa96c4a848614efe899130359c9f6fb889c3
> sqlite> CREATE TABLE t1 (x, y);
> sqlite> INSERT INTO t1 VALUES (1, 1), (2, 0);
> sqlite> SELECT x, y FROM t1 GROUP BY x, y ORDER BY x,y;
> 1|1
> 2|0
> sqlite> CREATE INDEX i1 ON t1 (y, x); -- (sic)
> sqlite> SELECT x, y FROM t1 GROUP BY x, y ORDER BY x, y;
> 2|0
> 1|1
>
> The second result appears to be ignoring the ORDER BY clause. Is this a
> bug, or am I missing something obvious?
>

Bug.  I created a ticket here
http://www.sqlite.org/src/tktview/b75a9ca6b0499

The work-around is to add a "+" before one of the terms on the ORDER BY
clause.  Ex:

 SELECT x,y FROM t1 GROUP BY x,y ORDER BY x,+y;

The problem is caused by an optimization (
http://www.sqlite.org/src/artifact/269c3e31a4?ln=4722-4732) that has been
in the code since 2010-04-26 that omits the ORDER BY clause if there is an
identical GROUP BY clause, since GROUP BY is (or at least was) implemented
by sorting as if it were an ORDER BY.  This optimization worked fine until
the next generation query planner (
http://www.sqlite.org/queryplanner-ng.html) was cut over in 2013-06-26.
The NGQP introduced some new ways to handle GROUP BY which made that
optimization no longer valid in some circumstances - one of which you have
just found.  So, this is a case of two separate optimizations interfering
with one another.

Thanks for the bug report.

Oops.  Look like Dan and I entered duplicate tickets.  I'll cancel one of
them



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

Re: [sqlite] Bug Report

[Jim Dodgen]

Ryan, The late Grace Hopper would be happy about your responce

*Jim Dodgen*








On Thu, Apr 17, 2014 at 10:40 AM, RSmith  wrote:

> That is just the compiler trying to be helpful without being able to
> completely grasp the code and can safely be ignored, but thank you for the
> notice and feel free to initialize the variable in your version.
>
> As an aside, that does not qualify as a "bug", the word "bug" means a
> whole other thing - which I am not going to elaborate on since I'm sure it
> was just a thought and not an actual contention.
>
> Have a great day!
> Ryan
>
>
> On 2014/04/17 18:23, jalal Mostafa wrote:
>
>> Hey,
>> I downloaded the "Legacy Source Code Distribution Formats -
>> sqlite-preprocessed-3080403.zip", while compiling in Microsoft Visual
>> Studio 2013 a bug appeared in file "fts3_tokenize_vtab.c","error C4703:
>> potentially uninitialized local pointer variable 'pTab' used"
>> Line:203.Assigning the pointer to NULL will solve the problem.
>> Sincerely,Jalal.
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug Report

[RSmith]

That is just the compiler trying to be helpful without being able to completely grasp the code and can safely be ignored, but thank 
you for the notice and feel free to initialize the variable in your version.


As an aside, that does not qualify as a "bug", the word "bug" means a whole other thing - which I am not going to elaborate on since 
I'm sure it was just a thought and not an actual contention.


Have a great day!
Ryan

On 2014/04/17 18:23, jalal Mostafa wrote:

Hey,
I downloaded the "Legacy Source Code Distribution Formats - sqlite-preprocessed-3080403.zip", while 
compiling in Microsoft Visual Studio 2013 a bug appeared in file "fts3_tokenize_vtab.c","error 
C4703: potentially uninitialized local pointer variable 'pTab' used" Line:203.Assigning the pointer to 
NULL will solve the problem.
Sincerely,Jalal.


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


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

Re: [sqlite] Bug Report - Analyzer app

[RSmith]

Thank you kindly and forgive me for not seeing this earlier...

It works perfectly.


On 2013/11/06 22:53, Richard Hipp wrote:

http://www.sqlite.org/src/info/42a11e7464



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

Re: [sqlite] Bug Report - Analyzer app

[Richard Hipp]

http://www.sqlite.org/src/info/42a11e7464


On Wed, Nov 6, 2013 at 3:48 PM, RSmith  wrote:

> Sorry to be a bother again, but no replies on this thread yet, and I'm
> still getting un-importable result sets from the sqlite3_analyzer.exe
> program.
>
> It's not exactly a high-profile bug, and I could just use a replace macro
> of sorts, but I'd rather it work as intended. I'm thinking this would be an
> easy fix, but may be very wrong - either way, kindly let us know if a fix
> is on the table or not.
>
> Thanks!
> Ryan
>
>
>
> On 2013/11/02 09:53, RSmith wrote:
>
>> Hi there,
>>
>> I use the sqlite3_analyzer.exe app get some data about tables (It's very
>> useful by the way - thanks.)
>>
>> The newest version downloaded some days ago gave me import errors for the
>> produced file, so I tried loading it manually, and then got the SQL which
>> follows for one of my small DB files. The Object names in the Values seem
>> to be unquoted, which is causing the failure for me:
>>
>> /*
>>   Removed lots of non-importing stats above this line  
>> 
>> ***
>> The entire text of this report can be sourced into any SQL database
>> engine for further analysis.  All of the text above is an SQL comment.
>> The data used to generate this report follows:
>> */
>> BEGIN;
>> CREATE TABLE space_used(
>>name clob,-- Name of a table or index in the database file
>>tblname clob, -- Name of associated table
>>is_index boolean, -- TRUE if it is an index, false for a table
>>nentry int,   -- Number of entries in the BTree
>>leaf_entries int, -- Number of leaf entries
>>payload int,  -- Total amount of data stored in this table or index
>>ovfl_payload int, -- Total amount of data stored on overflow pages
>>ovfl_cnt int, -- Number of entries that use overflow
>>mx_payload int,   -- Maximum payload size
>>int_pages int,-- Number of interior pages used
>>leaf_pages int,   -- Number of leaf pages used
>>ovfl_pages int,   -- Number of overflow pages used
>>int_unused int,   -- Number of unused bytes on interior pages
>>leaf_unused int,  -- Number of unused bytes on primary pages
>>ovfl_unused int,  -- Number of unused bytes on overflow pages
>>gap_cnt int,  -- Number of gaps in the page layout
>>compressed_size int  -- Total bytes stored on disk
>> );
>> INSERT INTO space_used VALUES(sqlite_master,sqlite_
>> master,0,14,12,2473,0,0,534,1,3,0,898,520,0,3,4096);
>> INSERT INTO space_used VALUES(DBases,DBases,0,5,5,
>> 644,0,0,181,0,1,0,0,349,0,0,1024);
>> INSERT INTO space_used VALUES(sqlite_autoindex_
>> DBases_1,DBases,1,5,5,64,0,0,14,0,1,0,0,937,0,0,1024);
>> INSERT INTO space_used VALUES(DBTables,DBTables,0,15,
>> 10,4458,0,0,923,1,6,0,977,1589,0,1,7168);
>> INSERT INTO space_used VALUES(DBHistory,DBHistory,0,
>> 0,0,0,0,0,0,0,1,0,0,1016,0,0,1024);
>> INSERT INTO space_used VALUES(DBSettings,DBSettings,
>> 0,7,7,266,0,0,98,0,1,0,0,715,0,0,1024);
>> INSERT INTO space_used VALUES(sqlite_autoindex_
>> DBSettings_1,DBSettings,1,7,7,139,0,0,24,0,1,0,0,856,0,0,1024);
>> INSERT INTO space_used VALUES(Idx_DBases_DBName,
>> DBases,1,5,5,64,0,0,14,0,1,0,0,937,0,0,1024);
>> INSERT INTO space_used VALUES(Idx_DBTables_DBID,
>> DBTables,1,10,10,145,0,0,22,0,1,0,0,841,0,0,1024);
>> INSERT INTO space_used VALUES(Idx_DBHistory,
>> DBHistory,1,0,0,0,0,0,0,0,1,0,0,1016,0,0,1024);
>> COMMIT;
>>
>> 
>> ---
>>
>> Importing this gets the usual "no such column: sqlite_master" error, but
>> it all works well if I go add quotes everywhere.
>> If it is already fixed, or if there is something I'm doing wrong, kindly
>> point me in the right direction.
>> Thank you
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Bug Report - Analyzer app

[RSmith]

Sorry to be a bother again, but no replies on this thread yet, and I'm still getting un-importable result sets from the 
sqlite3_analyzer.exe program.


It's not exactly a high-profile bug, and I could just use a replace macro of sorts, but I'd rather it work as intended. I'm thinking 
this would be an easy fix, but may be very wrong - either way, kindly let us know if a fix is on the table or not.


Thanks!
Ryan


On 2013/11/02 09:53, RSmith wrote:

Hi there,

I use the sqlite3_analyzer.exe app get some data about tables (It's very useful 
by the way - thanks.)

The newest version downloaded some days ago gave me import errors for the produced file, so I tried loading it manually, and then 
got the SQL which follows for one of my small DB files. The Object names in the Values seem to be unquoted, which is causing the 
failure for me:


/*
  Removed lots of non-importing stats above this line  
***
The entire text of this report can be sourced into any SQL database
engine for further analysis.  All of the text above is an SQL comment.
The data used to generate this report follows:
*/
BEGIN;
CREATE TABLE space_used(
   name clob,-- Name of a table or index in the database file
   tblname clob, -- Name of associated table
   is_index boolean, -- TRUE if it is an index, false for a table
   nentry int,   -- Number of entries in the BTree
   leaf_entries int, -- Number of leaf entries
   payload int,  -- Total amount of data stored in this table or index
   ovfl_payload int, -- Total amount of data stored on overflow pages
   ovfl_cnt int, -- Number of entries that use overflow
   mx_payload int,   -- Maximum payload size
   int_pages int,-- Number of interior pages used
   leaf_pages int,   -- Number of leaf pages used
   ovfl_pages int,   -- Number of overflow pages used
   int_unused int,   -- Number of unused bytes on interior pages
   leaf_unused int,  -- Number of unused bytes on primary pages
   ovfl_unused int,  -- Number of unused bytes on overflow pages
   gap_cnt int,  -- Number of gaps in the page layout
   compressed_size int  -- Total bytes stored on disk
);
INSERT INTO space_used 
VALUES(sqlite_master,sqlite_master,0,14,12,2473,0,0,534,1,3,0,898,520,0,3,4096);
INSERT INTO space_used 
VALUES(DBases,DBases,0,5,5,644,0,0,181,0,1,0,0,349,0,0,1024);
INSERT INTO space_used 
VALUES(sqlite_autoindex_DBases_1,DBases,1,5,5,64,0,0,14,0,1,0,0,937,0,0,1024);
INSERT INTO space_used 
VALUES(DBTables,DBTables,0,15,10,4458,0,0,923,1,6,0,977,1589,0,1,7168);
INSERT INTO space_used 
VALUES(DBHistory,DBHistory,0,0,0,0,0,0,0,0,1,0,0,1016,0,0,1024);
INSERT INTO space_used 
VALUES(DBSettings,DBSettings,0,7,7,266,0,0,98,0,1,0,0,715,0,0,1024);
INSERT INTO space_used 
VALUES(sqlite_autoindex_DBSettings_1,DBSettings,1,7,7,139,0,0,24,0,1,0,0,856,0,0,1024);
INSERT INTO space_used 
VALUES(Idx_DBases_DBName,DBases,1,5,5,64,0,0,14,0,1,0,0,937,0,0,1024);
INSERT INTO space_used 
VALUES(Idx_DBTables_DBID,DBTables,1,10,10,145,0,0,22,0,1,0,0,841,0,0,1024);
INSERT INTO space_used 
VALUES(Idx_DBHistory,DBHistory,1,0,0,0,0,0,0,0,1,0,0,1016,0,0,1024);
COMMIT;

---

Importing this gets the usual "no such column: sqlite_master" error, but it all 
works well if I go add quotes everywhere.
If it is already fixed, or if there is something I'm doing wrong, kindly point 
me in the right direction.
Thank you


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


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

Re: [sqlite] Bug report.

[Richard Hipp]

On Fri, Oct 11, 2013 at 7:57 AM, Alexander Syvak wrote:

> Hello,
>
> I am using the @ as a column name delimeter. For instance, if there's a
> table named woman, then a column describing the size of hips should be
> named woman@hip_size.
>
> There's a table created using
> CREATE TABLE country
> (
> 'country@id' INTEGER,
> 'country@name' TEXT,
> 'country@printable'_name TEXT,
>

Either quote the entire name or none of it.  The fact that you ended the
quote mid-name causes the name to be parsed as two separate tokens:

  "country@printable"  _name  TEXT,

Hence the column name is "country@printable" and the datatype is "_name
TEXT".

Also, you should be using double-quotes not single-quotes for quoting
column names.




> 'country@iso2' TEXT,
> 'country@iso3' TEXT,
> 'country@numcode' INTEGER,
> CONSTRAINT PK_country PRIMARY KEY ('country@id')
> )
>
> After execution of the next query
> pragma table_info('country');
> Sqliteman 1.2.2 yields
> 
> 
> Sqliteman export
> 
> 
> 
>
> cidnametypenotnulldflt_valuepk
> 0country@id
> INTEGER01
> 1country@name
> TEXT00
> 2country@printable_name
> TEXT00
> 3country@iso2
> TEXT00
> 4country@iso3
> TEXT00
> 5country@numcode
> INTEGER00
> 
> 
> 
> As you can see country@printable has the type _name TEXT.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

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

[Richard Hipp]

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

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

Not a concern.

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



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



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

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

[Brian Vincent]

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

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

-Brian Vincent



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

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

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

[Richard Hipp]

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

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

Thanks.  Fixed in http://www.sqlite.org/src/info/c3baca99f4 including a
test case.


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

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

[Stephan Beal]

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

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


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

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

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

[Warren Young]

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

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


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

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

Re: [sqlite] BUG REPORT: function instr() with chinese characters

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/07/13 01:20, ?? wrote:
> I am using the sqlite-shell-win32-x86-3071700, in WindowsXP SP3

I did the same queries on Linux and got very different answers that appear
to be correct.  I'd suggest using SQLite programmatically since the
Windows console isn't unicode.

SQLite version 3.7.15.2 (APSW 3.7.14.1-r1)
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a (b int, c int);
sqlite> insert into a values(11,22);
sqlite> select instr('', '') as x from a;
1
sqlite> select instr('', '') as x from a;
3
sqlite> select instr('', '') as x from a;
5
sqlite> select instr('', '') as x from a;
7

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.12 (GNU/Linux)

iEYEARECAAYFAlHYfTQACgkQmOOfHg372QTYygCfXW3MQtF9tHCnG9/PIHTRwBjt
a8sAnRo2/4DkqjfDfanykDwk0GG9tcIw
=dEpl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug report: query parser should trigger an error if a subquery selects unknown columns from table

[Hick Gunter]

Because then you would lose automatic matching of unique field names everywhere 
else...

-Ursprüngliche Nachricht-
Von: Thomas Krueger [mailto:tom.krue...@gmail.com]
Gesendet: Donnerstag, 02. Mai 2013 13:15
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Bug report: query parser should trigger an error if a 
subquery selects unknown columns from table

I ran into a similar issue:

DELETE FROM ATable  WHERE EXISTS(SELECT 1 FROM  TMPTable AS  t WHERE id =
t.id) ;

Syntactically I was expecting id to be the ATable.id as I had aliased the 
TMPTable with t. But the result was a non-correlated subquery, id = t.id was 
always true. The fix is clear, yet a bit unintuitive in this circumstance: Why 
not request that all aliased tables use their table alias?



On Thu, May 2, 2013 at 12:41 AM, Richard Hipp  wrote:

> On Wed, May 1, 2013 at 3:23 PM, Anderson Medeiros Gomes
> wrote:
>
> > Hi. I think I found a bug in SQLite, so I'm reporting it in this message.
> >
> > The print screen I have attached shows a query that SQLite executes
> > and brings no results. I believe SQLite should trigger an error
> > while parsing my input, because I used an unknown column in the subquery.
> >
>
> SQLite is giving the correct response here.  The "foocolumn" in the
> subquery refers out to the containing query.  We say that the subquery
> is a "correlated subquery" because it contains references to the outer query.
>
> In your case, the query is logically equivalent to:
>
>SELECT foocolumn FROM footable WHERE 123 NOT IN (SELECT 123 FROM
> bartable);
>
> Since bartable is not empty, the NOT EXISTS is always false and the
> query returns no rows.
>
>
>
> >
> > This is the print screen's textual representation:
> >
> > $ sqlite3 /tmp/test.sqlite
> > SQLite version 3.7.16.2 2013-04-12 11:52:43 Enter ".help" for
> > instructions Enter SQL statements terminated with a ";"
> > sqlite> CREATE TABLE footable (foocolumn INTEGER); CREATE TABLE
> > sqlite> bartable (barcolumn INTEGER); INSERT INTO footable
> > sqlite> (foocolumn) VALUES (1); INSERT INTO bartable (barcolumn)
> > sqlite> VALUES (2); *SELECT foocolumn FROM footable WHERE foocolumn
> > sqlite> NOT IN (SELECT
> > foocolumn FROM bartable);*
> > sqlite> exit
> >...> ;
> > Error: near "exit": syntax error
> > sqlite> .quit
> >
> >
> >
> > --
> > Anderson Medeiros Gomes
> > amg1...@gmail.com
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users