Formalize a parameterized variant to incorporate semi-structured data formats.

[RCHowell]

The PartiQL charter claims syntax independence, yet our specification encodes Ion into the syntax.

Charter Format Independence

PartiQL syntax and semantics are not tied to any particular data format.

PartiQL Specification Data Model Syntax

scalar_value     = "`", ? ion literal ?, "`"
                 | ? sql literal ? ;

This issue documents the recurring discussion around generalizing the backticks to be true to the charter while keeping Ion as the default.

<value>
    ::= "`" <utf8 body> "`" ["::" <encoding>] // if encoding is omitted, '::ion' is implicit
      | <sql literal>

<encoding>
    ::= <regular identifier>

[RCHowell]

I don't have any real preference (or knowledge) of what might be a good name for "encoding"

Some options

• format
• encoding
• model
• data-type
• content-type

[RCHowell]

Some notes,

• https://spark.apache.org/docs/4.0.0-preview1/api/java/org/apache/spark/sql/types/VariantType.html
• https://www.databricks.com/blog/introducing-open-variant-data-type-delta-lake-and-apache-spark
• https://github.com/apache/spark/blob/master/common/variant/README.md

Spark

A Variant represents a type that contain one of:

Primitive: A type and corresponding value (e.g. INT, STRING)
Array: An ordered list of Variant values
Object: An unordered collection of string/Variant pairs (i.e. key/value pairs). An object may not contain duplicate keys.

Snowflake

A VARIANT value can store a value of any other type, including OBJECT and ARRAY values.

[am357]

It looks like both Spark and Snowflake consider the VARIANT as a mean to represent semi-structured data.

From Snowflake documentation:

Semi-structured data types

The following Snowflake data types can contain other data types:

• VARIANT (can contain a value of any other data type).
• OBJECT (can directly contain a VARIANT value, and thus indirectly contain a value of any other data type, including itself).
• ARRAY (can directly contain a VARIANT value, and thus indirectly contain a value of any other data type, including itself).

We often refer to these data types as semi-structured data types. Strictly speaking, OBJECT is the only one of these data types that, by itself, has all of the characteristics of a true semi-structured data type. However, combining these data types allows you to explicitly represent arbitrary hierarchical data structures, which can be used to load and operate on data in semi-structured formats (such as JSON, Avro, ORC, Parquet, or XML).

I consider VARIANT as a good option for generalizing type of values inside back-tick in PartiQL. I think we just have to clarify that we consider all formats to be represented by this type including tabular (E.g., CSV).