|
| 1 | +/*- |
| 2 | + * #%L |
| 3 | + * JSQLParser library |
| 4 | + * %% |
| 5 | + * Copyright (C) 2004 - 2026 JSQLParser |
| 6 | + * %% |
| 7 | + * Dual licensed under GNU LGPL 2.1 or Apache License 2.0 |
| 8 | + * #L% |
| 9 | + */ |
| 10 | +package net.sf.jsqlparser.parser.dialect; |
| 11 | + |
| 12 | +import net.sf.jsqlparser.JSQLParserException; |
| 13 | +import net.sf.jsqlparser.parser.AbstractJSqlParser.Dialect; |
| 14 | +import net.sf.jsqlparser.parser.CCJSqlParser; |
| 15 | +import net.sf.jsqlparser.parser.CCJSqlParserUtil; |
| 16 | +import org.junit.jupiter.api.Test; |
| 17 | +import org.junit.jupiter.params.ParameterizedTest; |
| 18 | +import org.junit.jupiter.params.provider.Arguments; |
| 19 | +import org.junit.jupiter.params.provider.MethodSource; |
| 20 | + |
| 21 | +import java.io.BufferedReader; |
| 22 | +import java.io.IOException; |
| 23 | +import java.io.InputStreamReader; |
| 24 | +import java.nio.charset.StandardCharsets; |
| 25 | +import java.util.ArrayList; |
| 26 | +import java.util.Arrays; |
| 27 | +import java.util.HashMap; |
| 28 | +import java.util.LinkedHashMap; |
| 29 | +import java.util.List; |
| 30 | +import java.util.Map; |
| 31 | +import java.util.function.Consumer; |
| 32 | +import java.util.stream.Stream; |
| 33 | + |
| 34 | +import static net.sf.jsqlparser.test.TestUtils.assertSqlCanBeParsedAndDeparsed; |
| 35 | +import static org.junit.jupiter.api.Assertions.assertEquals; |
| 36 | +import static org.junit.jupiter.api.Assertions.assertThrows; |
| 37 | + |
| 38 | +/** |
| 39 | + * With {@link Dialect#DUCKDB}, every DuckDB keyword parses in each name position exactly when |
| 40 | + * DuckDB 1.5.5 itself parses it ({@code duckdb-keyword-names.csv}), except the exemptions below. |
| 41 | + * Function names are not compared: DuckDB's grammar has dedicated syntax for many keyword-named |
| 42 | + * functions (COALESCE, NULLIF, IF, ...), so whether a call parses depends on the function and its |
| 43 | + * arguments rather than on keyword reservation. |
| 44 | + */ |
| 45 | +public class DuckDbKeywordPolicyTest { |
| 46 | + private static final Consumer<CCJSqlParser> DUCKDB = |
| 47 | + parser -> parser.withDialect(Dialect.DUCKDB); |
| 48 | + |
| 49 | + private static final Map<String, String> POSITIONS = new LinkedHashMap<>(); |
| 50 | + static { |
| 51 | + POSITIONS.put("column", "SELECT %s FROM t"); |
| 52 | + POSITIONS.put("qualified_column", "SELECT t.%s FROM t"); |
| 53 | + POSITIONS.put("as_alias", "SELECT 1 AS %s"); |
| 54 | + POSITIONS.put("bare_alias", "SELECT 1 %s"); |
| 55 | + POSITIONS.put("table", "SELECT * FROM %s"); |
| 56 | + POSITIONS.put("table_alias", "SELECT * FROM t %s"); |
| 57 | + POSITIONS.put("cte", "WITH %1$s AS (SELECT 1) SELECT * FROM %1$s"); |
| 58 | + } |
| 59 | + |
| 60 | + /** |
| 61 | + * Differences that do not come from keyword reservation: JSqlParser parses these words as |
| 62 | + * dedicated syntax at that position, or as a date unit. |
| 63 | + */ |
| 64 | + private static final Map<String, List<String>> EXEMPTIONS = new HashMap<>(); |
| 65 | + static { |
| 66 | + // constructors, casts and expressions: MAP {...}, STRUCT(...), TRY_CAST(...), COLUMNS(...) |
| 67 | + for (String keyword : Arrays.asList("map", "struct", "try_cast", "columns", "generated")) { |
| 68 | + EXEMPTIONS.put(keyword, Arrays.asList("column", "table", "table_alias", "cte")); |
| 69 | + } |
| 70 | + EXEMPTIONS.put("default", Arrays.asList("column")); |
| 71 | + EXEMPTIONS.put("prior", Arrays.asList("column")); |
| 72 | + // WITH RECURSIVE, FETCH ... WITH TIES, UPDATE ... SET, a new SELECT after AS |
| 73 | + EXEMPTIONS.put("recursive", Arrays.asList("cte")); |
| 74 | + EXEMPTIONS.put("ties", Arrays.asList("cte")); |
| 75 | + EXEMPTIONS.put("set", Arrays.asList("table_alias")); |
| 76 | + EXEMPTIONS.put("select", Arrays.asList("as_alias")); |
| 77 | + // 1 DAY is a date unit, not an alias |
| 78 | + for (String keyword : Arrays.asList("day", "hour", "minute", "month", "second", "year")) { |
| 79 | + EXEMPTIONS.put(keyword, Arrays.asList("bare_alias")); |
| 80 | + } |
| 81 | + } |
| 82 | + |
| 83 | + static Stream<Arguments> duckDbKeywordNames() throws IOException { |
| 84 | + List<Arguments> arguments = new ArrayList<>(); |
| 85 | + try (BufferedReader reader = new BufferedReader(new InputStreamReader( |
| 86 | + DuckDbKeywordPolicyTest.class.getResourceAsStream("duckdb-keyword-names.csv"), |
| 87 | + StandardCharsets.UTF_8))) { |
| 88 | + List<String> header = null; |
| 89 | + for (String line = reader.readLine(); line != null; line = reader.readLine()) { |
| 90 | + if (line.startsWith("#")) { |
| 91 | + continue; |
| 92 | + } |
| 93 | + List<String> cells = Arrays.asList(line.split(",")); |
| 94 | + if (header == null) { |
| 95 | + header = cells; |
| 96 | + continue; |
| 97 | + } |
| 98 | + String keyword = cells.get(0); |
| 99 | + for (int i = 1; i < cells.size(); i++) { |
| 100 | + String position = header.get(i); |
| 101 | + if (!EXEMPTIONS.getOrDefault(keyword, List.of()).contains(position)) { |
| 102 | + arguments.add(Arguments.of(keyword, position, "1".equals(cells.get(i)))); |
| 103 | + } |
| 104 | + } |
| 105 | + } |
| 106 | + } |
| 107 | + return arguments.stream(); |
| 108 | + } |
| 109 | + |
| 110 | + @ParameterizedTest(name = "{0} as {1}: {2}") |
| 111 | + @MethodSource("duckDbKeywordNames") |
| 112 | + void parsesLikeDuckDb(String keyword, String position, boolean duckDbParses) { |
| 113 | + String sql = String.format(POSITIONS.get(position), keyword); |
| 114 | + boolean parses; |
| 115 | + try { |
| 116 | + CCJSqlParserUtil.parse(sql, DUCKDB); |
| 117 | + parses = true; |
| 118 | + } catch (JSQLParserException e) { |
| 119 | + parses = false; |
| 120 | + } |
| 121 | + assertEquals(duckDbParses, parses, sql); |
| 122 | + } |
| 123 | + |
| 124 | + @Test |
| 125 | + void keepsDefaultReservationWithoutTheDialect() throws JSQLParserException { |
| 126 | + assertThrows(JSQLParserException.class, |
| 127 | + () -> CCJSqlParserUtil.parse("SELECT settings, file FROM script")); |
| 128 | + assertSqlCanBeParsedAndDeparsed("SELECT * FROM t PREWHERE a = 1 WHERE b = 2", true); |
| 129 | + assertSqlCanBeParsedAndDeparsed("SELECT 1 FROM t MINUS SELECT 2 FROM u", true); |
| 130 | + assertSqlCanBeParsedAndDeparsed("SELECT a desc FROM t", true); |
| 131 | + } |
| 132 | + |
| 133 | + @Test |
| 134 | + void parsesDuckDbSyntaxUsingItsKeywords() throws JSQLParserException { |
| 135 | + assertSqlCanBeParsedAndDeparsed("SELECT settings, file FROM script s WHERE s.top > 1", true, |
| 136 | + DUCKDB); |
| 137 | + assertSqlCanBeParsedAndDeparsed("UPDATE t SET a = 1", true, DUCKDB); |
| 138 | + assertSqlCanBeParsedAndDeparsed("SELECT a FROM t USING SAMPLE 10%", true, DUCKDB); |
| 139 | + assertSqlCanBeParsedAndDeparsed("COPY t TO 'out.csv' (FORMAT csv)", true, DUCKDB); |
| 140 | + assertSqlCanBeParsedAndDeparsed("SELECT a AS from, t.where FROM t ORDER BY a DESC", true, |
| 141 | + DUCKDB); |
| 142 | + } |
| 143 | + |
| 144 | + @Test |
| 145 | + void rejectsOnlyDuckDbReservedKeywordsAsFunctionNames() throws JSQLParserException { |
| 146 | + for (String sql : Arrays.asList("SELECT order(1)", "SELECT where(1)", "SELECT table(1)")) { |
| 147 | + assertThrows(JSQLParserException.class, () -> CCJSqlParserUtil.parse(sql, DUCKDB), sql); |
| 148 | + } |
| 149 | + assertSqlCanBeParsedAndDeparsed( |
| 150 | + "SELECT coalesce(a, b), nullif(a, b), if(a > 1, 1, 2), greatest(a, b) FROM t", true, |
| 151 | + DUCKDB); |
| 152 | + } |
| 153 | + |
| 154 | + @Test |
| 155 | + void rejectsDuckDbReservedKeywordsAsNames() { |
| 156 | + for (String sql : Arrays.asList("SELECT desc FROM t", "SELECT 1 name", |
| 157 | + "SELECT * FROM table")) { |
| 158 | + assertThrows(JSQLParserException.class, () -> CCJSqlParserUtil.parse(sql, DUCKDB), sql); |
| 159 | + } |
| 160 | + } |
| 161 | +} |