Querying trough the Meta Database fails for some tables

[colpari]

How to reproduce the bug

1. Set up Meta Database like described here - in our case it's called 'meta'
2. Select the Meta Database in SQLLab
3. Try to run queries in /sqllab on other databases trough the Meta Database (SELECT * from "meta.3cxCallLog%2Ecall_log";)

Expected results

Data is returned.

Actual results

fptbr  10.200.14.252 - - [20/Oct/2023:13:02:11 +0000] "PUT /tabstateview/49 HTTP/1.1" 200 2 "https://XXXX/sqllab/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36"
fptbr  Triggering query_id: 801
fptbr  2023-10-20 13:02:11,182:INFO:superset.sqllab.commands.execute:Triggering query_id: 801
fptbr  Query 801: Executing 1 statement(s)
fptbr  2023-10-20 13:02:11,249:INFO:superset.sql_lab:Query 801: Executing 1 statement(s)
fptbr  Query 801: Set query to 'running'
fptbr  2023-10-20 13:02:11,249:INFO:superset.sql_lab:Query 801: Set query to 'running'
fptbr  Query 801: Running statement 1 out of 1
fptbr  2023-10-20 13:02:11,286:INFO:superset.sql_lab:Query 801: Running statement 1 out of 1
fptbr  2023-10-20 13:02:11,333:DEBUG:shillelagh.backends.apsw.vt:Instantiating adapter with deserialized arguments: ['meta.3cxCallLog%2Ecall_log', None]
fptbr  10.200.14.252 - - [20/Oct/2023:13:02:11 +0000] "PUT /tabstateview/49 HTTP/1.1" 200 2 "https://XXXX/sqllab/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/118.0.0.0 Safari/537.36"
fptbr  /app/superset/extensions/metadb.py:383: SAWarning: Dialect superset:apsw will not make use of SQL compilation caching as it does not set the 'supports_statement_cache' attribute to ``True``.  This can have significant performance implications including some performance degradations in comparison to prior SQLAlchemy versions.  Dialect maintainers should seek to set this attribute to True after appropriate development and testing for SQLAlchemy 1.4 caching support.   Alternatively, this attribute may be set to False which will disable this warning. (Background on this error at: https://sqlalche.me/e/14/cprf)
fptbr    rows = connection.execute(query)
fptbr  2023-10-20 13:02:12,127:DEBUG:shillelagh.backends.apsw.vt:Instantiating adapter with deserialized arguments: ['3cxCallLog.call_log', None]
fptbr  SupersetErrorsException
fptbr  Traceback (most recent call last):
fptbr    File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1823, in full_dispatch_request
fptbr      rv = self.dispatch_request()
fptbr    File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1799, in dispatch_request
fptbr      return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
fptbr    File "/usr/local/lib/python3.9/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
fptbr      return f(self, *args, **kwargs)
fptbr    File "/app/superset/views/base_api.py", line 127, in wraps
fptbr      raise ex
fptbr    File "/app/superset/views/base_api.py", line 121, in wraps
fptbr      duration, response = time_function(f, self, *args, **kwargs)
fptbr    File "/app/superset/utils/core.py", line 1518, in time_function
fptbr      response = func(*args, **kwargs)
fptbr    File "/app/superset/views/base_api.py", line 93, in wraps
fptbr      return f(self, *args, **kwargs)
fptbr    File "/app/superset/utils/log.py", line 255, in wrapper
fptbr      value = f(*args, **kwargs)
fptbr    File "/app/superset/sqllab/api.py", line 357, in execute_sql_query
fptbr      command_result: CommandResult = command.run()
fptbr    File "/app/superset/sqllab/commands/execute.py", line 121, in run
fptbr      raise ex
fptbr    File "/app/superset/sqllab/commands/execute.py", line 103, in run
fptbr      status = self._run_sql_json_exec_from_scratch()
fptbr    File "/app/superset/sqllab/commands/execute.py", line 161, in _run_sql_json_exec_from_scratch
fptbr      raise ex
fptbr    File "/app/superset/sqllab/commands/execute.py", line 156, in _run_sql_json_exec_from_scratch
fptbr      return self._sql_json_executor.execute(
fptbr    File "/app/superset/sqllab/sql_json_executer.py", line 111, in execute
fptbr      raise SupersetErrorsException(
fptbr  superset.exceptions.SupersetErrorsException: [SupersetError(message='superset error: Bad return type from function callback', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Superset meta database', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]
fptbr  2023-10-20 13:02:12,354:WARNING:superset.views.base:SupersetErrorsException
fptbr  Traceback (most recent call last):
fptbr    File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1823, in full_dispatch_request
fptbr      rv = self.dispatch_request()
fptbr    File "/usr/local/lib/python3.9/site-packages/flask/app.py", line 1799, in dispatch_request
fptbr      return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
fptbr    File "/usr/local/lib/python3.9/site-packages/flask_appbuilder/security/decorators.py", line 95, in wraps
fptbr      return f(self, *args, **kwargs)
fptbr    File "/app/superset/views/base_api.py", line 127, in wraps
fptbr      raise ex
fptbr    File "/app/superset/views/base_api.py", line 121, in wraps
fptbr      duration, response = time_function(f, self, *args, **kwargs)
fptbr    File "/app/superset/utils/core.py", line 1518, in time_function
fptbr      response = func(*args, **kwargs)
fptbr    File "/app/superset/views/base_api.py", line 93, in wraps
fptbr      return f(self, *args, **kwargs)
fptbr    File "/app/superset/utils/log.py", line 255, in wrapper
fptbr      value = f(*args, **kwargs)
fptbr    File "/app/superset/sqllab/api.py", line 357, in execute_sql_query
fptbr      command_result: CommandResult = command.run()
fptbr    File "/app/superset/sqllab/commands/execute.py", line 121, in run
fptbr      raise ex
fptbr    File "/app/superset/sqllab/commands/execute.py", line 103, in run
fptbr      status = self._run_sql_json_exec_from_scratch()
fptbr    File "/app/superset/sqllab/commands/execute.py", line 161, in _run_sql_json_exec_from_scratch
fptbr      raise ex
fptbr    File "/app/superset/sqllab/commands/execute.py", line 156, in _run_sql_json_exec_from_scratch
fptbr      return self._sql_json_executor.execute(
fptbr    File "/app/superset/sqllab/sql_json_executer.py", line 111, in execute
fptbr      raise SupersetErrorsException(
fptbr  superset.exceptions.SupersetErrorsException: [SupersetError(message='superset error: Bad return type from function callback', error_type=<SupersetErrorType.GENERIC_DB_ENGINE_ERROR: 'GENERIC_DB_ENGINE_ERROR'>, level=<ErrorLevel.ERROR: 'error'>, extra={'engine_name': 'Superset meta database', 'issue_codes': [{'code': 1002, 'message': 'Issue 1002 - The database returned an unexpected error.'}]})]

Screenshots

Environment

(please complete the following information):

• browser type and version: Chromium Version 118.0.5993.70 (Official Build) snap (64-bit)

• superset version: The Superset UI shows 0.0.0-dev. We're using this version of the 'latest' image from docker hub with the additional layers

  USER root
  RUN pip3 install AuthLib cloud-sql-python-connector google google-cloud-bigquery
  USER superset

  added by us

• python version: as in the docker hub image

• node.js version: as in the docker hub image

• any feature flags active: FEATURE_FLAGS = {"ALERT_REPORTS": True, "ENABLE_SUPERSET_META_DB": True}

• database 3cxCallLog is on a Postgres16 Server but it's maybe worthwhile noting that it was imported from a PG13 Server with pg_dump -F c | pg_restore -xO while keeping the original database name

Additional context

• If we directly select the 3cxCallLog database in SQL Lab and run SELECT * from "call_log"; it works as expected
• Everything about the 3cxCallLog database also works as expected when used in the psql client
• If we add a simple table containing 2 varchar-fields into the 3cxCallLog DB we are able to successfully query it via the Meta Database and get results

schema of the failing table (holds ~410K records)

c3cxlog=# \d+ call_log
                                                       Table "public.call_log"
       Column       |           Type           | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------------+--------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 historyid          | integer                  |           | not null |         | plain    |             |              | 
 callid             | character varying(128)   |           |          |         | extended |             |              | 
 duration           | interval                 |           |          |         | plain    |             |              | 
 time_start         | timestamp with time zone |           |          |         | plain    |             |              | 
 time_answered      | timestamp with time zone |           |          |         | plain    |             |              | 
 time_end           | timestamp with time zone |           |          |         | plain    |             |              | 
 reason_terminated  | character varying(128)   |           |          |         | extended |             |              | 
 from_no            | character varying(128)   |           |          |         | extended |             |              | 
 to_no              | character varying(128)   |           |          |         | extended |             |              | 
 from_dn            | character varying(128)   |           |          |         | extended |             |              | 
 to_dn              | character varying(128)   |           |          |         | extended |             |              | 
 dial_no            | character varying(128)   |           |          |         | extended |             |              | 
 reason_changed     | character varying(128)   |           |          |         | extended |             |              | 
 final_number       | character varying(128)   |           |          |         | extended |             |              | 
 final_dn           | character varying(128)   |           |          |         | extended |             |              | 
 chain              | text                     |           |          |         | extended |             |              | 
 from_type          | character varying(128)   |           |          |         | extended |             |              | 
 to_type            | character varying(128)   |           |          |         | extended |             |              | 
 final_type         | character varying(128)   |           |          |         | extended |             |              | 
 from_dispname      | character varying(256)   |           |          |         | extended |             |              | 
 to_dispname        | character varying(128)   |           |          |         | extended |             |              | 
 final_dispname     | character varying(128)   |           |          |         | extended |             |              | 
 missed_queue_calls | text                     |           |          |         | extended |             |              | 
 c3cx_source        | character varying(128)   |           |          |         | extended |             |              | 
 c3cx_time_received | timestamp with time zone |           |          |         | plain    |             |              | 
 start_time         | timestamp with time zone |           |          |         | plain    |             |              | 
 end_time           | timestamp with time zone |           |          |         | plain    |             |              | 
 ringing_dur        | interval                 |           |          |         | plain    |             |              | 
 talking_dur        | interval                 |           |          |         | plain    |             |              | 
 q_wait_dur         | interval                 |           |          |         | plain    |             |              | 
Indexes:
    "call_log_pkey" PRIMARY KEY, btree (historyid)
Access method: heap

schema of the working table in the same database

c3cxlog=# \d+ test
                                                  Table "public.test"
 Column |         Type          | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 code   | character(5)          |           |          |         | extended |             |              | 
 title  | character varying(40) |           |          |         | extended |             |              | 
Access method: heap

schema of a more complex table (odoo users table) from another DB (also imported from the same PG13 to the same PG16 with the same command) which works too:

o_sv=# \d+ res_users
                                                                           Table "public.res_users"
       Column       |            Type             | Collation | Nullable |                Default                | Storage  | Compression | Stats target |    Description     
--------------------+-----------------------------+-----------+----------+---------------------------------------+----------+-------------+--------------+--------------------
 id                 | integer                     |           | not null | nextval('res_users_id_seq'::regclass) | plain    |             |              | 
 active             | boolean                     |           |          | true                                  | plain    |             |              | 
 login              | character varying           |           | not null |                                       | extended |             |              | 
 password           | character varying           |           |          |                                       | extended |             |              | 
 company_id         | integer                     |           | not null |                                       | plain    |             |              | 
 partner_id         | integer                     |           | not null |                                       | plain    |             |              | 
 create_date        | timestamp without time zone |           |          |                                       | plain    |             |              | 
 signature          | text                        |           |          |                                       | extended |             |              | Email Signature
 action_id          | integer                     |           |          |                                       | plain    |             |              | Home Action
 share              | boolean                     |           |          |                                       | plain    |             |              | Share User
 create_uid         | integer                     |           |          |                                       | plain    |             |              | Created by
 write_uid          | integer                     |           |          |                                       | plain    |             |              | Last Updated by
 write_date         | timestamp without time zone |           |          |                                       | plain    |             |              | Last Updated on
 totp_secret        | character varying           |           |          |                                       | extended |             |              | Totp Secret
 notification_type  | character varying           |           | not null |                                       | extended |             |              | Notification
 odoobot_state      | character varying           |           |          |                                       | extended |             |              | OdooBot Status
 odoobot_failed     | boolean                     |           |          |                                       | plain    |             |              | Odoobot Failed
 sale_team_id       | integer                     |           |          |                                       | plain    |             |              | User Sales Team
 oauth_provider_id  | integer                     |           |          |                                       | plain    |             |              | OAuth Provider
 oauth_uid          | character varying           |           |          |                                       | extended |             |              | OAuth User ID
 oauth_access_token | character varying           |           |          |                                       | extended |             |              | OAuth Access Token
 website_id         | integer                     |           |          |                                       | plain    |             |              | Website
 karma              | integer                     |           |          |                                       | plain    |             |              | Karma
 rank_id            | integer                     |           |          |                                       | plain    |             |              | Rank
 next_rank_id       | integer                     |           |          |                                       | plain    |             |              | Next Rank
Indexes:
    "res_users_pkey" PRIMARY KEY, btree (id)
    "res_users_login_key" UNIQUE CONSTRAINT, btree (login, website_id)
    "res_users_partner_id_index" btree (partner_id)
    "res_users_uniq_users_oauth_provider_oauth_uid" UNIQUE CONSTRAINT, btree (oauth_provider_id, oauth_uid)
Foreign-key constraints:
    "res_users_company_id_fkey" FOREIGN KEY (company_id) REFERENCES res_company(id) ON DELETE RESTRICT
    "res_users_create_uid_fkey" FOREIGN KEY (create_uid) REFERENCES res_users(id) ON DELETE SET NULL
    "res_users_next_rank_id_fkey" FOREIGN KEY (next_rank_id) REFERENCES gamification_karma_rank(id) ON DELETE SET NULL
    "res_users_oauth_provider_id_fkey" FOREIGN KEY (oauth_provider_id) REFERENCES auth_oauth_provider(id) ON DELETE SET NULL
    "res_users_partner_id_fkey" FOREIGN KEY (partner_id) REFERENCES res_partner(id) ON DELETE RESTRICT
    "res_users_rank_id_fkey" FOREIGN KEY (rank_id) REFERENCES gamification_karma_rank(id) ON DELETE SET NULL
    "res_users_sale_team_id_fkey" FOREIGN KEY (sale_team_id) REFERENCES crm_team(id) ON DELETE SET NULL
    "res_users_website_id_fkey" FOREIGN KEY (website_id) REFERENCES website(id) ON DELETE SET NULL
    "res_users_write_uid_fkey" FOREIGN KEY (write_uid) REFERENCES res_users(id) ON DELETE SET NULL
Access method: heap

[mdeshmu]

cc: @betodealmeida

[betodealmeida]

Taking a look, thanks for the detailed info!

In the meantime, a small comment: you don't have to use the meta database name (though you can). So instead of this:

SELECT * from "meta.3cxCallLog%2Ecall_log";

It's better to write:

SELECT * from "3cxCallLog.call_log";

[betodealmeida]

This is definitely a bug coming from the SQLite library, surfaced by apsw. I suspect it's the interval column. I'll create a similar table in Postgres and test.

[betodealmeida]

OK, I was able to create a similar table (ChatGPT can be awesome) and repro the bug. Working on a fix.

[colpari]

Works in latest-dev - thank you folks!