Move handling of suffix_length from strnxfrm_bin() to filesort to ensure proper sorting of all kind of binary objects

field::sort_key() now adds length last for varbinary/blob
VARBINARY/BLOB is now sorted by filesort so that shorter strings comes before longer ones
Fixed issues in test cases from last merge



mysql-test/r/select.result:
  Change column name in test to get GROUP BY to use the alias
mysql-test/r/type_blob.result:
  Test BLOB and VARCHAR sorting
mysql-test/t/select.test:
  Change column name in test to get GROUP BY to use the alias
  Drop used tables at start of test
  Don't use table names 'a', 'b' or 'c'
mysql-test/t/type_blob.test:
  Test BLOB and VARCHAR sorting
sql/field.cc:
  Store length last in VARBINARY() and BLOB() columns to get shorter strings sorted before longer onces
sql/field.h:
  Added method 'sort_length()' to allow one to have length bytes last for VARBINARY/BLOB to get these to sort properly
sql/filesort.cc:
  Use 'sort_length()' instead of 'pack_length()' to get length of field.
  Store suffix_length last for varbinary (blob) objects.
  The above ensures that BLOB/VARBINARY are correctly sorted (shorter strings before longer ones)
sql/sql_class.h:
  Added sort suffix length (to get varbinary/blob to sort correctly)
sql/sql_select.cc:
  Use sort_length() instead of pack_lengths()
strings/ctype-bin.c:
  Don't let strnxfrm_bin store length last
  Better to do it in MySQL field object to ensure it's done properly for all cases

1 files changed, 7 insertions, 0 deletions

diff --git a/sql/field.h b/sql/field.h
index 632169868bc..a9f47ecc4a9 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -132,6 +132,7 @@ public:
   virtual bool eq_def(Field *field);
   virtual uint32 pack_length() const { return (uint32) field_length; }
   virtual uint32 pack_length_in_rec() const { return pack_length(); }
+  virtual uint32 sort_length() const { return pack_length(); }
   virtual void reset(void) { bzero(ptr,pack_length()); }
   virtual void reset_fields() {}
   virtual void set_default()
@@ -1048,6 +1049,11 @@ public:
   void reset(void) { bzero(ptr,field_length+length_bytes); }
   uint32 pack_length() const { return (uint32) field_length+length_bytes; }
   uint32 key_length() const { return (uint32) field_length; }
+  uint32 sort_length() const
+  {
+    return (uint32) field_length + (field_charset == &my_charset_bin ?
+                                    length_bytes : 0);
+  }
   int  store(const char *to,uint length,CHARSET_INFO *charset);
   int  store(longlong nr, bool unsigned_val);
   int  store(double nr) { return Field_str::store(nr); } /* QQ: To be deleted */
@@ -1120,6 +1126,7 @@ public:
   void sort_string(char *buff,uint length);
   uint32 pack_length() const
   { return (uint32) (packlength+table->s->blob_ptr_size); }
+  uint32 sort_length() const;
   inline uint32 max_data_length() const
   {
     return (uint32) (((ulonglong) 1 << (packlength*8)) -1);