disallowing sorting on binary type

[carloea2]

Task Summary

Add a frontend message explaining sorting on binary attributes is not supported and mention brief options such as extracting sortable features from them beforehand.

Priority

P1 – Low

Task Type

• Code Implementation
• Documentation
• Refactor / Cleanup
• Testing / QA
• DevOps / Deployment

[aglinxinyuan]

Why do we disallow sort for binary?

[carloea2]

In the PR of #3774 we were talking about sorting on binary attrs. The decision was based on the fact that the order may be different for every user, what do you think?

[aglinxinyuan]

I do have the need to sort binary. For example, I can have number 1, 2, 3, and I want to sort them as binary. We don't need to limit the possibility of use cases.

[carloea2]

What do you mean with sorting 1,2,3 as binary? are not they sorted as long or other types?

[aglinxinyuan]

See the following workflow:

Binary is simply the type used to decode the bytes. For example, if I have the characters '1', '2', and '3', and decode them using the binary type, I get the bytes 31, 32, and 33 in hex. However, I should still be able to sort them and later decode them as strings if needed.

My point is that the type used to decode the data should not affect the ability to sort the data, since we might choose a different decoding method later, or I just want to sort bytes.

[carloea2]

I want to confirm if the next behavior is what an avg user will expect, if I input 1, 2, 10 then the sort result is 1, 10, 2, is this desired for the avg user, will this not lead to confusion or even errors if the user do not notice they are using binary type?

[aglinxinyuan]

I want to confirm if the next behavior is what an avg user will expect, if I input 1, 2, 10 then the sort result is 1, 10, 2, is this desired for the avg user, will this not lead to confusion or even errors if the user do not notice they are using binary type?

User should know they are using binary type. We don't want to drop the support of something because user might make mistake. The same claim is also true for int and string. For example, what if user input 1, 2, 10 as string? Is the sorting result 1,10,2 unexpected?

[carloea2]

Yes, I think that is also unexpected for some users, and it may cause confusion for real users, example;

An user has a CSV with a column containing scientific notation numbers mixed with normal floats, if in my python code I sort with pandas default, then pandas will cast automatically to float, and the sort will be right.

In texera, now, when reading my CSV, then sorting by that column, the sort will be as strings resulting in a totally different output as in my python code, and since there is no hint in the UI that the column is string the user will have a bad experience.

//// Extra question
In Python, bytes are sequences of unsigned integers 0..255, and binary sequences compare lexicographically by those numeric byte values.

On the JVM (Java/Scala), byte/Byte is signed 8-bit two’s-complement (-128..127).

Is not this another source of problems when sorting bytes? I know similar things can apply to strings but managing bytes is more complex which means for users needing bytes sorting we should provide more Params on how to cast and manage the bytes, right?

[aglinxinyuan]

All the potential issues you described have nothing to do with sorting — they’re more about how to propagate the type correctly before sorting it. The sort operator should be general and capable of processing all data types. How to make the result correct is a separate issue. I don't think we should disable a function because we can't or we don't want to spend time making the result correct. Sort on binary type is supported in pretty much all the database systems, I think we should support it. I don’t have much insight into the technical difficulty since I didn’t review the PR, but as a user, I would like to sort the binary.

[carloea2]

You can't say they have nothing to do with sorting if in the end they affect the sorting, right?

I think we will not reach an agreement, if @chenlica agrees with you then I will close this issue and add binary sorting for the scala version.

@Yicong-Huang also agrees with you.

[aicam]

My understanding is both of the perspectives are right:

• @carloea2 is correct because sorting binary and bytes are meaningless and user may end up using the operator in wrong way
• @aglinxinyuan is correct because we should not intervene in user experience and give them full flexibility
  my suggestion is when user select an attribute that is binary, we show them a small warning like this:
  The selected attribute is binary type and will be sorted as bytes

[chenlica]

Each perspective has its own merits. To help us decide a design, can we evaluate existing systems and see their design decision?