mirror of
https://github.com/MariaDB/server.git
synced 2025-08-14 14:31:32 +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.
58 lines
2 KiB
Text
58 lines
2 KiB
Text
#
|
|
# MDEV-20022 sql_mode="oracle" does not support TO_NUMBER() function
|
|
#
|
|
SET NAMES utf8mb4;
|
|
SELECT to_number('$123-', CONVERT('$999S' USING ucs2)) AS c1;
|
|
c1
|
|
-123
|
|
SELECT to_number('$123-', CONVERT('$999Š' USING ucs2)) AS c1;
|
|
ERROR HY000: Incorrect <number format> value: 'Š' for function to_number
|
|
SELECT to_number(CONVERT('$123-' USING ucs2), '$999S') AS c1;
|
|
c1
|
|
-123
|
|
SELECT to_number(CONVERT('$123-' USING ucs2), CONVERT('$999S' USING ucs2)) AS c1;
|
|
c1
|
|
-123
|
|
SELECT to_number(CONVERT('GarbageŠ' USING ucs2), CONVERT('$999S' USING ucs2)) AS c1;
|
|
c1
|
|
NULL
|
|
Warnings:
|
|
Warning 1411 Incorrect <number format>='$999S' value: 'GarbageŠ' for function to_number
|
|
CREATE TABLE t1 (fmt VARCHAR(32) CHARACTER SET ucs2);
|
|
INSERT INTO t1 VALUES ('$999S'), ('$999MI'), ('$999Š');
|
|
SELECT fmt, to_number('$123-', fmt) AS c1 FROM t1;
|
|
fmt c1
|
|
$999S -123
|
|
$999MI -123
|
|
$999Š NULL
|
|
Warnings:
|
|
Warning 1411 Incorrect <number format> value: 'Š' for function to_number
|
|
SELECT fmt, to_number('GarbageŠ', fmt) AS c1 FROM t1;
|
|
fmt c1
|
|
$999S NULL
|
|
$999MI NULL
|
|
$999Š NULL
|
|
Warnings:
|
|
Warning 1411 Incorrect <number format>='$999S' value: 'GarbageŠ' for function to_number
|
|
Warning 1411 Incorrect <number format>='$999MI' value: 'GarbageŠ' for function to_number
|
|
Warning 1411 Incorrect <number format> value: 'Š' for function to_number
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (fmt VARCHAR(32) CHARACTER SET ucs2);
|
|
INSERT INTO t1 VALUES ('$999S'), ('$999MI'), ('$999Š');
|
|
SELECT fmt, to_number(CONVERT('$123-' USING ucs2), fmt) AS c1 FROM t1;
|
|
fmt c1
|
|
$999S -123
|
|
$999MI -123
|
|
$999Š NULL
|
|
Warnings:
|
|
Warning 1411 Incorrect <number format> value: 'Š' for function to_number
|
|
SELECT fmt, to_number(CONVERT('GarbageŠ' USING ucs2), fmt) AS c1 FROM t1;
|
|
fmt c1
|
|
$999S NULL
|
|
$999MI NULL
|
|
$999Š NULL
|
|
Warnings:
|
|
Warning 1411 Incorrect <number format>='$999S' value: 'GarbageŠ' for function to_number
|
|
Warning 1411 Incorrect <number format>='$999MI' value: 'GarbageŠ' for function to_number
|
|
Warning 1411 Incorrect <number format> value: 'Š' for function to_number
|
|
DROP TABLE t1;
|