You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Dmitry Sysolyatin <dm...@gmail.com> on 2022/02/04 11:57:41 UTC
ARRAY_CONCAT does not work
Hi!
I have a problem with ARRAY_CONCAT operator.
The following code failed with the exception ''No match found for function
signature array_concat(<INTEGER ARRAY>, <INTEGER ARRAY>)".
```
val config = Frameworks.newConfigBuilder()
.parserConfig(parserConfig)
.defaultSchema(CalciteSchema.createRootSchema(false, false).plus())
.operatorTable(SqlOperatorTables.chain(
SqlStdOperatorTable.instance(),
SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
SqlLibrary.BIG_QUERY
)))
.programs(Programs.standard())
.build()
val planner = Frameworks.getPlanner(config)
val query = "SELECT ARRAY_CONCAT(ARRAY[1, 2], ARRAY[2, 3])"
val parsedSqlNode = planner.parse(query)
planner.validate(parsedSqlNode)
```
Am I doing something wrong or it is a bug?
Re: ARRAY_CONCAT does not work
Posted by Julian Hyde <jh...@gmail.com>.
Let’s continue conversation in https://issues.apache.org/jira/browse/CALCITE-4999 <https://issues.apache.org/jira/browse/CALCITE-4999>. I already pushed back there on the idea that ARRAY_CONCAT’s type derivation was wrong, and Dmitry conceded.
> On Feb 10, 2022, at 5:08 AM, Michael Mior <mm...@apache.org> wrote:
>
> Exactly. I understand the problem is not CHAR(1) vs CHAR(7), but the record
> type. That is the point I was trying to make.
> --
> Michael Mior
> mmior@apache.org
>
>
> Le jeu. 10 févr. 2022 à 07:47, Dmitry Sysolyatin <dm...@gmail.com>
> a écrit :
>
>> Michael, the problem is not because CHAR(1) and CHAR(7). Calcite can derive
>> common type in this case = CHAR(7) and all will work ok.
>>
>> The problem is that one type is [<scalar> ARRAY] and another [<RecordType>
>> ARRAY]. I see two options for resolving this problem:
>>
>> 1. Allow casting scalar type to RecordType with one field. I described it
>> inside https://issues.apache.org/jira/browse/CALCITE-4999
>> 2. Modify `ARRAY` function in the way that it will return ARRAY of scalar
>> if subquery is used as argument. Like Postgres does -
>>
>> https://www.postgresql.org/docs/14/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
>>
>> On Thu, Feb 10, 2022 at 2:02 PM Michael Mior <mm...@apache.org> wrote:
>>
>>> The two types in your example are incompatible. One is an array of
>> CHAR(1).
>>> The other is an array of records, each with a single CHAR(7) field.
>>> --
>>> Michael Mior
>>> mmior@apache.org
>>>
>>>
>>> Le ven. 4 févr. 2022 à 11:27, Dmitry Sysolyatin <dm.sysolyatin@gmail.com
>>>
>>> a
>>> écrit :
>>>
>>>> So, the previous case started to work. But I faced with another issue.
>>> This
>>>> query does not work:
>>>>
>>>> ```
>>>> "SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
>>>> unnest(ARRAY['1','2']) x))"
>>>> ```
>>>>
>>>> ```
>>>> java.lang.IllegalArgumentException: Cannot infer return type for
>>>> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
>>>> ARRAY]
>>>> ```
>>>>
>>>> I created an issue -
>> https://issues.apache.org/jira/browse/CALCITE-4999
>>>> and
>>>> PR - https://github.com/apache/calcite/pull/2712
>>>>
>>>>
>>>> On Fri, Feb 4, 2022 at 2:51 PM Dmitry Sysolyatin <
>>> dm.sysolyatin@gmail.com>
>>>> wrote:
>>>>
>>>>> Actually,
>>>>>
>>> `SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY)`
>>>>> helped, thanks !
>>>>> I will try to understand why it doesn't work with BABEL conformance
>>>>>
>>>>> On Fri, Feb 4, 2022 at 2:35 PM Dmitry Sysolyatin <
>>>> dm.sysolyatin@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> How SqlParser config can help? If I understood correctly, parser
>>> config
>>>>>> affects only parser.parse stage. But code fails at the validation
>>> phase
>>>>>>
>>>>>> Also, I would like to use ARRAY_CONCAT function with postgres
>> dialect.
>>>> My
>>>>>> parser config at the moment:
>>>>>>
>>>>>> ```
>>>>>> val parserConfig: SqlParser.Config =
>>>>>> PostgresqlSqlDialect.DEFAULT
>>>>>> .configureParser(SqlParser
>>>>>> .config()
>>>>>> // It is needed in order to parse PG "!~" operator
>>>>>> .withParserFactory(SqlBabelParserImpl.FACTORY)
>>>>>> )
>>>>>> .withConformance(SqlConformanceEnum.BABEL)
>>>>>> ```
>>>>>>
>>>>>> On Fri, Feb 4, 2022 at 2:26 PM Thomas Rebele
>>> <trebele@tibco.com.invalid
>>>>>
>>>>>> wrote:
>>>>>>
>>>>>>> Hello,
>>>>>>>
>>>>>>> SqlParserTest might help you. It has some checks related to
>>> BIG_QUERY.
>>>>>>> Maybe a
>>>>>>>
>>>>>>>
>>>>
>>>
>> .parserConfig(SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY))
>>>>>>> could fix the problem.
>>>>>>>
>>>>>>> Cordialement / Best Regards,
>>>>>>> *Thomas Rebele, PhD* | R&D Developer | Germany | www.tibco.com
>>>>>>>
>>>>>>>
>>>>>>> On Fri, Feb 4, 2022 at 12:57 PM Dmitry Sysolyatin <
>>>>>>> dm.sysolyatin@gmail.com>
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Hi!
>>>>>>>> I have a problem with ARRAY_CONCAT operator.
>>>>>>>>
>>>>>>>> The following code failed with the exception ''No match found for
>>>>>>> function
>>>>>>>> signature array_concat(<INTEGER ARRAY>, <INTEGER ARRAY>)".
>>>>>>>> ```
>>>>>>>> val config = Frameworks.newConfigBuilder()
>>>>>>>> .parserConfig(parserConfig)
>>>>>>>> .defaultSchema(CalciteSchema.createRootSchema(false,
>>>>>>> false).plus())
>>>>>>>> .operatorTable(SqlOperatorTables.chain(
>>>>>>>> SqlStdOperatorTable.instance(),
>>>>>>>>
>>>> SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
>>>>>>>> SqlLibrary.BIG_QUERY
>>>>>>>> )))
>>>>>>>> .programs(Programs.standard())
>>>>>>>> .build()
>>>>>>>> val planner = Frameworks.getPlanner(config)
>>>>>>>>
>>>>>>>> val query = "SELECT ARRAY_CONCAT(ARRAY[1, 2], ARRAY[2, 3])"
>>>>>>>> val parsedSqlNode = planner.parse(query)
>>>>>>>> planner.validate(parsedSqlNode)
>>>>>>>> ```
>>>>>>>>
>>>>>>>> Am I doing something wrong or it is a bug?
>>>>>>>>
>>>>>>>
>>>>>>
>>>>
>>>
>>
Re: ARRAY_CONCAT does not work
Posted by Michael Mior <mm...@apache.org>.
Exactly. I understand the problem is not CHAR(1) vs CHAR(7), but the record
type. That is the point I was trying to make.
--
Michael Mior
mmior@apache.org
Le jeu. 10 févr. 2022 à 07:47, Dmitry Sysolyatin <dm...@gmail.com>
a écrit :
> Michael, the problem is not because CHAR(1) and CHAR(7). Calcite can derive
> common type in this case = CHAR(7) and all will work ok.
>
> The problem is that one type is [<scalar> ARRAY] and another [<RecordType>
> ARRAY]. I see two options for resolving this problem:
>
> 1. Allow casting scalar type to RecordType with one field. I described it
> inside https://issues.apache.org/jira/browse/CALCITE-4999
> 2. Modify `ARRAY` function in the way that it will return ARRAY of scalar
> if subquery is used as argument. Like Postgres does -
>
> https://www.postgresql.org/docs/14/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
>
> On Thu, Feb 10, 2022 at 2:02 PM Michael Mior <mm...@apache.org> wrote:
>
> > The two types in your example are incompatible. One is an array of
> CHAR(1).
> > The other is an array of records, each with a single CHAR(7) field.
> > --
> > Michael Mior
> > mmior@apache.org
> >
> >
> > Le ven. 4 févr. 2022 à 11:27, Dmitry Sysolyatin <dm.sysolyatin@gmail.com
> >
> > a
> > écrit :
> >
> > > So, the previous case started to work. But I faced with another issue.
> > This
> > > query does not work:
> > >
> > > ```
> > > "SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> > > unnest(ARRAY['1','2']) x))"
> > > ```
> > >
> > > ```
> > > java.lang.IllegalArgumentException: Cannot infer return type for
> > > ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> > > ARRAY]
> > > ```
> > >
> > > I created an issue -
> https://issues.apache.org/jira/browse/CALCITE-4999
> > > and
> > > PR - https://github.com/apache/calcite/pull/2712
> > >
> > >
> > > On Fri, Feb 4, 2022 at 2:51 PM Dmitry Sysolyatin <
> > dm.sysolyatin@gmail.com>
> > > wrote:
> > >
> > > > Actually,
> > > >
> > `SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY)`
> > > > helped, thanks !
> > > > I will try to understand why it doesn't work with BABEL conformance
> > > >
> > > > On Fri, Feb 4, 2022 at 2:35 PM Dmitry Sysolyatin <
> > > dm.sysolyatin@gmail.com>
> > > > wrote:
> > > >
> > > >> How SqlParser config can help? If I understood correctly, parser
> > config
> > > >> affects only parser.parse stage. But code fails at the validation
> > phase
> > > >>
> > > >> Also, I would like to use ARRAY_CONCAT function with postgres
> dialect.
> > > My
> > > >> parser config at the moment:
> > > >>
> > > >> ```
> > > >> val parserConfig: SqlParser.Config =
> > > >> PostgresqlSqlDialect.DEFAULT
> > > >> .configureParser(SqlParser
> > > >> .config()
> > > >> // It is needed in order to parse PG "!~" operator
> > > >> .withParserFactory(SqlBabelParserImpl.FACTORY)
> > > >> )
> > > >> .withConformance(SqlConformanceEnum.BABEL)
> > > >> ```
> > > >>
> > > >> On Fri, Feb 4, 2022 at 2:26 PM Thomas Rebele
> > <trebele@tibco.com.invalid
> > > >
> > > >> wrote:
> > > >>
> > > >>> Hello,
> > > >>>
> > > >>> SqlParserTest might help you. It has some checks related to
> > BIG_QUERY.
> > > >>> Maybe a
> > > >>>
> > > >>>
> > >
> >
> .parserConfig(SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY))
> > > >>> could fix the problem.
> > > >>>
> > > >>> Cordialement / Best Regards,
> > > >>> *Thomas Rebele, PhD* | R&D Developer | Germany | www.tibco.com
> > > >>>
> > > >>>
> > > >>> On Fri, Feb 4, 2022 at 12:57 PM Dmitry Sysolyatin <
> > > >>> dm.sysolyatin@gmail.com>
> > > >>> wrote:
> > > >>>
> > > >>> > Hi!
> > > >>> > I have a problem with ARRAY_CONCAT operator.
> > > >>> >
> > > >>> > The following code failed with the exception ''No match found for
> > > >>> function
> > > >>> > signature array_concat(<INTEGER ARRAY>, <INTEGER ARRAY>)".
> > > >>> > ```
> > > >>> > val config = Frameworks.newConfigBuilder()
> > > >>> > .parserConfig(parserConfig)
> > > >>> > .defaultSchema(CalciteSchema.createRootSchema(false,
> > > >>> false).plus())
> > > >>> > .operatorTable(SqlOperatorTables.chain(
> > > >>> > SqlStdOperatorTable.instance(),
> > > >>> >
> > > SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
> > > >>> > SqlLibrary.BIG_QUERY
> > > >>> > )))
> > > >>> > .programs(Programs.standard())
> > > >>> > .build()
> > > >>> > val planner = Frameworks.getPlanner(config)
> > > >>> >
> > > >>> > val query = "SELECT ARRAY_CONCAT(ARRAY[1, 2], ARRAY[2, 3])"
> > > >>> > val parsedSqlNode = planner.parse(query)
> > > >>> > planner.validate(parsedSqlNode)
> > > >>> > ```
> > > >>> >
> > > >>> > Am I doing something wrong or it is a bug?
> > > >>> >
> > > >>>
> > > >>
> > >
> >
>
Re: ARRAY_CONCAT does not work
Posted by Dmitry Sysolyatin <dm...@gmail.com>.
Michael, the problem is not because CHAR(1) and CHAR(7). Calcite can derive
common type in this case = CHAR(7) and all will work ok.
The problem is that one type is [<scalar> ARRAY] and another [<RecordType>
ARRAY]. I see two options for resolving this problem:
1. Allow casting scalar type to RecordType with one field. I described it
inside https://issues.apache.org/jira/browse/CALCITE-4999
2. Modify `ARRAY` function in the way that it will return ARRAY of scalar
if subquery is used as argument. Like Postgres does -
https://www.postgresql.org/docs/14/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS
On Thu, Feb 10, 2022 at 2:02 PM Michael Mior <mm...@apache.org> wrote:
> The two types in your example are incompatible. One is an array of CHAR(1).
> The other is an array of records, each with a single CHAR(7) field.
> --
> Michael Mior
> mmior@apache.org
>
>
> Le ven. 4 févr. 2022 à 11:27, Dmitry Sysolyatin <dm...@gmail.com>
> a
> écrit :
>
> > So, the previous case started to work. But I faced with another issue.
> This
> > query does not work:
> >
> > ```
> > "SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> > unnest(ARRAY['1','2']) x))"
> > ```
> >
> > ```
> > java.lang.IllegalArgumentException: Cannot infer return type for
> > ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> > ARRAY]
> > ```
> >
> > I created an issue - https://issues.apache.org/jira/browse/CALCITE-4999
> > and
> > PR - https://github.com/apache/calcite/pull/2712
> >
> >
> > On Fri, Feb 4, 2022 at 2:51 PM Dmitry Sysolyatin <
> dm.sysolyatin@gmail.com>
> > wrote:
> >
> > > Actually,
> > >
> `SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY)`
> > > helped, thanks !
> > > I will try to understand why it doesn't work with BABEL conformance
> > >
> > > On Fri, Feb 4, 2022 at 2:35 PM Dmitry Sysolyatin <
> > dm.sysolyatin@gmail.com>
> > > wrote:
> > >
> > >> How SqlParser config can help? If I understood correctly, parser
> config
> > >> affects only parser.parse stage. But code fails at the validation
> phase
> > >>
> > >> Also, I would like to use ARRAY_CONCAT function with postgres dialect.
> > My
> > >> parser config at the moment:
> > >>
> > >> ```
> > >> val parserConfig: SqlParser.Config =
> > >> PostgresqlSqlDialect.DEFAULT
> > >> .configureParser(SqlParser
> > >> .config()
> > >> // It is needed in order to parse PG "!~" operator
> > >> .withParserFactory(SqlBabelParserImpl.FACTORY)
> > >> )
> > >> .withConformance(SqlConformanceEnum.BABEL)
> > >> ```
> > >>
> > >> On Fri, Feb 4, 2022 at 2:26 PM Thomas Rebele
> <trebele@tibco.com.invalid
> > >
> > >> wrote:
> > >>
> > >>> Hello,
> > >>>
> > >>> SqlParserTest might help you. It has some checks related to
> BIG_QUERY.
> > >>> Maybe a
> > >>>
> > >>>
> >
> .parserConfig(SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY))
> > >>> could fix the problem.
> > >>>
> > >>> Cordialement / Best Regards,
> > >>> *Thomas Rebele, PhD* | R&D Developer | Germany | www.tibco.com
> > >>>
> > >>>
> > >>> On Fri, Feb 4, 2022 at 12:57 PM Dmitry Sysolyatin <
> > >>> dm.sysolyatin@gmail.com>
> > >>> wrote:
> > >>>
> > >>> > Hi!
> > >>> > I have a problem with ARRAY_CONCAT operator.
> > >>> >
> > >>> > The following code failed with the exception ''No match found for
> > >>> function
> > >>> > signature array_concat(<INTEGER ARRAY>, <INTEGER ARRAY>)".
> > >>> > ```
> > >>> > val config = Frameworks.newConfigBuilder()
> > >>> > .parserConfig(parserConfig)
> > >>> > .defaultSchema(CalciteSchema.createRootSchema(false,
> > >>> false).plus())
> > >>> > .operatorTable(SqlOperatorTables.chain(
> > >>> > SqlStdOperatorTable.instance(),
> > >>> >
> > SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
> > >>> > SqlLibrary.BIG_QUERY
> > >>> > )))
> > >>> > .programs(Programs.standard())
> > >>> > .build()
> > >>> > val planner = Frameworks.getPlanner(config)
> > >>> >
> > >>> > val query = "SELECT ARRAY_CONCAT(ARRAY[1, 2], ARRAY[2, 3])"
> > >>> > val parsedSqlNode = planner.parse(query)
> > >>> > planner.validate(parsedSqlNode)
> > >>> > ```
> > >>> >
> > >>> > Am I doing something wrong or it is a bug?
> > >>> >
> > >>>
> > >>
> >
>
Re: ARRAY_CONCAT does not work
Posted by Michael Mior <mm...@apache.org>.
The two types in your example are incompatible. One is an array of CHAR(1).
The other is an array of records, each with a single CHAR(7) field.
--
Michael Mior
mmior@apache.org
Le ven. 4 févr. 2022 à 11:27, Dmitry Sysolyatin <dm...@gmail.com> a
écrit :
> So, the previous case started to work. But I faced with another issue. This
> query does not work:
>
> ```
> "SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
> unnest(ARRAY['1','2']) x))"
> ```
>
> ```
> java.lang.IllegalArgumentException: Cannot infer return type for
> ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
> ARRAY]
> ```
>
> I created an issue - https://issues.apache.org/jira/browse/CALCITE-4999
> and
> PR - https://github.com/apache/calcite/pull/2712
>
>
> On Fri, Feb 4, 2022 at 2:51 PM Dmitry Sysolyatin <dm...@gmail.com>
> wrote:
>
> > Actually,
> > `SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY)`
> > helped, thanks !
> > I will try to understand why it doesn't work with BABEL conformance
> >
> > On Fri, Feb 4, 2022 at 2:35 PM Dmitry Sysolyatin <
> dm.sysolyatin@gmail.com>
> > wrote:
> >
> >> How SqlParser config can help? If I understood correctly, parser config
> >> affects only parser.parse stage. But code fails at the validation phase
> >>
> >> Also, I would like to use ARRAY_CONCAT function with postgres dialect.
> My
> >> parser config at the moment:
> >>
> >> ```
> >> val parserConfig: SqlParser.Config =
> >> PostgresqlSqlDialect.DEFAULT
> >> .configureParser(SqlParser
> >> .config()
> >> // It is needed in order to parse PG "!~" operator
> >> .withParserFactory(SqlBabelParserImpl.FACTORY)
> >> )
> >> .withConformance(SqlConformanceEnum.BABEL)
> >> ```
> >>
> >> On Fri, Feb 4, 2022 at 2:26 PM Thomas Rebele <trebele@tibco.com.invalid
> >
> >> wrote:
> >>
> >>> Hello,
> >>>
> >>> SqlParserTest might help you. It has some checks related to BIG_QUERY.
> >>> Maybe a
> >>>
> >>>
> .parserConfig(SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY))
> >>> could fix the problem.
> >>>
> >>> Cordialement / Best Regards,
> >>> *Thomas Rebele, PhD* | R&D Developer | Germany | www.tibco.com
> >>>
> >>>
> >>> On Fri, Feb 4, 2022 at 12:57 PM Dmitry Sysolyatin <
> >>> dm.sysolyatin@gmail.com>
> >>> wrote:
> >>>
> >>> > Hi!
> >>> > I have a problem with ARRAY_CONCAT operator.
> >>> >
> >>> > The following code failed with the exception ''No match found for
> >>> function
> >>> > signature array_concat(<INTEGER ARRAY>, <INTEGER ARRAY>)".
> >>> > ```
> >>> > val config = Frameworks.newConfigBuilder()
> >>> > .parserConfig(parserConfig)
> >>> > .defaultSchema(CalciteSchema.createRootSchema(false,
> >>> false).plus())
> >>> > .operatorTable(SqlOperatorTables.chain(
> >>> > SqlStdOperatorTable.instance(),
> >>> >
> SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
> >>> > SqlLibrary.BIG_QUERY
> >>> > )))
> >>> > .programs(Programs.standard())
> >>> > .build()
> >>> > val planner = Frameworks.getPlanner(config)
> >>> >
> >>> > val query = "SELECT ARRAY_CONCAT(ARRAY[1, 2], ARRAY[2, 3])"
> >>> > val parsedSqlNode = planner.parse(query)
> >>> > planner.validate(parsedSqlNode)
> >>> > ```
> >>> >
> >>> > Am I doing something wrong or it is a bug?
> >>> >
> >>>
> >>
>
Re: ARRAY_CONCAT does not work
Posted by Dmitry Sysolyatin <dm...@gmail.com>.
So, the previous case started to work. But I faced with another issue. This
query does not work:
```
"SELECT ARRAY_CONCAT(ARRAY['1', '2'], array(select 'toast.' || x from
unnest(ARRAY['1','2']) x))"
```
```
java.lang.IllegalArgumentException: Cannot infer return type for
ARRAY_CONCAT; operand types: [CHAR(1) ARRAY, RecordType(CHAR(7) EXPR$0)
ARRAY]
```
I created an issue - https://issues.apache.org/jira/browse/CALCITE-4999 and
PR - https://github.com/apache/calcite/pull/2712
On Fri, Feb 4, 2022 at 2:51 PM Dmitry Sysolyatin <dm...@gmail.com>
wrote:
> Actually,
> `SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY)`
> helped, thanks !
> I will try to understand why it doesn't work with BABEL conformance
>
> On Fri, Feb 4, 2022 at 2:35 PM Dmitry Sysolyatin <dm...@gmail.com>
> wrote:
>
>> How SqlParser config can help? If I understood correctly, parser config
>> affects only parser.parse stage. But code fails at the validation phase
>>
>> Also, I would like to use ARRAY_CONCAT function with postgres dialect. My
>> parser config at the moment:
>>
>> ```
>> val parserConfig: SqlParser.Config =
>> PostgresqlSqlDialect.DEFAULT
>> .configureParser(SqlParser
>> .config()
>> // It is needed in order to parse PG "!~" operator
>> .withParserFactory(SqlBabelParserImpl.FACTORY)
>> )
>> .withConformance(SqlConformanceEnum.BABEL)
>> ```
>>
>> On Fri, Feb 4, 2022 at 2:26 PM Thomas Rebele <tr...@tibco.com.invalid>
>> wrote:
>>
>>> Hello,
>>>
>>> SqlParserTest might help you. It has some checks related to BIG_QUERY.
>>> Maybe a
>>>
>>> .parserConfig(SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY))
>>> could fix the problem.
>>>
>>> Cordialement / Best Regards,
>>> *Thomas Rebele, PhD* | R&D Developer | Germany | www.tibco.com
>>>
>>>
>>> On Fri, Feb 4, 2022 at 12:57 PM Dmitry Sysolyatin <
>>> dm.sysolyatin@gmail.com>
>>> wrote:
>>>
>>> > Hi!
>>> > I have a problem with ARRAY_CONCAT operator.
>>> >
>>> > The following code failed with the exception ''No match found for
>>> function
>>> > signature array_concat(<INTEGER ARRAY>, <INTEGER ARRAY>)".
>>> > ```
>>> > val config = Frameworks.newConfigBuilder()
>>> > .parserConfig(parserConfig)
>>> > .defaultSchema(CalciteSchema.createRootSchema(false,
>>> false).plus())
>>> > .operatorTable(SqlOperatorTables.chain(
>>> > SqlStdOperatorTable.instance(),
>>> > SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
>>> > SqlLibrary.BIG_QUERY
>>> > )))
>>> > .programs(Programs.standard())
>>> > .build()
>>> > val planner = Frameworks.getPlanner(config)
>>> >
>>> > val query = "SELECT ARRAY_CONCAT(ARRAY[1, 2], ARRAY[2, 3])"
>>> > val parsedSqlNode = planner.parse(query)
>>> > planner.validate(parsedSqlNode)
>>> > ```
>>> >
>>> > Am I doing something wrong or it is a bug?
>>> >
>>>
>>
Re: ARRAY_CONCAT does not work
Posted by Dmitry Sysolyatin <dm...@gmail.com>.
Actually,
`SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY)`
helped, thanks !
I will try to understand why it doesn't work with BABEL conformance
On Fri, Feb 4, 2022 at 2:35 PM Dmitry Sysolyatin <dm...@gmail.com>
wrote:
> How SqlParser config can help? If I understood correctly, parser config
> affects only parser.parse stage. But code fails at the validation phase
>
> Also, I would like to use ARRAY_CONCAT function with postgres dialect. My
> parser config at the moment:
>
> ```
> val parserConfig: SqlParser.Config =
> PostgresqlSqlDialect.DEFAULT
> .configureParser(SqlParser
> .config()
> // It is needed in order to parse PG "!~" operator
> .withParserFactory(SqlBabelParserImpl.FACTORY)
> )
> .withConformance(SqlConformanceEnum.BABEL)
> ```
>
> On Fri, Feb 4, 2022 at 2:26 PM Thomas Rebele <tr...@tibco.com.invalid>
> wrote:
>
>> Hello,
>>
>> SqlParserTest might help you. It has some checks related to BIG_QUERY.
>> Maybe a
>>
>> .parserConfig(SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY))
>> could fix the problem.
>>
>> Cordialement / Best Regards,
>> *Thomas Rebele, PhD* | R&D Developer | Germany | www.tibco.com
>>
>>
>> On Fri, Feb 4, 2022 at 12:57 PM Dmitry Sysolyatin <
>> dm.sysolyatin@gmail.com>
>> wrote:
>>
>> > Hi!
>> > I have a problem with ARRAY_CONCAT operator.
>> >
>> > The following code failed with the exception ''No match found for
>> function
>> > signature array_concat(<INTEGER ARRAY>, <INTEGER ARRAY>)".
>> > ```
>> > val config = Frameworks.newConfigBuilder()
>> > .parserConfig(parserConfig)
>> > .defaultSchema(CalciteSchema.createRootSchema(false, false).plus())
>> > .operatorTable(SqlOperatorTables.chain(
>> > SqlStdOperatorTable.instance(),
>> > SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
>> > SqlLibrary.BIG_QUERY
>> > )))
>> > .programs(Programs.standard())
>> > .build()
>> > val planner = Frameworks.getPlanner(config)
>> >
>> > val query = "SELECT ARRAY_CONCAT(ARRAY[1, 2], ARRAY[2, 3])"
>> > val parsedSqlNode = planner.parse(query)
>> > planner.validate(parsedSqlNode)
>> > ```
>> >
>> > Am I doing something wrong or it is a bug?
>> >
>>
>
Re: ARRAY_CONCAT does not work
Posted by Dmitry Sysolyatin <dm...@gmail.com>.
How SqlParser config can help? If I understood correctly, parser config
affects only parser.parse stage. But code fails at the validation phase
Also, I would like to use ARRAY_CONCAT function with postgres dialect. My
parser config at the moment:
```
val parserConfig: SqlParser.Config =
PostgresqlSqlDialect.DEFAULT
.configureParser(SqlParser
.config()
// It is needed in order to parse PG "!~" operator
.withParserFactory(SqlBabelParserImpl.FACTORY)
)
.withConformance(SqlConformanceEnum.BABEL)
```
On Fri, Feb 4, 2022 at 2:26 PM Thomas Rebele <tr...@tibco.com.invalid>
wrote:
> Hello,
>
> SqlParserTest might help you. It has some checks related to BIG_QUERY.
> Maybe a
>
> .parserConfig(SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY))
> could fix the problem.
>
> Cordialement / Best Regards,
> *Thomas Rebele, PhD* | R&D Developer | Germany | www.tibco.com
>
>
> On Fri, Feb 4, 2022 at 12:57 PM Dmitry Sysolyatin <dm.sysolyatin@gmail.com
> >
> wrote:
>
> > Hi!
> > I have a problem with ARRAY_CONCAT operator.
> >
> > The following code failed with the exception ''No match found for
> function
> > signature array_concat(<INTEGER ARRAY>, <INTEGER ARRAY>)".
> > ```
> > val config = Frameworks.newConfigBuilder()
> > .parserConfig(parserConfig)
> > .defaultSchema(CalciteSchema.createRootSchema(false, false).plus())
> > .operatorTable(SqlOperatorTables.chain(
> > SqlStdOperatorTable.instance(),
> > SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
> > SqlLibrary.BIG_QUERY
> > )))
> > .programs(Programs.standard())
> > .build()
> > val planner = Frameworks.getPlanner(config)
> >
> > val query = "SELECT ARRAY_CONCAT(ARRAY[1, 2], ARRAY[2, 3])"
> > val parsedSqlNode = planner.parse(query)
> > planner.validate(parsedSqlNode)
> > ```
> >
> > Am I doing something wrong or it is a bug?
> >
>
Re: ARRAY_CONCAT does not work
Posted by Thomas Rebele <tr...@tibco.com.INVALID>.
Hello,
SqlParserTest might help you. It has some checks related to BIG_QUERY.
Maybe a
.parserConfig(SqlParser.Config.DEFAULT.withConformance(SqlConformanceEnum.BIG_QUERY))
could fix the problem.
Cordialement / Best Regards,
*Thomas Rebele, PhD* | R&D Developer | Germany | www.tibco.com
On Fri, Feb 4, 2022 at 12:57 PM Dmitry Sysolyatin <dm...@gmail.com>
wrote:
> Hi!
> I have a problem with ARRAY_CONCAT operator.
>
> The following code failed with the exception ''No match found for function
> signature array_concat(<INTEGER ARRAY>, <INTEGER ARRAY>)".
> ```
> val config = Frameworks.newConfigBuilder()
> .parserConfig(parserConfig)
> .defaultSchema(CalciteSchema.createRootSchema(false, false).plus())
> .operatorTable(SqlOperatorTables.chain(
> SqlStdOperatorTable.instance(),
> SqlLibraryOperatorTableFactory.INSTANCE.getOperatorTable(
> SqlLibrary.BIG_QUERY
> )))
> .programs(Programs.standard())
> .build()
> val planner = Frameworks.getPlanner(config)
>
> val query = "SELECT ARRAY_CONCAT(ARRAY[1, 2], ARRAY[2, 3])"
> val parsedSqlNode = planner.parse(query)
> planner.validate(parsedSqlNode)
> ```
>
> Am I doing something wrong or it is a bug?
>