Search code examples
sqlalibaba-cloud

How to Join (equal) two data columns that belongs to the String and Double types respectively in Alibaba MaxCompute?

I am not authorized to share the table details.

For instance, let me consider an Example:

I am trying to join two columns of String and Double data types respectively in Alibaba MaxCompute.

In the earlier version of MaxCompute, the String and Double data types are converted to the bigint data type at the cost of precision. 1.1 = “1” in a Join condition.

Whereas the same code does not work in the new version of the MaxCompute. The code syntax is like follows:

SELECT * FROM t1 JOIN t2 ON t1.double_value = t2.string_value;

Error:

WARNING:[1,48]  implicit conversion from STRING to DOUBLE, potential data loss, use CAST function to suppress

What is the correct syntax to do the join operation in Alibaba MaxCompute V2?

Solution

  • I did a bit of digging and it seems like this SQL command is the recommended way of getting around this issue. 

    select * from t1 join t2 on t.double_value = cast(t2.string_value as double);