mirror of
https://github.com/MariaDB/server.git
synced 2025-08-16 23:41:37 +02:00
6ba83565f0
This patch implements an Oracle style function to_number() with the following signatures: - to_number(number_or_string_subject) - to_number(string_subject, string_format) The function is implemented in sql/item_numconvfunc.cc. The function returns the DOUBLE data type for all signatures and input data types. The format parser understands the following components: - Digits: 0, 9 - Hex digits: X - Group separators: comma (,) and G - Decimal delimiers: period (.) and D - Approximate number signature: EEEE - Currency/numeric flags: $ and B - Currency signatures: C, L, U - Sign signatures: S, MI, PR - Special format signatures: V, TM, TM9, TME - Format flag: FM Note, the parser was implemented assuming that we'll also have the oppostite function to_char() soon for numeric input. So it parser all known components. However, the function to_number() does not support: - Formats V, TM, TM9, TME. to_number() returns NULL if the format string has these components. These componens are supported only by to_char() in Oracle. Features not inclided into this patch: - The ON CONVERSION ERROR clause - The third parameter (nlsparam) - Internationalized components: G, D, C, L, U. These features will be implemented later, under terms of MDEV-36978. Notable changes in the other files: - item_func.h: adding Item_handled_func::Handler_double - simple_parser.h: adding a number of *CONTAINER* templates They help to save on duplicate code when creating classes suitable for passing into parsing templates such as OPT, OR2C, OR3C, etc - simple_parser.h: Adding parsing templates OR4C and OR5C - simple_parser.h: Moving the template "OPT" towars the beginning of the file Rule parsing templates TOKEN, TokenChoice, AND2, OR2C, OR3C, OR4C, OR5C, LIST now provide a sub-class Opt, to parse its optional rule. - simple_parser.h: Adding "explicit" to all "operator bool" definitions - Renaming Parser_templates::TOKEN to Parser_templates::TokenParser - Adding "explicit" to all "operator bool()" templates/classes, to avoid hidden implicit conversion (to int, void*, etc). - Renaming the LIST template parameter ELEMENT to ELEMENT_PARSER, to make it clearer what it is for. - Renaming the OPT template parameter RULE to RULE_PARSER, to make it clearer what it is for.
100 lines
5 KiB
Text
100 lines
5 KiB
Text
SET @saved_debug_dbug= @@debug_dbug;
|
|
SET @@debug_dbug='+d,numconv_format';
|
|
#
|
|
# MDEV-20022 sql_mode="oracle" does not support TO_NUMBER() function
|
|
#
|
|
CREATE FUNCTION bad(fmt VARCHAR(128)) RETURNS VARCHAR(128)
|
|
BEGIN
|
|
IF fmt RLIKE '(FM.*FM|B.*B|EEEE.*EEEE|[CLU$].*[CLU$]|[.VD].*[.VD]|(S|MI|PR).*(S|MI|PR))'
|
|
THEN
|
|
RETURN 'FM/B/currency/dec/sign cannot repeat';
|
|
END IF;
|
|
IF fmt RLIKE '[09XBCDLUV,G.$].*FM' THEN RETURN 'Unexpected + FM'; END IF;
|
|
IF fmt RLIKE '^[^09]*([.DV].*)?EEEE' THEN RETURN 'EEEE with no int digits #1'; END IF;
|
|
IF fmt RLIKE '^(FM|S)?[$B]+[CLU][0-9]*EEEE' THEN RETURN 'EEEE with no int digits #2'; END IF;
|
|
IF fmt RLIKE '.*EEEE.*(FM|[B09X.DV,GLCU$V])' THEN RETURN 'EEEE + unexpected'; END IF;
|
|
IF fmt RLIKE '^([$BC.DLUVS]|FM)EEEE' THEN RETURN '^Unexpected + EEEE immediately'; END IF;
|
|
IF fmt RLIKE '(G.*,|,.*G)' THEN RETURN 'comma and G cannot co-exist'; END IF;
|
|
IF fmt RLIKE '^([$B]|[^09]|)*[,G]' THEN RETURN 'group with no int digits'; END IF;
|
|
IF fmt RLIKE '[.DV].*[,G]' THEN RETURN 'dec + group'; END IF;
|
|
IF fmt RLIKE '(FM|[S.DCLUV])[,G]' THEN RETURN 'Unexpected + group immediately'; END IF;
|
|
IF fmt RLIKE '(MI|PR).+' THEN RETURN 'Trailing sign + something'; END IF;
|
|
IF fmt RLIKE '[$09BVCLUX.D,G].*TM' THEN RETURN 'Unexpected + TM'; END IF;
|
|
IF fmt RLIKE 'TM([^E9].*|[E9].+)' THEN RETURN 'TMx + something'; END IF;
|
|
IF fmt RLIKE '([9BCDLUVS.,G$]|TM).*X' THEN RETURN 'Unexpected + X'; END IF;
|
|
IF fmt RLIKE 'X[^X]' THEN RETURN 'X + unexpected'; END IF;
|
|
-- Bad combinations consisting of three elements
|
|
IF fmt RLIKE '[.VD].*[CLU].*[09]' THEN RETURN 'Dec + CLU + digit'; END IF;
|
|
IF fmt RLIKE '[CLUV09.BD$].*[S].*([09$BVCLU.D]|EEEE)' THEN RETURN 'Trailing S sign + unexpected'; END IF;
|
|
IF fmt RLIKE '[B09].*[CLUV].*[,GV.D]' THEN RETURN 'CLUV as dec + group/dec'; END IF;
|
|
IF fmt RLIKE '[.DV].*[CLU].*[B]' THEN RETURN 'CLU as trailing currency + B'; END IF;
|
|
RETURN '';
|
|
END;
|
|
$$
|
|
CREATE TABLE t1 (fmt VARCHAR(32));
|
|
INSERT INTO t1 VALUES ('');
|
|
INSERT INTO t1 VALUES ('FM') /* leading flags */;
|
|
INSERT INTO t1 VALUES ('.'),('D'),('V') /* dec */;
|
|
INSERT INTO t1 VALUES (','),('G') /* group */;
|
|
INSERT INTO t1 VALUES ('$'),('B') /* inline flags */;
|
|
INSERT INTO t1 VALUES ('C'),('L'),('U') /* currency */;
|
|
INSERT INTO t1 VALUES ('S') /* sign */;
|
|
INSERT INTO t1 VALUES ('MI'),('PR') /* trailing sign */;
|
|
INSERT INTO t1 VALUES ('TM'),('TM9'),('TME') /* TM formats */;
|
|
INSERT INTO t1 VALUES ('0'),('9'),('X') /* digits */;
|
|
INSERT INTO t1 VALUES ('EEEE') /* approximate format */;
|
|
SELECT fmt FROM t1 WHERE bad(fmt)='' AND to_number('1',fmt) IS NULL;
|
|
fmt
|
|
SELECT fmt FROM t1 WHERE bad(fmt)<>'' AND to_number('1',fmt) IS NOT NULL;
|
|
fmt
|
|
CREATE VIEW v2 AS SELECT CONCAT(t1.fmt, t2.fmt) AS fmt FROM t1 t1, t1 t2;
|
|
SELECT fmt FROM v2 WHERE bad(fmt)='' AND to_number('1',fmt) IS NULL;
|
|
fmt
|
|
SELECT fmt FROM v2 WHERE bad(fmt)<>'' AND to_number('1',fmt) IS NOT NULL;
|
|
fmt
|
|
DROP VIEW v2;
|
|
CREATE VIEW v3 AS SELECT CONCAT(t1.fmt, t2.fmt, t2.fmt) AS fmt FROM t1 t1, t1 t2, t1 t3;
|
|
SELECT fmt FROM v3 WHERE bad(fmt)='' AND to_number('1',fmt) IS NULL;
|
|
fmt
|
|
SELECT fmt FROM v3 WHERE bad(fmt)<>'' AND to_number('1',fmt) IS NOT NULL;
|
|
fmt
|
|
DROP VIEW v3;
|
|
DROP TABLE t1;
|
|
CREATE TABLE t4 (fmt VARCHAR(32));
|
|
INSERT INTO t4 VALUES ('.'),('D'),('V') /* dec */;
|
|
INSERT INTO t4 VALUES (','),('G') /* group */;
|
|
INSERT INTO t4 VALUES ('$'),('B') /* inline flags */;
|
|
INSERT INTO t4 VALUES ('C') /* currency - simplified */;
|
|
INSERT INTO t4 VALUES ('0'),('9'),('X') /* digits */;
|
|
CREATE VIEW v4 AS
|
|
SELECT CONCAT(t1.fmt, t2.fmt, t3.fmt,t4.fmt) AS fmt
|
|
FROM t4 t1, t4 t2, t4 t3, t4 t4;
|
|
SELECT fmt FROM v4 WHERE bad(fmt)='' AND to_number('1',fmt) IS NULL;
|
|
fmt
|
|
SELECT fmt FROM v4 WHERE bad(fmt)<>'' AND to_number('1',fmt) IS NOT NULL;
|
|
fmt
|
|
DROP VIEW v4;
|
|
DROP TABLE t4;
|
|
CREATE TABLE t4 (fmt VARCHAR(32));
|
|
INSERT INTO t4 VALUES ('');
|
|
INSERT INTO t4 VALUES ('FM') /* leading flags */;
|
|
INSERT INTO t4 VALUES ('.') /* dec - simplified */;
|
|
INSERT INTO t4 VALUES (',') /* group - simplified */;
|
|
INSERT INTO t4 VALUES ('$'),('B') /* inline flags */;
|
|
INSERT INTO t4 VALUES ('C') /* currency - simplified */;
|
|
INSERT INTO t4 VALUES ('S') /* sign */;
|
|
INSERT INTO t4 VALUES ('MI') /* trailing sign - simplified */;
|
|
INSERT INTO t4 VALUES ('TM9') /* TM formats - simplified */;
|
|
INSERT INTO t4 VALUES ('9'),('X') /* digits - simplified */;
|
|
INSERT INTO t4 VALUES ('EEEE') /* approximate format */;
|
|
CREATE VIEW v4 AS
|
|
SELECT CONCAT(t1.fmt, t2.fmt, t3.fmt,t4.fmt) AS fmt
|
|
FROM t4 t1, t4 t2, t4 t3, t4 t4;
|
|
SELECT fmt FROM v4 WHERE bad(fmt)='' AND to_number('1',fmt) IS NULL;
|
|
fmt
|
|
SELECT fmt FROM v4 WHERE bad(fmt)<>'' AND to_number('1',fmt) IS NOT NULL;
|
|
fmt
|
|
DROP VIEW v4;
|
|
DROP TABLE t4;
|
|
DROP FUNCTION bad;
|
|
SET debug_dbug= @saved_debug_dbug;
|