Casting VARCHAR => DOUBLE values fails

[Robert-M-Muench]

What happens?

My column decimal-string is of type VARCHAR(0) and I get:

1. select "decimal-string" from data gives 119102.8373515950
2. select "decimal-string"::DECIMAL from data gives Conversion Error: Could not convert string "" to DECIMAL(18,3)
3. select '119102.8373515950'::DECIMAL AS result_col from data works

Why does the 3. query work, while 2. fails?

How can I alter the column type from VARCHAR to DOUBLE while getting all decimal strings converted?

To Reproduce

Try the statements in a DuckDB shell.

OS:

macos

DuckDB Version:

0.9.2

DuckDB Client:

JetBrains DataGrip 2023.3.4

Full Name:

Robert M. Münch

Affiliation:

-

Have you tried this on the latest nightly build?

I have tested with a release build (and could not test with a nightly build)

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• [X] Yes, I have

[szarnyasg]

Hi @Robert-M-Muench, thanks for the issue. Unfortunately, I was not able to reproduce it using data produced with these instructions:

create table data ("decimal-string" VARCHAR(0));
insert into data select '119102.8373515950';

Can you please provide SQL instructions or a CSV/Parquet file to reproduce the data set?

[Robert-M-Muench]

I want to select all values from a VARCHAR column and get them back as DOUBLE so that I can use functions like 'abs()'.

The main problem seems to be that the conversion is not very fault-tolerant. The query returns an error if any value is a non-decimal string or empty.

Can the result be typecast, if possible, and returned empty/NaN in case of any problems?

[Tishj]

Yes try TRY_CAST('decimal-string', DECIMAL) https://duckdb.org/docs/sql/expressions/cast