2021-03-08
| | |
01:36 |
|
•
(Awaiting Moderator Approval)
(artifact: 8e9528c270 user: 999999999 (unpublished))
|
2021-03-07
| | |
23:58 |
|
•
(artifact: 32731aada3 user: kmedcalf)
3.33.0 (at least) and later show the bad behaviour. The upcoming 3.35 version appears to work properly once the statistics are correct ...
|
23:27 |
|
•
(artifact: 988f8b9ebc user: kmedcalf)
OS is likely irrelevant. What version of SQLite3 are you using?
|
23:26 |
|
•
(artifact: e9b2d9653f user: kmedcalf)
Fascinating, but dropping the statistics tables is inconsistent. If there was previously good tables, then dropping them and the query still works, but if you close and re-open the database, it does not. Almost like the statistics are used even though they have been dropped. BTW, you need analysis_limit=45000 "sufficiently correct" statistics generated.
|
22:51 |
|
•
(artifact: 8d8908f06a user: kmedcalf)
After running analyze (or making sure that sqlite_stat1 and sqlite_stat4 are deleted), I get the following:
sqlite> .timer on
sqlite> .stats on
sqlite> .eqp on
sqlite> SELECT expr.langvar,langvar.uid,expr.txt,expr2.txt,expr2.langvar
...> FROM denotationx
...> JOIN expr ON (expr.id = denotationx.expr)
...> JOIN denotationx denotationx2 ON (denotationx2.meaning = denotationx.meaning)
...> JOIN expr expr2 ON (expr2.id = denotationx2.expr)
...> JOIN langvar ON (expr.langvar=langvar.id)
...> WHERE denotationx.expr != denotationx2.expr AND expr.langvar IN (1261,1776,3003,5741) AND expr2.langvar = 187
...> ORDER BY expr.langvar;
QUERY PLAN
|--SEARCH TABLE expr USING INDEX expr_langvar (langvar=?) (~2304 rows)
|--SEARCH TABLE langvar USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
|--SEARCH TABLE denotationx USING INDEX denotationx_expr (expr=?) (~4 rows)
|--SEARCH TABLE denotationx AS denotationx2 USING INDEX denotationx_meaning (meaning=?) (~2 rows)
`--SEARCH TABLE expr AS expr2 USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
┌─────────┬─────────┬─────────────────────────┬─────────────────────────┬─────────┐
│ langvar │ uid │ txt │ txt │ langvar │
├─────────┼─────────┼─────────────────────────┼─────────────────────────┼─────────┤
│ 1261 │ roa-000 │ Agropoli │ Agropoli │ 187 │
│ 1261 │ roa-000 │ Aieta │ Aieta │ 187 │
│ 1261 │ roa-000 │ Aquara │ Aquara │ 187 │
│ 1261 │ roa-000 │ Birori │ Birori │ 187 │
│ 1261 │ roa-000 │ Bonarcado │ Bonarcado │ 187 │
│ 1261 │ roa-000 │ Bracigliano │ Bracigliano │ 187 │
│ 1261 │ roa-000 │ Buccino │ Buccino │ 187 │
│ 1261 │ roa-000 │ Calvanico │ Calvanico │ 187 │
│ 1261 │ roa-000 │ Camerota │ Camerota │ 187 │
│ 1261 │ roa-000 │ Casalbuono │ Casalbuono │ 187 │
│ 1261 │ roa-000 │ Casaletto Spartano │ Casaletto Spartano │ 187 │
│ 1261 │ roa-000 │ Ceraso │ Ceraso │ 187 │
│ 1261 │ roa-000 │ Cuccaro Vetere │ Cuccaro Vetere │ 187 │
│ 1261 │ roa-000 │ Curitiba │ Curitiba │ 187 │
│ 1261 │ roa-000 │ Filiano │ Filiano │ 187 │
│ 1261 │ roa-000 │ Futani │ Futani │ 187 │
│ 1261 │ roa-000 │ Ispani │ Ispani │ 187 │
│ 1261 │ roa-000 │ Laino Borgo │ Laino Borgo │ 187 │
│ 1261 │ roa-000 │ Massa Lubrense │ Massa Lubrense │ 187 │
│ 1261 │ roa-000 │ Novi Velia │ Novi Velia │ 187 │
│ 1261 │ roa-000 │ Nuphar │ Nuphar │ 187 │
│ 1261 │ roa-000 │ Padula │ Padula │ 187 │
│ 1261 │ roa-000 │ Palermiti │ Palermiti │ 187 │
│ 1261 │ roa-000 │ Papa Benedetto XVI │ Pope Benedict XVI │ 187 │
│ 1261 │ roa-000 │ Polla │ Polla │ 187 │
│ 1261 │ roa-000 │ Pollica │ Pollica │ 187 │
│ 1261 │ roa-000 │ Sala Consilina │ Sala Consilina │ 187 │
│ 1261 │ roa-000 │ Santa Marina │ Santa Marina │ 187 │
│ 1261 │ roa-000 │ Silvio Berlusconi │ Silvio Berlusconi │ 187 │
│ 1261 │ roa-000 │ Solarino │ Solarino │ 187 │
│ 1261 │ roa-000 │ Stazzema │ Stazzema │ 187 │
│ 1261 │ roa-000 │ Stio │ Stio │ 187 │
│ 1261 │ roa-000 │ Tortora │ Tortora │ 187 │
│ 1261 │ roa-000 │ Uetersen │ Uetersen │ 187 │
│ 1261 │ roa-000 │ Caggiano │ Caggiano │ 187 │
│ 1261 │ roa-000 │ Centola │ Centola │ 187 │
│ 1261 │ roa-000 │ Due Carrare │ Due Carrare │ 187 │
│ 1261 │ roa-000 │ Gela │ Gela │ 187 │
│ 1261 │ roa-000 │ Gianfranco Fini │ Gianfranco Fini │ 187 │
│ 1261 │ roa-000 │ Laurito │ Laurito │ 187 │
│ 1261 │ roa-000 │ Montoro Inferiore │ Montoro Inferiore │ 187 │
│ 1261 │ roa-000 │ Oliveto Citra │ Oliveto Citra │ 187 │
│ 1261 │ roa-000 │ Orsago │ Orsago │ 187 │
│ 1261 │ roa-000 │ Perdifumo │ Perdifumo │ 187 │
│ 1261 │ roa-000 │ Pertosa │ Pertosa │ 187 │
│ 1261 │ roa-000 │ Praia a Mare │ Praia a Mare │ 187 │
│ 1261 │ roa-000 │ Regnum Plantae │ Plant │ 187 │
│ 1261 │ roa-000 │ Romano Prodi │ Romano Prodi │ 187 │
│ 1261 │ roa-000 │ Salerno │ Salerno │ 187 │
│ 1261 │ roa-000 │ Sant'Angelo a Fasanella │ Sant'Angelo a Fasanella │ 187 │
│ 1261 │ roa-000 │ Sapri │ Sapri │ 187 │
│ 1261 │ roa-000 │ Sassano │ Sassano │ 187 │
│ 1261 │ roa-000 │ Sessa Cilento │ Sessa Cilento │ 187 │
│ 1261 │ roa-000 │ Tarde │ Taranto │ 187 │
│ 1261 │ roa-000 │ Tortolì │ Tortolì │ 187 │
│ 1261 │ roa-000 │ Voltaire │ Voltaire │ 187 │
│ 1261 │ roa-000 │ Alfano │ Alfano │ 187 │
│ 1261 │ roa-000 │ Amendolara │ Amendolara │ 187 │
│ 1261 │ roa-000 │ Arzano │ Arzano │ 187 │
│ 1261 │ roa-000 │ Ascea │ Ascea │ 187 │
│ 1261 │ roa-000 │ Auletta │ Auletta │ 187 │
│ 1261 │ roa-000 │ Baronissi │ Baronissi │ 187 │
│ 1261 │ roa-000 │ Calabritto │ Calabritto │ 187 │
│ 1261 │ roa-000 │ Caselle in Pittari │ Caselle in Pittari │ 187 │
│ 1261 │ roa-000 │ Catalao │ Catalao │ 187 │
│ 1261 │ roa-000 │ Colico │ Colico │ 187 │
│ 1261 │ roa-000 │ Controne │ Controne │ 187 │
│ 1261 │ roa-000 │ Denis Diderot │ Denis Diderot │ 187 │
│ 1261 │ roa-000 │ Felitto │ Felitto │ 187 │
│ 1261 │ roa-000 │ Giffoni Sei Casali │ Giffoni Sei Casali │ 187 │
│ 1261 │ roa-000 │ Giffoni Valle Piana │ Giffoni Valle Piana │ 187 │
│ 1261 │ roa-000 │ Ginosa │ Ginosa │ 187 │
│ 1261 │ roa-000 │ Kurów │ Kurów │ 187 │
│ 1261 │ roa-000 │ Massafra │ Massafra │ 187 │
│ 1261 │ roa-000 │ Minervino Murge │ Minervino Murge │ 187 │
│ 1261 │ roa-000 │ Moncalieri │ Moncalieri │ 187 │
│ 1261 │ roa-000 │ Pontecagnano Faiano │ Pontecagnano Faiano │ 187 │
│ 1261 │ roa-000 │ Poznan │ Poznan │ 187 │
│ 1261 │ roa-000 │ Quentin Tarantino │ Quentin Tarantino │ 187 │
│ 1261 │ roa-000 │ Rofrano │ Rofrano │ 187 │
│ 1261 │ roa-000 │ Salènde │ Salento │ 187 │
│ 1261 │ roa-000 │ Teggiano │ Teggiano │ 187 │
│ 1261 │ roa-000 │ Torchiara │ Torchiara │ 187 │
│ 1261 │ roa-000 │ Avë Marije │ Hail Mary │ 187 │
│ 1261 │ roa-000 │ Angri │ Angri │ 187 │
│ 1261 │ roa-000 │ Caraglio │ Caraglio │ 187 │
│ 1261 │ roa-000 │ Castelcivita │ Castelcivita │ 187 │
│ 1261 │ roa-000 │ Colliano │ Colliano │ 187 │
│ 1261 │ roa-000 │ Fisciano │ Fisciano │ 187 │
│ 1261 │ roa-000 │ Homo neanderthalensis │ Neanderthal │ 187 │
│ 1261 │ roa-000 │ Italie │ Italy │ 187 │
│ 1261 │ roa-000 │ Jean-Jacques Rousseau │ Jean-Jacques Rousseau │ 187 │
│ 1261 │ roa-000 │ Mercato San Severino │ Mercato San Severino │ 187 │
│ 1261 │ roa-000 │ Montano Antilia │ Montano Antilia │ 187 │
│ 1261 │ roa-000 │ Montecorice │ Montecorice │ 187 │
│ 1261 │ roa-000 │ Nardò │ Nardò │ 187 │
│ 1261 │ roa-000 │ Pellezzano │ Pellezzano │ 187 │
│ 1261 │ roa-000 │ Piaggine │ Piaggine │ 187 │
│ 1261 │ roa-000 │ Piombino │ Piombino │ 187 │
│ 1261 │ roa-000 │ Postiglione │ Postiglione │ 187 │
│ 1261 │ roa-000 │ Ricigliano │ Ricigliano │ 187 │
│ 1261 │ roa-000 │ Rivello │ Rivello │ 187 │
│ 1261 │ roa-000 │ San Gregorio Magno │ San Gregorio Magno │ 187 │
│ 1261 │ roa-000 │ Sarno │ Sarno │ 187 │
│ 1261 │ roa-000 │ Serramezzana │ Serramezzana │ 187 │
│ 1261 │ roa-000 │ Siano │ Siano │ 187 │
│ 1261 │ roa-000 │ Sirignano │ Sirignano │ 187 │
│ 1261 │ roa-000 │ Torraca │ Torraca │ 187 │
│ 1261 │ roa-000 │ Tortorella │ Tortorella │ 187 │
│ 1261 │ roa-000 │ Torun │ Torun │ 187 │
│ 1261 │ roa-000 │ Lódz │ Lódz │ 187 │
│ 1261 │ roa-000 │ Bari │ Bari │ 187 │
│ 1261 │ roa-000 │ Breinigerberg │ Breinigerberg │ 187 │
│ 1261 │ roa-000 │ Brindisi │ Brindisi │ 187 │
│ 1261 │ roa-000 │ Cannalonga │ Cannalonga │ 187 │
│ 1261 │ roa-000 │ Capitignano │ Capitignano │ 187 │
│ 1261 │ roa-000 │ Castellabate │ Castellabate │ 187 │
│ 1261 │ roa-000 │ Crotone │ Crotone │ 187 │
│ 1261 │ roa-000 │ Lauria │ Lauria │ 187 │
│ 1261 │ roa-000 │ Laurino │ Laurino │ 187 │
│ 1261 │ roa-000 │ Lonigo │ Lonigo │ 187 │
│ 1261 │ roa-000 │ Maratea │ Maratea │ 187 │
│ 1261 │ roa-000 │ Mediaset │ Mediaset │ 187 │
│ 1261 │ roa-000 │ Monguzzo │ Monguzzo │ 187 │
│ 1261 │ roa-000 │ Montoro Superiore │ Montoro Superiore │ 187 │
│ 1261 │ roa-000 │ Nanto │ Nanto │ 187 │
│ 1261 │ roa-000 │ Portoferraio │ Portoferraio │ 187 │
│ 1261 │ roa-000 │ Prali │ Prali │ 187 │
│ 1261 │ roa-000 │ Sacco │ Sacco │ 187 │
│ 1261 │ roa-000 │ San Giuliano Terme │ San Giuliano Terme │ 187 │
│ 1261 │ roa-000 │ San Rufo │ San Rufo │ 187 │
│ 1261 │ roa-000 │ Senerchia │ Senerchia │ 187 │
│ 1261 │ roa-000 │ Torre Annunziata │ Torre Annunziata │ 187 │
│ 1261 │ roa-000 │ Trentinara │ Trentinara │ 187 │
│ 1261 │ roa-000 │ 'ngeclopedije │ Encyclopedia │ 187 │
│ 3003 │ rng-000 │ -ñwe │ one │ 187 │
│ 3003 │ rng-000 │ -birji │ two │ 187 │
│ 3003 │ rng-000 │ -rjarju │ three │ 187 │
│ 3003 │ rng-000 │ mune │ four │ 187 │
│ 3003 │ rng-000 │ ntlhanu │ five │ 187 │
│ 3003 │ rng-000 │ ntlhanu na xiñwe │ six │ 187 │
│ 3003 │ rng-000 │ ntlhanu na bsibirji │ seven │ 187 │
│ 3003 │ rng-000 │ ntlhanu na bsirjarju │ eight │ 187 │
│ 3003 │ rng-000 │ ntlhanu na mune │ nine │ 187 │
│ 3003 │ rng-000 │ khume │ ten │ 187 │
│ 5741 │ yuk-004 │ bowik │ one │ 187 │
│ 5741 │ yuk-004 │ opik │ two │ 187 │
│ 5741 │ yuk-004 │ molmik │ three │ 187 │
│ 5741 │ yuk-004 │ hilkilópik │ four │ 187 │
│ 5741 │ yuk-004 │ powbát │ five │ 187 │
│ 5741 │ yuk-004 │ powtít │ six │ 187 │
│ 5741 │ yuk-004 │ ó?edo │ seven │ 187 │
│ 5741 │ yuk-004 │ mólmetit │ eight │ 187 │
│ 5741 │ yuk-004 │ hilkilópetit │ nine │ 187 │
│ 5741 │ yuk-004 │ bo:bátedit │ ten │ 187 │
└─────────┴─────────┴─────────────────────────┴─────────────────────────┴─────────┘
Memory Used: 30880088 (max 30910304) bytes
Number of Outstanding Allocations: 9481 (max 9569)
Number of Pcache Overflow Bytes: 30580008 (max 30596928) bytes
Largest Allocation: 65544 bytes
Largest Pcache Allocation: 4360 bytes
Lookaside Slots Used: 41 (max 123)
Successful lookaside attempts: 2170
Lookaside failures due to size: 36
Lookaside failures due to OOM: 381
Pager Heap Usage: 30465968 bytes
Page cache hits: 984127
Page cache misses: 7008
Page cache writes: 0
Page cache spills: 0
Schema Heap Usage: 123896 bytes
Statement Heap/Lookaside Usage: 16704 bytes
Fullscan Steps: 0
Sort Operations: 0
Autoindex Inserts: 0
Virtual Machine Steps: 1227159
Reprepare operations: 0
Number of times run: 1
Memory used by prepared stmt: 16704
Run Time: real 0.249 user 0.078125 sys 0.046875
sqlite>
(which is 155 rows)
|
22:41 |
|
•
(artifact: e04f52e896 user: 999999999)
Tried doing both. No dice.
Can this issue be Windows-specific? Still using XP at home.
If not, what else can be done in this case?
Convert this thing into a different format maybe?
|
21:56 |
|
•
(artifact: 0f1f23930c user: kmedcalf)
Also, interestingly, with that database using the .expert command causes an abend (with the usual Windows "Sorry for you luck" error message). This is all with the current tip version.
|
21:54 |
|
•
(artifact: 9b5317a72e user: kmedcalf)
Also, interestingly, with that database using the .expert command causes an abend (with the usual Windows "Sorry for you luck" error message).
|
21:49 |
|
•
(artifact: 0a9545ef17 user: kmedcalf)
In the working case, expr is in the outer loop. In the non-working case, the optimizer errantly puts expr2 in the outer loop.
If you either (a) do a full analyze or (b) delete the statistics tables, then the results work correctly (puts expr in the outer loop).
|
21:44 |
|
•
(artifact: 459974a0b4 user: kmedcalf)
That does not help, but if you do a full analyze then eveything works fine.
|
21:12 |
|
•
(artifact: 1d336a3cca user: 999999999)
The query in the original post gets me my results in about 3.5 secs.
With IN expanded into IN (1261,1776,3003,5741) I'm waiting for a whopping 160 rows for 45 minutes and counting....
_______________________________
SELECT expr.langvar,langvar.uid,expr.txt,expr2.txt,expr2.langvar
FROM denotationx
JOIN expr ON (expr.id = denotationx.expr)
JOIN denotationx denotationx2 ON (denotationx2.meaning = denotationx.meaning)
JOIN expr expr2 ON (expr2.id = denotationx2.expr)
JOIN langvar ON (expr.langvar=langvar.id)
WHERE denotationx.expr != denotationx2.expr AND expr.langvar IN (1261,1776,3003,5741) AND expr2.langvar = 187
ORDER BY expr.langvar;
|
20:27 |
|
•
(artifact: c463ef8552 user: kmedcalf)
Can you post a query that displays the problem you are seeing?
|
19:48 |
|
•
(artifact: d41677e53f user: 999999999)
Hi,
I'm having trouble with this query:
SELECT expr2.txt,expr2.langvar
FROM denotationx
JOIN expr ON (expr.id = denotationx.expr)
JOIN denotationx denotationx2 ON (denotationx2.meaning = denotationx.meaning)
JOIN expr expr2 ON (expr2.id = denotationx2.expr)
WHERE denotationx.expr != denotationx2.expr AND expr.langvar IN (5741,2626) AND expr2.langvar = 620
ORDER BY expr.langvar
The database is largish (6Gb, 76M+ rows). Nevertheless, when I use this query with only two parameters within IN, I get the results within a couple of secs. BUT (!) whenever I try to add even ONE more parameter within IN, the sqlite balks. Just to make sure I waited for an HOUR. Nothing, zilch, nada. A blinking cursor. Though the output should have been 30 measly rows...
Any ideas on how to fix this thing would be extremely welcome...
|
19:37 |
|
•
(artifact: a0c2aba4aa user: 999999999)
Nope. Doesn't work.
It's an open PanLex database. If you can spare 2Gb of traffic, it's available here: https://db.panlex.org/panlex_lite.zip
The schema is:
CREATE TABLE langvar (
id integer PRIMARY KEY,
lang_code text,
var_code integer,
uid text,
meaning integer,
name_expr integer,
name_expr_txt text,
region_expr integer,
region_expr_txt text,
script_expr integer,
script_expr_txt text
);
CREATE TABLE source (
id integer PRIMARY KEY,
grp integer,
label text,
reg_date text,
url text,
isbn text,
author text,
title text,
publisher text,
year text,
quality integer,
note text,
license text,
ip_claim text,
ip_claimant text,
ip_claimant_email text
);
CREATE TABLE expr (
id integer PRIMARY KEY,
langvar integer,
txt text
);
CREATE TABLE denotationx (
meaning integer,
source integer,
grp integer,
quality integer,
expr integer,
langvar integer
);
CREATE VIEW lv AS SELECT id as lv, lang_code as lc, var_code as vc, uid, meaning as mn, name_expr as ex, name_expr_txt as tt, region_expr as rg, region_expr_txt as rgtt, script_expr as sc, script_expr_txt as sctt FROM langvar
/* lv(lv,lc,vc,uid,mn,ex,tt,rg,rgtt,sc,sctt) */;
CREATE VIEW ex AS SELECT id as ex, langvar as lv, txt as tt FROM expr
/* ex(ex,lv,tt) */;
CREATE VIEW dnx AS SELECT meaning as mn, source as ap, grp as ui, quality as uq, expr as ex, langvar as lv FROM denotationx
/* dnx(mn,ap,ui,uq,ex,lv) */;
CREATE INDEX expr_langvar ON expr (langvar);
CREATE INDEX expr_txt_langvar ON expr (txt, langvar);
CREATE INDEX denotationx_meaning ON denotationx (meaning);
CREATE INDEX denotationx_expr ON denotationx (expr);
CREATE INDEX denotationx_langvar ON denotationx (langvar);
CREATE TABLE sqlite_stat1(tbl,idx,stat);
|
18:42 |
|
•
(artifact: 621c786fcc user: drh)
Have you tried running:
PRAGMA analysis_limit=200;
ANALYZE;
And then rerunning your query?
If that doesn't work, then please send us the schema of your database.
|
18:39 |
|
•
(artifact: 11cd947379 user: kmedcalf)
Note that the sqlite3 (pysqlite2) wrapper allows you to "adapt" a non-base type for storage and "convert" it for retrieval. The "conversion" is based on either parsing the column declared type or the query column name (see the documentation).
However, the wrapper retrieves such values as byte strings even if they are of another type (INTEGER, REAL), so you have to handle that:
>python
Python 3.9.1 (tags/v3.9.1:1e5d33e, Dec 7 2020, 17:08:21) [MSC v.1927 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import ctypes
>>> import sqlite3
>>> a = ctypes.c_short(4)
>>> db = sqlite3.connect('', detect_types=sqlite3.PARSE_DECLTYPES)
>>> sqlite3.register_adapter(ctypes.c_short, lambda x: x.value)
>>> sqlite3.register_converter('short', lambda x: ctypes.c_short(int(x)))
>>> db.execute('create table x(x short integer)')
<sqlite3.Cursor object at 0x000001BE2C015AB0>
>>> db.execute('insert into x values (?)', (a,))
<sqlite3.Cursor object at 0x000001BE2C0159D0>
>>> for row in db.execute('select x from x'):
... print(row)
...
(c_short(4),)
>>>
|
18:27 |
|
•
(artifact: 916b7736a3 user: kmedcalf)
Note that the sqlite3 (pysqlite2) wrapper allows you to "adapt" a non-base type for storage and "convert" it for retrieval. The "conversion" is based on either parsing the column declared type or the query column name (see the documentation).
However, the wrapper retrieves such values as byte (text) strings even if they are of another type (INTEGER, REAL), so you have to handle that:
>python
Python 3.9.1 (tags/v3.9.1:1e5d33e, Dec 7 2020, 17:08:21) [MSC v.1927 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import ctypes
>>> import sqlite3
>>> a = ctypes.c_short(4)
>>> db = sqlite3.connect('', detect_types=sqlite3.PARSE_DECLTYPES)
>>> sqlite3.register_adapter(ctypes.c_short, lambda x: x.value)
>>> sqlite3.register_converter('short', lambda x: ctypes.c_short(int(x)))
>>> db.execute('create table x(x short integer)')
<sqlite3.Cursor object at 0x000001BE2C015AB0>
>>> db.execute('insert into x values (?)', (a,))
<sqlite3.Cursor object at 0x000001BE2C0159D0>
>>> for row in db.execute('select x from x'):
... print(row)
...
(c_short(4),)
>>>
|
18:21 |
|
•
(artifact: 014fb9575d user: 999999999)
Hi,
I'm having trouble with this query:
SELECT expr2.txt,expr2.langvar
FROM denotationx
JOIN expr ON (expr.id = denotationx.expr)
JOIN denotationx denotationx2 ON (denotationx2.meaning = denotationx.meaning)
JOIN expr expr2 ON (expr2.id = denotationx2.expr)
WHERE denotationx.expr != denotationx2.expr AND expr.langvar IN (5741,2626) AND expr2.langvar = 620
ORDER BY expr.langvar
The database is largish (6Gb, 26M+ rows). Nevertheless, when I use this query with only two parameters within IN, I get the results within a couple of secs. BUT (!) whenever I try to add even ONE more parameter within IN, the sqlite balks. Just to make sure I waited for an HOUR. Nothing, zilch, nada. A blinking cursor. Though the output should have been 30 measly rows...
Any ideas on how to fix this thing would be extremely welcome...
|
17:51 |
|
•
(artifact: fc008fb3c5 user: kmedcalf)
int32 is a 32-bit integer, as in 4 bytes
int64 is a 64-bit integer, as in 8 bytes
The "root cause" is that when the sqlite3 (pysqlite2) wrapper asks the python variable what "type" it is, it responds that the closest type it is to a supported type is a "byte array", "buffer", or "blob", so the byte-array is bound to the statement as a blob.
pysqlite2 (and apsw) only recognize Python base types None, int (long), float, str (unicode) or types that conform to the buffer protocol.
np.int32 and np.int64 are not python types that correspond to any python type, but do support the buffer protocol, so they are sent to the database as blobs.
Registering the adapters tells the pysqlite2 wrapper layer how to convert that type into a supported python type.
The same thing happens if you used a ctypes type. pysqlite2 does not know that, for example, ctypes.c_short can be converted to a python integer. It does however support the buffer protocol, so the bytes are stored as a blob. If you want to store them as an integer, you have to convert them to an integer first, or register an adapter that can convert the arbitrary type into a recognizable and supported base type.
>python
Python 3.9.1 (tags/v3.9.1:1e5d33e, Dec 7 2020, 17:08:21) [MS
Type "help", "copyright", "credits" or "license" for more inf
>>> import ctypes
>>> a = ctypes.c_short(4)
>>> a
c_short(4)
>>> import sqlite3
>>> db = sqlite3.connect('')
>>> db.execute('create table x(x integer)')
<sqlite3.Cursor object at 0x00000210DBE75AB0>
>>> db.execute('insert into x values (?)', (a,))
<sqlite3.Cursor object at 0x00000210DBE759D0>
>>> for row in db.execute('select x from x'):
... print(row)
...
(b'\x04\x00',)
>>> a.value
4
>>> sqlite3.register_adapter(ctypes.c_short, lambda x: x.value)
>>> db.execute('insert into x values (?)', (a,))
<sqlite3.Cursor object at 0x00000210DBE75AB0>
>>> for row in db.execute('select x from x'):
... print(row)
...
(b'\x04\x00',)
(4,)
>>>
|
17:35 |
|
•
(artifact: 04e24ede9d user: kmedcalf)
int32 is a 32-bit integer, as in 4 bytes
int64 is a 64-bit integer, as in 8 bytes
The "root cause" is that when the sqlite3 (pysqlite2) wrapper asks the python variable what "type" it is, it responds that the closest type it is to a supported type is a "byte array", "buffer", or "blob", so the byte-array is bound to the statement as a blob.
pysqlite2 (and apsw) only recognize Python base types None, int (long), float, str (unicode) or types that conform to the buffer protocol.
np.int32 and np.int64 are not python types that correspond to any python type, but do support the buffer protocol, so they are sent to the database as blobs.
Registering the adapters tells the pysqlite2 wrapper layer how to convert that type into a supported python type.
Type same thing would happen if you used a ctypes type. pysqlite2 does not know that, for example, ctypes.c_short can be converted to a python integer. It does however support the buffer protocol, so the bytes are stored as a blob. If you want to store them as an integer, you have to convert them to an integer first, or register an adapter that can convert the arbitrary type into a recognizable and supported base type.
|
| | |