Incomplete table list with 0.9.3.5

[real-dam]

Hi,

With version 0.9.3.5 there is a regression. The list of tables is incomplete and is missing about 3/4 of the tables. I am attaching two screenshots of the database object tree - first with 0.9.3.4, and the second with 0.9.3.5.

[arvanus]

@real-dam can you provide a simple/reproducible DLL sample of a table that disappeared?

@Jdochoa take a look please

Thanks

[luronumen]

Hi @real-dam

I was unable to reproduce this issue in my 5 different system databases created in Firebird 3.0. Was your database created in Firebird 2.5 and then migrated to Firebird 3.0 or was it created in Firebird 3.0?

[kalwados]

I have the same problem with V0.9.3.5 x64 with fbclient.dll x64. Server: Firebird 2.5.8 (32 Bit) on Windows. Instead of 196 tables, only 38 tables are displayed in the tree: Retrieving the DDL for this database results in an error message:

[Window Title] Unhandled Error in FlameRobin

[Content] IBPP::SQLException Context: Statement::Prepare( select rdb$package_name from rdb$packages where rdb$system_flag = 0 order by rdb$package_name ) Message: isc_dsql_prepare failed

Engine Code : 335544569 Engine Message : Dynamic SQL Error SQL error code = -204 Table unknown RDB$PACKAGES At line 1, column 44

[real-dam]

I was unable to reproduce this issue in my 5 different system databases created in Firebird 3.0. Was your database created in Firebird 2.5 and then migrated to Firebird 3.0 or was it created in Firebird 3.0?

The database was created with older Firebird, yes. Probably 1.5. Then it was migrated using backup + restore (gbak) to 2.0, 2.5 and 3.0.

[Jdochoa]

Hi All.

@real-dam @kalwados You can help me with the result of this query?

select rdb$relation_name from rdb$relations where (rdb$system_flag = 0 or rdb$system_flag is null) and rdb$relation_type = 0 and rdb$view_source is null order by 1

This returns the list of tables to show.

./jo

[real-dam]

select rdb$relation_name from rdb$relations where (rdb$system_flag = 0 or rdb$system_flag is null) and rdb$relation_type = 0 and rdb$view_source is null order by 1

This returns the list of tables to show.

Returns 58 rows here.

Changing the second condition to (rdb$relation_type = 0 or rdb$relation_type is null) makes it return 248 rows.

[arvanus]

@Jdochoa I was thinking, I don`t see a problem to show the GTT tables both in "Tables" and "Global Temporaries" What do you think?

[Jdochoa]

@Jdochoa I was thinking, I don`t see a problem to show the GTT tables both in "Tables" and "Global Temporaries" What do you think?

I'm not sure, I think they should be separated, to make it more scalable. I think in Local temporary Tables (http://tracker.firebirdsql.org/browse/CORE-720)

[Jdochoa]

select rdb$relation_name from rdb$relations where (rdb$system_flag = 0 or rdb$system_flag is null) and rdb$relation_type = 0 and rdb$view_source is null order by 1 This returns the list of tables to show.

Returns 58 rows here.

Changing the second condition to (rdb$relation_type = 0 or rdb$relation_type is null) makes it return 248 rows.

Thank you @real-dam .

Anyone to help me with this query select rdb$relation_name from rdb$relations where (rdb$system_flag = 0 or rdb$system_flag is null) and rdb$view_source is null order by 1 This retrieve the list for < FB 2.5

[arvanus]

Worked fine, but it's strange, in the documentation there is no information about null values in rdb$view_source

https://ib-aid.com/download/docs/firebird-language-reference-2.5/fblangref-appx04-relations.html The possible values at FB 2.5 was:

RDB$RELATION_TYPE - Description

0 - system or user-defined table 1 - view 2 - external table 3 - monitoring table 4 - connection-level GTT (PRESERVE ROWS) 5 - transaction-level GTT (DELETE ROWS)

But I don't see a problem to change the SQL to accept null values @real-dam can you send me a metadata-only database to take a look, or a reproducible way to repeat this problem?

Thanks

[kalwados]

Hello *,

select rdb$relation_name from rdb$relations where (rdb$system_flag = 0 or rdb$system_flag is null) and rdb$relation_type = 0 and rdb$view_source is null order by 1

In my case, this query returns 38 of 196 Tables. But with @real-dam's modified query

Changing the second condition to (rdb$relation_type = 0 or rdb$relation_type is null) ...

all 196 tables are listed.

Anyone to help me with this query:

select rdb$relation_name from rdb$relations where (rdb$system_flag = 0 or rdb$system_flag is null) and rdb$view_source is null order by 1

Works also fine; all tables are listed (Firebird 2.5.x).

[real-dam]

RDB$RELATION_TYPE - Description

0 - system or user-defined table 1 - view 2 - external table 3 - monitoring table 4 - connection-level GTT (PRESERVE ROWS) 5 - transaction-level GTT (DELETE ROWS)

But I don't see a problem to change the SQL to accept null values

I think accepting NULL values is the way to go.

@real-dam can you send me a metadata-only database to take a look, or a reproducible way to repeat this problem?

I can't, because with current Firebird, restoring the database (via gbak) fixes RDB$RELATION_TYPE -- there are no NULL values, only zeroes.

I see RDB$RELATION_TYPE being NULL after a restore was a bug fixed in Firebird 3.0.3. The release notes contain:

• Improvement CORE-5543 Restore of pre ODS 11.1 database can leave RDB$RELATION_TYPE null

So to reproduce the issue you need a backup taken with firebird 2.5 and restored with firebird 3 before 3.0.3. I don't think this is worth the hassle. Adding the or rdb$relation_type is null or using coalesce() seems like an easy workaround without side effects.

Do you want me to produce a pull request implementing that?

[arvanus]

I don`t know if @Jdochoa will fix this soon, but if you can do a pull request, please do 👍 just don't forget to refer this issue in the commit Thanks!

[arvanus]

Done, please update and test (and close this issue if everything is OK)

[Jdochoa]

I don`t know if @Jdochoa will fix this soon, but if you can do a pull request, please do 👍 just don't forget to refer this issue in the commit Thanks!

@arvanus I'm sorry if i don't response cuickly

[arvanus]

I don`t know if @Jdochoa will fix this soon, but if you can do a pull request, please do 👍 just don't forget to refer this issue in the commit Thanks!

@arvanus I'm sorry if i don't response cuickly

No problem! It`s a open-source project, and everybody has jobs or other activities 😃