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 1 commit
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
Prev Previous commit
update
  • Loading branch information
@jxnu-liguobin
jxnu-liguobin committed Dec 19, 2023
commit 511cdce2bf98308bb5f35e57771ed2738d6524f8
6 changes: 1 addition & 5 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 @@ -136,12 +136,8 @@ public boolean isMysqlWithRollup() {
return mysqlWithRollup;
}

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

public GroupByElement withMysqlWithRollup(boolean mysqlWithRollup) {
this.setMysqlWithRollup(mysqlWithRollup);
return this;
}
}
7 changes: 2 additions & 5 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 @@ -114,12 +114,9 @@ public boolean isMysqlWithRollup() {
return mysqlWithRollup;
}

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

public OrderByElement withMysqlWithRollup(boolean mysqlWithRollup) {
this.setMysqlWithRollup(mysqlWithRollup);
return this;
}

}
87 changes: 42 additions & 45 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 @@ -1478,24 +1478,21 @@ public void testCase() throws JSQLParserException {
statement = "SELECT a FROM tab1 WHERE CASE b WHEN 1 THEN 2 + 3 ELSE 4 END > 34";
assertSqlCanBeParsedAndDeparsed(statement);

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'
// "
// +
"END) FROM tab1";
assertSqlCanBeParsedAndDeparsed(statement);
statement = "SELECT a\n"
+ " , ( CASE\n"
+ " WHEN ( CASE\n"
+ " WHEN 1\n"
+ " THEN 10\n"
+ " ELSE 20\n"
+ " END ) > 15\n"
+ " THEN 'BBB'\n"
+ " WHEN ( SELECT c\n"
+ " FROM tab2\n"
+ " WHERE d = 2 ) = 3\n"
+ " THEN 'AAA'\n"
+ " END )\n"
+ "FROM tab1\n";
assertSqlCanBeParsedAndDeparsed(statement, true);
}

@Test
Expand Down Expand Up @@ -2971,22 +2968,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,17 +5772,6 @@ 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 All @@ -5809,4 +5795,15 @@ void testBackSlashQuotationIssue1812() throws JSQLParserException {
sqlStr, parser -> parser
.withBackslashEscapeCharacter(true));
}

@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);
}
}
2 changes: 1 addition & 1 deletion src/test/resources/net/sf/jsqlparser/statement/select/oracle-tests/merge04.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -27,4 +27,4 @@ mm.flags = mm.flags + v.flags - bitand(mm.flags,v.flags) , mm.drop_segments = mm
when not matched then insert values (v.obj#, v.inserts, v.updates, v.deletes, sysdate, v.flags, v.drop_segments)

--@FAILURE: merge into sys.mon_mods_all$ mm using(select decode(grouping_id(tp.bo#,tsp.pobj#,m.obj#),3,tp.bo#,1,tsp.pobj#,m.obj#)obj#,sum(m.inserts)inserts,sum(m.updates)updates,sum(m.deletes)deletes,decode(sum(bitand(m.flags,1)),0,0,1)+decode(sum(bitand(m.flags,2)),0,0,2)+decode(sum(bitand(m.flags,4)),0,0,4)flags,sum(m.drop_segments)drop_segments from sys.mon_mods$ m,sys.tabcompart$ tp,sys.tabsubpart$ tsp where m.obj#=tsp.obj# and tp.obj#=tsp.pobj# group by rollup(tp.bo#,tsp.pobj#,m.obj#)having grouping_id(tp.bo#,tsp.pobj#,m.obj#)<7 order by 1,2,3)v on(mm.obj#=v.obj#)when matched then update set mm.inserts=mm.inserts+v.inserts,mm.updates=mm.updates+v.updates,mm.deletes=mm.deletes+v.deletes,mm.flags=mm.flags+v.flags-bitand(mm.flags,v.flags),mm.drop_segments=mm.drop_segments+v.drop_segments when not matched then insert values(v.obj#,v.inserts,v.updates,v.deletes,sysdate,v.flags,v.drop_segments) recorded first on Aug 3, 2021, 7:20:08 AM
--@FAILURE: Encountered unexpected token: recorded first on 2023年12月18日 下午3:48:33
--@SUCCESSFULLY_PARSED_AND_DEPARSED first on 18 Dec 2023, 17:18:40
You are viewing a condensed version of this merge commit. You can view the full changes here.