Comments (14)
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).
- we are not aiming for embedded (where 4 bytes matter), are we?
- we are aiming for Oracle and BigQuery and MS SQL Server (not for MySQL), are we?
- 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 expectedFLOAT
!=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
wereFLOAT8
, but changing the meaning of something as fundamental asFLOAT
can be difficult / scare off users, while writingDOUBLE
is easy.
from duckdb.
- If you are coming from "traditional databases", wouldn't you have expected
FLOAT
=DOUBLE PRECISION
? Your OP suggests you expectedFLOAT
!=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.
Related Issues (20)
- Dependency Error HOT 4
- UNPIVOT statement is extremely slow when working with many columns HOT 1
- information_schema.tables.is_insertable_into is always true for all tables returned by duckdb_tables() HOT 1
- Variables can still 'leak' through the stack in Python HOT 1
- Cannot make Concurrency work - One read and write process with multiple read only process HOT 4
- duckdb.duckdb.InvalidInputException when set s3_* configs HOT 1
- Invalid BM25 scores when building index with multiple fields HOT 8
- Positional parameters in prepared statement does not adjust positional orders in "FROM first" syntax
- Window range ending in `0 preceding` invalid result HOT 2
- Query with `row_number()` function hangs HOT 7
- .tables doesn't include catalog/database and schema name
- Catalog Errors only offer one suggestion when multiple suggestions are returned with the same score
- array_agg with ORDER BY does not respect default_collation HOT 2
- Equivalent MACRO definitions yield different results HOT 2
- DROP TABLE IF EXISTS fails with an existing VIEW HOT 2
- Newly imported tables not showing HOT 3
- Date Difference Discrepancy in DuckDB HOT 2
- Change SQL autocompletion function name into a pragma rather than sql_auto_complete
- Using random() in an uncorrelated subquery replicates the result HOT 4
- `USE` does not affect for the table referenced after the keyword ON for the `CREATE UNIQUE INDEX ... ON ...` HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from duckdb.