[MCOL-193] || operator fails 528 of 1229 tests when SQL_MODE=pipes_as_concat Created: 2016-06-24  Updated: 2023-07-02  Resolved: 2023-07-02

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.2.4
Fix Version/s: Icebox

Type: Bug Priority: Minor
Reporter: Justin Swanhart (Inactive) Assignee: alexey vorovich (Inactive)
Resolution: Won't Do Votes: 0
Labels: None

Epic Link: ColumnStore Compatibility Improvements

 Description   

git clone https://github.com/greenlion/columnstore_tests.git
sudo yum install php-cli php-mysql php-pear
cd columnstore_tests
git checkout pipe_concat
php run_tests --record # record results against MyISAM table
php run_tests --debug | tee out.txt #compare to CS table
Examine out.txt and you will find that the operator returns wrong results for CS compared to MyISAM in 528 of 1229 tests.

 Comments   
Comment by David Hall (Inactive) [ 2019-07-11 ]

There appear to be a number of problems here:

(1)The first appears to be caused by the result buffer being too small. After loading the table as above, run:

set SQL_MODE=pipes_as_concat;
select seq, i8, -128 || i8 from fact where seq = 29;
-------------------------------------------------

seq i8 -128 i8

-------------------------------------------------

29 -9223372036854775800 -128-922337203685477580

-------------------------------------------------
1 row in set (0.059 sec)

You can easily see that a char has been dropped from the result.
Similarly, concat with a positive number:
select seq, i8, 126 || i8 from fact where seq = 29;
------------------------------------------------

seq i8 126 i8

------------------------------------------------

29 -9223372036854775800 126-922337203685477580

------------------------------------------------
1 row in set (0.028 sec)

Or a string:
select seq, i8, '255' || i8 from fact where seq = 29;
------------------------------------------------

seq i8 '255' i8

------------------------------------------------

29 -9223372036854775800 255-922337203685477580

------------------------------------------------
1 row in set (0.028 sec)

The size of the item doesn't matter:
select seq, i8, -555.55555 || i8 from fact where seq = 29;
-------------------------------------------------------

seq i8 -555.55555 i8

-------------------------------------------------------

29 -9223372036854775800 -555.55555-922337203685477580

-------------------------------------------------------
1 row in set (0.027 sec)

(2) FLOAT. When the column is a float, it converts to scientific notation, whether it needs to or not:
select seq, f1, -128 || f1 from fact where seq = 2;
----------------------

seq f1 -128 f1

----------------------

2 -128 -128-1.28e02

----------------------
1 row in set (0.029 sec)

This is different than InnoDB, where the result is '-128-128'

(3) FLOAT(2,1)Here we have truncation for some reason:
select seq, f2, 127 || f2 from fact where seq=2;
-------------------

seq f2 127 f2

-------------------

2 -9.9 127-9

-------------------

(4) FLOAT(23,4) which converts to scientific notation as in (2) FLOAT above. Other float types such as(53,20) do this also.

select seq, f23, 127 || f23 from fact where seq=2;
--------------------------

seq f23 127 f23

--------------------------

2 -128.0000 127-1.28e02

--------------------------

DOUBLE misbehaves the same as FLOAT.

Comment by Todd Stoffel (Inactive) [ 2023-07-02 ]

Test suite is based on php5 and no longer works.

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