Re: [I] sql result discrepency with sqlite, postgres and duckdb [datafusion]
Omega359 commented on issue #13780: URL: https://github.com/apache/datafusion/issues/13780#issuecomment-2585831382 Resolved as fixed with change 'as REAL' to 'AS FLOAT8' in sqlite test files -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
Re: [I] sql result discrepency with sqlite, postgres and duckdb [datafusion]
Omega359 closed issue #13780: sql result discrepency with sqlite, postgres and duckdb URL: https://github.com/apache/datafusion/issues/13780 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
Re: [I] sql result discrepency with sqlite, postgres and duckdb [datafusion]
Omega359 commented on issue #13780: URL: https://github.com/apache/datafusion/issues/13780#issuecomment-2585311683 I went with using FLOAT8 vs DOUBLE as double isn't a valid type name in Postgresql and it interfered with the result comparisons. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
Re: [I] sql result discrepency with sqlite, postgres and duckdb [datafusion]
alamb commented on issue #13780: URL: https://github.com/apache/datafusion/issues/13780#issuecomment-2575115298 First of all, very nice 🕵️ work ! > 'AS REAL' -> 'AS DOUBLE' > > This would better match the actual types being tested and would fix many of the failing results. Along with correcting the nullif type behavior would fix almost all the remaining tests that have result mismatches with sqlite/postgresql. As I understand, this means using f64 for floating point operations in the test. This sounds like a very good and pragmatic thing to do in my mind The other potential thing to do might simply be to map `AS REAL` to use `DataType::Float64` but that is a larger and much more potentially disruptive change Thanks agian @Omega359 -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
Re: [I] sql result discrepency with sqlite, postgres and duckdb [datafusion]
Omega359 commented on issue #13780: URL: https://github.com/apache/datafusion/issues/13780#issuecomment-2573650403 Addendum: Since the sqlite tests come from sqlite (duh) where REAL is mapped to 8 bytes (Double/f64) I would like to propose that I update the sqlite .slt files and change: 'AS REAL' -> 'AS DOUBLE' This would better match the actual types being tested and would fix many of the failing results. Along with correcting the nullif type behavior would fix almost all the remaining tests that have result mismatches with sqlite/postgresql. Thoughts? @alamb, @aweltsch, @2010YOUY01, @jayzhan-synnada -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
Re: [I] sql result discrepency with sqlite, postgres and duckdb [datafusion]
Omega359 commented on issue #13780: URL: https://github.com/apache/datafusion/issues/13780#issuecomment-2573323646 I've spent a few hours trying to determine where DataFusion was going wrong with the math and finally came up with a condensed test case. The math is fine ... as far as Rust is concerned. You can see a boiled down test case I wrote up on the [rust playground](https://play.rust-lang.org/?version=nightly&mode=debug&edition=2021&gist=835b3d7bc195114a70d490f0d8973354). The core issue is that the result *cannot* be represented as a f32 which is what the `REAL` type maps to. So technically the result that DataFusion is returning is correct. Sqlite maps real to 8 bytes. Postgresql maps real to 4 bytes ... but for the query in query it uses an int type (proper handling of nullif types as opposed to DataFusion's incorrect type selection). Even if you force part of the query to be a real type it still uses int: `SELECT - 15.::float4 * 16860600 + 31 AS col0;` => -252908969 `SELECT - 15.0001::float4 * 16860600 + 31 AS col0;` => -252910657.34972382 Duckdb maps real to 4 bytes. If you force the type to be float4 it returns the same result as DataFusion: ```sql D SELECT - 15.::float4 * 16860600 + 31 AS col0; ┌──┐ │ col0 │ │float │ ├──┤ │ -252908960.0 │ └──┘ ``` So I think for these queries the resolution here should be that DataFusion should fix the typing of nullif .. and then update the results in the slt test if the type maps to f32. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
Re: [I] sql result discrepency with sqlite, postgres and duckdb [datafusion]
Omega359 commented on issue #13780: URL: https://github.com/apache/datafusion/issues/13780#issuecomment-2571776995 The core issue is something related to the cast to the `REAL` type. Changing the sql to cast to DOUBLE results in the correct results: ```sql > SELECT - NULLIF ( + 15, - 27 + + CAST ( NULL AS DOUBLE ) + + + MIN ( 35 ) * - COUNT ( * ) * - 14 + 64 ) * 87 * + 75 * 34 * + 76 + - - 31 AS col0, - 10 * - + 33 * ( + - NULLIF ( 21, + 70 * + 71 ) ) * - 15; +--+---+ | col0 | Int64(-10) * (- Int64(33)) * (- nullif(Int64(21),Int64(70) * Int64(71))) * Int64(-15) | +--+---+ | -252908969.0 | 103950 | +--+---+ ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
