Filter operation (GT AND LT) on Float type result error

[loloxwg]

Describe the bug

SQL Query: Drone Information Table Operations

Create Table

CREATE TABLE droneinfo_test_where (ts timestamp, signal_strength INT, speed FLOAT, calibration_method STRING);

Insert Data

INSERT INTO droneinfo_test_where VALUES(NOW(), 80, 500.2, 'method2'); 

Select Query

SELECT * FROM droneinfo_test_where WHERE signal_strength >= 80 AND speed <= 500.2;

Expected Result

+----+-----------------+-------+--------------------+
| ts | signal_strength | speed | calibration_method  |
+----+-----------------+-------+--------------------+
|    |        80       | 500.2 | method2            |
+----+-----------------+-------+--------------------+

This result should match the output in DuckDB.

To Reproduce

run datadusion-cli
input
CREATE TABLE droneinfo_test_where (ts timestamp, signal_strength INT, speed FLOAT, calibration_method STRING);

INSERT INTO droneinfo_test_where VALUES(NOW(),80,500.2,'method2');

SELECT * FROM droneinfo_test_where WHERE signal_strength >= 80 AND speed <= 500.2;

Expected behavior

Same sql excute on duckdb

Additional context

No response

[loloxwg]

[buraksenn]

I'm not sure about the internals but this is probably about floating precision while casting as far as I can see:

[loloxwg]

It may be that the literal (500.2) should be cast to float32. I think

[JasonLi-cn]

Float is DataType::Float32 in datafusion, and 500.2 resolves to DataType::Float64 at the parser stage. Therefore, the speed field is casted to DataType::Float64 when the query plan is generated.

datafusion/datafusion/sql/src/planner.rs

Line 451 in 3f3a0cf

SQLDataType::Float(_) => Ok(DataType::Float32),

datafusion/datafusion/sql/src/expr/value.rs

Lines 89 to 92 in 3f3a0cf

	signed_number.parse::<f64>().map(lit).map_err(|_| {
	DataFusionError::from(ParserError(format!(
	"Cannot parse {signed_number} as f64"
	)))

Try:

SELECT * FROM droneinfo_test_where WHERE signal_strength >= 80 AND speed <= 500.2::float;

DataFusion CLI v42.0.0
> CREATE TABLE droneinfo_test_where (ts timestamp, signal_strength INT, speed FLOAT, calibration_method STRING);


0 row(s) fetched.
Elapsed 0.021 seconds.

> INSERT INTO droneinfo_test_where VALUES(NOW(),80,500.2,'method2');
+-------+
| count |
+-------+
| 1     |
+-------+
1 row(s) fetched.
Elapsed 0.020 seconds.

> SELECT * FROM droneinfo_test_where WHERE signal_strength >= 80 AND speed <= 500.2;


+----+-----------------+-------+--------------------+
| ts | signal_strength | speed | calibration_method |
+----+-----------------+-------+--------------------+
+----+-----------------+-------+--------------------+
0 row(s) fetched.
Elapsed 0.013 seconds.

> SELECT * FROM droneinfo_test_where WHERE signal_strength >= 80 AND speed <= 500.2::float;
+----------------------------+-----------------+-------+--------------------+
| ts                         | signal_strength | speed | calibration_method |
+----------------------------+-----------------+-------+--------------------+
| 2024-10-24T02:57:17.767451 | 80              | 500.2 | method2            |
+----------------------------+-----------------+-------+--------------------+
1 row(s) fetched.
Elapsed 0.009 seconds.

[JasonLi-cn]

take

[andygrove]

Also, worth noting that the parser is incorrect (at least not ANSI SQL compliant) when parsing numeric literals. It should be parsing them as Decimal rather than Float64.

#12817