Support the SQL TRANSLATE function

[djsstarburst]

#3313

This implementation follows the specification used by Postgres; see the documentation section of this commit.

[electrum]

Overall looks good. A few minor comments

[martint]

How does this relate to the standard SQL translate syntax?

TRANSLATE <left paren> <character value expression>
      USING <transliteration name> <right paren>

[djsstarburst]

How does this relate to the standard SQL translate syntax?

TRANSLATE <left paren> <character value expression>
      USING <transliteration name> <right paren>

@martint and I discussed this. He made the point that TRANSLATE(source, originals, translations) could in general be implemented using TRANSLATE(source USING character_routine) where the routine itself had knowledge of the originals and translations. The reverse is not true, since character translation routines can make arbitrary transformations.

TRANSLATE(source USING character_routine) is definitely part of the SQL specification. However, the only RDB vendor I've found that supports it is Oracle.

TRANSLATE(source, originals, translations) is supported by Oracle, SQL Server, Redshift, Azure and probably others.

Supporting TRANSLATE(source USING character_routine) requires a bunch of machinery that we don't currently have to be built.

@martint registered these concerns:

• confusion: once we add support for standard TRANSLATE, will that be confusing to users? Similar names, very different semantics
• name conflict: in theory, there could be parsing ambiguities. The advantage here is that the standard TRANSLATE has the USING clause in the middle, so that should avoid all ambiguities.

I would vote to move forward with new function TRANSLATE(source, originals, translations), mostly because AFAICT it's more commonly supported, and it's straightforward.

[electrum]

The SQL standard TRANSLATE ... USING function is rather different:

<character transliteration> is a function for changing each character of a given string according to some many- to-one or one-to-one mapping between two not necessarily distinct character sets. The mapping, rather than being specified as part of the function, is some external function identified by a <transliteration name>.

The transliteration name is defined separately via CREATE TRANSLATION. Oracle doesn't support this -- they only have two predefined translations. I can't find any systems that actually implement this feature (which makes me wonder who added this to the standard and why).

@martint unless you have a strong objection, we should merge this as-is since it's a common function and provides a useful feature that can't be done any other way.

[electrum]

I was looking at the Oracle and PostgreSQL and found a couple interesting things.

Duplicates in from are ignored -- the first one is used. Oracle documents this:

If a character appears multiple times in from_string, then the to_string mapping corresponding to the first occurrence is used.

The other is that the lengths don't have to match:

The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string.

For duplicates, we could be strict and not allow them, or we could follow Oracle / PostgreSQL behavior. The current behavior of using the last one is not a good choice.

Similar, we can be strict for different lengths, or we could allow a shorter to_string to be used for removal. We support removal today with regex replace, so perhaps it's best to start off strict. The SQL Server implementation requires the strings to be the same lengths.

We might want to use the from/to language for the arguments as that's easy to reason about and matches Oracle and PostgreSQL.

[martint]

@martint unless you have a strong objection, we should merge this as-is since it's a common function and provides a useful feature that can't be done any other way.

No, my main concern was whether the semantics were too different or the intent behind them was similar (it is). The situation to avoid would be having two functions that look similar but do something completely different -- in that case, we'd need to come up with a different name.

[djsstarburst]

I just pushed updates to this PR that follow the Postgres spec:

• Changed names originals and translations to from and to.
• In the case of duplicates, use the "to" character corresponding to the first instance of a character in "from"
• If the "from" character is at an index that would put it off the end of the "to" string, don't translate the source character.

One result is that no correspondence is required of the "from" and "to" lengths.

[electrum]

The behavior for from being larger than to doesn't match PostgreSQL. The correct behavior is to drop any characters in from that do not exist in to:

# select translate('abcd', 'a', '');
 translate 
-----------
 bcd
(1 row)

# select translate('abcd', 'ac', 'z');
 translate 
-----------
 zbd
(1 row)

# select translate('abcd', 'aac', 'zq');
 translate 
-----------
 zbd
(1 row)

Otherwise, everything else looks good.

[djsstarburst]

Doh, I hadn't noticed the deletion case. I just force pushed the commit with code that handles the deletion case properly. I also included documentation examples copied from the unit test, since the translate function isn't trivial. I hope this is ready for final review and merge now, @electrum

[djsstarburst]

@electrum, I force-pushed the translate branch, with the static int eliminated, replaced by -1. and the translate examples formatted like the JSON examples as you suggested.

[electrum]

Please write these as full select queries that can be copy/pasted by the user:

SELECT translate('abcd', '', ''); -- 'abcd'

So add SELECT and semicolon.

[electrum]

This \u is actually a Java escape and not valid in SQL (it works in the test because it's translated by the Java compiler, so it's effectively a literal character in the SQL)

We can write this using the SQL U& syntax as documented here: https://prestosql.io/docs/current/language/types.html#string

For the output, we can paste the literal in directly.

SELECT translate('abcd', 'b', U&'\+01F600'); -- a😀cd

[djsstarburst]

@electrum, I added the SELECT and ; in the docs, and used the suggested unicode transliteration.

[electrum]

Thanks!