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
`When 2 tables joined on INT and LONG columns error shown that there is join column mismatch
select x, y from long_sequence(100) ls join ( select x ,y from long_sequence(100) ) as ls2 on CAST(ls.x as int)= ls2.y`
`When 2 tables joined on INT and LONG columns error shown that there is join column mismatch
select x, y from long_sequence(100) ls join ( select x ,y from long_sequence(100) ) as ls2 on CAST(ls.x as int)= ls2.y`
Will this resolve the issue?
@zhangliyuSustech I don't think someone is working on this one, so your contribution is highly welcome. 👍
I think this issue is beyond my ability, but I will continuously pay attention to it and discuss with my classmates for solution.
Hi, what's the status of this issue? I'd like to work on it if it's still open.
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!!
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]
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.
Based on your description, it sounds like you expect the join to work by casting the INT column to a LONG column and then performing the join. However, it appears that this is not currently possible in QuestDB.
One potential workaround for this issue could be to cast both columns to a common data type that is compatible with both INT and LONG. For example, you could cast both columns to a DOUBLE or a DECIMAL type before joining them. This should allow the join to work as expected.
Another option could be to modify the data types of the columns in one or both tables so that they match. For example, you could change the INT column to a LONG column, or vice versa.
I Want to work on this assign me this issue please.
Hi, I want to contribute to this issue. In this case, you are casting the "x" column of the "ls" table to "int" in the subquery, but you are still joining it with the "x" column of the "ls" table, which is of type "long". To fix this, you can change the alias of the "y" column in the subquery to "x" so that it matches the column name of the "ls" table and then cast the "x" column of the "ls" table to "long" in the join condition. Please Assign me this issue.
This query will return all pairs of values (x, y) where x is a number from 1 to 100 and y is the integer representation of x. The result set will contain 100 rows with x and y values ranging from 1 to 100.
