SQLite Forum

Views, Casting vs and converters
(First post, sorry if I'm doing anything wrong)

Working on a converter **for a view** (explanation below) and my starting point was the Python SQLITE doc but I'm getting some behavior I don't understand. Also not quite sure where to look for answers between SQLITE and python.

Specifically, the behavior when accessing a column (`my_col INTEGER`) differs when using `CAST(my_col as SOME_TYPE)` from what I see when using `my_col as "my_col_some_type [SOME_TYPE]"` (assuming that there is a converter defined and registered for the appropriate type).

With a simple converter defined for `BOOLEAN`s, the results were:

* `my_col as "my_col float [FLOAT]"` evaluated as 1
* `CAST(my_col as FLOAT) as [my_col cast float]` evaluated as 1.0
* `my_col as "my_col bol [BOOLEAN]"` evaluated as True
* `CAST(my_col as BOOLEAN) as [my_col cast bol]` evaluated as 1
Is there a reason/recommendation I'm missing about why the `CAST()` approach seems to return the expected results with built-in sqlite types (I tried with `FLOAT`) but doesn't seem to work with types I've defined. Alternatively, any understanding on why the other approach `my_col as "my_col_some_type [SOME_TYPE]"` approach seems opposite, working on types I've defined but not with built-in?

Potentially related: from the relevant section in default adapters and converters — link:  https://docs.python.org/3/library/sqlite3.html#default-adapters-and-converters — uses both `detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES` but from what I saw doesn't clarify on why both are included. Could this value for `detect_types` impact behavior?

Notes on what I'm trying to do: using a view that joins the two tables below, I'm essentially trying to pivot from (using a silly example)


<tr><th>name</th><th> clothing_type_id </th></tr>

<tr><td>bob</td><td>shirt, pants </td></tr>

and I want to implement a converter to return the list of clothing items for each person as a list (rather than the string from `group_concat`).