wangyum commented on a change in pull request #24862: [SPARK-28038][SQL][TEST] Port text.sql URL: https://github.com/apache/spark/pull/24862#discussion_r308510669
########## File path: sql/core/src/test/resources/sql-tests/inputs/pgSQL/text.sql ########## @@ -0,0 +1,134 @@ +-- +-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group +-- +-- +-- TEXT +-- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/text.sql + +SELECT string('this is a text string') = string('this is a text string') AS true; + +SELECT string('this is a text string') = string('this is a text strin') AS `false`; + +CREATE TABLE TEXT_TBL (f1 string) USING parquet; + +INSERT INTO TEXT_TBL VALUES ('doh!'); +INSERT INTO TEXT_TBL VALUES ('hi de ho neighbor'); + +SELECT '' AS two, * FROM TEXT_TBL; + +-- As of 8.3 we have removed most implicit casts to text, so that for example +-- this no longer works: +-- Spark SQL implicit cast integer to string +select length(42); + +-- But as a special exception for usability's sake, we still allow implicit +-- casting to text in concatenations, so long as the other input is text or +-- an unknown literal. So these work: +-- [SPARK-28033] String concatenation low priority than other arithmeticBinary +select string('four: ') || 2+2; +select string('four: ') || 2+2; + +-- but not this: +-- Spark SQL implicit cast both side to string +select 3 || 4.0; + +/* + * various string functions + */ +select concat('one'); +-- Spark SQL does not support YYYYMMDD, we replace it to yyyyMMdd +select concat(1,2,3,'hello',true, false, to_date('20100309','yyyyMMdd')); +select concat_ws('#','one'); +select concat_ws('#',1,2,3,'hello',true, false, to_date('20100309','yyyyMMdd')); +select concat_ws(',',10,20,null,30); +select concat_ws('',10,20,null,30); +select concat_ws(NULL,10,20,null,30) is null; +select reverse('abcde'); +-- [SPARK-28036] Built-in udf left/right has inconsistent behavior +-- select i, left('ahoj', i), right('ahoj', i) from range(-5, 5) t(i) order by i; Review comment: Because of ANSI mode: ```sql spark-sql> select left('12345', 2); 12 spark-sql> set spark.sql.parser.ansi.enabled=true; spark.sql.parser.ansi.enabled true spark-sql> select left('12345', 2); Error in query: no viable alternative at input 'left'(line 1, pos 7) == SQL == select left('12345', 2) -------^^^ ``` https://issues.apache.org/jira/browse/SPARK-28479 The output if disable ANSI mode: **Spark SQL**: ```sql spark-sql> select i, left('ahoj', i), right('ahoj', i) from range(-5, 6) t(i) order by i; -5 -4 -3 -2 -1 0 1 a j 2 ah oj 3 aho hoj 4 ahoj ahoj 5 ahoj ahoj ``` **PostgreSQL**: ```sql postgres=# select i, left('ahoj', i), right('ahoj', i) from generate_series(-5, 5) t(i) order by i; i | left | right ----+------+------- -5 | | -4 | | -3 | a | j -2 | ah | oj -1 | aho | hoj 0 | | 1 | a | j 2 | ah | oj 3 | aho | hoj 4 | ahoj | ahoj 5 | ahoj | ahoj (11 rows) ``` ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
