MySQL Bugs: #18492: mysqld reports ER_ILLEGAL

Bug #18492	mysqld reports ER_ILLEGAL_REFERENCE in --ps-protocol
Submitted:	24 Mar 2006 15:53	Modified:	2 May 2006 22:35
Reporter:	Andrei Elkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S3 (Non-critical)
Version:	5.1.8, 5.0.21, 4.1.19	OS:
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
In execution of rpl_switch_stm_row_mixed with --ps-protocol on line 118 
create table t4  select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3)' failed: 1247: Reference '<result>' not supported (forward reference in item list)

From section how to repeat it should be obvious that the bug is not about replication.
The error is detected in Item_ref::fix_fields
 ...
  if (((*ref)->with_sum_func && name &&
       !(current_sel->linkage != GLOBAL_OPTIONS_TYPE &&
         current_sel->having_fix_field)) ||
      !(*ref)->fixed)
  {
    my_error(ER_ILLEGAL_REFERENCE, MYF(0),
             name, ((*ref)->with_sum_func?
                    "reference to group function":
                    "forward reference in item list"));
    goto error;
  }
  ...
Backtrace

#0  Item_ref::fix_fields (this=0xa691060, thd=0xa66b618, reference=0xa69112c)
    at item.cc:4780
#1  0x081babef in Item_func::fix_fields (this=0xa6910e0, thd=0xa66b618, 
    ref=0xa69a590) at item_func.cc:163
#2  0x082939d6 in JOIN::prepare (this=0xa699928, rref_pointer_array=0xa6906ac, 
    tables_init=0x0, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, 
    group_init=0x0, having_init=0xa6910e0, proc_param_init=0x0, 
    select_lex_arg=0xa690588, unit_arg=0xa68ff88) at sql_select.cc:363
#3  0x08384bd7 in st_select_lex_unit::prepare (this=0xa68ff88, 
    thd_arg=0xa66b618, sel_result=0xa690a18, additional_options=268435456)
    at sql_union.cc:242
#4  0x081fb6a8 in subselect_union_engine::prepare (this=0xa690a28)
    at item_subselect.cc:1484
#5  0x081f7a2f in Item_subselect::fix_fields (this=0xa690990, 
    thd_param=0xa66b618, ref=0xa690bb4) at item_subselect.cc:145
#6  0x081cf37e in Item_in_optimizer::fix_fields (this=0xa690b68, 
    thd=0xa66b618, ref=0xa68c70c) at item_cmpfunc.cc:735
#7  0x0828860c in setup_conds (thd=0xa66b618, tables=0xa68fc28, 
    leaves=0xa68fc28, conds=0xa68c70c) at sql_base.cc:5558
#8  0x082b64d3 in setup_without_group (thd=0xa66b618, 
    ref_pointer_array=0xa690b18, tables=0xa68fc28, leaves=0xa68fc28, 
    fields=@0xa65d8b0, all_fields=@0xa68c698, conds=0xa68c70c, order=0x0, 
    group=0x0, hidden_group_fields=0xa68c67e) at sql_select.cc:282
#9  0x082938c6 in JOIN::prepare (this=0xa68b960, rref_pointer_array=0xa65d94c, 
    tables_init=0xa68fc28, wild_num=0, conds_init=0xa690b68, og_num=0, 
    order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, 
    select_lex_arg=0xa65d828, unit_arg=0xa65d5f8) at sql_select.cc:339
#10 0x08298b59 in mysql_select (thd=0xa66b618, rref_pointer_array=0xa65d94c, 
    tables=0xa68fc28, wild_num=0, fields=@0xa65d8b0, conds=0xa690b68, 
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2416200192, result=0xa68b8d8, unit=0xa65d5f8, 
    select_lex=0xa65d828) at sql_select.cc:1911
#11 0x082934fe in handle_select (thd=0xa66b618, lex=0xa65d5e8, 
    result=0xa68b8d8, setup_tables_done_option=0) at sql_select.cc:238
#12 0x08251aae in mysql_execute_command (thd=0xa66b618) at sql_parse.cc:2892
#13 0x082c5133 in Prepared_statement::execute (this=0xa65d5a8, 
    expanded_query=0xb13fcfb0, open_cursor=false) at sql_prepare.cc:2911
#14 0x082c3571 in mysql_stmt_execute (thd=0xa66b618, 

How to repeat:
cd mysql-test
cat <<.>t/bug.test
CREATE TABLE t1 (a varchar(100));
create table t4 select * from t1 where 3 in (select 1 union select 2 union select UUID() union select 3);
.
./mysql-test-run --record --extern --ps-protocol --mysqld=--binlog-format=row t/bug.test

Thank you for a problem report. Please, specify the exact version(s) that demonstrates this behaviour.

Verified just as described on 5.1.8-BK (ChangeSet@1.2213.1.2, 2006-03-23 22:50:20+01:00):

openxs@suse:~/dbs/5.1/mysql-test> ./mysql-test-run --ps-protocol t/bug.test
Stopping master cluster
Installing Test Databases
Removing Stale Files
Installing Master Databases
running  ../libexec/mysqld --no-defaults --bootstrap --skip-grant-tables     --basedir=.. --datadir=mysql-test/var/master-data --skip-innodb --skip-ndbcluster --skip-bdb
Installing Master Databases 1
running  ../libexec/mysqld --no-defaults --bootstrap --skip-grant-tables     --basedir=.. --datadir=mysql-test/var/master-data1 --skip-innodb --skip-ndbcluster
--skip-bdb
Installing Slave Databases
running  ../libexec/mysqld --no-defaults --bootstrap --skip-grant-tables     --b
asedir=.. --datadir=mysql-test/var/slave-data --skip-innodb --skip-ndbcluster --skip-bdb
Manager disabled, skipping manager start.
Loading Standard Test Databases
Starting Tests

TEST                            RESULT
-------------------------------------------------------
bug                            [ fail ]

Errors are (from /home/openxs/dbs/5.1/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 2: query 'create table t4 select * from t1 where 3 in (select
 1 union select 2 union
select UUID() union select 3)' failed: 1247: Reference '<result>' not supported
(forward reference in item list)
(the last lines may be the most important ones)

Aborting: bug failed in ps-protocol mode. To continue, re-run with '--force'.

Ending Tests
Shutting-down MySQL daemon

Master shutdown finished
Slave shutdown finished

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/5190

The bug is reproducible also for 5.0 and 4.1. I will move my fix back to 4.1 and will merge it up to 5.1

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/5547

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/5548

Need the three-part version number(s) for the fix.

Also, a one- or two-sentence characterization of the
problem for the changelog would be appreciated.
Thanks.

The fix is pushed to the bk repositories (versions 4.1.19/5.0.22).
A possible two sentence description would be : 
The IN-to-EXISTS transformation was making a reference to a parse tree fragment that was left out of the parse tree. This caused problems with prepared statements.

Noted in 4.1.19, 5.0.22 changelogs.