Issue 1:
Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

Searched open and closed GitHub issues.

Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.

If no response more than 7 days and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

4.1.0

Which project did you use? Sharding-JDBC or Sharding-Proxy?

Sharding-JDBC

Expected behavior

No output log

Actual behavior

line 1:45 no viable alternative at input 'CONVERT(substring(customer_code,3),signed'

line 1:45 no viable alternative at input 'CONVERT(substring(customer_code,3),signed'

Reason analyze (If you can)

Unsupported function

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

select CONVERT(substring(customer_code, 3) , signed) as maxCustNo from tb_sp_user_customer order by id desc limit 1

Example codes for reproduce this issue (such as a github link).

select CONVERT(substring(customer_code, 3) , signed) as maxCustNo from tb_sp_user_customer order by id desc limit 1

Issue 2:
BackGround

Using the built-in test suite in the MySQL test tool, evaluate the level of support for MySQL provided by ShardingSphere. In the initial round of testing, there were over 1,000 cases in which errors occurred during parsing due to lack of support from the SQLParser. Here are the unsupported scenarios, categorized by priority based on importance.

Task

High priority

ALTER TABLE t1 MODIFY COLUMN c1 FLOAT(10.3), DROP CHECK t1_chk_1, ADD CONSTRAINT CHECK(C1 > 10.1) ENFORCED;

ALTER TABLE t1 ORDER BY t1.id, t1.status, t1.type_id, t1.user_id, t1.body;

ALTER USER 20553132_u2@localhost IDENTIFIED BY 'abcd'

ALTER USER USER() IDENTIFIED BY 'def'

ALTER USER tu1@localhost IDENTIFIED WITH 'sha256_password' REQUIRE CIPHER "DHE-RSA-AES256-SHA";

ALTER USER u10@localhost IDENTIFIED WITH 'sha256_password' BY 'auth_string' REQUIRE SSL

ALTER USER u2@localhost IDENTIFIED WITH 'mysql_native_password' PASSWORD EXPIRE NEVER

ALTER USER user11@localhost IDENTIFIED WITH 'sha256_password' REQUIRE CIPHER "DHE-RSA-AES256-SHA" AND SUBJECT "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=Client" ISSUER "/C=SE/ST=Stockholm/L=Stockholm/O=Oracle/OU=MySQL/CN=CA" PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

ANALYZE TABLE tbl_datetime UPDATE HISTOGRAM ON col1 USING DATA '{"buckets": [[20170216162223, 0.125], ["2017-02-10 08:15:18.000000", 0.375], ["2018-02-12 07:04:18.000000", 0.5], ["2018-03-21 21:12:42.000000", 0.75]], "data-type": "datetime", "null-values": 0.25, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 4}';

CHECK TABLES t1

CREATE TABLE buildings ( fid INTEGER NOT NULL PRIMARY KEY, address CHARACTER VARYING(64), position POINT, footprint POLYGON);

CREATE TABLE t1 (f1 INT) START TRANSACTION

CREATE TABLE t1 (g GEOMCOLLECTION)

CREATE TABLE t1(j json, INDEX mv_idx((CAST(j AS UNSIGNED ARRAY))));

CREATE TABLESPACE ts1 ADD DATAFILE 'df1.ibd' COMMENT 'ts🐬'

DROP USER u1@120.0.0.1

EXPLAIN ANALYZE DELETE t1 FROM t t1, t t2 WHERE t1.x = t2.x + 1

FLUSH TABLE WITH READ LOCK

FLUSH TABLE db1.t1 FOR EXPORT

FLUSH TABLE mysql_db1.t1

GRANT CREATE ON * . * TO user_name_len_25_01234567@localhost

GRANT PROXY ON bug20364862_dest@localhost TO bug20364862_user@localhost

IMPORT TABLE FROM 's1/t1*.sdi'

INSERT INTO t1 SET b='11', a=0 AS nON DUPLICATE KEY UPDATE b= n.a, a= n.b

INSERT INTO t1 VALUES(_utf16 0x1EC2), (_utf16 0x1EC3), (_utf16 0x1EC5), (_utf16 0x1EC0), (_utf16 0x1EC7), (_Utf16 0x1EBF)

KILL QUERY @id

LOCK TABLE m1 READ

LOCK TABLE m1 WRITE

RENAME TABLES child1 TO child

SELECT ""(""

SELECT ((+0) IN((0b111111111111111111111111111111111111111111111111111),(rpad(1.0,2048,1)),(32767.1)))

SELECT 1 /!99999 / */ */

SELECT 1 AS STATUS FROM performance_schema.global_status WHERE VARIABLE_NAME= 'MAX_EXECUTION_TIME_SET_FAILED' AND CONVERT(VARIABLE_VALUE, UNSIGNED) > @time_set_failed;

SELECT 1 FROM t1, LATERAL (SELECT DISTINCT t1.x) AS dt1, LATERAL (SELECT DISTINCT dt1.x) AS dt2WHERE dt1.x = dt2.x;

SELECT @@global.default.`key_buffer_size`

SELECT @before=@after

SELECT @trace RLIKE ""keypart_reference_from_where_clause""

SELECT AES_DECRYPT(AES_ENCRYPT(@ENCSTR, @keys, @Iv), @keys, @Iv)=@ENCSTR FROM t1

SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33" USING latin1)

SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33", BINARY(4))

SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33", CHAR(4))

SELECT CONVERT(TIMESTAMP "2004-01-22 21:45:33", CHAR)

SELECT DISTINCT f1 FROM t1 GROUP BY f1 WITH ROLLUP ORDER BY f1, ANY_VALUE(GROUPING(f1))

SELECT HEX(_binary 0x0003 << (_binary 0x38 | 0x38))

SELECT NAME_CONST('var', 'value') COLLATE latin1_general_cs

SELECT NULL IN(SELECT (f1 between 0 and 1) FROM (SELECT f1 FROM t WHERE (@b:=NULL) - f2)as dt)

SELECT REGEXP_REPLACE(e, 'pattern', 'xyz')FROM (VALUES ROW('Find pattern'), ROW(NULL), ROW('Find pattern')) AS v(e)

SELECT STD(0) FROM t2, LATERAL ( SELECT f1 FROM t1 GROUP BY f2,f1 ) AS d1

SELECT ST_AsWKB(ST_GeomFromText('POINT(0 0)')) IN (SELECT b FROM t1) AS result

SELECT ST_GeomFromText('POINT(0 0)') IN (SELECT b FROM t1) AS result

SELECT TRACE NOT RLIKE '"final_filtering_effect": 1' AS OK FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

SELECT b, b IN ('20161213'), b in ('20161213', 0) FROM t2;

SELECT point(1,1) IN ('1',1,'1') AS res

SHOW CREATE USER user1@47.9.9.9

create table ```a` (i int)

create table `t``1`(a int) engine=myisam

drop table ```a`

grant select on mysqltest.* to mysqltest_8@

insert into t2 select x3.a, -- 3 concat('val-', x3.a + 3x4.a), -- 12 concat('val-', @A:=x3.a + 3x4.a + 12C.a), -- 120 concat('val-', @A + 120D.a)from t1 x3, t1 x4, t1 C, t1 D where x3.a < 3 and x4.a < 4 and D.a < 4order by x3.a, x4.a, C.a, D.a;

kill @id

select * from (select * from t1 except all select * from t1 limit 2) a

select * from t1 where str <> default(str)

select _latin1'B' collate latin1_bin in (_latin1'a',_latin1'b')

select case 1/0 when "a" then "true" else "false" END

select extract(DAY_HOUR FROM "1999-01-02 10:11:12")

select mysqltest1.f1()

select position("0" in "baaa" in (1)),position("0" in "1" in (1,2,3)),position("sql" in ("mysql"))

show grants for mysqltest_7@

Medium priority

ALTER TABLESPACE ts1 RENAME TO ts2

CREATE EVENT e1 ON SCHEDULE EVERY 1 SECOND DO DROP DATABASE BUG52792

CREATE USER u3@120.0.0.1

CREATE VIEW V_T1_C AS SELECT DB29023216.TESTFUNC()

EXECUTE ps1 USING @expected, @db, @table

cache index t1,t2 in default;

Low priority

CHANGE REPLICATION SOURCE TO SOURCE_USER= 'plug_user',SOURCE_PASSWORD= 'plug_user',SOURCE_RETRY_COUNT= 0;

DO ST_AsText(@centroid_point) as centroid

HANDLER t1 CLOSE

SET @drop = IF(@have_ndb = 0, @drop_cmd, 'SET @dummy = 0');PREPARE drop_stmt FROM @drop;

alter event e_43 dobeginalter event e_43 on schedule every 5 minute;insert into test_nested values(1);end;

handler a1 read first limit 9