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#testIndexOperatorsBigQuery
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.compareResultSetWithMatcher
>> > (ResultCheckers.java:252)
>> > > at
>> > >
>> > org.apache.calcite.sql.test.ResultCheckers$MatcherResultChecker.checkR
>> > esult(ResultCheckers.java:321)
>> > > at
>> > >
>> > org.apache.calcite.test.SqlOperatorTest$TesterImpl.check(SqlOperatorTe
>> > 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(AbstractSql
>> > Tester.java:446)
>> > > at
>> > >
>> > org.apache.calcite.test.SqlOperatorFixtureImpl.checkScalar(SqlOperator
>> > FixtureImpl.java:223)
>> > > at
>> > >
>> > org.apache.calcite.sql.test.SqlOperatorFixture.checkScalar(SqlOperator
>> > Fixture.java:238)
>> > > at
>> > >
>> > org.apache.calcite.test.SqlOperatorTest.testSplitFunction(SqlOperatorT
>> > est.java:7657)
>> > > at
>> > >
>> > org.junit.platform.commons.util.ReflectionUtils.invokeMethod(Reflectio
>> > 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.compareResultSetWithMatcher
>> > (ResultCheckers.java:252)
>> > >> at
>> > >>
>> > org.apache.calcite.sql.test.ResultCheckers$MatcherResultChecker.checkR
>> > esult(ResultCheckers.java:321)
>> > >> at
>> > >>
>> > org.apache.calcite.test.SqlOperatorTest$TesterImpl.check(SqlOperatorTe
>> > 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(AbstractSql
>> > Tester.java:446)
>> > >> at
>> > >>
>> > org.apache.calcite.test.SqlOperatorFixtureImpl.checkScalar(SqlOperator
>> > FixtureImpl.java:223)
>> > >> at
>> > >>
>> > org.apache.calcite.sql.test.SqlOperatorFixture.checkScalar(SqlOperator
>> > Fixture.java:238)
>> > >> at
>> > >>
>> > org.apache.calcite.test.SqlOperatorTest.testSplitFunction(SqlOperatorT
>> > est.java:7657)
>> > >> at
>> > >>
>> > org.junit.platform.commons.util.ReflectionUtils.invokeMethod(Reflectio
>> > 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)
>> > >>
>> > >
>> >
>>
>>
