|
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. 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. I have not been able to reproduce this without our database structure (which for obvious reasons I won't post here). |
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 04 Jan '21, 09:25
question was seen: 731 times
last updated: 04 Jan '21, 13:23
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 :)
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...