bug: union clause return incorrect result set

davidshiz commented 1 year ago

Have you read the Contributing Guidelines on issues?

[X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

[X] I confirm there is no existing issue for this

Describe the problem

mysql> SELECT d1, d1, d2 FROM cs1 UNION SELECT d2, d3, d3 FROM cs1;
+-----------------+-----------------------+----------------------+
| d1              | d1                    | d2                   |
+-----------------+-----------------------+----------------------+
|  125.0000000000 | -59.46744073709551616 |  1.25000000000000000 |
| -125.0000000000 |  59.46744073709551616 | -1.25000000000000000 |
|    1.2500000000 |   0.12500000000000000 |  0.12500000000000000 |
|   -1.2500000000 |  -0.12500000000000000 | -0.12500000000000000 |
+-----------------+-----------------------+----------------------+
4 rows in set (0.00 sec)

Expected behavior

mysql> SELECT d1, d1, d2 FROM cs1 UNION SELECT d2, d3, d3 FROM cs1;
+-----------------+------------------------+----------------------+
| d1              | d1                     | d2                   |
+-----------------+------------------------+----------------------+
|  125.0000000000 |  125.00000000000000000 |  1.25000000000000000 |
| -125.0000000000 | -125.00000000000000000 | -1.25000000000000000 |
|    1.2500000000 |    0.12500000000000000 |  0.12500000000000000 |
|   -1.2500000000 |   -0.12500000000000000 | -0.12500000000000000 |
+-----------------+------------------------+----------------------+
4 rows in set (0.00 sec)

How To Reproduce

CREATE TABLE cs1 (d1 DECIMAL(17), d2 DECIMAL(17,10), d3 DECIMAL(17,17));
INSERT INTO cs1 VALUES (125, 1.25, 0.125);
INSERT INTO cs1 VALUES (-125, -1.25, -0.125);
SELECT d1, d1, d2 FROM cs1 UNION SELECT d2, d3, d3 FROM cs1;

Environment

[root@stonedb-test bin]# ./mysqld --version
./mysqld  Ver 5.7.36-StoneDB-v1.0.1 for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: f180323
        Last commit time: Date:   Wed Jun 14 15:44:47 2023 +0800
        Build time: Date: Thu Jun 15 07:27:24 UTC 2023

Are you interested in submitting a PR to solve the problem?

[ ] Yes, I will!

RingsC commented 1 year ago

~~ it's about data precision. ~~ with duplication columns, it seems output a correct result.

mysql>  SELECT d1,  d2 FROM cs1 UNION SELECT d2, d3 FROM cs1;
+-----------------+----------------------+
| d1              | d2                   |
+-----------------+----------------------+
|  125.0000000000 |  1.25000000000000000 |
| -125.0000000000 | -1.25000000000000000 |
|    1.2500000000 |  0.12500000000000000 |
|   -1.2500000000 | -0.12500000000000000 |
+-----------------+----------------------+
4 rows in set (0.00 sec)

RingsC commented 1 year ago

add query table: ./test/cs1 T:-1 = TABLE_ALIAS(T:0,"cs1") T:-2 = TMP_TABLE(T:4294967295) VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:0)) A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,"d1","ALL") A:-2 = T:-2.ADD_COLUMN(VC:-2.0,LIST,"d1","ALL") VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:1)) A:-3 = T:-2.ADD_COLUMN(VC:-2.1,LIST,"d2","ALL") T:-2.APPLY_CONDS() T:-3 = TABLE_ALIAS(T:0,"cs1") T:-4 = TMP_TABLE(T:4294967293) VC:-4.0 = CREATE_VC(T:-4,PHYS_COL(T:-3,A:1)) A:-1 = T:-4.ADD_COLUMN(VC:-4.0,LIST,"d2","ALL") VC:-4.1 = CREATE_VC(T:-4,PHYS_COL(T:-3,A:2)) A:-2 = T:-4.ADD_COLUMN(VC:-4.1,LIST,"d3","ALL") A:-3 = T:-4.ADD_COLUMN(VC:-4.1,LIST,"d3","ALL") T:-4.APPLY_CONDS() T:-2 = UNION(T:-2,T:-4,0) RESULT(T:-2) Result: 4 Costtime(ms): 2

RingsC commented 1 year ago

the tye of the 3rd of output column is : decimal(17,17). and in union operation. The column type of output is determined by combination of two types of columns. For example: col1: decimal(17), and the col3 is decmial(17,10), and the type of output column of union is decmial(17,17). But on stonedb, now, the precision of decimal type cannot be higher than 18. Therefore, the 59.46744073709551616 means that it overflows

the val of the output is : 125 * 100000000000000000. Therefore, it overflows with type of int64_t.

On StoneDB, the precision for DECIMAL numbers cannot be higher than 18. For example, if you specify decimal(19) in your code, an error will be reported. DECIMAL(6, 2) indicates that up to 6 places are supported at the left of the decimal and up to 2 at the right, and thus the value range is [-9999.99, 9999.99].

ref: https://stonedb.io/docs/SQL-reference/data-types

mysql> INSERT INTO cs1 VALUES (-126, -1.26, 125);
ERROR 1264 (22003): Out of range value for column 'd3' at row 1

davidshiz commented 1 year ago

It is recommended to give a prompt, prompting super precision after union

stoneatom / stonedb