Special treatment of RDB$DATABASE needed in schemas

[Mark Rotteveel]

Given the ubiquitous use of RDB$DATABASE as a 'dual' table, I think the
search rules for schemas need a separate rule to *always* resolve
"SYSTEM"."RDB$DATABASE" for unqualified use of RDB$DATABASE, even if
SYSTEM is _not_ in the search path.

For example, this currently fails:

```
SQL> set search_path to public;
SQL> select 1 from rdb$database;
Statement failed, SQLSTATE = 42S02
Dynamic SQL Error
-SQL error code = -204
-Table unknown
-"RDB$DATABASE"
-At line 1, column 15
```

And I think it should succeed always.

--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 17.06.2025 10:44:
> Given the ubiquitous use of RDB$DATABASE as a 'dual' table, I think the search
> rules for schemas need a separate rule to *always* resolve
> "SYSTEM"."RDB$DATABASE" for unqualified use of RDB$DATABASE, even if SYSTEM is
> _not_ in the search path.

I'm sure that SYSTEM schema must be unconditionally first in the search path.
I.e. order of lookup:

1. SYSTEM schema
2. Current schema
3. Search path
4. PUBLIC schema

But it was already discussed, right?

--
WBR, SD.

[Adriano dos Santos Fernandes]

System is by default in the search path.

Don't remove it and be happy.

We should also implement SELECT syntax without table and avoid this abomination in user's queries.

Adriano

[Mark Rotteveel]

On 17/06/2025 11:24, Adriano dos Santos Fernandes wrote:
>
> Given the ubiquitous use of RDB$DATABASE as a 'dual' table, I think the
> search rules for schemas need a separate rule to *always* resolve
> "SYSTEM"."RDB$DATABASE" for unqualified use of RDB$DATABASE, even if
> SYSTEM is _not_ in the search path.
>
> For example, this currently fails:
>
> ```
> SQL> set search_path to public;
> SQL> select 1 from rdb$database;
> Statement failed, SQLSTATE = 42S02
> Dynamic SQL Error
> -SQL error code = -204
> -Table unknown
> -"RDB$DATABASE"
> -At line 1, column 15
> ```
>
> And I think it should succeed always.
>
>
> System is by default in the search path.
>
> Don't remove it and be happy.

That is not a realistic position to take.

> We should also implement SELECT syntax without table and avoid this
> abomination in user's queries.

Given the amount of tooling, libraries and query generators (including
ORMs) use this, we must make allowance for this, because otherwise it
will be a nightmare to support and will be yet another reason for users
to stick to older versions.

Mark
--
Mark Rotteveel

[Denis Simonov]

I am categorically against a special status for the RDB$DATABASE table.

There are other tools that are sometimes used to solve the problem of querying scalar values, such as Table Value Constructor.

вторник, 17 июня 2025 г. в 12:26:31 UTC+3, Mark Rotteveel:

[Denis Simonov]

I don't understand the point of the complaints here at all. 

Why did you set up a search path where RDB$DATABASE is not visible? Who is stopping you from setting up the correct search path?

вторник, 17 июня 2025 г. в 14:58:47 UTC+3, Denis Simonov:

[Dimitry Sibiryakov]

Denis Simonov wrote 17.06.2025 14:09:
> Why did you set up a search path where RDB$DATABASE is not visible?

Just because they can. That's why I said that SYSTEM must be looked into
unconditionally.

--
WBR, SD.

[Denis Simonov]

I don't think this is right. Those who write bad programs should suffer. 

Either specify the correct search_path, or rewrite the query so that the names are fully qualified.

вторник, 17 июня 2025 г. в 15:13:50 UTC+3, Dimitry Sibiryakov:

[Dimitry Sibiryakov]

Denis Simonov wrote 17.06.2025 14:18:
> I don't think this is right. Those who write bad programs should suffer.

You are not seen much in firebird-support... ;-)
If we cut off those who write bad programs - Firebird will have no users at all.

--
WBR, SD.

[Mark Rotteveel]

On 17/06/2025 14:09, Denis Simonov wrote:
> I don't understand the point of the complaints here at all.
>
> Why did you set up a search path where RDB$DATABASE is not visible? Who
> is stopping you from setting up the correct search path?

Maybe you and I won't write such program, or set such settings, but it
will happen. Either because of users executing `SET SEARCH_PATH TO ...`,
or tooling doing odd things.

And given the status of RDB$DATABASE as Firebird's DUAL (which in
Oracle, I believe, is not schema-bound, or at least always available),
it is commonly used, also in query generators/ORMs, so some robustness
around this is IMHO needed.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 18.06.2025 9:42:
> And given the status of RDB$DATABASE as Firebird's DUAL (which in Oracle, I
> believe, is not schema-bound, or at least always available)

IIRC it has public alias which in Firebird would put it into PUBLIC schema.

--
WBR, SD.

[Denis Simonov]

It always seems to me that someone confuses CURRENT_SCHEMA and SEARCH_PATH. That's where such strange requests come from. 

Don't try to set one current scheme in SEARCH_PATH if you need to work with objects from the system scheme without qualification.

And yes, in my opinion, using RDB$DATABASE for these purposes is a crutch that should be replaced with a more suitable tool.

This can be replaced with something like this

SELECT GEN_ID(MyGen, 1) FROM (VALUES (NULL)) T

среда, 18 июня 2025 г. в 11:15:14 UTC+3, Dimitry Sibiryakov:

[Denis Simonov]

Or even like this

VALUES (GEN_ID(MyGen, 1))

среда, 18 июня 2025 г. в 15:07:38 UTC+3, Denis Simonov:

[Mark Rotteveel]

On 18/06/2025 14:07, Denis Simonov wrote:
>
> It always seems to me that someone confuses CURRENT_SCHEMA and
> SEARCH_PATH. That's where such strange requests come from.
> Don't try to set one current scheme in SEARCH_PATH if you need to work
> with objects from the system scheme without qualification.
>
> And yes, in my opinion, using RDB$DATABASE for these purposes is a
> crutch that should be replaced with a more suitable tool.
>
> This can be replaced with something like this
>
> SELECT GEN_ID(MyGen, 1) FROM (VALUES (NULL)) T

1) We don't have such syntax yet,
2) We and our users have decades of history that you can't just erase.

Mark
--
Mark Rotteveel

[Dmitry Yemanov]

18.06.2025 15:34, 'Mark Rotteveel' via firebird-devel wrote:
>
>> It always seems to me that someone confuses CURRENT_SCHEMA and
>> SEARCH_PATH. That's where such strange requests come from.
>> Don't try to set one current scheme in SEARCH_PATH if you need to work
>> with objects from the system scheme without qualification.
>>
>> And yes, in my opinion, using RDB$DATABASE for these purposes is a
>> crutch that should be replaced with a more suitable tool.
>>
>> This can be replaced with something like this
>>
>> SELECT GEN_ID(MyGen, 1) FROM (VALUES (NULL)) T
>
> 1) We don't have such syntax yet,

Consider we have it. Table value expressions are to be PR'ed soon.

> 2) We and our users have decades of history that you can't just erase.

True. We cannot force everyone to rewrite drivers/libraries and/or user
applications. RDB$DATABASE must be publicly available (at least by
default) and remain singular forever.


Dmitry

[Denis Simonov]

> True. We cannot force everyone to rewrite drivers/libraries and/or user
> applications. RDB$DATABASE must be publicly available (at least by
> default) and remain singular forever.

It is already available by default.

SEARCH_PATH is PUBLIC,SYSTEM by default

Those applications, drivers, etc. that do not support schemes should not change SEARCH_PATH.

Many will want to support schemes partially, that is, only in queries. Then they can always specify the scheme explicitly in the query. Or they can specify the correct SEARCH_PATH, which contains the SYSTEM scheme.

That is, there are no problems here at all.

среда, 18 июня 2025 г. в 15:43:30 UTC+3, Dmitry Yemanov:

[Mark Rotteveel]

On 18/06/2025 15:24, Denis Simonov wrote:
> > True. We cannot force everyone to rewrite drivers/libraries and/or user
> > applications. RDB$DATABASE must be publicly available (at least by
> > default) and remain singular forever.
>
> It is already available by default.
>
> SEARCH_PATH is PUBLIC,SYSTEM by default
>
> Those applications, drivers, etc. that do not support schemes should not
> change SEARCH_PATH.
>
> Many will want to support schemes partially, that is, only in queries.
> Then they can always specify the scheme explicitly in the query. Or they
> can specify the correct SEARCH_PATH, which contains the SYSTEM scheme.
>
> That is, there are no problems here at all.

There is a problem if you think about all the tools, libraries, and
other query generators out there that will either assume that
RDB$DATABASE is simply available, or - if they even are aware Firebird
has schemas in Firebird 6 - will assume that SYSTEM is always on the
search path.

You're acting as if everything interacting with Firebird is a thinking
human running interactive queries, that can correct things on the fly;
it is not.

Given the decades of history we already have, providing a robust
backwards compatibility for some things that are in extreme common use,
is a must IMHO.

You're also thinking as if everything that interacts with Firebird is 1)
aware how Firebird works and 2) will keep up with how Firebird works in
a timely fashion, and 3) will keep up with how Firebird works at all.

In the ecosystem I work in (Java), that is hardly the case (e.g.
sometimes someone contributed basic Firebird support decades ago, and it
works to this day), and producing too much friction might just be the
trigger for users to switch to something else, or not bother to upgrade
beyond Firebird 5.

Sure, you can argue that with the default setting, such friction won't
happen, but the fact someone can configure their connection with a
search path of say "SCHEMA_I_USE", and then deep down the library or
tool they use will try to reference RDB$DATABASE (or god forbid, a
character set), things will break down inexplicably, and it will be
"Firebird's fault", not their fault for not knowing that you really need
to always include SYSTEM if you want things to work.

Mark
--
Mark Rotteveel

[Denis Simonov]

I don't consider people stupid by default, and therefore I don't believe that they will twist knobs that they don't understand.

You can change the search path only if the driver, ORM, application and query generator support schemas.

And don't make something magical out of RDB$DATABASE. Changing the search path will break a lot in other places if the system can't work with schemas. Because any query generator works with system tables. Moreover, queries to system tables must be written taking into account schemas. Otherwise, it can be broken in no time.

Try creating objects with the same name in two schemas, for example, a table. And then try to extract the metadata of this table from the system tables with a query that does not take into account schemas. It will be a lot of fun.

Therefore, let's not introduce bad things like "And here the fish was wrapped" into the DBMS design by default, because the architecture should be logical. As I already said, trying to make something special out of RDB$DATABASE won't help much against fools, because the system can be broken in 10 other different places.

суббота, 21 июня 2025 г. в 11:11:24 UTC+3, Mark Rotteveel:

[Dimitry Sibiryakov]

Denis Simonov wrote 21.06.2025 20:10:
> Try creating objects with the same name in two schemas, for example, a table.
> And then try to extract the metadata of this table from the system tables with a
> query that does not take into account schemas. It will be a lot of fun.

That's why I said that for schemas a complete new set of system tables had to
be introduced and a backward-compatible RDB$RELATIONS etc must show only tables
from current schema (which is PUBIC by default).
This way old queries and tools can work as if nothing happened.

--
WBR, SD.

[F. D.Castel]

Mark, I completely agree with your points on backward compatibility. So much so that I’d consider anyone who disagrees with it downright crazy. That or they've never had any contact with customer support in their life.

However, the obligation to maintain backward compatibility applies only until the user begins using new features.

If my code, written for Firebird 3, is unaware of schemas, then it's reasonable to preserve the current behavior of RDB$DATABASE. My legacy code has no way of understanding the new features or their implications.

However, the moment I start dealing with things like schema search paths, it's entirely justifiable to discard those assumptions. That's sufficient proof that the user is from the present, not from the past.

Once a user modifies the search path, they must understand what that entails. Wouldn't you agree?

[Dalton Calford]

Has anyone considered replacing the need for using rdb$database?

For example in MSSQL you can perform a select without a table.  The assumption is that it is a singleton select.  This would be a change in the parser and could be easily retrofitted onto existing databases, while still supporting the legacy dialect.
Just a thought.

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-deve...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/firebird-devel/8597ac0c-685f-49dd-9f16-4df2dc139566n%40googlegroups.com.

[Mark Rotteveel]

On 21/06/2025 21:17, Dalton Calford wrote:
> Has anyone considered replacing the need for using rdb$database?
>
> For example in MSSQL you can perform a select without a table.  The
> assumption is that it is a singleton select.  This would be a change in
> the parser and could be easily retrofitted onto existing databases,
> while still supporting the legacy dialect.
> Just a thought.

A table value constructor is the replacement, and it is expected "soon".
However, that doesn't solve the problem for existing code and tools.
This is about backwards compatibility for existing behaviour and
functionality that is in use, not about new and better ways to solve things.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 21/06/2025 21:27, F. D.Castel wrote:
> However, the moment I start dealing with things like schema search
> paths, it's entirely justifiable to discard those assumptions. That's
> sufficient proof that the user is from the present, not from the past.
>

> Once a user modifies the search path, they /must/ understand what that

> entails. Wouldn't you agree?

You'd hope so, but given other DBMSes have similar features that work
slightly different, or don't have nor need a table like RDB$DATABASE
that is "misused" as a singleton row source, you very much run the
chance assumptions will be made based on the behaviour in other products.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 21/06/2025 20:10, Denis Simonov wrote:
> I don't consider people stupid by default, and therefore I don't believe
> that they will twist knobs that they don't understand.

I have worked in customer support, IT support, and answered hundreds if
not thousands questions here and elsewhere, and I'm not that optimistic.

> You can change the search path only if the driver, ORM, application and
> query generator support schemas.

Not really, as long as the driver supports it, and you can control the
configuration of the database connection (e.g. in Java, the JDBC URL),
then you can configure the search path to you hearts content, even if
the application, etc., is not schema-aware.

> And don't make something magical out of RDB$DATABASE. Changing the
> search path will break a lot in other places if the system can't work
> with schemas. Because any query generator works with system tables.
> Moreover, queries to system tables must be written taking into account

> schemas. Otherwise, it can be broken in no time.Not all query generators query metadata tables directly (e.g. they use a
common API, like JDBC's `DatabaseMetaData`, which I am updating right
now for Jaybird), or even at all (e.g. they have a code-first model of
the database, or some other sort of description of the database).

For example, Hibernate will emulate table value constructors using union
all and RDB$DATABASE for Firebird, and it also uses RDB$DATABASE in some
other places. Now, Hibernate is probably a "bad example", as I help
maintain its Firebird dialect, so I'll soon be working to write in
additional Firebird 6 support for it.

> Try creating objects with the same name in two schemas, for example, a
> table. And then try to extract the metadata of this table from the
> system tables with a query that does not take into account schemas. It
> will be a lot of fun.

See my previous point, the tool might be using a common API to get its
information, and for example the JDBC API itself does support schemas,
so as long as the driver provides the right information, the tool might
happily work until it generates a query involving RDB$DATABASE as that
table name might be hardcoded somewhere in its dialect for Firebird.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-devel wrote 22.06.2025 11:07:
> Not really, as long as the driver supports it, and you can control the
> configuration of the database connection (e.g. in Java, the JDBC URL), then you
> can configure the search path to you hearts content, even if the application,
> etc., is not schema-aware.

And don't forget about ON CONNECT triggers.

--
WBR, SD.

[Alex Peshkoff]

Mark, as soon as Adriano agreed with suggestion to "always append (in
the tail) SYSTEM to the user informed search path when it's not there"
this problem appears to be solved too.

[Mark Rotteveel]

Yes, if that gets implemented for all object types.
--
Mark Rotteveel