The mailing list strips images from emails.
Mihai From: P.F. ZHAN Sent: Tuesday, August 08, 2023 10:23 AM To: [email protected] Subject: Re: [Discussion] Split function has some wrong and strange behaviors 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. 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] <mailto:[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] <mailto:[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] <mailto:[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] <mailto:[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] <mailto:[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] <mailto:[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 <http://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 <http://big-query.iq> >> > > org.opentest4j.AssertionFailedError: Files differ: >> > > ~/calcite/babel/build/quidem/test/sql/big-query.iq <http://big-query.iq> >> > > ~/calcite/babel/build/resources/test/sql/big-query.iq >> > > <http://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] >> > <mailto:[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 <http://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 <http://big-query.iq> >> > >> org.opentest4j.AssertionFailedError: Files differ: >> > >> ~/calcite/babel/build/quidem/test/sql/big-query.iq >> > >> <http://big-query.iq> >> > >> ~/calcite/babel/build/resources/test/sql/big-query.iq >> > >> <http://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) >> > >> >> > > >> > >> >>
