MySQL Bugs: #101721: Creating a table which function instr is a field property

Bug #101721	Creating a table which function instr is a field property
Submitted:	23 Nov 2020 14:05	Modified:	23 Nov 2020 16:43
Reporter:	yi qian	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When I execute the following statement on the MySQL client
--create table t1 (a varchar(10), b varchar(10), c int generated always as (instr(a,b)) virtual) charset latin1;
--show create table t1;

The results show that
--------------------------------
Table   Create Table
t1      CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) GENERATED ALWAYS AS (locate(`b`,`a`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
--------------------------------
It's strange why the attribute of c is "int(11) GENERATED ALWAYS AS (locate(`b`,`a`)) VIRTUAL" rather then "int(11) GENERATED ALWAYS AS (instr(`a`,`b`)) VIRTUAL" 

How to repeat:
execute the following statement on the MySQL client

--create table t1 (a varchar(10), b varchar(10), c int generated always as (instr(a,b)) virtual) charset latin1;
--show create table t1;

Suggested fix:
When execute the following statement on the MySQL client,the result should as follows:

Table   Create Table
t1      CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int(11) GENERATED ALWAYS AS (instr(`a`,`b`)) VIRTUAL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Hello, 

instr(a,b) is the same as locate(b,a).

So this is not a bug ;)

And just a note: you should not use MyISAM storage engine if your data is important. I recommend you to use InnoDB.

Cheers,

lefred

same thing for POSITION:

mysql> drop table if exists t1; create table t1 (a varchar(10), b varchar(10), c int generated always as (position(a in b)) virtual) charset latin1;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int GENERATED ALWAYS AS (locate(`a`,`b`)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_position
"POSITION(substr IN str) is a synonym for LOCATE(substr,str). "

same for MID/SUBSTR:

mysql> drop table if exists t1; create table t1 (a varchar(10), b varchar(10), c int generated always as (mid(a,b,1)) virtual) charset latin1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` varchar(10) DEFAULT NULL,
  `b` varchar(10) DEFAULT NULL,
  `c` int GENERATED ALWAYS AS (substr(`a`,`b`,1)) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

there's many synonyms that are equivalent (LCASE/LOWER + UCASE/UPPER, etc).
So as lefred said it's not a bug.