RFC-003: New SQL syntax: support HAVING clause and LIKE operator

[jingchen2222]

Status: Approved

Problem

Currently, OpenMLDB doesn't support certain SQL syntaxes like the HAVING clause and the LIKE operator. These abilities are required by data scientists for data analysis.

HAVING Clause

Related issue: #547

HAVING clause in SQL specifies that an SQL SELECT statement must only return rows where aggregate values meet the specified conditions.

Generally, the HAVING clause is used in conjunction with aggregate functions and the GROUP BY clause to control which groups are selected.

Example:

-- To return a list of users whose average transaction amount exceeded 1000, 
-- along with the sum, avg, max, min transaction amount: 
SELECT user, 
COUNT(user) as F_TRANS_COUNT,
SUM(trans_amount) as F_TRANS_AMOUNT_SUM, 
AVG(trans_amount) as F_TRANS_AMOUNT_AVG, 
MAX(trans_amount) as F_TRANS_AMOUNT_MAX, 
MIN(trans_amount) as F_TRANS_AMOUNT_MIN 
FROM t1 GROUP BY user HAVING AVG(trans_amount) > 1000;

LIKE expression

Related issue: #224

LIKE is a logical operator that determines if a character string matches a specified pattern. A pattern may include regular characters and wildcard characters.

Example:

CREATE TABLE person (id INT, name STRING, age INT);
INSERT INTO person VALUES
(100, 'John', 30),
(200, 'Mary', NULL),
(300, 'Mike', 80),
(400, 'Dan', 50),
(500, 'Evan_w', 16);
SELECT * FROM person WHERE name LIKE 'M%';
+---+----+----+
| id|name| age|
+---+----+----+
|300|Mike| 80|
|200|Mary|null|
+---+----+----+
SELECT * FROM person WHERE name LIKE 'M_ry';
+---+----+----+
| id|name| age|
+---+----+----+
|200|Mary|null|
+---+----+----+
SELECT * FROM person WHERE name LIKE '%\_%';
+---+------+---+
| id| name|age|
+---+------+---+
|500|Evan_W| 16|
+---+------+---+
SELECT * FROM person WHERE name LIKE '%$_%' ESCAPE '$';
+---+------+---+
| id| name|age|
+---+------+---+
|500|Evan_W| 16|
+---+------+---+

Solution

Support HAVING clause in SELECT statement

Syntax

The syntax for the HAVING clause in SQL is:

SELECT expression1, expression2, ... expression_n, aggregate_function (aggregate_expression) 
FROM tables [WHERE conditions] 
GROUP BY expression1, expression2, ... expression_n HAVING condition;

Constraints:

• HAVING clause specifies a search condition for a group or an aggregate.
• HAVING clause can be used only with the SELECT statement.
• HAVING clause is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregation
• HAVING clause isn't used with a WINDOW clause.

Support LIKE operator and ESCAPE operator

Syntax

[ NOT ] { LIKE search_pattern [ ESCAPE esc_char ] }

Parameters

• search_pattern:

  Specifies a string pattern to be searched by the LIKE clause. It can contain special pattern-matching characters:

  • precent(%) matches zero or more characters.
  • underscore(_) matches exactly one character.

• esc_char: Specifies the escape character.

  • The default escape character is backslash(\).
  • if escape character is empty or null, escape feature is disabled

  the LIKE is case sensitive, there will also be ILIKE, which is the case insensitive version of like

Constraints:

• During pattern matching, regular characters must exactly match the characters specified in the character string.
• However, wildcard characters can be matched with arbitrary fragments of the character string.
• If any one of the arguments isn't of character string data type, the OpenMLDB Engine needs to convert it to character string data type, if it's possible.

Changes and Additions to Public Interfaces

This is the new component and will not affect the public interface

Performance Impact

This is the new component and will not affect the current performance.

Backwards Compatibility and Upgrade Path

This is the new component and doesn't have backwards compatibility and upgrade issues.

Related Work

#547
#224

References

• https://docs.databricks.com/sql/language-manual/functions/ilike.html
• https://www.postgresql.org/docs/8.3/functions-matching.html

[aceforeverd]

here are detail rules/works I'd consider @jingchen2222

LIKE predicate is case sensitive by default. There is also a ILIKE predicate, which is case insensitive.

Escape character:

• backslash(\) is the default escape character
• length of must <= 1
• if is empty, escape feautre is disabled(makes it impossible to turn off the special meaning of underscore and percent signs in the pattern)

To write backslash in sql string literal, one must write as \\