apache / apache/shardingsphere

Support federated query mysql length function

Open
#31,897 0 comments 0 reactions 0 assignees View on GitHub
feature: SQL federation type: enhancement
Dominant language
Java
Stars
20.8k
Forks
6.9k
Avg merge
11h 35m
Merged PRs (30d)
326

Description

# Background

Hi Community,

Federated queries help users perform cross-database join queries, subqueries, and aggregate queries. Currently, we plan to enhance MySQL functions in the federated query environment to support more SQL execution scenarios in federated query contexts.

For more details, please refer to: [Apache ShardingSphere Documentation on SQL Federation](https://shardingsphere.apache.org/document/current/cn/features/sql-federation/).

Function document: https://dev.mysql.com/doc/refman/8.4/en/string-functions.html#function_length

# Task

- [ ] `select length('\n\t\r\b\0\_\%\\');`

Run log:
```
expected:
+------------------------------+
| length('\n\t\r\b\0\_\%\\') |
+==============================+
| 10 |
+------------------------------+

actual:
SQL federation does not support SQL 'select length('\\n\\t\\r\\b\\0\\_\\%\\\\');\n'.\nMore details: At line 0, column 0: No match found for function signature length()
```

- [ ] `select length(_latin1'\n\t\n\b\0\\_\\%\\');`

Run log:
```
expected:
+---------------------------------------+
| length(_latin1'\n\t\n\b\0\\_\\%\\') |
+=======================================+
| 10 |
+---------------------------------------+

actual:
SQL federation does not support SQL 'select length(_latin1'\\n\\t\\n\\b\\0\\\\_\\\\%\\\\');\n'.\nMore details: At line 0, column 0: No match found for function signature length()
```

- [ ] `SELECT LENGTH(code), code FROM t1 WHERE code='A12';`
- [ ] `SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5;`

Run log:
```
CREATE TABLE t1 (code varchar(10)) charset utf8mb4;
INSERT INTO t1 VALUES ('a12'), ('A12'), ('a13');
INSERT INTO t1 VALUES (_utf16 0x0061003100320007), (_utf16 0x00410031003200070007);

SELECT LENGTH(code), code FROM t1 WHERE code='A12';
expected:
+----------------+--------+
| LENGTH(code) | code |
+================+========+
| 3 | a12 |
+----------------+--------+
| 3 | A12 |
+----------------+--------+
| 4 | a12 |
+----------------+--------+
| 5 | A12 |
+----------------+--------+

actual:
SQL federation does not support SQL 'SELECT LENGTH(code), code FROM t1 WHERE code='A12';\n'.\nMore details: At line 0, column 0: No match found for function signature LENGTH()

SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5;
expected:
+----------------+--------+
| LENGTH(code) | code |
+================+========+
| 5 | A12 |
+----------------+--------+

actual:
SQL federation does not support SQL 'SELECT LENGTH(code), code FROM t1 WHERE code='A12' AND LENGTH(code)=5;\n'.\nMore details: At line 0, column 0: No match found for function signature LENGTH()
```

- [ ] TODO Test cases for nested function calls

Contributor guide

Open the contributing guide

Research direction

Start with the SQL Federation documentation and reproduce the listed LENGTH queries against the federated query environment. Use the provided SQL and expected results as regression cases, including escaped strings, character sets, predicates, and nested calls; done means these queries return the documented MySQL-compatible results instead of an unsupported-function error.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, mysql
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.