Random invalid characters in SQL result

[Chris26]

Using 17.0.10.6230 From Interactive SQL

When I run the following SQL statement (something like a manual cascade delete; and yes I know it's not perfect, but that's beside the point here) on a database with a specific table and FK structure.

WITH RECURSIVE table_structure (child_table_name, join_clause, recursive_level) 
    AS ( // initial subquery
         SELECT Lower(String(sysfk.foreign_creator, '.', sysfk.foreign_tname)), 
                String(Lower(String(sysfk.primary_creator, '.' , sysfk.primary_tname)), 
                       ' INNER JOIN ', 
                       Lower(String(sysfk.foreign_creator, '.', sysfk.foreign_tname)), 
                       // Build up the join clause
                       ( SELECT ' ON ' + List((SELECT '(' + List(String(Lower(String(fk_coltab.table_name, '.', fk_col.column_name)), ' = ', Lower(String(pk_coltab.table_name, '.', pk_col.column_name))), ' AND ') + ') '
                                                 FROM sysidxcol AS fkc
                                                      INNER JOIN systabcol AS fk_col 
                                                              ON fkc.table_id  = fk_col.table_id
                                                             AND fkc.column_id = fk_col.column_id
                                                      INNER JOIN systab AS fk_coltab
                                                              ON fk_col.table_id = fk_coltab.table_id,
                                                      systabcol AS pk_col
                                                      INNER JOIN systab AS pk_coltab
                                                              ON pk_col.table_id = pk_coltab.table_id
                                                WHERE fkc.table_id = fk.foreign_table_id
                                                  AND fkc.index_id = fk.foreign_index_id
                                                  AND pk_col.table_id = fk.primary_table_id
                                                  AND pk_col.column_id = fkc.primary_column_id), 'OR ')

                           FROM sysfkey AS fk
                                INNER JOIN systab AS fk_tab 
                                        ON fk_tab.table_id = fk.foreign_table_id
                                INNER JOIN systab AS pk_tab 
                                        ON pk_tab.table_id = fk.primary_table_id
                                INNER JOIN SYSIDX AS ix 
                                        ON ix.table_id = fk.foreign_table_id 
                                       AND ix.index_id = fk.foreign_index_id
                          WHERE pk_tab.table_name = sysfk.primary_tname
                            AND fk_tab.table_name = sysfk.foreign_tname )), 
                0 AS recursive_level
           FROM sysforeignkeys AS sysfk
          WHERE sysfk.primary_tname = 'MyTableName'
            AND sysfk.primary_tname <> sysfk.foreign_tname
         UNION ALL
         // recursive subquery
         SELECT Lower(String(sysfk.foreign_creator, '.', sysfk.foreign_tname)), 
                String(ts.join_clause,
--                       char(13), char(10),
                       ' INNER JOIN ', 
                       Lower(String(sysfk.foreign_creator, '.', sysfk.foreign_tname)), 
                       // Build up the join clause
                       ( SELECT ' ON ' + List((SELECT '(' + List(String(Lower(String(fk_coltab.table_name, '.', fk_col.column_name)), ' = ', Lower(String(pk_coltab.table_name, '.', pk_col.column_name))), ' AND ') + ') '
                                                 FROM sysidxcol AS fkc
                                                      INNER JOIN systabcol AS fk_col 
                                                              ON fkc.table_id  = fk_col.table_id
                                                             AND fkc.column_id = fk_col.column_id
                                                      INNER JOIN systab AS fk_coltab
                                                              ON fk_col.table_id = fk_coltab.table_id,
                                                      systabcol AS pk_col
                                                      INNER JOIN systab AS pk_coltab
                                                              ON pk_col.table_id = pk_coltab.table_id
                                                WHERE fkc.table_id = fk.foreign_table_id
                                                  AND fkc.index_id = fk.foreign_index_id
                                                  AND pk_col.table_id = fk.primary_table_id
                                                  AND pk_col.column_id = fkc.primary_column_id), 'OR ')
                           FROM sysfkey AS fk
                                INNER JOIN systab AS fk_tab 
                                        ON fk_tab.table_id = fk.foreign_table_id
                                INNER JOIN systab AS pk_tab 
                                        ON pk_tab.table_id = fk.primary_table_id
                                INNER JOIN SYSIDX AS ix 
                                        ON ix.table_id = fk.foreign_table_id 
                                       AND ix.index_id = fk.foreign_index_id
                          WHERE pk_tab.table_name = sysfk.primary_tname
                            AND fk_tab.table_name = sysfk.foreign_tname ) ), 
                ts.recursive_level + 1 AS recursive_level
           FROM sysforeignkeys AS sysfk
                INNER JOIN table_structure AS ts
                        ON String(sysfk.primary_creator, '.' , sysfk.primary_tname) = ts.child_table_name
                       AND sysfk.primary_tname <> sysfk.foreign_tname
                       AND CharIndex(Lower(String(sysfk.foreign_creator, '.', sysfk.foreign_tname, ' ')), ts.join_clause) = 0
                       AND ts.recursive_level < 20           
       )
  SELECT DISTINCT recursive_level, child_table_name, String('DELETE FROM ', child_table_name, ' FROM ', join_clause, ' ', 'WHERE MyTableName.Id = 1') AS sql_delete
    FROM table_structure
   WHERE child_table_name = 'MyOwner.MyTableNameWithInvalidResultInOutput'
   ORDER BY recursive_level desc, child_table_name;

If I run this, I get 20 rows.
The first row contains a bunch of invalid random characters (for eaxample €5d´\x01x\x04t\x04ˆµ• and €ÿÿÿÿ€&d) in the sql_delete column.
Each time I run it, the invalid characters will come back at the same place at the same row. But the invalid characters and the length of them are different each time.

If I try to copy the row from the result pane, it will only copy the data to the last position before the invalid characters.
If I uncomment the line with "char(13), char(10)," the result now no longer contains the line with the invalid characters and only 19 rows are returned (which is what I expected in the first place).

I have not been able to reproduce this without our database structure (which for obvious reasons I won't post here).
I don't have access to the SAP launchpad to report an incident at the moment.
If someone from SAP is interested, please send me a pm at [first letter first name]dot[last name]atBCSdotNL and I can send you a reload script with which you can reproduce this.

[justin_willey]

I remember something a bit similar where random data from memory was being inserted into the first row of a result set where the system was having to try to work out the data type of the result set from the data, and the first row contained nulls. It was reproduceable in a similar way to what you describe.

The work around in our case was to either a) (fudge) ensure there were no nulls in the first line of the result set or b) (better) explicitly cast each item in the select set to a specific data type

This may, of course, be something completely different 🙂

[VolkerBarth]

Hm, I do have had issues with unions and recursive CTEs with truncated data because the optimizer chose too short data types based on the result set of the first query block – but I do not remember cases with random data...

But I guess it is generally helpful to cast the columns of the first query block to the desired data type when using unions and the like...