Skip to content

Fix issue 2089: Enhance MySQL CONVERT Statement Parsing #2117

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Merged
manticore-projects merged 2 commits into from
Nov 26, 2024
Merged

Fix issue 2089: Enhance MySQL CONVERT Statement Parsing #2117

Show file tree
Hide file tree
Changes from 1 commit
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
6227b0b
fix convert toString
mj-db Nov 25, 2024
0562417
fix mysql convert
mj-db Nov 26, 2024
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
Prev Previous commit
fix mysql convert
  • Loading branch information
@mj-db
mj-db committed Nov 26, 2024
commit 0562417b7c1abc4be75677dcc0d8a395664e1029
53 changes: 48 additions & 5 deletions src/main/java/net/sf/jsqlparser/statement/alter/AlterExpression.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -53,6 +53,11 @@ public class AlterExpression implements Serializable {
private List<PartitionDefinition> partitionDefinitions;
private List<ConstraintState> constraints;
private List<String> parameters;

private ConvertType convertType;
private boolean hasEqualForCharacterSet;
private boolean hasEqualForCollate;

private String characterSet;
private String collation;
private String lockOption;
Expand Down Expand Up @@ -196,7 +201,7 @@ public boolean isOnDeleteCascade() {
/**
* @param onDeleteCascade
* @deprecated use
* {@link #setReferentialAction(ReferentialAction.Type, ReferentialAction.Action, boolean)}
* {@link #setReferentialAction(ReferentialAction.Type, ReferentialAction.Action, boolean)}
*/
@Deprecated
public void setOnDeleteCascade(boolean onDeleteCascade) {
Expand All @@ -216,7 +221,7 @@ public boolean isOnDeleteRestrict() {
/**
* @param onDeleteRestrict
* @deprecated use
* {@link #setReferentialAction(ReferentialAction.Type, ReferentialAction.Action, boolean)}
* {@link #setReferentialAction(ReferentialAction.Type, ReferentialAction.Action, boolean)}
*/
@Deprecated
public void setOnDeleteRestrict(boolean onDeleteRestrict) {
Expand All @@ -236,7 +241,7 @@ public boolean isOnDeleteSetNull() {
/**
* @param onDeleteSetNull
* @deprecated use
* {@link #setReferentialAction(ReferentialAction.Type, ReferentialAction.Action, boolean)}
* {@link #setReferentialAction(ReferentialAction.Type, ReferentialAction.Action, boolean)}
*/
@Deprecated
public void setOnDeleteSetNull(boolean onDeleteSetNull) {
Expand Down Expand Up @@ -401,6 +406,14 @@ public List<String> getParameters() {
return parameters;
}

public ConvertType getConvertType() {
return convertType;
}

public void setConvertType(ConvertType convertType) {
this.convertType = convertType;
}

public String getCharacterSet() {
return characterSet;
}
Expand Down Expand Up @@ -486,12 +499,30 @@ public String toString() {

b.append("DROP PRIMARY KEY ");
} else if (operation == AlterOperation.CONVERT) {
b.append("CONVERT TO CHARACTER SET ");
if (convertType == ConvertType.CONVERT_TO) {
b.append("CONVERT TO CHARACTER SET ");
} else if (convertType == ConvertType.DEFAULT_CHARACTER_SET) {
b.append("DEFAULT CHARACTER SET ");
if (hasEqualForCharacterSet) {
b.append("= ");
}
} else if (convertType == ConvertType.CHARACTER_SET) {
b.append("CHARACTER SET ");
if (hasEqualForCharacterSet) {
b.append("= ");
}
}

if (getCharacterSet() != null) {
b.append(getCharacterSet());
}

if (getCollation() != null) {
b.append(" COLLATE ").append(getCollation());
b.append(" COLLATE ");
if (hasEqualForCollate) {
b.append("= ");
}
b.append(getCollation());
}
} else if (operation == AlterOperation.DROP_UNIQUE) {

Expand Down Expand Up @@ -805,6 +836,14 @@ public void setPartitionDefinitions(List<PartitionDefinition> partitionDefinitio
this.partitionDefinitions = partitionDefinition;
}

public void setHasEqualForCharacterSet(boolean hasEqualForCharacterSet) {
this.hasEqualForCharacterSet = hasEqualForCharacterSet;
}

public void setHasEqualForCollate(boolean hasEqualForCollate) {
this.hasEqualForCollate = hasEqualForCollate;
}

public static final class ColumnDataType extends ColumnDefinition {

private final boolean withType;
Expand Down Expand Up @@ -898,4 +937,8 @@ public String toString() {
return columnName + " DROP DEFAULT";
}
}

public enum ConvertType {
CONVERT_TO, DEFAULT_CHARACTER_SET, CHARACTER_SET
}
}
26 changes: 23 additions & 3 deletions src/main/jjtree/net/sf/jsqlparser/parser/JSqlParserCC.jjt
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -7430,11 +7430,31 @@ AlterExpression AlterExpression():
<K_RENAME> <K_TO> {alterExp.setOperation(AlterOperation.RENAME_TABLE);}
(tk2=<S_IDENTIFIER> | tk2=<S_QUOTED_IDENTIFIER>) { alterExp.setNewTableName(tk2.image);}
)
|
(<K_CONVERT> { alterExp.setOperation(AlterOperation.CONVERT); }
| (<K_CONVERT> {
alterExp.setOperation(AlterOperation.CONVERT);
alterExp.setConvertType(AlterExpression.ConvertType.CONVERT_TO);
}
<K_TO> <K_CHARACTER> <K_SET> tk=<S_IDENTIFIER> { alterExp.setCharacterSet(tk.image); }
[<K_COLLATE> tk2=<S_IDENTIFIER> { alterExp.setCollation(tk2.image); }]
)
)
| (<K_DEFAULT> {
alterExp.setOperation(AlterOperation.CONVERT);
alterExp.setConvertType(AlterExpression.ConvertType.DEFAULT_CHARACTER_SET);
}
<K_CHARACTER> <K_SET> [ "=" { alterExp.setHasEqualForCharacterSet(true); } ]
tk=<S_IDENTIFIER> { alterExp.setCharacterSet(tk.image); }
[<K_COLLATE> [ "=" { alterExp.setHasEqualForCollate(true); } ]
tk2=<S_IDENTIFIER> { alterExp.setCollation(tk2.image); }]
)
| (<K_CHARACTER> <K_SET> [ "=" { alterExp.setHasEqualForCharacterSet(true); } ]
tk=<S_IDENTIFIER> {
alterExp.setOperation(AlterOperation.CONVERT);
alterExp.setConvertType(AlterExpression.ConvertType.CHARACTER_SET);
alterExp.setCharacterSet(tk.image);
}
[<K_COLLATE> [ "=" { alterExp.setHasEqualForCollate(true); } ]
tk2=<S_IDENTIFIER> { alterExp.setCollation(tk2.image); }]
)
|
(<K_COMMENT> {alterExp.setOperation(AlterOperation.COMMENT);}
["=" {alterExp.setOperation(AlterOperation.COMMENT_WITH_EQUAL_SIGN);} ]
Expand Down
69 changes: 42 additions & 27 deletions src/test/java/net/sf/jsqlparser/statement/alter/AlterTest.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -24,10 +24,14 @@
import net.sf.jsqlparser.statement.create.table.*;
import net.sf.jsqlparser.statement.create.table.Index.ColumnParams;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.params.ParameterizedTest;
import org.junit.jupiter.params.provider.Arguments;
import org.junit.jupiter.params.provider.MethodSource;

import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.stream.Stream;

import static net.sf.jsqlparser.test.TestUtils.*;
import static org.junit.jupiter.api.Assertions.*;
Expand Down Expand Up @@ -834,7 +838,7 @@ public void testAlterTableDefaultValueTrueIssue926() throws JSQLParserException
}

private void assertReferentialActionOnConstraint(Alter parsed, Action onUpdate,
Action onDelete) {
Action onDelete) {
AlterExpression alterExpression = parsed.getAlterExpressions().get(0);
ForeignKeyIndex index = (ForeignKeyIndex) alterExpression.getIndex();

Expand Down Expand Up @@ -1092,45 +1096,56 @@ public void testIssue2090LockExclusive() throws JSQLParserException {
assertEquals("EXCLUSIVE", lockExp.getLockOption());
}

@Test
public void testIssue2089() throws JSQLParserException {
String sql = "ALTER TABLE test_table CONVERT TO CHARACTER SET utf8mb4";
@ParameterizedTest
@MethodSource("provideMySQLConvertTestCases")
public void testIssue2089(String sql, String expectedCharacterSet, String expectedCollation)
throws JSQLParserException {
Statement stmt = CCJSqlParserUtil.parse(sql);
assertTrue(stmt instanceof Alter);
Alter alter = (Alter) stmt;
assertEquals("test_table", alter.getTable().getFullyQualifiedName());

List<AlterExpression> alterExpressions = alter.getAlterExpressions();
assertNotNull(alterExpressions);
assertEquals(1, alterExpressions.size());

AlterExpression convertExp = alterExpressions.get(0);
assertEquals(AlterOperation.CONVERT, convertExp.getOperation());
assertEquals("utf8mb4", convertExp.getCharacterSet());
assertNull(convertExp.getCollation());
}
assertTrue(stmt instanceof Alter,
"Expected instance of Alter but got: " + stmt.getClass().getSimpleName());

@Test
public void testIssue2089ConvertWithCollation() throws JSQLParserException {
String sql =
"ALTER TABLE test_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci";
Statement stmt = CCJSqlParserUtil.parse(sql);
assertTrue(stmt instanceof Alter);
Alter alter = (Alter) stmt;
assertEquals("test_table", alter.getTable().getFullyQualifiedName());

List<AlterExpression> alterExpressions = alter.getAlterExpressions();
assertNotNull(alterExpressions);
assertEquals(1, alterExpressions.size());
assertNotNull(alterExpressions, "Alter expressions should not be null for SQL: " + sql);
assertEquals(1, alterExpressions.size(), "Expected 1 alter expression for SQL: " + sql);

AlterExpression convertExp = alterExpressions.get(0);
assertEquals(AlterOperation.CONVERT, convertExp.getOperation());
assertEquals("utf8mb4", convertExp.getCharacterSet());
assertEquals("utf8mb4_general_ci", convertExp.getCollation());

assertEquals(expectedCharacterSet, convertExp.getCharacterSet(),
"CHARACTER SET mismatch for SQL: " + sql);
assertEquals(expectedCollation, convertExp.getCollation(),
"COLLATE mismatch for SQL: " + sql);
assertSqlCanBeParsedAndDeparsed(sql);
}

private static Stream<Arguments> provideMySQLConvertTestCases() {
return Stream.of(
Arguments.of("ALTER TABLE test_table CONVERT TO CHARACTER SET utf8mb4", "utf8mb4",
null),
Arguments.of(
"ALTER TABLE test_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci",
"utf8mb4", "utf8mb4_general_ci"),
Arguments.of(
"ALTER TABLE test_table DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci",
"utf8mb4", "utf8mb4_general_ci"),
Arguments.of(
"ALTER TABLE test_table DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci",
"utf8mb4", "utf8mb4_general_ci"),
Arguments.of(
"ALTER TABLE test_table CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci",
"utf8mb4", "utf8mb4_general_ci"),
Arguments.of(
"ALTER TABLE test_table CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci",
"utf8mb4", "utf8mb4_general_ci"),
Arguments.of("ALTER TABLE test_table DEFAULT CHARACTER SET utf8mb4", "utf8mb4",
null),
Arguments.of("ALTER TABLE test_table DEFAULT CHARACTER SET = utf8mb4", "utf8mb4",
null));
}

@Test
public void testIssue2106AlterTableAddPartition1() throws JSQLParserException {
String sql = "ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));";
Expand Down