Re: [Maria-developers] MySQL Bug 59449 - Accepted by MariaDB?

5 Oct 2011

Cool, thank you!

As I'm not very familiar with the working process between MariaDB and MySQL
upstream, do you think this patch will go back upstream?  My intention in my
original work on it was to get a fix into all distros that track MySQL.

Thanks again!
Jeremy Thomerson

On Wed, Oct 5, 2011 at 8:56 AM, Timour Katchaounov
wrote:
...
Hello Jeremy,
This is to inform you that I started working on
MySQL Bug 59449. In order to track the bug progress
I created a bug in Launchpad:
https://bugs.launchpad.net/**maria/+bug/861763https://bugs.launchpad.net/maria/+bug/861763
If you are interested in following the progress of
this work, please subscribe to the Launchpad bug above.
I will work on implementing the generic solution
suggested by Monty.
Timour
...
Hi!
"Jeremy" == Jeremy Thomerson>
...
...
...
...
>  writes:
>
Jeremy>  Monty, et al...
Jeremy>  On Mon, Jun 13, 2011 at 9:36 AM, Michael Widenius<
monty@askmonty.org>**wrote:
...
...
Hi!
"Sergei" == Sergei Golubchik  writes:
...
...
>>>
>>
 Sergei>  Hi, Jeremy!
Sergei>  On Jun 10, Jeremy Thomerson wrote:
...
I just submitted a patch to a MySQL bug that I am also experiencing.  I
...
...
...
would like to see it accepted into the wider community (MySQL,
MariaDB,
Percona, etc).  Please let me know what I need to do so that this
patch
could also be accepted into your project.
The bug is here: http://bugs.mysql.com/bug.php?**id=59449http://bugs.mysql.com/bug.php?id=59449
The "[11 Jun 3:16] Jeremy Thomerson" comment will be the most useful.
Sergei>  But if Oracle will refuse to fix this bug, we will look at your
...
patch,
...
Sergei>  certainly.
...
...
To ensure we don't forget about this, if you are not sure that Oracle
has fixed the bug within one month, ping us and we will look at
adding this into MariaDB at once.
Jeremy>  Thanks a bunch!  I was away a lot in July and August and didn't
get to
Jeremy>  follow up on this.  Looking at the original bug report, I have
seen
Jeremy>  absolutely no activity.  I'm not even sure why they have not
touched /
Jeremy>  accepted it.  If anyone here knows a way to get it into one (or
both)
Jeremy>  projects, I'd appreciate any help you can offer.
The patch you suggested was:
=== modified file 'sql/sql_base.cc'

--- sql/sql_base.cc     2011-04-12 10:59:59 +0000
+++ sql/sql_base.cc     2011-06-10 20:11:52 +0000
@@ -6975,6 +6975,28 @@
         *counter= i;
         *resolution= RESOLVED_IGNORING_ALIAS;
         break;
+      }
+      else if (find->type() == Item::FUNC_ITEM&&  ((Item_func*)
find)->functype() == Item_func::COLLATE_FUNC&&  ((Item_func*)
find)->arg_count == 2)
+      {
+        /*
+          If someone uses "ORDER BY column_name COLLATE utf8_spanish_ci",
it is a function
+          that we are trying to find, and not a real column.  Bug #59449
exposes that this
+          failed when column_name is actually referring to the name of an
alias, whether
+          that alias is for a real column or for a function result (such
as coalesce). This
+          block of code finds the real item, which is the "column"
referred to by the
+          collation function for this scenario.
+        */
+        Item *collated_column = ((Item_func*) find)->arguments()[0];
+        int is_alias = item->type() == Item::FUNC_ITEM ||
+                       ((item->type() == Item::FIELD_ITEM || item->type()
== Item::REF_ITEM)&&
+                        my_strcasecmp(system_charset_**info,
((Item_ident*) item)->field_name, item->name));
+        if (is_alias&&  collated_column->name&&  item->name&&
 !my_strcasecmp(system_charset_**info, item->name,
collated_column->name))
+        {
+          found =&collated_column;
+          *counter = i;
+          *resolution = RESOLVED_BEHIND_ALIAS;
+          break;
+        }
       }
     }
     else if (table_name&&  item->type() == Item::REF_ITEM&&
What I don't like with the patch is that we make COLLATE a special
case. I think that all usage of alias names in ORDER BY should be
treated the same.
In other words:
ORDER BY LanguageName
ORDER BY LanguageName COLLATE utf8_spanish_ci
ORDER BY concat(LanguageName,"aa")
should all use either the column or the alias in the same manner.
As we normally prefer alias for a column name in the ORDER BY,
I think we should fix this so that we always prefer the alias name in
all ORDER BY context.
The other option would be to give an error in this case as it's not
clear what the user wants.  This is hower likely to break many old
applictions.
After talking with Sergei, he concluded:
"yes, I believe now that according to the SQL:2003 (ISO/IEC
9075-2:2003, part 2 (foundations), 14.1<declare cursor>, Syntax
Rules, item 18) aliases from the select list take precedence over
column names from the table"
The standard is very vague in the area, but it seams to support the
idea that alias'es should resolve first).
This means that we should fix things to always first prefer alias.
The fix in this case needs to be done in item::fix_fields() and not in
sql_base.cc
I will ask Timour to see if he can provide a fix for this.
(As he knows the fix_fields code quite well and he needs something to
bite into now when he is back from his vacation).
Timour, can you handle this?
Regards,
Monty


    