Order by seems broken with round robin on all

Asked by Ranga Gopalan

I tried an order by query on one table that is partitioned by a single column and a second table that is "round robin on all" - The order of the rows returned seems to be broken (incorrect) when the table is defined as "round robin on all".

Please see following sql for an example:

GridSQL -> create table p1 (f1 int, f2 varchar(10)) round robin on all;
OK

GridSQL -> create table p2 (f1 int, f2 varchar(10)) partitioning key f1 on all;
OK

GridSQL -> insert into p1 values (10, 'abcd');
insert into p1 values (10, 'defg');
insert into p1 values (10, 'Cbcd');
insert into p1 values (10, 'Hefg');

insert into p2 values (10, 'abcd');
insert into p2 values (10, 'defg');
insert into p2 values (10, 'Cbcd');
insert into p2 values (10, 'Hefg');
1 row(s) affected

GridSQL -> 1 row(s) affected

GridSQL -> 1 row(s) affected

GridSQL -> 1 row(s) affected

GridSQL -> GridSQL -> GridSQL -> 1 row(s) affected

GridSQL -> 1 row(s) affected

GridSQL -> 1 row(s) affected

GridSQL -> 1 row(s) affected

GridSQL -> commit;

# Query from table that is "round robin on all" returns wrong results - (i.e. data is fine but presented in the wrong order)

GridSQL -> select * from p1 order by f2 desc;
+-----------+
| f1 | f2 |
+-----------+
| 10 | defg |
| 10 | Hefg |
| 10 | abcd |
| 10 | Cbcd |
+-----------+
4 row(s).

# Query from table partitioned by f2 returns correct results

GridSQL -> select * from p2 order by f2 desc;
+-----------+
| f1 | f2 |
+-----------+
| 10 | defg |
| 10 | abcd |
| 10 | Hefg |
| 10 | Cbcd |
+-----------+
4 row(s).

Question information

Language:
English Edit question
Status:
Answered
For:
Stado Edit question
Assignee:
No assignee Edit question
Last query:
Last reply:
Revision history for this message
Jim Mlodgenski (jim-cirrusql) said :
#1

This was answered in the linked bug report:

"This is a configuration problem.
Backend is configured to sort strings as case sensitive, while Stado is configured to sort strings as case insensitive (default).
Stado engine expects backend is returning data properly sorted.
To fix the problem add
xdb.sort.case.sensitive=true
to the Stado configuration file."

Can you help with this problem?

Provide an answer of your own, or ask Ranga Gopalan for more information if necessary.

To post a message you must log in.