It needs to be documented in known issues and limitation that "max_length_for_sort_data " should not be set

Please validate David Hall's analysis on max_length_for_sort_data

This test case, as well as this "set max_length_for_sort_data = 4096;" statement, worked in InfiniDB and MariaDB. It is an issue in ColumnStore. The issue needs to be fixed, instead of documenting it. Without being able to set ma_length_for_sort_data, lots of queries would failed.

I have put in a fix that should eliminate the need to set max_length_for_sort_data in most cases. However, the problem here is that 4096 is now too small for this query, where it used to be sufficient. I believe the only thing that should be done is a change to the test to something a bit larger until it works. 4096 is too small. It may be that setting it smaller than the default is a problem, don't know for sure, but I think it should be OK.

I know we just started using Jira. In this ticket, there was no information to for me to QA on.

With the default value for max_length_for_sort_data, which is 1024, the reference worked, but not in ColumnStore (same thing in InfiniDB). The test case was to set the value to 4096 so that InfiniDB passed. Now 4096 still failed in ColumnStore. It worked when I set it to 8192 (not sure at what value it starts to work).

Not sure what "eliminate the need to set max_length_for_sort_data in most cases" means. We should keep the behavior as close to MariaDB as possible, and if ColumnStore requires larger value, as InfiniDB did, due to system and/or storage design, it is ok. We should not try to eliminate the use of the variable and cause it to go up even more. 4096 is already a huge value to use since the column type is a varchar(1000). Now that we have to increase the value more seems to be unreasonable.

Regarding max_length_for_sort_data. It's true that 1024 is the default and 4096 seems big, but it's not. The max for this value is 8192*1024 (8,388,608) and this is the value that KKA always put in. I believe this should be our default value, set in the my.cnf. Any ORDER BY clause uses this space, and we don't play nice with ORDER BY, which would rather the engine do the work. We force the server to do it all, and so it needs a lot of memory to do it.

David.Hall, dleeyh By reading both of your comments - I understand that
(1) We should be able to set the value of max_lenthg_for_sort_data in my,cnf, and use that value what ever it is set to
(2) For this particular bug to pass, it should be set to 8192

If you both agree with above, then I would have David Hill set the value to 8192 for this particular test script - so it can pass

It worked when I set it to 8192.

This test does not modify the value in my.cnf, but rather with a SQL statement for setting at the session level.

David.HallOk, thanks David, we should get David Hill to modify the SQL statement in the script to set max_length_for_sort_data to 8192

hill Please update line in 2 of working_tpch1/misc/bug3783.sql

set max_length_for_sort_data = 8192;

That will allow this test script to pass

working_tpch1/misc/bug3783.sql updated with

set max_length_for_sort_data = 8192;

Regression suite is updated by using max_length_of_sort_data to 8192