[Bug]: Issue in prefixing schema names

[Dipak1122]

Bug Description

The current implementation of appending schema names to SQL queries in the library is too broad, leading to incorrect SQL syntax in specific Oracle queries.

• Original Query: SELECT EXTRACT(YEAR FROM create_date) AS Year FROM T_PROFILE ;
• Modified Query: SELECT EXTRACT(YEAR FROM SCHEMANAME.create_date) AS Year FROM SCHEMANAME.T_PROFILE ;

The modification incorrectly introduces the schema name within the EXTRACT function's arguments, rendering the SQL command invalid.

Suggestion:
Can we Implement a more robust logic that checks if the table name following a FROM/JOIN keyword exists in _all_tables before prefixing it with the schema name.

Version

0.11.16

Steps to Reproduce

Pass the below query -
SELECT EXTRACT(YEAR FROM create_date) AS Year FROM T_PROFILE ;

Expected Result:
SELECT EXTRACT(YEAR FROM create_date) AS Year FROM SCHEMANAME.T_PROFILE ;

Actual Result:
SELECT EXTRACT(YEAR FROM SCHEMANAME.create_date) AS Year FROM SCHEMANAME.T_PROFILE ;

Relevant Logs/Tracbacks

No response

[dosubot]

Hey @Dipak1122! I'm here to help you with any bugs, questions, or contributions. Let's tackle this together!

The issue you're encountering is related to the incorrect insertion of schema names within SQL functions, such as EXTRACT, which leads to invalid SQL syntax. The suggestion to improve this involves implementing logic that checks if the table name following a FROM or JOIN keyword exists in the _all_tables set before prefixing it with the schema name. This approach would help ensure that only valid table names are prefixed, avoiding incorrect modifications within SQL functions.

The _all_tables data structure is a set that contains the names of all tables and, optionally, views in the SQL database. It is used to validate and determine the usable tables for operations ^[1].

To address this bug, you would need to refine the logic responsible for appending schema names, ensuring it correctly identifies table names and does not mistakenly modify function arguments. This might involve parsing the SQL query to differentiate between table names and other identifiers, such as column names or function arguments.

To continue talking to Dosu, mention @dosu.

---

^{Help Dosu learn! Give it feedback: Great Response | Irrelevant Answer | Incorrect Sources | Too Verbose | Hallucination | Other}