Reputation: 25
I'm looking for a python assert-like pattern for a pure SQL statement in SQLite3. In python I could write code
assert len(my_var)==1, "Unexpected length, raise an error"
Is there a pattern I can use to achieve the same in an SQLite3 call that would be executed from the command-line in the form:
sqlite3 my_database.db < script.sql > output.tsv
The structure of the problem is this. Given a table with columns (col_A, col_B, col_C), column C is a hash of A and B, such that unique pairs of A and B are expected to produce a unique value for C, and duplicate pairs of A and B produce the same value for C. I'm interested in getting
SELECT A, B, C, COUNT(*) FROM mytable GROUP BY A, B;
However, there exists the slim possibility of my hashing algorithm producing a different value for identical A,B pairs (don't ask about the hashing algorithm, this is a frictionless metaphor operating in a vacuum). In the SELECT above, for SQLite, I believe the behavior would be to silently get the first value for C and discard the rest. I could use GROUP_CONCAT and save all the values, I don't want my hash value to possibly become a separated string. I could just GROUP BY A, B, C, but later steps depend on AB pairs being unique. Really, the behavior I'd like to see is that the query blows up and complains.
Is there a way of asserting that GROUP BY A, B didn't discard any values for C? Or that LENGTH(GROUP BY A, B) == LENGTH(GROUP BY A, B, C)
Upvotes: 1
Views: 370
Reputation: 19695
From your description, especially...
later steps depend on A, B pairs being unique
...one approach that comes close is to already enforce at this stage that the pairs must be unique:
CREATE TABLE mytable (
A,
B,
C,
UNIQUE (A, B)
);
That way, there are never duplicate pairs of (A, B) -- SQLite won't let you insert them -- and because there cannot be duplicates, there can never be a case where their C columns disagree.
Upvotes: 1