Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-29663

Incorrect, confusing error message: ERROR 1054 (42S22): Unknown column 't0.c1' in 'field list'

Details

    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.

      Attachments

        Issue Links

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29430 'Got error 1054 when reading table' on SELECT (Spider)

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29664 Assertion `!n_mysql_tables_in_use' failed in innobase_close_connection

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            Roel Roel Van de Paar added a comment -

            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'

            Roel Roel Van de Paar added a comment - 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'
            Roel Roel Van de Paar added a comment -

            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.

            Roel Roel Van de Paar added a comment - 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.
            Roel Roel Van de Paar added a comment -

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

            Roel Roel Van de Paar added a comment - There is an additional testcase/information in MDEV-29430 , which was marked as a duplicate of this bug.

            People

              ycp Yuchen Pei
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.