[MDEV-29663] Incorrect, confusing error message: ERROR 1054 (42S22): Unknown column 't0.c1' in 'field list' Created: 2022-09-29  Updated: 2023-11-28

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

Type: Bug Priority: Major
Reporter: Roel Van de Paar Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: affects-tests, errorlog, errors

Issue Links:
Duplicate
duplicates MDEV-29430 'Got error 1054 when reading table' o... Closed
Relates
relates to MDEV-29664 Assertion `!n_mysql_tables_in_use' fa... Closed

 Description    

INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE USER Spider@localhost IDENTIFIED BY 'PWD1';
 
CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE 'test',user 'Spider',PASSWORD 'PWD1');
 
CREATE TABLE t (c INT) ENGINE=InnoDB;
 
CREATE TABLE ts (c1 INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
 
SELECT c1 FROM ts;

Leads to:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug)

 
10.11.0-dbg>SELECT c1 FROM ts;
 
ERROR 1054 (42S22): Unknown column 't0.c1' in 'field list'

1. The t0 is incorrect, there is no t0 defined. This is likely a Spider-internal reference.
2. The error is confusing. We could replace it with something like:

ERROR 1054 (42S22): Unknown column 'c1' in parent spider table 't'.

Or similar (assuming that this is the underlaying issue).

Changing the definition of table t to column name c1 (instead of c) fixes the issue.

 Comments   
Comment by Roel Van de Paar [ 2022-10-20 ]

Additional testcase

INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE USER Spider@localhost IDENTIFIED BY 'P';
 
CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE 'test',user 'Spider',PASSWORD 'P');
 
CREATE TABLE t (c1 INT) ENGINE=InnoDB;
 
CREATE TABLE t2 (c INT KEY) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
 
SELECT c FROM t2 WHERE c>ALL (SELECT 1 FROM t);

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)

 
2022-10-20 14:39:11 4 [ERROR] Got error 1054 when reading table './test/t2'

Comment by Roel Van de Paar [ 2022-10-20 ]

In certain cases, a correct error makes it to the error log. For example, when executing:

INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE USER Spider@localhost IDENTIFIED BY 'P';
 
CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET '../socket.sock',DATABASE 'test',user 'Spider',PASSWORD 'P');
 
CREATE TABLE t (c INT) ENGINE=InnoDB;
 
CREATE TABLE t1 (a INT KEY) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
 
INSERT INTO t1 VALUES(0);
 
DROP TABLE t;
 
SELECT COUNT(*)=1 FROM t1;

We see ERROR 1146 in both the client and the error log:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)

 
10.11.0-opt>SELECT COUNT(*)=1 FROM t1;
 
ERROR 1146 (42S02): Table 'test.t' doesn't exist

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Optimized)

 
2022-10-20 17:03:03 4 [ERROR] Got error 1146 when reading table './test/t1'

However, when only the last line is changed to:

SELECT COUNT(*) FROM t1;

Then only the CLI will show the issue, not the error log. This seems closely related to the testcase in the previous comment.

Comment by Roel Van de Paar [ 2022-10-20 ]

There is an additional testcase/information in MDEV-29430, which was marked as a duplicate of this bug.

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