HELP WITH "AS SELECT" STATEMENT

[makssent]

Hello everyone,

I recently encountered a problem that I've been struggling with for a long time. I implemented a CREATE VIEW statement for my database, and it looks like this:

createView
    : (CREATE (OR ALTER)? VIEW)
      viewName viewAliasClause? 
      AS select 
      (WITH (CASCADED | LOCAL)? CHECK OPTION)?
    ;
    
    viewAliasClause
    : LP_ viewAlias (COMMA_ viewAlias)* RP_
    ;

    viewAlias
    : columnName (AS alias)?
    ;
    
    columnName
    : (owner DOT_)? name
    ;
    
    name
    : identifier
    ;

I ran into an error: if I use BEGIN instead of AS, everything works fine. However, when using the syntax AS select, it throws the following error:

class org.apache.shardingsphere.sql.parser.sql.common.segment.generic.table.SimpleTableSegment cannot be cast to class org.apache.shardingsphere.sql.parser.sql.common.statement.SQLStatement

Does anyone have any ideas on how I can resolve this issue or where I can look for a solution?

[strongduanmu]

@makssent Are you using ShardingSphere SQL Parser component? Your exception appears to be caused by type conversion during visit, a more complete exception stack may help solve the problem.

[makssent]

@strongduanmu

If I understood you correctly, I am using ShardingSphere for SQL parsing, but the debugging is done through a custom utility. This utility runs the necessary queries through ShardingSphere and my database, then generates an HTML report with errors or their absence. The utility doesn't have logging like a typical project using ShardingSphere. Do you think I should create such a project?

You can check the project at this link: https://github.com/makssent/shardingsphere/tree/shardingsphere-rdb
The working branch is: shardingsphere-rdb.

Thanks in advance for noticing my ticket.

[strongduanmu]

Hi @makssent, I took a quick look at your project, and it looks like you are adapting the Firebird SQL dialect? This work looks cool, but I think a better way would be to submit the Firebird SQL parsing to the ShardingSphere main repository so that all community participants can optimize it.
https://github.com/makssent/shardingsphere/tree/shardingsphere-rdb/parser/sql/dialect/firebird

[makssent]

Hi @makssent, I took a quick look at your project, and it looks like you are adapting the Firebird SQL dialect? This work looks cool, but I think a better way would be to submit the Firebird SQL parsing to the ShardingSphere main repository so that all community participants can optimize it. https://github.com/makssent/shardingsphere/tree/shardingsphere-rdb/parser/sql/dialect/firebird

Yes, you understood correctly. I will get in touch with you about this later. Thank you again for your response.

[makssent]

Hi @makssent, I took a quick look at your project, and it looks like you are adapting the Firebird SQL dialect? This work looks cool, but I think a better way would be to submit the Firebird SQL parsing to the ShardingSphere main repository so that all community participants can optimize it. https://github.com/makssent/shardingsphere/tree/shardingsphere-rdb/parser/sql/dialect/firebird

Hi, I’m back with an answer. Yes, it would be great to merge our work regarding the Firebird dialect. How can we do this, would a regular pull request be enough, or..? Is there a patch we can review on this?

[strongduanmu]

First, you need to create an issue with feature request template, which can be called "Adapt Firebird database for ShardingSphere", and then create some subtasks in the issue, such as: 1. Add Firebird database type and metadata loading; 2. SQL Parser adapts to Firebird; 3. Add Firebird database protocol, etc.

After creating the issue, you can leave a message below to claim the SQL Parser task. You are also welcome to claim other parts, or let other developers in the community participate.

[makssent]

Alright, thank you, we will do as you said soon.

Regarding the error, I think I should finish the implementation first and then create a new ticket with the log trace and the complete implementation. Since I haven’t committed my implementation of CREATE VIEW, which I was working on in DDLStatement, the full picture of what’s happening might not be clear. Anyway, thanks for everything, we’ll try to add support for the Firebird Dialect to your project as soon as possible =)