[Postgresql] Export returns empty when schema name is not added to table name in query

[adeifeadekunbi]

Provide the Bytebase version you are using

3.6.0

Describe the bug

When running a query in Bytebase without explicitly including the schema name before the table in PostgreSQL, it will return the query result but the export function returns an empty file. However, if the schema name is included in the query, the export works correctly and returns the expected data.

Steps to reproduce

Without Schema name added to table

1. Write a query without adding schema name to the table.
2. Run the query
3. Returns query result
4. Click on Export button to export to CSV. Empty result will be exported.
   With Schema name added to table
5. Write a query adding schema name to the table.
6. Run the query
7. Returns query result
8. Click on Export button to export to CSV. Query result will be exported.

Expected behavior

Export should return the query results even if the schema name is omitted, as long as the query runs successfully in the UI.

Provide the database you are using

Postgresql 16

Additional context

This behavior appears to be PostgreSQL-specific and may be related to how the export function handles default schema resolution.

[h3n4l]

The issue is related to search_path in PostrgeSQL. Could you check the search_path by running the SHOW search_path and check the database users' search_path preferences?

[adeifeadekunbi]

The issue is not related to search_path because schema name is selected on the schema dropdown on the interface and both queries returns output result to the result panel on the interface. Show search_path command is picking the correct selected schema.

Select * from table_name --- Will return the query out to the result panel but will export empty file.
select * from schema_name.table_name --- Will return the query out to the result panel and will export the out result correctly.

[h3n4l]

The reason is the export feature doesn't respect the chosen schema. #17196 will fix this.