SQLite Forum

Timeline
Login

20 most recent forum posts

2021-03-08
01:36 (Awaiting Moderator Approval) Reply: IN parameters within a query (artifact: 8e9528c270 user: 999999999 (unpublished))
2021-03-07
23:58 Reply: IN parameters within a query (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 Reply: IN parameters within a query (artifact: 988f8b9ebc user: kmedcalf)

OS is likely irrelevant. What version of SQLite3 are you using?

23:26 Reply: IN parameters within a query (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 Reply: IN parameters within a query (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 Reply: IN parameters within a query (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 Edit reply: IN parameters within a query (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 Reply: IN parameters within a query (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 Reply: IN parameters within a query (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 Reply: IN parameters within a query (artifact: 459974a0b4 user: kmedcalf)

That does not help, but if you do a full analyze then eveything works fine.

21:12 Reply: IN parameters within a query (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 Reply: IN parameters within a query (artifact: c463ef8552 user: kmedcalf)

Can you post a query that displays the problem you are seeing?

19:48 Edit: IN parameters within a query (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 Reply: IN parameters within a query (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 Reply: IN parameters within a query (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 Edit reply: Integer becomes blob (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 Reply: Integer becomes blob (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 Post: IN parameters within a query (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 Edit reply: Integer becomes blob (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 Reply: Integer becomes blob (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.

More ↓