parse error with in-column-select-statement

[aisbergde]

This mssqlserver view can't be parsed

the issue could be related to the in-column-statement, which for example is used at

• InheritDescriptions_actual
• PK_json

CREATE VIEW [integration].[Schema_Table] AS
SELECT
	[Schema_Name] = [T2].[Schema_Name],
	[Table_Name] = [T1].[Table_Name],
	[AnonStatement] = [T1].[AnonStatement],
	[CompressionTypeID] = [T1].[CompressionTypeID],
	[Description] = [T1].[Description],
	[DoNotExport] = [T1].[DoNotExport],
	[DontInheritPK] = [T1].[DontInheritPK],
	[FriendlyName] = [T1].[FriendlyName],
	[Hashkey] = [T1].[Hashkey],
	[HistOfTableID] = [T1].[HistOfTableID],
	[HubOfTableID] = [T1].[HubOfTableID],
	[InheritDescriptions] = [T1].[InheritDescriptions],
	[InheritDescriptions_actual] = ISNULL([T1].[InheritDescriptions], -- Parameter_Name must exist in INTERN.PARAMETERS:
--(SELECT ParameterValue = COALESCE(PV.Param_Value, P.Default_Value, '') FROM INTERN.PARAMETERS P (NOLOCK) LEFT JOIN [CFG].[PARAM_VALUES] PV (NOLOCK) ON PV.Param_Name = P.Param_Name  WHERE P.Param_Name = 'DESCRIPTION_INHERIT_TABLES')
--this works also for Custom-Parmeters, existing only in [CFG].[PARAM_VALUES]:
(SELECT ParameterValue = COALESCE(PV.Param_Value, P.Default_Value, '') FROM (SELECT Param_Name FROM INTERN.PARAMETERS (NOLOCK) UNION SELECT Param_Name FROM INTERN.PARAMETERS (NOLOCK)) AS U LEFT JOIN INTERN.PARAMETERS P (NOLOCK) ON P.Param_Name = U.Param_Name LEFT JOIN [CFG].[PARAM_VALUES] PV (NOLOCK) ON PV.Param_Name = U.Param_Name WHERE P.Param_Name = 'DESCRIPTION_INHERIT_TABLES')
),
	[InheritFriendlyNames] = [T1].[InheritFriendlyNames],
	[InheritFriendlyNames_actual] = ISNULL([T1].[InheritFriendlyNames], -- Parameter_Name must exist in INTERN.PARAMETERS:
--(SELECT ParameterValue = COALESCE(PV.Param_Value, P.Default_Value, '') FROM INTERN.PARAMETERS P (NOLOCK) LEFT JOIN [CFG].[PARAM_VALUES] PV (NOLOCK) ON PV.Param_Name = P.Param_Name  WHERE P.Param_Name = 'FRIENDLYNAME_INHERIT_TABLES')
--this works also for Custom-Parmeters, existing only in [CFG].[PARAM_VALUES]:
(SELECT ParameterValue = COALESCE(PV.Param_Value, P.Default_Value, '') FROM (SELECT Param_Name FROM INTERN.PARAMETERS (NOLOCK) UNION SELECT Param_Name FROM INTERN.PARAMETERS (NOLOCK)) AS U LEFT JOIN INTERN.PARAMETERS P (NOLOCK) ON P.Param_Name = U.Param_Name LEFT JOIN [CFG].[PARAM_VALUES] PV (NOLOCK) ON PV.Param_Name = U.Param_Name WHERE P.Param_Name = 'FRIENDLYNAME_INHERIT_TABLES')
),
	[InheritReferences] = [T1].[InheritReferences],
	[IsInMemory] = [T1].[IsInMemory],
	[LinkOfTableID] = [T1].[LinkOfTableID],
	[OLAP_DataCategory] = [T1].[OLAP_DataCategory],
	[OLAP_IsHidden] = [T1].[OLAP_IsHidden],
	[PersistOfTableID] = [T1].[PersistOfTableID],
	[Perspective] = [T1].[Perspective],
	[SatOfTableID] = [T1].[SatOfTableID],
	[SchemaID] = [T1].[SchemaID],
	[SchemaName_TableName] = CONCAT(T2.Schema_Name, '.', [T1].[Table_Name]),
	[SchemaName_TableName_brackets] = CONCAT('[', T2.Schema_Name, '].[', [T1].[Table_Name], ']'),
	[TableID] = [T1].[TableID],
	[TableType_Name] = [T3].[Type_Name],
	[TableTypeID] = [T1].[TableTypeID],
	[PK_json] = --sorted list of PK:
(
SELECT
    [PK_Ordinal_Position] AS PK
    , [Column_Name] AS C
FROM
    [CFG].[COLUMNS] AS cfgc
WHERE
[T1].[TableID] = cfgc.[TableID]
AND NOT [PK_Ordinal_Position] IS NULL
ORDER BY [PK_Ordinal_Position]
FOR JSON AUTO
)
FROM
	[CFG].[TABLES] T1 (NOLOCK)
	LEFT JOIN [CFG].[SCHEMAS] T2 (NOLOCK) ON 
		T2.[SchemaID] = T1.[SchemaID]
	LEFT JOIN [INTERN].[TABLE_TYPES] T3 (NOLOCK) ON 
		T3.[TableTypeID] = T1.[TableTypeID]
WHERE
	1 = 1

[aisbergde]

similar issue:

CREATE VIEW [AC_metadata].[ac_cfg_columns] AS
SELECT
	[ColumnID] = [T1].[ColumnID],
	[ac_cfg_columns] = (
SELECT [ColumnID]
      ,[TableID]
      ,[Column_Name]
      ,[Description]
      ,[TypeID]
      ,[Character_Maximum_Length]
      ,[Numeric_Scale]
      ,[Numeric_Precision]
      ,[Is_Nullable]
      ,[PK_Ordinal_Position]
      ,[FriendlyName]
      ,[ToOLAP]
      ,[Default]
      ,[IsCalculated]
      ,[IsPersisted]
      ,[Statement]
      ,[IsIdentity]
      ,[IdentitySeed]
      ,[IdentityIncrement]
      ,[Anonymize]
      ,[Collation]
      ,[InheritFriendlyNames]
      ,[InheritDescriptions]
      ,[OLAP_DisplayFolder]
      ,[OLAP_DataCategory]
      ,[OLAP_FormatString]
      ,[AggregateID]
      ,[OLAPRefColumnID]
      ,[OLAPDoubleSided]
FOR JSON PATH
),
	[Column_Name] = [T1].[Column_Name]
FROM
	[CFG].[COLUMNS] T1 (NOLOCK)
WHERE
	1 = 1

[nickolay]

I'm not sure what the requested feature is. Please check out this part of the README:

If you are unable to submit a patch, feel free to file an issue instead. Please try to include:

• some representative examples of the syntax you wish to support or fix;
• the relevant bits of the SQL grammar, if the syntax is part of SQL:2016; and
• links to documentation for the feature for a few of the most popular databases that support it.

Please be aware that, while we strive to address bugs and review PRs quickly, we make no such guarantees for feature requests. If you need support for a feature, you will likely need to implement it yourself. Our goal as maintainers is to facilitate the integration of various features from various contributors, but not to provide the implementations ourselves, as we simply don't have the resources.

[aisbergde]

this is valid syntax:

select a = (select top 1 c from D) from b

in the Syntax for SELECT the select_list is not deeper specified but it can be a sub query included in ()

After my first evaluations of this parser it looks like it is to complicated to post processing the json. What I need is something between sql formatter and full parser. For example if the statement is

select a = (select top 1 c from D) from b

then I don't need the (select top 1 c from D) to be parsed 'to the end' but I need this as a string as it is. If I get this parsed by the parser (which currently is not supported) I would need to reconvert it from json into a string / sql.

But I am afraid the goal of this parser here is to "parse the statement to the end" and not to do some half parsing as I would need.