Skip to content

feat: support mysql with rollup #1923

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 3 commits into from
Dec 19, 2023
Merged

feat: support mysql with rollup #1923

Show file tree
Hide file tree
Changes from 2 commits
Commits
Show all changes
3 commits
Select commit Hold shift + click to select a range
1bc7c08
feat: support mysql with rollup
jxnu-liguobin Dec 18, 2023
8bcf8c8
feat: support mysql with rollup
jxnu-liguobin Dec 18, 2023
511cdce
update
jxnu-liguobin Dec 19, 2023
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
26 changes: 22 additions & 4 deletions src/main/java/net/sf/jsqlparser/statement/select/GroupByElement.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -9,21 +9,22 @@
*/
package net.sf.jsqlparser.statement.select;

import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.ParenthesedExpressionList;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Optional;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.relational.ExpressionList;
import net.sf.jsqlparser.expression.operators.relational.ParenthesedExpressionList;

public class GroupByElement implements Serializable {
private ExpressionList groupByExpressions = new ExpressionList();
private List<ExpressionList> groupingSets = new ArrayList<>();
// postgres rollup is an ExpressionList
private boolean mysqlWithRollup = false;

public boolean isUsingBrackets() {
return groupByExpressions.isUsingBrackets();
Expand Down Expand Up @@ -90,6 +91,10 @@ public String toString() {
b.append(")");
}

if (isMysqlWithRollup()) {
b.append(" WITH ROLLUP");
}

return b.toString();
}

Expand Down Expand Up @@ -126,4 +131,17 @@ public GroupByElement addGroupingSets(Collection<? extends Object> groupingSets)
collection.addAll(groupingSets);
return this.withGroupingSets(collection);
}

public boolean isMysqlWithRollup() {
return mysqlWithRollup;
}

public void setMysqlWithRollup(boolean mysqlWithRollup) {
this.mysqlWithRollup = mysqlWithRollup;
}

public GroupByElement withMysqlWithRollup(boolean mysqlWithRollup) {
Copy link
Contributor

@manticore-projects manticore-projects Dec 18, 2023

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I know, that previously those "withMethods" have been implemented, but nobody ever gave me a reason why the "setMethod" should not return the object directly.

Feel free to avoid that redundant "withMethod" and return the object when calling the "set" method.

Copy link
Contributor Author

@jxnu-liguobin jxnu-liguobin Dec 19, 2023

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

To my knowledge, in functional programming, it is generally referred to as with. The true meaning is to create a new object and return it without modifying the object itself, but here, with modifies the object, so set and with are indeed duplicates at this point.

this.setMysqlWithRollup(mysqlWithRollup);
return this;
}
}
20 changes: 18 additions & 2 deletions src/main/java/net/sf/jsqlparser/statement/select/OrderByElement.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -9,9 +9,8 @@
*/
package net.sf.jsqlparser.statement.select;

import net.sf.jsqlparser.expression.Expression;

import java.io.Serializable;
import net.sf.jsqlparser.expression.Expression;

public class OrderByElement implements Serializable {

Expand All @@ -24,6 +23,8 @@ public static NullOrdering from(String ordering) {
}

private Expression expression;
// postgres rollup is an ExpressionList
private boolean mysqlWithRollup = false;
private boolean asc = true;
private boolean ascDescPresent = false;
private NullOrdering nullOrdering;
Expand Down Expand Up @@ -79,6 +80,9 @@ public String toString() {
b.append(' ');
b.append(nullOrdering == NullOrdering.NULLS_FIRST ? "NULLS FIRST" : "NULLS LAST");
}
if (isMysqlWithRollup()) {
b.append(" WITH ROLLUP");
}
return b.toString();
}

Expand Down Expand Up @@ -106,4 +110,16 @@ public <E extends Expression> E getExpression(Class<E> type) {
return type.cast(getExpression());
}

public boolean isMysqlWithRollup() {
return mysqlWithRollup;
}

public void setMysqlWithRollup(boolean mysqlWithRollup) {
this.mysqlWithRollup = mysqlWithRollup;
}

public OrderByElement withMysqlWithRollup(boolean mysqlWithRollup) {
this.setMysqlWithRollup(mysqlWithRollup);
return this;
}
}
4 changes: 4 additions & 0 deletions src/main/java/net/sf/jsqlparser/util/deparser/GroupByDeParser.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -45,5 +45,9 @@ public void deParse(GroupByElement groupBy) {
}
buffer.append(")");
}

if (groupBy.isMysqlWithRollup()) {
buffer.append(" WITH ROLLUP");
}
}
}
7 changes: 5 additions & 2 deletions src/main/java/net/sf/jsqlparser/util/deparser/OrderByDeParser.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -11,7 +11,6 @@

import java.util.Iterator;
import java.util.List;

import net.sf.jsqlparser.expression.ExpressionVisitor;
import net.sf.jsqlparser.statement.select.OrderByElement;

Expand Down Expand Up @@ -58,9 +57,13 @@ public void deParseElement(OrderByElement orderBy) {
}
if (orderBy.getNullOrdering() != null) {
buffer.append(' ');
buffer.append(orderBy.getNullOrdering() == OrderByElement.NullOrdering.NULLS_FIRST ? "NULLS FIRST"
buffer.append(orderBy.getNullOrdering() == OrderByElement.NullOrdering.NULLS_FIRST
? "NULLS FIRST"
: "NULLS LAST");
}
if (orderBy.isMysqlWithRollup()) {
buffer.append(" WITH ROLLUP");
}
}

void setExpressionVisitor(ExpressionVisitor expressionVisitor) {
Expand Down
19 changes: 12 additions & 7 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 @@ -383,6 +383,7 @@ TOKEN: /* SQL Keywords. prefixed with K_ to avoid name clashes */
| <K_RETURNING: "RETURNING">
| <K_RIGHT:"RIGHT">
| <K_ROLLBACK:"ROLLBACK">
| <K_ROLLUP:"ROLLUP">
| <K_ROOT:"ROOT">
| <K_ROW: "ROW">
| <K_ROWS: "ROWS">
Expand Down Expand Up @@ -1802,7 +1803,7 @@ String RelObjectNameWithoutValue() :
{ Token tk = null; }
{
( tk=<S_IDENTIFIER> | tk=<S_QUOTED_IDENTIFIER> | tk=<K_DATE_LITERAL> | tk=<K_DATETIMELITERAL> | tk=<K_STRING_FUNCTION_NAME> | tk=<K_ISOLATION> | tk=<K_TIME_KEY_EXPR>
| tk="ACTION" | tk="ACTIVE" | tk="ADD" | tk="ADVANCE" | tk="ADVISE" | tk="AGAINST" | tk="ALGORITHM" | tk="ALTER" | tk="ANALYZE" | tk="APPLY" | tk="ARCHIVE" | tk="ARRAY" | tk="ASC" | tk="AT" | tk="AUTHORIZATION" | tk="AUTO" | tk="BEGIN" | tk="BERNOULLI" | tk="BINARY" | tk="BIT" | tk="BLOCK" | tk="BROWSE" | tk="BUFFERS" | tk="BY" | tk="BYTE" | tk="BYTES" | tk="CACHE" | tk="CALL" | tk="CASCADE" | tk="CASE" | tk="CAST" | tk="CHANGE" | tk="CHANGES" | tk="CHAR" | tk="CHARACTER" | tk="CHECKPOINT" | tk="CLOSE" | tk="COLLATE" | tk="COLUMN" | tk="COLUMNS" | tk="COMMENT" | tk="COMMIT" | tk="CONCURRENTLY" | tk="CONFLICT" | tk="CONSTRAINTS" | tk="CONVERT" | tk="COSTS" | tk="CS" | tk="CYCLE" | tk="DATA" | tk="DATABASE" | tk="DDL" | tk="DECLARE" | tk="DEFAULT" | tk="DEFERRABLE" | tk="DELAYED" | tk="DELETE" | tk="DESC" | tk="DESCRIBE" | tk="DISABLE" | tk="DISCONNECT" | tk="DIV" | tk="DML" | tk="DO" | tk="DOMAIN" | tk="DROP" | tk="DUMP" | tk="DUPLICATE" | tk="ELEMENTS" | tk="EMIT" | tk="ENABLE" | tk="END" | tk="ESCAPE" | tk="EXCLUDE" | tk="EXEC" | tk="EXECUTE" | tk="EXPLAIN" | tk="EXPLICIT" | tk="EXTENDED" | tk="EXTRACT" | tk="FALSE" | tk="FILTER" | tk="FIRST" | tk="FLUSH" | tk="FN" | tk="FOLLOWING" | tk="FORMAT" | tk="FULLTEXT" | tk="FUNCTION" | tk="GRANT" | tk="GUARD" | tk="HASH" | tk="HISTORY" | tk="HOPPING" | tk="INCLUDE" | tk="INCREMENT" | tk="INDEX" | tk="INSERT" | tk="INTERLEAVE" | tk="INTERPRET" | tk="INVALIDATE" | tk="ISNULL" | tk="JSON" | tk="KEEP" | tk="KEY" | tk="KEYS" | tk="LAST" | tk="LEADING" | tk="LINK" | tk="LOCAL" | tk="LOCK" | tk="LOCKED" | tk="LOG" | tk="LOOP" | tk="MATCH" | tk="MATCHED" | tk="MATERIALIZED" | tk="MAXVALUE" | tk="MEMBER" | tk="MERGE" | tk="MINVALUE" | tk="MODE" | tk="MODIFY" | tk="MOVEMENT" | tk="NEXT" | tk="NO" | tk="NOCACHE" | tk="NOKEEP" | tk="NOLOCK" | tk="NOMAXVALUE" | tk="NOMINVALUE" | tk="NOORDER" | tk="NOTHING" | tk="NOTNULL" | tk="NOVALIDATE" | tk="NOWAIT" | tk="NULLS" | tk="OF" | tk="OFF" | tk="OPEN" | tk="OVER" | tk="OVERLAPS" | tk="PARALLEL" | tk="PARENT" | tk="PARTITION" | tk="PATH" | tk="PERCENT" | tk="PLACING" | tk="PRECEDING" | tk="PRECISION" | tk="PRIMARY" | tk="PRIOR" | tk="PURGE" | tk="QUERY" | tk="QUICK" | tk="QUIESCE" | tk="RANGE" | tk="RAW" | tk="READ" | tk="RECYCLEBIN" | tk="REFERENCES" | tk="REFRESH" | tk="REGISTER" | tk="REMOTE" | tk="RENAME" | tk="REPEATABLE" | tk="REPLACE" | tk="RESET" | tk="RESTART" | tk="RESTRICT" | tk="RESTRICTED" | tk="RESUMABLE" | tk="RESUME" | tk="RETURN" | tk="RLIKE" | tk="ROLLBACK" | tk="ROOT" | tk="ROW" | tk="ROWS" | tk="RR" | tk="RS" | tk="SAVEPOINT" | tk="SCHEMA" | tk="SEED" | tk="SEPARATOR" | tk="SEQUENCE" | tk="SESSION" | tk="SETS" | tk="SHARE" | tk="SHOW" | tk="SHUTDOWN" | tk="SIBLINGS" | tk="SIGNED" | tk="SIMILAR" | tk="SIZE" | tk="SKIP" | tk="STORED" | tk="STRING" | tk="SUSPEND" | tk="SWITCH" | tk="SYNONYM" | tk="SYSTEM" | tk="TABLE" | tk="TABLESPACE" | tk="TEMP" | tk="TEMPORARY" | tk="THEN" | tk="TIMEOUT" | tk="TIMESTAMPTZ" | tk="TO" | tk="TRIGGER" | tk="TRUE" | tk="TRUNCATE" | tk="TUMBLING" | tk="TYPE" | tk="UNLOGGED" | tk="UNQIESCE" | tk="UNSIGNED" | tk="UPDATE" | tk="UPSERT" | tk="UR" | tk="USER" | tk="VALIDATE" | tk="VERBOSE" | tk="VIEW" | tk="WAIT" | tk="WITHIN" | tk="WITHOUT" | tk="WORK" | tk="XML" | tk="XMLAGG" | tk="XMLDATA" | tk="XMLSCHEMA" | tk="XMLTEXT" | tk="XSINIL" | tk="YAML" | tk="YES" | tk="ZONE" )
| tk="ACTION" | tk="ACTIVE" | tk="ADD" | tk="ADVANCE" | tk="ADVISE" | tk="AGAINST" | tk="ALGORITHM" | tk="ALTER" | tk="ANALYZE" | tk="APPLY" | tk="ARCHIVE" | tk="ARRAY" | tk="ASC" | tk="AT" | tk="AUTHORIZATION" | tk="AUTO" | tk="BEGIN" | tk="BERNOULLI" | tk="BINARY" | tk="BIT" | tk="BLOCK" | tk="BROWSE" | tk="BUFFERS" | tk="BY" | tk="BYTE" | tk="BYTES" | tk="CACHE" | tk="CALL" | tk="CASCADE" | tk="CASE" | tk="CAST" | tk="CHANGE" | tk="CHANGES" | tk="CHAR" | tk="CHARACTER" | tk="CHECKPOINT" | tk="CLOSE" | tk="COLLATE" | tk="COLUMN" | tk="COLUMNS" | tk="COMMENT" | tk="COMMIT" | tk="CONCURRENTLY" | tk="CONFLICT" | tk="CONSTRAINTS" | tk="CONVERT" | tk="COSTS" | tk="CS" | tk="CYCLE" | tk="DATA" | tk="DATABASE" | tk="DDL" | tk="DECLARE" | tk="DEFAULT" | tk="DEFERRABLE" | tk="DELAYED" | tk="DELETE" | tk="DESC" | tk="DESCRIBE" | tk="DISABLE" | tk="DISCONNECT" | tk="DIV" | tk="DML" | tk="DO" | tk="DOMAIN" | tk="DROP" | tk="DUMP" | tk="DUPLICATE" | tk="ELEMENTS" | tk="EMIT" | tk="ENABLE" | tk="END" | tk="ESCAPE" | tk="EXCLUDE" | tk="EXEC" | tk="EXECUTE" | tk="EXPLAIN" | tk="EXPLICIT" | tk="EXTENDED" | tk="EXTRACT" | tk="FALSE" | tk="FILTER" | tk="FIRST" | tk="FLUSH" | tk="FN" | tk="FOLLOWING" | tk="FORMAT" | tk="FULLTEXT" | tk="FUNCTION" | tk="GRANT" | tk="GUARD" | tk="HASH" | tk="HISTORY" | tk="HOPPING" | tk="INCLUDE" | tk="INCREMENT" | tk="INDEX" | tk="INSERT" | tk="INTERLEAVE" | tk="INTERPRET" | tk="INVALIDATE" | tk="ISNULL" | tk="JSON" | tk="KEEP" | tk="KEY" | tk="KEYS" | tk="LAST" | tk="LEADING" | tk="LINK" | tk="LOCAL" | tk="LOCKED" | tk="LOG" | tk="LOOP" | tk="MATCH" | tk="MATCHED" | tk="MATERIALIZED" | tk="MAXVALUE" | tk="MEMBER" | tk="MERGE" | tk="MINVALUE" | tk="MODIFY" | tk="MOVEMENT" | tk="NEXT" | tk="NO" | tk="NOCACHE" | tk="NOKEEP" | tk="NOLOCK" | tk="NOMAXVALUE" | tk="NOMINVALUE" | tk="NOORDER" | tk="NOTHING" | tk="NOTNULL" | tk="NOVALIDATE" | tk="NOWAIT" | tk="NULLS" | tk="OF" | tk="OFF" | tk="OPEN" | tk="OVER" | tk="OVERLAPS" | tk="PARALLEL" | tk="PARENT" | tk="PARTITION" | tk="PATH" | tk="PERCENT" | tk="PLACING" | tk="PRECEDING" | tk="PRECISION" | tk="PRIMARY" | tk="PRIOR" | tk="PURGE" | tk="QUERY" | tk="QUICK" | tk="QUIESCE" | tk="RANGE" | tk="RAW" | tk="READ" | tk="RECYCLEBIN" | tk="REFERENCES" | tk="REFRESH" | tk="REGISTER" | tk="REMOTE" | tk="RENAME" | tk="REPEATABLE" | tk="REPLACE" | tk="RESET" | tk="RESTART" | tk="RESTRICT" | tk="RESTRICTED" | tk="RESUMABLE" | tk="RESUME" | tk="RETURN" | tk="RLIKE" | tk="ROLLBACK" | tk="ROLLUP" | tk="ROOT" | tk="ROW" | tk="ROWS" | tk="RR" | tk="RS" | tk="SAVEPOINT" | tk="SCHEMA" | tk="SEED" | tk="SEPARATOR" | tk="SEQUENCE" | tk="SESSION" | tk="SETS" | tk="SHARE" | tk="SHOW" | tk="SHUTDOWN" | tk="SIBLINGS" | tk="SIGNED" | tk="SIMILAR" | tk="SIZE" | tk="SKIP" | tk="STORED" | tk="STRING" | tk="SUSPEND" | tk="SWITCH" | tk="SYNONYM" | tk="SYSTEM" | tk="TABLE" | tk="TABLESPACE" | tk="TEMP" | tk="TEMPORARY" | tk="THEN" | tk="TIMEOUT" | tk="TIMESTAMPTZ" | tk="TO" | tk="TRIGGER" | tk="TRUE" | tk="TRUNCATE" | tk="TUMBLING" | tk="TYPE" | tk="UNLOGGED" | tk="UNQIESCE" | tk="UNSIGNED" | tk="UPDATE" | tk="UPSERT" | tk="UR" | tk="USER" | tk="VALIDATE" | tk="VERBOSE" | tk="VIEW" | tk="WAIT" | tk="WITHIN" | tk="WITHOUT" | tk="WORK" | tk="XML" | tk="XMLAGG" | tk="XMLDATA" | tk="XMLSCHEMA" | tk="XMLTEXT" | tk="XSINIL" | tk="YAML" | tk="YES" | tk="ZONE" )
{ return tk.image; }
}

Expand Down Expand Up @@ -2984,20 +2985,23 @@ GroupByElement GroupByColumnReferences():
<K_GROUP> <K_BY>
(
LOOKAHEAD(2) (
<K_GROUPING> <K_SETS> "("
list = GroupingSet() { groupBy.addGroupingSet(list); }
( LOOKAHEAD(2) "," list = GroupingSet() { groupBy.addGroupingSet(list); })*
<K_GROUPING> <K_SETS>
"("
list = GroupingSet() { groupBy.addGroupingSet(list); }
( LOOKAHEAD(2) "," list = GroupingSet() { groupBy.addGroupingSet(list); })*
")"
)
|
(
list = ExpressionList() { groupBy.setGroupByExpressions(list); }
(
LOOKAHEAD(2) <K_GROUPING> <K_SETS> "("
list = GroupingSet() { groupBy.addGroupingSet(list); }
( LOOKAHEAD(2) "," list = GroupingSet() { groupBy.addGroupingSet(list); })*
LOOKAHEAD(2) <K_GROUPING> <K_SETS>
"("
list = GroupingSet() { groupBy.addGroupingSet(list); }
( LOOKAHEAD(2) "," list = GroupingSet() { groupBy.addGroupingSet(list); })*
")"
)?
[ LOOKAHEAD(2) <K_WITH> <K_ROLLUP> { groupBy.setMysqlWithRollup(true); } ]
)
)
{
Expand Down Expand Up @@ -3072,6 +3076,7 @@ OrderByElement OrderByElement():
)
]
]
[ LOOKAHEAD(2) <K_WITH> <K_ROLLUP> { orderByElement.setMysqlWithRollup(true); } ]
{
orderByElement.setExpression(columnReference);
return orderByElement;
Expand Down
71 changes: 41 additions & 30 deletions src/test/java/net/sf/jsqlparser/statement/select/SelectTest.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -1480,20 +1480,20 @@ public void testCase() throws JSQLParserException {

statement =
"SELECT a, (CASE " + "WHEN (CASE a WHEN 1 THEN 10 ELSE 20 END) > 15 THEN 'BBB' " + // "WHEN
// (SELECT
// c
// FROM
// tab2
// WHERE
// d
// =
// 2)
// =
// 3
// THEN
// 'AAA'
// "
// +
// (SELECT
// c
// FROM
// tab2
// WHERE
// d
// =
// 2)
// =
// 3
// THEN
// 'AAA'
// "
// +
"END) FROM tab1";
assertSqlCanBeParsedAndDeparsed(statement);
}
Expand Down Expand Up @@ -2971,22 +2971,22 @@ public void testPostgreSQLRegExpCaseSensitiveMatch4() throws JSQLParserException
public void testReservedKeyword() throws JSQLParserException {
final String statement =
"SELECT cast, do, extract, first, following, last, materialized, nulls, partition, range, row, rows, siblings, value, xml FROM tableName"; // all
// of
// these
// are
// legal
// in
// SQL
// server;
// 'row'
// and
// 'rows'
// are
// not
// legal
// on
// Oracle,
// though;
// of
// these
// are
// legal
// in
// SQL
// server;
// 'row'
// and
// 'rows'
// are
// not
// legal
// on
// Oracle,
// though;
final Select select = (Select) parserManager.parse(new StringReader(statement));
assertStatementCanBeDeparsedAs(select, statement);
}
Expand Down Expand Up @@ -5775,6 +5775,17 @@ public void testNotNullInFilter() throws JSQLParserException {
assertSqlCanBeParsedAndDeparsed(stmt);
}

@Test
public void testIssue1907() throws JSQLParserException {
String stmt = "SELECT MAX(a, b, c), COUNT(*), D FROM tab1 GROUP BY D WITH ROLLUP";
assertSqlCanBeParsedAndDeparsed(stmt);

// since mysql 8.0.12
String stmt2 =
"SELECT * FROM (SELECT year, person, SUM(amount) FROM rentals GROUP BY year, person) t1 ORDER BY year DESC WITH ROLLUP";
assertSqlCanBeParsedAndDeparsed(stmt2);
}

@Test
public void testNotIsNullInFilter() throws JSQLParserException {
String stmt = "SELECT count(*) FILTER (WHERE i NOT ISNULL) AS filtered FROM tasks";
Expand Down
Loading