SQL: Casting to more types (e.g., VARCHAR)

[philrz]

tl;dr

$ super -c "SELECT cast(a AS VARCHAR) FROM 'test.csv';"
type "varchar" does not exist at line 1, column 18:
SELECT cast(a AS VARCHAR) FROM 'test.csv';
                 ~~~~~~~

Details

Repro is with super commit 850e978. This is based on a query from a sqllogictest.

With input file test.csv:

a,b
11,22
12,21
13,22

The following query with attempted cast to VARCHAR fails:

$ super -version
Version: 850e97898

$ super -c "SELECT cast(a AS VARCHAR) FROM 'test.csv';"
type "varchar" does not exist at line 1, column 18:
SELECT cast(a AS VARCHAR) FROM 'test.csv';
                 ~~~~~~~

However, it succeeds in other SQL-based systems, e.g.,

$ duckdb --version
v1.3.0 71c5c07cdd

$ duckdb -c "SELECT cast(a AS VARCHAR) FROM 'test.csv';"
┌────────────────────┐
│ CAST(a AS VARCHAR) │
│      varchar       │
├────────────────────┤
│ 11                 │
│ 12                 │
│ 13                 │
└────────────────────┘

There's a whole lot of SQL types that may show up in contexts like this, e.g., here's the Postgres list. While we could whack-a-mole the individual ones as we come across them in example queries, perhaps we could knock out many at once.

[philrz]

It was noted we could potentially work around this in the short term by running the query with a -I included set of named types, but it looks like these aren't yet supported in the SQL support in SuperSQL. Here's an example.

Given input data.csv:

a
1

I can do this successfully in pipe mode:

$ super -version
Version: 9fcbd222b

$ super -c "type VARCHAR=string from 'data.csv' (format csv) | values cast(a, VARCHAR)"
"1"::=VARCHAR

But no luck with this:

$ super -c "type VARCHAR=string SELECT CAST(a AS VARCHAR) FROM 'data.csv' (FORMAT csv);"
type "varchar" does not exist at line 1, column 38:
type VARCHAR=string SELECT CAST(a AS VARCHAR) FROM 'data.csv' (FORMAT csv);
                                     ~~~~~~~

SQL casting with the native type works fine of course.

$ super -c "SELECT CAST(a AS STRING) FROM 'data.csv' (FORMAT csv);"
{"CAST(a AS STRING)":"1"}

[philrz]

Verified in super commit d388d44.

The SQL example shown above with the cast to VARCHAR now succeeds.

$ super -version
Version: d388d4472

$ super -c "SELECT cast(a AS VARCHAR) FROM 'test.csv';"
{"CAST(a AS string)":"11"}
{"CAST(a AS string)":"12"}
{"CAST(a AS string)":"13"}

The linked PR #6346 includes a ztest that shows the successful casting to many other types that are supported in Postgres.

Thanks @nwt!