SQLite Forum

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 ...

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

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.

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)

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.

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).

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

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

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.

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),)
>>>

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,)
>>>