Sorry, maybe I didn't describe it very well, which caused you to not understand what I meant. Here, I gives some query results to describe this problem. [image: image.png]
[image: image.png] [image: image.png] Calcite does not support split function without array subscript operator, I wonder should we support this kind of usage of this function? The BigQuery doc: https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator On Wed, Aug 9, 2023 at 12:43 AM <[email protected]> wrote: > I don' t understand from this message what the problem with split is. > > Mihai > > -----Original Message----- > From: P.F. ZHAN > Sent: Tuesday, August 08, 2023 8:21 AM > To: [email protected] > Subject: Re: [Discussion] Split function has some wrong and strange > behaviors > > I rechecked Calcite's implementation of the split function today again, > and I found that this behavior appears to be a by design behavior. > > > > > > org.apache.calcite.rel.rel2sql.RelToSqlConverterTest#testIndexOperator > > sBigQuery > > > org.apache.calcite.rel.rel2sql.RelToSqlConverterTest#testIndexWithoutOperatorBigQuery > > > I also checked the BigQuery doc which only mentions these standard usages > in our test case of *testIndexOperatorsBigQuery*, but in fact when we use > SQL like > > > select split('aaa-bbb-ccc', '-')[0] > > the results are available in BigQuery and the behavior is the same as > > > select split('aaa-bbb-ccc', '-')[ offset(0)] > > Maybe Calcite should support this kind of usage rather than treat it as a > limitation. > > > > On Tue, Aug 8, 2023 at 10:31 AM P.F. ZHAN <[email protected]> wrote: > > > Thank you mbudiu, Ran Tao and Lake Shen. > > > > I have tried your suggestions and debugged the code, this kind of sql > > seems not support > > > >> select split('aaa-bbb-ccc', '-')[1] > >> > > > > I have checked it in both Bigquery and Spark, it works. This looks > > like a bug at present. Can I open a jira issue to follow this problem? > > > > > > On Tue, Aug 8, 2023 at 1:09 AM <[email protected]> wrote: > > > >> See also this thread: > >> https://issues.apache.org/jira/browse/CALCITE-5760 > >> > >> You can run CalciteSqlOperatorTest instead. > >> If you want to quickly debug only this test in the IDE you can > >> copy-paste this test into the CalciteSqlOperatorTest class and run it > >> from there, then delete it when you are done. > >> > >> Mihai > >> > >> -----Original Message----- > >> From: Ran Tao > >> Sent: Monday, August 07, 2023 4:04 AM > >> To: [email protected] > >> Subject: Re: [Discussion] Split function has some wrong and strange > >> behaviors > >> > >> > I found that when I use "*./gradlew build*" to build the project > >> > happens > >> with some errors, but I execute this case in IDEA alone and it is > >> successful. > >> > >> The default `fixture()` does not execute in unit tests (but works in > >> ./gradlew build), you should use > >> Fixture.forOperators(true) to execute in unit tests. You can check > >> this usage, hope it helps you. > >> > >> > >> Best Regards, > >> Ran Tao > >> https://github.com/chucheng92 > >> > >> > >> P.F. ZHAN <[email protected]> 于2023年8月7日周一 15:22写道： > >> > >> > Sorry, I made a mistake, used Chinese to describe my question. > >> > > >> > Hi, When I was testing the split function with a case as follows: > >> > org.apache.calcite.test.SqlOperatorTest#testSplitFunction > >> > > >> > > f.checkScalar("SPLIT('h-e-l-l-o', '-')[0]", "h", "VARCHAR"); > >> > > > >> > > >> > I found that when I use "*./gradlew build*" to build the project > >> > happens with some errors, but I execute this case in IDEA alone and > >> > it is successful. > >> > > >> > FAILURE 0.3sec, org.apache.calcite.test.CalciteSqlOperatorTest > > >> > > testSplitFunction() > >> > > java.lang.AssertionError: Query: values (SPLIT('h-e-l-l-o', > >> '-')[0]) > >> > > Expected: is "h" > >> > > but: was null > >> > > at > >> org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:18) > >> > > at > >> > > > >> > org.apache.calcite.sql.test.ResultCheckers.compareResultSetWithMatc > >> > her > >> > (ResultCheckers.java:252) > >> > > at > >> > > > >> > org.apache.calcite.sql.test.ResultCheckers$MatcherResultChecker.che > >> > ckR > >> > esult(ResultCheckers.java:321) > >> > > at > >> > > > >> > org.apache.calcite.test.SqlOperatorTest$TesterImpl.check(SqlOperato > >> > rTe > >> > st.java:12150) > >> > > at > >> > org.apache.calcite.sql.test.SqlTester.check(SqlTester.java:160) > >> > > at > >> > > > >> > org.apache.calcite.test.SqlOperatorFixtureImpl.lambda$checkScalar$2 > >> > (Sq > >> > lOperatorFixtureImpl.java:224) > >> > > at > >> > > > >> > org.apache.calcite.sql.test.AbstractSqlTester.forEachQuery(Abstract > >> > Sql > >> > Tester.java:446) > >> > > at > >> > > > >> > org.apache.calcite.test.SqlOperatorFixtureImpl.checkScalar(SqlOpera > >> > tor > >> > FixtureImpl.java:223) > >> > > at > >> > > > >> > org.apache.calcite.sql.test.SqlOperatorFixture.checkScalar(SqlOpera > >> > tor > >> > Fixture.java:238) > >> > > at > >> > > > >> > org.apache.calcite.test.SqlOperatorTest.testSplitFunction(SqlOperat > >> > orT > >> > est.java:7657) > >> > > at > >> > > > >> > org.junit.platform.commons.util.ReflectionUtils.invokeMethod(Reflec > >> > tio > >> > nUtils.java:727) > >> > > > >> > > >> > I also add a case in the file big-query.iq > >> > > >> > SELECT SPLIT("h,e,l,l,o", ',')[1] as result; > >> > > +------- + > >> > > | result | > >> > > +------- + > >> > > | e | > >> > > +------- + > >> > > (1 row) > >> > > > >> > > !ok > >> > > > >> > > >> > The error log is as follows: > >> > > >> > > FAILURE 27.6sec, org.apache.calcite.test.BabelQuidemTest > > >> > > test(String)[3], [3] sql/big-query.iq > >> > > org.opentest4j.AssertionFailedError: Files differ: > >> > > ~/calcite/babel/build/quidem/test/sql/big-query.iq > >> > > ~/calcite/babel/build/resources/test/sql/big-query.iq > >> > > 759c759 > >> > > < +------- + > >> > > --- > >> > > > +--------+ > >> > > 761,763c761,763 > >> > > < +------- + > >> > > < | e | > >> > > < +------- + > >> > > --- > >> > > > +--------+ > >> > > > | h | > >> > > > +--------+ > >> > > > >> > > at > >> > > org.junit.jupiter.api.AssertionUtils.fail(AssertionUtils.java:38) > >> > > at > org.junit.jupiter.api.Assertions.fail(Assertions.java:135) > >> > > at > >> > org.apache.calcite.test.QuidemTest.checkRun(QuidemTest.java:183) > >> > > at > >> > > org.apache.calcite.test.QuidemTest.test(QuidemTest.java:236) > >> > > > >> > > > >> > On Mon, Aug 7, 2023 at 3:13 PM P.F. ZHAN <[email protected]> wrote: > >> > > >> > > 大家好。我在测试 split 函数功能的时候，添加了个 case > >> > > org.apache.calcite.test.SqlOperatorTest#testSplitFunction > >> > > > >> > >> f.checkScalar("SPLIT('h-e-l-l-o', '-')[0]", "h", "VARCHAR"); > >> > >> > >> > > > >> > > 我发现我使用 gradlew build 整个项目时会出错，但我单独在 IDEA 里面执行这个 case 是成功的。 > >> > > > >> > >> FAILURE 0.3sec, org.apache.calcite.test.CalciteSqlOperatorTest > > >> > >> testSplitFunction() > >> > >> java.lang.AssertionError: Query: values (SPLIT('h-e-l-l-o', > >> '-')[0]) > >> > >> Expected: is "h" > >> > >> but: was null > >> > >> at > >> org.hamcrest.MatcherAssert.assertThat(MatcherAssert.java:18) > >> > >> at > >> > >> > >> > org.apache.calcite.sql.test.ResultCheckers.compareResultSetWithMatc > >> > her > >> > (ResultCheckers.java:252) > >> > >> at > >> > >> > >> > org.apache.calcite.sql.test.ResultCheckers$MatcherResultChecker.che > >> > ckR > >> > esult(ResultCheckers.java:321) > >> > >> at > >> > >> > >> > org.apache.calcite.test.SqlOperatorTest$TesterImpl.check(SqlOperato > >> > rTe > >> > st.java:12150) > >> > >> at > >> > org.apache.calcite.sql.test.SqlTester.check(SqlTester.java:160) > >> > >> at > >> > >> > >> > org.apache.calcite.test.SqlOperatorFixtureImpl.lambda$checkScalar$2 > >> > (Sq > >> > lOperatorFixtureImpl.java:224) > >> > >> at > >> > >> > >> > org.apache.calcite.sql.test.AbstractSqlTester.forEachQuery(Abstract > >> > Sql > >> > Tester.java:446) > >> > >> at > >> > >> > >> > org.apache.calcite.test.SqlOperatorFixtureImpl.checkScalar(SqlOpera > >> > tor > >> > FixtureImpl.java:223) > >> > >> at > >> > >> > >> > org.apache.calcite.sql.test.SqlOperatorFixture.checkScalar(SqlOpera > >> > tor > >> > Fixture.java:238) > >> > >> at > >> > >> > >> > org.apache.calcite.test.SqlOperatorTest.testSplitFunction(SqlOperat > >> > orT > >> > est.java:7657) > >> > >> at > >> > >> > >> > org.junit.platform.commons.util.ReflectionUtils.invokeMethod(Reflec > >> > tio > >> > nUtils.java:727) > >> > >> > >> > > > >> > > 我在big-query.iq 中加入下面这段 > >> > > > >> > >> SELECT SPLIT("h,e,l,l,o", ',')[1] as result; > >> > >> +------- + > >> > >> | result | > >> > >> +------- + > >> > >> | e | > >> > >> +------- + > >> > >> (1 row) > >> > >> > >> > >> !ok > >> > >> > >> > > > >> > > 报错如下： > >> > > > >> > >> FAILURE 27.6sec, org.apache.calcite.test.BabelQuidemTest > > >> > >> test(String)[3], [3] sql/big-query.iq > >> > >> org.opentest4j.AssertionFailedError: Files differ: > >> > >> ~/calcite/babel/build/quidem/test/sql/big-query.iq > >> > >> ~/calcite/babel/build/resources/test/sql/big-query.iq > >> > >> 759c759 > >> > >> < +------- + > >> > >> --- > >> > >> > +--------+ > >> > >> 761,763c761,763 > >> > >> < +------- + > >> > >> < | e | > >> > >> < +------- + > >> > >> --- > >> > >> > +--------+ > >> > >> > | h | > >> > >> > +--------+ > >> > >> > >> > >> at > >> > >> org.junit.jupiter.api.AssertionUtils.fail(AssertionUtils.java:38) > >> > >> at > org.junit.jupiter.api.Assertions.fail(Assertions.java:135) > >> > >> at > >> > >> org.apache.calcite.test.QuidemTest.checkRun(QuidemTest.java:183) > >> > >> at > >> > >> org.apache.calcite.test.QuidemTest.test(QuidemTest.java:236) > >> > >> > >> > > > >> > > >> > >> > >
