Skip to content

SQL cannot be parsed #1342

New issue
New issue
Closed
#1351
Closed
SQL cannot be parsed#1342
#1351

Description

@coderstory
coderstory
opened on Sep 22, 2021

Describe the bug
ALTER TABLE ... DROP FOREIGN KEY ... not implemented

To Reproduce
Steps to reproduce the behavior:

  1. Example SQL
    ALTER TABLE rcp_dev_application . qweqwe DROP FOREIGN KEY wqewqe

  2. Parsing this SQL using JSqlParser with this statements

  3. Exception

net.sf.jsqlparser.JSQLParserException: Encountered unexpected token: "FOREIGN" "FOREIGN"
  at line 1, column 49.

Was expecting one of:

  "COLUMN"
  "CONSTRAINT"
  "INDEX"
  <S_IDENTIFIER>
  <S_QUOTED_IDENTIFIER>


  at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:190)
  at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:63)
  at net.sf.jsqlparser.parser.CCJSqlParserUtil.parse(CCJSqlParserUtil.java:38)
  at cn.bobmao.pro.data.SqlGeneraterTest.sqlGenerater(SqlGeneraterTest.java:76)
  at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.base/java.lang.reflect.Method.invoke(Method.java:566)
  at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:686)
  at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
  at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
  at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
  at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
  at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
  at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
  at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
  at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
  at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
  at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
  at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
  at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
  at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
  at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:212)
  at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:208)
  at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:137)
  at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:71)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
  at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
  at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
  at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
  at java.base/java.util.ArrayList.forEach(ArrayList.java:1540)
  at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
  at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
  at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
  at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
  at java.base/java.util.ArrayList.forEach(ArrayList.java:1540)
  at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
  at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
  at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
  at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
  at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
  at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
  at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
  at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
  at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:248)
  at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$5(DefaultLauncher.java:211)
  at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:226)
  at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:199)
  at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:132)
  at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:71)
  at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33)
  at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
  at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "FOREIGN" "FOREIGN"
  at line 1, column 49.

Was expecting one of:

  "COLUMN"
  "CONSTRAINT"
  "INDEX"
  <S_IDENTIFIER>
  <S_QUOTED_IDENTIFIER>

  at net.sf.jsqlparser.parser.CCJSqlParser.generateParseException(CCJSqlParser.java:30694)
  at net.sf.jsqlparser.parser.CCJSqlParser.jj_consume_token(CCJSqlParser.java:30527)
  at net.sf.jsqlparser.parser.CCJSqlParser.AlterExpression(CCJSqlParser.java:17353)
  at net.sf.jsqlparser.parser.CCJSqlParser.AlterTable(CCJSqlParser.java:17457)
  at net.sf.jsqlparser.parser.CCJSqlParser.SingleStatement(CCJSqlParser.java:267)
  at net.sf.jsqlparser.parser.CCJSqlParser.Statement(CCJSqlParser.java:153)
  at net.sf.jsqlparser.parser.CCJSqlParserUtil.parseStatement(CCJSqlParserUtil.java:188)
  ... 66 more

Expected behavior
A clear and concise description of what you expected to happen.

System
win10 x64

  • Database you are using
  • mysql 8.0
  • Java Version
  • 11
  • JSqlParser version
  • 4.2

Activity

  1. coderstory commented on Sep 22, 2021

    @coderstory
    coderstory
    on Sep 22, 2021
    Author

    alter table tableName drop foreign key foreignKeyName;

  2. manticore-projects commented on Sep 22, 2021

    @manticore-projects
    manticore-projects
    on Sep 22, 2021
    Contributor

    Greetings.

    Thank you for the report, this has not been implemented yet.
    Currently supported syntax is:

    AlterExpression
         ::= ( 'ADD' | 'ALTER' | 'MODIFY' ) ( ( ( 'PRIMARY' 'KEY' | ( 'KEY' | 'INDEX' ) RelObjectName ) ColumnsNamesList AlterExpressionConstraintState | 'UNIQUE' ( ( 'KEY' | 'INDEX' ) ( S_IDENTIFIER | S_QUOTED_IDENTIFIER ) )? ColumnsNamesList ) ( 'USING' RelObjectName )? | 'COLUMN'? ( AlterExpressionColumnDataType | AlterExpressionColumnDropNotNull ) | '(' AlterExpressionColumnDataType ( ',' AlterExpressionColumnDataType )* ')' | 'FOREIGN' 'KEY' ColumnsNamesList 'REFERENCES' Table ColumnsNamesList? ( 'ON' ( 'DELETE' | 'UPDATE' ) Action )? ( 'ON' ( 'DELETE' | 'UPDATE' ) Action )? | 'CONSTRAINT' RelObjectName ( ( 'FOREIGN' 'KEY' ColumnsNamesList 'REFERENCES' Table ColumnsNamesList? ( 'ON' ( 'DELETE' | 'UPDATE' ) Action )? ( 'ON' ( 'DELETE' | 'UPDATE' ) Action )? | 'KEY' ColumnsNamesList ) AlterExpressionConstraintState | ( 'PRIMARY' 'KEY' | 'UNIQUE' ( 'KEY' | 'INDEX' )? ) ColumnsNamesList AlterExpressionConstraintState ( 'USING' RelObjectName )? | 'CHECK' ( '(' Expression ')' )* ) | RelObjectName 'COMMENT' S_CHAR_LITERAL )
           | 'CHANGE' 'COLUMN'? ( S_IDENTIFIER | S_QUOTED_IDENTIFIER ) AlterExpressionColumnDataType
           | 'DROP' ( 'COLUMN'? ( S_IDENTIFIER | S_QUOTED_IDENTIFIER ) 'CASCADE'? | ( 'INDEX' | 'CONSTRAINT' ( 'IF' 'EXISTS' )? ) ( S_IDENTIFIER | S_QUOTED_IDENTIFIER ) )
           | 'ALGORITHM' '='? RelObjectName
           | 'RENAME' ( 'COLUMN' ( S_IDENTIFIER | S_QUOTED_IDENTIFIER ) )? 'TO' ( S_IDENTIFIER | S_QUOTED_IDENTIFIER )
           | 'COMMENT' S_CHAR_LITERAL
           | captureRest

    Please change the caption of the issue to something like ALTER TABLE ... DROP FOREIGN KEY ... not implemented and I will look into this soonest.

  3. coderstory commented on Sep 22, 2021

    @coderstory
    coderstory
    on Sep 22, 2021
    Author

    Greetings.

    Thank you for the report, this has not been implemented yet.
    Currently supported syntax is:

    AlterExpression
         ::= ( 'ADD' | 'ALTER' | 'MODIFY' ) ( ( ( 'PRIMARY' 'KEY' | ( 'KEY' | 'INDEX' ) RelObjectName ) ColumnsNamesList AlterExpressionConstraintState | 'UNIQUE' ( ( 'KEY' | 'INDEX' ) ( S_IDENTIFIER | S_QUOTED_IDENTIFIER ) )? ColumnsNamesList ) ( 'USING' RelObjectName )? | 'COLUMN'? ( AlterExpressionColumnDataType | AlterExpressionColumnDropNotNull ) | '(' AlterExpressionColumnDataType ( ',' AlterExpressionColumnDataType )* ')' | 'FOREIGN' 'KEY' ColumnsNamesList 'REFERENCES' Table ColumnsNamesList? ( 'ON' ( 'DELETE' | 'UPDATE' ) Action )? ( 'ON' ( 'DELETE' | 'UPDATE' ) Action )? | 'CONSTRAINT' RelObjectName ( ( 'FOREIGN' 'KEY' ColumnsNamesList 'REFERENCES' Table ColumnsNamesList? ( 'ON' ( 'DELETE' | 'UPDATE' ) Action )? ( 'ON' ( 'DELETE' | 'UPDATE' ) Action )? | 'KEY' ColumnsNamesList ) AlterExpressionConstraintState | ( 'PRIMARY' 'KEY' | 'UNIQUE' ( 'KEY' | 'INDEX' )? ) ColumnsNamesList AlterExpressionConstraintState ( 'USING' RelObjectName )? | 'CHECK' ( '(' Expression ')' )* ) | RelObjectName 'COMMENT' S_CHAR_LITERAL )
           | 'CHANGE' 'COLUMN'? ( S_IDENTIFIER | S_QUOTED_IDENTIFIER ) AlterExpressionColumnDataType
           | 'DROP' ( 'COLUMN'? ( S_IDENTIFIER | S_QUOTED_IDENTIFIER ) 'CASCADE'? | ( 'INDEX' | 'CONSTRAINT' ( 'IF' 'EXISTS' )? ) ( S_IDENTIFIER | S_QUOTED_IDENTIFIER ) )
           | 'ALGORITHM' '='? RelObjectName
           | 'RENAME' ( 'COLUMN' ( S_IDENTIFIER | S_QUOTED_IDENTIFIER ) )? 'TO' ( S_IDENTIFIER | S_QUOTED_IDENTIFIER )
           | 'COMMENT' S_CHAR_LITERAL
           | captureRest

    Please change the caption of the issue to something like ALTER TABLE ... DROP FOREIGN KEY ... not implemented and I will look into this soonest.

    i edited it
    thanks your work

  4. manticore-projects commented on Sep 25, 2021

    @manticore-projects
    manticore-projects
    on Sep 25, 2021
    Contributor

    Same for DROP PRIMARY KEY

  5. manticore-projects
    added a commit that references this issue on Sep 25, 2021

    Enhance ALTER TABLE ... DROP CONSTRAINTS ...

    d244be0

  6. manticore-projects commented on Sep 25, 2021

    @manticore-projects
    manticore-projects
    on Sep 25, 2021
    Contributor

    Greetings.

    Please be aware that ALTER TABLE ... DROP FOREIGN KEY (...) is not Oracle compliant and other RDBMS will use a List of COLUMNs but not a CONSTRAINT identifier.

    https://docs.snowflake.com/en/sql-reference/constraints-drop.html#id1 is what I have implemented.

    If your want to drop a FOREIGN KEY using an identifier, then please use DROP CONSTRAINT identifier instead.

  7. coderstory commented on Sep 26, 2021

    @coderstory
    coderstory
    on Sep 26, 2021
    Author

    Greetings.

    Please be aware that ALTER TABLE ... DROP FOREIGN KEY (...) is not Oracle compliant and other RDBMS will use a List of COLUMNs but not a CONSTRAINT identifier.

    https://docs.snowflake.com/en/sql-reference/constraints-drop.html#id1 is what I have implemented.

    If your want to drop a FOREIGN KEY using an identifier, then please use DROP CONSTRAINT identifier instead.

    DROP CONSTRAINT identifier is support with mysql , but version >=8.0.19
    thanks

  8. coderstory
    closed this as completedon Sep 26, 2021
  9. wumpz
    added a commit that references this issue on Oct 8, 2021

    Enhance ALTER TABLE ... DROP CONSTRAINTS ... (#1351)

    388b7c3
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions