[CALCITE-3263] Add MD5, SHA1 SQL functions

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Fix Version/s: 1.21.0
• Labels: pull-request-available
• Assignee: Julian Hyde
• Reporter: ShuMing Li
• Created: 19/Aug/19 10:03
• Updated: 10/Sep/19 21:36
• Resolved: 23/Aug/19 05:42

Description

`MD5/SHA1` functions are common UDFs in many SQL engines. We may support them in SQL just like `from_base64`/`to_base64`. 

A Review of Other Databases

• BigQuery : https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#md5
  • Function : MD5(String/Bytes)
  • Input : String/Bytes
  • Output : Bytes
• MySQL : https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html
  • Function : MD5(String)
  • Input : String
  • Output : String
• Oracle : https://docs.oracle.com/database/121/SQLRF/functions183.htm#SQLRF55647
  • Function : STANDARD_HASH(expr, method)
  • Input : String
  • Output : RAW
• PostgreSQL : https://www.postgresql.org/docs/current/functions-string.html 
  • Function : MD5(String)
  • Input : String
  • Output : Text
• Redshift : https://docs.aws.amazon.com/redshift/latest/dg/r_MD5.html
  • Function : MD5(String)
  • Input : String
  • Output : String

Issue Links

• links to: GitHub Pull Request #1390