@Test public void testIdentifier() { checkExp("ab", "`AB`"); checkExp(" \"a \"\" b!c\"", "`a \" b!c`"); checkExpFails(" ^`^a \" b!c`", "(?s).*Encountered.*"); checkExp("\"x`y`z\"", "`x``y``z`"); checkExpFails("^`^x`y`z`", "(?s).*Encountered.*"); checkExp("myMap[field] + myArray[1 + 2]", "(`MYMAP`[`FIELD`] + `MYARRAY`[(1 + 2)])"); }
@Test public void testArithmeticOperators() { checkExp("1-2+3*4/5/6-7", "(((1 - 2) + (((3 * 4) / 5) / 6)) - 7)"); checkExp("power(2,3)", "POWER(2, 3)"); checkExp("aBs(-2.3e-2)", "ABS(-2.3E-2)"); checkExp("MOD(5 ,\t\f\r\n2)", "(MOD(5, 2))"); checkExp("ln(5.43 )", "LN(5.43)"); checkExp("log10(- -.2 )", "LOG10(0.2)"); }
@Test public void testBackTickIdentifier() { quoting = Quoting.BACK_TICK; checkExp("ab", "`AB`"); checkExp(" `a \" b!c`", "`a \" b!c`"); checkExpFails(" ^\"^a \"\" b!c\"", "(?s).*Encountered.*"); checkExpFails("^\"^x`y`z\"", "(?s).*Encountered.*"); checkExp("`x``y``z`", "`x``y``z`"); checkExp("myMap[field] + myArray[1 + 2]", "(`MYMAP`[`FIELD`] + `MYARRAY`[(1 + 2)])"); }
@Test public void testIdentifier() { checkExp("ab", "`AB`"); checkExp(" \"a \"\" b!c\"", "`a \" b!c`"); checkExpFails(" ^`^a \" b!c`", "(?s).*Encountered.*"); checkExp("\"x`y`z\"", "`x``y``z`"); checkExpFails("^`^x`y`z`", "(?s).*Encountered.*"); checkExp("myMap[field] + myArray[1 + 2]", "(`MYMAP`[`FIELD`] + `MYARRAY`[(1 + 2)])"); }
@Test public void testJsonArray() { checkExp("json_array('foo')", "JSON_ARRAY('foo' ABSENT ON NULL)"); checkExp("json_array(null)", "JSON_ARRAY(NULL ABSENT ON NULL)"); checkExp("json_array(null null on null)", "JSON_ARRAY(NULL NULL ON NULL)"); checkExp("json_array(json_array('foo', 'bar') format json)", "JSON_ARRAY(JSON_ARRAY('foo', 'bar' ABSENT ON NULL) FORMAT JSON ABSENT ON NULL)"); }
@Test public void testFunctionNamedArgument() { checkExp("foo(x => 1)", "`FOO`(`X` => 1)"); checkExp("foo(x => 1, \"y\" => 'a', z => x <= y)", "`FOO`(`X` => 1, `y` => 'a', `Z` => (`X` <= `Y`))"); checkExpFails("foo(x.y ^=>^ 1)", "(?s).*Encountered \"=>\" at .*"); checkExpFails("foo(a => 1, x.y ^=>^ 2, c => 3)", "(?s).*Encountered \"=>\" at .*"); }
@Test public void testNullIf() { checkExp( "nullif(v1,v2)", "(NULLIF(`V1`, `V2`))"); checkExpFails( "1 + ^nullif^ + 3", "(?s)Encountered \"nullif \\+\" at line 1, column 5.*"); }
@Test public void testWindowReference() { checkExp("sum(sal) over (w)", "(SUM(`SAL`) OVER (`W`))"); // Only 1 window reference allowed checkExpFails( "sum(sal) over (w ^w1^ partition by deptno)", "(?s)Encountered \"w1\" at.*"); }
@Test public void testArrayValueConstructor() { checkExp("array[1, 2]", "(ARRAY[1, 2])"); checkExp("array [1, 2]", "(ARRAY[1, 2])"); // with space // parser allows empty array; validator will reject it checkExp("array[]", "(ARRAY[])"); checkExp( "array[(1, 'a'), (2, 'b')]", "(ARRAY[(ROW(1, 'a')), (ROW(2, 'b'))])"); }
@Test public void testWindowReference() { checkExp("sum(sal) over (w)", "(SUM(`SAL`) OVER (`W`))"); // Only 1 window reference allowed checkExpFails( "sum(sal) over (w ^w1^ partition by deptno)", "(?s)Encountered \"w1\" at.*"); }
public void checkExp(String sql, String expected) { SqlParserTest.this.checkExp( sql.replace("$op", op).replace("$p", period), expected.replace("$op", op.toUpperCase(Locale.ROOT))); }
@Test public void testPeriod() { // We don't have a PERIOD constructor currently; // ROW constructor is sufficient for now. checkExp("period (date '1969-01-05', interval '2-3' year to month)", "(ROW(DATE '1969-01-05', INTERVAL '2-3' YEAR TO MONTH))"); }
@Test public void testFunction() { check("select substring('Eggs and ham', 1, 3 + 2) || ' benedict' from emp", "SELECT (SUBSTRING('Eggs and ham' FROM 1 FOR (3 + 2)) || ' benedict')\n" + "FROM `EMP`"); checkExp( "log10(1)\r\n+power(2, mod(\r\n3\n\t\t\f\n,ln(4))*log10(5)-6*log10(7/abs(8)+9))*power(10,11)", "(LOG10(1) + (POWER(2, (((MOD(3, LN(4))) * LOG10(5)) - (6 * LOG10(((7 / ABS(8)) + 9))))) * POWER(10, 11)))"); }
@Test public void testFunction() { check("select substring('Eggs and ham', 1, 3 + 2) || ' benedict' from emp", "SELECT (SUBSTRING('Eggs and ham' FROM 1 FOR (3 + 2)) || ' benedict')\n" + "FROM `EMP`"); checkExp( "log10(1)\r\n+power(2, mod(\r\n3\n\t\t\f\n,ln(4))*log10(5)-6*log10(7/abs(8)+9))*power(10,11)", "(LOG10(1) + (POWER(2, (((MOD(3, LN(4))) * LOG10(5)) - (6 * LOG10(((7 / ABS(8)) + 9))))) * POWER(10, 11)))"); }