Failed to project when items contain expr and Cast(expr) #4478

Activity

1. ygf11

   added

   bugSomething isn't workingSomething isn't working

   on Dec 2, 2022
2. ygf11

   changed the title [-]Failed to project when projection contains expr and Cast(expr)[/-] [+]Failed to project when items contain expr and Cast(expr)[/+] on Dec 2, 2022
3. HaoYang670 commented on Dec 2, 2022

   

   HaoYang670

   on Dec 2, 2022

   Contributor

   More actions

   This is because the cast expression is ignored in the column name, which is an expected behaviour, I guess.
4. ygf11 commented on Dec 2, 2022

   

   ygf11

   on Dec 2, 2022

   Last edited by ygf11

   ContributorAuthor

   More actions

   Yes, cast expression is ignored in the column name.

   I think the other reason is our projection does not support duplicated items which seems PostgreSQL and spark both support.

   spark

   spark-sql> select a,a,a from test0;
   a	a	a
   1	1	1
   Time taken: 0.134 seconds, Fetched 1 row(s)

   PostgreSQL

   > select c0,c0,c0,c0 from test0 limit 3;
     c0   |  c0   |  c0   |  c0
   -------+-------+-------+-------
          |       |       |
    19470 | 19470 | 19470 | 19470
    22111 | 22111 | 22111 | 22111
   (3 rows)
   (1 row)

   datafusion

   ❯ select c0, c0, c0 from test0;
   SchemaError(FieldNotFound { field: Column { relation: None, name: "c0" }, valid_fields: Some([Column { relation: Some("test0"), name: "a" }, Column { relation: Some("test0"), name: "b" }, Column { relation: Some("test0"), name: "c" }]) })
5. mingmwang commented on Dec 7, 2022

   

   mingmwang

   on Dec 7, 2022

   Contributor

   More actions

   This is because the cast expression is ignored in the column name, which is an expected behaviour, I guess.

   Why it is the expected behavior ?
6. mingmwang commented on Dec 7, 2022

   

   mingmwang

   on Dec 7, 2022

   Contributor

   More actions

   I think SparkSQL does not have a consistent behavior.
   See example below:

   SparkSQL 3.x Thrift Server JDBC

   CREATE TEMPORARY TABLE t1 (t1_id INT,t1_name String);
   select t1_id, cast(t1_id as decimal(10, 0)), t1_id + cast(t1_id as decimal(10, 0)) from t1;
   

   Result:

   +-----+-----+-------------------------------------------------------------------+
   | t1_id | t1_id | (CAST(t1_id AS DECIMAL(10,0)) + CAST(t1_id AS DECIMAL(10,0))) |
   +-----+-----+-------------------------------------------------------------------+

   +-----+-----+-------------------------------------------------------------------+