filters out user tables with "$" in names as system tables

[makssent]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

5.5.2

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

---

Hi everyone.

While working with Proxy, I ran into an issue: tables with a $ in their name do not work correctly — they are not loaded into the schema during metadata loading, and any queries to them fail with an error (Table or view 'R$TABLE' does not exist). I reproduced this problem on both Firebird and PostgreSQL.

Looking at the code, such tables are filtered out during metadata loading in SchemaMetaDataLoader by the following rule:

private boolean isSystemTable(final String table) {
    return table.contains("/") || table.contains("##") || table.contains("$");
}

This filter is used in the table loading method:

private Collection<String> loadTableNames(final Connection connection, final String schemaName, final Collection<String> excludedTables) throws SQLException {
    Collection<String> result = new CaseInsensitiveSet<>();
    String[] tableTypes = new String[]{TABLE_TYPE, PARTITIONED_TABLE_TYPE, VIEW_TYPE, SYSTEM_TABLE_TYPE, SYSTEM_VIEW_TYPE};
    try (ResultSet resultSet = connection.getMetaData().getTables(connection.getCatalog(), schemaName, null, tableTypes)) {
        while (resultSet.next()) {
            String table = resultSet.getString(TABLE_NAME);
            if (!isSystemTable(table) && !excludedTables.contains(table)) {
                result.add(table);
            }
        }
    }
    return result;
}

This means that any table whose name contains $ is automatically treated as “system” and excluded from the user schema. The problem is that in both PostgreSQL and Firebird, $ is perfectly valid in user identifiers and does not imply a system object. As a result, valid user tables with $ are filtered out at Proxy startup and become unusable.

At the same time, system tables for each database are already explicitly defined in system/schema (YAML). This mechanism allows Proxy to recognize which tables are truly system ones and handle them separately. Given that, it’s not clear why we still need a global heuristic like contains("$"). Wouldn’t it be more reliable to check against the system tables list for the current databaseType (e.g., for Firebird: RDB$*) rather than applying generic character-based rules?

I understand that this rule was originally introduced to prevent loading system tables that are not yet fully implemented, but it would be better if it were refined so that it does not also break ordinary user tables.

Right now the global $ rule creates a contradiction: system tables are already covered by system/schema, but ordinary user tables containing $ are also excluded and lost. Should this behavior be reconsidered so that user tables with $ remain available after Proxy restarts? Otherwise, it becomes impossible to work with non-system tables whose names happen to include $.

P.S. I also noticed the DriverQuerySystemCatalogOption interface and its isSystemTable(...) method. At first I thought I could override it to define system tables in a dialect-specific way (as done for openGauss). But it turns out this interface is not used during metadata loading in SchemaMetaDataLoader; instead, it is used later during query execution (SQL federation) to recognize system catalog queries and choose a special execution path (e.g., in-memory results / avoiding backend scans). That makes sense for offloading Proxy and handling system queries, but at startup the contains("$") rule still applies and filters out such tables.

[makssent]

I forgot to mention that this issue occurs when working with tables defined using !SINGLE. I tried a couple of ideas today to solve this problem, but none of them were successful:

1. Use SystemSchemaManager:
   The first idea was to directly call the isSystemTable function from SystemSchemaManager inside SchemaMetaDataLoader. This would allow us to check whether a table is system using an existing mechanism. But this can’t be done, because it would require using the infra-common module, which in turn means reworking all dependencies in pom.xml and creates build conflicts.
2. New option:
   The second idea was to introduce a new option similar to DialectSchemaOption, so that each dialect could determine its own system tables. But for just one function, this looks overly excessive and would unnecessarily complicate the architecture.
3. Adjust the current rule:
   The third idea was to change the current rule in isSystemTable so that it is dialect-specific, rather than universal (contains("$")). The problem is that there really is no universal rule: In Firebird, for example, a user can create a table RDB$REL, even though there is already a system table RDB$RELATIONS. That means users are allowed to create tables with the RDB$* prefix, and a blanket filter on $ or even on RDB$ would result in false positives.
4. Use YAML system table lists:
   Another idea was to rely on the YAML system table lists that already exist for each dialect. But this is also not a good solution: a similar implementation already exists in SystemSchemaManager, and duplicating it in SchemaMetaDataLoader would mean adding yet another parser and logic for YAML handling, which would be redundant and “hacky” from an architectural point of view.

At the same time, completely removing the rule at the schema loading stage is also not an option. If we load all tables first and only filter them later, it means unnecessary system objects will be fetched initially and then discarded. That’s extra operations, wasted time, and degraded performance during Proxy startup.

[makssent]

So far, I have noticed the problem only in !SINGLE.
In broadcastTables, it is not possible to use * to select tables — they must be explicitly listed one by one, and therefore they do not go through the generic system-table check.
In !SINGLE, however, wildcards are allowed: you can specify *, and then all tables in the node will be checked. Because of this design, even if you don’t actually use * and only list specific tables, they still go through the same filtering logic. As a result, valid user tables are also filtered out. Single tables always go through SchemaMetaDataLoader, where the system-table check is applied, while broadcast tables are only taken from the explicitly listed names and bypass this check.

- !SINGLE
  tables:
    - ds_0.R$TABLE
    - ds_0.r$table
    - ds_0.*.*
    - "*.*"
    - "*.*.*"
  defaultDataSource: ds_0

P.S I mention broadcastTables here only as an example, since they are also not sharded (like single tables), unlike tables with the !SHARDING rule — for example, T_ORDER, which can be split into T_ORDER_0, T_ORDER_1, and so on.

[strongduanmu]

Hi @makssent, sorry for late reply. SchemaMetaDataLoader is a tool class for loading single tables. Since single-table rules support *.*, we need to identify which tables are truly single tables. The phenomenon you mentioned that the single table containing $ is filtered out is indeed a problem. This logic has existed in the early days of ShardingSphere. I think it is time to enhance it.
I don't have any good ideas for a new solution yet. I think I should first try to investigate which tables in different databases are system tables and which DB or schema these tables are stored in, and then design a new logic for single-table loading based on this.

[makssent]

Hi! Thanks for the reply — it came pretty quickly, I thought I’d have to wait longer. I’m also struggling with this problem and wanted to help, but so far I haven’t found a reliable solution that wouldn’t break the architecture. I described my attempts above; the only option I see is to reuse the implementation from SystemSchemaManager.isSystemTable() to avoid duplicating logic, but the current dependencies don’t allow that. If I come up with any ideas, I’ll definitely share them. For now, I’m working on other tasks in parallel and hope to carve out some time to dig deeper into the code and hopefully be of help.

[strongduanmu]

Perhaps we could move the logic for SystemSchemaManager.isSystemTable() to a more fundamental module, such as shardingsphere-database-connector-core, and design it as an SPI, leaving the specific implementation of isSystemTable checks to individual database modules.
For databases that don't currently provide system tables yaml config, such as Oracle, we could opt out of implementing Oracle's SPI and continue to use the system table check logic in SchemaMetaDataLoader within shardingsphere-database-connector-core.

[makssent]

I agree, this seems like the best approach. I had also considered a similar implementation, but I didn’t want to change the modules on my own, so I was looking for alternative solutions.

[strongduanmu]

With your current familiarity with ShardingSphere, I believe you can handle this task. Although the adjustments are relatively large, as long as you sort out the plan well, you can definitely complete it well. Good luck for you.