questdb: SQL: Join Column Type Mismatch
Describe the bug
When 2 tables joined on INT and LONG columns error shown that there is join column mismatch
To reproduce
select x, y
from long_sequence(100) ls
join (
select cast(x as int) y from long_sequence(100)
) as ls2
on ls.x = ls2.y
Expected Behavior
INT can be be upcast to LONG and equality should be checked as LONG equality so that above query returns 100 rows
Environment
- **QuestDB version**: 6.1.2
- **OS**: any
- **Browser**: any
Additional context
Comparing mixed data types works for many numeric types in WHERE. Same should be in JOIN condition
About this issue
- Original URL
- State: open
- Created 3 years ago
- Comments: 21 (4 by maintainers)
Commits related to this issue
- #1679 Fits column mismatch worked with Noemi Dellgadillo Roldan and Christopher Judd — committed to VrittiE/questdb by VrittiE a year ago
it seems like there is a type mismatch error when joining two tables on an INT and a LONG column.
The query is attempting to join the “x” column from a table generated using the “long_sequence” function, with the “y” column from a subquery that casts the “x” column to an INT.
The expected behavior is that the INT value in the subquery should be upcast to LONG and compared to the LONG value in the other table. This should result in the query returning 100 rows.
However, the current behavior is that an error is shown indicating a join column mismatch, which suggests that the query is not correctly handling the type conversion.
This issue seems to be specific to the QuestDB database version 6.1.2. It is possible that this issue has been fixed in a later version of QuestDB or may require a different syntax to achieve the desired behavior.
You receive a column mismatch error due to comparing incompatible data types. Upcasting and int to a long can be done using the data types BIGint instead. This would allow enough bytes in this data type to compare to a long. The new code is shown below:
select x, y from long_sequence(100) ls join ( select cast(x as BIGint) y from long_sequence(100) ) as ls2 on ls.x = ls2.y
When I run this code in QuestDB the table displays 100 rows with the same values for both the x and y columns. No mismatch error occurs.
QuestDB version: [10:26:50]
Hi @XinyiQiao , thank you very much for offering help!! another T-shirt in the horizon 😃 You are very welcome to work on the issue, we have assigned it to you. Have a lot of fun!!
I have scheduled this issue, I will try my best to fix it in three weeks.