Polybase - fix file format type default for decimals
Currently it's not possible to load a file with empty decimal value and convert it to 0 when loading data.
This will load fine:
This will fail:
According to the documentation( https://msdn.microsoft.com/en-us/library/dn935026.aspx) when USE_TYPE_DEFAULT = TRUE is set in the file format definition empty value should be converted to 0. While it works for Integers, it doesn't work for decimals. USE_TYPE_DEFAULT = FALSE will work fine for decimals (but of course the value will be loaded as NULL).
CREATE EXTERNAL FILE FORMAT text_file_format_raw
FORMAT_TYPE = DELIMITEDTEXT,
STRING_DELIMITER = '0x22',
USE_TYPE_DEFAULT = TRUE
CREATE EXTERNAL TABLE dbo.test_external
LOCATION = 'test.txt',
DATA_SOURCE = azure_storage,
FILE_FORMAT = text_file_format_raw
CREATE TABLE dbo.test
WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = ROUND_ROBIN)
SELECT * FROM dbo.test_external;
Thank you for reporting this issue. We have identified this as a product defect and plan to fix this behavior in an upcoming release. 7297383.
Jamey Johnston commented
still a problem. Any resolution?
Sai Nandam commented
Has this been resolved, I am still seeing this issue.
Any status on this ?