use test;

# Uncomment the next line, if you want to see the collation values
# let $show_collation= 1;


###### The statement (explicit conversion to CHAR) with the bad output.
# Attention: We have no collation mix, which might make things
#            complicated.
#
#     I miss the '1' after the '-' !
select concat('first line  ', CAST(-1 AS CHAR)) as "my_string"
UNION select 'last line' ;

while ($show_collation)
{
  # some statements giving additional informations
  SELECT COLLATION('first line  ')   as "coll1";
  SELECT COLLATION(CAST(-1 AS CHAR)) as "coll2";
  SELECT COLLATION(concat('first line  ', CAST(-1 AS CHAR))) as "coll_concat = coll_row1";
  SELECT COLLATION('last line')   as "coll_row2";
  dec $show_collation;
  let $show_collation1= 1;
}

#-----------------------------------------------------------------------------------------

###### The statement (explicit conversion to BINARY) with the bad output.
# Attention: We have a now a collation mix.
#     I miss the '1' after the '-' !
select concat('first line  ', CAST(-1 AS BINARY)) as "my_string"
UNION select 'last line' ;

while ($show_collation1)
{
  # some statements giving additional informations
  SELECT COLLATION('first line  ')   as "coll1";
  SELECT COLLATION(CAST(-1 AS BINARY)) as "coll2";
  SELECT COLLATION(concat('first line  ', CAST(-1 AS CHAR))) as "coll_concat = coll_row1";
  SELECT COLLATION('last line')   as "coll_row2";
  dec $show_collation1;
  let $show_collation2= 1;
}

#-----------------------------------------------------------------------------------------

###### The statement (implicit conversion to BINARY) with the bad output.
#     The manual says:
#     If you use a number in string context, the number automatically is 
#     converted to a BINARY string.
# Attention: We have a now a collation mix.
#     I miss the '1' after the '-' !
select concat('first line  ', -1 ) as "my_string"
UNION select 'last line' ;

while ($show_collation2)
{
  # some statements giving additional informations
  SELECT COLLATION('first line  ')   as "coll1";
  SELECT COLLATION(-1) as "coll2";
  SELECT COLLATION(concat('first line  ', -1)) as "coll_concat = coll_row1";
  SELECT COLLATION('last line')   as "coll_row2";
  dec $show_collation2;
  let $show_collation3= 1;
}

#-----------------------------------------------------------------------------------------

###### Statements without UNION for comparison
# They give the output line I expect.
select concat('first line  ', CAST(-1 AS CHAR)) as "my_string";
select concat('first line  ', CAST(-1 AS BINARY)) as "my_string";
select concat('first line  ', -1 ) as "my_string";

###### Statements with UNION but float numbers for comparison
# They give the output line I expect.
select concat('first line  ', CAST(2.2E-1 AS CHAR)) as "my_string"
UNION select 'last line' ;
select concat('first line  ', CAST(-2.2E-1 AS CHAR)) as "my_string"
UNION select 'last line' ;
# Attention: This number equals (mathematical only!) -22 (negative integer).
select concat('first line  ', CAST(-2.2E+1 AS CHAR)) as "my_string"
UNION select 'last line' ;


###### Statement with UNION but positive integer for comparison
# It gives the output line I expect.
select concat('first line  ', CAST(2 AS CHAR)) as "my_string"
UNION select 'last line' ;

###### Statement with UNION and negative, more than one digit integer for comparison
# I miss the last digit.
select concat('first line  ', CAST(-22 AS CHAR)) as "my_string"
UNION select 'last line' ;

###### Statement with UNION and negative integer but additional string for comparison
# I miss the last character of the additional string.
select concat('first line  ', CAST(-22 AS CHAR), ' some text') as "my_string"
UNION select 'last line' ;

###### CONCLUSION ##############
# So it seems that this bug is caused by
# - UNION and
# - concat(?, implicit or explicit conversion of negative! integer! value) .
# The effect is, that the last character of the CONCAT result is missing.
# Maybe the computation of the length of the CONCAT result forgets to
# count the '-'.
#
# This bug is NOT caused by probably difficult collation mixes.