Limitations of current QuestDB support in sqlancer

[SuriZhang]

I implemented the very basic support for questDB, here are some limitations worth mentioning/imporving:

• Resetting database to a clean state after each run
  Since there is no DATABSE concept and DROP DATABASE; statement in QuestDB, tables need to be dropped individually by their names. This has caused many trouble in actual testing. The database were flooded with approximately 300 tables and many generated data and resulted in errors when attempting to connect. Previously, clearing database is completed when SQLancer is creating a test connection by executing DROP DATABSE statement. I first tried to use a for-loop to drop each table, this did not work out neither since a full list of table names stored in GlobalState is only retrievable after test connection is established hence this approach is not achievable.
  I did not want to change the main function of running SQLancer on different databases by adding an extra function to clear databases just applicable for QuestDB. The final solution is to manually delete the root directory of QuestDB before each run. The root directory is automatically spawned at the start of QuestDB, since we have stored the error logs under log directory, the actual tables can be safely deleted.

• Parsing QuestDB system-generated tables
  In QuestDBSchema::getTableNames(), I added code to explictly exclude adding reserved table names to table name list for testing. This is due to an error encountered in SQLancer when it parses table sys.column_versions_purge_log it cannot recognize it as a full table name. SQLancer would parse it as sys. (note the ending dot character) and an error is encountered that pauses the process. I did not see similar cases in DuckDB and PostgreSQL, I would guess their reserved table names may not contain dots. By excluding those table names, it simply fixed the issue and does not generate any side effects for our tests.

[ideoma]

You can change QuestDB configuration property

cairo.system.table.prefix=sys_

and it will turn sys.XXX tables into sys_XXX. Configuration can be changed using environment variables, in this case, you can define environment variable

EXPORT QDB_CAIRO_SYSTEM_TABLE_PREFIX=sys_