Bug #1958 Scalar subquery ordered case-sensitively (binary) when column is longtext
Submitted: 26 Nov 2003 4:30 Modified: 26 Nov 2003 10:07
Reporter: Phil Sladen
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:4.1.0.0 OS:Linux (Linux 9.0)
Assigned to: Target Version:

[26 Nov 2003 4: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 5: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 10: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.