• malff/marcsql@weblab.(none)'s avatar
    Bug#25411 (trigger code truncated), PART II · a508260b
    malff/marcsql@weblab.(none) authored
    Bug 28127 (Some valid identifiers names are not parsed correctly)
    Bug 26302 (MySQL server cuts off trailing "*/" from comments in SP/func)
    
    This patch is the second part of a major cleanup, required to fix
    Bug 25411 (trigger code truncated).
    
    The root cause of the issue stems from the function skip_rear_comments,
    which was a work around to remove "extra" "*/" characters from the query
    text, when parsing a query and reusing the text fragments to represent a
    view, trigger, function or stored procedure.
    The reason for this work around is that "special comments",
    like /*!50002 XXX */, were not parsed properly, so that a query like:
      AAA /*!50002 BBB */ CCC
    would be seen by the parser as "AAA BBB */ CCC" when the current version
    is greater or equal to 5.0.2
    
    The root cause of this stems from how special comments are parsed.
    Special comments are really out-of-bound text that appear inside a query,
    that affects how the parser behave.
    In nature, /*!50002 XXX */ in MySQL is similar to the C concept
    of preprocessing :
      #if VERSION >= 50002
      XXX
      #endif
    
    Depending on the current VERSION of the server, either the special comment
    should be expanded or it should be ignored, but in all cases the "text" of
    the query should be re-written to strip the "/*!50002" and "*/" markers,
    which does not belong to the SQL language itself.
    
    Prior to this fix, these markers would leak into :
    - the storage format for VIEW,
    - the storage format for FUNCTION,
    - the storage format for FUNCTION parameters, in mysql.proc (param_list),
    - the storage format for PROCEDURE,
    - the storage format for PROCEDURE parameters, in mysql.proc (param_list),
    - the storage format for TRIGGER,
    - the binary log used for replication.
    
    In all cases, not only this cause format corruption, but also provide a vector
    for dormant security issues, by allowing to tunnel code that will be activated
    after an upgrade.
    
    The proper solution is to deal with special comments strictly during parsing,
    when accepting a query from the outside world.
    Once a query is parsed and an object is created with a persistant
    representation, this object should not arbitrarily mutate after an upgrade.
    In short, special comments are a useful but limited feature for MYSQLdump,
    when used at an *interface* level to facilitate import/export,
    but bloating the server *internal* storage format is *not* the proper way
    to deal with configuration management of the user logic.
    
    With this fix:
    - the Lex_input_stream class now acts as a comment pre-processor,
    and either expands or ignore special comments on the fly.
    - MYSQLlex and sql_yacc.yy have been cleaned up to strictly use the
    public interface of Lex_input_stream. In particular, how the input stream
    accepts or rejects a character is private to Lex_input_stream, and the
    internal buffer pointers of that class are strictly private, and should not
    be tempered with during parsing.
    
    This caused many changes mostly in sql_lex.cc.
    
    During the code cleanup in case MY_LEX_NUMBER_IDENT,
    Bug 28127 (Some valid identifiers names are not parsed correctly)
    was found and fixed.
    
    By parsing special comments properly, and removing the function
    'skip_rear_comments' [sic],
    Bug 26302 (MySQL server cuts off trailing "*/" from comments in SP/func)
    has been fixed as well.
    a508260b
varbinary.test 2.56 KB
# This test uses chmod, can't be run with root permissions
-- source include/not_as_root.inc


# Initialise
--disable_warnings
drop table if exists t1;
--enable_warnings

#
# varbinary as string and number
#

select 0x41,0x41+0,0x41 | 0x7fffffffffffffff | 0,0xffffffffffffffff | 0 ;
select 0x31+1,concat(0x31)+1,-0xf;
select x'31',X'ffff'+0;

#
# Test of hex constants in WHERE:
#

create table t1 (ID int(8) unsigned zerofill not null auto_increment,UNIQ bigint(21) unsigned zerofill not null,primary key (ID),unique (UNIQ) );
insert into t1 set UNIQ=0x38afba1d73e6a18a;
insert into t1 set UNIQ=123; 
explain extended select * from t1 where UNIQ=0x38afba1d73e6a18a;
drop table t1;

#
# Test error conditions
#
--error 1064
select x'hello';
--error 1054
select 0xfg;

#
# Test likely error conditions
#
create table t1 select 1 as x, 2 as xx;
select x,xx from t1;
drop table t1;

# End of 4.1 tests

#
# Bug #19371 VARBINARY() have trailing zeros after upgrade from 4.1
#

# Test with a saved table from 4.1
copy_file std_data/bug19371.frm $MYSQLTEST_VARDIR/master-data/test/t1.frm;
chmod 0777 $MYSQLTEST_VARDIR/master-data/test/t1.frm;
copy_file std_data/bug19371.MYD $MYSQLTEST_VARDIR/master-data/test/t1.MYD;
chmod 0777 $MYSQLTEST_VARDIR/master-data/test/t1.MYD;
copy_file std_data/bug19371.MYI $MYSQLTEST_VARDIR/master-data/test/t1.MYI;
chmod 0777 $MYSQLTEST_VARDIR/master-data/test/t1.MYI;

# Everything _looks_ fine
show create table t1;

# But the length of the varbinary columns are too long
select length(a), length(b) from t1;

# Run CHECK TABLE, it should indicate table need a REPAIR TABLE
CHECK TABLE t1 FOR UPGRADE;

# Run REPAIR TABLE to alter the table and repair
# the varbinary fields
REPAIR TABLE t1;

# Now check it's back to normal
show create table t1;
select length(a), length(b) from t1;
insert into t1 values("ccc", "ddd");
select length(a), length(b) from t1;
select hex(a), hex(b) from t1;
select concat("'", a, "'"), concat("'", b, "'") from t1;

drop table t1;

# Check that the fix does not affect table created with current version
create table t1(a varbinary(255));
insert into t1 values("aaa   ");
select length(a) from t1;
alter table t1 modify a varchar(255);
select length(a) from t1;
drop table t1;


#
# Bug#28127 (Some valid identifiers names are not parsed correctly)
#

--disable_warnings
drop table if exists table_28127_a;
drop table if exists table_28127_b;
--enable_warnings

create table table_28127_a(0b02 int);
show create table table_28127_a;

create table table_28127_b(0b2 int);
show create table table_28127_b;

drop table table_28127_a;
drop table table_28127_b;