database/mariadb-columnstore-engine
1
0
Fork 0
mirror of https://github.com/mariadb-corporation/mariadb-columnstore-engine.git synced 2025-04-21 19:45:56 +03:00
Code
mariadb-columnstore-engine/mysql-test/columnstore/basic/t/mcol641-arithmetic.test
Gagan Goel 78dbf3a7e1 MCOL-641 Move MTR tests from future/ to basic/
2021-07-12 13:01:45 +00:00

310 lines
23 KiB
Plaintext
Raw Permalink Blame History

-- source ../include/have_columnstore.inc
--disable_warnings
DROP DATABASE IF EXISTS mcol641_arithmetic_db;
--enable_warnings
CREATE DATABASE mcol641_arithmetic_db;
USE mcol641_arithmetic_db;
--disable_warnings
DROP PROCEDURE IF EXISTS deleteproc;
--enable_warnings
DELIMITER //;
CREATE PROCEDURE deleteproc ()
BEGIN
DELETE FROM cs1;
DELETE FROM cs2;
DELETE FROM cs3;
DELETE FROM cs4;
DELETE FROM cs5;
DELETE FROM cs6;
DELETE FROM cs7;
DELETE FROM cs8;
DELETE FROM cs9;
DELETE FROM cs10;
DELETE FROM cs11;
DELETE FROM cs12;
END//
DELIMITER ;//
CREATE TABLE cs1 (d1 DECIMAL(38), d2 DECIMAL(38)) ENGINE=columnstore;
CREATE TABLE cs2 (d1 DECIMAL(19), d2 DECIMAL(19)) ENGINE=columnstore;
CREATE TABLE cs3 (d1 DECIMAL(38), d2 DECIMAL(19)) ENGINE=columnstore;
CREATE TABLE cs4 (d1 DECIMAL(38), d2 DECIMAL(10)) ENGINE=columnstore;
CREATE TABLE cs5 (d1 DECIMAL(38,10), d2 DECIMAL(38,10)) ENGINE=columnstore;
CREATE TABLE cs6 (d1 DECIMAL(19,10), d2 DECIMAL(19,10)) ENGINE=columnstore;
CREATE TABLE cs7 (d1 DECIMAL(38,10), d2 DECIMAL(19,10)) ENGINE=columnstore;
CREATE TABLE cs8 (d1 DECIMAL(38,10), d2 DECIMAL(10,10)) ENGINE=columnstore;
CREATE TABLE cs9 (d1 DECIMAL(38,38), d2 DECIMAL(38,38)) ENGINE=columnstore;
CREATE TABLE cs10 (d1 DECIMAL(19,19), d2 DECIMAL(19,19)) ENGINE=columnstore;
CREATE TABLE cs11 (d1 DECIMAL(38,38), d2 DECIMAL(19,19)) ENGINE=columnstore;
CREATE TABLE cs12 (d1 DECIMAL(38,38), d2 DECIMAL(10,10)) ENGINE=columnstore;
# Addition
# without overflow check
SET columnstore_decimal_overflow_check=off;
INSERT INTO cs1 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs1 VALUES (12345678901234567890123456789012345678, 12345678901234567890123456789012345678), (12345678901234567890123456789012345678, -22345678901234567890123456789012345678);
INSERT INTO cs1 VALUES (99999999999999999999999999999999999998, 1), (-99999999999999999999999999999999999998, -1);
SELECT "add_test_38_0_38_0", d1 + d2 FROM cs1;
INSERT INTO cs2 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs2 VALUES (1234567890123456789, 1234567890123456789), (1234567890123456789, -2234567890123456789);
INSERT INTO cs2 VALUES (9999999999999999998, 1), (-9999999999999999998, -1);
SELECT "add_test_19_0_19_0", d1 + d2 FROM cs2;
INSERT INTO cs3 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs3 VALUES (12345678901234567890123456789012345678, 1234567890123456789), (12345678901234567890123456789012345678, -2234567890123456789);
INSERT INTO cs3 VALUES (99999999999999999999999999999999999998, 1), (-99999999999999999999999999999999999998, -1);
SELECT "add_test_38_0_19_0", d1 + d2 FROM cs3;
INSERT INTO cs4 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs4 VALUES (12345678901234567890123456789012345678, 1234567890), (12345678901234567890123456789012345678, -2234567890);
INSERT INTO cs4 VALUES (99999999999999999999999999999999999998, 1), (-99999999999999999999999999999999999998, -1);
SELECT "add_test_38_0_10_0", d1 + d2 FROM cs4;
INSERT INTO cs5 VALUES (12.34, 56.78), (12.34, -56.78);
INSERT INTO cs5 VALUES (1234567890123456789012345678.9012345678, 1234567890123456789012345678.9012345678), (1234567890123456789012345678.9012345678, -2234567890123456789012345678.9012345678);
INSERT INTO cs5 VALUES (9999999999999999999999999999.9999999998, 0.0000000001), (-9999999999999999999999999999.9999999998, -0.0000000001);
SELECT "add_test_38_10_38_10", d1 + d2 FROM cs5;
INSERT INTO cs6 VALUES (12.34, 56.78), (12.34, -56.78);
INSERT INTO cs6 VALUES (123456789.0123456789, 123456789.0123456789), (123456789.0123456789, -223456789.0123456789);
INSERT INTO cs6 VALUES (999999999.9999999998, 0.0000000001), (-999999999.9999999998, -0.0000000001);
SELECT "add_test_19_10_19_10", d1 + d2 FROM cs6;
INSERT INTO cs7 VALUES (12.34, 56.78), (12.34, -56.78);
INSERT INTO cs7 VALUES (1234567890123456789012345678.9012345678, 123456789.0123456789), (1234567890123456789012345678.9012345678, -223456789.0123456789);
INSERT INTO cs7 VALUES (9999999999999999999999999999.9999999998, 0.0000000001), (-9999999999999999999999999999.9999999998, -0.0000000001);
SELECT "add_test_38_10_19_10", d1 + d2 FROM cs7;
INSERT INTO cs8 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs8 VALUES (1234567890123456789012345678.9012345678, 0.1234567890), (1234567890123456789012345678.9012345678, -0.2234567890);
INSERT INTO cs8 VALUES (9999999999999999999999999999.9999999998, 0.0000000001), (-9999999999999999999999999999.9999999998, -0.0000000001);
SELECT "add_test_38_10_10_10", d1 + d2 FROM cs8;
INSERT INTO cs9 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs9 VALUES (0.12345678901234567890123456789012345678, 0.12345678901234567890123456789012345678), (0.12345678901234567890123456789012345678, -0.22345678901234567890123456789012345678);
INSERT INTO cs9 VALUES (0.99999999999999999999999999999999999998, 0.00000000000000000000000000000000000001), (-0.99999999999999999999999999999999999998, -0.00000000000000000000000000000000000001);
SELECT "add_test_38_38_38_38", d1 + d2 FROM cs9;
INSERT INTO cs10 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs10 VALUES (0.1234567890123456789, 0.1234567890123456789), (0.1234567890123456789, -0.2234567890123456789);
INSERT INTO cs10 VALUES (0.9999999999999999998, 0.0000000000000000001), (-0.9999999999999999998, -0.0000000000000000001);
SELECT "add_test_19_19_19_19", d1 + d2 FROM cs10;
INSERT INTO cs11 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs11 VALUES (0.12345678901234567890123456789012345678, 0.1234567890123456789), (0.12345678901234567890123456789012345678, -0.2234567890123456789);
INSERT INTO cs11 VALUES (0.99999999999999999999999999999999999998, 0.0000000000000000001), (-0.99999999999999999999999999999999999998, -0.0000000000000000001);
SELECT "add_test_38_38_19_19", d1 + d2 FROM cs11;
INSERT INTO cs12 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs12 VALUES (0.12345678901234567890123456789012345678, 0.1234567890), (0.12345678901234567890123456789012345678, -0.2234567890);
INSERT INTO cs12 VALUES (0.99999999999999999999999999999999999998, 0.0000000001), (-0.99999999999999999999999999999999999998, -0.0000000001);
SELECT "add_test_38_38_10_10", d1 + d2 FROM cs12;
# with overflow check
# SET columnstore_decimal_overflow_check=on;
# CALL deleteproc();
# INSERT INTO cs1 VALUES (99999999999999999999999999999999999999, 99999999999999999999999999999999999999), (-99999999999999999999999999999999999999, -99999999999999999999999999999999999999);
# SELECT "add_test_38_0_38_0_overflow", d1 + d2 FROM cs1;
# INSERT INTO cs9 VALUES (0.99999999999999999999999999999999999999, 0.99999999999999999999999999999999999999), (-0.99999999999999999999999999999999999999, -0.99999999999999999999999999999999999999);
# SELECT "add_test_38_38_38_38_overflow", d1 + d2 FROM cs9;
# Subtraction
# without overflow check
SET columnstore_decimal_overflow_check=off;
CALL deleteproc();
INSERT INTO cs1 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs1 VALUES (12345678901234567890123456789012345678, -12345678901234567890123456789012345678), (-12345678901234567890123456789012345678, -22345678901234567890123456789012345678);
INSERT INTO cs1 VALUES (99999999999999999999999999999999999999, 1), (-99999999999999999999999999999999999999, -1);
SELECT "subtract_test_38_0_38_0", d1 - d2 FROM cs1;
INSERT INTO cs2 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs2 VALUES (1234567890123456789, -1234567890123456789), (-1234567890123456789, -2234567890123456789);
INSERT INTO cs2 VALUES (9999999999999999999, 1), (-9999999999999999999, -1);
SELECT "subtract_test_19_0_19_0", d1 - d2 FROM cs2;
INSERT INTO cs3 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs3 VALUES (12345678901234567890123456789012345678, 1234567890123456789), (-12345678901234567890123456789012345678, -2234567890123456789);
INSERT INTO cs3 VALUES (99999999999999999999999999999999999999, 1), (-99999999999999999999999999999999999999, -1);
SELECT "subtract_test_38_0_19_0", d1 - d2 FROM cs3;
INSERT INTO cs4 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs4 VALUES (12345678901234567890123456789012345678, 1234567890), (-12345678901234567890123456789012345678, -2234567890);
INSERT INTO cs4 VALUES (99999999999999999999999999999999999999, 1), (-99999999999999999999999999999999999999, -1);
SELECT "subtract_test_38_0_10_0", d1 - d2 FROM cs4;
INSERT INTO cs5 VALUES (12.34, 56.78), (12.34, -56.78);
INSERT INTO cs5 VALUES (1234567890123456789012345678.9012345678, -1234567890123456789012345678.9012345678), (-1234567890123456789012345678.9012345678, -2234567890123456789012345678.9012345678);
INSERT INTO cs5 VALUES (9999999999999999999999999999.9999999999, 0.0000000001), (-9999999999999999999999999999.9999999999, -0.0000000001);
SELECT "subtract_test_38_10_38_10", d1 - d2 FROM cs5;
INSERT INTO cs6 VALUES (12.34, 56.78), (12.34, -56.78);
INSERT INTO cs6 VALUES (123456789.0123456789, -123456789.0123456789), (-123456789.0123456789, -223456789.0123456789);
INSERT INTO cs6 VALUES (999999999.9999999998, 0.0000000001), (-999999999.9999999998, -0.0000000001);
SELECT "subtract_test_19_10_19_10", d1 - d2 FROM cs6;
INSERT INTO cs7 VALUES (12.34, 56.78), (12.34, -56.78);
INSERT INTO cs7 VALUES (1234567890123456789012345678.9012345678, 123456789.0123456789), (-1234567890123456789012345678.9012345678, -223456789.0123456789);
INSERT INTO cs7 VALUES (9999999999999999999999999999.9999999998, 0.0000000001), (-9999999999999999999999999999.9999999998, -0.0000000001);
SELECT "subtract_test_38_10_19_10", d1 - d2 FROM cs7;
INSERT INTO cs8 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs8 VALUES (1234567890123456789012345678.9012345678, 0.1234567890), (-1234567890123456789012345678.9012345678, -0.2234567890);
INSERT INTO cs8 VALUES (9999999999999999999999999999.9999999998, 0.0000000001), (-9999999999999999999999999999.9999999998, -0.0000000001);
SELECT "subtract_test_38_10_10_10", d1 - d2 FROM cs8;
INSERT INTO cs9 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs9 VALUES (-0.12345678901234567890123456789012345678, 0.12345678901234567890123456789012345678), (-0.12345678901234567890123456789012345678, -0.22345678901234567890123456789012345678);
INSERT INTO cs9 VALUES (0.99999999999999999999999999999999999998, 0.00000000000000000000000000000000000001), (-0.99999999999999999999999999999999999998, -0.00000000000000000000000000000000000001);
SELECT "subtract_test_38_38_38_38", d1 - d2 FROM cs9;
INSERT INTO cs10 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs10 VALUES (0.1234567890123456789, 0.1234567890123456788), (0.1234567890123456789, -0.2234567890123456789);
INSERT INTO cs10 VALUES (0.9999999999999999998, 0.0000000100000000001), (-0.9999999999999999998, -0.0000000100000000001);
SELECT "subtract_test_19_19_19_19", d1 - d2 FROM cs10;
INSERT INTO cs11 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs11 VALUES (0.12345678901234567890123456789012345678, 0.1234567890123456789), (-0.12345678901234567890123456789012345678, -0.2234567890123456789);
INSERT INTO cs11 VALUES (0.99999999999999999999999999999999999998, 0.0000000000000000001), (-0.99999999999999999999999999999999999998, -0.0000000000000000001);
SELECT "subtract_test_38_38_19_19", d1 - d2 FROM cs11;
INSERT INTO cs12 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs12 VALUES (0.12345678901234567890123456789012345678, 0.1234567890), (0.12345678901234567890123456789012345678, -0.2234567890);
INSERT INTO cs12 VALUES (0.99999999999999999999999999999999999998, 0.0000000001), (-0.99999999999999999999999999999999999998, -0.0000000001);
SELECT "subtract_test_38_38_10_10", d1 - d2 FROM cs12;
# with overflow check
# SET columnstore_decimal_overflow_check=on;
# CALL deleteproc();
# INSERT INTO cs1 VALUES (99999999999999999999999999999999999999, -99999999999999999999999999999999999999), (99999999999999999999999999999999999999, -99999999999999999999999999999999999999);
# SELECT "subtract_test_38_0_38_0_overflow", d1 - d2 FROM cs1;
# INSERT INTO cs9 VALUES (0.99999999999999999999999999999999999999, -0.99999999999999999999999999999999999999), (0.99999999999999999999999999999999999999, -0.99999999999999999999999999999999999999);
# SELECT "subtract_test_38_38_38_38_overflow", d1 - d2 FROM cs9;
# Multiplication
# without overflow check
SET columnstore_decimal_overflow_check=off;
CALL deleteproc();
INSERT INTO cs1 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs1 VALUES (1234567890123456789, 1234567890123456789), (1234567890123456789, -2234567890123456789);
INSERT INTO cs1 VALUES (9999999999999999999999999999, 10000000000), (-9999999999999999999999999999, -10000000000);
SELECT "multiply_test_38_0_38_0", d1 * d2 FROM cs1;
INSERT INTO cs2 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs2 VALUES (1234567890123456789, 1234567890123456789), (1234567890123456789, -2234567890123456789);
INSERT INTO cs2 VALUES (9999999999999999998, 1), (-9999999999999999998, -1);
SELECT "multiply_test_19_0_19_0", d1 * d2 FROM cs2;
INSERT INTO cs3 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs3 VALUES (1234567890123456789, 1234567890123456789), (1234567890123456789, -2234567890123456789);
INSERT INTO cs3 VALUES (99999999999999999999999999999999999998, 1), (-99999999999999999999999999999999999998, -1);
SELECT "multiply_test_38_0_19_0", d1 * d2 FROM cs3;
INSERT INTO cs4 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs4 VALUES (1234567890123456789012345678, 1234567890), (1234567890123456789012345678, -2234567890);
INSERT INTO cs4 VALUES (99999999999999999999999999998, 1000000000), (-99999999999999999999999999998, -1000000000);
SELECT "multiply_test_38_0_10_0", d1 * d2 FROM cs4;
INSERT INTO cs5 VALUES (12.34, 56.78), (12.34, -56.78);
INSERT INTO cs5 VALUES (123456789.0123456789, 123456789.0123456789), (123456789.0123456789, -223456789.0123456789);
INSERT INTO cs5 VALUES (999999999.9999999998, 0.0000000001), (-999999999.9999999998, -0.0000000001);
SELECT "multiply_test_38_10_38_10", d1 * d2 FROM cs5;
INSERT INTO cs6 VALUES (12.34, 56.78), (12.34, -56.78);
INSERT INTO cs6 VALUES (123456789.0123456789, 123456789.0123456789), (123456789.0123456789, -223456789.0123456789);
INSERT INTO cs6 VALUES (999999999.9999999998, 0.0000000001), (-999999999.9999999998, -0.0000000001);
SELECT "multiply_test_19_10_19_10", d1 * d2 FROM cs6;
INSERT INTO cs7 VALUES (12.34, 56.78), (12.34, -56.78);
INSERT INTO cs7 VALUES (123456789.0123456789, 123456789.0123456789), (123456789.0123456789, -223456789.0123456789);
INSERT INTO cs7 VALUES (999999999.9999999998, 0.0000000001), (-999999999.9999999998, -0.0000000001);
SELECT "multiply_test_38_10_19_10", d1 * d2 FROM cs7;
INSERT INTO cs8 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs8 VALUES (123456789012345678.9012345678, 0.1234567890), (123456789012345678.9012345678, -0.2234567890);
INSERT INTO cs8 VALUES (999999999999999999.9999999998, 0.0000000001), (-999999999999999999.9999999998, -0.0000000001);
SELECT "multiply_test_38_10_10_10", d1 * d2 FROM cs8;
INSERT INTO cs9 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs9 VALUES (0.1234567890123456789, 0.1234567890123456789), (0.1234567890123456789, -0.2234567890123456789);
INSERT INTO cs9 VALUES (0.9999999999999999998, 0.0000000000000000001), (-0.9999999999999999998, -0.0000000000000000001);
SELECT "multiply_test_38_38_38_38", d1 * d2 FROM cs9;
INSERT INTO cs10 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs10 VALUES (0.1234567890123456789, 0.1234567890123456789), (0.1234567890123456789, -0.2234567890123456789);
INSERT INTO cs10 VALUES (0.9999999999999999998, 0.0000000000000000001), (-0.9999999999999999998, -0.0000000000000000001);
SELECT "multiply_test_19_19_19_19", d1 * d2 FROM cs10;
INSERT INTO cs11 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs11 VALUES (0.1234567890123456789, 0.123456789), (0.1234567890123456789, -0.223456789);
INSERT INTO cs11 VALUES (0.9999999999999999998, 0.000000001), (-0.9999999999999999998, -0.000000001);
SELECT "multiply_test_38_38_19_19", d1 * d2 FROM cs11;
INSERT INTO cs12 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs12 VALUES (0.1234567890123456789012345678, 0.12345), (0.1234567890123456789012345678, -0.22345);
INSERT INTO cs12 VALUES (0.9999999999999999999999999998, 0.00001), (-0.9999999999999999999999999998, -0.00001);
SELECT "multiply_test_38_38_10_10", d1 * d2 FROM cs12;
# with overflow check
# SET columnstore_decimal_overflow_check=on;
# CALL deleteproc();
# INSERT INTO cs1 VALUES (99999999999999999999999999999999999999, 99999999999999999999999999999999999999), (-99999999999999999999999999999999999999, -99999999999999999999999999999999999999);
# INSERT INTO cs1 VALUES (12345678901234567890123456789012345678, 12345678901234567890123456789012345678), (12345678901234567890123456789012345678, -22345678901234567890123456789012345678);
# SELECT "multiply_test_38_0_38_0_overflow", d1 * d2 FROM cs1;
# INSERT INTO cs3 VALUES (12345678901234567890123456789012345678, 1234567890123456789), (12345678901234567890123456789012345678, -2234567890123456789);
# SELECT "multiply_test_38_0_19_0", d1 * d2 FROM cs3;
# INSERT INTO cs4 VALUES (12345678901234567890123456789012345678, 1234567890), (12345678901234567890123456789012345678, -2234567890);
# SELECT "multiply_test_38_0_10_0", d1 * d2 FROM cs4;
# INSERT INTO cs5 VALUES (1234567890123456789012345678.9012345678, 1234567890123456789012345678.9012345678), (1234567890123456789012345678.9012345678, -2234567890123456789012345678.9012345678);
# SELECT "multiply_test_38_10_38_10", d1 * d2 FROM cs5;
# INSERT INTO cs7 VALUES (1234567890123456789012345678.9012345678, 123456789.0123456789), (1234567890123456789012345678.9012345678, -223456789.0123456789);
# SELECT "multiply_test_38_10_19_10", d1 * d2 FROM cs7;
# INSERT INTO cs8 VALUES (1234567890123456789012345678.9012345678, 0.1234567890), (1234567890123456789012345678.9012345678, -0.2234567890);
# SELECT "multiply_test_38_10_10_10", d1 * d2 FROM cs8;
# INSERT INTO cs9 VALUES (0.12345678901234567890123456789012345678, 0.12345678901234567890123456789012345678), (0.12345678901234567890123456789012345678, -0.22345678901234567890123456789012345678);
# SELECT "multiply_test_38_38_38_38_overflow", d1 * d2 FROM cs9;
# INSERT INTO cs11 VALUES (0.12345678901234567890123456789012345678, 0.1234567890123456789), (0.12345678901234567890123456789012345678, -0.2234567890123456789);
# SELECT "multiply_test_38_38_19_19", d1 * d2 FROM cs11;
# INSERT INTO cs12 VALUES (0.12345678901234567890123456789012345678, 0.1234567890), (0.12345678901234567890123456789012345678, -0.2234567890);
# SELECT "multiply_test_38_38_10_10", d1 * d2 FROM cs12;
# Division
# without overflow check
SET columnstore_decimal_overflow_check=off;
CALL deleteproc();
INSERT INTO cs1 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs1 VALUES (12345678901234567890123456789012345678, 4567890123456789), (12345678901234567890123456789012345678, -223456789012345678901234567890123456);
INSERT INTO cs1 VALUES (44444444444444444444444444444444444444, 10000000000), (-44444444444444444444444444444444, 1000);
SELECT "divide_test_38_0_38_0", d1 / d2, d2 / d1 FROM cs1;
INSERT INTO cs2 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs2 VALUES (1234567890123456789, 4567890123456789), (1234567890123456789, -223456789012345678);
INSERT INTO cs2 VALUES (4444444444444444444, 10000000000), (-44444444444444444, -1000);
SELECT "divide_test_19_0_19_0", d1 / d2, d2 / d1 FROM cs2;
INSERT INTO cs3 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs3 VALUES (12345678901234567890123456789012345678, 4567890123456789), (12345678901234567890123456789012345678, -2234567890123456);
INSERT INTO cs3 VALUES (4444444444444444444, 10000000000), (-44444444444444444, -1000);
SELECT "divide_test_38_0_19_0", d1 / d2, d2 / d1 FROM cs3;
INSERT INTO cs4 VALUES (1234, 5678), (1234, -5678);
INSERT INTO cs4 VALUES (12345678901234567890123456789012345678, 4567890123), (12345678901234567890123456789012345678, -2234567890);
INSERT INTO cs4 VALUES (4444444444444444444, 1000000000), (-44444444444444444, -1000);
SELECT "divide_test_38_0_10_0", d1 / d2, d2 / d1 FROM cs4;
INSERT INTO cs5 VALUES (12.34, 56.78), (12.34, -56.78);
INSERT INTO cs5 VALUES (1234567890123456789012345678.9012345678, 4567890123456789.0123456789), (1234567890123456789012345678.9012345678, -22345678901234567890123456.7890123456);
INSERT INTO cs5 VALUES (4444444444444444444444444444.4444444444, 10000000000.0), (-4444444444444444444444.4444444444, 1000.0);
SELECT "divide_test_38_10_38_10", d1 / d2, d2 / d1 FROM cs5;
INSERT INTO cs6 VALUES (12.34, 56.78), (12.34, -56.78);
INSERT INTO cs6 VALUES (123456789.0123456789, 456789.0123456789), (123456789.0123456789, -22345678.9012345678);
INSERT INTO cs6 VALUES (444444444.4444444444, 100000000.0), (-4444444.4444444444, -1000.0);
SELECT "divide_test_19_10_19_10", d1 / d2, d2 / d1 FROM cs6;
INSERT INTO cs7 VALUES (12.34, 56.78), (12.34, -56.78);
INSERT INTO cs7 VALUES (1234567890123456789012345678.9012345678, 456789012.0123456789), (1234567890123456789012345678.9012345678, -223456789.7890123456);
INSERT INTO cs7 VALUES (444444444.4444444444, 100000000.0), (-4444444.4444444444, -1000.0);
SELECT "divide_test_38_10_19_10", d1 / d2, d2 / d1 FROM cs7;
INSERT INTO cs8 VALUES (12.34, 0.5678), (12.34, -0.5678);
INSERT INTO cs8 VALUES (1234567890123456789.9012345678, 0.4567890123), (1234567890123456789.9012345678, -0.2234567890);
INSERT INTO cs8 VALUES (444444444.4444444444, 0.10000000), (-4444444.4444444444, -0.10000);
SELECT "divide_test_38_10_10_10", d1 / d2, d2 / d1 FROM cs8;
INSERT INTO cs9 VALUES (0.0000000000000000000000000001234, 0.000000000000000000005678), (0.000000000000000000000000001234, -0.000000000000000000005678);
INSERT INTO cs9 VALUES (0.00000000000000000004567890123456789, 0.12345678901234567890123456789012345678), (-0.000000000000000002234567890123456789, 0.12345678901234567890123456789012345678);
INSERT INTO cs9 VALUES (0.000000000000010000000000, 0.44444444444444444444444444444444444444), (0.00000000000001000, -0.44444444444444444444444444444444);
# d2 / d1 will result in an overflow
SELECT "divide_test_38_38_38_38", d1 / d2 FROM cs9;
INSERT INTO cs10 VALUES (0.1234, 0.5678), (0.1234, -0.5678);
INSERT INTO cs10 VALUES (0.1234567890123456789, 0.4567890123456789), (0.1234567890123456789, -0.223456789012345678);
INSERT INTO cs10 VALUES (0.4444444444444444444, 0.10000000000), (-0.44444444444444444, -0.1000);
SELECT "divide_test_19_19_19_19", d1 / d2, d2 / d1 FROM cs10;
# we lose a few decimal places in the end in the following test,
# so commenting out for now
# INSERT INTO cs11 VALUES (0.00000000000000000000001234, 0.0000000000000005678), (0.0000000000000000000001234, -0.0000000000000005678);
# INSERT INTO cs11 VALUES (0.000000000000004567890123456789, 0.1234567890123456789), (-0.0000000000002234567890123456789, 0.1234567890123456789);
# INSERT INTO cs11 VALUES (0.0000000010000000000, 0.44444444444444444444), (0.000000001000, -0.4444444444444444444);
# SELECT "divide_test_38_38_19_19", d1 / d2, d2 / d1 FROM cs11;
INSERT INTO cs12 VALUES (0.0000000000000000000000000000001234, 0.5678), (0.0000000000000000000000000000001234, -0.5678);
INSERT INTO cs12 VALUES (0.00000000001234567890123456789012345678, 0.4567890123), (0.00000000001234567890123456789012345678, -0.2234567890);
INSERT INTO cs12 VALUES (0.000000000000000000004444444444444444444, 0.1000000000), (-0.0000000000000000000044444444444444444, -0.1000);
# d2 / d1 will result in an overflow
SELECT "divide_test_38_38_10_10", d1 / d2 FROM cs12;
# with overflow check
# we cannot trigger the division overflow of lhs == Decimal::minInt128 && rhs == -1
# since lhs = -170141183460469231731687303715884105728 cannot be inserted
# into a decimal(38) column
# Clean UP
DROP PROCEDURE IF EXISTS deleteproc;
DROP DATABASE mcol641_arithmetic_db;
View Git Blame Copy Permalink