SQLite 3.37.2 seems no to be using my collation properly ; worked in 3.31(?)
(1) By Yann Salmon (yannsalmon) on 2022-12-05 10:14:28 [link] [source]
I have been using a SQLite database in a custom python script to manage grading of exams.
I have a custom collation, defined as a python function, to order questions in my reports.
Since September (and an upgrade from Ubuntu 20.04 to 22.04, which entailed upgrades to both python and sqlite), I have not been getting the questions in the right order in my reports. It seems the collation is not properly used.
Or maybe I am doing thins wrong and had been lucky with an anomalous behaviour in previous versions of sqlite.
The questions are numbered (and should appear in that order) q1, q2, q3, ..., q10, q11, ..., q20.a, q20.b, ..., penalite.
The python function for that ordering is
class StringOrInt(str) :
"""Compare strings as numbers if they are numbers, else as strings"""
def __lt__(self, other) :
if self.isdigit() and other.isdigit() :
return int(self) < int(other)
elif not self.isdigit() and not other.isdigit() :
return super().__lt__(other)
elif self.isdigit() :
return True
else :
return False
def ordonner_question(t1, t2) :
"""Relation d'ordre pour la BDD"""
if t1[0] == "q" and t2[0] != "q" :
return -1
if t1[0] != "q" and t2[0] == "q" :
return 1
if t1[0] != "q" and t2[0] != "q" :
if len(t1) < len(t2) :
return -1
elif len(t1) > len(t2) :
return +1
elif t1 < t2 :
return -1
elif t1 > t2 :
return +1
else :
return 0
if t1 == "qcm" :
return 1
elif t2 == "qcm" :
return -1
q1 = [StringOrInt(x) for x in t1[1:].split(".")]
q2 = [StringOrInt(x) for x in t2[1:].split(".")]
if q1 < q2 :
return -1
if q1 > q2 :
return 1
return 0
In a MWE, the db is set up as follows :
import sqlite3
bdd = sqlite3.connect(":memory:")
bdd.create_collation("ordonner_quest", ordonner_question)
bdd.executescript("""
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;
-- Table: mp_opt_dec_struct
CREATE TABLE mp_opt_dec_struct (question text NOT NULL, item text NOT NULL, bareme int NOT NULL, points real, PRIMARY KEY(question, item));
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('penalite', '', 8, -4.0);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q1', 'exemple', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q2', 'exemple', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q3', 'exemple', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q4', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q5', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q6', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q7', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q8', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q8', 'compl', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q9', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q9', 'compl', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q10', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q11', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q12', 'compl', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q13', 'exemple', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q14', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q14', 'compl', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q15', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q16', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q17', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q18', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q18', 'compl', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q19', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q19', 'compl', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q20.a', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q20.b', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q21', 'exemple', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q22', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q23', 'algo', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q24', 'exemple', 9, NULL);
COMMIT TRANSACTION;
PRAGMA foreign_keys = on;""")
bdd.commit()
Then, data is extracted again with
l1 = bdd.execute("select question, item from mp_opt_dec_struct order by question collate ordonner_quest, item;").fetchall()
which gives
[('q1', 'exemple'), ('q2', 'exemple'), ('q10', 'prog'), ('q11', 'prog'), ('q12', 'compl'), ('q13', 'exemple'), ('q14', 'compl'), ('q14', 'prog'), ('q15', 'prog'), ('q16', 'prog'), ('q17', 'prog'), ('q18', 'compl'), ('q18', 'prog'), ('q19', 'compl'), ('q19', 'prog'), ('q20.a', 'raiso'), ('q20.b', 'raiso'), ('q21', 'exemple'), ('q22', 'raiso'), ('q23', 'algo'), ('q24', 'exemple'), ('q3', 'exemple'), ('q4', 'raiso'), ('q5', 'raiso'), ('q6', 'raiso'), ('q7', 'raiso'), ('q8', 'compl'), ('q8', 'prog'), ('q9', 'compl'), ('q9', 'prog'), ('penalite', '')]
An unsatisfactory result because q10 comes before q3.
Sorting the data with Python gives the wanted order :
class MS(str) :
def __lt__(self, other) :
return ordonner_question(self, other) == -1
l2 = [(MS(x[0]), x[1]) for x in bdd.execute("select question, item from mp_opt_dec_struct")]
giving
[('q1', 'exemple'), ('q2', 'exemple'), ('q3', 'exemple'), ('q4', 'raiso'), ('q5', 'raiso'), ('q6', 'raiso'), ('q7', 'raiso'), ('q8', 'compl'), ('q8', 'prog'), ('q9', 'compl'), ('q9', 'prog'), ('q10', 'prog'), ('q11', 'prog'), ('q12', 'compl'), ('q13', 'exemple'), ('q14', 'compl'), ('q14', 'prog'), ('q15', 'prog'), ('q16', 'prog'), ('q17', 'prog'), ('q18', 'compl'), ('q18', 'prog'), ('q19', 'compl'), ('q19', 'prog'), ('q20.a', 'raiso'), ('q20.b', 'raiso'), ('q21', 'exemple'), ('q22', 'raiso'), ('q23', 'algo'), ('q24', 'exemple'), ('penalite', '')]
Again, in June, I think I had the correct order (presumably with sqlite v3.31 or lower). The current version of the sqlite3 python module is 2.6.0. It dates back to 2010, so I assume it was the same version in June.
(2) By Gunter Hick (gunter_hick) on 2022-12-05 10:59:45 in reply to 1 [link] [source]
Have you tried tracing the calls to your collation function and verifying that it gives the correct results? Does the result change if you move the collation sequence into the table definition (i.e. question TEXT NOT NULL COLLATE ordonner_quest)?
(3) By Yann Salmon (yannsalmon) on 2022-12-05 11:30:58 in reply to 2 [link] [source]
Have you tried tracing the calls to your collation function and verifying that it gives the correct results?
The collation function gives correct results. The following comparisons are made during the SQL request :
q9 q9
q8 q8
q8 q9
q8 q9
q6 q7
q4 q5
q4 q6
q5 q6
q4 q8
q5 q8
q6 q8
q7 q8
q24 q3
q22 q23
q22 q24
q23 q24
q20.b q21
q2 q20.a
q2 q20.b
q20.a q20.b
q2 q22
q20.a q22
q20.b q22
q21 q22
q2 q4
q20.a q4
q20.b q4
q21 q4
q22 q4
q23 q4
q24 q4
q3 q4
q19 q19
q18 q18
q18 q19
q18 q19
q16 q17
q14 q15
q14 q16
q15 q16
q14 q18
q15 q18
q16 q18
q17 q18
q13 q14
q11 q12
q11 q13
q12 q13
q1 q10
penalite q1
penalite q10
q1 q11
q10 q11
penalite q11
penalite q12
penalite q13
penalite q14
q1 q14
q10 q14
q11 q14
q12 q14
q13 q14
q14 q14
penalite q14
penalite q15
penalite q16
penalite q17
penalite q18
penalite q18
penalite q19
penalite q19
q1 q2
q10 q2
q10 q20.a
q11 q20.a
q12 q20.a
q13 q20.a
q14 q20.a
q14 q20.a
q15 q20.a
q16 q20.a
q17 q20.a
q18 q20.a
q18 q20.a
q19 q20.a
q19 q20.a
penalite q20.a
penalite q20.b
penalite q21
penalite q22
penalite q23
penalite q24
penalite q3
penalite q4
penalite q5
penalite q6
penalite q7
penalite q8
penalite q8
penalite q9
penalite q9
Sorting with python does not make the same comparisons, but in itself it is not shocking ; the sorting algorithm must be different.
Does the result change if you move the collation sequence into the table definition (i.e. question TEXT NOT NULL COLLATE ordonner_quest)?
Yes it does ; thanks for the idea !
The comparisons made are now
penalite q1
q1 q2
penalite q2
q2 q3
penalite q3
q2 q4
q3 q4
penalite q4
q3 q5
q4 q5
penalite q5
q3 q6
q5 q6
penalite q6
q4 q7
q6 q7
penalite q7
q4 q8
q6 q8
q7 q8
penalite q8
q5 q8
q7 q8
q8 q8
q5 q9
q8 q9
q8 q9
penalite q9
q6 q9
q8 q9
q9 q9
q6 q10
q8 q10
q9 q10
penalite q10
q7 q11
q9 q11
q10 q11
penalite q11
q7 q12
q9 q12
q11 q12
penalite q12
q8 q13
q10 q13
q12 q13
penalite q13
q8 q14
q10 q14
q12 q14
q13 q14
penalite q14
q8 q14
q11 q14
q13 q14
q14 q14
q8 q15
q12 q15
q14 q15
q14 q15
penalite q15
q9 q16
q13 q16
q14 q16
q15 q16
penalite q16
q9 q17
q13 q17
q15 q17
q16 q17
penalite q17
q9 q18
q14 q18
q16 q18
q17 q18
penalite q18
q9 q18
q14 q18
q17 q18
q18 q18
q10 q19
q15 q19
q18 q19
q18 q19
penalite q19
q10 q19
q15 q19
q18 q19
q19 q19
q18 q19
q11 q20.a
q16 q20.a
q18 q20.a
q19 q20.a
penalite q20.a
q11 q20.b
q17 q20.b
q19 q20.b
q20.a q20.b
penalite q20.b
q12 q21
q18 q21
q19 q21
q20.b q21
penalite q21
q12 q22
q18 q22
q20.a q22
q21 q22
penalite q22
q13 q23
q18 q23
q20.b q23
q22 q23
penalite q23
q13 q24
q19 q24
q21 q24
q23 q24
penalite q24
and the end result is correct.
However, I cannot put the collation in the table definition in production, because I need to also access this DB from outside python, where the corresponding function is not available.
But this points to a bug in SQLite, does it not ?
(4) By Stephan Beal (stephan) on 2022-12-05 11:38:40 in reply to 3 [link] [source]
But this points to a bug in SQLite, does it not ?
Given the extreme amount of testing sqlite goes through to prevent such regressions, the culprit seems more likely to be the python/sqlite3 binding layer. Thats not to say it's impossible that you've uncovered an sqlite3 bug, but the odds are good that the 3rd-party binding is the culprit.
Your report will get a lot more traction here if you can demonstrate the incorrect behavior using only sqlite3's API, as opposed to a python binding layer (none of which are maintained by the sqlite developers). As soon as a 3rd-party product is required for reproducing a problem, the suspicion immediately shifts to that product.
(5) By Yann Salmon (yannsalmon) on 2022-12-05 12:06:37 in reply to 4 [link] [source]
using only sqlite3's API
Would not that require rewriting the MWE in the C language ?
the odds are good that the 3rd-party binding is the culprit
So I should report it to pysqlite.
(6.1) By Stephan Beal (stephan) on 2022-12-05 12:13:39 edited from 6.0 in reply to 5 [link] [source]
Would not that require rewriting the MWE in the C language ?
C is the only language this project uses. Most 3rd-party bindings use some other language, but every level of code which gets introduced between you and sqlite increases the odds that the problem you're seeing is not in sqlite itself. Again, it's not impossible, but regressions of the nature you're reporting are relatively rare in this project because the test suite is so huge and is constantly adapted to cover new features as well as fixed bugs (to make sure they don't reappear).
So I should report it to pysqlite.
It couldn't hurt. They would be in a better position to determine whether it's a bug on their end or this end, and very likely in a position to demonstrate, in the latter case, how to reproduce it without a level of python sitting between the bug and sqlite.
(7) By Gunter Hick (gunter_hick) on 2022-12-05 12:26:41 in reply to 3 [link] [source]
If I read your Python code correctly, "penalite" should come out greater than anything starting with "q". You have not shown the result value of the comparisons. Thus it is not possible to verify the transitive nature of the function, i.e. A < B and B < C implies A < C. If transitivity is violated, then the result will change depending on the order of the comparisons and their arguments.
(8) By Yann Salmon (yannsalmon) on 2022-12-05 12:31:04 in reply to 6.1 [link] [source]
I understand. However, while I can read "simple" C and maybe modify some, my doing unicode string manipulation in that language would likely require more than a whole day, and produce a program containing more bugs than both SQLite and pysqlite together could ever.
We'll see how the pysqlite project ressponds to my report.
(9.1) By Yann Salmon (yannsalmon) on 2022-12-05 12:59:43 edited from 9.0 in reply to 7 [link] [source]
In practice, if I memoize the ordonner_quest function then do
s = {x[0] for x in memo} | {x[1] for x in memo}
for a in s :
for b in s :
if (a,b) in memo and memo[(a,b)] == 1 :
if (b,a) in memo and memo[(b,a)] != -1 :
print(a,b)
break
memo[(b,a)]=-1
for a in s :
for b in s :
for c in s :
if (a,b) in memo and (b,c) in memo and (a,c) in memo :
if memo[(a,b)]==-1 and memo[(b,c)]==-1 and memo[(a,c)] != -1 :
print(a, b, c)
break
print("fini")
I get no inconsistency.
More generally, assume A<B and B<C.
If neither A, B and C begin with "q", they are compared by radicial order, thus A<C.
If C begins with "q", then B must begin with "q" (else ordonner_quest(B, C) would equal 1 by the second alternative condition), and thus A too. Therefore, unless one of them is "qcm", which we'll exclude as it is not in the MWE, they are compared by a lexicographical ordering of the "."-separated list, based on the StringOrInt order, that is either the integer order or the standard string order (the case whre one part is a digit and the corresponding part in the other question number is not does not happen in practice). Thus A<C.
If C does not begin with "q", but B does, then so does A. Because A begins with "q" and C does not, A<C by the first conditional instruction.
If C and B do not begin with "q", but A does, then A<C.
(10) By Kees Nuyt (knu) on 2022-12-05 13:12:52 in reply to 8 [link] [source]
drh already proposed an SQLite extension for collation natural sort.
(11) By Keith Medcalf (kmedcalf) on 2022-12-05 13:26:22 in reply to 9.0 [link] [source]
Very interesting.
run the MWE (whatever that is) such that the database is created and immediately after the commit eject to an interactive command prompt. That is, create a file mwe.py as follows:
class StringOrInt(str) :
"""Compare strings as numbers if they are numbers, else as strings"""
def __lt__(self, other) :
if self.isdigit() and other.isdigit() :
return int(self) < int(other)
elif not self.isdigit() and not other.isdigit() :
return super().__lt__(other)
elif self.isdigit() :
return True
else :
return False
def ordonner_question(t1, t2) :
"""Relation d'ordre pour la BDD"""
if t1[0] == "q" and t2[0] != "q" :
return -1
if t1[0] != "q" and t2[0] == "q" :
return 1
if t1[0] != "q" and t2[0] != "q" :
if len(t1) < len(t2) :
return -1
elif len(t1) > len(t2) :
return +1
elif t1 < t2 :
return -1
elif t1 > t2 :
return +1
else :
return 0
if t1 == "qcm" :
return 1
elif t2 == "qcm" :
return -1
q1 = [StringOrInt(x) for x in t1[1:].split(".")]
q2 = [StringOrInt(x) for x in t2[1:].split(".")]
if q1 < q2 :
return -1
if q1 > q2 :
return 1
return 0
import sqlite3
bdd = sqlite3.connect(":memory:")
bdd.create_collation("ordonner_quest", ordonner_question)
bdd.executescript("""
BEGIN TRANSACTION;
-- Table: mp_opt_dec_struct
CREATE TABLE mp_opt_dec_struct (question text NOT NULL, item text NOT NULL, bareme int NOT NULL, points real, PRIMARY KEY(question, item));
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('penalite', '', 8, -4.0);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q1', 'exemple', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q2', 'exemple', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q3', 'exemple', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q4', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q5', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q6', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q7', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q8', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q8', 'compl', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q9', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q9', 'compl', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q10', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q11', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q12', 'compl', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q13', 'exemple', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q14', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q14', 'compl', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q15', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q16', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q17', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q18', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q18', 'compl', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q19', 'prog', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q19', 'compl', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q20.a', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q20.b', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q21', 'exemple', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q22', 'raiso', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q23', 'algo', 9, NULL);
INSERT INTO mp_opt_dec_struct (question, item, bareme, points) VALUES ('q24', 'exemple', 9, NULL);
COMMIT TRANSACTION;
""")
bdd.commit()
Then issue the following command:
python -i mwe.py
At the Python prompt execute the following commands:
for row in bdd.execute("select question, item from mp_opt_dec_struct order by question collate ordonner_quest, item;"): print(row)
and then
for row in bdd.execute("select question, item from mp_opt_dec_struct not indexed order by question collate ordonner_quest, item;"): print(row)
Which basically shows that the "order by question collate ordonner_quest, item" is doing something which is using an index where it ought not.
The results, using Python 3.11, out-of-the-box with the exception that the sqlite3.dll included therewith is replaced with a custom built dll from the tip of trunk is as follows:
>python -i mwe.py
>>> for row in bdd.execute("select question, item from mp_opt_dec_struct order by question collate ordonner_quest, item;"): print(row)
...
('q1', 'exemple')
('q2', 'exemple')
('q10', 'prog')
('q11', 'prog')
('q12', 'compl')
('q13', 'exemple')
('q14', 'compl')
('q14', 'prog')
('q15', 'prog')
('q16', 'prog')
('q17', 'prog')
('q18', 'compl')
('q18', 'prog')
('q19', 'compl')
('q19', 'prog')
('q20.a', 'raiso')
('q20.b', 'raiso')
('q21', 'exemple')
('q22', 'raiso')
('q23', 'algo')
('q24', 'exemple')
('q3', 'exemple')
('q4', 'raiso')
('q5', 'raiso')
('q6', 'raiso')
('q7', 'raiso')
('q8', 'compl')
('q8', 'prog')
('q9', 'compl')
('q9', 'prog')
('penalite', '')
>>> for row in bdd.execute("select question, item from mp_opt_dec_struct not indexed order by question collate ordonner_quest, item;"): print(row)
...
('q1', 'exemple')
('q2', 'exemple')
('q3', 'exemple')
('q4', 'raiso')
('q5', 'raiso')
('q6', 'raiso')
('q7', 'raiso')
('q8', 'compl')
('q8', 'prog')
('q9', 'compl')
('q9', 'prog')
('q10', 'prog')
('q11', 'prog')
('q12', 'compl')
('q13', 'exemple')
('q14', 'compl')
('q14', 'prog')
('q15', 'prog')
('q16', 'prog')
('q17', 'prog')
('q18', 'compl')
('q18', 'prog')
('q19', 'compl')
('q19', 'prog')
('q20.a', 'raiso')
('q20.b', 'raiso')
('q21', 'exemple')
('q22', 'raiso')
('q23', 'algo')
('q24', 'exemple')
('penalite', '')
>>>
(12) By Keith Medcalf (kmedcalf) on 2022-12-05 13:32:44 in reply to 9.1 [link] [source]
For S's and G's, note the following:
>>> for row in bdd.execute("select question, item from mp_opt_dec_struct not indexed order by question collate ordonner_quest desc, item desc;"): print(row)
...
('penalite', '')
('q24', 'exemple')
('q23', 'algo')
('q22', 'raiso')
('q21', 'exemple')
('q20.b', 'raiso')
('q20.a', 'raiso')
('q19', 'prog')
('q19', 'compl')
('q18', 'prog')
('q18', 'compl')
('q17', 'prog')
('q16', 'prog')
('q15', 'prog')
('q14', 'prog')
('q14', 'compl')
('q13', 'exemple')
('q12', 'compl')
('q11', 'prog')
('q10', 'prog')
('q9', 'prog')
('q9', 'compl')
('q8', 'prog')
('q8', 'compl')
('q7', 'raiso')
('q6', 'raiso')
('q5', 'raiso')
('q4', 'raiso')
('q3', 'exemple')
('q2', 'exemple')
('q1', 'exemple')
>>> for row in bdd.execute("select question, item from mp_opt_dec_struct order by question collate ordonner_quest desc, item desc;"): print(row)
...
('penalite', '')
('q19', 'prog')
('q9', 'prog')
('q24', 'exemple')
('q21', 'exemple')
('q20.b', 'raiso')
('q20.a', 'raiso')
('q19', 'compl')
('q18', 'prog')
('q18', 'compl')
('q17', 'prog')
('q16', 'prog')
('q15', 'prog')
('q14', 'prog')
('q14', 'compl')
('q13', 'exemple')
('q12', 'compl')
('q11', 'prog')
('q10', 'prog')
('q9', 'compl')
('q8', 'prog')
('q8', 'compl')
('q7', 'raiso')
('q6', 'raiso')
('q5', 'raiso')
('q4', 'raiso')
('q3', 'exemple')
('q23', 'algo')
('q22', 'raiso')
('q2', 'exemple')
('q1', 'exemple')
>>>
(13.1) By Keith Medcalf (kmedcalf) on 2022-12-05 13:50:36 edited from 13.0 in reply to 11 [link] [source]
And if you use the uint collation, you get:
>>> for row in bdd.execute('select * from mp_opt_dec_struct order by question collate uint, item'): print(row)
...
('penalite', '', 8, -4.0)
('q1', 'exemple', 9, None)
('q2', 'exemple', 9, None)
('q3', 'exemple', 9, None)
('q4', 'raiso', 9, None)
('q5', 'raiso', 9, None)
('q6', 'raiso', 9, None)
('q7', 'raiso', 9, None)
('q8', 'compl', 9, None)
('q8', 'prog', 9, None)
('q9', 'compl', 9, None)
('q9', 'prog', 9, None)
('q10', 'prog', 9, None)
('q11', 'prog', 9, None)
('q12', 'compl', 9, None)
('q13', 'exemple', 9, None)
('q14', 'compl', 9, None)
('q14', 'prog', 9, None)
('q15', 'prog', 9, None)
('q16', 'prog', 9, None)
('q17', 'prog', 9, None)
('q18', 'compl', 9, None)
('q18', 'prog', 9, None)
('q19', 'compl', 9, None)
('q19', 'prog', 9, None)
('q20.a', 'raiso', 9, None)
('q20.b', 'raiso', 9, None)
('q21', 'exemple', 9, None)
('q22', 'raiso', 9, None)
('q23', 'algo', 9, None)
('q24', 'exemple', 9, None)
>>> for row in bdd.execute('select * from mp_opt_dec_struct order by question collate uint desc, item desc'): print(row)
...
('q24', 'exemple', 9, None)
('q23', 'algo', 9, None)
('q22', 'raiso', 9, None)
('q21', 'exemple', 9, None)
('q20.b', 'raiso', 9, None)
('q20.a', 'raiso', 9, None)
('q19', 'prog', 9, None)
('q19', 'compl', 9, None)
('q18', 'prog', 9, None)
('q18', 'compl', 9, None)
('q17', 'prog', 9, None)
('q16', 'prog', 9, None)
('q15', 'prog', 9, None)
('q14', 'prog', 9, None)
('q14', 'compl', 9, None)
('q13', 'exemple', 9, None)
('q12', 'compl', 9, None)
('q11', 'prog', 9, None)
('q10', 'prog', 9, None)
('q9', 'prog', 9, None)
('q9', 'compl', 9, None)
('q8', 'prog', 9, None)
('q8', 'compl', 9, None)
('q7', 'raiso', 9, None)
('q6', 'raiso', 9, None)
('q5', 'raiso', 9, None)
('q4', 'raiso', 9, None)
('q3', 'exemple', 9, None)
('q2', 'exemple', 9, None)
('q1', 'exemple', 9, None)
('penalite', '', 8, -4.0)
>>>
So I can only conclude that the python sorter is broken ...
NOTE this should be a followon reply to 9 so that it appears in the correct order.
(14) By David Raymond (dvdraymond) on 2022-12-05 13:51:57 in reply to 1 [link] [source]
I think your problem is the line
> if q1 > q2:
in the odonner_question function.
You only defined the __lt__ operator for your StringOrInt class (which inherits from str)
So when you get down to if q1 > q2, it doesn't use StringOrInt's __lt__, it uses the built int str class's __gt__
I put in print statements for every return, and you can see if you do ordonner_question("q24", "q3") it returns saying they're equal.
It gets down to
if q1 < q2...
and compares them as StringOrInt's and gets False becase 24 < 3 is False.
It then gets to
if q1 > q2
and there is no call to StringOrInt's __lt__, so it must be comparing them as strings. "24" > "3" returns False, so it goes down to the "return 0" line, saying the two are equal.
Either define __gt__ for StringOrInt, or change that line to
if q2 < q1
(15.1) By Keith Medcalf (kmedcalf) on 2022-12-05 14:02:03 edited from 15.0 in reply to 9.1 [link] [source]
Note the bytecode.
>>> for row in bdd.execute('select * from bytecode where stmt=?', ("select question, item from mp_opt_dec_struct order by question collate ordonner_quest, item;",)): print(row)
...
(0, 'Init', 0, 24, 0, None, 0, 'Start at 24', None)
(1, 'SorterOpen', 1, 5, 0, 'k(2,ordonner_quest,B)', 0, None, None)
(2, 'OpenRead', 2, 3, 0, 'k(2,,)', 0, 'root=3 iDb=0; sqlite_autoindex_mp_opt_dec_struct_1', None)
(3, 'ColumnsUsed', 2, 0, 0, '3', 0, None, None)
(4, 'Explain', 4, 0, 0, 'SCAN mp_opt_dec_struct USING COVERING INDEX sqlite_autoindex_mp_opt_dec_struct_1 (~1048576 rows)', 0, None, None)
(5, 'Noop', 0, 0, 0, None, 0, 'Begin WHERE-loop0: mp_opt_dec_struct', None)
(6, 'Rewind', 2, 15, 1, '0', 0, None, None)
(7, 'Noop', 0, 0, 0, None, 0, 'Begin WHERE-core', None)
(8, 'Column', 2, 0, 3, None, 0, 'r[3]= cursor 2 column 0', None)
(9, 'Column', 2, 0, 1, None, 0, 'r[1]= cursor 2 column 0', None)
(10, 'Column', 2, 1, 2, None, 0, 'r[2]= cursor 2 column 1', None)
(11, 'MakeRecord', 1, 3, 5, None, 0, 'r[5]=mkrec(r[1..3])', None)
(12, 'SorterInsert', 1, 5, 1, '3', 0, 'key=r[5]', None)
(13, 'Noop', 0, 0, 0, None, 0, 'End WHERE-core', None)
(14, 'Next', 2, 7, 0, None, 1, None, None)
(15, 'Noop', 0, 0, 0, None, 0, 'End WHERE-loop0: mp_opt_dec_struct', None)
(16, 'OpenPseudo', 3, 6, 5, None, 0, '5 columns in r[6]', None)
(17, 'SorterSort', 1, 23, 0, None, 0, None, None)
(18, 'SorterData', 1, 6, 3, None, 0, 'r[6]=data', None)
(19, 'Column', 3, 1, 4, None, 0, 'r[4]=item', None)
(20, 'Column', 3, 2, 3, None, 0, 'r[3]=question', None)
(21, 'ResultRow', 3, 2, 0, None, 0, 'output=r[3..4]', None)
(22, 'SorterNext', 1, 18, 0, None, 0, None, None)
(23, 'Halt', 0, 0, 0, None, 0, None, None)
(24, 'Transaction', 0, 0, 1, '0', 1, 'usesStmtJournal=0', None)
(25, 'Goto', 0, 1, 0, None, 0, None, None)
>>> for row in bdd.execute('select * from bytecode where stmt=?', ("select question, item from mp_opt_dec_struct not indexed order by question collate ordonner_quest, item;",)): print(row)
...
(0, 'Init', 0, 24, 0, None, 0, 'Start at 24', None)
(1, 'SorterOpen', 1, 5, 0, 'k(2,ordonner_quest,B)', 0, None, None)
(2, 'OpenRead', 0, 2, 0, '2', 0, 'root=2 iDb=0; mp_opt_dec_struct', None)
(3, 'ColumnsUsed', 0, 0, 0, '3', 0, None, None)
(4, 'Explain', 4, 0, 0, 'SCAN mp_opt_dec_struct (~1048576 rows)', 0, None, None)
(5, 'Noop', 0, 0, 0, None, 0, 'Begin WHERE-loop0: mp_opt_dec_struct', None)
(6, 'Rewind', 0, 15, 0, None, 0, None, None)
(7, 'Noop', 0, 0, 0, None, 0, 'Begin WHERE-core', None)
(8, 'Column', 0, 0, 3, None, 0, 'r[3]= cursor 0 column 0', None)
(9, 'Column', 0, 0, 1, None, 0, 'r[1]= cursor 0 column 0', None)
(10, 'Column', 0, 1, 2, None, 0, 'r[2]= cursor 0 column 1', None)
(11, 'MakeRecord', 1, 3, 5, None, 0, 'r[5]=mkrec(r[1..3])', None)
(12, 'SorterInsert', 1, 5, 1, '3', 0, 'key=r[5]', None)
(13, 'Noop', 0, 0, 0, None, 0, 'End WHERE-core', None)
(14, 'Next', 0, 7, 0, None, 1, None, None)
(15, 'Noop', 0, 0, 0, None, 0, 'End WHERE-loop0: mp_opt_dec_struct', None)
(16, 'OpenPseudo', 2, 6, 5, None, 0, '5 columns in r[6]', None)
(17, 'SorterSort', 1, 23, 0, None, 0, None, None)
(18, 'SorterData', 1, 6, 2, None, 0, 'r[6]=data', None)
(19, 'Column', 2, 1, 4, None, 0, 'r[4]=item', None)
(20, 'Column', 2, 2, 3, None, 0, 'r[3]=question', None)
(21, 'ResultRow', 3, 2, 0, None, 0, 'output=r[3..4]', None)
(22, 'SorterNext', 1, 18, 0, None, 0, None, None)
(23, 'Halt', 0, 0, 0, None, 0, None, None)
(24, 'Transaction', 0, 0, 1, '0', 1, 'usesStmtJournal=0', None)
(25, 'Goto', 0, 1, 0, None, 0, None, None)
>>>
The only difference is the presentement order to the sorter (collation function). The current tip prefers to use the index to feed the sorter because it will obviously be faster (read less data) than scanning the underlying table.
(16.1) By Yann Salmon (yannsalmon) on 2022-12-05 14:27:16 edited from 16.0 in reply to 14 [link] [source]
Oh, I think you nailed it !
And probably, I had been lucky for many years that the implementation of collation ordering happened not to trigger that on my data.
That or Python's behaviour relating to __lt__ and __gt__ changed (I remember reading that x > y would call y.__lt__(x) if there was no __gt__ ; but there may be a subtlety with inheritance indeed, or maybe a __gt__ was added in str ?).
(17) By David Raymond (dvdraymond) on 2022-12-05 15:38:37 in reply to 16.1 [link] [source]
> I remember reading that x > y would call y.__lt__(x) if there was no __gt__ > or maybe a __gt__ was added in str ? Correct on both of these. Since StringOrInt is a subclass of str, it has all of str's methods. You overrode __lt__, but str has all the normal comparison methods, so it did find a __gt__ when it looked for it.
(18) By Yann Salmon (yannsalmon) on 2022-12-05 18:58:13 in reply to 17 [link] [source]
Note to self : always define
def __gt__(self, other) :
return other.__lt__(self)
(19) By Yann Salmon (yannsalmon) on 2022-12-05 19:04:04 in reply to 15.1 [link] [source]
Thank you ; I had no idea one could get such information with a special statement.
(20) By Keith Medcalf (kmedcalf) on 2022-12-05 20:11:03 in reply to 19 [link] [source]
You would, of course, have to have compiled your own library with the BYTECODE_VTAB enabled.
There are quite a lot of enhancements that you can enable if you compile your own library and quite a lot of extensions that you can compile yourself.
(21) By David Raymond (dvdraymond) on 2022-12-05 20:14:25 in reply to 19 [link] [source]
Most of that is available just by using EXPLAIN
The Bytecode Virtual Table (which I didn't know about til now) adds one more field with info on whether that line is for a trigger or foreign key.
Looks like Keith also has some added extras in his output I'm not familiar with. Row counts in the Explain opcode, a ColumnsUsed opcode, and a Noop opcode with extra info.
(22) By Keith Medcalf (kmedcalf) on 2022-12-05 21:16:51 in reply to 21 [link] [source]
Those are enablable standard options, you just have to enable them when you build your shell/library. There is also the explain.c extension, which basically produces the same tabular output as executing "explain ..." by using "select * from explain where sql='...'", ie, what the shell does, but without the shell.
#define SQLITE_ENABLE_COLUMN_METADATA 1 // Add Column Metadata Functions
#define SQLITE_ENABLE_COLUMN_USED_MASK 1 // Enable Column Used Hints
#define SQLITE_ENABLE_COSTMULT 1 // Use Cost Multiplication Factors
#define SQLITE_ENABLE_CURSOR_HINTS 1 // Output Cursor Hints to B-Tree Layer
#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1 // Add Comments to Explain
#define SQLITE_ENABLE_MODULE_COMMENTS 1 // Add Module Comments to Explain
#define SQLITE_EXPLAIN_ESTIMATED_ROWS 1 // Add Estimated Rows to Explain
(23) By Keith Medcalf (kmedcalf) on 2022-12-05 21:22:50 in reply to 21 [link] [source]
That is:
sqlite> select * from explain where sql='select value from wholenumber where value between 1 and 10';
┌──────┬───────────────┬────┬────┬────┬───────────────────────────────────────────────────────┬────┬──────────────────────────────────┐
│ addr │ opcode │ p1 │ p2 │ p3 │ p4 │ p5 │ comment │
├──────┼───────────────┼────┼────┼────┼───────────────────────────────────────────────────────┼────┼──────────────────────────────────┤
│ 0 │ 'Init' │ 0 │ 16 │ 0 │ NULL │ 0 │ 'Start at 16' │
│ 1 │ 'VOpen' │ 0 │ 0 │ 0 │ 'vtab:25A3FF3D640' │ 0 │ NULL │
│ 2 │ 'Explain' │ 2 │ 0 │ 0 │ 'SCAN wholenumber VIRTUAL TABLE INDEX 10: (~24 rows)' │ 0 │ NULL │
│ 3 │ 'Noop' │ 0 │ 0 │ 0 │ NULL │ 0 │ 'Begin WHERE-loop0: wholenumber' │
│ 4 │ 'Integer' │ 1 │ 3 │ 0 │ NULL │ 0 │ 'r[3]=1' │
│ 5 │ 'Integer' │ 10 │ 4 │ 0 │ NULL │ 0 │ 'r[4]=10' │
│ 6 │ 'Integer' │ 10 │ 1 │ 0 │ NULL │ 0 │ 'r[1]=10' │
│ 7 │ 'Integer' │ 2 │ 2 │ 0 │ NULL │ 0 │ 'r[2]=2' │
│ 8 │ 'VFilter' │ 0 │ 14 │ 1 │ NULL │ 0 │ 'iplan=r[1] zplan=''''' │
│ 9 │ 'Noop' │ 0 │ 0 │ 0 │ NULL │ 0 │ 'Begin WHERE-core' │
│ 10 │ 'VColumn' │ 0 │ 0 │ 5 │ NULL │ 0 │ 'r[5]=vcolumn(0)' │
│ 11 │ 'ResultRow' │ 5 │ 1 │ 0 │ NULL │ 0 │ 'output=r[5]' │
│ 12 │ 'Noop' │ 0 │ 0 │ 0 │ NULL │ 0 │ 'End WHERE-core' │
│ 13 │ 'VNext' │ 0 │ 9 │ 0 │ NULL │ 0 │ NULL │
│ 14 │ 'Noop' │ 0 │ 0 │ 0 │ NULL │ 0 │ 'End WHERE-loop0: wholenumber' │
│ 15 │ 'Halt' │ 0 │ 0 │ 0 │ NULL │ 0 │ NULL │
│ 16 │ 'Transaction' │ 0 │ 0 │ 0 │ '0' │ 1 │ 'usesStmtJournal=0' │
│ 17 │ 'Goto' │ 0 │ 1 │ 0 │ NULL │ 0 │ NULL │
└──────┴───────────────┴────┴────┴────┴───────────────────────────────────────────────────────┴────┴──────────────────────────────────┘
sqlite> explain select value from wholenumber where value between 1 and 10;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 16 0 0 Start at 16
1 VOpen 0 0 0 vtab:25A3FF3D640 0
2 Explain 2 0 0 SCAN wholenumber VIRTUAL TABLE INDEX 10: (~24 rows) 0
3 Noop 0 0 0 0 Begin WHERE-loop0: wholenumber
4 Integer 1 3 0 0 r[3]=1
5 Integer 10 4 0 0 r[4]=10
6 Integer 10 1 0 0 r[1]=10
7 Integer 2 2 0 0 r[2]=2
8 VFilter 0 14 1 0 iplan=r[1] zplan=''
9 Noop 0 0 0 0 Begin WHERE-core
10 VColumn 0 0 5 0 r[5]=vcolumn(0)
11 ResultRow 5 1 0 0 output=r[5]
12 Noop 0 0 0 0 End WHERE-core
13 VNext 0 9 0 0
14 Noop 0 0 0 0 End WHERE-loop0: wholenumber
15 Halt 0 0 0 0
16 Transaction 0 0 0 0 1 usesStmtJournal=0
17 Goto 0 1 0 0
sqlite> select * from bytecode where stmt='select value from wholenumber where value between 1 and 10';
┌──────┬───────────────┬────┬────┬────┬───────────────────────────────────────────────────────┬────┬──────────────────────────────────┬─────────┐
│ addr │ opcode │ p1 │ p2 │ p3 │ p4 │ p5 │ comment │ subprog │
├──────┼───────────────┼────┼────┼────┼───────────────────────────────────────────────────────┼────┼──────────────────────────────────┼─────────┤
│ 0 │ 'Init' │ 0 │ 16 │ 0 │ NULL │ 0 │ 'Start at 16' │ NULL │
│ 1 │ 'VOpen' │ 0 │ 0 │ 0 │ 'vtab:25A3FF3D640' │ 0 │ NULL │ NULL │
│ 2 │ 'Explain' │ 2 │ 0 │ 0 │ 'SCAN wholenumber VIRTUAL TABLE INDEX 10: (~24 rows)' │ 0 │ NULL │ NULL │
│ 3 │ 'Noop' │ 0 │ 0 │ 0 │ NULL │ 0 │ 'Begin WHERE-loop0: wholenumber' │ NULL │
│ 4 │ 'Integer' │ 1 │ 3 │ 0 │ NULL │ 0 │ 'r[3]=1' │ NULL │
│ 5 │ 'Integer' │ 10 │ 4 │ 0 │ NULL │ 0 │ 'r[4]=10' │ NULL │
│ 6 │ 'Integer' │ 10 │ 1 │ 0 │ NULL │ 0 │ 'r[1]=10' │ NULL │
│ 7 │ 'Integer' │ 2 │ 2 │ 0 │ NULL │ 0 │ 'r[2]=2' │ NULL │
│ 8 │ 'VFilter' │ 0 │ 14 │ 1 │ NULL │ 0 │ 'iplan=r[1] zplan=''''' │ NULL │
│ 9 │ 'Noop' │ 0 │ 0 │ 0 │ NULL │ 0 │ 'Begin WHERE-core' │ NULL │
│ 10 │ 'VColumn' │ 0 │ 0 │ 5 │ NULL │ 0 │ 'r[5]=vcolumn(0)' │ NULL │
│ 11 │ 'ResultRow' │ 5 │ 1 │ 0 │ NULL │ 0 │ 'output=r[5]' │ NULL │
│ 12 │ 'Noop' │ 0 │ 0 │ 0 │ NULL │ 0 │ 'End WHERE-core' │ NULL │
│ 13 │ 'VNext' │ 0 │ 9 │ 0 │ NULL │ 0 │ NULL │ NULL │
│ 14 │ 'Noop' │ 0 │ 0 │ 0 │ NULL │ 0 │ 'End WHERE-loop0: wholenumber' │ NULL │
│ 15 │ 'Halt' │ 0 │ 0 │ 0 │ NULL │ 0 │ NULL │ NULL │
│ 16 │ 'Transaction' │ 0 │ 0 │ 0 │ '0' │ 1 │ 'usesStmtJournal=0' │ NULL │
│ 17 │ 'Goto' │ 0 │ 1 │ 0 │ NULL │ 0 │ NULL │ NULL │
└──────┴───────────────┴────┴────┴────┴───────────────────────────────────────────────────────┴────┴──────────────────────────────────┴─────────┘
(24.1) By Keith Medcalf (kmedcalf) on 2022-12-05 21:31:38 edited from 24.0 in reply to 21 [link] [source]
Yes, explain/explain query plan works in Python:
Python 3.11.0 (main, Oct 24 2022, 18:26:48) [MSC v.1933 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import mpsw
>>> db = mpsw.Connection()
>>> for row in db.execute('explain select value from wholenumber where value between 1 and 10'): print(row)
...
Row(addr=0, opcode='Init', p1=0, p2=16, p3=0, p4=None, p5=0, comment='Start at 16')
Row(addr=1, opcode='VOpen', p1=0, p2=0, p3=0, p4='vtab:27FC3840970', p5=0, comment=None)
Row(addr=2, opcode='Explain', p1=2, p2=0, p3=0, p4='SCAN wholenumber VIRTUAL TABLE INDEX 10: (~24 rows)', p5=0, comment=None)
Row(addr=3, opcode='Noop', p1=0, p2=0, p3=0, p4=None, p5=0, comment='Begin WHERE-loop0: wholenumber')
Row(addr=4, opcode='Integer', p1=1, p2=3, p3=0, p4=None, p5=0, comment='r[3]=1')
Row(addr=5, opcode='Integer', p1=10, p2=4, p3=0, p4=None, p5=0, comment='r[4]=10')
Row(addr=6, opcode='Integer', p1=10, p2=1, p3=0, p4=None, p5=0, comment='r[1]=10')
Row(addr=7, opcode='Integer', p1=2, p2=2, p3=0, p4=None, p5=0, comment='r[2]=2')
Row(addr=8, opcode='VFilter', p1=0, p2=14, p3=1, p4=None, p5=0, comment="iplan=r[1] zplan=''")
Row(addr=9, opcode='Noop', p1=0, p2=0, p3=0, p4=None, p5=0, comment='Begin WHERE-core')
Row(addr=10, opcode='VColumn', p1=0, p2=0, p3=5, p4=None, p5=0, comment='r[5]=vcolumn(0)')
Row(addr=11, opcode='ResultRow', p1=5, p2=1, p3=0, p4=None, p5=0, comment='output=r[5]')
Row(addr=12, opcode='Noop', p1=0, p2=0, p3=0, p4=None, p5=0, comment='End WHERE-core')
Row(addr=13, opcode='VNext', p1=0, p2=9, p3=0, p4=None, p5=0, comment=None)
Row(addr=14, opcode='Noop', p1=0, p2=0, p3=0, p4=None, p5=0, comment='End WHERE-loop0: wholenumber')
Row(addr=15, opcode='Halt', p1=0, p2=0, p3=0, p4=None, p5=0, comment=None)
Row(addr=16, opcode='Transaction', p1=0, p2=0, p3=0, p4='0', p5=1, comment='usesStmtJournal=0')
Row(addr=17, opcode='Goto', p1=0, p2=1, p3=0, p4=None, p5=0, comment=None)
>>>
>>> for row in db.execute('explain query plan select value from wholenumber where value between 1 and 10'): print(row)
...
Row(id=2, parent=0, notused=0, detail='SCAN wholenumber VIRTUAL TABLE INDEX 10: (~24 rows)')
>>>
(25.1) By Keith Medcalf (kmedcalf) on 2022-12-05 22:13:58 edited from 25.0 in reply to 18 [link] [source]
That is incorrect. The obverse of __lt__ is __ge__.
The obverse pairs (where one is the inverse of the other) would be:
__lt__ / __ge__
__gt__ / __le__
__eq__ / __ne__
It would probably be much simpler to just write the collation function to compute the "total ordering" directly. There is no advantage to be had by fiddling with classes. If you know that if the first character == 'q' then the remainder is a float, then why not just be doing that directly?
(26) By David Raymond (dvdraymond) on 2022-12-06 13:04:49 in reply to 25.1 [link] [source]
My terminology is bad, but what you gave are the negated function pairs. not (a < b) is a >= b not (a > b) is a <= b not (a = b) is a != b What he's creating is the equivalent with the order reversed a < b is b > a a > b is b < a a <= b is b >= a a = b is b = a a != b is b != a
(27) By David Raymond (dvdraymond) on 2022-12-06 13:50:09 in reply to 22 [link] [source]
#define SQLITE_ENABLE_COLUMN_METADATA 1 // Add Column Metadata Functions
#define SQLITE_ENABLE_COLUMN_USED_MASK 1 // Enable Column Used Hints
#define SQLITE_ENABLE_COSTMULT 1 // Use Cost Multiplication Factors
#define SQLITE_ENABLE_CURSOR_HINTS 1 // Output Cursor Hints to B-Tree Layer
#define SQLITE_ENABLE_EXPLAIN_COMMENTS 1 // Add Comments to Explain
#define SQLITE_ENABLE_MODULE_COMMENTS 1 // Add Module Comments to Explain
#define SQLITE_EXPLAIN_ESTIMATED_ROWS 1 // Add Estimated Rows to Explain
Interesting.
Only SQLITE_ENABLE_COLUMN_METADATA and SQLITE_ENABLE_EXPLAIN_COMMENTS are actually documented in the Compile-time Options page.
Does that mean the others are "undocumented" and shouldn't be relied on?
(28) By Larry Brasfield (larrybr) on 2022-12-06 16:20:05 in reply to 27 [source]
Does that mean the others are "undocumented" and shouldn't be relied on?
The features provided as debugging aids, particularly those which expose implementation details, are ever-evolving. I would be quite surprised to find them become part of the stable, documented SQLite API.
Yes, the undocumented features should not be relied upon except by those prepared to deal with future breakage or changes in meaning.