Version Information

Server Version: 2.2.0

Environment

Core

What is the expected behaviour?

Nvarchar(max) should be casted as proper varchar length

Keywords

Hello, we are seeing an issue when a query is being made. nvarchar(max)) AS varchar - SQL server varchar default is to convert to 30 characters and our GUID fails to match - seems like this was fixed in v2.0.0-alpha.3 but we still see it. We are on 2.2.0 version.
Please see the below link/query. Any help would be appreciated

What is the current behaviour?

https://github.com/hasura/graphql-engine/search?q=%22as+varchar%22&type=code

SELECT ISNULL((SELECT [t_tblSettingsPersona1].[PKey] AS [PKey],\n [t_tblSettingsPersona1].[fldAcctAdmin] AS [fldAcctAdmin]\nFROM [dbo].[tblSettingsPersonal] AS [t_tblSettingsPersona1]\nWHERE ((((([t_tblSettingsPersona1].[fldCrewID]) = (CAST(CAST((N'36a6257b-08bb-45ef-a5cf-c1b7a7997087') AS nvarchar(max)) AS varchar)))\n OR ((([t_tblSettingsPersona1].[fldCrewID]) IS NULL)\n AND ((CAST(CAST((N'36a6257b-08bb-45ef-a5cf-c1b7a7997087') AS nvarchar(max)) AS varchar)) IS NULL)))))\nFOR JSON PATH, INCLUDE_NULL_VALUES), '[]')"

SELECT (CAST(CAST((N'36a6257b-08bb-45ef-a5cf-c1b7a7997087') AS nvarchar(max)) AS varchar)) will give you 36a6257b-08bb-45ef-a5cf-c1b7a7 as result and hence won't match the GUID.

How to reproduce the issue?

1. Create a varchar column
2. Issue a query via API console
3. Watch console and you will see nvarchar(max)) AS varchar in generated query

Screenshots or Screencast

Please provide any traces or logs that could help here.

{"type":"query-log","timestamp":"2022-02-08T20:47:21.449+0000","level":"info","detail":{"kind":"database","request_id":"17f78a13-d0f6-48ca-99f6-dbc155912425","generated_sql":{"tblSettingsPersonal":{"prepared_arguments":null,"query":"SELECT ISNULL((SELECT [t_tblSettingsPersona1].[PKey] AS [PKey],\n [t_tblSettingsPersona1].[fldAcctAdmin] AS [fldAcctAdmin]\nFROM [dbo].[tblSettingsPersonal] AS [t_tblSettingsPersona1]\nWHERE ((((([t_tblSettingsPersona1].[fldCrewID]) = (CAST(CAST((N'36a6257b-08bb-45ef-a5cf-c1b7a7997087') AS nvarchar(max)) AS varchar)))\n OR ((([t_tblSettingsPersona1].[fldCrewID]) IS NULL)\n AND ((CAST(CAST((N'36a6257b-08bb-45ef-a5cf-c1b7a7997087') AS nvarchar(max)) AS varchar)) IS NULL)))))\nFOR JSON PATH, INCLUDE_NULL_VALUES), '[]')"}},"query":{"operationName":"MyQuery","query":"query MyQuery {\n tblSettingsPersonal {\n PKey\n fldAcctAdmin\n }\n}\n"}}}

Any possible solutions?

Below query will work and give appropriate results back - change from varchar to varchar(max) - column is actually varchar(255)

SELECT
	ISNULL((
	SELECT
		[t_tblSettingsPersona1].[PKey] AS [PKey],
		[t_tblSettingsPersona1].[fldAcctAdmin] AS [fldAcctAdmin]
		FROM [dbo].[tblSettingsPersonal] AS [t_tblSettingsPersona1]
		WHERE ((((([t_tblSettingsPersona1].[fldCrewID]) = (CAST(CAST((N'36a6257b-08bb-45ef-a5cf-c1b7a7997087') AS nvarchar(max)) AS varchar(max))))
			OR ((([t_tblSettingsPersona1].[fldCrewID]) IS NULL)
				AND ((CAST(CAST((N'36a6257b-08bb-45ef-a5cf-c1b7a7997087') AS nvarchar(max)) AS varchar(max))) IS NULL)))))FOR JSON PATH,
		INCLUDE_NULL_VALUES),
	'[]')