[YSQL] Issue with queries involving small numeric(m,n) fields as keys

[ajcaldera1]

UPDATE: Issue is not specific to numeric type. It affects tables if the number of columns in PRIMARY KEY is 3 or more and the order in which columns are specified in CREATE TABLE is different from the order of those columns in the PRIMARY KEY clause.

mydb=> create table c_food (c_id numeric(5,0), c_w_id numeric(4,0), c_d_id numeric(2,0), primary key(c_w_id, c_d_id, c_id));
CREATE TABLE
mydb=> insert into c_food values(9902,89,1);
INSERT 0 1
mydb=> select * from c_food where c_d_id = 1;
ERROR:  numeric field overflow
DETAIL:  A field with precision 2, scale 0 must round to an absolute value less than 10^2.
mydb=> select * from c_food where c_d_id = cast(1 as numeric(2,0));
ERROR:  numeric field overflow
DETAIL:  A field with precision 2, scale 0 must round to an absolute value less than 10^2.

[kmuthukk]

thx for finding/reporting this @ajcaldera1 !

[ndeodhar]

Good find @ajcaldera1 !
This seems to be similar to #1250 which happens if the order in which columns are specified while creating table don't match the order in which primary keys are specified. @d-uspenskiy had fixed the bug during table creation. It looks like we have a similar bug on the INSERT/SELECT path.

If we change the CREATE TABLE column ordering to match PK ordering, then it works:

create table c_food_2 (c_w_id numeric(4,0), c_d_id numeric(2,0), c_id numeric(5,0), primary key(c_w_id, c_d_id, c_id));
insert into c_food_2 values(89,1,9902);
select * from c_food_2 where c_d_id = 1;
 c_w_id | c_d_id | c_id 
--------+--------+------
     89 |      1 | 9902