Oracle SQL group - a group from char

Question

Using a char -cast in a group-by clause results in an unexpected:

select cast(col as char(2)) from ( select 'Abc' as col from dual union all select 'Abc' as col from dual ) group by cast(col as char(10)); 

The result is 'Abc ' (10 characters). Intuitively, I expected Oracle to return one of the following:

• Error: "not a group expression" because the group-by clause is different from the select clause
• Result of length 2 'Ab' .

Replacing cast(col as char(2)) with cast(col as char(3)) , Oracle returns a "not group expression" error. This, again, is a very strange behavior.

How can this be explained? What is the reason for this?

I am using Oracle SQL 11g.

Answer 1

As mentioned above, I think there is a misunderstanding. oo

I can’t explain why he does this, but here is the template for the type of request that you have:

If you generalize it a bit, where [A] and [B] are integers, and [STRING] is any text you want:

 select cast(col as char([A])) from ( select '[STRING]' as col from dual union all select '[STRING]' as col from dual ) group by cast(col as char([B])); 

it seems like this always fails if one of the two conditions below is true (there may be others):

• (LENGTH ([STRING]) <[B] OR LENGTH ([STRING]> [B]) and [A] = LENGTH ([STRING])
• (LENGTH ([STRING]) = [B] AND [A] <> LENGTH ([STRING]))

Otherwise, it will return a string.

But if you take your example, which runs and uses it in the CREATE TABLE statement, it will fail because it sets the column width to 2 and cannot accommodate 3 lines of characters that come in.

To add to the oddity, if you add something at the beginning and end of a line, like this:

 select '\*'||cast(col as char([A]))||'\*' from ( select '[STRING]' as col from dual union all select '[STRING]' as col from dual ) group by cast(col as char([B])); 

This will only work if [A]> = [B], otherwise it will not work on ORA-01489: the string concatenation result is too long.

Curious...