Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-27764 Feature Parity between PostgreSQL and Spark
  3. SPARK-29980

Whitespaces handling for Cast and BinaryOperation between StringType and NumericTypes

    XMLWordPrintableJSON

Details

    • Sub-task
    • Status: Resolved
    • Major
    • Resolution: Duplicate
    • 3.0.0
    • None
    • SQL
    • None

    Description

      Here is a case, let see how it goes in different SQL engines.

      select cast('1 ' as int) as v1, '1 ' = 1 as v2
      

      spark 1.6

      NULL	true
      

      spark 2.1

      NULL	true
      

      spark 2.2

      NULL	NULL
      

      spark 2.3

      NULL	NULL
      

      spark 2.4

      NULL	NULL
      

      hive

      NULL	true
      

      PostgreSQL

      postgres=# select cast('1 ' as int) as v1, '1 ' = 1 as v2;
       v1 | v2
      ----+----
        1 | t
      (1 row)
      

      presto

      presto> select cast('1 ' as int) as v1, '1 ' = 1 as v2;
      Query 20191120_060530_00002_f5kcs failed: line 1:38: '=' cannot be applied to varchar(2), integer
      select cast('1 ' as int) as v1, '1 ' = 1 as v2
      
      presto> select cast('1 ' as int) as v1, '1 ' = '1 ' as v2;
      Query 20191120_060545_00003_f5kcs failed: Cannot cast '1 ' to INT
      

      Our behavior is unstable because type coercion changed since 2.2.
      Personally, I think What PostgreSQL and Presto does here is more reasonable and consistent

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              Qin Yao Kent Yao 2
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: