search

zettadb / kunlun

KunlunBase is a distributed relational database management system(RDBMS) with complete NewSQL capabilities and robust transaction ACID guarantees and is compatible with standard SQL. Applications which used PostgreSQL or MySQL can work with KunlunBase as-is without any code change or rebuild because KunlunBase supports both PostgreSQL and MySQL connection protocols and DML SQL grammars. MySQL DBAs can quickly work on a KunlunBase cluster because we use MySQL as storage nodes of KunlunBase. KunlunBase can elastically scale out as needed, and guarantees transaction ACID under error conditions, and KunlunBase fully passes TPC-C, TPC-H and TPC-DS test suites, so it not only support OLTP workloads but also OLAP workloads. Application developers can use KunlunBase to build IT systems that handles terabytes of data, without any effort on their part to implement data sharding, distributed transaction processing, distributed query processing, crash safety, high availability, strong consistency, horizontal scalability. All these powerful features are provided by KunlunBase. KunlunBase supports powerful and user friendly cluster management, monitor and provision features, can be readily used as DBaaS.
http://www.kunlunbase.com
Apache License 2.0
143 stars 20 forks source link

Incorrect float comparison result #468

Open jd-zhang opened 3 years ago

jd-zhang commented 3 years ago

Issue migrated from trac ticket # 94

component: computing nodes | priority: major

2021-06-11 11:54:43: @jd-zhang created the issue

sql code:

drop table if exists FLOAT4_TBL; CREATE TABLE FLOAT4_TBL (f1 float4); INSERT INTO FLOAT4_TBL(f1) VALUES (' 0.0'); INSERT INTO FLOAT4_TBL(f1) VALUES ('1004.30 '); INSERT INTO FLOAT4_TBL(f1) VALUES (' -34.84 '); INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e+20'); INSERT INTO FLOAT4_TBL(f1) VALUES ('1.2345678901234e-20');

For kunlun: regress=# select f1 from float4_tbl; 0 1004.3 -34.84 1.23457e+20 1.23457e-20 (5 rows)

regress=# SELECT '' AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = 1004.3; (0 rows)

regress=# SELECT '' AS four, f.* FROM FLOAT4_TBL f WHERE f.f1 <> '1004.3'; | 0 | 1004.3 | -34.84 | 1.23457e+20 | 1.23457e-20

The result is incorrect, since 1004.3 should match the = query, but not the <> query.

jd-zhang commented 3 years ago

2021-06-11 11:57:42: @jd-zhang commented

The other issue is for division/0.0:

SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f; bad ?column?

It just returns empty result, but for official pg, it gives error: ERROR: division by zero

jd-zhang commented 3 years ago

2021-10-11 14:17:07: @david-zhao

jd-zhang commented 3 years ago

2021-10-11 14:17:07: @david-zhao changed priority from minor to major

jd-zhang commented 3 years ago

2021-10-11 14:17:07: @david-zhao changed severity from minor to normal

jd-zhang commented 3 years ago

2021-10-26 17:51:31: @david-zhao commented

fixed in revision 8ed766394f0e2189cebccc69d2762fda8d3118b7

jd-zhang commented 3 years ago

2021-10-26 17:51:31: @david-zhao changed status from assigned to accepted

jd-zhang commented 3 years ago

2021-10-26 17:52:18: @david-zhao commented

Replying to [comment:1 winter]: This is how mysql handles division by 0 error --- the result is NULL. we have to follow mysql's convention.

The other issue is for division/0.0:

SELECT '' AS bad, f.f1 / '0.0' from FLOAT4_TBL f; bad ?column?

It just returns empty result, but for official pg, it gives error: ERROR: division by zero

jd-zhang commented 2 years ago

2021-12-21 11:28:58: @david-zhao commented

numeric VS float4/8 comparison in pg is counter intuition and counter commonsense, because the 

SELECT AS one, f.* FROM FLOAT4_TBL f WHERE f.f1 = 1004.3;

query is expected to return no rows. needs further investigation.

jd-zhang commented 2 years ago

2021-12-21 11:28:58: @david-zhao