FLOAT/Integer gives FLOAT, when it should be a Double about duckdb HOT 14 CLOSED

C, Java, and C# all behave like duckdb.
Python doesn't support single precision floats, but numpy does and behaves like duckdb.

MySQL, MSSQL, Oracle, PostgreSQL all behave like duckdb.

SQLite doesn't offer single precision floats.

from duckdb.

I have tested Oracle

SELECT 345349 * POWER((1+Cast(7 AS FLOAT)/100/12),120) qty2010 from dual;

/*
┌───────────────┐
│ QTY2010       │
├───────────────┤
│ 694,034.54678 │
└───────────────┘
*/

I have tested H2:

SELECT 345349 * POWER((1+Cast(7 AS FLOAT)/100/12),120) qty2010;

/*
┌───────────────┐
│ QTY2010       │
├───────────────┤
│ 694,034.54678 │
└───────────────┘
*/

I have tested MS SQL Sever: https://sqlfiddle.com/sql-server/online-compiler?id=cebe35c8-a664-4e14-a7de-42889a348798

SELECT 345349 * POWER((1+Cast(7 AS FLOAT)/100/12),120) qty2010;

/*
qty2010
694034.54678046005
*/

from duckdb.

The equivalents of duckdb's REAL and DOUBLE in Oracle DB are BINARY_FLOAT and BINARY_DOUBLE, see https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html

from duckdb.

The equivalents of duckdb's REAL and DOUBLE in H2 are REAL(=FLOAT(24)) and DOUBLE PRECISION(=FLOAT(53)), see https://www.h2database.com/html/datatypes.html#double_precision_type

from duckdb.

The equivalents of duckdb's REAL and DOUBLE in MSSQL are REAL(=FLOAT(24)) and DOUBLE PRECISION(=FLOAT(53)=FLOAT), see https://learn.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql?view=sql-server-ver16

from duckdb.

The equivalents of duckdb's REAL and DOUBLE in PostgreSQL are REAL(=FLOAT(24)=FLOAT4) and DOUBLE PRECISION(=FLOAT(53)=FLOAT=FLOAT8), see
https://www.postgresql.org/docs/current/datatype-numeric.html

from duckdb.

What's uncommon is therefore not promotion/upcasting, but that duckdb resolves the ambiguity allowed by the SQL standard by letting FLOAT be a 32 bit floating point type (like most programming languages in the C family and like Clickhouse and FirebirdSQL), whereas many other SQL dialects let it be a 64 bit floating point type. (And Oracle is apparently the odd one out, doing the crazy thing of letting it be a fixed point decimal data type)

If you don't like that, best to be explicit and write FLOAT4 or FLOAT8 always.

from duckdb.

Thank you very much, I get your explanation.
I still think, the FLOAT = FLOAT4 instead of FLOAT = FLOAT8 confusing (at least for anyone coming from traditional databases).

1. we are not aiming for embedded (where 4 bytes matter), are we?
2. we are aiming for Oracle and BigQuery and MS SQL Server (not for MySQL), are we?
3. what exactly would be lost when aliasing FLOAT8 as FLOAT (instead of FLOAT4)

Of course I understand, that all of this is no issue when starting from scratch on DuckDB and casting to FLOAT8 explicitly.

But what about "migration from and compatibility with existing RDBMS/Applications"?

Maybe a special Web Page on those topics would help also.

from duckdb.

• If you are coming from "traditional databases", wouldn't you have expected FLOAT = DOUBLE PRECISION? Your OP suggests you expected FLOAT!=DOUBLE.
• As I've written above, Oracle doesn't define FLOAT the same way as MS SQL, so what should duckdb do if it were "aiming for traditional databases"?
• I agree a "Important differences to other databases" page in the documentation could be useful. Personally, I haven't used other databases but have used all the regular programming languages mentioned above, so I for one am happy with the current definitions in duckdb. Maybe you can start with adding a warning that could have helped you to https://duckdb.org/docs/sql/data_types/numeric.html (which you can edit yourself, see link at bottom)?
• 32 bits can be very useful if you have lots of data and not as much RAM. Also, 32 bits can be faster even if you have infinite memory; many things are RAM throughout limited, and having the amount of data that needs to be moved from RAM to CPU can help.
• Personally, I would be fine if FLOAT were FLOAT8, but changing the meaning of something as fundamental asFLOAT can be difficult / scare off users, while writing DOUBLE is easy.

from duckdb.

• If you are coming from "traditional databases", wouldn't you have expected FLOAT = DOUBLE PRECISION? Your OP suggests you expected FLOAT!=DOUBLE.

No, I would have wanted a DOUBLE PRECISION based calculation.

* As I've written above, Oracle doesn't define `FLOAT` the same way as MS SQL, so what should duckdb do if it were "aiming for traditional databases"?

I get that, but Oracle's FLOAT is closer to DOUBLE PRECISION regarding the calculation.

* I agree a "Important differences to other databases" page in the documentation could be useful. Personally, I haven't used other databases but have used all the regular programming languages mentioned above, so I for one am happy with the current definitions in duckdb. Maybe you can start with adding a warning that could have helped you to https://duckdb.org/docs/sql/data_types/numeric.html (which you can edit yourself, see link at bottom)?

Ok, will do so and we can close this issue.

Again, thank you for explanation and time. I do appreciate!

from duckdb.

@soerenwolfers, please pardon me for asking further:

select typeOf(1/10)
union all
select typeOf(1.0::float/10)

/*
┌────────┐
│ typeof │
├────────┤
│ DOUBLE │
├────────┤
│ FLOAT  │
└────────┘
*/

Why is Integer upcast to Double, but Float is not?
Allow me to explain my thought. We are dealing with 2 different questions here: storing data and calculating with data. It makes totally sense to store FLOAT4. But why would you ever want to calculate with FLOAT4 instead of FLOAT8? Depending on your answer the above example should either return INT FLOAT or DOUBLE DOUBLE.

I think it should be DOUBLE DOUBLE because we can always round down when storing the data in a FLOAT column.

from duckdb.

select 1/3 - Cast(1 AS float)/3;
-- MS SQLServer: -0.3
-- H2: -0.3
-- Postgres: -0.3

select 1/3 - Cast(1 AS binary_float)/3  from dual;
-- Oracle: 0.00 sharp (because DOUBLE DOUBLE)

select printf('%15.14f', 1/3 - Cast(1 AS float)/3 );
-- DuckDB: -0.00000000993411 (because DOUBLE FLOAT)

I do understand the first team and I understand Oracle. DuckDB puzzles me.

from duckdb.

You'd want to calculate with float4 because it's faster.

float / int is float because that requires only one cast. It'd be pretty strange if 3f / 2 returned a different answer than 3f / 2f.

int / int could have been double or float.

Personally, I'd prefer if both float / int and int / int were errors and you'd need to explicitly cast or use integer division (//), but a lot of people would disagree.

The only difference between duckdb and PostgreSQL+MSSQL in your last snippet is that duckdb uses // for integer division.

from duckdb.

You have helped me a lot. I am simply rewriting every explicit and implicit cast of a REAL or FLOAT into a FLOAT8 before executing in DuckDB. This solves all my problems (on math functions).

Thank you again and have a nice weekend.

from duckdb.