Description:
Some functions depend on the value of sql_mode; changing this value means that
gcol will be calculated differently from what was intended at CREATE TABLE time.
In the example below, content of gcol is different after FLUSH TABLES.
Additional problems could appear if the column were indexed (I suspect InnoDB would have stale key values in its index).
--------------
select @@sql_mode
--------------
+------------+
| @@sql_mode |
+------------+
| |
+------------+
1 row in set (0,00 sec)
--------------
create table t1(a char(1), b char(1), c char(2) as (a || b))
--------------
Query OK, 0 rows affected (0,21 sec)
--------------
insert into t1 (a,b)values(1,1)
--------------
Query OK, 1 row affected (0,04 sec)
--------------
select * from t1
--------------
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | 1 |
+------+------+------+
1 row in set (0,00 sec)
--------------
set sql_mode=pipes_as_concat
--------------
Query OK, 0 rows affected (0,01 sec)
--------------
select * from t1
--------------
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | 1 |
+------+------+------+
1 row in set (0,00 sec)
--------------
flush tables
--------------
Query OK, 0 rows affected (0,00 sec)
--------------
select * from t1
--------------
+------+------+------+
| a | b | c |
+------+------+------+
| 1 | 1 | 11 |
+------+------+------+
1 row in set (0,00 sec)
See how 'c' changed from 1 to 11 for the same row.
This was for a virtual gcol. A stored gcol value won't change, but if we inserted rows after FLUSH, we would have new rows with a differently-defined 'c', on top of old rows, which makes the column practically unusable.
How to repeat:
drop table t1;
set sql_mode='';
select @@sql_mode;
create table t1(a char(1), b char(1), c char(2) as (a || b));
insert into t1 (a,b)values(1,1);
select * from t1;
set sql_mode=pipes_as_concat;
select * from t1;
flush tables;
select * from t1;
FLUSH TABLES is to trigger re-parsing of the gcol expression, which then picks up the new sql_mode and behaviour of || .
Other sql_mode values could be used to create other bugs.
Suggested fix:
SPs solve that by storing the value of sql_mode at definition time and reusing it at execution time...
