I discovered this bug when I found a date equality filter not working.
When doing a where filter on date equality such as:
select * from "mytable" where "mycol" = '2023-01-01';
it was not correctly return the matching rows.
After some more investigation I found that this was due to how I was inserting the rows.
When I used the pg-mem api to insert i.e pgMemDb.public.getTable('mytable').insert({ mycol: '2023-01-01' })
and did a subsequent select query of select * from "mytable" where "mycol" = '2023-01-01'; it returned nothing.
When I inserted the row using the query interface insert into "mytable" ("mycol") values ('2023-01-01')
and ran the same select query as above it returned the row.
There are no error messages returned.
To Reproduce
-- This SQL succeeds within the play ground and when run using the query interface on pg-mem.
create table "mytable" ("mycol" date);
insert into "mytable" ("mycol") values ('2020-01-01');
select * from "mytable" where "mycol" = '2020-01-01';
// This does not succeed and returns an empty list.
import { newDb, DataType } from "pg-mem";
const db = newDb();
db.public.declareTable({
name: "mytable",
fields: [{ type: DataType.date, name: "mycol" }],
});
db.public.getTable("mytable").insert({ mycol: "2020-01-01" });
console.log("select all", db.public.many(`SELECT * FROM "mytable"`)); // [{mycol: '2020-01-01'}]
console.log(
"select only dates matching",
db.public.many(`SELECT * FROM "mytable" WHERE "mycol" = '2020-01-01'`)
); // should be [{mycol: '2020-01-01'}], is instead []
// This succeeds and returns the inserted row
import { newDb, DataType } from "pg-mem";
const db = newDb();
// db.public.query(`create table "mytable" ("mycol" date);`);
db.public.declareTable({
name: "mytable",
fields: [{ type: DataType.date, name: "mycol" }],
});
db.public.query(`insert into "mytable" ("mycol") values ('2020-01-01');`);
console.log(
db.public.many(`select * from "mytable" where "mycol" = '2020-01-01';`)
);
pg-mem version
2.6.12
I have attached some demo files that demonstrate the bug
demo.zip
Describe the bug
I discovered this bug when I found a date equality filter not working. When doing a where filter on date equality such as:
it was not correctly return the matching rows.
After some more investigation I found that this was due to how I was inserting the rows.
When I used the pg-mem api to insert i.e
pgMemDb.public.getTable('mytable').insert({ mycol: '2023-01-01' })
and did a subsequent select query ofselect * from "mytable" where "mycol" = '2023-01-01';
it returned nothing.When I inserted the row using the query interface
insert into "mytable" ("mycol") values ('2023-01-01')
and ran the same select query as above it returned the row.There are no error messages returned.
To Reproduce
pg-mem version
2.6.12
I have attached some demo files that demonstrate the bug demo.zip