[
https://issues.apache.org/jira/browse/CALCITE-7756?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18116918#comment-18116918
]
Guanyu Chen edited comment on CALCITE-7756 at 9/19/26 12:11 AM:
----------------------------------------------------------------
Hi, I'd like to share some thoughts and potentially help on this.
According to the SQL Server's documentation
([https://learn.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017#b-show-the-default-value-of-n-when-varchar-is-used-with-cast-and-convert]),
the default value of _n_ is 30 when the *char* or *varchar* data types are
used with the {{CAST}} and {{CONVERT}} functions.
There are potential two ways to handle this:
# Use the suggested varchar(max) to allow all possible length that can be
casted or converted.
# Use varchar(n) (where n can be a value between 1 and 8,000) with an
appropriate value of n.
Also quoted from SQL Server's documentation warning from the same link:
{quote}Each non-null *varchar(max)* or *nvarchar(max)* column requires 24 bytes
of additional fixed allocation, which counts against the 8,060 byte row limit
during a sort operation. This can create an implicit limit to the number of
non-null *varchar(max)* or *nvarchar(max)* columns that can be created in a
table.
{quote}
It looks like it can introduce some potential issue even though I'm not sure
the likelihood.
was (Author: JIRAUSER314714):
Hi, I'd like to share some thoughts and potentially help on this.
According to the SQL Server's documentation
([https://learn.microsoft.com/en-us/sql/t-sql/data-types/char-and-varchar-transact-sql?view=sql-server-2017#b-show-the-default-value-of-n-when-varchar-is-used-with-cast-and-convert]),
the default value of _n_ is 30 when the *char* or *varchar* data types are
used with the {{CAST}} and {{CONVERT}} functions.
There are potential two ways to handle this:
# Use the suggested varchar(max) to allow all possible length that can be
casted or converted.
# Use varchar(n) (where n can be a value between 1 and 8,000) with an
appropriate value of n.
Also quoted from SQL Server's documentation warning from the same link:
{quote}Each non-null *varchar(max)* or *nvarchar(max)* column requires 24 bytes
of additional fixed allocation, which counts against the 8,060 byte row limit
during a sort operation. This can create an implicit limit to the number of
non-null *varchar(max)* or *nvarchar(max)* columns that can be created in a
table.
{quote}
It looks like it can introduce some potential issue even though I'm not sure
the likelihood.
> Invalid unparse for VARCHAR without precision in MssqlSqlDialect
> ----------------------------------------------------------------
>
> Key: CALCITE-7756
> URL: https://issues.apache.org/jira/browse/CALCITE-7756
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.42.0
> Reporter: Jerome Isaac Haltom
> Priority: Major
>
> {{CAST(x AS VARCHAR)}} with no precision unparses as a bare {{VARCHAR}} for
> SQL Server. A bare {{varchar}} is 30 characters in a CAST, so the value gets
> cut to 30.
> {code:sql}
> -- g is a uniqueidentifier, n is an nvarchar(200) holding 40 characters
> SELECT CAST(g AS VARCHAR) FROM t
> -- Msg 8170: Insufficient result space to convert uniqueidentifier value to
> char
> SELECT LEN(CAST(n AS VARCHAR)) FROM t
> -- 30, no error
> {code}
> SQL Server spells unbounded {{varchar(max)}}. I think that is what
> {{MssqlSqlDialect#getCastSpec}} should write when the precision is
> unspecified.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
