[MDEV-30530] SPIDER table error when using GENERATED VIRTUAL columns Created: 2023-01-31  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Storage Engine - Spider, Virtual Columns
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Major
Reporter: Vincent Milum Jr Assignee: Yuchen Pei
Resolution: Unresolved Votes: 1
Labels: Papercut


 Description   

The SPIDER engine doesn't recognize GENERATED VIRTUAL columns, and is still trying to push down the column to the remote backend server. The SPIDER engine, much like other engines, should ignore the GENERATED VIRTUAL column from the storage backend itself.

CREATE TABLE `tbl` (
 
  `id` int,
 
  `testing` int as (1),
 
) ENGINE=SPIDER COMMENT='wrapper "mysql", srv "srv1"';
 
 
 
select * from tbl;
 
 
 
ERROR 1054 (42S22): Unknown column 't0.testing' in 'field list'



 Comments   
Comment by Andrew Hutchings [ 2023-02-01 ]

At the moment SPIDER does not support virtual columns as indicated here: https://mariadb.com/kb/en/generated-columns/

But MariaDB should have told you this at create time, so it is a bug.

Comment by Roel Van de Paar [ 2023-02-11 ]

I cannot repeat this issue on a recent 10.6 (12a85c6caf595c685336455e416099b6a8020534) nor on 10.11 (b075191ba8598af6aff5549e6e19f6255aef258a).

MTR testcase:

--source include/have_innodb.inc
 
--let $SOCKET=`SELECT @@global.socket`
 
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE USER Spider@localhost IDENTIFIED BY '';
 
GRANT ALL ON test.* TO Spider@localhost;
 
eval CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS(SOCKET "$SOCKET",DATABASE'test',USER'Spider',PASSWORD'');
 
CREATE TABLE t(c INT, d INT AS (1)) ENGINE=InnoDB;
 
INSERT INTO t VALUES (1,NULL);
 
CREATE TABLE st(c INT, d INT AS (1)) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
 
SELECT * FROM st;
 
# Cleanup
 
DROP TABLE t;
 
DROP TABLE st;

Results in the expected output:

10.6.12 12a85c6caf595c685336455e416099b6a8020534 (Debug)

 
SELECT * FROM st;
 
c	d
 
1	1

Comment by Roel Van de Paar [ 2023-02-11 ]

darkain Can you please provide a detailed testcase for what you are seeing as well as the exact version you are using? Thank you!

Comment by Vincent Milum Jr [ 2023-02-13 ]

Your test case there "worked" because the source table had the "d" column and so the value was generated there. In my case, I was trying to do the calculation on the spider side rather than the innodb side.

Change:

CREATE TABLE t(c INT, d INT AS (1)) ENGINE=InnoDB;

Over to:

CREATE TABLE t(c INT) ENGINE=InnoDB;

Comment by Roel Van de Paar [ 2023-02-18 ]

Thank you, that fixed the issue. ycp What are your thoughts on this? MTR testcase:

--source include/have_innodb.inc
 
--let $SOCKET=`SELECT @@global.socket`
 
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE USER Spider@localhost IDENTIFIED BY '';
 
GRANT ALL ON test.* TO Spider@localhost;
 
eval CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS(SOCKET "$SOCKET",DATABASE'test',USER'Spider',PASSWORD'');
 
CREATE TABLE t(c INT) ENGINE=InnoDB;
 
INSERT INTO t VALUES (1);
 
CREATE TABLE st(c INT, d INT AS (1)) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
 
SELECT * FROM st;
 
DROP TABLE t,st;  # Cleanup

Leads to:

mysqltest: At line 10: query 'SELECT * FROM st' failed: ER_BAD_FIELD_ERROR (1054): Unknown column 't0.d' in 'field list'

Present in all versions

Generated at Thu Feb 08 10:16:55 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.