Bug #1958 Scalar subquery ordered case-sensitively (binary) when column is longtext

• Submitted: 26 Nov 2003 3:30
• Modified: 26 Nov 2003 9:07
• Reporter: Phil Sladen
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.0.0
• OS: Linux (Linux 9.0)
• CPU Architecture: Any

[26 Nov 2003 3:30] Phil Sladen

Description:
When a scalar subquery is used in a select column, even though the type of it's result is longtext, an 'order by' the select column is done as though the column was binary, ie. case-sensitively.

I upgraded to version 4.1 so that i could use such queries to order results based on user-specified indexing. If I can't do it this way, or with a small work-around, then I'll have to do the result sorting myself! Either way, I'd like to know sooner, rather than later which way I have to go.

I'm happy to be alpha-testing as long as the code is reasonably solid :)

How to repeat:
create table t (recid int, contents longtext);
insert into t values (1,'a');
insert into t values (2,'z');
insert into t values (3,'C');

NOW:
select contents from t order by 1;
RETURNS:
+----------+
| contents |
+----------+
| a        |
| C        |
| z        |
+----------+
3 rows in set (0.00 sec)

ie. case-insensitive due to column type longtext.

BUT:
select (select contents from t where t.recid=tt.recid) from t tt order by 1;
RETURNS:
+-------------------------------------------------+
| (select contents from t where t.recid=tt.recid) |
+-------------------------------------------------+
| C                                               |
| a                                               |
| z                                               |
+-------------------------------------------------+
3 rows in set (0.00 sec)

ie. case-sensitive ordering, even though the type of the result should be longtext.

[26 Nov 2003 4:45] Phil Sladen

Ok. I can work around this easily by casting the column to 'char', so I've downgraded the severity to low and the priority to low.

Sorry, I'm a bit of a newbie to sql.

[26 Nov 2003 9:07] Dean Ellis

This has already been corrected in the development branch 4.1.1, which you can access from our BitKeeper source repository (see the manual).

Thank you.