Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMgsh-000000029Tu-2kca for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:32 +0000 Received: from localhost ([127.0.0.1]:51180 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMgsf-00000008qJl-24W7 for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:18:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:57952) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJze-0000000353Q-0KIy for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:52:10 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJza-00000001Y0G-1DkB for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:52:09 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=4NcHfe4cxnY0F55hqw0glI8dv0sNkAMJ8ZXt6Mq9WVE=; b=BE0L66dKi0xVYe0yZOHRgVFodn mKxljTSdQ/94p39f2jWq4T/zRaFVQ6ZJCHY3WfuxuTLXvHH9YwSGLqk6ZRMJyl1zpYDRAHSO/bN+T xs2V54MMMMyg3Wgtl5RoexxQOfL8+K6gn7d/JpIqTxnu46NQD8gXEQaHPirJE/+9/yw9P8oEh9aK+ idlspXYr/nlxE47CjBovt+TMLSEp1AgX+/lWxQblDRpa/avftm3XSUXR66ukTS4SW6Rv8G9cYEbKL gRHS1IbunSuZ0hS3Y50sLh9jK3cjIK3Z5/bt/Q5eMNX6ISRaF2phLPH4dJnzD8AjNlgPrYB/lY+v9 RtyixNXw==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMJzY-00000004I0e-3zCe for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:52:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMJzW-00000005coq-49qM for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 06:52:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19813: jsonpath: datetime errors bypass silent/ON ERROR; boolean() inconsistent; PASSING rejects bpchar/nam To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Thu, 29 Oct 2026 06:51:13 +0000 Message-ID: <19813-6a460679ae295ed6@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19813 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux x86_64 Description: =20 Three issues in the jsonpath / SQL:JSON error-handling family. All reproduce on the official postgres:latest docker image (18.6). 1. .datetime() template errors bypass silent mode and ON ERROR SELECT jsonb_path_query(jsonb '"abc"', '$.datetime("XYZ")', '{}'::jsonb, true); -- ERROR: invalid datetime format separator: "X" (expected: no rows) SELECT JSON_VALUE(jsonb '"abc"', '$.datetime("XYZ")' RETURNING text NULL ON ERROR); -- ERROR: invalid datetime format separator: "X" (expected: NULL) SELECT JSON_VALUE(jsonb '"abc"', '$.datetime("XYZ")' RETURNING text DEFAULT 'dflt' ON ERROR); -- ERROR (expected: 'dflt') Controls: - A valid template with a non-matching input correctly returns no rows under silent: jsonb_path_query(jsonb '"abc"', '$.datetime("YYYY-MM-DD")', '{}'::jsonb, true) -> (no rows) - .decimal() was given exactly this treatment by commit 84001a0 ("Fix jsonpath .decimal() to honor silent mode"): jsonb_path_query(jsonb '1.234', '$.decimal(10,2000)', '{}'::jsonb, true) -> (no rows) .datetime() appears to have the same class of execution error without the same handling. - Without silent, raising the error is correct. - $.integer() on a non-matching value under silent correctly returns no rows, so the mechanism works for other methods. 2. .boolean() is inconsistent for numerically equal values SELECT jsonb_path_query(jsonb '0', '$.boolean()'); -> false SELECT jsonb_path_query(jsonb '0.0', '$.boolean()'); -> ERROR: argument "0.0" of jsonpath item method .boolean() is invalid for type boolean SELECT jsonb_path_query(jsonb '1e2', '$.boolean()'); -> true SELECT jsonb_path_query(jsonb '100.0', '$.boolean()'); -> ERROR SELECT jsonb_path_query(jsonb '2147483648', '$.boolean()'); -> ERROR (outside int4 range) SELECT jsonb '0' =3D jsonb '0.0'; -> true (the two inputs are the same JSON number) SELECT jsonb '1e2' =3D jsonb '100.0'; -> true The documentation says .boolean() converts "a JSON boolean, number, or string". The sibling methods accept these inputs: .integer()/.bigint()/.number() on 1.5 return 2/2/1.5, while .boolean() errors; under silent, .boolean() yields no rows where .integer() yields a value. The implementation appears to route the number through int4 parsing, so any spelling of a number that is not a plain int4 literal fails. 3. JSON_* PASSING rejects bpchar/name values, bypassing ON ERROR CREATE TABLE tchar (a char(3), b name, c text); INSERT INTO tchar VALUES ('abc','abc','abc'); SELECT JSON_VALUE(jsonb 'null', '$v' PASSING c AS v) FROM tchar; -> 'abc' (text works) SELECT JSON_VALUE(jsonb 'null', '$v' PASSING a AS v RETURNING text NULL ON ERROR) FROM tchar; -- ERROR: could not convert value of type character to jsonpath SELECT JSON_VALUE(jsonb 'null', '$v' PASSING b AS v RETURNING text NULL ON ERROR) FROM tchar; -- ERROR: could not convert value of type name to jsonpath Two problems: a char(N) column or a name column (the type that information_schema columns use) cannot be passed to a jsonpath variable at all, and the conversion failure is raised as a hard error that ignores the ON ERROR clause of the enclosing SQL/JSON expression.