[MCOL-4273] SELECT...union/intersect/except...ORDER BY results are not consistent Created: 2020-08-25  Updated: 2023-10-27  Resolved: 2023-10-27

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: None
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: susil.behera Assignee: Leonid Fedorov
Resolution: Won't Fix Votes: 0
Labels: None
Environment:

Server version: 10.5.6
MCS version: 1.5.4-1
Drone buildNo: 463


 Description   

SELECT...UNION...ORDER BY results are not consistent on both the engines Columnstore and Innodb. Due to this some tests are failing.

MariaDB [test]> CREATE TABLE t1 (t1_int INT, t1_char CHAR(5))ENGINE=Columnstore;
Query OK, 0 rows affected (0.127 sec)

MariaDB [test]> CREATE TABLE t2 (t2_int INT, t2_char CHAR(5))ENGINE=Columnstore;
Query OK, 0 rows affected (0.138 sec)

MariaDB [test]> INSERT INTO t1 VALUES (NULL, ''),(1, 'aaa'),(2, 'aaa'),(3, 'ccc'),(4, 'ddd'),(5, 'aaa'),(6, ''),(7, 'eee');
Query OK, 8 rows affected (0.824 sec)
Records: 8 Duplicates: 0 Warnings: 0

MariaDB [test]> INSERT INTO t2 VALUES (NULL, ''),(1, 'eee'),(3, 'ccc'),(5, 'jjj'),(6, ''),(7, 'lll'),(9, 'eee'),(11, 'nnn');
Query OK, 8 rows affected (0.503 sec)
Records: 8 Duplicates: 0 Warnings: 0

Run 1 -
MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 UNION SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

ddd Character
eee Character
NULL Character
aaa Character
ccc Character
5 Integer
6 Integer
7 Integer
NULL Integer
9 Integer
1 Integer
11 Integer
3 Integer

------------------+
13 rows in set (0.153 sec)

Run 2 -
MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 UNION SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

ddd Character
eee Character
NULL Character
aaa Character
ccc Character
5 Integer
6 Integer
7 Integer
NULL Integer
9 Integer
1 Integer
11 Integer
3 Integer

------------------+
13 rows in set (0.026 sec)

Run 3 -
MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 UNION SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

ddd Character
eee Character
NULL Character
aaa Character
ccc Character
5 Integer
6 Integer
7 Integer
NULL Integer
9 Integer
1 Integer
11 Integer
3 Integer

------------------+
13 rows in set (0.025 sec)

Run 4 -
MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 UNION SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

ddd Character
eee Character
NULL Character
aaa Character
ccc Character
5 Integer
6 Integer
7 Integer
NULL Integer
9 Integer
1 Integer
11 Integer
3 Integer

------------------+
13 rows in set (0.017 sec)

Run 5 -
MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 UNION SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

ddd Character
eee Character
NULL Character
aaa Character
ccc Character
5 Integer
6 Integer
7 Integer
NULL Integer
9 Integer
1 Integer
11 Integer
3 Integer

------------------+
13 rows in set (0.349 sec)

Run 6 -
MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 UNION SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

ddd Character
eee Character
NULL Character
aaa Character
ccc Character
5 Integer
6 Integer
7 Integer
NULL Integer
9 Integer
1 Integer
11 Integer
3 Integer

------------------+
13 rows in set (0.022 sec)

Run 7 -
MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 UNION SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

ddd Character
eee Character
NULL Character
aaa Character
ccc Character
5 Integer
6 Integer
7 Integer
NULL Integer
9 Integer
1 Integer
11 Integer
3 Integer

------------------+
13 rows in set (0.018 sec)

Run 8 (here it started giving different result) -
MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 UNION SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

ccc Character
ddd Character
eee Character
NULL Character
aaa Character
5 Integer
6 Integer
7 Integer
NULL Integer
9 Integer
1 Integer
11 Integer
3 Integer

------------------+
13 rows in set (0.019 sec)

Run 9 -
MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 UNION SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

aaa Character
ccc Character
ddd Character
eee Character
NULL Character
9 Integer
1 Integer
11 Integer
3 Integer
5 Integer
6 Integer
7 Integer
NULL Integer

------------------+
13 rows in set (0.022 sec)

Run 10 -
MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 UNION SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

eee Character
NULL Character
aaa Character
ccc Character
ddd Character
7 Integer
NULL Integer
9 Integer
1 Integer
11 Integer
3 Integer
5 Integer
6 Integer

------------------+
13 rows in set (0.024 sec)

 Comments   
Comment by susil.behera [ 2020-08-25 ]

INTERSECT results are also inconsistent.

MariaDB [test]> SELECT t1_char, t1_int FROM t1 INTERSECT SELECT t2_char, t2_int FROM t2 ORDER BY 1;
---------------+

t1_char t1_int

---------------+

NULL 6
NULL NULL
ccc 3

---------------+
3 rows in set (0.143 sec)

MariaDB [test]> SELECT t1_char, t1_int FROM t1 INTERSECT SELECT t2_char, t2_int FROM t2 ORDER BY 1;
---------------+

t1_char t1_int

---------------+

NULL NULL
NULL 6
ccc 3

---------------+
3 rows in set (0.026 sec)

EXCEPT results are also inconsistent.

MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 EXCEPT SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

ccc Character
ddd Character
eee Character
NULL Character
aaa Character

------------------+
5 rows in set (0.023 sec)

MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 EXCEPT SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

ccc Character
ddd Character
eee Character
NULL Character
aaa Character

------------------+
5 rows in set (0.020 sec)

MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 EXCEPT SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

NULL Character
aaa Character
ccc Character
ddd Character
eee Character

------------------+
5 rows in set (0.020 sec)

UNION ALL results are also inconsistent.

MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 UNION ALL SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

aaa Character
NULL Character
NULL Character
aaa Character
eee Character
aaa Character
ccc Character
ddd Character
11 Integer
3 Integer
5 Integer
6 Integer
7 Integer
NULL Integer
9 Integer
1 Integer

------------------+
16 rows in set (0.023 sec)

MariaDB [test]> SELECT t1_char, 'Character' AS Table1 FROM t1 UNION ALL SELECT t2_int, 'Integer' AS Table2 FROM t2 ORDER BY 2 ASC;
------------------+

t1_char Table1

------------------+

NULL Character
aaa Character
eee Character
aaa Character
ccc Character
ddd Character
aaa Character
NULL Character
6 Integer
7 Integer
NULL Integer
9 Integer
1 Integer
11 Integer
3 Integer
5 Integer

------------------+
16 rows in set (0.019 sec)

MariaDB [test]>

Comment by Roman [ 2020-09-02 ]

susil.behera MCS doesn't support nor EXCEPT nor INTERCEPT logic.

Generated at Thu Feb 08 02:49:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.