srielau commented on code in PR #58224: URL: https://github.com/apache/spark/pull/58224#discussion_r4330581414
########## docs/sql-ref-character-string-types.md: ########## @@ -0,0 +1,375 @@ +--- +layout: global +title: Character String Types +displayTitle: Character String Types (Preview) +license: | + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +--- + +First-class `CHAR` / `VARCHAR` / `STRING` semantics are a preview feature in Spark 4.4 and may +change in future releases. They are off by default. This page describes that preview mode only. + +Enable the preview feature before using the behavior below: + +```sql +SET spark.sql.charVarchar.standardSemantics.enabled = true; +``` + +By default, `CHAR` and `VARCHAR` are table-schema overlays on `STRING`. DataFrame schemas +show `STRING`, `CAST(... AS CHAR(n))` is `STRING`, and function schemas raise +`UNSUPPORTED_CHAR_OR_VARCHAR_AS_STRING`. See [Data Types](sql-ref-datatypes.html). + +Spark SQL then has a family of three character string types: + +```text +CHAR(n) -> VARCHAR(n) -> STRING +``` + +The arrow denotes type precedence from the most constrained type to the least constrained type. +This is analogous to the precedence of integral numeric types such as +`TINYINT -> SMALLINT -> INT -> BIGINT`. `CHAR`, `VARCHAR`, and `STRING` are distinct SQL types in +the same family; `CHAR` and `VARCHAR` are not special uses of `STRING`. + +## Related settings + +The preview flag is persisted with view definitions, so a view continues to use the semantics +under which it was created. + +The older `spark.sql.preserveCharVarcharTypeInfo` setting is an experimental compatibility mode. +It preserves type information, but does not apply all the type precedence and expression-result +rules described here. Standard semantics take precedence over +`spark.sql.legacy.charVarcharAsString` when both settings are `true`. + +Two other settings still apply when standard semantics are on: + +* `spark.sql.charAsVarchar` still rewrites `CHAR` to `VARCHAR` in `CREATE`/`REPLACE`/`ALTER TABLE`. + `CAST(... AS CHAR(n))` stays `CHAR(n)`. +* `spark.sql.readSideCharPadding` is ignored. Read-side CHAR padding and length checks always run; + setting it to `false` logs a warning. + +## Type definitions + +| SQL type | Value constraint | Behavior | +|----------|------------------|----------| +| `CHAR(n)` | Exactly `n` characters | Values shorter than `n` characters are padded on the right with spaces. | +| `VARCHAR(n)` | At most `n` characters | Values retain their length and are not padded. | +| `STRING` | Unbounded length | Values have no declared length constraint. | + +The length is required for `CHAR` and `VARCHAR`. `CHARACTER(n)` is a synonym for `CHAR(n)`. +The length `n` counts characters, not UTF-8 bytes. All three types can have a +[collation](sql-ref-syntax-aux-show-collations.html), which controls comparison, ordering, and +other collation-sensitive operations. + +For example: + +```sql +SELECT typeof(CAST('ab' AS CHAR(5))); +-- char(5) + +SELECT concat('<', CAST('ab' AS CHAR(5)), '>'); +-- <ab > + +SELECT typeof(CAST('ab' AS VARCHAR(5))); +-- varchar(5) +``` + +## Type precedence and least common type + +Spark finds the least common type when an operation needs one type that can represent multiple +inputs. Within the character string family, Spark uses these rules: + +| Input types | Least common type | +|-------------|-------------------| +| `CHAR(n)`, `CHAR(m)` | `CHAR(max(n, m))` | +| `CHAR(n)`, `VARCHAR(m)` | `VARCHAR(max(n, m))` | +| `VARCHAR(n)`, `VARCHAR(m)` | `VARCHAR(max(n, m))` | +| Any character string type and `STRING` | `STRING` | +| Any character string type `T` and untyped `NULL` | `T` | + +These rules apply recursively to arrays, maps, and structs. They are used by expressions and +operations such as `CASE`, `COALESCE`, `IN`, `UNION`, `INTERSECT`, `EXCEPT`, multi-row `VALUES`, +and collection constructors. + +```sql +SELECT typeof(coalesce( + CAST('a' AS CHAR(2)), + CAST('bb' AS CHAR(4)))); +-- char(4) + +SELECT typeof(coalesce( + CAST('a' AS CHAR(2)), + CAST('bb' AS VARCHAR(4)))); +-- varchar(4) + +SELECT typeof(coalesce( + CAST('a' AS VARCHAR(4)), + CAST('unbounded' AS STRING))); +-- string +``` + +Collation is also part of a character string type. The least-common-type rules above apply after +Spark resolves the input collations. When the result remains `CHAR` or `VARCHAR`, it retains the +resolved collation. + +## Casts + +An explicit `CAST` or `TRY_CAST` can introduce a `CHAR` or `VARCHAR` value: + +```sql +SELECT CAST('abc' AS CHAR(5)); +-- 'abc ' + +SELECT CAST('abc' AS VARCHAR(5)); +-- 'abc' +``` + +For an explicit cast from one character string type to another: + +* Casting to `CHAR(n)` truncates values longer than `n` characters and pads values shorter than + `n` characters. +* Casting to `VARCHAR(n)` truncates values longer than `n` characters. Spaces already present in + a `CHAR` value are kept, so `CAST(CAST('ab' AS CHAR(4)) AS VARCHAR(4))` is `'ab '`. + +For a cast from a non-character value, Spark first converts the value to characters and then +checks the target length. `CAST` raises `EXCEED_LIMIT_LENGTH` if the value does not fit, while +`TRY_CAST` returns `NULL`. + +```sql +SELECT CAST('abcdef' AS VARCHAR(3)); +-- 'abc' + +SELECT TRY_CAST(12345 AS VARCHAR(4)); +-- NULL +``` + +## Assignment and reading + +Assignment to a declared `CHAR` or `VARCHAR` type is stricter than an explicit +character-to-character cast. Assignment occurs for operations such as table writes, routine +arguments and return values, session variables, schema-driven Dataset or DataFrame conversion, and +the output columns of a script `TRANSFORM` clause. + +* A value that fits is stored unchanged, except that `CHAR(n)` is padded on the right to `n` + characters. +* If only trailing spaces exceed the declared length, Spark removes only the number of trailing + spaces needed to fit. +* If non-space characters exceed the declared length, Spark raises `EXCEED_LIMIT_LENGTH`. + +Reads apply the same checks. In particular, a `CHAR(n)` value read from external or otherwise +untrusted storage is padded if short and rejected if its non-space content is too long. The same +`EXCEED_LIMIT_LENGTH` check applies to oversize partition values. + +```sql +CREATE TABLE contacts (code CHAR(4), name VARCHAR(10)) USING parquet; + +INSERT INTO contacts VALUES ('ab', 'Ada'); + +SELECT concat('<', code, '>'), name FROM contacts; +-- <ab > Ada +``` + +A script `TRANSFORM` clause (including `MAP` and `REDUCE`) exchanges rows with the external script +as unbounded `STRING`, so a `CHAR(n)` or `VARCHAR(n)` in its `AS` output list does not constrain +what the script emits. Spark instead applies the assignment above to the script output in a +projection: a declared `CHAR(n)` is padded and an over-length value raises `EXCEED_LIMIT_LENGTH`. +The declared type is kept only when these semantics (or `spark.sql.preserveCharVarcharTypeInfo`) +are enabled; otherwise an `AS` type such as `CHAR(n)` is treated as plain `STRING` with no padding +or length check. + +## Expression result types + +A reference to a `CHAR` or `VARCHAR` column preserves its declared type. Expressions that select +among values without changing their character content, such as `CASE` and `COALESCE`, use the +least common type rules above. + +An expression that transforms character content returns `STRING`, because its result length is +not constrained by an input declaration. This includes string functions and operators such as +`upper`, `lower`, `substring`, `concat`, `||`, `regexp_replace`, `trim`, `split`, and `mask`. + +```sql +SELECT typeof(upper(CAST('ab' AS CHAR(2)))); +-- string + +SELECT typeof(CAST('a' AS CHAR(1)) || CAST('b' AS VARCHAR(1))); +-- string +``` + +This rule prevents a length constraint from propagating to newly computed content. To constrain +the result, cast it explicitly to `CHAR(n)` or `VARCHAR(n)`. + +## Comparisons and trailing spaces + +Spark coerces character string operands to a least common type before comparing them. Casting to a +wider `CHAR` pads the value, while casting to `VARCHAR` or `STRING` preserves spaces already +present in a `CHAR` value. + +```sql +SELECT CAST('a' AS CHAR(2)) = CAST('a' AS CHAR(4)); +-- true + +SELECT CAST('a' AS CHAR(2)) = CAST('a' AS VARCHAR(2)); +-- false + +SELECT CAST('a' AS CHAR(2)) = CAST('a ' AS VARCHAR(2)); +-- true +``` + +A [string literal](sql-ref-literals.html#string-literal) is `STRING`, not `CHAR` or `VARCHAR`. +Comparing it with `CHAR` is the same widening: the `CHAR` pad is kept, and the comparison is +exact. + +```sql +SELECT CAST('a' AS CHAR(2)) = 'a'; +-- false + +SELECT CAST('a' AS CHAR(2)) = 'a '; +-- true +``` + +A [parameter marker](sql-ref-parameter-markers.html) bound to a string value is also `STRING`. +The bound value is not padded unless you cast it to `CHAR`. + +```sql +EXECUTE IMMEDIATE 'SELECT CAST(''a'' AS CHAR(2)) = :p' USING 'a' AS p; +-- false + +EXECUTE IMMEDIATE 'SELECT CAST(''a'' AS CHAR(2)) = :p' USING 'a ' AS p; +-- true + +EXECUTE IMMEDIATE + 'SELECT CAST(''a'' AS CHAR(2)) = CAST(:p AS CHAR(2))' USING 'a' AS p; +-- true +``` + +Ignoring trailing spaces is a collation property, not an implicit property of every `CHAR` +comparison. Use an `RTRIM` collation when comparisons should ignore trailing spaces: + +```sql +SELECT CAST('a' AS CHAR(2) COLLATE UTF8_BINARY_RTRIM) = + 'a' COLLATE UTF8_BINARY_RTRIM; +-- true +``` + +## Parsing functions and map keys + +The functions `from_json`, `from_csv`, `from_xml`, and `from_avro` have two CHAR/VARCHAR surfaces: +the document they parse, and the schema they parse into. File scans use the same schema rules as +the corresponding `from_*` function except where noted. + +### Document arguments + +`from_json`, `from_csv`, `from_xml`, and `schema_of_json` / `schema_of_csv` / `schema_of_xml` take a +STRING document. A `CHAR` argument is not rtrimmed. Padding is part of the value after CAST to +STRING, so it is parsed as trailing spaces in the document: + +```sql +SELECT from_json(CAST('{"a":1}' AS CHAR(12)), 'a INT'); +-- same as from_json('{"a":1} ', 'a INT') +``` + +JSON and XML ignore that trailing whitespace. CSV can tokenize it: with a space delimiter, padding +becomes extra empty fields; with the default comma delimiter, padding lands on the last unquoted +field. When CHAR is first-class, `schema_of_csv(CAST('1' AS CHAR(3)))` infers +`STRUCT<_c0: DOUBLE>` because the padded last field is not a clean integer. Under default +settings that CAST is STRING and the inferred field is INT. +`map('ignoreTrailingWhiteSpace', 'true')` restores a trimmed last-field parse. + +`from_avro` takes binary Avro, not a character document, so CHAR padding of a SQL string does not +apply there. + +### Parsed values + +With a reader schema that declares `CHAR`/`VARCHAR` fields, parsed string values are assigned +when CHAR/VARCHAR are first-class (this preview flag or +`spark.sql.preserveCharVarcharTypeInfo`): `CHAR(n)` is padded, and an over-length value is a +bad record. For `from_json`, `from_csv`, and `from_xml`, `FAILFAST` raises +`EXCEED_LIMIT_LENGTH` and `PERMISSIVE` nulls the failed field. CSV field overflow follows the +CSV parse mode the same way. + +`from_avro` assigns STRING values only. Avro ENUM symbols are not padded or length-checked, so a +padded CHAR cannot invent a symbol. Overflow is a malformed record handled by the `mode` +option: `FAILFAST` fails with `EXCEED_LIMIT_LENGTH` as the cause; `PERMISSIVE` returns a null +record. Avro file scans leave CHAR padding of STRING values to read-side CHAR padding rather +than assigning in the deserializer. `to_json`, `to_csv`, `to_xml`, and `to_avro` serialize the +current string content; `to_avro` does not itself pad or trim. + +### Map keys + +A `CHAR(n)` or `VARCHAR(n)` map key is length-checked but never padded or trimmed: a `CHAR(n)` key +must already be exactly `n` characters, and a `VARCHAR(n)` key at most `n`. Padding or trimming a +key would change its identity and could silently merge distinct keys. + +* JSON (`from_json` and JSON scans): `UNSUPPORTED_JSON_CHAR_VARCHAR_MAP_KEY` (SQLSTATE `0A000`). + `from_json` captures the standard-semantics flag at analysis, so a view keeps the check from + when it was created. JSON file scans, including scans inside persisted views, read the flag + from the executing session. Duplicate names are kept, exactly as for STRING keys; + `spark.sql.mapKeyDedupPolicy` is not applied at parse time. Keys rebuilt read-side on a file + scan can still raise `DUPLICATED_MAP_KEY`. `from_json(to_json(m))` is not folded away when `m` + has a CHAR/VARCHAR map key. +* XML (`from_xml` and XML scans): `UNSUPPORTED_XML_CHAR_VARCHAR_MAP_KEY`. Checked names are + element names, `attributePrefix` plus attribute names (default prefix `_`), and the `valueTag` + (default `_VALUE`) when mixed text is present. Exact repeated names last-win. + `spark.sql.mapKeyDedupPolicy` is not applied. Empty, attribute-only, and text-only map elements + stay SQL NULL or a malformed record; they do not become an empty map or a `valueTag` entry. + When standard semantics are off and `spark.sql.preserveCharVarcharTypeInfo` is on, short CHAR + keys are padded and over-length keys raise `EXCEED_LIMIT_LENGTH`. +* Avro (`from_avro` and Avro scans): `UNSUPPORTED_AVRO_CHAR_VARCHAR_MAP_KEY`. `from_avro` + PERMISSIVE nulls that malformed record; Avro file scans do not wrap per-row PERMISSIVE around + the error. +* CSV has no object-name map keys; `from_csv` schemas are structs of fields. + +`PERMISSIVE` sets the nearest enclosing field to `null`; `FAILFAST` fails with the error above as +the cause of `MALFORMED_RECORD_IN_PARSING`. `variant_get` and `to_variant_object` reject +CHAR/VARCHAR map keys at analysis. Review Comment: Thanks. variant_get, to_variant_object, and CAST(... AS VARIANT) now reject CHAR/VARCHAR anywhere in the type, not only as map keys. See dee7bd4d8a1. ########## docs/sql-ref-character-string-types.md: ########## @@ -0,0 +1,375 @@ +--- +layout: global +title: Character String Types +displayTitle: Character String Types (Preview) +license: | + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +--- + +First-class `CHAR` / `VARCHAR` / `STRING` semantics are a preview feature in Spark 4.4 and may +change in future releases. They are off by default. This page describes that preview mode only. + +Enable the preview feature before using the behavior below: + +```sql +SET spark.sql.charVarchar.standardSemantics.enabled = true; +``` + +By default, `CHAR` and `VARCHAR` are table-schema overlays on `STRING`. DataFrame schemas +show `STRING`, `CAST(... AS CHAR(n))` is `STRING`, and function schemas raise +`UNSUPPORTED_CHAR_OR_VARCHAR_AS_STRING`. See [Data Types](sql-ref-datatypes.html). + +Spark SQL then has a family of three character string types: + +```text +CHAR(n) -> VARCHAR(n) -> STRING +``` + +The arrow denotes type precedence from the most constrained type to the least constrained type. +This is analogous to the precedence of integral numeric types such as +`TINYINT -> SMALLINT -> INT -> BIGINT`. `CHAR`, `VARCHAR`, and `STRING` are distinct SQL types in +the same family; `CHAR` and `VARCHAR` are not special uses of `STRING`. + +## Related settings + +The preview flag is persisted with view definitions, so a view continues to use the semantics +under which it was created. + +The older `spark.sql.preserveCharVarcharTypeInfo` setting is an experimental compatibility mode. +It preserves type information, but does not apply all the type precedence and expression-result +rules described here. Standard semantics take precedence over +`spark.sql.legacy.charVarcharAsString` when both settings are `true`. + +Two other settings still apply when standard semantics are on: + +* `spark.sql.charAsVarchar` still rewrites `CHAR` to `VARCHAR` in `CREATE`/`REPLACE`/`ALTER TABLE`. + `CAST(... AS CHAR(n))` stays `CHAR(n)`. +* `spark.sql.readSideCharPadding` is ignored. Read-side CHAR padding and length checks always run; + setting it to `false` logs a warning. + +## Type definitions + +| SQL type | Value constraint | Behavior | +|----------|------------------|----------| +| `CHAR(n)` | Exactly `n` characters | Values shorter than `n` characters are padded on the right with spaces. | +| `VARCHAR(n)` | At most `n` characters | Values retain their length and are not padded. | +| `STRING` | Unbounded length | Values have no declared length constraint. | + +The length is required for `CHAR` and `VARCHAR`. `CHARACTER(n)` is a synonym for `CHAR(n)`. +The length `n` counts characters, not UTF-8 bytes. All three types can have a +[collation](sql-ref-syntax-aux-show-collations.html), which controls comparison, ordering, and +other collation-sensitive operations. + +For example: + +```sql +SELECT typeof(CAST('ab' AS CHAR(5))); +-- char(5) + +SELECT concat('<', CAST('ab' AS CHAR(5)), '>'); +-- <ab > + +SELECT typeof(CAST('ab' AS VARCHAR(5))); +-- varchar(5) +``` + +## Type precedence and least common type + +Spark finds the least common type when an operation needs one type that can represent multiple +inputs. Within the character string family, Spark uses these rules: + +| Input types | Least common type | +|-------------|-------------------| +| `CHAR(n)`, `CHAR(m)` | `CHAR(max(n, m))` | +| `CHAR(n)`, `VARCHAR(m)` | `VARCHAR(max(n, m))` | +| `VARCHAR(n)`, `VARCHAR(m)` | `VARCHAR(max(n, m))` | +| Any character string type and `STRING` | `STRING` | +| Any character string type `T` and untyped `NULL` | `T` | + +These rules apply recursively to arrays, maps, and structs. They are used by expressions and +operations such as `CASE`, `COALESCE`, `IN`, `UNION`, `INTERSECT`, `EXCEPT`, multi-row `VALUES`, +and collection constructors. + +```sql +SELECT typeof(coalesce( + CAST('a' AS CHAR(2)), + CAST('bb' AS CHAR(4)))); +-- char(4) + +SELECT typeof(coalesce( + CAST('a' AS CHAR(2)), + CAST('bb' AS VARCHAR(4)))); +-- varchar(4) + +SELECT typeof(coalesce( + CAST('a' AS VARCHAR(4)), + CAST('unbounded' AS STRING))); Review Comment: Thanks. Updated the example to string collate UTF8_BINARY. Returning the STRING singleton for the default collation looks worth a separate JIRA rather than mixing it into this docs PR. See dee7bd4d8a1. ########## docs/sql-ref-character-string-types.md: ########## @@ -0,0 +1,375 @@ +--- +layout: global +title: Character String Types +displayTitle: Character String Types (Preview) +license: | + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +--- + +First-class `CHAR` / `VARCHAR` / `STRING` semantics are a preview feature in Spark 4.4 and may +change in future releases. They are off by default. This page describes that preview mode only. + +Enable the preview feature before using the behavior below: + +```sql +SET spark.sql.charVarchar.standardSemantics.enabled = true; +``` + +By default, `CHAR` and `VARCHAR` are table-schema overlays on `STRING`. DataFrame schemas +show `STRING`, `CAST(... AS CHAR(n))` is `STRING`, and function schemas raise +`UNSUPPORTED_CHAR_OR_VARCHAR_AS_STRING`. See [Data Types](sql-ref-datatypes.html). + +Spark SQL then has a family of three character string types: + +```text +CHAR(n) -> VARCHAR(n) -> STRING +``` + +The arrow denotes type precedence from the most constrained type to the least constrained type. +This is analogous to the precedence of integral numeric types such as +`TINYINT -> SMALLINT -> INT -> BIGINT`. `CHAR`, `VARCHAR`, and `STRING` are distinct SQL types in +the same family; `CHAR` and `VARCHAR` are not special uses of `STRING`. + +## Related settings + +The preview flag is persisted with view definitions, so a view continues to use the semantics +under which it was created. + +The older `spark.sql.preserveCharVarcharTypeInfo` setting is an experimental compatibility mode. +It preserves type information, but does not apply all the type precedence and expression-result +rules described here. Standard semantics take precedence over +`spark.sql.legacy.charVarcharAsString` when both settings are `true`. + +Two other settings still apply when standard semantics are on: + +* `spark.sql.charAsVarchar` still rewrites `CHAR` to `VARCHAR` in `CREATE`/`REPLACE`/`ALTER TABLE`. + `CAST(... AS CHAR(n))` stays `CHAR(n)`. +* `spark.sql.readSideCharPadding` is ignored. Read-side CHAR padding and length checks always run; + setting it to `false` logs a warning. + +## Type definitions + +| SQL type | Value constraint | Behavior | +|----------|------------------|----------| +| `CHAR(n)` | Exactly `n` characters | Values shorter than `n` characters are padded on the right with spaces. | +| `VARCHAR(n)` | At most `n` characters | Values retain their length and are not padded. | +| `STRING` | Unbounded length | Values have no declared length constraint. | + +The length is required for `CHAR` and `VARCHAR`. `CHARACTER(n)` is a synonym for `CHAR(n)`. +The length `n` counts characters, not UTF-8 bytes. All three types can have a +[collation](sql-ref-syntax-aux-show-collations.html), which controls comparison, ordering, and +other collation-sensitive operations. + +For example: + +```sql +SELECT typeof(CAST('ab' AS CHAR(5))); +-- char(5) + +SELECT concat('<', CAST('ab' AS CHAR(5)), '>'); +-- <ab > + +SELECT typeof(CAST('ab' AS VARCHAR(5))); +-- varchar(5) +``` + +## Type precedence and least common type + +Spark finds the least common type when an operation needs one type that can represent multiple +inputs. Within the character string family, Spark uses these rules: + +| Input types | Least common type | +|-------------|-------------------| +| `CHAR(n)`, `CHAR(m)` | `CHAR(max(n, m))` | +| `CHAR(n)`, `VARCHAR(m)` | `VARCHAR(max(n, m))` | +| `VARCHAR(n)`, `VARCHAR(m)` | `VARCHAR(max(n, m))` | +| Any character string type and `STRING` | `STRING` | +| Any character string type `T` and untyped `NULL` | `T` | + +These rules apply recursively to arrays, maps, and structs. They are used by expressions and +operations such as `CASE`, `COALESCE`, `IN`, `UNION`, `INTERSECT`, `EXCEPT`, multi-row `VALUES`, +and collection constructors. + +```sql +SELECT typeof(coalesce( + CAST('a' AS CHAR(2)), + CAST('bb' AS CHAR(4)))); +-- char(4) + +SELECT typeof(coalesce( + CAST('a' AS CHAR(2)), + CAST('bb' AS VARCHAR(4)))); +-- varchar(4) + +SELECT typeof(coalesce( + CAST('a' AS VARCHAR(4)), + CAST('unbounded' AS STRING))); +-- string +``` + +Collation is also part of a character string type. The least-common-type rules above apply after +Spark resolves the input collations. When the result remains `CHAR` or `VARCHAR`, it retains the +resolved collation. + +## Casts + +An explicit `CAST` or `TRY_CAST` can introduce a `CHAR` or `VARCHAR` value: + +```sql +SELECT CAST('abc' AS CHAR(5)); +-- 'abc ' + +SELECT CAST('abc' AS VARCHAR(5)); +-- 'abc' +``` + +For an explicit cast from one character string type to another: + +* Casting to `CHAR(n)` truncates values longer than `n` characters and pads values shorter than + `n` characters. +* Casting to `VARCHAR(n)` truncates values longer than `n` characters. Spaces already present in + a `CHAR` value are kept, so `CAST(CAST('ab' AS CHAR(4)) AS VARCHAR(4))` is `'ab '`. + +For a cast from a non-character value, Spark first converts the value to characters and then +checks the target length. `CAST` raises `EXCEED_LIMIT_LENGTH` if the value does not fit, while +`TRY_CAST` returns `NULL`. + +```sql +SELECT CAST('abcdef' AS VARCHAR(3)); +-- 'abc' + +SELECT TRY_CAST(12345 AS VARCHAR(4)); +-- NULL +``` + +## Assignment and reading + +Assignment to a declared `CHAR` or `VARCHAR` type is stricter than an explicit +character-to-character cast. Assignment occurs for operations such as table writes, routine +arguments and return values, session variables, schema-driven Dataset or DataFrame conversion, and +the output columns of a script `TRANSFORM` clause. + +* A value that fits is stored unchanged, except that `CHAR(n)` is padded on the right to `n` + characters. +* If only trailing spaces exceed the declared length, Spark removes only the number of trailing + spaces needed to fit. +* If non-space characters exceed the declared length, Spark raises `EXCEED_LIMIT_LENGTH`. + +Reads apply the same checks. In particular, a `CHAR(n)` value read from external or otherwise +untrusted storage is padded if short and rejected if its non-space content is too long. The same +`EXCEED_LIMIT_LENGTH` check applies to oversize partition values. + +```sql +CREATE TABLE contacts (code CHAR(4), name VARCHAR(10)) USING parquet; + +INSERT INTO contacts VALUES ('ab', 'Ada'); + +SELECT concat('<', code, '>'), name FROM contacts; +-- <ab > Ada +``` + +A script `TRANSFORM` clause (including `MAP` and `REDUCE`) exchanges rows with the external script +as unbounded `STRING`, so a `CHAR(n)` or `VARCHAR(n)` in its `AS` output list does not constrain +what the script emits. Spark instead applies the assignment above to the script output in a +projection: a declared `CHAR(n)` is padded and an over-length value raises `EXCEED_LIMIT_LENGTH`. +The declared type is kept only when these semantics (or `spark.sql.preserveCharVarcharTypeInfo`) +are enabled; otherwise an `AS` type such as `CHAR(n)` is treated as plain `STRING` with no padding +or length check. + +## Expression result types + +A reference to a `CHAR` or `VARCHAR` column preserves its declared type. Expressions that select +among values without changing their character content, such as `CASE` and `COALESCE`, use the +least common type rules above. + +An expression that transforms character content returns `STRING`, because its result length is +not constrained by an input declaration. This includes string functions and operators such as +`upper`, `lower`, `substring`, `concat`, `||`, `regexp_replace`, `trim`, `split`, and `mask`. + +```sql +SELECT typeof(upper(CAST('ab' AS CHAR(2)))); +-- string + +SELECT typeof(CAST('a' AS CHAR(1)) || CAST('b' AS VARCHAR(1))); +-- string +``` + +This rule prevents a length constraint from propagating to newly computed content. To constrain +the result, cast it explicitly to `CHAR(n)` or `VARCHAR(n)`. + +## Comparisons and trailing spaces + +Spark coerces character string operands to a least common type before comparing them. Casting to a +wider `CHAR` pads the value, while casting to `VARCHAR` or `STRING` preserves spaces already +present in a `CHAR` value. + +```sql +SELECT CAST('a' AS CHAR(2)) = CAST('a' AS CHAR(4)); +-- true + +SELECT CAST('a' AS CHAR(2)) = CAST('a' AS VARCHAR(2)); +-- false + +SELECT CAST('a' AS CHAR(2)) = CAST('a ' AS VARCHAR(2)); +-- true +``` + +A [string literal](sql-ref-literals.html#string-literal) is `STRING`, not `CHAR` or `VARCHAR`. +Comparing it with `CHAR` is the same widening: the `CHAR` pad is kept, and the comparison is +exact. + +```sql +SELECT CAST('a' AS CHAR(2)) = 'a'; +-- false + +SELECT CAST('a' AS CHAR(2)) = 'a '; +-- true +``` + +A [parameter marker](sql-ref-parameter-markers.html) bound to a string value is also `STRING`. +The bound value is not padded unless you cast it to `CHAR`. + +```sql +EXECUTE IMMEDIATE 'SELECT CAST(''a'' AS CHAR(2)) = :p' USING 'a' AS p; +-- false + +EXECUTE IMMEDIATE 'SELECT CAST(''a'' AS CHAR(2)) = :p' USING 'a ' AS p; +-- true + +EXECUTE IMMEDIATE + 'SELECT CAST(''a'' AS CHAR(2)) = CAST(:p AS CHAR(2))' USING 'a' AS p; +-- true +``` + +Ignoring trailing spaces is a collation property, not an implicit property of every `CHAR` +comparison. Use an `RTRIM` collation when comparisons should ignore trailing spaces: + +```sql +SELECT CAST('a' AS CHAR(2) COLLATE UTF8_BINARY_RTRIM) = + 'a' COLLATE UTF8_BINARY_RTRIM; +-- true +``` + +## Parsing functions and map keys + +The functions `from_json`, `from_csv`, `from_xml`, and `from_avro` have two CHAR/VARCHAR surfaces: +the document they parse, and the schema they parse into. File scans use the same schema rules as +the corresponding `from_*` function except where noted. + +### Document arguments + +`from_json`, `from_csv`, `from_xml`, and `schema_of_json` / `schema_of_csv` / `schema_of_xml` take a +STRING document. A `CHAR` argument is not rtrimmed. Padding is part of the value after CAST to +STRING, so it is parsed as trailing spaces in the document: + +```sql +SELECT from_json(CAST('{"a":1}' AS CHAR(12)), 'a INT'); +-- same as from_json('{"a":1} ', 'a INT') +``` + +JSON and XML ignore that trailing whitespace. CSV can tokenize it: with a space delimiter, padding +becomes extra empty fields; with the default comma delimiter, padding lands on the last unquoted +field. When CHAR is first-class, `schema_of_csv(CAST('1' AS CHAR(3)))` infers +`STRUCT<_c0: DOUBLE>` because the padded last field is not a clean integer. Under default +settings that CAST is STRING and the inferred field is INT. +`map('ignoreTrailingWhiteSpace', 'true')` restores a trimmed last-field parse. + +`from_avro` takes binary Avro, not a character document, so CHAR padding of a SQL string does not +apply there. + +### Parsed values + +With a reader schema that declares `CHAR`/`VARCHAR` fields, parsed string values are assigned +when CHAR/VARCHAR are first-class (this preview flag or +`spark.sql.preserveCharVarcharTypeInfo`): `CHAR(n)` is padded, and an over-length value is a +bad record. For `from_json`, `from_csv`, and `from_xml`, `FAILFAST` raises +`EXCEED_LIMIT_LENGTH` and `PERMISSIVE` nulls the failed field. CSV field overflow follows the +CSV parse mode the same way. + +`from_avro` assigns STRING values only. Avro ENUM symbols are not padded or length-checked, so a +padded CHAR cannot invent a symbol. Overflow is a malformed record handled by the `mode` +option: `FAILFAST` fails with `EXCEED_LIMIT_LENGTH` as the cause; `PERMISSIVE` returns a null +record. Avro file scans leave CHAR padding of STRING values to read-side CHAR padding rather +than assigning in the deserializer. `to_json`, `to_csv`, `to_xml`, and `to_avro` serialize the +current string content; `to_avro` does not itself pad or trim. + +### Map keys + +A `CHAR(n)` or `VARCHAR(n)` map key is length-checked but never padded or trimmed: a `CHAR(n)` key +must already be exactly `n` characters, and a `VARCHAR(n)` key at most `n`. Padding or trimming a +key would change its identity and could silently merge distinct keys. + +* JSON (`from_json` and JSON scans): `UNSUPPORTED_JSON_CHAR_VARCHAR_MAP_KEY` (SQLSTATE `0A000`). + `from_json` captures the standard-semantics flag at analysis, so a view keeps the check from + when it was created. JSON file scans, including scans inside persisted views, read the flag + from the executing session. Duplicate names are kept, exactly as for STRING keys; + `spark.sql.mapKeyDedupPolicy` is not applied at parse time. Keys rebuilt read-side on a file + scan can still raise `DUPLICATED_MAP_KEY`. `from_json(to_json(m))` is not folded away when `m` + has a CHAR/VARCHAR map key. +* XML (`from_xml` and XML scans): `UNSUPPORTED_XML_CHAR_VARCHAR_MAP_KEY`. Checked names are + element names, `attributePrefix` plus attribute names (default prefix `_`), and the `valueTag` + (default `_VALUE`) when mixed text is present. Exact repeated names last-win. + `spark.sql.mapKeyDedupPolicy` is not applied. Empty, attribute-only, and text-only map elements + stay SQL NULL or a malformed record; they do not become an empty map or a `valueTag` entry. + When standard semantics are off and `spark.sql.preserveCharVarcharTypeInfo` is on, short CHAR + keys are padded and over-length keys raise `EXCEED_LIMIT_LENGTH`. +* Avro (`from_avro` and Avro scans): `UNSUPPORTED_AVRO_CHAR_VARCHAR_MAP_KEY`. `from_avro` + PERMISSIVE nulls that malformed record; Avro file scans do not wrap per-row PERMISSIVE around + the error. +* CSV has no object-name map keys; `from_csv` schemas are structs of fields. + +`PERMISSIVE` sets the nearest enclosing field to `null`; `FAILFAST` fails with the error above as +the cause of `MALFORMED_RECORD_IN_PARSING`. `variant_get` and `to_variant_object` reject +CHAR/VARCHAR map keys at analysis. + +## Schema propagation + +With standard semantics enabled, declared character string types remain visible through Spark SQL +and DataFrame schemas. This includes: + +* table, view, and common table expression columns; +* `CREATE TABLE AS SELECT` results; +* SQL routine parameters and return types; +* session and scripting variables; +* nested array, map, and struct fields; and +* schemas supplied to Dataset and DataFrame APIs. + +Storage formats and catalogs differ in how they persist logical type metadata. Whenever a schema +declares `CHAR(n)` or `VARCHAR(n)`, Spark applies the length, padding, assignment, and read checks +described on this page. + +### Data source behavior + +Catalog tables retain `CHAR` and `VARCHAR` declarations in their catalog schema. For file-only +schema inference, support depends on the format: + +| Data source | Behavior | +|-------------|----------| +| Parquet catalog table | Uses the catalog schema to retain the declared type. | +| ORC | Stores logical type metadata on physical string fields so schema inference can restore `CHAR` and `VARCHAR`. | +| Avro | Stores logical type metadata on string fields and map keys so schema inference can restore `CHAR` and `VARCHAR`. | +| JSON, CSV, and XML | Apply `CHAR` and `VARCHAR` semantics when the reader is given an explicit schema; text inference alone does not infer a length constraint. See [Parsing functions and map keys](#parsing-functions-and-map-keys) for map-key handling. CSV overflow is a bad record under the CSV parse mode. | + +When standard semantics are disabled, file-only inference reads the constrained fields as Review Comment: Thanks. Parquet is now documented like ORC: file-only schema inference can restore CHAR/VARCHAR from logical type metadata. The overlay-as-STRING sentence is limited to when CHAR/VARCHAR are not first-class / by default. See dee7bd4d8a1. ########## docs/sql-ref-ansi-compliance.md: ########## @@ -257,6 +268,11 @@ At the heart of this conflict resolution is the Type Precedence List which defin \*\*\* For a complex type, the precedence rule applies recursively to its component elements. +\*\*\*\* Preview (`spark.sql.charVarchar.standardSemantics.enabled`). Without that flag, CHAR +and VARCHAR are rewritten to STRING and this row does not apply. After CHAR or VARCHAR Review Comment: Thanks. Store assignment and the ANSI precedence footnote now use first-class types (preview or preserve) and "By default, CHAR and VARCHAR are rewritten to STRING". See dee7bd4d8a1. ########## docs/sql-ref-character-string-types.md: ########## @@ -0,0 +1,375 @@ +--- +layout: global +title: Character String Types +displayTitle: Character String Types (Preview) +license: | + Licensed to the Apache Software Foundation (ASF) under one or more + contributor license agreements. See the NOTICE file distributed with + this work for additional information regarding copyright ownership. + The ASF licenses this file to You under the Apache License, Version 2.0 + (the "License"); you may not use this file except in compliance with + the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + + Unless required by applicable law or agreed to in writing, software + distributed under the License is distributed on an "AS IS" BASIS, + WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + See the License for the specific language governing permissions and + limitations under the License. +--- + +First-class `CHAR` / `VARCHAR` / `STRING` semantics are a preview feature in Spark 4.4 and may +change in future releases. They are off by default. This page describes that preview mode only. + +Enable the preview feature before using the behavior below: + +```sql +SET spark.sql.charVarchar.standardSemantics.enabled = true; +``` + +By default, `CHAR` and `VARCHAR` are table-schema overlays on `STRING`. DataFrame schemas +show `STRING`, `CAST(... AS CHAR(n))` is `STRING`, and function schemas raise +`UNSUPPORTED_CHAR_OR_VARCHAR_AS_STRING`. See [Data Types](sql-ref-datatypes.html). + +Spark SQL then has a family of three character string types: + +```text +CHAR(n) -> VARCHAR(n) -> STRING +``` + +The arrow denotes type precedence from the most constrained type to the least constrained type. +This is analogous to the precedence of integral numeric types such as +`TINYINT -> SMALLINT -> INT -> BIGINT`. `CHAR`, `VARCHAR`, and `STRING` are distinct SQL types in +the same family; `CHAR` and `VARCHAR` are not special uses of `STRING`. + +## Related settings + +The preview flag is persisted with view definitions, so a view continues to use the semantics +under which it was created. + +The older `spark.sql.preserveCharVarcharTypeInfo` setting is an experimental compatibility mode. +It preserves type information, but does not apply all the type precedence and expression-result +rules described here. Standard semantics take precedence over +`spark.sql.legacy.charVarcharAsString` when both settings are `true`. + +Two other settings still apply when standard semantics are on: + +* `spark.sql.charAsVarchar` still rewrites `CHAR` to `VARCHAR` in `CREATE`/`REPLACE`/`ALTER TABLE`. + `CAST(... AS CHAR(n))` stays `CHAR(n)`. +* `spark.sql.readSideCharPadding` is ignored. Read-side CHAR padding and length checks always run; + setting it to `false` logs a warning. + +## Type definitions + +| SQL type | Value constraint | Behavior | +|----------|------------------|----------| +| `CHAR(n)` | Exactly `n` characters | Values shorter than `n` characters are padded on the right with spaces. | +| `VARCHAR(n)` | At most `n` characters | Values retain their length and are not padded. | +| `STRING` | Unbounded length | Values have no declared length constraint. | + +The length is required for `CHAR` and `VARCHAR`. `CHARACTER(n)` is a synonym for `CHAR(n)`. +The length `n` counts characters, not UTF-8 bytes. All three types can have a +[collation](sql-ref-syntax-aux-show-collations.html), which controls comparison, ordering, and +other collation-sensitive operations. + +For example: + +```sql +SELECT typeof(CAST('ab' AS CHAR(5))); +-- char(5) + +SELECT concat('<', CAST('ab' AS CHAR(5)), '>'); +-- <ab > + +SELECT typeof(CAST('ab' AS VARCHAR(5))); +-- varchar(5) +``` + +## Type precedence and least common type + +Spark finds the least common type when an operation needs one type that can represent multiple +inputs. Within the character string family, Spark uses these rules: + +| Input types | Least common type | +|-------------|-------------------| +| `CHAR(n)`, `CHAR(m)` | `CHAR(max(n, m))` | +| `CHAR(n)`, `VARCHAR(m)` | `VARCHAR(max(n, m))` | +| `VARCHAR(n)`, `VARCHAR(m)` | `VARCHAR(max(n, m))` | +| Any character string type and `STRING` | `STRING` | +| Any character string type `T` and untyped `NULL` | `T` | + +These rules apply recursively to arrays, maps, and structs. They are used by expressions and +operations such as `CASE`, `COALESCE`, `IN`, `UNION`, `INTERSECT`, `EXCEPT`, multi-row `VALUES`, +and collection constructors. + +```sql +SELECT typeof(coalesce( + CAST('a' AS CHAR(2)), + CAST('bb' AS CHAR(4)))); +-- char(4) + +SELECT typeof(coalesce( + CAST('a' AS CHAR(2)), + CAST('bb' AS VARCHAR(4)))); +-- varchar(4) + +SELECT typeof(coalesce( + CAST('a' AS VARCHAR(4)), + CAST('unbounded' AS STRING))); +-- string +``` + +Collation is also part of a character string type. The least-common-type rules above apply after +Spark resolves the input collations. When the result remains `CHAR` or `VARCHAR`, it retains the +resolved collation. + +## Casts + +An explicit `CAST` or `TRY_CAST` can introduce a `CHAR` or `VARCHAR` value: + +```sql +SELECT CAST('abc' AS CHAR(5)); +-- 'abc ' + +SELECT CAST('abc' AS VARCHAR(5)); +-- 'abc' +``` + +For an explicit cast from one character string type to another: + +* Casting to `CHAR(n)` truncates values longer than `n` characters and pads values shorter than + `n` characters. +* Casting to `VARCHAR(n)` truncates values longer than `n` characters. Spaces already present in + a `CHAR` value are kept, so `CAST(CAST('ab' AS CHAR(4)) AS VARCHAR(4))` is `'ab '`. + +For a cast from a non-character value, Spark first converts the value to characters and then +checks the target length. `CAST` raises `EXCEED_LIMIT_LENGTH` if the value does not fit, while +`TRY_CAST` returns `NULL`. + +```sql +SELECT CAST('abcdef' AS VARCHAR(3)); +-- 'abc' + +SELECT TRY_CAST(12345 AS VARCHAR(4)); +-- NULL +``` + +## Assignment and reading + +Assignment to a declared `CHAR` or `VARCHAR` type is stricter than an explicit +character-to-character cast. Assignment occurs for operations such as table writes, routine +arguments and return values, session variables, schema-driven Dataset or DataFrame conversion, and +the output columns of a script `TRANSFORM` clause. + +* A value that fits is stored unchanged, except that `CHAR(n)` is padded on the right to `n` + characters. +* If only trailing spaces exceed the declared length, Spark removes only the number of trailing + spaces needed to fit. +* If non-space characters exceed the declared length, Spark raises `EXCEED_LIMIT_LENGTH`. + +Reads apply the same checks. In particular, a `CHAR(n)` value read from external or otherwise +untrusted storage is padded if short and rejected if its non-space content is too long. The same +`EXCEED_LIMIT_LENGTH` check applies to oversize partition values. + +```sql +CREATE TABLE contacts (code CHAR(4), name VARCHAR(10)) USING parquet; + +INSERT INTO contacts VALUES ('ab', 'Ada'); + +SELECT concat('<', code, '>'), name FROM contacts; +-- <ab > Ada +``` + +A script `TRANSFORM` clause (including `MAP` and `REDUCE`) exchanges rows with the external script +as unbounded `STRING`, so a `CHAR(n)` or `VARCHAR(n)` in its `AS` output list does not constrain +what the script emits. Spark instead applies the assignment above to the script output in a +projection: a declared `CHAR(n)` is padded and an over-length value raises `EXCEED_LIMIT_LENGTH`. +The declared type is kept only when these semantics (or `spark.sql.preserveCharVarcharTypeInfo`) +are enabled; otherwise an `AS` type such as `CHAR(n)` is treated as plain `STRING` with no padding +or length check. + +## Expression result types + +A reference to a `CHAR` or `VARCHAR` column preserves its declared type. Expressions that select +among values without changing their character content, such as `CASE` and `COALESCE`, use the +least common type rules above. + +An expression that transforms character content returns `STRING`, because its result length is +not constrained by an input declaration. This includes string functions and operators such as +`upper`, `lower`, `substring`, `concat`, `||`, `regexp_replace`, `trim`, `split`, and `mask`. + +```sql +SELECT typeof(upper(CAST('ab' AS CHAR(2)))); +-- string + +SELECT typeof(CAST('a' AS CHAR(1)) || CAST('b' AS VARCHAR(1))); +-- string +``` + +This rule prevents a length constraint from propagating to newly computed content. To constrain +the result, cast it explicitly to `CHAR(n)` or `VARCHAR(n)`. + +## Comparisons and trailing spaces + +Spark coerces character string operands to a least common type before comparing them. Casting to a +wider `CHAR` pads the value, while casting to `VARCHAR` or `STRING` preserves spaces already +present in a `CHAR` value. + +```sql +SELECT CAST('a' AS CHAR(2)) = CAST('a' AS CHAR(4)); +-- true + +SELECT CAST('a' AS CHAR(2)) = CAST('a' AS VARCHAR(2)); +-- false + +SELECT CAST('a' AS CHAR(2)) = CAST('a ' AS VARCHAR(2)); +-- true +``` + +A [string literal](sql-ref-literals.html#string-literal) is `STRING`, not `CHAR` or `VARCHAR`. +Comparing it with `CHAR` is the same widening: the `CHAR` pad is kept, and the comparison is +exact. + +```sql +SELECT CAST('a' AS CHAR(2)) = 'a'; +-- false + +SELECT CAST('a' AS CHAR(2)) = 'a '; +-- true +``` + +A [parameter marker](sql-ref-parameter-markers.html) bound to a string value is also `STRING`. +The bound value is not padded unless you cast it to `CHAR`. + +```sql +EXECUTE IMMEDIATE 'SELECT CAST(''a'' AS CHAR(2)) = :p' USING 'a' AS p; +-- false + +EXECUTE IMMEDIATE 'SELECT CAST(''a'' AS CHAR(2)) = :p' USING 'a ' AS p; +-- true + +EXECUTE IMMEDIATE + 'SELECT CAST(''a'' AS CHAR(2)) = CAST(:p AS CHAR(2))' USING 'a' AS p; +-- true +``` + +Ignoring trailing spaces is a collation property, not an implicit property of every `CHAR` +comparison. Use an `RTRIM` collation when comparisons should ignore trailing spaces: + +```sql +SELECT CAST('a' AS CHAR(2) COLLATE UTF8_BINARY_RTRIM) = + 'a' COLLATE UTF8_BINARY_RTRIM; +-- true +``` + +## Parsing functions and map keys + +The functions `from_json`, `from_csv`, `from_xml`, and `from_avro` have two CHAR/VARCHAR surfaces: +the document they parse, and the schema they parse into. File scans use the same schema rules as +the corresponding `from_*` function except where noted. + +### Document arguments + +`from_json`, `from_csv`, `from_xml`, and `schema_of_json` / `schema_of_csv` / `schema_of_xml` take a +STRING document. A `CHAR` argument is not rtrimmed. Padding is part of the value after CAST to +STRING, so it is parsed as trailing spaces in the document: + +```sql +SELECT from_json(CAST('{"a":1}' AS CHAR(12)), 'a INT'); +-- same as from_json('{"a":1} ', 'a INT') +``` + +JSON and XML ignore that trailing whitespace. CSV can tokenize it: with a space delimiter, padding +becomes extra empty fields; with the default comma delimiter, padding lands on the last unquoted +field. When CHAR is first-class, `schema_of_csv(CAST('1' AS CHAR(3)))` infers +`STRUCT<_c0: DOUBLE>` because the padded last field is not a clean integer. Under default +settings that CAST is STRING and the inferred field is INT. +`map('ignoreTrailingWhiteSpace', 'true')` restores a trimmed last-field parse. + +`from_avro` takes binary Avro, not a character document, so CHAR padding of a SQL string does not +apply there. + +### Parsed values + +With a reader schema that declares `CHAR`/`VARCHAR` fields, parsed string values are assigned +when CHAR/VARCHAR are first-class (this preview flag or +`spark.sql.preserveCharVarcharTypeInfo`): `CHAR(n)` is padded, and an over-length value is a +bad record. For `from_json`, `from_csv`, and `from_xml`, `FAILFAST` raises +`EXCEED_LIMIT_LENGTH` and `PERMISSIVE` nulls the failed field. CSV field overflow follows the +CSV parse mode the same way. + +`from_avro` assigns STRING values only. Avro ENUM symbols are not padded or length-checked, so a +padded CHAR cannot invent a symbol. Overflow is a malformed record handled by the `mode` +option: `FAILFAST` fails with `EXCEED_LIMIT_LENGTH` as the cause; `PERMISSIVE` returns a null +record. Avro file scans leave CHAR padding of STRING values to read-side CHAR padding rather +than assigning in the deserializer. `to_json`, `to_csv`, `to_xml`, and `to_avro` serialize the +current string content; `to_avro` does not itself pad or trim. + +### Map keys + +A `CHAR(n)` or `VARCHAR(n)` map key is length-checked but never padded or trimmed: a `CHAR(n)` key +must already be exactly `n` characters, and a `VARCHAR(n)` key at most `n`. Padding or trimming a +key would change its identity and could silently merge distinct keys. + +* JSON (`from_json` and JSON scans): `UNSUPPORTED_JSON_CHAR_VARCHAR_MAP_KEY` (SQLSTATE `0A000`). + `from_json` captures the standard-semantics flag at analysis, so a view keeps the check from + when it was created. JSON file scans, including scans inside persisted views, read the flag + from the executing session. Duplicate names are kept, exactly as for STRING keys; + `spark.sql.mapKeyDedupPolicy` is not applied at parse time. Keys rebuilt read-side on a file + scan can still raise `DUPLICATED_MAP_KEY`. `from_json(to_json(m))` is not folded away when `m` + has a CHAR/VARCHAR map key. +* XML (`from_xml` and XML scans): `UNSUPPORTED_XML_CHAR_VARCHAR_MAP_KEY`. Checked names are + element names, `attributePrefix` plus attribute names (default prefix `_`), and the `valueTag` + (default `_VALUE`) when mixed text is present. Exact repeated names last-win. + `spark.sql.mapKeyDedupPolicy` is not applied. Empty, attribute-only, and text-only map elements + stay SQL NULL or a malformed record; they do not become an empty map or a `valueTag` entry. + When standard semantics are off and `spark.sql.preserveCharVarcharTypeInfo` is on, short CHAR + keys are padded and over-length keys raise `EXCEED_LIMIT_LENGTH`. +* Avro (`from_avro` and Avro scans): `UNSUPPORTED_AVRO_CHAR_VARCHAR_MAP_KEY`. `from_avro` + PERMISSIVE nulls that malformed record; Avro file scans do not wrap per-row PERMISSIVE around + the error. +* CSV has no object-name map keys; `from_csv` schemas are structs of fields. + +`PERMISSIVE` sets the nearest enclosing field to `null`; `FAILFAST` fails with the error above as +the cause of `MALFORMED_RECORD_IN_PARSING`. `variant_get` and `to_variant_object` reject +CHAR/VARCHAR map keys at analysis. + +## Schema propagation + +With standard semantics enabled, declared character string types remain visible through Spark SQL +and DataFrame schemas. This includes: + +* table, view, and common table expression columns; +* `CREATE TABLE AS SELECT` results; +* SQL routine parameters and return types; +* session and scripting variables; +* nested array, map, and struct fields; and +* schemas supplied to Dataset and DataFrame APIs. + +Storage formats and catalogs differ in how they persist logical type metadata. Whenever a schema +declares `CHAR(n)` or `VARCHAR(n)`, Spark applies the length, padding, assignment, and read checks +described on this page. + +### Data source behavior + +Catalog tables retain `CHAR` and `VARCHAR` declarations in their catalog schema. For file-only +schema inference, support depends on the format: + +| Data source | Behavior | +|-------------|----------| +| Parquet catalog table | Uses the catalog schema to retain the declared type. | +| ORC | Stores logical type metadata on physical string fields so schema inference can restore `CHAR` and `VARCHAR`. | +| Avro | Stores logical type metadata on string fields and map keys so schema inference can restore `CHAR` and `VARCHAR`. | +| JSON, CSV, and XML | Apply `CHAR` and `VARCHAR` semantics when the reader is given an explicit schema; text inference alone does not infer a length constraint. See [Parsing functions and map keys](#parsing-functions-and-map-keys) for map-key handling. CSV overflow is a bad record under the CSV parse mode. | + +When standard semantics are disabled, file-only inference reads the constrained fields as +`STRING`. + +### Client metadata + +Spark Connect and the Spark Thrift Server expose the declared character string type and length to +clients. JDBC metadata reports `java.sql.Types.CHAR` or `java.sql.Types.VARCHAR`, the declared +length as the precision, and four times the declared length as `CHAR_OCTET_LENGTH`, reflecting the +maximum UTF-8 bytes per character. PySpark Arrow conversion at the Python host rejects CHAR and Review Comment: Thanks. Named the actual surfaces: Spark Connect collect() returns CHAR/VARCHAR; classic toPandas()/createDataFrame from pandas fall back off Arrow by default; Python UDF/UDAF/UDTF raise CHAR_VARCHAR_NOT_SUPPORTED_IN_PYTHON. See dee7bd4d8a1. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
