exec postgresql range type construcor function faild, error: no viable alternative at input xxxxxxxx tsrange

[weibiansanjue]

Question

• env

  • shardingsphere-proxy 5.3.0 / 5.3.1
  • postgresql 9.6.4
  • client : dbeaver 23.0.0
  • Use

    clent --sql--> shardingsphere-proxy ----> postgresql
    

• mini case

  • select public.int4range(400, 500, '[]');

  • ERROR: You have an error in your SQL syntax: select public.int4range(400, 500, '[]')
    , no viable alternative at input 'selectpublic.int4range' at line 1, position 14, near [@3,14:22='int4range',<219>,1:14]
    

  • Other range type construcor function is same too.(int8range, numrange, tsrange, tstzrange, daterange)

  • other function don't wrapped in quotes is success

• solution

  • range type construcor function wrapped in quotes is success, like

    select "int4range"(400, 500, '[]');
    select "tsrange"('[2023-01-01 14:30, 2023-03-01 15:30]');
    

• question

  • Why does this happen that range type constructor function？
  • What function needs wrapped in quotes? or only range type constructor function have to?

• suppose

  • Each range type has a constructor function with the same name as the range type, range type name is keyword in ANTLR4 file，mybe name conflict or ambiguity

• other test

  • data type, no quotes success

    CREATE TABLE reservation (
        during tsrange
    );
    
    INSERT INTO reservation VALUES ('[2010-01-01 11:30, 2010-01-01 15:00)');
    

  • defining new range types, success

    CREATE TYPE floatrange AS RANGE (
        subtype = float8,
        subtype_diff = float8mi
    );
    
    SELECT '[1.234, 5.678]'::floatrange;  -- no quotes
    

• Thanks a lot.

[weibiansanjue]

test case use quotes, success

/** int4range  **/
select '[400,500]'::"int4range";

select null::"int4range";
select "int4range"(null);
select 'empty'::"int4range";
select "int4range"('empty');

select "int4range"('[400,500]');
select "int4range"(400, 500, '[]');


/** int8range — bigint的范围  **/
select '[400000000000,500000000000]'::"int8range";

select null::"int8range";
select "int8range"(null);
select 'empty'::"int8range";
select "int8range"('empty');

select "int8range"('[400000000000,500000000000]');
select "int8range"(400000000000, 500000000000, '[]');


/** numrange  **/
select '[11.11, 22.22]'::"numrange";

select null::"numrange";
select "numrange"(null);
select 'empty'::"numrange";
select "numrange"('empty');

select "numrange"('[11.11, 22.22]');
select "numrange"(11.11, 22.22, '[]');


/** tsrange  **/
select '[2023-01-01 14:30, 2023-03-01 15:30]'::"tsrange";

select null::"tsrange";
select "tsrange"(null);
select 'empty'::"tsrange";
select "tsrange"('empty');

select "tsrange"('[2023-01-01 14:30, 2023-03-01 15:30]');
select "tsrange"(now()::timestamp(0)without time zone, now()::timestamp(0)without time zone, '[]');


/** tstzrange **/
select '[2023-01-01 14:30, 2023-03-01 15:30]'::"tstzrange";

select null::"tstzrange";
select "tstzrange"(null);
select 'empty'::"tstzrange";
select "tstzrange"('empty');

select "tstzrange"(now(), now(), '[]');


/** daterange  **/
select '[2023-01-01, 2023-03-31)'::"daterange";

select null::"daterange";
select "daterange"(null);
select 'empty'::"daterange";
select "daterange"('empty');

select "daterange"('2023-01-01', '2023-03-31', '[]');
select "daterange"(current_date-1, current_date, '[]');

[RaigorJiang]

Hi @weibiansanjue Thanks for your feedback, are you interested in submitting a PR to improve it?

[weibiansanjue]

Hi @weibiansanjue Thanks for your feedback, are you interested in submitting a PR to improve it?

Hi @RaigorJiang, I'm sorry, I find this question, but I'm not capable of solving this problem for the time being. if you have time, could you please help me? thanks a lot.

[RaigorJiang]

Ok, I'll mark it as volunteer wanted, see if anyone is interested.

[terrymanu]

Problem Understanding:

In PostgreSQL 9.6.4 with ShardingSphere-Proxy 5.3.0/5.3.1, running range constructor calls like select public.int4range(400, 500, '[]'); fails unless the function name is quoted. Using quoted names or range literals/type casts works.

Root Cause:

ShardingSphere’s PostgreSQL grammar treats int4range/int8range/numrange/tsrange/tstzrange/daterange as keywords (PostgreSQLKeyword.g4). In the function name rule (BaseRule.g4 funcName → typeFunctionName), only identifiers, unreserved words, and specific typeFuncName keywords are allowed; these range keywords are not included. As a result, bare int4range(...) is seen as a reserved token and rejected, leading to the “no viable alternative” parse error.

Problem Analysis:

During parsing, funcName must match typeFunctionName (identifier | unreservedWord | typeFuncNameKeyword). The lexer produces INT4RANGE tokens for range constructors; those tokens are outside the allowed set, so funcName fails. Quoting the name forces it to an IDENTIFIER_ token, which passes. This is a parser-layer issue, not a database behavior difference.

Problem Conclusion:

This is a SQL parser bug: range constructor keywords are excluded from allowable function names. Workarounds: quote the function name or use range literals/type casts. To fix, the PostgreSQL grammar should allow these range keywords as function names (e.g., add them to typeFunctionName/unreserved) to match native PostgreSQL syntax.