Bug #46259 5.0.83 -> 5.1.36, query doesn't work
Submitted: 17 Jul 2009 10:05 Modified: 19 Dec 2009 0:09
Reporter: Henry Rolofs Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.36, 5.4.4 OS:Any (openSUSE 11.1)
Assigned to: Martin Hansson CPU Architecture:Any
Tags: parser, regression, udf

[17 Jul 2009 10:05] Henry Rolofs
Description:
Hello,

i use on Linux Systems (openSUSE 11.1, 2.6.27.23-0.1-pae) a MySQL-Server 5.0.83 (mysql-5.0.83-linux-i686-glibc23) and try to change to MySQL-Server 5.1.36 (mysql-5.1.36-linux-i686-glibc23).

I have problems with some querys that worked with 5.0.83 and doesn't workd with 5.1.36.

How to repeat:
Test this query on a MySQL Server 5.1.36

select
  if (`tab1`.`data1` is not null, `tab1`.`data1`,'') as `data2`
from (select 1 as `data1`) as `tab1`
order by field(`data2`,1);

and you got an error messages 

1054 Unknown column 'data2' in 'oder clause'
[17 Jul 2009 10:32] Valeriy Kravchuk
Sorry, but I can't repeat this problem using my simple test cases, with built in or stored function I define:

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.36-community |
+------------------+
1 row in set (0.00 sec)

mysql> create function my(p1 int, p2 int) returns int return 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select if(1 is not null,1,2) as a from (select 1 as b) as t order by my(a
,1);
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select if(t.b is not null, t.b, 2) as a from (select 1 as b) as t order b
y my(a,1);
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

So, please, check your test case, provide a code of your field() function and copy/paste testing results from mysql command line client as I did.
[17 Jul 2009 11:51] Henry Rolofs
Hello Valeriy,

the field() function is the mysql internal string function.

Here are my tests:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 5.1.36 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.1.36    |
+-----------+
1 row in set (0.00 sec)

mysql> select if (`tab1`.`data1` is not null, `tab1`.`data1`,2) as `data2` from (select 1 as `data1`) as `tab1` order by field(`data2`,1);
ERROR 1054 (42S22): Unknown column 'data2' in 'order clause'
mysql>

I figured out that the ` inside the field function makes the problem.

mysql> select if (`tab1`.`data1` is not null, `tab1`.`data1`,2) as `data2` from (select 1 as `data1`) as `tab1` order by field(data2,1);
+-------+
| data2 |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

mysql>

The same with your example

mysql> select if(1 is not null,1,2) as a from (select 1 as b) as t order by my(a ,1);
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select if(1 is not null,1,2) as a from (select 1 as b) as t order by my(`a` ,1);
ERROR 1054 (42S22): Unknown column 'a' in 'order clause'
mysql>

Regards,Henry
[17 Jul 2009 12:23] Valeriy Kravchuk
Indeed, you are right. I just was not able to imagine that `a` vs a can make any difference. My bad. Verified just as described:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.36-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select if(t.b is not null, t.b, 2) as a from (select 1 as b) as t order b
y field(a,1);
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.05 sec)

mysql> select if(t.b is not null, t.b, 2) as `a` from (select 1 as b) as t order
 by field(`a`,1);
ERROR 1054 (42S22): Unknown column 'a' in 'order clause'
mysql> exit
Bye

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3308 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.0.79-enterprise-gpl-nt MySQL Enterprise Server - Pro Edition (
GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select if(t.b is not null, t.b, 2) as `a` from (select 1 as b) as t order
 by field(`a`,1);
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.20 sec)
[17 Jul 2009 12:25] Valeriy Kravchuk
Recent azalea is also affected. This time on Linux, simply:

openxs@suse:/home2/openxs/dbs/azalea> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.4.4-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select 1 as a order by filed(a,1);
ERROR 1305 (42000): FUNCTION test.filed does not exist
mysql> select 1 as a order by field(a,1);
+---+
| a |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select 1 as `a` order by field(`a`,1);
ERROR 1054 (42S22): Unknown column 'a' in 'order clause'
[17 Jul 2009 15:36] Henry Rolofs
Hello Valeriy,

thanks for fast verifing the bug.

Henry
[17 Jul 2009 19:23] Peter Laursen
http://bugs.mysql.com/bug.php?id=35863
"Clarified that aliases may be quoted as identifiers or as strings here:
http://dev.mysql.com/doc/refman/5.0/en/identifiers.html"
[4 Sep 2009 9:08] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82427

3093 Martin Hansson	2009-09-04
      Bug#46259: 5.0.83 -> 5.1.36, query doesn't work
      
      The parser rule for expressions in a udf parameter list contains 
      two hacks: 
      First, the parser input stream is read verbatim, bypassing 
      the lexer.
      Second, the Item::name field is overwritten. If the argument to a
      udf was a field, the field's name as seen by name resolution was
      overwritten this way.
      If the field name was quoted or escaped, it would appear as e.g. "`field`".
      
      Fixed by disabling the hack for fields.
     @ mysql-test/r/udf.result
        Bug#46259: Test result.
     @ mysql-test/t/udf.test
        Bug#46259: Test case.
     @ sql/sql_yacc.yy
        Bug#46259: Fix.
[7 Sep 2009 9:45] Henry Rolofs
Hello Martin,

thanks for your patch. :-) 

With this patch (if it included in the binary distrubition)i can start with the migration of my databases/application from 5.0 to 5.1.

Thanks, Henry
[7 Sep 2009 9:58] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82565

3093 Martin Hansson	2009-09-07
      Bug#46259: 5.0.83 -> 5.1.36, query doesn't work
            
      The parser rule for expressions in a udf parameter list contains 
      two hacks: 
      First, the parser input stream is read verbatim, bypassing 
      the lexer.
      Second, the Item::name field is overwritten. If the argument to a
      udf was a field, the field's name as seen by name resolution was
      overwritten this way.
      If the field name was quoted or escaped, it would appear as e.g. "`field`".
      Fixed by not overwriting field names.
     @ mysql-test/r/udf.result
        Bug#46259: Test result.
     @ mysql-test/t/udf.test
        Bug#46259: Test case.
     @ sql/sql_yacc.yy
        Bug#46259: Fix.
[7 Sep 2009 12:07] Martin Hansson
Fix has changed slightly, reviewers might want to take a look.
[7 Sep 2009 12:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82586

3586 Martin Hansson	2009-09-07 [merge]
      Bug#46259: Merge
[7 Sep 2009 12:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82589

3116 Martin Hansson	2009-09-07 [merge]
      Bug#46259: Merge
[7 Sep 2009 14:54] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/82601

3107 Martin Hansson	2009-09-07 [merge]
      Bug#46259: Merge
[14 Sep 2009 16:03] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (version source revid:alik@sun.com-20090914155317-m1g9wodmndzdj4l1) (merge vers: 5.4.4-alpha) (pib:11)
[2 Oct 2009 1:19] Paul Dubois
Noted in 5.4.3 (not 5.4.4) changelog:

A parser problem prevented properly stripping backquotes from an
argument to a user-defined function (UDF). If the UDF was in an ORDER
BY clause, its name would not be properly resolved against an alias
with the same name in the select list.

Setting report to NDI pending push into 5.1.x.
[6 Oct 2009 8:59] Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:martin.hansson@sun.com-20090907145247-q8qhcgweg0qt0j7y) (merge vers: 5.1.39) (pib:11)
[7 Oct 2009 0:45] Paul Dubois
Noted in 5.1.40 changelog.
[22 Oct 2009 7:52] Henry Rolofs
Yesterday I have done the first tests with mysql-5.1.40-linux-i686-glibc23.tar.gz.

Till now everything works find. All my application and scripts runs with 5.1.40. Now I can start with the migration from 5.0.x to 5.1.x.

Many thanks, Henry :-)
[23 Oct 2009 12:58] Martin Hansson
I am happy you're pleased...
[23 Oct 2009 18:17] Henry Rolofs
Today during the migration iy "found" an other problem. 

http://bugs.mysql.com/bug.php?id=48265

step by step ;-)
[18 Dec 2009 10:30] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:45] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:01] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:15] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)