Re: [sqlite] Detecting erroneous multi-row subqueries

[J. King]

On 2018-11-29 21:17:59, "Keith Medcalf"  wrote:


while I cannot comment on the "reverse_unordered_select" pragma, you are 
getting the result you are because you are using union rather than union all.

The actual query didn't use a union and wasn't affected by the pragma 
either (presumably for some other reason I'd have to puzzle out), so I 
didn't realize my error. Thanks.




Probably not since this would break backwards compatibility.  It is however 
documented:

https://sqlite.org/lang_expr.html#subq

I've probably read that several times over the years and didn't recall 
it when it was actually important. Once again, thanks. :)


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

Re: [sqlite] Detecting erroneous multi-row subqueries

[Keith Medcalf]

On Thursday, 29 November, 2018 18:24, J. King  wrote:
>select (select 'foo' union select 'bar') || 'bar';

>SQLite 3.25.3 returns 'barbar' (regardless of the value of the
>reverse_unordered_select pragma) while PostgreSQL 11 refuses to
>process the query unless the subquery is reduced to a single row. 

while I cannot comment on the "reverse_unordered_select" pragma, you are 
getting the result you are because you are using union rather than union all.  

select ...
UNION
select ...

returns only distinct rows.  Distinctness is generated by passing the results 
of the two queries into a sorter/temp table such that duplicates get discarded, 
and then returning the contents of the sorter/temp table.  Because 'bar' sorts 
before 'foo' your select returns 'bar' for the first row and 'foo' for the 
second row.  It will do this irrespective of the order of your two selects 
being unioned.

If you used "union all" as in

select 'foo'
union all
select 'bar'

the result returned will be 'foo' since the resulting rows will not be sorted 
so that only distinct rows are returned and all rows will be returned in the 
order in which they are generated.

Also, when you request a scalar value from a sub-select some databases will 
return the first row/value retrieved, some will require that the sub-select 
generate only a single value (and throw an error if that is not the case).  
SQLite falls into the first category (since you asked for a scalar result you 
must have meant LIMIT 1, so SQLite helpfully adds that if you forgot), 
PostgreSQL of the version you are using obviously falls into the latter.  Some 
databases will change what they do from one version to the next or even from 
query to query depending on how they "feel" at the time.  I don't off-hand 
recall what the standard says (if it says anything at all), nor the behaviour 
of any particular database.

>In my application the actual query was erroneous and would potentially
>return data belonging to a user other than the one making the request, which
>concerns me quite a bit. Is it possible to make SQLite fail like
>PostgreSQL does?

Probably not since this would break backwards compatibility.  It is however 
documented:

https://sqlite.org/lang_expr.html#subq

---
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] Detecting erroneous multi-row subqueries

[J. King]

On 2018-11-29 20:56:13, "Richard Hipp"  wrote:


On 11/29/18, J. King  wrote:

 Is it possible to make SQLite fail like
 PostgreSQL does?


That is possible in theory, but how many of the millions of existing
applications would that break?



I was wondering more if it's -currently- possible via some switch I'm 
not aware of. As an enhancement obviously I wouldn't expect it as a 
default (not before SQLite 4, anyway), but as a debugging feature like 
reverse_unordered_selects, it might help to uncover millions of bugs. ;)


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

Re: [sqlite] Detecting erroneous multi-row subqueries

[Richard Hipp]

On 11/29/18, J. King  wrote:
> Is it possible to make SQLite fail like
> PostgreSQL does?

That is possible in theory, but how many of the millions of existing
applications would that break?

-- 
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] Boosting insert and indexing performance for 10 billion rows (?)

[AJ Miles]

Ah, this tool seems very handy. For those curious, I'll paste the results
below. The index approximately doubles the storage size, but I am
intentionally making that tradeoff to avoid the slow down when enforcing a
unique/primary key on the Reference table while inserting.

-AJ

/** Disk-Space Utilization Report For F:/mytestdb2.db

Page size in bytes 4096  
Pages in the whole file (measured) 104071490 
Pages in the whole file (calculated).. 104071489 
Pages that store data. 104071489  100.000% 
Pages on the freelist (per header) 00.0% 
Pages on the freelist (calculated) 10.0% 
Pages of auto-vacuum overhead. 00.0% 
Number of tables in the database.. 3 
Number of indices. 1 
Number of defined indices. 1 
Number of implied indices. 0 
Size of the file in bytes. 426276823040
Bytes of user payload stored.. 174138410641  40.9% 

*** Page counts for all tables with their indices
*

REFERENCE. 9300818889.4% 
MAINDATA.. 1106330010.6% 
SQLITE_MASTER. 10.0% 

*** Page counts for all tables and indices separately
*

REFERENCE. 5030453448.3% 
HASHINDEX. 4270365441.0% 
MAINDATA.. 1106330010.6% 
SQLITE_MASTER. 10.0% 

*** All tables and indices


Percentage of total database.. 100.000%  
Number of entries. 17948049998
Bytes of storage consumed. 426276818944
Bytes of payload.. 321412979244  75.4% 
Bytes of metadata. 100378462716  23.5% 
Average payload per entry. 17.91 
Average unused bytes per entry 0.25  
Average metadata per entry 5.59  
Average fanout 241.00
Maximum payload per entry. 37003 
Entries that use overflow. 535470.0% 
Index pages used.. 430147
Primary pages used 103581347 
Overflow pages used... 59995 
Total pages used.. 104071489 
Unused bytes on index pages... 96815925 5.5% 
Unused bytes on primary pages. 4383344069   1.0% 
Unused bytes on overflow pages 5242782  2.1% 
Unused bytes on all pages. 4485402776   1.1% 

*** All tables


Percentage of total database..  59.0%
Number of entries. 9031683455
Bytes of storage consumed. 251362652160
Bytes of payload.. 174138410911  69.3% 
Bytes of metadata. 73116919243  29.1% 
Average payload per entry. 19.28 
Average unused bytes per entry 0.45  
Average metadata per entry 8.10  
Average fanout 332.00
Maximum payload per entry. 37003 
Entries that use overflow. 535470.0% 
Index pages used.. 184608
Primary pages used 61123232  
Overflow pages used... 59995 
Total pages used.. 61367835  
Unused bytes on index pages... 9414297112.5% 
Unused bytes on primary pages. 4007962045   1.6% 
Unused bytes on overflow pages 5242782  2.1% 
Unused bytes on all pages. 4107347798   1.6% 

*** All indices
***

Percentage of total database..  41.0%
Number of entries. 8916366543
Bytes of storage consumed. 174914166784
Bytes of payload.. 147274568333  84.2% 
Bytes of metadata. 27261543473  15.6% 
B-tree depth.. 5 
Average payload per 

[sqlite] Detecting erroneous multi-row subqueries

[J. King]

Good people of sqlite-users,
I've been porting an application originally designed for SQLite to 
PostgreSQL, and the latter failed on a test case where the former does 
not. While I've encountered many more such failures during the process 
than I'd like, in this case I think PostgreSQL's behaviour is more 
desirable. Here's a minimal query:


select (select 'foo' union select 'bar') || 'bar';

SQLite 3.25.3 returns 'barbar' (regardless of the value of the 
reverse_unordered_select pragma) while PostgreSQL 11 refuses to process 
the query unless the subquery is reduced to a single row. In my 
application the actual query was erroneous and would potentially return 
data belonging to a user other than the one making the request, which 
concerns me quite a bit. Is it possible to make SQLite fail like 
PostgreSQL does?


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

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

[Richard Hipp]

On 11/29/18, AJ Miles  wrote:
>
> I misspoke when I said it was 200-300gb for just the integers -- my brain
> was a little fuzzy. Right now, the integer table has 3 columns: rowid,
> integer, and foreign row id to a second table (so 8byte int, 8 byte int,
> variable byte int I believe, unless the rowid is also a variably-sized int).
> The rowid is left in to prevent using it as a primary key, or enforcing a
> unique constraint upfront which would result in an extremely slow insertion
> if I understand the basics correctly (and based on my own tests). This works
> out to about 180 gb of the table.
>
> There is a second table which stores some text information at 150 million
> rows, and then references my integer table. This makes up some of the
> difference. These rows are probably on the order of 200-300 bytes each so if
> I do the math, something in the 210-250 gb range seems reasonable.
>

Just for grins, consider downloading the "bundle of command-line tools
for managing SQLite database files" for your platform from
https://sqlite.org/download.html and running the "sqlite3_analyzer"
tool against your database.  That tool will print out the average
number of bytes used for each row of each table, along with additional
information about how content is laid out in your database.  Maybe
post the results here.  The utility will run at 1-5 GB/s so it might
take a minute or three for it to complete.

 sqlite3_analyzer  your-big-file.db >analysis.txt

Then copy/paste analysis.txt into an email to this list.

-- 
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] Boosting insert and indexing performance for 10 billion rows (?)

[AJ Miles]

Simon, Dominique, and Arun -

Thank you for the feedback. I'll leave the threading off for inserts since
I've seen DB contention issues with other multithreaded/multiprocessed
attempts. The indexing improvement is nice though.

I misspoke when I said it was 200-300gb for just the integers -- my brain
was a little fuzzy. Right now, the integer table has 3 columns: rowid,
integer, and foreign row id to a second table (so 8byte int, 8 byte int,
variable byte int I believe, unless the rowid is also a variably-sized int).
The rowid is left in to prevent using it as a primary key, or enforcing a
unique constraint upfront which would result in an extremely slow insertion
if I understand the basics correctly (and based on my own tests). This works
out to about 180 gb of the table.

There is a second table which stores some text information at 150 million
rows, and then references my integer table. This makes up some of the
difference. These rows are probably on the order of 200-300 bytes each so if
I do the math, something in the 210-250 gb range seems reasonable.

-AJ



--
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] Getting SQLITE_LOCKED

[Keith Medcalf]

Ya must be using shared cache as well?


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


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Prajeesh Prakash
>Sent: Thursday, 29 November, 2018 04:37
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Getting SQLITE_LOCKED
>
>Hi Team,
>
>I am writing to a table 1 and reading from table 2 both operation are
>from different DB connection i am getting SQLITE_LOCKED
>
>and when i try to read and write the same table from different
>connection i am getting same error. (I know read and write are the
>incompatible at same time). Why this happening bu default sqlite will
>act on FULLMUTEXT.
>
>Thank You
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

Re: [sqlite] function named geopolyCosine is a misnomer

[Richard Hipp]

On 11/29/18, Thomas Kurz  wrote:
> Could it be that the one angle is north-based, the other one east-based?

Ha Ha.  No, Graham is right.  I started out writing a Cosine function,
then I switched it over to be a Sine function but failed to change the
name.  A rename has now been committed to trunk, is in the latest
"prerelease snapshot", and will appear in the next official release
(which will also be the first official release that includes the new
capability).

-- 
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] function named geopolyCosine is a misnomer

[Thomas Kurz]

Could it be that the one angle is north-based, the other one east-based?


- Original Message - 
From: Graham Hardman 
To: SQLite mailing list 
Sent: Thursday, November 29, 2018, 12:46:05
Subject: [sqlite] function named geopolyCosine is a misnomer

Hi, 

I was very interested in the numerical approximation used in the
function named geopolyCosine and after a bit of on-line research decided
to test it's accuracy myself. What I discovered was that the function in
fact returns the sin value rather than the cosine value. 

This is quickly noticed by checking the return value when r = 0. The
value returned is 0 rather than the expected 1.0 

The function is only employed by the geopoly_regular function which I
found does actually return the expected shape (taking into account the
approximation being used). 

On checking the geopoly_regular program code I saw that the coordinate
calculation formulae cancel out the incorrect value returned from
geopolyCosine by essentially  reversing the normal understanding of sin
and cosine. 

I hope it is understood that I do not wish to offend. I would, in fact
appreciate a link to the site where this approximation was discovered. 

regards, 

Graham
___
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] SQLITE_LOCKED and SQLITE_BUSY

[Simon Slavin]

On 29 Nov 2018, at 11:35am, Prajeesh Prakash  
wrote:

> I am writing to a table 1 and reading from table 2 both operation are from 
> different DB connection i am getting SQLITE_LOCKED

SQLite locks the entire database.  It does not lock each table independently.

> and when i try to read and write the same table from different connection i 
> am getting same error. (I know read and write are the incompatible at same 
> time). Why this happening bu default sqlite will act on FULLMUTEXT.

Ignore MUTEX when using two different connections.  You need to set a timeout 
of a minute or two for each connection:



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

[sqlite] Strange query result

[Maurice van der Stee]

Thanks , that explains it. Leanerd something today.

Sorry to reply out of thread, but I am subscribed to the digest, so
haven't yet gotten the original messages.
-- 
===
Maurice van der Stee (s...@planet.nl)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Strange query results.

[Hick Gunter]

Just as expected. You are comparing INTEGER to TEXT values. '4' is a text 
value, not an integer.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Maurice van der Stee
Gesendet: Donnerstag, 29. November 2018 14:46
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Strange query results.

This reproduces the issue for me:

create table config (config_package integer, config_flags integer); insert into 
config (config_package, config_flags) values (1, 2); insert into config 
(config_package, config_flags) values (2, 4); insert into config 
(config_package, config_flags) values (3, 6); select config_package, 
config_flags, (config_flags & '4') from config where (config_flags & '4') != 
'4';

This produces:

1|2|0
2|4|4
3|6|4

While it should only have returned the first row.
--
===
Maurice van der Stee (s...@planet.nl)
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

[Dan Kennedy]

On 11/29/2018 07:10 PM, szmate1618 wrote:

I'm afraid I still didn't make any progress on this.

It looks to me that PRAGMA foreign_key_check; and the deferred foreign key
mechanism sometimes contradict each other,
consequently at least one of these features must have a bug, but I don't
know for sure which one,
so I can't risk using any of them in production until the situation is
resolved.

Do you have any suggestion how I should go about it? Can I escalate this to
someone?


If there is a bug, please do. I haven't actually understood the 
explanation of it yet though.


Or what is wrong with:

  PRAGMA foreign_keys = 0;
  BEGIN;

  if( [PRAGMA foreign_key_check]=="ok" ) COMMIT else ROLLBACK;
  PRAGMA foreign_keys = 1;


Dan.





Thanks in advance,
Máté

szmate1618  ezt írta (időpont: 2018. nov. 23., P,
17:21):


"PRAGMA foreign_keys = ?" is a property of the connection only, not the
database file. So what advantage would there be in including the PRAGMA
statements in the body of a transaction?


Not much, if I do everything right, I guess. But what if I break the
foreign key integrity?
If it's inside a transaction, I can rollback easily, given that I realize
I broke it.
But if I don't, because foreign key checks are turned off, and I commit
everything before noticing that
something's wrong, that's a lot harder to fix.

I can either take extra care not to break anything, or just do a backup of
the database file before
'risky' transactions, but it would be much easier if I could just use
deferred foreign keys that don't
let me commit if the database is in an inconsistent state.

Máté

Dan Kennedy  ezt írta (időpont: 2018. nov. 23., P,
16:30):


On 11/23/2018 09:54 PM, szmate1618 wrote:

Dear list members,

I have the following problem, with which I'd like to request your aid:

Currently, at version 3.25.2, SQLite only has a limited support for

alter

table. E.g. you cannot change the datatype (type affinity) of a column,

or

drop a column.

The usual workaround is to create a new table with the desired schema,

fill

it with data from the original table, drop the original table, and

rename

the new one. But what if the original table is a parent table in a

foreign

key relationship?

The official solution
 is turning

foreign

keys off, making the changes you want, then turning foreign keys on. But
I'm slightly annoyed this cannot be done in a transaction (because

these PRAGMA

foreign_keys =s don't take effect inside of transactions, so they need

to

be issued before and after).



"PRAGMA foreign_keys = ?" is a property of the connection only, not the
database file. So what advantage would there be in including the PRAGMA
statements in the body of a transaction?

Dan.





I'd like to use deferred foreign keys instead. I have 3 queries, one of
them seems to work, the two others do not. My questions are the

following:


   - Does the seemingly working query work by design? Or it's just a
   fortunate(?) constellation of multiple factors, and depending on

other

   tables or new data in the database it might break in the future?

Somewhat

   like undefined behavior in C++?
   - Why do the other ones not work? How are they different from the

first

   one?

Setup

PRAGMA foreign_keys = OFF;
DROP TABLE IF EXISTS Parent;CREATE TABLE Parent(A TEXT UNIQUE,
COLUMN_TO_DROP FLOAT);INSERT INTO Parent VALUES('whatever', 0.0);
DROP TABLE IF EXISTS Child;CREATE TABLE Child(C TEXT REFERENCES
Parent(A) DEFERRABLE INITIALLY DEFERRED);INSERT INTO Child
VALUES('whatever');

PRAGMA foreign_keys = ON;

Query1 - seems to be working as intended

BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT INTO Parent SELECT *
FROM Temp;DROP TABLE Temp;COMMIT;

Query2 - create [...] as select [...] fails

BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
Parent;CREATE TABLE Parent AS SELECT * FROM Temp; -- different
from Query1CREATE UNIQUE INDEX ParentIndex on Parent(A);  -- different
from Query1DROP TABLE Temp;COMMIT;

Result:

sqlite> PRAGMA foreign_key_check;
sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent(A TEXT);CREATE
UNIQUE INDEX ParentIndex on Parent(A);
sqlite> SELECT * FROM Parent;
whatever

Query3 - insert into [...] fails

BEGIN TRANSACTION;CREATE TABLE Temp (A TEXT UNIQUE);  -- different
from Query1INSERT INTO Temp SELECT A FROM Parent;  -- different from
Query1DROP TABLE Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT
INTO Parent SELECT * FROM Temp;DROP TABLE Temp;COMMIT;

Result:

sqlite> PRAGMA foreign_key_check;
sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent (A TEXT UNIQUE);
sqlite> SELECT * FROM Parent;
whatever

Note that PRAGMA foreign_key_check does not report any problem in any of
the cases.


I posted an identical 

Re: [sqlite] Strange query results.

[Olivier Mascia]

> Le 29 nov. 2018 à 14:45, Maurice van der Stee  a écrit :
> 
> This reproduces the issue for me:
> 
> create table config (config_package integer, config_flags integer);
> insert into config (config_package, config_flags) values (1, 2);
> insert into config (config_package, config_flags) values (2, 4);
> insert into config (config_package, config_flags) values (3, 6);
> select config_package, config_flags, (config_flags & '4') from config
> where (config_flags & '4') != '4';
> This produces:
> 
> 1|2|0
> 2|4|4
> 3|6|4
> 
> While it should only have returned the first row.

Just like this?

select config_package, config_flags, (config_flags & 4) from config
where (config_flags & 4) != 4;

1|2|0

-- 
Best Regards, Meilleures salutations, Met vriendelijke groeten,
Olivier Mascia


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

Re: [sqlite] Strange query results.

[Jay Kreibich]

> On Nov 29, 2018, at 7:45 AM, Maurice van der Stee  wrote:
> 
> This reproduces the issue for me:
> 
> create table config (config_package integer, config_flags integer);
> insert into config (config_package, config_flags) values (1, 2);
> insert into config (config_package, config_flags) values (2, 4);
> insert into config (config_package, config_flags) values (3, 6);
> select config_package, config_flags, (config_flags & '4') from config
> where (config_flags & '4') != '4';
> 
> This produces:
> 
> 1|2|0
> 2|4|4
> 3|6|4

You’re putting single-quotes around the 4, so it is a one character string, not 
a number.

In the case of “ config_flags & ‘4’ ”, the “&” operator only accepts numbers, 
so the string is converted to a number.

In the case of “  != ‘4’ “, the equality operator is checking to see if 
the number 4 is equal to the string ‘4’.  They are not.  != already returns 
true.

You can fix this by changing all instances of “ ‘4’ “ to just “4”.

  -j



> While it should only have returned the first row.
> -- 
> ===
> Maurice van der Stee (s...@planet.nl)
> ___
> 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] [EXTERNAL] Getting SQLITE_LOCKED

[Prajeesh Prakash]

Hi Team,

Actually this is the case. From my main application i created two thread (One 
thread is for read from table 1 on the DB and another thread is for write into 
a separate table (Say table 2) on the DB). Similary i created two separate 
connection to DB (For these two thread to operate)using sqlite3 * and i used  
sqlite3_open() to open the DB connection (That is two sqlite3_open() for two 
connection). Once i started the operation (writing and reading using 
sqlite3_exec()) the thread which trying to read getting SQLITE_BUSY (error code 
5 and error message database is locked) and result writing to the DB was 
successfully done (Thread 1 operation). 


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

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

[Arun - Siara Logics (cc)]

Without going into details of what your structure is, I suggest you look into 
"without rowid" option when creating the table. It reduces the overhead 
drastically.

  On Thu, 29 Nov 2018 18:59:26 +0530 Dominique Devienne 
 wrote  
 > On Wed, Nov 28, 2018 at 6:03 PM AJ M  wrote:
 > 
 > > [...] The data comes out to 10 billion rows of an 8 byte signed integer
 > 
 > (~200-300 gb pre-index), and while insertion takes ~6 hours, indexing takes
 > > 8 hours by
 > > itself. [...] query speed is fine as-is. [...]
 > >
 > 
 > Hi AJ. Your message is quite intriguing, because you make it sound like
 > your row
 > is composed of a single 8-byte signed integer. Even multiplied by 1e10
 > rows, that's only
 > 80GB ideally, so 200-300GB pre-indexing means a large 3x overhead in the
 > DB, which doesn't sound right.
 > 
 > Also, a row composed of a single integer column is not that interesting at
 > first sight, and a SQL
 > DB does not seem appropriate for such a simple data "structure". What kind
 > of query would
 > you be running on that one signed integer? Surely you have other columns in
 > your DB?
 > What's the natural or primary key of those rows?
 > 
 > So far you got answers on your specific question, but if we backed up a
 > little and got more context
 > on what you are trying to achieve at a higher level, your exact table(s)
 > structures and indexes,
 > and the kind of queries you are running? I'm sure you'd get a different
 > perspective on your
 > problem, which may even not be related to SQLite at all I kinda suspect. My
 > $0.02. --DD
 > ___
 > 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

[sqlite] Strange query results.

[Maurice van der Stee]

This reproduces the issue for me:

create table config (config_package integer, config_flags integer);
insert into config (config_package, config_flags) values (1, 2);
insert into config (config_package, config_flags) values (2, 4);
insert into config (config_package, config_flags) values (3, 6);
select config_package, config_flags, (config_flags & '4') from config
where (config_flags & '4') != '4';

This produces:

1|2|0
2|4|4
3|6|4

While it should only have returned the first row.
-- 
===
Maurice van der Stee (s...@planet.nl)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Boosting insert and indexing performance for 10 billion rows (?)

[Dominique Devienne]

On Wed, Nov 28, 2018 at 6:03 PM AJ M  wrote:

> [...] The data comes out to 10 billion rows of an 8 byte signed integer

(~200-300 gb pre-index), and while insertion takes ~6 hours, indexing takes
> 8 hours by
> itself. [...] query speed is fine as-is. [...]
>

Hi AJ. Your message is quite intriguing, because you make it sound like
your row
is composed of a single 8-byte signed integer. Even multiplied by 1e10
rows, that's only
80GB ideally, so 200-300GB pre-indexing means a large 3x overhead in the
DB, which doesn't sound right.

Also, a row composed of a single integer column is not that interesting at
first sight, and a SQL
DB does not seem appropriate for such a simple data "structure". What kind
of query would
you be running on that one signed integer? Surely you have other columns in
your DB?
What's the natural or primary key of those rows?

So far you got answers on your specific question, but if we backed up a
little and got more context
on what you are trying to achieve at a higher level, your exact table(s)
structures and indexes,
and the kind of queries you are running? I'm sure you'd get a different
perspective on your
problem, which may even not be related to SQLite at all I kinda suspect. 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] [EXTERNAL] Strange query result

[Hick Gunter]

Works as expected. Maybe you are not declaring the correct types and end up 
comparing text to integer (1 is not equal to '1', unless an implicit or 
explicit cast is involved).

asql> create temp table flags( flag integer );
asql> insert into flags values (0),(1),(2),(3),(4),(5),(6),(7);
rows inserted
-
8

asql> select * from flags where flag & 1;
flag
--
1
3
5
7
asql> select * from flags where flag & 1 == 1;
flag
--
1
3
5
7
asql> select * from flags where flag & 1 != 0;
flag
--
1
3
5
7
asql> select * from flags where (flag & 1) != 0;
flag
--
1
3
5
7
asql> select * from flags where (flag & 1) != 0;
flag
--
1
3
5
7

asql> select * from flags where flag & (1|2);
flag
--
1
2
3
5
6
7
asql> select * from flags where flag & (1|2) != 0;
flag
--
1
2
3
5
6
7
asql> select * from flags where (flag & (1|2)) != 0;
flag
--
1
2
3
5
6
7

asql> select * from flags where flag & (1|2) == (1|2);
flag
--
3
7asql> select * from flags where (flag & (1|2)) == (1|2);
flag
--
3
7

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Maurice van der Stee
Gesendet: Donnerstag, 29. November 2018 13:53
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Strange query result

Hello,

I have a sqlite database containing a table config with amongst others the 
column conf_flags. I want to select entries from this table which have one or 
more specific flags set.

If do

select config_flags from config where config_flags & WANTED_FLAG != 0.

I get the correct result. Now this works if there is only one flag to test for. 
With multiple flags this works:

select config_flags from config where (config_flags & (WANTED_FLAG1 |
WANTED_FLAG2))  != 0

but this also returns the rows with only one of the flags set, which is not 
what I want.

This returns 0 rows:

select config_flags from config where (config_flags &
WANTED_FLAG)  =  WANTED_FLAG.

The strange thing is that if I do

select config_flags, config_flags & WANTED_FLAG  from config where 
(config_flags & WANTED_FLAG) != 0.

This has the correct evaluation in the second result column.

So what am I doing wrong.

===
Maurice van der Stee (s...@planet.nl)
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange query result

[Richard Hipp]

On 11/29/18, Maurice van der Stee  wrote:
> This returns 0 rows:
>
> select config_flags from config where (config_flags &
> WANTED_FLAG)  =  WANTED_FLAG.

Can you provide a concrete example - something we can actually run in
the sqlite3 command-line shell?

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

[sqlite] Strange query result

[Maurice van der Stee]

Hello,

I have a sqlite database containing a table config with amongst others
the column conf_flags. I want to select entries from this table which
have one or more specific flags set.

If do

select config_flags from config where config_flags & WANTED_FLAG != 0. 

I get the correct result. Now this works if there is only one flag to
test for. With multiple flags this works:

select config_flags from config where (config_flags & (WANTED_FLAG1 |
WANTED_FLAG2))  != 0

but this also returns the rows with only one of the flags set, which is
not what I want.

This returns 0 rows:

select config_flags from config where (config_flags &
WANTED_FLAG)  =  WANTED_FLAG.

The strange thing is that if I do

select config_flags, config_flags & WANTED_FLAG  from config where
(config_flags & WANTED_FLAG) != 0.

This has the correct evaluation in the second result column.

So what am I doing wrong.   
 
===
Maurice van der Stee (s...@planet.nl)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Getting SQLITE_LOCKED

[Hick Gunter]

Locking (errors SQLITE_LOCKED and SQLITE_BUSY) are about **WHAT** you are doing 
to the DB. There can be at most 1 thread (same process or different process, it 
does not matter) with a write transaction on any DB file at any time. 
Typically, a transaction involves multiple calls to sqlite functions 
(sqlite3_prepare() to compile the statement, the first call to sqlite3_step() 
starts the transaction and calling sqlite3_reset() or sqlite3_finalize() ends 
the transaction).

FULLMUTEX has nothing to do with transactions. Using threads is about **HOW** 
you are doing things to the DB. FULLMUTEX is about making sure that your 
threads take turns when sharing a single connection (with a single transaction).

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Prajeesh Prakash
Gesendet: Donnerstag, 29. November 2018 12:37
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Getting SQLITE_LOCKED

Hi Team,

I am writing to a table 1 and reading from table 2 both operation are from 
different DB connection i am getting SQLITE_LOCKED

and when i try to read and write the same table from different connection i am 
getting same error. (I know read and write are the incompatible at same time). 
Why this happening bu default sqlite will act on FULLMUTEXT.

Thank You
___
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 | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Getting SQLITE_LOCKED

[Prajeesh Prakash]

Sorry i was a wrong attempt.

> 
> On November 29, 2018 at 5:07 PM Prajeesh Prakash 
>  wrote:
> 
> Hi Team,
> 
> I am writing to a table 1 and reading from table 2 both operation are 
> from different DB connection i am getting SQLITE_LOCKED
> 
> and when i try to read and write the same table from different connection 
> i am getting same error. (I know read and write are the incompatible at same 
> time). Why this happening bu default sqlite will act on FULLMUTEXT.
> 
> Thank You
> 
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug?: unexpected behaviour - alter parent table in a deferred foreign key relationship

[szmate1618]

I'm afraid I still didn't make any progress on this.

It looks to me that PRAGMA foreign_key_check; and the deferred foreign key
mechanism sometimes contradict each other,
consequently at least one of these features must have a bug, but I don't
know for sure which one,
so I can't risk using any of them in production until the situation is
resolved.

Do you have any suggestion how I should go about it? Can I escalate this to
someone?

Thanks in advance,
Máté

szmate1618  ezt írta (időpont: 2018. nov. 23., P,
17:21):

> >"PRAGMA foreign_keys = ?" is a property of the connection only, not the
> >database file. So what advantage would there be in including the PRAGMA
> >statements in the body of a transaction?
>
> Not much, if I do everything right, I guess. But what if I break the
> foreign key integrity?
> If it's inside a transaction, I can rollback easily, given that I realize
> I broke it.
> But if I don't, because foreign key checks are turned off, and I commit
> everything before noticing that
> something's wrong, that's a lot harder to fix.
>
> I can either take extra care not to break anything, or just do a backup of
> the database file before
> 'risky' transactions, but it would be much easier if I could just use
> deferred foreign keys that don't
> let me commit if the database is in an inconsistent state.
>
> Máté
>
> Dan Kennedy  ezt írta (időpont: 2018. nov. 23., P,
> 16:30):
>
>> On 11/23/2018 09:54 PM, szmate1618 wrote:
>> > Dear list members,
>> >
>> > I have the following problem, with which I'd like to request your aid:
>> >
>> > Currently, at version 3.25.2, SQLite only has a limited support for
>> alter
>> > table. E.g. you cannot change the datatype (type affinity) of a column,
>> or
>> > drop a column.
>> >
>> > The usual workaround is to create a new table with the desired schema,
>> fill
>> > it with data from the original table, drop the original table, and
>> rename
>> > the new one. But what if the original table is a parent table in a
>> foreign
>> > key relationship?
>> >
>> > The official solution
>> >  is turning
>> foreign
>> > keys off, making the changes you want, then turning foreign keys on. But
>> > I'm slightly annoyed this cannot be done in a transaction (because
>> these PRAGMA
>> > foreign_keys =s don't take effect inside of transactions, so they need
>> to
>> > be issued before and after).
>>
>>
>> "PRAGMA foreign_keys = ?" is a property of the connection only, not the
>> database file. So what advantage would there be in including the PRAGMA
>> statements in the body of a transaction?
>>
>> Dan.
>>
>>
>>
>> >
>> > I'd like to use deferred foreign keys instead. I have 3 queries, one of
>> > them seems to work, the two others do not. My questions are the
>> following:
>> >
>> >- Does the seemingly working query work by design? Or it's just a
>> >fortunate(?) constellation of multiple factors, and depending on
>> other
>> >tables or new data in the database it might break in the future?
>> Somewhat
>> >like undefined behavior in C++?
>> >- Why do the other ones not work? How are they different from the
>> first
>> >one?
>> >
>> > Setup
>> >
>> > PRAGMA foreign_keys = OFF;
>> > DROP TABLE IF EXISTS Parent;CREATE TABLE Parent(A TEXT UNIQUE,
>> > COLUMN_TO_DROP FLOAT);INSERT INTO Parent VALUES('whatever', 0.0);
>> > DROP TABLE IF EXISTS Child;CREATE TABLE Child(C TEXT REFERENCES
>> > Parent(A) DEFERRABLE INITIALLY DEFERRED);INSERT INTO Child
>> > VALUES('whatever');
>> >
>> > PRAGMA foreign_keys = ON;
>> >
>> > Query1 - seems to be working as intended
>> >
>> > BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
>> > Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT INTO Parent SELECT *
>> > FROM Temp;DROP TABLE Temp;COMMIT;
>> >
>> > Query2 - create [...] as select [...] fails
>> >
>> > BEGIN TRANSACTION;CREATE TABLE Temp AS SELECT A FROM Parent;DROP TABLE
>> > Parent;CREATE TABLE Parent AS SELECT * FROM Temp; -- different
>> > from Query1CREATE UNIQUE INDEX ParentIndex on Parent(A);  -- different
>> > from Query1DROP TABLE Temp;COMMIT;
>> >
>> > Result:
>> >
>> > sqlite> PRAGMA foreign_key_check;
>> > sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
>> > DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent(A TEXT);CREATE
>> > UNIQUE INDEX ParentIndex on Parent(A);
>> > sqlite> SELECT * FROM Parent;
>> > whatever
>> >
>> > Query3 - insert into [...] fails
>> >
>> > BEGIN TRANSACTION;CREATE TABLE Temp (A TEXT UNIQUE);  -- different
>> > from Query1INSERT INTO Temp SELECT A FROM Parent;  -- different from
>> > Query1DROP TABLE Parent;CREATE TABLE Parent (A TEXT UNIQUE);INSERT
>> > INTO Parent SELECT * FROM Temp;DROP TABLE Temp;COMMIT;
>> >
>> > Result:
>> >
>> > sqlite> PRAGMA foreign_key_check;
>> > sqlite> .schemaCREATE TABLE Child(C TEXT REFERENCES Parent(A)
>> > DEFERRABLE INITIALLY DEFERRED);CREATE TABLE Parent (A TEXT UNIQUE);
>> > sqlite> SELECT * 

[sqlite] function named geopolyCosine is a misnomer

[Graham Hardman]

Hi, 

I was very interested in the numerical approximation used in the
function named geopolyCosine and after a bit of on-line research decided
to test it's accuracy myself. What I discovered was that the function in
fact returns the sin value rather than the cosine value. 

This is quickly noticed by checking the return value when r = 0. The
value returned is 0 rather than the expected 1.0 

The function is only employed by the geopoly_regular function which I
found does actually return the expected shape (taking into account the
approximation being used). 

On checking the geopoly_regular program code I saw that the coordinate
calculation formulae cancel out the incorrect value returned from
geopolyCosine by essentially  reversing the normal understanding of sin
and cosine. 

I hope it is understood that I do not wish to offend. I would, in fact
appreciate a link to the site where this approximation was discovered. 

regards, 

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

[sqlite] Getting SQLITE_LOCKED

[Prajeesh Prakash]

Hi Team,

I am writing to a table 1 and reading from table 2 both operation are from 
different DB connection i am getting SQLITE_LOCKED

and when i try to read and write the same table from different connection i am 
getting same error. (I know read and write are the incompatible at same time). 
Why this happening bu default sqlite will act on FULLMUTEXT.

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

Re: [sqlite] SQLITE_LOCKED and SQLITE_BUSY

[Prajeesh Prakash]

Hi Team,

I am writing to a table 1 and reading from table 2 both operation are from 
different DB connection i am getting SQLITE_LOCKED

and when i try to read and write the same table from different connection i am 
getting same error. (I know read and write are the incompatible at same time). 
Why this happening bu default sqlite will act on FULLMUTEXT.

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