Re: [sqlite] Potential problem in SQLite 3.7.16.2

2013-05-01 Thread Mario M. Westphal
Thank you for providing this pre-release amalgamation ;-) I downloaded it immediately and compiled it into my software. The problem has been ++resolved++ and the performance is at least as good as with previous versions of SQLite. It even feels a bit faster, although I only could try it with a

[sqlite] Slowness with FTS Queries and non-FTS tables

2013-05-01 Thread Paul Vercellotti
Hi All, We've got some trouble with FTS4 queries taking too long in which we're looking for a subset of matching records in the FTS table, as narrowed by a non-FTS table. CREATE TABLE metadata( key AS INTEGER PRIMARY KEY, sectionID AS INTEGER ); CREATE INDEX sectionIdx on

Re: [sqlite] Query optimizer bug?

2013-05-01 Thread Kevin Benson
> > On Tue, 30 Apr 2013 12:59:17 -0400 > Richard Hipp wrote: > > > http://www.sqlite.org/draft/queryplanner-ng.html > > Just a "heads-up" that the paragraph has a couple of accidental transpositions: *IS* "TCP-H Q8" is a test query from the Transaction Processing Performance

[sqlite] Timezone is supported by date/time functions but is not documented

2013-05-01 Thread gwenn
Hello, SQLite datetime function correctly parses timestring with timezone: sqlite> select datetime('2013-04-30T18:38:54Z'); 2013-04-30 18:38:54 sqlite> select datetime('2013-04-30T20:38:54+02:00'); 2013-04-30 18:38:54 But this is not documented: http://sqlite.org/lang_datefunc.html

[sqlite] How to link the packet in C file then store in sqlite database?

2013-05-01 Thread Newbie89
Here is the c file code I need to link and store in database: struct Packet { char Src_MAC[18], Dest_MAC[18]; char Net_P[5],Trans_P[5]; char Src_IP[16], Dest_IP[16]; long int Src_Port,Dest_Port, Cap_Bytes; //[ long int Range: −2,147,483,648 to 2,147,483,647] }; struct Packet Pack[6]; void

Re: [sqlite] How to link the packet in C file then store in sqlite database?

2013-05-01 Thread Fehmi Noyan ISI
This seems to be a C programming question rather than sqlite related issue. You even did not mention which information you would like to store, we only have a code snippet that processes a captured network packet. something like this would work sqlite3_prepare_v2() // use a template with ?

Re: [sqlite] Slowness with FTS Queries and non-FTS tables

2013-05-01 Thread Dan Kennedy
On Tue, 30 Apr 2013 23:56:35 -0700 (PDT) Paul Vercellotti wrote: > > > > Hi All, > > We've got some trouble with FTS4 queries taking too long in which > we're looking for a subset of matching records in the FTS table, as > narrowed by a non-FTS table. > > CREATE TABLE

Re: [sqlite] Query optimizer bug?

2013-05-01 Thread Richard Hipp
On Wed, May 1, 2013 at 12:42 AM, James K. Lowden wrote: > On Tue, 30 Apr 2013 12:59:17 -0400 > Richard Hipp wrote: > > > http://www.sqlite.org/draft/queryplanner-ng.html > > Feel free to use this version of the diagram. > >

Re: [sqlite] How to link the packet in C file then store in sqlite database?

2013-05-01 Thread Newbie89
Fehmi Noyan ISI wrote > This seems to be a C programming question rather than sqlite related > issue. > > You even did not mention which information you would like to store, we > only have a code snippet that processes a captured network packet. > > something like this would work > >

[sqlite] Convert mysql to sqllite with tooling

2013-05-01 Thread John Ophof
I am new in the field of sqllite and want to convert my MySQL db to mysql using tooling. I try to use https://gist.github.com/esperlu/943776 When I place my dump file and the sh file to /Applications/mqsql2sqllite/ and try to run it with terminal. I got command not found or permission denied. I

Re: [sqlite] How to link the packet in C file then store in sqlite database?

2013-05-01 Thread fnoyanisi
I cannot understand "So I need to create a table Packet right?", but you do need to create a table first. You may need to bother yourself by reading the API reference for the functions I gave. On 01/05/2013, at 9:09 PM, Newbie89 wrote: > So I need to create a table

Re: [sqlite] Segmentation fault in SQLite parser

2013-05-01 Thread Klaas V
Eelco wrote:  Using version 3.7.16.2 select * from ((select * from x)) y; Segmentation fault On OSX we get 'Bus error'. Not a bug, because you were syntactical not exact enough. See http://www.sqlite.org/lang_select.html  You're supposed to use only one pair () not more. Before 3.7.15 or 16

Re: [sqlite] Segmentation fault in SQLite parser

2013-05-01 Thread Richard Hipp
On Wed, May 1, 2013 at 9:27 AM, Klaas V wrote: > Eelco wrote: > Using version 3.7.16.2 > select * from ((select * from x)) y; > Segmentation fault > > > On OSX we get 'Bus error'. Thanks for the report. This bug has already been fixed. See

[sqlite] Performance issue with JOIN and large IN operator

2013-05-01 Thread Martin Altmayer
Hi, I have a query that runs more than 400x slower in 3.7.16.2 than in 3.7.11. Instead of posting the original query, I post a simplified version which still experiences the problem with a factor of over 100x: SELECT COUNT(*) FROM elements JOIN tags ON elements.id = tags.element_id WHERE

Re: [sqlite] Performance issue with JOIN and large IN operator

2013-05-01 Thread Richard Hipp
On Wed, May 1, 2013 at 8:30 AM, Martin Altmayer < martin.altma...@googlemail.com> wrote: > Hi, > > I have a query that runs more than 400x slower in 3.7.16.2 than in 3.7.11. > This seems to be caused by the use of transitive constraints in version 3.7.16. Your work-around (until an official fix

Re: [sqlite] Performance issue with JOIN and large IN operator

2013-05-01 Thread Richard Hipp
On Wed, May 1, 2013 at 11:24 AM, Richard Hipp wrote: > > > On Wed, May 1, 2013 at 8:30 AM, Martin Altmayer < > martin.altma...@googlemail.com> wrote: > >> Hi, >> >> I have a query that runs more than 400x slower in 3.7.16.2 than in 3.7.11. >> > > This seems to be caused by the

Re: [sqlite] Performance issue with JOIN and large IN operator

2013-05-01 Thread jic
"Richard Hipp" wrote... On Wed, May 1, 2013 at 11:24 AM, Richard Hipp wrote: On Wed, May 1, 2013 at 8:30 AM, Martin Altmayer < martin.altma...@googlemail.com> wrote: Hi, I have a query that runs more than 400x slower in 3.7.16.2 than in 3.7.11. This seems to be

Re: [sqlite] Performance issue with JOIN and large IN operator

2013-05-01 Thread Richard Hipp
On Wed, May 1, 2013 at 2:11 PM, jic wrote: > "Richard Hipp" wrote... > > Dr. Hipp, > > will this fix break the work-around you provided, > > " > to put a "+" sign in front of the "elements.id" identifier in the ON > clause: > > SELECT count(*) FROM elements JOIN tags ON

[sqlite] How people test db failures

2013-05-01 Thread Igor Korot
Hi, ALL, I wrote a code that executes fine. There is no memory leaks and no issues. However, trying to check whether my error handling is written correctly, I set breakpoint before accessing db and when I hit it I manually changes the value returned. This is not how it should be done and I feel

Re: [sqlite] Query optimizer bug?

2013-05-01 Thread James K. Lowden
On Wed, 1 May 2013 07:02:38 -0400 Richard Hipp wrote: > On Wed, May 1, 2013 at 12:42 AM, James K. Lowden > wrote: > > > On Tue, 30 Apr 2013 12:59:17 -0400 > > Richard Hipp wrote: > > > > >

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

2013-05-01 Thread Anderson Medeiros Gomes
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. This is the

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

2013-05-01 Thread Nico Williams
On Wed, May 1, 2013 at 2:23 PM, Anderson Medeiros Gomes wrote: > Hi. I think I found a bug in SQLite, so I'm reporting it in this message. I do't think it's a bug. Correlated sub-queries can refer to columns from table sources outside them. Your example query is silly, no

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

2013-05-01 Thread Richard Hipp
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

[sqlite] Is _reset() removes binding?

2013-05-01 Thread Igor Korot
Hi, ALL, Consider this code: int var1 = 5, var2 = 10; query = "SELECT * FROM foo WHERE foo.a = ? AND foo.b = ?;"; if( ( result = sqlite3_prepare_v2( handle, query, -1, , 0 ) ) == SQLITE_OK ) { sqlite3_bind_int( stmt, 1, var1 ); for( int i = 0; i < 10; i++ ) {

Re: [sqlite] How people test db failures

2013-05-01 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 01/05/13 12:00, Igor Korot wrote: > So, how people check whether db failure result in graceful program > termination/proper flow? I use a macro that takes this form: #define TESTPOINT(name, normal, failure) I use it like this:

Re: [sqlite] Is _reset() removes binding?

2013-05-01 Thread Igor Tandetnik
On 5/1/2013 7:30 PM, Igor Korot wrote: 1. Is the call to sqlite3_reset() removes the binding for the column a? No. You can use sqlite3_clear_bindings for that. 2. Do I need to call sqlite3_reset() at all? Yes. You can't bind parameters to an active statement. -- Igor Tandetnik