| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, software |
| * distributed under the License is distributed on an "AS IS" BASIS, |
| * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| * See the License for the specific language governing permissions and |
| * limitations under the License. |
| */ |
| package org.apache.drill.exec.expr.fn.impl; |
| |
| import static org.junit.Assert.assertTrue; |
| |
| import org.apache.commons.lang3.RandomStringUtils; |
| import org.apache.drill.categories.UnlikelyTest; |
| import org.apache.drill.test.BaseTestQuery; |
| import org.apache.drill.categories.SqlFunctionTest; |
| import org.apache.drill.exec.util.Text; |
| import org.junit.Test; |
| |
| import com.google.common.collect.ImmutableList; |
| import org.junit.experimental.categories.Category; |
| |
| import java.io.BufferedWriter; |
| import java.io.File; |
| import java.io.FileWriter; |
| |
| @Category({SqlFunctionTest.class, UnlikelyTest.class}) |
| public class TestStringFunctions extends BaseTestQuery { |
| |
| @Test |
| public void testStrPosMultiByte() throws Exception { |
| testBuilder() |
| .sqlQuery("select `position`('a', 'abc') res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues(1L) |
| .go(); |
| |
| testBuilder() |
| .sqlQuery("select `position`('\\u11E9', '\\u11E9\\u0031') res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues(1L) |
| .go(); |
| } |
| |
| @Test |
| public void testSplitPart() throws Exception { |
| testBuilder() |
| .sqlQuery("select split_part(a, '~@~', 1) res1 from (values('abc~@~def~@~ghi'), ('qwe~@~rty~@~uio')) as t(a)") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("abc") |
| .baselineValues("qwe") |
| .go(); |
| |
| testBuilder() |
| .sqlQuery("select split_part(a, '~@~', 2) res1 from (values('abc~@~def~@~ghi'), ('qwe~@~rty~@~uio')) as t(a)") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("def") |
| .baselineValues("rty") |
| .go(); |
| |
| testBuilder() |
| .sqlQuery("select split_part(a, '~@~', -2) res1 from (values('abc~@~def~@~ghi'), ('qwe~@~rty~@~uio')) as t(a)") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("def") |
| .baselineValues("rty") |
| .go(); |
| |
| // with a multi-byte splitter |
| testBuilder() |
| .sqlQuery("select split_part('abc\\u1111drill\\u1111ghi', '\\u1111', 2) res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("drill") |
| .go(); |
| |
| testBuilder() |
| .sqlQuery("select split_part('abc\\u1111drill\\u1111ghi', '\\u1111', -2) res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("drill") |
| .go(); |
| |
| // going beyond the last available index, returns empty string |
| testBuilder() |
| .sqlQuery("select split_part('a,b,c', ',', 4) res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("") |
| .go(); |
| |
| testBuilder() |
| .sqlQuery("select split_part('a,b,c', ',', -4) res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("") |
| .go(); |
| |
| // if the delimiter does not appear in the string, 1 returns the whole string |
| testBuilder() |
| .sqlQuery("select split_part('a,b,c', ' ', 1) res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("a,b,c") |
| .go(); |
| |
| testBuilder() |
| .sqlQuery("select split_part('a,b,c', ' ', -1) res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("a,b,c") |
| .go(); |
| } |
| |
| @Test |
| public void testSplitPartStartEnd() throws Exception { |
| testBuilder() |
| .sqlQuery("select split_part(a, '~@~', 1, 2) res1 from (" + |
| "values('abc~@~def~@~ghi'), ('qwe~@~rty~@~uio')) as t(a)") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("abc~@~def") |
| .baselineValues("qwe~@~rty") |
| .go(); |
| |
| testBuilder() |
| .sqlQuery("select split_part(a, '~@~', 2, 3) res1 from (" + |
| "values('abc~@~def~@~ghi'), ('qwe~@~rty~@~uio')) as t(a)") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("def~@~ghi") |
| .baselineValues("rty~@~uio") |
| .go(); |
| |
| testBuilder() |
| .sqlQuery("select split_part(a, '~@~', -2, -1) res1 from (" + |
| "values('abc~@~def~@~ghi'), ('qwe~@~rty~@~uio')) as t(a)") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("def~@~ghi") |
| .baselineValues("rty~@~uio") |
| .go(); |
| |
| // with a multi-byte splitter |
| testBuilder() |
| .sqlQuery("select split_part('abc\\u1111drill\\u1111ghi', '\\u1111', 2, 2) " + |
| "res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("drill") |
| .go(); |
| |
| testBuilder() |
| .sqlQuery("select split_part('abc\\u1111drill\\u1111ghi', '\\u1111', -2, -2) " + |
| "res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("drill") |
| .go(); |
| |
| // start index going beyond the last available index, returns empty string |
| testBuilder() |
| .sqlQuery("select split_part('a,b,c', ',', 4, 5) res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("") |
| .go(); |
| |
| testBuilder() |
| .sqlQuery("select split_part('a,b,c', ',', -5, -4) res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("") |
| .go(); |
| |
| // end index going beyond the last available index, returns remaining string |
| testBuilder() |
| .sqlQuery("select split_part('a,b,c', ',', 1, 10) res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("a,b,c") |
| .go(); |
| |
| testBuilder() |
| .sqlQuery("select split_part('a,b,c', ',', -10, -1) res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("a,b,c") |
| .go(); |
| |
| // if the delimiter does not appear in the string, 1 returns the whole string |
| testBuilder() |
| .sqlQuery("select split_part('a,b,c', ' ', 1, 2) res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("a,b,c") |
| .go(); |
| |
| testBuilder() |
| .sqlQuery("select split_part('a,b,c', ' ', -2, -1) res1 from (values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("a,b,c") |
| .go(); |
| } |
| |
| @Test |
| public void testInvalidSplitPartParameters() { |
| boolean expectedErrorEncountered; |
| try { |
| testBuilder() |
| .sqlQuery("select split_part('abc~@~def~@~ghi', '~@~', 0) res1 from " + |
| "(values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("abc") |
| .go(); |
| expectedErrorEncountered = false; |
| } catch (Exception ex) { |
| assertTrue(ex.getMessage(), |
| ex.getMessage().contains("Index in split_part can not be zero")); |
| expectedErrorEncountered = true; |
| } |
| if (!expectedErrorEncountered) { |
| throw new RuntimeException("Missing expected error on invalid index for " + |
| "split_part function"); |
| } |
| |
| try { |
| testBuilder() |
| .sqlQuery("select split_part('abc~@~def~@~ghi', '~@~', 2, 1) res1 from " + |
| "(values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("abc") |
| .go(); |
| expectedErrorEncountered = false; |
| } catch (Exception ex) { |
| assertTrue(ex.getMessage(), |
| ex.getMessage().contains("End index in split_part must be greater or equal to start index")); |
| expectedErrorEncountered = true; |
| } |
| if (!expectedErrorEncountered) { |
| throw new RuntimeException("Missing expected error on invalid index for " + |
| "split_part function"); |
| } |
| |
| try { |
| testBuilder() |
| .sqlQuery("select split_part('abc~@~def~@~ghi', '~@~', -1, -2) res1 from " + |
| "(values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("abc") |
| .go(); |
| expectedErrorEncountered = false; |
| } catch (Exception ex) { |
| assertTrue(ex.getMessage(), |
| ex.getMessage().contains("End index in split_part must be greater or equal to start index")); |
| expectedErrorEncountered = true; |
| } |
| if (!expectedErrorEncountered) { |
| throw new RuntimeException("Missing expected error on invalid index for " + |
| "split_part function"); |
| } |
| |
| try { |
| testBuilder() |
| .sqlQuery("select split_part('abc~@~def~@~ghi', '~@~', -1, 2) res1 from " + |
| "(values(1))") |
| .ordered() |
| .baselineColumns("res1") |
| .baselineValues("abc") |
| .go(); |
| expectedErrorEncountered = false; |
| } catch (Exception ex) { |
| assertTrue(ex.getMessage(), |
| ex.getMessage().contains("End index in split_part must has the same sign as the start index")); |
| expectedErrorEncountered = true; |
| } |
| if (!expectedErrorEncountered) { |
| throw new RuntimeException("Missing expected error on invalid index for " + |
| "split_part function"); |
| } |
| } |
| |
| @Test |
| public void testRegexpMatches() throws Exception { |
| testBuilder() |
| .sqlQuery("select regexp_matches(a, '^a.*') res1, regexp_matches(b, '^a.*') res2 " + |
| "from (values('abc', 'bcd'), ('bcd', 'abc')) as t(a,b)") |
| .unOrdered() |
| .baselineColumns("res1", "res2") |
| .baselineValues(true, false) |
| .baselineValues(false, true) |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testRegexpMatchesNonAscii() throws Exception { |
| testBuilder() |
| .sqlQuery("select regexp_matches(a, 'München') res1, regexp_matches(b, 'AMünchenA') res2 " + |
| "from (values('München', 'MünchenA'), ('MünchenA', 'AMünchenA')) as t(a,b)") |
| .unOrdered() |
| .baselineColumns("res1", "res2") |
| .baselineValues(true, false) |
| .baselineValues(false, true) |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testRegexpReplace() throws Exception { |
| testBuilder() |
| .sqlQuery("select regexp_replace(a, 'a|c', 'x') res1, regexp_replace(b, 'd', 'zzz') res2 " + |
| "from (values('abc', 'bcd'), ('bcd', 'abc')) as t(a,b)") |
| .unOrdered() |
| .baselineColumns("res1", "res2") |
| .baselineValues("xbx", "bczzz") |
| .baselineValues("bxd", "abc") |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testReplaceOutBuffer() throws Exception { |
| String originValue = RandomStringUtils.randomAlphabetic(8192).toLowerCase() + "12345"; |
| String expectValue = originValue.replace("12345", "67890"); |
| String sql = "select replace(c1, '12345', '67890') as col from (values('" + originValue + "')) as t(c1)"; |
| testBuilder() |
| .sqlQuery(sql) |
| .ordered() |
| .baselineColumns("col") |
| .baselineValues(expectValue) |
| .go(); |
| } |
| |
| @Test |
| public void testLikeStartsWith() throws Exception { |
| |
| // all ASCII. |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'), ('ABD'), ('ABCD'),('ABCDE')," + |
| "('AABCD'),('ABABCD'),('ABC$XYZ'), (''),('abcd')," + |
| "('x'), ('xyz'), ('%')) tbl(id) " + |
| "where id like 'ABC%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC") |
| .baselineValues("ABCD") |
| .baselineValues("ABCDE") |
| .baselineValues("ABC$XYZ") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'), ('ABD')," + |
| "('ABCD'),('ABCDE'),('AABCD'),('ABAB CD'),('ABC$XYZ')," + |
| "(''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like 'AB%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("AB") |
| .baselineValues("ABC") |
| .baselineValues("ABD") |
| .baselineValues("ABCD") |
| .baselineValues("ABCDE") |
| .baselineValues("ABAB CD") |
| .baselineValues("ABC$XYZ") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'), ('ABC'), ('ABD'), ('ABCD')," + |
| "('ABCDE'),('AABCD'),('ABAB CD'),('ABC$XYZ'), ('')," + |
| "('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like 'A%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("A") |
| .baselineValues("AB") |
| .baselineValues("ABC") |
| .baselineValues("ABD") |
| .baselineValues("ABCD") |
| .baselineValues("ABCDE") |
| .baselineValues("AABCD") |
| .baselineValues("ABAB CD") |
| .baselineValues("ABC$XYZ") |
| .build() |
| .run(); |
| |
| // no match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('ABCD'),('ABCDE')," + |
| "('AABCD'), ('ABABCD'),('ABC$XYZ'), (''),('abcd')," + |
| "('x'), ('xyz'), ('%')) tbl(id)" + |
| " where id like 'z%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| // patternLength > txtLength |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('ABCD'),('ABCDE'),('AABCD')," + |
| "('ABABCD'),('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like 'ABCDEXYZRST%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| // non ASCII |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~')," + |
| " ('xyz'), ('%')) tbl(id)" + |
| " where id like '¤%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), " + |
| "('xyz'), ('%')) tbl(id)" + |
| " where id like 'ABC¤%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), ('xyz'), ('%')) tbl(id)" + |
| " where id like 'A%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC") |
| .baselineValues("ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~") |
| .build() |
| .run(); |
| |
| // no match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), ('xyz'), ('%')) tbl(id) " + |
| "where id like 'Z%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testLikeEndsWith() throws Exception { |
| |
| // all ASCII. End with multiple characters |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'), ('ABD'), ('ABCD'),('ABCDE')," + |
| "('AABCD'),('ABABCD'),('ABC$XYZ'), (''),('abcd'), " + |
| "('x'), ('xyz'), ('%')) tbl(id) " + |
| "where id like '%BCD'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABCD") |
| .baselineValues("AABCD") |
| .baselineValues("ABABCD") |
| .build() |
| .run(); |
| |
| // all ASCII. End with single character. |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'), ('ABD'), ('ABCD'),('ABCDE')," + |
| "('AABCD'),('ABABCD'),('ABC$XYZ'), (''),('abcd'), " + |
| "('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%D'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABD") |
| .baselineValues("ABCD") |
| .baselineValues("AABCD") |
| .baselineValues("ABABCD") |
| .build() |
| .run(); |
| |
| // all ASCII. End with nothing. Should match all. |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'), ('ABD'), ('ABCD'),('ABCDE')," + |
| "('AABCD'),('ABABCD'),('ABC$XYZ'), (''),('abcd'), " + |
| "('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("A") |
| .baselineValues("AB") |
| .baselineValues("ABC") |
| .baselineValues("ABD") |
| .baselineValues("ABCD") |
| .baselineValues("ABCDE") |
| .baselineValues("AABCD") |
| .baselineValues("ABABCD") |
| .baselineValues("ABC$XYZ") |
| .baselineValues("") |
| .baselineValues("abcd") |
| .baselineValues("x") |
| .baselineValues("xyz") |
| .baselineValues("%") |
| .build() |
| .run(); |
| |
| // no match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('ABCD'),('ABCDE'),('AABCD')," + |
| "('ABABCD'),('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%F'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| // patternLength > txtLength |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('ABCD'),('ABCDE'),('AABCD'),('ABABCD')," + |
| "('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id) " + |
| "where id like '%ABCDEXYZRST'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| // patternLength == txtLength |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('ABCD'),('ABCDE'),('AABCD'),('ABABCD')," + |
| "('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id) " + |
| "where id like '%ABC'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC") |
| .build() |
| .run(); |
| |
| // non ASCII. End with single character |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), ('')," + |
| "('¶TÆU2~~'), ('xyz'), ('%')) tbl(id) " + |
| "where id like '%~~'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~") |
| .baselineValues("¶TÆU2~~") |
| .build() |
| .run(); |
| |
| // non ASCII. End with multiple characters |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), " + |
| "(''), ('¶TÆU2~~'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%¶TÆU2~~'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~") |
| .baselineValues("¶TÆU2~~") |
| .build() |
| .run(); |
| |
| // non ASCII, no match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), ('')," + |
| "('xyz'), ('%')) tbl(id)" + |
| "where id like '%E'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testLikeContains() throws Exception { |
| |
| // all ASCII. match at the beginning, middle and end. |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'), ('ABD'), ('ABCD'),('DEABC')," + |
| "('AABCD'), ('ABABCDEF'),('AABC$XYZ'), (''),('abcd'), ('x'), " + |
| "('xyz'), ('%')) tbl(id) " + |
| "where id like '%ABC%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC") |
| .baselineValues("ABCD") |
| .baselineValues("DEABC") |
| .baselineValues("AABCD") |
| .baselineValues("ABABCDEF") |
| .baselineValues("AABC$XYZ") |
| .build() |
| .run(); |
| |
| // all ASCII. match at the beginning, middle and end, single character. |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'), ('ABD'), ('ABCD'),('ABCDE')," + |
| "('AABCD'),('ABABCD'),('CAB$XYZ'), (''),('abcd'), ('x'), " + |
| "('xyz'), ('%')) tbl(id)" + |
| "where id like '%C%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC") |
| .baselineValues("ABCD") |
| .baselineValues("ABCDE") |
| .baselineValues("AABCD") |
| .baselineValues("ABABCD") |
| .baselineValues("CAB$XYZ") |
| .build() |
| .run(); |
| |
| // no match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'), ('ABD'), ('ABCD'),('ABCDE')," + |
| "('AABCD'),('ABABCD'),('CAB$XYZ'), (''),('abcd'), ('x')," + |
| "('xyz'), ('%')) tbl(id)" + |
| "where id like '%FGH%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| // patternLength > txtLength |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('ABCD'),('ABCDE'),('AABCD'),('ABABCD')," + |
| "('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%ABCDEXYZRST%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| // all match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('ABCD'),('ABCDE'),('AABCD'),('ABABCD')," + |
| "('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("A") |
| .baselineValues("AB") |
| .baselineValues("ABC") |
| .baselineValues("ABCD") |
| .baselineValues("ABCDE") |
| .baselineValues("AABCD") |
| .baselineValues("ABABCD") |
| .baselineValues("ABC$XYZ") |
| .baselineValues("") |
| .baselineValues("abcd") |
| .baselineValues("x") |
| .baselineValues("xyz") |
| .baselineValues("%") |
| .build() |
| .run(); |
| |
| // non ASCII |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), " + |
| "(''), ('¶TÆU2~~'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%ÆU2%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~") |
| .baselineValues("¶TÆU2~~") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), " + |
| "(''), ('¶TÆU2~~'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%EÀsÆW%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~") |
| .build() |
| .run(); |
| |
| // no match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), ('')," + |
| "('xyz'), ('%')) tbl(id) where id like '%¶T¶T%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testLikeConstant() throws Exception { |
| |
| // all ASCII |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'), ('ABD'), " + |
| "('ABCD'),('ABCDE'),('AABCD'),('ABABCD'),('ABC$XYZ'), ('')," + |
| "('abcd'), ('x'), ('xyz'), ('%')) tbl(id) " + |
| "where id like 'ABC'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC") |
| .build() |
| .run(); |
| |
| |
| // Multiple same values |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'), ('ABC')," + |
| "('ABD'), ('ABCD'),('ABCDE'),('AABCD'),('ABABCD'),('ABC$XYZ')," + |
| "(''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like 'ABC'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC") |
| .baselineValues("ABC") |
| .build() |
| .run(); |
| |
| // match empty string |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'), ('ABD'), ('ABCD'),('ABCDE')," + |
| "('AABCD'),('ABABCD'),('ABC$XYZ'), (''),('abcd'), ('x')," + |
| " ('xyz'), ('%')) tbl(id)" + |
| "where id like ''") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("") |
| .build() |
| .run(); |
| |
| // no match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('ABCD'),('ABCDE'),('AABCD')," + |
| "('ABABCD'),('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz')," + |
| "('%')) tbl(id) where id like 'EFGH'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| // patternLength > txtLength |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('ABCD'),('ABCDE'),('AABCD'),('ABABCD')," + |
| "('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| " where id like 'ABCDEXYZRST'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| |
| // non ASCII |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), (''), " + |
| "('¶TÆU2~~'), ('xyz'), ('%')) tbl(id)" + |
| " where id like '¶TÆU2~~'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("¶TÆU2~~") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), ('')," + |
| "('¶TÆU2~~'), ('xyz'), ('%')) tbl(id)" + |
| "where id like 'ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~") |
| .build() |
| .run(); |
| |
| // no match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('ABC¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), (''), " + |
| "('xyz'), ('%')) tbl(id) where id like '¶T¶T'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testLikeWithEscapeStartsWith() throws Exception { |
| |
| // all ASCII |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC%'), ('ABD'), ('ABCD'),('ABCDE')," + |
| "('AABCD'),('ABABCD'),('ABC$XYZ'), (''),('abcd'), ('x')," + |
| "('xyz'), ('%')) tbl(id) " + |
| "where id like 'ABC#%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC%") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('A%B'),('A%B%C%'), ('ABD'), ('ABCD')," + |
| "('ABCDE'),('AABCD'),('A%BABCD'),('ABC$XYZ'), ('')," + |
| "('abcd'), ('x'), ('xyz'), ('%')) tbl (id)" + |
| "where id like 'A#%B%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("A%B") |
| .baselineValues("A%B%C%") |
| .baselineValues("A%BABCD") |
| .build() |
| .run(); |
| |
| // Multiple escape characters |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A_'),('AB'),('ABC'), ('ABD'), ('A_BC%D_')," + |
| "('ABCDE'), ('A_BC%D_XYZ'),('ABABCD'),('A_BC%D_$%XYZ')," + |
| " (''),('abcd'), ('x'), ('%')) tbl(id)" + |
| "where id like 'A#_BC#%D#_%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("A_BC%D_") |
| .baselineValues("A_BC%D_XYZ") |
| .baselineValues("A_BC%D_$%XYZ") |
| .build() |
| .run(); |
| |
| // Escape character followed by escape character |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A_'),('AB'),('ABC'), ('ABD'), ('ABC%D_')," + |
| "('A#BC%D_E'),('A_BC%D_XYZ'),('ABABCD'),('A#BC%D_$%XYZ')," + |
| " (''),('abcd'), ('x'), ('%')) tbl(id)" + |
| "where id like 'A##BC#%D#_%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("A#BC%D_E") |
| .baselineValues("A#BC%D_$%XYZ") |
| .build() |
| .run(); |
| |
| // no match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('ABCD'),('ABCDE'),('AABCD')," + |
| "('ABABCD'),('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| " where id like 'z#%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| // patternLength > txtLength |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('ABCD'),('ABCDE'),('AABCD')," + |
| "('ABABCD'),('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| " where id like 'ABCDEXYZRST#_%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| // non ASCII |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('¤E_ÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~')," + |
| " ('xyz'), ('%')) tbl(id)" + |
| " where id like '¤E#_%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("¤E_ÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('ABC¤EÀ%sÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), " + |
| "('xyz'), ('%')) tbl(id)" + |
| " where id like 'ABC¤EÀ#%sÆW%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC¤EÀ%sÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('AB%C'), " + |
| "('AB%C¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~'), ('xyz'), ('%')) tbl(id)" + |
| " where id like 'AB#%C%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("AB%C") |
| .baselineValues("AB%C¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~") |
| .build() |
| .run(); |
| |
| // no match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~')," + |
| " ('xyz'), ('%')) tbl(id)" + |
| "where id like 'Z$%%' escape '$'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testLikeWithEscapeEndsWith() throws Exception { |
| |
| // all ASCII |
| testBuilder().sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('AB%C'),('ABCDE'),('AABCD')," + |
| "('ABAB%C'),('ABC$XYZAB%C'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id) " + |
| "where id like '%AB$%C' escape '$'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("AB%C") |
| .baselineValues("ABAB%C") |
| .baselineValues("ABC$XYZAB%C") |
| .build() |
| .run(); |
| |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB_'),('AB%C%AB_'), ('ABD'), ('ABCD')," + |
| "('ABCDE'), ('AABCD'),('AB%ABCD'),('ABC$XYZAB_'), ('')," + |
| "('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%AB#_' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("AB_") |
| .baselineValues("AB%C%AB_") |
| .baselineValues("ABC$XYZAB_") |
| .build() |
| .run(); |
| |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A_'),('AB'),('ABC'), ('ABD'), ('A_BCD'),('ABCDEA_')," + |
| "('A_ABCD'),('ABABCDA_'),('A_BC$XYZA_'), (''),('abcd')," + |
| " ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%A#_' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues(("A_")) |
| .baselineValues("ABCDEA_") |
| .baselineValues("ABABCDA_") |
| .baselineValues("A_BC$XYZA_") |
| .build() |
| .run(); |
| |
| // Multiple escape characters |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A_'),('AB'),('ABC'), ('ABD'), ('A_BC%D_'),('ABCDE')," + |
| "('XYZA_BC%D_'),('ABABCD'),('$%XYZA_BC%D_'), ('')," + |
| "('abcd'), ('x'), ('%')) tbl(id)" + |
| " where id like '%A#_BC#%D#_' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("A_BC%D_") |
| .baselineValues("XYZA_BC%D_") |
| .baselineValues("$%XYZA_BC%D_") |
| .build() |
| .run(); |
| |
| |
| // Escape character followed by escape character |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A_'),('AB'),('ABC'), ('ABD'), ('A#BC%D_'),('A#BC%D_E')," + |
| "('A_BC%D_XYZ'),('ABABCD'),('$%XYZA#BC%D_'), ('')," + |
| "('abcd'), ('x'), ('%')) tbl(id)" + |
| " where id like '%A##BC#%D#_' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("A#BC%D_") |
| .baselineValues("$%XYZA#BC%D_") |
| .build() |
| .run(); |
| |
| // non ASCII |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('¤E_ÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2_~~')," + |
| " ('xyz'), ('%')) tbl(id)" + |
| " where id like '%2#_~~' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("¤E_ÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2_~~") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('ABC¤EÀ%sÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~ABC¤EÀ%sÆW'), " + |
| "('xyz'), ('%')) tbl(id)" + |
| " where id like '%ABC¤EÀ#%sÆW' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC¤EÀ%sÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~ABC¤EÀ%sÆW") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('AB%C'), " + |
| "('AB%C¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~AB%C'), ('xyz'), ('%')) tbl(id)" + |
| " where id like '%AB#%C' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("AB%C") |
| .baselineValues("AB%C¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~AB%C") |
| .build() |
| .run(); |
| |
| // no match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('ABC'), ('¤EÀsÆW°ê»Ú®i¶T¤¤¤ß3¼Ó®i¶TÆU2~~')," + |
| " ('xyz'), ('%')) tbl(id)" + |
| "where id like '%$%' escape '$'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("%") |
| .build() |
| .run(); |
| |
| } |
| |
| @Test |
| public void testLikeWithEscapeContains() throws Exception { |
| |
| // test EndsWith |
| testBuilder().sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC'),('AB%C'),('ABCDE'),('AB%AB%CDED')," + |
| "('ABAB%CDE'),('ABC$XYZAB%C'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%AB$%C%' escape '$'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABAB%CDE") |
| .baselineValues("AB%AB%CDED") |
| .baselineValues("AB%C") |
| .baselineValues("ABC$XYZAB%C") |
| .build() |
| .run(); |
| |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB_'),('%AB%C%AB_'), ('%AB%D'), ('ABCD')," + |
| "('AB%AC%AB%DE'), ('AABCD'),('AB%AB%CD'),('ABC$XYZAB_')," + |
| "(''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%#%AB#%%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("%AB%C%AB_") |
| .baselineValues("%AB%D") |
| .baselineValues("AB%AB%CD") |
| .baselineValues("AB%AC%AB%DE") |
| .build() |
| .run(); |
| |
| // no match |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB_'),('%AB%C%AB_'), ('%AB%D'), ('ABCD')," + |
| "('AB%AC%AB%DE'), ('AABCD'),('AB%AB%CD'),('ABC$XYZAB_')," + |
| "(''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%#%A#_B#%%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A_'),('AB'),('ABC'), ('ABD'), ('A_BCD'),('ABA_CDEA_')," + |
| "('A_ABCD'),('ABABCDA_'),('A_BC$XYZA_'), ('')," + |
| "('abcd'), ('x'), ('xyz'), ('%')) tbl(id)" + |
| "where id like '%A#_%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues(("A_")) |
| .baselineValues("A_BCD") |
| .baselineValues("ABA_CDEA_") |
| .baselineValues("A_ABCD") |
| .baselineValues("A_BC$XYZA_") |
| .baselineValues("ABABCDA_") |
| .build() |
| .run(); |
| |
| |
| // Multiple escape characters |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A_'),('AB'),('ABC'), ('ABD'), ('A_BC%D_'),('ABCDE')," + |
| "('XYZA_BC%D_'),('ABABCD'),('$%XYZA_BC%D_'), ('')," + |
| "('abcd'), ('x'), ('%')) tbl" + |
| "(id) where id like '%A#_BC#%D#_' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("A_BC%D_") |
| .baselineValues("XYZA_BC%D_") |
| .baselineValues("$%XYZA_BC%D_") |
| .build() |
| .run(); |
| |
| |
| // Escape character followed by escape character |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A_'),('AB'),('ABC'), ('ABDA#BC%D_'), ('A#BC%D_')," + |
| "('A#BC%BC%D_E'),('A_BC%D_XYZ'),('ABABCD'),('$%XYZA#BC%D_')," + |
| " (''),('abcd'), ('x'), ('%')) tbl(id)" + |
| " where id like '%A##BC#%D#_%' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("A#BC%D_") |
| .baselineValues("$%XYZA#BC%D_") |
| .baselineValues("ABDA#BC%D_") |
| .build() |
| .run(); |
| |
| } |
| |
| @Test |
| public void testLikeWithEscapeConstant() throws Exception { |
| |
| // test startsWith |
| testBuilder().sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC%'),('ABCD'),('ABCDE'),('AABCD')," + |
| "('ABABCD'),('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id) " + |
| "where id like 'ABC%%' escape '%' ") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("ABC%") |
| .build() |
| .run(); |
| |
| testBuilder().sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('%ABC'),('ABCD'),('ABCDE'),('AABCD')," + |
| "('ABABCD'),('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id) " + |
| "where id like '%%ABC' escape '%' ") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("%ABC") |
| .build() |
| .run(); |
| |
| testBuilder().sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('AB%C'),('ABCD'),('ABCDE'),('AABCD'),('ABABCD')," + |
| "('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id) " + |
| "where id like 'AB%%C' escape '%' ") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("AB%C") |
| .build() |
| .run(); |
| |
| // Multiple escape characters |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A_'),('AB'),('ABC'), ('ABD'), ('%_BC%D_'),('ABCDE')," + |
| "('XYZA_BC%D_'),('ABABCD'),('$%XYZA_BC%D_'), (''),('abcd'), ('x'), ('%')) tbl(id)" + |
| " where id like '%%_BC%%D%_' escape '%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("%_BC%D_") |
| .build() |
| .run(); |
| |
| // Escape character followed by escape character |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('A_'),('AB'),('ABC'), ('ABDA#BC%D_'), ('A#BC%D_'),('A#BA#BC%D_E')," + |
| "('A_BC%D_XYZ'),('ABABCD'),('$%XYZA#BC%D_'), (''),('abcd'), ('x'), ('%')) tbl(id)" + |
| " where id like 'A##BC#%D#_' escape '#'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("A#BC%D_") |
| .build() |
| .run(); |
| |
| // no match |
| testBuilder().sqlQuery(" SELECT id FROM (" + |
| "VALUES('A'),('AB'),('ABC%'),('ABCD'),('ABCDE'),('AABCD')," + |
| "('ABABCD'),('ABC$XYZ'), (''),('abcd'), ('x'), ('xyz'), ('%')) tbl(id) " + |
| "where id like '%_ABC%%' escape '%' ") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| } |
| |
| @Test |
| public void testLikeRandom() throws Exception { |
| |
| // test Random queries with like |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('aeiou'),('abcdef'),('afdrgt'),('abcdt'),('aaaa'),('a'),('aeiou'),(''),('a aa')) tbl(id)" + |
| "where id not like 'a %'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("aeiou") |
| .baselineValues("abcdef") |
| .baselineValues("afdrgt") |
| .baselineValues("abcdt") |
| .baselineValues("aaaa") |
| .baselineValues("a") |
| .baselineValues("aeiou") |
| .baselineValues("") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('aeiou'),('abcdefizu'),('afdrgt'),('abcdt'),('aaaa'),('a'),('aeiou'),(''),('a aa')) tbl(id)" + |
| "where id like 'a%i_u'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("aeiou") |
| .baselineValues("aeiou") |
| .baselineValues("abcdefizu") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('xyzaeioughbcd'),('abcdefizu'),('afdrgt'),('abcdt'),('aaaa'),('a'),('aeiou'),(''),('a aa')) tbl(id)" + |
| "where id like '%a_i_u%bcd%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("xyzaeioughbcd") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like 'ab'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like '%ab'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like 'ab%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("abc") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like '%ab%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("abc") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like 'abc'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("abc") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like 'abc%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("abc") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like '%abc'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("abc") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like '%abc%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("abc") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like 'abcd'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like 'abcd%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like '%abcd'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like '%abcd%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like ''") |
| .unOrdered() |
| .baselineColumns("id") |
| .expectsEmptyResultSet() |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like '%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("abc") |
| .build() |
| .run(); |
| |
| testBuilder() |
| .sqlQuery(" SELECT id FROM (" + |
| "VALUES('abc')) tbl(id)" + |
| "where id like '%%'") |
| .unOrdered() |
| .baselineColumns("id") |
| .baselineValues("abc") |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testILike() throws Exception { |
| testBuilder() |
| .sqlQuery("select n_name from cp.`tpch/nation.parquet` where `ilike`(n_name, '%united%') = true") |
| .unOrdered() |
| .baselineColumns("n_name") |
| .baselineValues("UNITED STATES") |
| .baselineValues("UNITED KINGDOM") |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testILikeEscape() throws Exception { |
| testBuilder() |
| .sqlQuery("select a from (select concat(r_name , '_region') a from cp.`tpch/region.parquet`) where `ilike`(a, 'asia#_region', '#') = true") |
| .unOrdered() |
| .baselineColumns("a") |
| .baselineValues("ASIA_region") |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testSubstr() throws Exception { |
| testBuilder() |
| .sqlQuery("select substr(n_name, 'UN.TE.') a from cp.`tpch/nation.parquet` where `ilike`(n_name, 'united%') = true") |
| .unOrdered() |
| .baselineColumns("a") |
| .baselineValues("UNITED") |
| .baselineValues("UNITED") |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testLpadTwoArgConvergeToLpad() throws Exception { |
| final String query_1 = "SELECT lpad(r_name, 25) \n" + |
| "FROM cp.`tpch/region.parquet`"; |
| |
| |
| final String query_2 = "SELECT lpad(r_name, 25, ' ') \n" + |
| "FROM cp.`tpch/region.parquet`"; |
| |
| testBuilder() |
| .sqlQuery(query_1) |
| .unOrdered() |
| .sqlBaselineQuery(query_2) |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testRpadTwoArgConvergeToRpad() throws Exception { |
| final String query_1 = "SELECT rpad(r_name, 25) \n" + |
| "FROM cp.`tpch/region.parquet`"; |
| |
| |
| final String query_2 = "SELECT rpad(r_name, 25, ' ') \n" + |
| "FROM cp.`tpch/region.parquet`"; |
| |
| testBuilder() |
| .sqlQuery(query_1) |
| .unOrdered() |
| .sqlBaselineQuery(query_2) |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testLtrimOneArgConvergeToLtrim() throws Exception { |
| final String query_1 = "SELECT ltrim(concat(' ', r_name, ' ')) \n" + |
| "FROM cp.`tpch/region.parquet`"; |
| |
| |
| final String query_2 = "SELECT ltrim(concat(' ', r_name, ' '), ' ') \n" + |
| "FROM cp.`tpch/region.parquet`"; |
| |
| testBuilder() |
| .sqlQuery(query_1) |
| .unOrdered() |
| .sqlBaselineQuery(query_2) |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testRtrimOneArgConvergeToRtrim() throws Exception { |
| final String query_1 = "SELECT rtrim(concat(' ', r_name, ' ')) \n" + |
| "FROM cp.`tpch/region.parquet`"; |
| |
| |
| final String query_2 = "SELECT rtrim(concat(' ', r_name, ' '), ' ') \n" + |
| "FROM cp.`tpch/region.parquet`"; |
| |
| testBuilder() |
| .sqlQuery(query_1) |
| .unOrdered() |
| .sqlBaselineQuery(query_2) |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testBtrimOneArgConvergeToBtrim() throws Exception { |
| final String query_1 = "SELECT btrim(concat(' ', r_name, ' ')) \n" + |
| "FROM cp.`tpch/region.parquet`"; |
| |
| |
| final String query_2 = "SELECT btrim(concat(' ', r_name, ' '), ' ') \n" + |
| "FROM cp.`tpch/region.parquet`"; |
| |
| testBuilder() |
| .sqlQuery(query_1) |
| .unOrdered() |
| .sqlBaselineQuery(query_2) |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testSplit() throws Exception { |
| testBuilder() |
| .sqlQuery("select split(n_name, ' ') words from cp.`tpch/nation.parquet` where n_nationkey = 24") |
| .unOrdered() |
| .baselineColumns("words") |
| .baselineValues(ImmutableList.of(new Text("UNITED"), new Text("STATES"))) |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testSplitWithNullInput() throws Exception { |
| // Contents of the generated file: |
| /* |
| {"a": "aaaaaa.bbb.cc.ddddd"} |
| {"a": null} |
| {"a": "aa"} |
| */ |
| try (BufferedWriter writer = new BufferedWriter(new FileWriter(new File(dirTestWatcher.getRootDir(), "nullable_strings.json")))) { |
| String[] fieldValue = {"\"aaaaaa.bbb.cc.ddddd\"", null, "\"aa\""}; |
| for (String value : fieldValue) { |
| String entry = String.format("{ \"a\": %s}\n", value); |
| writer.write(entry); |
| } |
| } |
| |
| testBuilder() |
| .sqlQuery("select split(a, '.') wordsCount from dfs.`nullable_strings.json` t") |
| .unOrdered() |
| .baselineColumns("wordsCount") |
| .baselineValues(ImmutableList.of(new Text("aaaaaa"), new Text("bbb"), new Text("cc"), new Text("ddddd"))) |
| .baselineValues(ImmutableList.of()) |
| .baselineValues(ImmutableList.of(new Text("aa"))) |
| .go(); |
| } |
| |
| @Test |
| public void testReverse() throws Exception { |
| testBuilder() |
| .sqlQuery("select reverse('qwerty') words from (values(1))") |
| .unOrdered() |
| .baselineColumns("words") |
| .baselineValues("ytrewq") |
| .build() |
| .run(); |
| } |
| |
| @Test // DRILL-5424 |
| public void testReverseLongVarChars() throws Exception { |
| try (BufferedWriter writer = new BufferedWriter(new FileWriter(new File(dirTestWatcher.getRootDir(), "table_with_long_varchars.json")))) { |
| for (int i = 0; i < 10; i++) { |
| writer.write("{ \"a\": \"abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz\"}"); |
| } |
| } |
| |
| test("select reverse(a) from dfs.`table_with_long_varchars.json` t"); |
| } |
| |
| @Test |
| public void testLower() throws Exception { |
| testBuilder() |
| .sqlQuery("select\n" + |
| "lower('ABC') col_upper,\n" + |
| "lower('abc') col_lower,\n" + |
| "lower('AbC aBc') col_space,\n" + |
| "lower('123ABC$!abc123.') as col_special,\n" + |
| "lower('') as col_empty,\n" + |
| "lower(cast(null as varchar(10))) as col_null\n" + |
| "from (values(1))") |
| .unOrdered() |
| .baselineColumns("col_upper", "col_lower", "col_space", "col_special", "col_empty", "col_null") |
| .baselineValues("abc", "abc", "abc abc", "123abc$!abc123.", "", null) |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testUpper() throws Exception { |
| testBuilder() |
| .sqlQuery("select\n" + |
| "upper('ABC')as col_upper,\n" + |
| "upper('abc') as col_lower,\n" + |
| "upper('AbC aBc') as col_space,\n" + |
| "upper('123ABC$!abc123.') as col_special,\n" + |
| "upper('') as col_empty,\n" + |
| "upper(cast(null as varchar(10))) as col_null\n" + |
| "from (values(1))") |
| .unOrdered() |
| .baselineColumns("col_upper", "col_lower", "col_space", "col_special", "col_empty", "col_null") |
| .baselineValues("ABC", "ABC", "ABC ABC", "123ABC$!ABC123.", "", null) |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testInitcap() throws Exception { |
| testBuilder() |
| .sqlQuery("select\n" + |
| "initcap('ABC')as col_upper,\n" + |
| "initcap('abc') as col_lower,\n" + |
| "initcap('AbC aBc') as col_space,\n" + |
| "initcap('123ABC$!abc123.') as col_special,\n" + |
| "initcap('') as col_empty,\n" + |
| "initcap(cast(null as varchar(10))) as col_null\n" + |
| "from (values(1))") |
| .unOrdered() |
| .baselineColumns("col_upper", "col_lower", "col_space", "col_special", "col_empty", "col_null") |
| .baselineValues("Abc", "Abc", "Abc Abc", "123abc$!Abc123.", "", null) |
| .build() |
| .run(); |
| } |
| |
| @Test |
| public void testMultiByteEncoding() throws Exception { |
| testBuilder() |
| .sqlQuery("select\n" + |
| "upper('привет')as col_upper,\n" + |
| "lower('ПРИВЕТ') as col_lower,\n" + |
| "initcap('приВЕТ') as col_initcap\n" + |
| "from (values(1))") |
| .unOrdered() |
| .baselineColumns("col_upper", "col_lower", "col_initcap") |
| .baselineValues("ПРИВЕТ", "привет", "Привет") |
| .build() |
| .run(); |
| } |
| } |
