My query doesn't seems to parse information_type

STEP TO REPRODUCE:

1. Take AdventureWork or any other test database
2. Right click on your test database > Tasks > Data Discovery and Classification > Classify Data... and classify a bunch of data
3. Now use this query to check your information_type column (here I'm using AdventureWork2019):

Paste this select and execute

SELECT 
    schema_name(O.schema_id) AS schema_name,
    O.NAME AS table_name,
    C.NAME AS column_name,
         [Type] = 
    CASE 
      WHEN ct.[name] IN ('varchar', 'char') THEN ct.[name]   '('   IIF(c.max_length = -1, 'max', CAST(c.max_length AS VARCHAR(25)))   ')' 
      WHEN ct.[name] IN ('nvarchar','nchar') THEN ct.[name]   '('   IIF(c.max_length = -1, 'max', CAST(c.max_length / 2 AS VARCHAR(25)))  ')'      
      WHEN ct.[name] IN ('decimal', 'numeric') THEN ct.[name]   '('   CAST(c.precision AS VARCHAR(25))   ', '   CAST(c.scale AS VARCHAR(25))   ')'
      WHEN ct.[name] IN ('datetime2') THEN ct.[name]   '('   CAST(c.scale AS VARCHAR(25))   ')'
      ELSE ct.[name]
    END,
    information_type,
    label,
    rank,
    rank_desc,
    CAST(
             CASE
                  WHEN information_type = 'Contact Info' 
                     THEN 'contact info'
                  ELSE 'not contact info'
             END AS varchar(max)) as Checking_Content
FROM sys.sensitivity_classifications sc
    JOIN sys.objects O
    ON  sc.major_id = O.object_id
    JOIN sys.columns C 
    ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id
    JOIN sys.types ct ON C.user_type_id = ct.user_type_id
    --AND EP.minor_id = C.column_id
    order by information_type

As you can see the query is returning not contact info even when the column information_type has value Contact Info

I'm very confused:

What do you think?

CodePudding user response：

According to

exec sp_describe_first_result_set N'select * from sys.sensitivity_classifications sc'

information_type is a sql_variant. Which is not per the docs but should not be an issue as compatible types are converted to sql_variant for comparison without issue. EG

select case when  cast(N'Contact Info' as sql_variant) = 'Contact Info' then 1 else 0 end

returns

1

So this is potentially a bug. You can work around by casting explitly to nvarchar. eg

SELECT 
    schema_name(O.schema_id) AS schema_name,
    O.NAME AS table_name,
    C.NAME AS column_name,
         [Type] = 
    CASE 
      WHEN ct.[name] IN ('varchar', 'char') THEN ct.[name]   '('   IIF(c.max_length = -1, 'max', CAST(c.max_length AS VARCHAR(25)))   ')' 
      WHEN ct.[name] IN ('nvarchar','nchar') THEN ct.[name]   '('   IIF(c.max_length = -1, 'max', CAST(c.max_length / 2 AS VARCHAR(25)))  ')'      
      WHEN ct.[name] IN ('decimal', 'numeric') THEN ct.[name]   '('   CAST(c.precision AS VARCHAR(25))   ', '   CAST(c.scale AS VARCHAR(25))   ')'
      WHEN ct.[name] IN ('datetime2') THEN ct.[name]   '('   CAST(c.scale AS VARCHAR(25))   ')'
      ELSE ct.[name]
    END,
    information_type,
    label,
    rank,
    rank_desc,
    CAST(
             CASE
                  WHEN cast(information_type as nvarchar(200)) = 'Contact Info' 
                     THEN 'contact info'
                  ELSE 'not contact info'
             END AS varchar(max)) as Checking_Content
FROM sys.sensitivity_classifications sc
    JOIN sys.objects O
    ON  sc.major_id = O.object_id
    JOIN sys.columns C 
    ON  sc.major_id = C.object_id  AND sc.minor_id = C.column_id
    JOIN sys.types ct ON C.user_type_id = ct.user_type_id
    --AND EP.minor_id = C.column_id
    order by information_type