[SPARK-29573] Spark should work as PostgreSQL when using + Operator

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 3.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: ABHISHEK KUMAR GUPTA
• Created: 23/Oct/19 14:51
• Updated: 12/Dec/22 18:10
• Resolved: 01/Dec/19 21:55

Description

Spark and PostgreSQL result is different when concatenating as below :

Spark : Giving NULL result 
0: jdbc:hive2://10.18.19.208:23040/default> select * from emp12;
+-----+---------+
| id | name |
+-----+---------+
| 20 | test |
| 10 | number |
+-----+---------+
2 rows selected (3.683 seconds)
0: jdbc:hive2://10.18.19.208:23040/default> select id as ID, id+name as address from emp12;
+-----+----------+
| ID | address |
+-----+----------+
| 20 | NULL |
| 10 | NULL |
+-----+----------+
2 rows selected (0.649 seconds)
0: jdbc:hive2://10.18.19.208:23040/default> select id as ID, id+name as address from emp12;
+-----+----------+
| ID | address |
+-----+----------+
| 20 | NULL |
| 10 | NULL |
+-----+----------+
2 rows selected (0.406 seconds)
0: jdbc:hive2://10.18.19.208:23040/default> select id as ID, id+','+name as address from emp12;
+-----+----------+
| ID | address |
+-----+----------+
| 20 | NULL |
| 10 | NULL |
+-----+----------+
PostgreSQL: Saying throwing Error saying not supported
create table emp12(id int,name varchar(255));
insert into emp12 values(10,'number');
insert into emp12 values(20,'test');
select id as ID, id+','+name as address from emp12;

output: invalid input syntax for integer: ","

create table emp12(id int,name varchar(255));
insert into emp12 values(10,'number');
insert into emp12 values(20,'test');
select id as ID, id+name as address from emp12;

Output: 42883: operator does not exist: integer + character varying