CODEWITHSUNDEEP
sqlsqlite
A Holman
A Holman

Reputation: 25

ASSERT like pattern in SQLite statement

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

Answers (1)

Wander Nauta
Wander Nauta

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

Related Questions