Querying for NULL values results in error `DEFAULT is not allowed in this context`

[deepakrkris]

Steps to reproduce

Detailed steps are in issue loopbackio/loopback-next#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 loopbackio/loopback-next#4332

Additional information

Related Issues

[deepakrkris]

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.

[jannyHou]

@deepakrkris Good investigation 👍

I am a bit confused with the 3 statements:

• Which tries to replace with a string "DEFAULT" for null values in id fields, which causes this issue.
  • IMO this means the "DEFAULT" value causes the problem, so when the id field in query is null, we should preserve it instead of replacing it with default
• But the following SQL runs without any issue: SELECT "id","title","color" FROM "public"."todolist" WHERE "id" IN (NULL, 2) ORDER BY "id"
  • This seems the same as 1, and should be the EXPECTED generated query?
• So post gres does not seem to expect id field queries to have non-NULL values.
  • what does it mean?

[deepakrkris]

@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.