[MCOL-1098] Implement/test new server ORDER BY implementation Created: 2017-12-11  Updated: 2023-11-06  Resolved: 2021-04-19

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.2.1
Fix Version/s: 5.6.1

Type: Bug Priority: Major
Reporter: Andrew Hutchings (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 1
Labels: Compatibility

Issue Links:
Relates
relates to MDEV-14500 filesort to support engines with slow... Closed
Epic Link: ColumnStore Compatibility Improvements

 Description   

MDEV-14500 implements ORDER BY in a way that removes our need for postion()/rnd_pos(). If there are any implementation tasks for this it should be tracked here.

Description: Hi,
during the execution of a simple query an a simple 2000 rows Columnstore table, we have this exception:

Error Code: 1815
Internal error: IDB-2015: Sorting length exceeded. Session variable max_length_for_sort_data needs to be set higher

To reproduce (quote from customer ticket)

Create Table: CREATE TABLE `campagna_web_codice` (
`cwc_id` int(11) DEFAULT NULL,
`cwc_cw_id` int(11) DEFAULT NULL,
`cwc_cd_id` int(11) DEFAULT NULL,
`cwc_codice` varchar(30) DEFAULT NULL,
`cwc_va_id` int(11) DEFAULT NULL,
`cwc_prezzo` decimal(12,2) DEFAULT NULL,
`cwc_prezzoListino` decimal(12,2) DEFAULT NULL,
`cwc_prezzoSpedizione` decimal(8,2) DEFAULT NULL,
`cwc_in_id` int(11) DEFAULT NULL,
`cwc_na_id` int(11) DEFAULT NULL,
`cwc_desc` varchar(100) DEFAULT NULL,
`cwc_datainizio` date DEFAULT NULL,
`cwc_datafine` date DEFAULT NULL,
`cwc_mo_id` int(11) DEFAULT NULL,
`cwc_ma_id` int(11) DEFAULT NULL,
`cwc_pd_id` int(11) DEFAULT NULL,
`cwc_set_id` int(11) DEFAULT NULL,
`cwc_mr_id` int(11) DEFAULT NULL,
`cwc_ca_id` int(11) DEFAULT NULL,
`cwc_fa_id` int(11) DEFAULT NULL,
`cwc_ti_id` int(11) DEFAULT NULL,
`cwc_azws` longtext DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8

The query is as simple as :

select * from dmc_dg_ita_1.campagna_web_codice order by 1 DESC limit 0, 1000;

we tried to raise max_length_for_sort_data up to 51200 (1024 is the default value), but with no success.

If we delete "cwc_azws" row (longtext), we get the result correctly.

 Comments   
Comment by Daniel Lee (Inactive) [ 2021-04-19 ]

Build verified: 5.6.1 (Drone #2191)

Tested on a VM that has 8gb memory.

Reproduced the reported issue in release 1.2.6-1
The issue no longer happens in releases 1.5.0 and 5.6.1.

test table with char(100) and long text columns:

lineitem | CREATE TABLE `lineitem` (
 
  `l_orderkey` int(11) DEFAULT NULL,
 
  `l_partkey` int(11) DEFAULT NULL,
 
  `l_suppkey` int(11) DEFAULT NULL,
 
  `l_linenumber` bigint(20) DEFAULT NULL,
 
  `l_quantity` decimal(12,2) DEFAULT NULL,
 
  `l_extendedprice` decimal(12,2) DEFAULT NULL,
 
  `l_discount` decimal(12,2) DEFAULT NULL,
 
  `l_tax` decimal(12,2) DEFAULT NULL,
 
  `l_returnflag` char(1) DEFAULT NULL,
 
  `l_linestatus` char(1) DEFAULT NULL,
 
  `l_shipdate` date DEFAULT NULL,
 
  `l_commitdate` date DEFAULT NULL,
 
  `l_receiptdate` date DEFAULT NULL,
 
  `l_shipinstruct` char(25) DEFAULT NULL,
 
  `l_shipmode` char(100) DEFAULT NULL,
 
  `l_comment` longtext DEFAULT NULL
 
) ENGINE=Columnstore DEFAULT CHARSET=utf8

Loaded over 6 millions rows, way more than the 2000 rows the user had.

[centos7:root~]# cpimport tpch1 lineitem /data/qa/source/dbt3/1g/lineitem.tbl 
Locale is : C
 
 
 
Using table OID 3050 as the default JOB ID
 
Input file(s) will be read from : /root
 
Job description file : /usr/local/mariadb/columnstore/data/bulk/tmpjob/3050_D20210419_T223927_S760746_Job_3050.xml
 
Log file for this job: /usr/local/mariadb/columnstore/data/bulk/log/Job_3050.log
 
2021-04-19 22:39:27 (14790) INFO : successfully loaded job file /usr/local/mariadb/columnstore/data/bulk/tmpjob/3050_D20210419_T223927_S760746_Job_3050.xml
 
2021-04-19 22:39:27 (14790) INFO : Job file loaded, run time for this step : 0.0886271 seconds
 
2021-04-19 22:39:27 (14790) INFO : PreProcessing check starts
 
2021-04-19 22:39:27 (14790) INFO : input data file /data/qa/source/dbt3/1g/lineitem.tbl
 
2021-04-19 22:39:27 (14790) INFO : PreProcessing check completed
 
2021-04-19 22:39:27 (14790) INFO : preProcess completed, run time for this step : 0.0761371 seconds
 
2021-04-19 22:39:27 (14790) INFO : No of Read Threads Spawned = 1
 
2021-04-19 22:39:27 (14790) INFO : No of Parse Threads Spawned = 3
 
2021-04-19 22:39:41 (14790) INFO : For table tpch1.lineitem: 6001215 rows processed and 6001215 rows inserted.
 
2021-04-19 22:39:41 (14790) INFO : Bulk load completed, total run time : 14.2421 seconds

1.2.6-1

MariaDB [tpch1]> select * from lineitem order by 1 DESC LIMIT 0, 1000;
 
ERROR 1815 (HY000): Internal error: IDB-2015: Sorting length exceeded. Session variable max_length_for_sort_data needs to be set higher.
 
 
 
MariaDB [tpch1]> show variables like "%max_length_for_sort_data%";
 
+--------------------------+-------+
 
| Variable_name            | Value |
 
+--------------------------+-------+
 
| max_length_for_sort_data | 1024  |
 
+--------------------------+-------+
 
1 row in set (0.004 sec)
 
 
 
MariaDB [tpch1]> set max_length_for_sort_data=51200;
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [tpch1]> select * from lineitem order by 1 DESC LIMIT 0, 1000;
 
ERROR 1815 (HY000): Internal error: IDB-2015: Sorting length exceeded. Session variable max_length_for_sort_data needs to be set higher.
 
MariaDB [tpch1]>

5.6.1

MariaDB [(none)]> use tpch1
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Database changed
 
MariaDB [tpch1]> show variables like "%max_length_for_sort_data%";
 
+--------------------------+-------+
 
| Variable_name            | Value |
 
+--------------------------+-------+
 
| max_length_for_sort_data | 1024  |
 
+--------------------------+-------+
 
1 row in set (0.003 sec)
 
 
 
MariaDB [tpch1]> select count(*) from lineitem;
 
+----------+
 
| count(*) |
 
+----------+
 
|  6001215 |
 
+----------+
 
1 row in set (0.216 sec)
 
 
 
MariaDB [tpch1]> select * from lineitem order by 1 DESC LIMIT 0, 1000;
 
+------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+-------------------+------------+---------------------------------------------+
 
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct    | l_shipmode | l_comment                                   |
 
+------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+-------------------+------------+---------------------------------------------+
 
|    6000000 |     96127 |      6128 |            2 |      28.00 |        31447.36 |       0.01 |  0.02 | N            | O            | 1996-09-22 | 1996-10-01   | 1996-10-21    | NONE              | AIR        | ooze furiously about the pe                 |
 
|    6000000 |     32255 |      2256 |            1 |       5.00 |         5936.25 |       0.04 |  0.03 | N            | O            | 1996-11-02 | 1996-11-19   | 1996-12-01    | TAKE BACK RETURN  | MAIL       | carefully                                   |
 
|    5999975 |     37131 |      2138 |            3 |      18.00 |        19226.34 |       0.04 |  0.01 | A            | F            | 1993-11-17 | 1993-08-28   | 1993-12-08    | DELIVER IN PERSON | FOB        | , quick deposits. ironic, unusual deposi    |
 
|    5999975 |      7272 |      2273 |            1 |      32.00 |        37736.64 |       0.07 |  0.01 | R            | F            | 1993-10-07 | 1993-09-30   | 1993-10-21    | COLLECT COD       | REG AIR    | tructions. excu                             |
 
|    5999975 |      6452 |      1453 |            2 |       7.00 |         9509.15 |       0.04 |  0.00 | A            | F            | 1993-11-02 | 1993-09-23   | 1993-11-19    | DELIVER IN PERSON | SHIP       | lar pinto beans aft                         |
 
.
 
.
 
.
 
|    5998976 |    102642 |      2643 |            6 |       5.00 |         8223.20 |       0.10 |  0.05 | N            | O            | 1998-06-03 | 1998-07-07   | 1998-06-26    | COLLECT COD       | RAIL       | thinly regular deposits sleep acro          |
 
|    5998976 |    120183 |       184 |            3 |      13.00 |        15641.34 |       0.06 |  0.02 | N            | O            | 1998-07-27 | 1998-08-16   | 1998-08-03    | COLLECT COD       | AIR        | uests mainta                                |
 
|    5998976 |    156782 |      4328 |            1 |      47.00 |        86422.66 |       0.04 |  0.00 | N            | O            | 1998-09-24 | 1998-07-31   | 1998-10-22    | COLLECT COD       | MAIL       | g to the quickly regular theo               |
 
|    5998976 |    105475 |       496 |            4 |       3.00 |         4441.41 |       0.03 |  0.04 | N            | O            | 1998-07-16 | 1998-07-18   | 1998-07-29    | COLLECT COD       | TRUCK      | press requests detect.                      |
 
|    5998951 |     55931 |      8437 |            1 |      10.00 |        18869.30 |       0.03 |  0.02 | A            | F            | 1992-10-18 | 1992-10-06   | 1992-10-30    | COLLECT COD       | FOB        | st furiously across the regular platelets   |
 
+------------+-----------+-----------+--------------+------------+-----------------+------------+-------+--------------+--------------+------------+--------------+---------------+-------------------+------------+---------------------------------------------+
 
1000 rows in set (2.581 sec)

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