Re: [sqlite] Crash in libsqlite3 with sqlite-3.21.0 and KDE plasmashell

On 1/19/18, Leonard Lausen wrote: > > A crash in sqlite crashes plasmashell. Downstream bug > https://bugs.kde.org/show_bug.cgi?id=388140. Please find the backtrace > below: > (1) These kinds of things are almost always the result of heap corruption in the application. In other words, there is n

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

I missed that part of the documentation, thanks On 19 January 2018 at 07:51, Rowan Worth wrote: > https://www.sqlite.org/withoutrowid.html > > "NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID > table." > > It goes on to say that NOT NULL is supposed to be enforced on a

[sqlite] Crash in libsqlite3 with sqlite-3.21.0 and KDE plasmashell

A crash in sqlite crashes plasmashell. Downstream bug https://bugs.kde.org/show_bug.cgi?id=388140. Please find the backtrace below: -- Backtrace: Application: Plasma (plasmashell), signal: Segmentation fault Using host libthread_db library "/lib64/libthread_db.so.1". [Current thread is 1 (Thread

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

NO. I wrote that to rule out the objection that PostrgeSQL is getting the right answer because random() is only being computed once per statement. I naturally DO expect random() to run every time it is called. What I wrote is a formality for those thinking ahead about what else could be happening.

Re: [sqlite] Defect: single row table cross join causes infinite loop

This also works as expected in PostreSQL but not in SQLite. WITH RECURSIVE params(n) AS ( VALUES (5) ), coinflip(flip,side) AS ( SELECT 1, random()>0.5 UNION ALL SELECT flip+1, random()>0.5 FROM coinflip ) SELECT flip,side FROM coinflip,params WHERE flip%n=0 LIMIT 3; flip sid

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

https://www.sqlite.org/withoutrowid.html "NOT NULL is enforced on every column of the PRIMARY KEY in a WITHOUT ROWID table." It goes on to say that NOT NULL is supposed to be enforced on all PRIMARY KEY columns of _every_ table according to the SQL standard, but an early version of sqlite include

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

On 19 Jan 2018, at 5:04am, petern wrote: > WITH flips(s) AS (VALUES (random()), (random()), (random())) > SELECT * FROM flips; > > s > 0.760850821621716 > 0.9941047639586031 > 0.48273737309500575 Are you expecting the three values to be the same ? What about INSERT INTO MyTable VAKUES (random

[sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

Hello, The following SQL works as I expect - sqlite> CREATE TABLE edges(parent int, child int, primary key(parent, child)); sqlite> insert into edges select null, 1; sqlite> select * from edges; parent child 1 sqlite> but if I remove the superfluous rowid column from the table definitio

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

Good question On 19 January 2018 at 06:04, petern wrote: > WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0)) > SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips; > sum(s),"(SELECT sum(s) FROM flips)" > 1,3 > --Expected output is 1,1. > > Why isn't the constant notional table t

Re: [sqlite] Can a record count column be added to this random hierarchical view?

>I was hoping to find a way to force the query planner to evaluate v_random_hierarchy only once. There is a way with a CTE if the defect I just reported is fixed. Replying to my bug report with your vote to fix the problem can help. I think PostgreSQL, the model for SQLite, has it correct. Once c

Re: [sqlite] Can a record count column be added to this random hierarchical view?

On 19 January 2018 at 05:41, petern wrote: > Were you expecting random() to return the same sequence when the view > materialized again in the subquery? > I was hoping to find a way to force the query planner to evaluate v_random_hierarchy only once. Perhaps this is not possible since it uses th

[sqlite] Defect: Redundant CTE table materialization gives wrong answer.

WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0)) SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips; sum(s),"(SELECT sum(s) FROM flips)" 1,3 --Expected output is 1,1. Why isn't the constant notional table table [flips] materialized just once per CTE? FYI. PostgreSQL 9.6 materi

Re: [sqlite] Can a record count column be added to this random hierarchical view?

Were you expecting random() to return the same sequence when the view materialized again in the subquery? Your ultimate query works fine when the random view is materialized once into a table. CREATE TABLE v_random_hierarchy AS WITH r(parent, child) as (select null, 1 union all select abs(random()

Re: [sqlite] Python program to convert CSV to sqlite

Hi, I've also written a similar script to convert csv to sql (SQLite and PostgreSQL compatible). This script doesn't require fancy external libraries, and will correctly identify data types. 2018-01-18 15:33, Simon Slavin: > csvs-to-

Re: [sqlite] sqlite3_column_name with alias

There is sqlite3_column_origin_name( stmt, n ) ; is that also the alias? instead of sqlite3_column_name(stmt,n ); or does there need to be sqlite3_column_original that really returns the not alias? On Mon, Jan 8, 2018 at 3:21 AM, Bart Smissaert wrote: > > As you can see > > Should read: > As you

[sqlite] Can a record count column be added to this random hierarchical view?

Hello, Here is a view which assigns randomly chosen parents to a sequence of children - CREATE VIEW v_random_hierarchy as with r(parent, child) as (select null, 1 union all select abs(random())%child+1, child+1 from r) select * from r limit 5; sqlite> select * from v_random_hierarchy; parent ch

Re: [sqlite] Speed issue of SELECT in my application

On Wed, 17 Jan 2018 17:59:22 + Simon Slavin wrote: > Folks. Nick published a figure of 60ms for his search. That?s not > unusually slow. There was no request to shave every last millisecond > off that figure. There wasn?t even a statement that it was too > slow. No need to scare the guy

Re: [sqlite] Speed issue of SELECT in my application

> On Jan 17, 2018, at 6:16 PM, Nick wrote: > > Jens, I totally agree with your opinion of profile. I have tried to find some > useful tools to profile applications using sqlite A CPU profiler works on arbitrary code, so it shouldn’t need to know anything about SQLite. (Although some profilers

Re: [sqlite] IN clause

Of the IN clause under PostgreSQL 9.6 (sqlfiddle.com), both syntax variants return true without error: SELECT (1,2) IN ((1,2),(3,4)); SELECT (1,2) IN (VALUES (1,2),(3,4)); A clone of PostgreSQL would also have optional VALUES table alias and column name specifiers as observed earlier. On Thu,

Re: [sqlite] IN clause

>I am open to enhancing the syntax here, but not right now because we are trying to get the 3.22.0 release out - this would need to be during the next cycle. Also, I'll need to check to see what PostgreSQL does first, and emulate them. Yes please! Thank you for getting around to this: (VALUES )

Re: [sqlite] IN clause

W dniu 18.01.2018 o 19:58, Richard Hipp pisze: Try it this way: SELECT * FROM card WHERE (statNumber,question) IN (VALUES(2211,'psuc sei'),(2542,'kontynuowac'), (1449,'wymrzec')); Thanks. This works (with spelling fixes). I am open to enhancing the syntax here, but not right now becau

Re: [sqlite] IN clause

On 1/18/18, Szyk Cech wrote: > Hi > > My concern is about a Primary Key in two columns (integer values) which > I want type explicitly in my query (like in example "Not works"). My > example only shows problem and it is not real case (however database is > real). > > Not works: > > select * from c

[sqlite] IN clause

Hi My concern is about a Primary Key in two columns (integer values) which I want type explicitly in my query (like in example "Not works"). My example only shows problem and it is not real case (however database is real). Not works: select * from card where (statNumber, question) in ((2211

[sqlite] Typo on https://www.sqlite.org/draft/c3ref/c_trace.html

If I'm not mistaken, on https://www.sqlite.org/draft/c3ref/c_trace.html , the sentence "The third argument to sqlite3_trace_v2() is an OR-ed combination..." should read "The second argument...", according to https://www.sqlite.org/draft/c3ref/tra

[sqlite] SQLite V3.21.0 debug symbols

I'm writing an application that is using sqlite in a multi-threaded application and I am experiencing an app crash with an access violation (exception 0xC005) in the sqlite3 dll. I configured sqlite with SQLITE_CONFIG_MULTITHREAD, and I'm using my own mutexes to synchronize concurrent access t

Re: [sqlite] SQLite 3.22.0 coming soon

8 d. Omit unused LEFT JOINs even if they are not the right-most joins of a query. Thanks for fixing this. Working fine for me so far. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.or

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

True, "some" parts of "some" games can be implemented with DB technology, particularly matrix- and graph-based ones. Not only for fast storage and retrieval of game data, but, more interestingly, for implementing complex computation algorithms through SQL queries, that may prove faster than t

Re: [sqlite] Speed issue of SELECT in my application

No worries, I had figured you meant this applied to multiple read statements. RBS On Thu, Jan 18, 2018 at 9:24 AM, R Smith wrote: > > On 2018/01/17 4:26 PM, Bart Smissaert wrote: > >> 3. Start a transaction and hold the DB read locks for the duration of >>> >> your application (again, if it won

Re: [sqlite] Speed issue of SELECT in my application

On 2018/01/17 4:26 PM, Bart Smissaert wrote: 3. Start a transaction and hold the DB read locks for the duration of your application (again, if it won't need writing) I had a look at this but couldn't see a speed increase. This was for a single statement, so that is repeated (in a loop) sqlite3

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

To add some thoughts to Peter's discussion... In game design speed is definitely of the utmost importance since a visual game is basically a UI that is time-sensitive (unlike nearly any other type of software). It's usual to implement some slow data mechanism, typically an internet service DB