[SPARK-27931] Accept 'on' and 'off' as input for boolean data type

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.0.0
• Fix Version/s: 3.0.0
• Component/s: SQL
• Assignee: YoungGyu Chun
• Reporter: Yuming Wang
• Created: 03/Jun/19 13:22
• Updated: 29/Dec/19 09:41
• Resolved: 30/Aug/19 21:18

Description

This ticket contains three things:
1. Accept 'on' and 'off' as input for boolean data type

SELECT cast('no' as boolean) AS false;
SELECT cast('off' as boolean) AS false;

2. Accept unique prefixes thereof:

SELECT cast('of' as boolean) AS false;
SELECT cast('fal' as boolean) AS false;

3. Trim the string when cast to boolean type

SELECT cast('    true   ' as boolean) AS true;
SELECT cast('     FALSE' as boolean) AS true;

More details:
https://www.postgresql.org/docs/devel/datatype-boolean.html
https://github.com/postgres/postgres/blob/REL_12_BETA1/src/backend/utils/adt/bool.c#L25
https://github.com/postgres/postgres/commit/05a7db05826c5eb68173b6d7ef1553c19322ef48
https://github.com/postgres/postgres/commit/9729c9360886bee7feddc6a1124b0742de4b9f3d

Other DBs:
http://docs.aws.amazon.com/redshift/latest/dg/r_Boolean_type.html
https://my.vertica.com/docs/5.0/HTML/Master/2983.htm
https://github.com/prestosql/presto/blob/b845cd66da3eb1fcece50efba83ea12bc40afbaa/presto-main/src/main/java/com/facebook/presto/type/VarcharOperators.java#L108-L138

Issue Links

• is related to: SPARK-10442 select cast('false' as boolean) returns true
• relates to: SPARK-28023 Trim the string when cast string type to other types
• relates to: SPARK-30000 Trim the string when cast string type to decimals
• links to: GitHub Pull Request #25458