none
INSERT/SELECT involving sys.views query generates gibberish in "truncated value" error when values wouldn't be truncated RRS feed

  • Question

  • The following INSERT/SELECT is intended to insert a list of unique index column names into a user table, excluding tables specified in a separate user table:

    INSERT src (refid,columnid,columnsequence)
    SELECT si.name,sc.name,sic.index_column_id 
    FROM sys.indexes si, sys.objects so, sys.index_columns sic, sys.columns sc 
    WHERE si.object_id = so.object_id 
    AND sic.object_id = si.object_id 
    AND sic.index_id = si.index_id 
    AND sc.object_id = si.object_id 
    AND sc.column_id = sic.column_id 
    AND so.type = 'U' 
    AND si.is_primary_key = 0 
    AND si.is_unique = 1 
    AND so.name NOT IN  
     (
      SELECT tableid FROM ignoretab 
     )

    For reference:
    create table ignoretab (tableid NVARCHAR(40))

    create table src (
      refid NVARCHAR(40),
      columnid NVARCHAR(40),
      columnsequence NUMERIC(18,0))

    Our software uses SQL Server as backend, and this example works as intended in all but in 3 of many databases with essentially same data model. In those, the following error occurs:

    Msg 2628, Level 16, State 1, Line 29
    String or binary data would be truncated in table 'testbad.dbo.src', column 'refid'. Truncated value: '珐葻ʒ.P...睐葻ʒ..........Ì.몀롿Ì..ʒ.༠脡ʒ.᫠葸ʒ.'.
    The statement has been terminated.

    Problematic because:
    1. the truncated value is gibberish
    2. none of the values that should be inserted are > 40 characters

    The SELECT without INSERT returns "refid" values all <= 30. Internal indexes with names longer names exist do exist e.g., plan_persist_query_template_parameterization_cidx, but these are filtered out as type IT.

    Apparently length checking is done "too early", thus generating the truncation error that should not actually occur.

    Error vs. no error is apparent as different execution plans. Understood that different data might result in different plans, but with essentially same data, it is not clear what is "wrong" in these few databases to result in a different plan.

    Normally we rebuild indexes/refresh stats, but these are data dictionary views. So the question is what can be done in the "bad" databases to bring them in line with the majority? One answer might be hints, but arguably hints aren't necessary in the majority.
        
    Any recommendations would be appreciated.
    Tuesday, June 30, 2020 3:38 PM

All replies

  • Well, check the collations of the server(s), database(s), tables(s) and column(s) involved. And check the actual column names..

    Tuesday, June 30, 2020 3:51 PM
  • Hi
    can display the rows of ignoretab 

    Thanks and Regards
    Laxmidhar saho
    Tuesday, June 30, 2020 5:10 PM
  • The simple way to make sure your query doesn't produce an error is to cast the character values to the maximum length of columns in the table, e.g.

    INSERT src (refid,columnid,columnsequence)
    SELECT cast(si.name as varchar(40)), cast(sc.name as varchar(40)), sic.index_column_id 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 30, 2020 5:17 PM
    Moderator
  • I would say that this has a distinct small of a bug. It looks like a text pointer that has gone awry. What CU of SQL 2019 are the instances where you see this problem on?

    What happens if you disable the database-scoped configuration opekion VERBOSE_TRUNCATION_WARNINGS for these databases? I'm not saying that this is a solution, but if the bug is related to the new verbose (and much more useful) truncation error messags in SQL 2019, this can serve as a workaround until Microsoft has a fix ready.

    I would also recommend you to open a support case to get this resolved.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 30, 2020 9:07 PM
  • Hi Erland,

    It may not return gibberish with the setting disabled, but the truncation is going to occur anyway, right?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 30, 2020 9:33 PM
    Moderator
  • It may not return gibberish with the setting disabled, but the truncation is going to occur anyway, right?

    The gibberish will certainly not be there, since then you will get a fixed error message. But the truncation message seems to be bogus from the start, so if that is an accident that happens in the code path for the new error message, the truncation error may not appear at all. But that is just pure speculation.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, June 30, 2020 10:01 PM
  • Hi bpol22,

    Please check if below links could help you.

    String or Binary data would be truncated: replacing the infamous error 8152
    SQL truncate enhancement: Silent Data truncation in SQL Server 2019

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 1, 2020 7:00 AM
  • Interesting thought, but I only have copies of 2 of the 3 "problematic" databases. One does hhave a slightly different collation - SQL_Latin1_General_CP437_CI_AS but the other has the default SQL_Latin1_General_CP1_CI_AS which matches "good" databases. So I suspect not necessarily related to collation. 
    Wednesday, July 1, 2020 3:00 PM
  • ignoretab is actually empty. I can add a row with a random name but it makes no difference. Table names therein are to be excluded anyway.
    Wednesday, July 1, 2020 3:01 PM
  • This is a good trick and does indeed workaround the error! It doesn't seem like this should be necessary but I never argue with what works.
    Wednesday, July 1, 2020 3:01 PM
  • It isn't specific to SQL 2019. I believe the gibberish is a bug - that only displays in SQL 2019 as that is new functionality. However the "inappropriate" truncation error also occurs in SQL 2017, just without the gibberish. This INSERT/SELECT has been around for some time and only as of SQL 2017 and SQL 2019 has the error appeared. SQL 2017 introduced internal table plan_persist_query_template_parameterization that has 2 long index names that appear to be the cause. These appear with the SELECT only if the so.type='U' predicate is removed, so those would be truncated as such. My point is that since they are filtered out, there is nothing to truncate and should not be a truncation error. IMO this is a bug, but perhaps more debateable. The way it works is seemingly not new.

    Thought about turning off the warning, but the context here is to match meta data in application tables with actual database data dictionary. At times there are real "illegal" values, so we don't want to cover up the error.
    Wednesday, July 1, 2020 3:02 PM
  • As mentioned above, my opinion is that the gibberish and the "check too early" are separate problems, I don't know enough internals to argue that case. Given that the same truncation problem happens in SQL 2017, it wouldn't seem they necessary broke the processing with the additional supplemental info added in SQL 2019, but just didn't do it quite right in these circumstances. You may be right, though - the "check too early" behavior may have always been there but coincidently never noticed, and the gibberish is just a manifestation.
    Wednesday, July 1, 2020 3:04 PM
  • Thanks all for your input!
    I did plan on opening a case with MS Support, but we are a development shop and do not have support. We've done one-off cases before, but thanks to COVID-19 cutbacks, unfortunately I won't get authorization to spend the $500 to tell MS about the bug, particularly when I have a viable workaround. 
    Wednesday, July 1, 2020 3:05 PM
  • Logically and according to this article https://www.itprotoday.com/sql-server/logical-query-processing-what-it-and-what-it-means-you the check should not occur since the values are supposed to be filtered. However, perhaps that initial check using table to insert happens right away without actually executing the query, so it's better to use defensive code to make sure to not even have a possibility of the truncation.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 1, 2020 3:27 PM
    Moderator
  • Sorry for response confusion - Haven't posted here before and was thinking, a la Teams, replying under a comment would keep comments together, but upon F5, everything went to the bottom.

    So just wanted to clarify that the "good trick" comment applied to the suggestion to explicitly CAST () the data types in the SELECT.

    However, not necessarily useable for us - if there is an actual user index (on table type=U) name too long, we do want the exception to occur. It should just ignore internal indexes (on table type=IT). The logic does that in theory, but the theory isn't being necessarily applied here, in a select few databases.

    I do always advocate defensive coding, but here I wouldn't have thought there was something that needed defending against, beyond defending against internal objects being included.

    The long shot I was hoping for was to understand what is different (and "fix") in these 2 or 3 databases vs. like another 50+ databases in which there is no problem. Perhaps it is MS that would have to answer that question. 

    Thanks.

    Wednesday, July 1, 2020 4:27 PM
  • Sounds good, perhaps we'll get a good response from someone from MS with details and explanation. I'm guessing Erland may also discuss this thread among the other SQL Server MVPs and MS people so we'll get more ideas.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 1, 2020 4:43 PM
    Moderator