MySQL Bugs: #46259: 5.0.83 -> 5.1.36, query doesn't work

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

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'

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.

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

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)

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'

Hello Valeriy,

thanks for fast verifing the bug.

Henry

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"

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.

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

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.

Fix has changed slightly, reviewers might want to take a look.

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

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

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

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)

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.

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)

Noted in 5.1.40 changelog.

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 :-)

I am happy you're pleased...

Today during the migration iy "found" an other problem. 

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

step by step ;-)

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)

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)

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)

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)