| --- | |
| 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). | |
| With the preview enabled, Spark SQL 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 for analysis of the view body, so a view | |
| continues to use the semantics under which it was created. `from_json` and `from_avro` capture | |
| the flag at analysis. JSON file scans and `from_xml` read the executing session's flag. | |
| 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 interact with standard semantics: | |
| * `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. The least common type is computed across all inputs together. | |
| Adding a `VARCHAR` input can change equality: `CHAR` vs `CHAR` pads, but widening to | |
| `VARCHAR` keeps each original pad. | |
| ```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 collate UTF8_BINARY | |
| SELECT CAST('a' AS CHAR(2)) IN (CAST('a' AS CHAR(4))); | |
| -- true | |
| SELECT CAST('a' AS CHAR(2)) IN ( | |
| CAST('a' AS CHAR(4)), CAST('b' AS VARCHAR(3))); | |
| -- false (all widen to VARCHAR(4): 'a ' vs 'a ') | |
| ``` | |
| 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`, and `mask`. | |
| `split` returns `ARRAY<STRING>`. | |
| ```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 | |
| ``` | |
| The same widening applies to a `CHAR` table column. By default the column is typed `STRING`, so | |
| Spark pads the literal to the column length and `WHERE code = 'ab'` matches. Under the preview | |
| the column is `CHAR(n)`, the comparison casts the column to `STRING`, and the literal is not | |
| padded, so a stored `'ab '` does not match `'ab'`. The same applies to `IN ('ab', ...)` and | |
| to predicates on CHAR partition columns. | |
| ```sql | |
| SELECT name FROM contacts WHERE code = 'ab'; | |
| -- no rows | |
| SELECT name FROM contacts WHERE code = 'ab '; | |
| -- Ada | |
| ``` | |
| 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` fails with | |
| `MALFORMED_RECORD_IN_PARSING` caused by `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 | |
| struct with every field NULL (NULL for a non-struct schema). 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. | |
| Unlike `from_json`, `from_xml` reads the executing session's flag, so a persisted view created | |
| with the flag on uses pad-and-`EXCEED_LIMIT_LENGTH` for a caller with the flag off. 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 returns a struct with every field NULL (NULL for a non-struct schema); 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. | |
| For JSON and XML, `PERMISSIVE` sets the nearest enclosing field to `null`; `FAILFAST` fails | |
| with the error above as the cause of `MALFORMED_RECORD_IN_PARSING`. `from_avro` FAILFAST | |
| throws a malformed-record `SparkException` with no error class. `variant_get`, | |
| `to_variant_object`, and `CAST(... AS VARIANT)` reject CHAR/VARCHAR anywhere in the type, | |
| not only as map keys. | |
| ## 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 | Stores logical type metadata so file-only schema inference can restore `CHAR` and `VARCHAR`, same as ORC. | | |
| | 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. | | |
| By default, when CHAR/VARCHAR are not first-class, 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. Spark Connect `collect()` returns CHAR/VARCHAR values. | |
| Classic `toPandas()` and `createDataFrame` from pandas fall back off Arrow by default when | |
| Python's `CharType` cannot convert; Python UDF return types, UDAF buffer schemas, and UDTF | |
| return types raise `CHAR_VARCHAR_NOT_SUPPORTED_IN_PYTHON`. |