Closed deepakrkris closed 4 years ago
LoopBack postgres connector makes this check : https://github.com/strongloop/loopback-connector-postgresql/blob/master/lib/postgresql.js#L630 which tries to replace with a string "DEFAULT" for null values in id fields, which causes this issue.
But the following SQL runs without any issue:
SELECT "id","title","color" FROM "public"."todolist" WHERE "id" IN (NULL, 2) ORDER BY "id"
DDL:
CREATE TABLE public.todolist
("id" int8 NOT NULL,
title varchar(25),
color varchar(25),
PRIMARY KEY ("id"))
CREATE TABLE public.todo (
"id" int8 NOT NULL,
title varchar(25),
"desc" varchar(25),
iscomplete bool,
todoListId int8,
PRIMARY KEY ("id")
);
ALTER TABLE public.todo ADD CONSTRAINT todoListId FOREIGN KEY (todoListId) REFERENCES todolist(id)
CREATE UNIQUE INDEX title_index ON todo (title);
INSERT INTO public.todolist (id, title, color) VALUES (1, 'green', 'green' );
INSERT INTO public.todo (id, title, "desc", todoListId, iscomplete) VALUES (1, 'green', 'basketball', 1, true);
INSERT INTO public.todolist (id, title, color) VALUES (2, 'red', 'red' );
INSERT INTO public.todo (id, title, "desc", todoListId, iscomplete) VALUES (2, 'red', 'soccer', 2, true);
So post gres does not seem to expect id field queries to have non-NULL values.
@deepakrkris Good investigation 👍
I am a bit confused with the 3 statements:
null
, we should preserve it instead of replacing it with defaultSELECT "id","title","color" FROM "public"."todolist" WHERE "id" IN (NULL, 2) ORDER BY "id"
@jannyHou technically all 3 statements mean the same. Having a Null in the select query where clause for an id field is accepted. Default can only used for insert , update statements for an auto increment ID field or non ID fields.
Steps to reproduce
Detailed steps are in issue https://github.com/strongloop/loopback-next/issues/4332
Current Behavior
When the connector is asked to build a where clause it replaces NULL values with
DEFAULT
for id fields which seems to cause issues.Expected Behavior
This check seems to be an unnecessary one, postgres does not seem to complain for NULL values placed in where clauses even for id fields.
Link to reproduction sandbox
Provided in issue https://github.com/strongloop/loopback-next/issues/4332
Additional information
Related Issues