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.