[
https://issues.apache.org/jira/browse/CALCITE-7512?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
terran2010 updated CALCITE-7512:
--------------------------------
Description:
Currently, babel does not support "@> <@ &&". Upon checking other database
documentation, it is found that only PostgreSQL and databases compatible with
PostgreSQL support the above operation symbols. Below are some examples of the
usage of these operators
{code:java}
-- @> contains: left array contains all elements of right array
SELECT ARRAY[1,2,3] @> ARRAY[2,3]; -- true, [1,2,3] contains [2,3]
SELECT ARRAY[1,2,3] @> ARRAY[2,4]; -- false, 4 is not in left array-- <@ is
contained by: left array is subset of right array
SELECT ARRAY[2] <@ ARRAY[1,2,3]; -- true, [2] is subset of [1,2,3]
SELECT ARRAY[2,4] <@ ARRAY[1,2,3]; -- false, 4 is not in right array-- &&
overlap: two arrays have common elements
SELECT ARRAY[1,2] && ARRAY[2,3]; -- true, common element 2
SELECT ARRAY[1,2] && ARRAY[3,4]; -- false, no common elements{code}
{code:java}
-- @> contains: left JSON object contains all key-value pairs of right
SELECT '{"a":1,"b":2}'::jsonb @> '{"b":2}'::jsonb; -- true, {"b":2} is
contained
SELECT '{"a":1,"b":2}'::jsonb @> '{"b":3}'::jsonb; -- false, value of b
differs
SELECT '{"a":1,"b":2}'::jsonb @> '{"c":1}'::jsonb; -- false, key c does
not exist-- <@ is contained by: left JSON is subset of right JSON
SELECT '{"b":2}'::jsonb <@ '{"a":1,"b":2}'::jsonb; -- true, subset
relationship-- && have common top-level keys: two JSON objects share keys
SELECT '{"a":1,"b":2}'::jsonb && '{"b":3,"c":4}'::jsonb; -- true, common key "b"
SELECT '{"a":1,"b":2}'::jsonb && '{"c":3,"d":4}'::jsonb; -- false, no common
keys{code}
This time, we aim to complete the parsing support for "@> <@ &&" and already
have support for array function operations
We can refer postgresql document about Position:
[https://www.postgresql.org/docs/current/functions-array.html]
We can consider supporting the above situation.
was:
When execute sql with babel：
{code:java}
SELECT ARRAY[1,2,3] @> ARRAY[1,2] {code}
now will run error：
{code:java}
Caused by: org.apache.calcite.sql.parser.babel.TokenMgrError: Lexical error at
line 1, column 21. Encountered: "@" (64), after : "" at
org.apache.calcite.sql.parser.babel.SqlBabelParserImplTokenManager.getNextToken(SqlBabelParserImplTokenManager.java:26853)
at
org.apache.calcite.sql.parser.babel.SqlBabelParserImpl.jj_scan_token(SqlBabelParserImpl.java:41652)
at
org.apache.calcite.sql.parser.babel.SqlBabelParserImpl.jj_3_414(SqlBabelParserImpl.java:31680)
at
org.apache.calcite.sql.parser.babel.SqlBabelParserImpl.jj_2_414(SqlBabelParserImpl.java:14651)
at
org.apache.calcite.sql.parser.babel.SqlBabelParserImpl.AddExpression2b(SqlBabelParserImpl.java:4917)
at
org.apache.calcite.sql.parser.babel.SqlBabelParserImpl.Expression2(SqlBabelParserImpl.java:4956)
at
org.apache.calcite.sql.parser.babel.SqlBabelParserImpl.Expression(SqlBabelParserImpl.java:4805)
at
org.apache.calcite.sql.parser.babel.SqlBabelParserImpl.SelectExpression(SqlBabelParserImpl.java:2816)
{code}
We can refer postgresql document about Position:
[https://www.postgresql.org/docs/current/functions-array.html]
We can consider supporting the above situation.
> Support array operators for PostgreSql
> --------------------------------------
>
> Key: CALCITE-7512
> URL: https://issues.apache.org/jira/browse/CALCITE-7512
> Project: Calcite
> Issue Type: New Feature
> Components: babel
> Affects Versions: 1.41.0
> Reporter: terran2010
> Priority: Major
> Labels: pull-request-available
>
> Currently, babel does not support "@> <@ &&". Upon checking other database
> documentation, it is found that only PostgreSQL and databases compatible with
> PostgreSQL support the above operation symbols. Below are some examples of
> the usage of these operators
> {code:java}
> -- @> contains: left array contains all elements of right array
> SELECT ARRAY[1,2,3] @> ARRAY[2,3]; -- true, [1,2,3] contains [2,3]
> SELECT ARRAY[1,2,3] @> ARRAY[2,4]; -- false, 4 is not in left array-- <@
> is contained by: left array is subset of right array
> SELECT ARRAY[2] <@ ARRAY[1,2,3]; -- true, [2] is subset of [1,2,3]
> SELECT ARRAY[2,4] <@ ARRAY[1,2,3]; -- false, 4 is not in right array-- &&
> overlap: two arrays have common elements
> SELECT ARRAY[1,2] && ARRAY[2,3]; -- true, common element 2
> SELECT ARRAY[1,2] && ARRAY[3,4]; -- false, no common elements{code}
> {code:java}
> -- @> contains: left JSON object contains all key-value pairs of right
> SELECT '{"a":1,"b":2}'::jsonb @> '{"b":2}'::jsonb; -- true, {"b":2} is
> contained
> SELECT '{"a":1,"b":2}'::jsonb @> '{"b":3}'::jsonb; -- false, value of b
> differs
> SELECT '{"a":1,"b":2}'::jsonb @> '{"c":1}'::jsonb; -- false, key c does
> not exist-- <@ is contained by: left JSON is subset of right JSON
> SELECT '{"b":2}'::jsonb <@ '{"a":1,"b":2}'::jsonb; -- true, subset
> relationship-- && have common top-level keys: two JSON objects share keys
> SELECT '{"a":1,"b":2}'::jsonb && '{"b":3,"c":4}'::jsonb; -- true, common key
> "b"
> SELECT '{"a":1,"b":2}'::jsonb && '{"c":3,"d":4}'::jsonb; -- false, no common
> keys{code}
> This time, we aim to complete the parsing support for "@> <@ &&" and already
> have support for array function operations
> We can refer postgresql document about Position:
> [https://www.postgresql.org/docs/current/functions-array.html]
> We can consider supporting the above situation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
