[MDEV-11850] Can't create foreign key referencing a virtual column Created: 2017-01-20  Updated: 2017-01-31  Resolved: 2017-01-31

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2
Fix Version/s: 10.2.4

Type: Bug Priority: Major
Reporter: Jan Lindström (Inactive) Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 0
Labels: 10.2-rc


 Description    

create or replace table a (
 
  cola int(10) primary key,
 
  v_cola int(10) as (cola mod 10) virtual,
 
  p_cola int(10) as (cola mod 10) persistent
 
);
 
 
 
create index v_cola on a (v_cola);
 
create index p_cola on a (p_cola);
 
 
 
create or replace table b(
 
cola int(10),
 
v_cola int(10),
 
p_cola int(10)
 
);
 
 
 
alter table b add constraint `p_cola_fk`
 
foreign key (p_cola) references a (p_cola)
 
on delete restrict
 
on update restrict;
 
 
 
alter table b add constraint `v_cola_fk`
 
foreign key (v_cola) references a (v_cola)
 
on delete restrict
 
on update restrict;


ERROR 1005 (HY000): Can't create table `test`.`#sql-181_8` (errno: 150 "Foreign key constraint is incorrectly formed")
 
MariaDB [test]> show warnings;
 
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level   | Code | Message                                                                                                                                                                                                              |
 
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Warning |  150 | Alter  table `test`.`b` with foreign key constraint failed. Parse error in '
 
foreign key (v_cola) references a (v_cola)
 
on delete restrict
 
on update restrict' near 'v_cola)
 
on delete restrict
 
on update restrict'. |
 
| Error   | 1005 | Can't create table `test`.`#sql-181_8` (errno: 150 "Foreign key constraint is incorrectly formed")                                                                                                                   |
 
| Warning | 1215 | Cannot add foreign key constraint                                                                                                                                                                                    |
 
+---------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
3 rows in set (0.00 sec)



 Comments   
Comment by Jan Lindström (Inactive) [ 2017-01-20 ]

http://lists.askmonty.org/pipermail/commits/2017-January/010471.html

Comment by Marko Mäkelä [ 2017-01-30 ]

Is it possible to match the columns by position (with a debug assertion that the names match with strcmp), instead of looping over all columns and looking for a matching name using a case-insensitive search?

Also, please import (and if necessary, adapt) all the virtual column tests from MySQL 5.7 that are related to foreign keys, such as innodb.virtual_basic, innodb.virtual_fk, innodb.virtual_fk_restart. We want to make sure that the patch is not breaking other functionality.

Comment by Jan Lindström (Inactive) [ 2017-01-31 ]

I could not find easy way to map column positions to virtual column number. Tests you mention are already part of the gcol suite. Note that MySQL 5.7 does not support the above syntax or foreign keys to virtual columns:

jan@jan-laptop-asus:~/mysql/mysql-server/mysql-test$ perl -I. -U mysql-test-run.pl --vardir=/dev/shm jfk
 
Logging: mysql-test-run.pl  --vardir=/dev/shm jfk
 
2017-01-31T09:21:54.420345Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
 
2017-01-31T09:21:54.420405Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
 
MySQL Version 5.7.17
 
Checking supported features...
 
 - SSL connections supported
 
 - binaries are debug compiled
 
Collecting tests...
 
Checking leftover processes...
 
Removing old var directory...
 
Creating var directory '/dev/shm'...
 
Installing system database...
 
Using parallel: 1
 
 
 
==============================================================================
 
 
 
TEST                                      RESULT   TIME (ms) or COMMENT
 
--------------------------------------------------------------------------
 
 
 
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
 
 
 
create table a (
 
cola int(10) primary key,
 
v_cola int(10) as (cola mod 10) virtual,
 
p_cola int(10) as (cola mod 10)
 
) engine=innodb;
 
create index v_cola on a (v_cola);
 
create index p_cola on a (p_cola);
 
create table b(
 
cola int(10),
 
v_cola int(10),
 
p_cola int(10)
 
) engine=innodb;
 
show create table a;
 
Table	Create Table
 
a	CREATE TABLE `a` (
 
  `cola` int(10) NOT NULL,
 
  `v_cola` int(10) GENERATED ALWAYS AS ((`cola` % 10)) VIRTUAL,
 
  `p_cola` int(10) GENERATED ALWAYS AS ((`cola` % 10)) VIRTUAL,
 
  PRIMARY KEY (`cola`),
 
  KEY `v_cola` (`v_cola`),
 
  KEY `p_cola` (`p_cola`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
alter table b add constraint `p_cola_fk`
 
foreign key (p_cola) references a (p_cola)
 
on delete restrict
 
on update restrict;
 
ERROR HY000: Cannot add foreign key constraint
 
show warnings;
 
Level	Code	Message
 
Error	1215	Cannot add foreign key constraint
 
show create table b;
 
Table	Create Table
 
b	CREATE TABLE `b` (
 
  `cola` int(10) DEFAULT NULL,
 
  `v_cola` int(10) DEFAULT NULL,
 
  `p_cola` int(10) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1
 
alter table b add constraint `v_cola_fk`
 
foreign key (v_cola) references a (v_cola)
 
on delete restrict
 
on update restrict;
 
ERROR HY000: Cannot add foreign key constraint
 
show warnings;
 
Level	Code	Message
 
Error	1215	Cannot add foreign key constraint
 
show create table b;
 
Table	Create Table
 
b	CREATE TABLE `b` (
 
  `cola` int(10) DEFAULT NULL,
 
  `v_cola` int(10) DEFAULT NULL,
 
  `p_cola` int(10) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Comment by Jan Lindström (Inactive) [ 2017-01-31 ]

commit 45f451c769668e6a351b0c023a994fdf9c07b1f7
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Fri Jan 20 12:10:13 2017 +0200

MDEV-11850: Can't create foreign key referencing a virtual column

Both dict_foreign_find_index and dict_foreign_qualify_index
did not consider virtual columns as possible foreign key
columns and there was assertion to disable virtual columns.

Fixed by also looking referencing and referenced column
from virtual columns if needed.

Generated at Thu Feb 08 07:53:08 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.