[SPARK-31508] string type compare with numberic cause data inaccurate

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 2.4.5
• Component/s: SQL

Environment

hadoop2.7

spark2.4.5

• Assignee: Unassigned
• Reporter: philipse
• Created: 21/Apr/20 10:56
• Updated: 23/Apr/20 04:34
• Resolved: 23/Apr/20 04:34

Description

Hi all

 

Sparksql may should convert values to double if string type compare with number type.the cases shows as below
1, create table
create table test1(id string);
 
2,insert data into table
insert into test1 select 'avc';
insert into test1 select '2';
insert into test1 select '0a';
insert into test1 select '1111111111111111';
insert into test1 select '22222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222';
3.Let's check what's happening
select * from test_gf13871.test1 where id > 0
the results shows below
2
11111111
Really amazing,the big number 222...cannot be selected.
while when i check in hive,the 222...shows normal.
4.try to explain the command,we may know what happened,if the data is big enough than max_int_value，it will not selected,we may need to convert to double instand.

I wanna know if we have fixed or planned in 3.0 or later version.,please feel free to give any advice,
 
Many Thanks

Issue Links

• duplicates: SPARK-30471 Fix issue when compare string and IntegerType
• links to: 【31508】