Re: EXPLAIN(VERBOSE) to CTE with SEARCH BREADTH FIRST fails

> WorkTable Scan on gen gen_1 (cost=0.00..0.23 rows=3 width=4) Output: (gen_1.n + 1) Filter: (gen_1.n < 3) (9 rows) Both variants work fine before that patch (4ac0f450b698442c3273ddfe8eed0e1a7e56645f). Markus Winand winand.at > On 21.09.2021, at 14:

Re: EXPLAIN(VERBOSE) to CTE with SEARCH BREADTH FIRST fails

> On 11.10.2021, at 16:27, Peter Eisentraut > wrote: > > On 11.10.21 12:22, Markus Winand wrote: >> Both variants work fine before that patch >> (4ac0f450b698442c3273ddfe8eed0e1a7e56645f). > > That commit is a message wording patch. Are you sure you meant that

SQL/JSON path: collation for comparisons, minor typos in docs

Hi! I was playing around with JSON path quite a bit and might have found one case where the current implementation doesn’t follow the standard. The functionality in question are the comparison operators except ==. They use the database default collation rather then the standard-mandated "Unicod

Re: SQL/JSON path: collation for comparisons, minor typos in docs

t; wrote: >> On Thu, Aug 8, 2019 at 12:55 AM Alexander Korotkov >> wrote: >>> On Wed, Aug 7, 2019 at 4:11 PM Alexander Korotkov >>> wrote: >>>> On Wed, Aug 7, 2019 at 2:25 PM Markus Winand >>>> wrote: >>>>> I was playing around w

VALUES ROW(...)

Hi! PostgreSQL does not accept the following standard conforming statement: VALUES ROW(1,2), ROW(3,4) There is a comment about this in the source code [0]: /* * We should allow ROW '(' expr_list ')' too, but that seems to require * making VALUES a fully reserved word, which will probably bre

Re: PostgreSQL vs SQL/XML Standards

> On 2018-11-6, at 15:23 , Pavel Stehule wrote: > > > > po 29. 10. 2018 v 11:45 odesílatel Pavel Stehule > napsal: > > > po 29. 10. 2018 v 10:11 odesílatel Pavel Stehule > napsal: > Hi > > čt 25. 10. 2018 v 21:47 odesílatel Alvaro Herrera > napsal: > On 2018-Oct-25, Pavel Stehule wro

Re: PostgreSQL vs SQL/XML Standards

> On 2018-11-9, at 05:07 , Pavel Stehule wrote: > > > > čt 8. 11. 2018 v 15:18 odesílatel Markus Winand <mailto:markus.win...@winand.at>> napsal: > > > On 2018-11-6, at 15:23 , Pavel Stehule > <mailto:pavel.steh...@gmail.com>> wrote: > >

Re: Wrong command name in writeable-CTE related error messages

> On 23.05.2023, at 19:40, Tom Lane wrote: > > Markus Winand writes: >> I noticed that errors due to writable CTEs in read-only or non-volatile >> context say the offensive command is SELECT. > > Good point. > >> My first thought was that these error

Re: ON ERROR in json_query and the like

> On 11.06.2024, at 03:58, jian he wrote: > > On Tue, May 28, 2024 at 5:29 PM Markus Winand wrote: >> >> Hi! >> >> I’ve noticed two “surprising” (to me) behaviors related to >> the “ON ERROR” clause of the new JSON query functions in 17beta1. >&g

Re: ON ERROR in json_query and the like

> On 04.06.2024, at 07:00, jian he wrote: > > On Tue, May 28, 2024 at 5:29 PM Markus Winand wrote: > >> 2. EMPTY [ARRAY|OBJECT] ON ERROR implies ERROR ON EMPTY >> >> 17beta1=# SELECT JSON_QUERY('[]', '$[*]' EMPTY ARRAY ON ERROR) a; >&g

Re: ON ERROR in json_query and the like

> On 12.06.2024, at 15:31, David G. Johnston wrote: > > On Tuesday, May 28, 2024, Markus Winand wrote: > > 2. EMPTY [ARRAY|OBJECT] ON ERROR implies ERROR ON EMPTY > >17beta1=# SELECT JSON_QUERY('[]', '$[*]' EMPTY ARRAY ON ERROR) a; > a

Re: ON ERROR in json_query and the like

> On 17.06.2024, at 08:20, Amit Langote wrote: > > Hi, > > (apologies for not replying to this thread sooner) > > On Tue, May 28, 2024 at 6:57 PM Pavel Stehule wrote: >> út 28. 5. 2024 v 11:29 odesílatel Markus Winand >> napsal: >>> >>>

Re: ON ERROR in json_query and the like

> On 21.06.2024, at 03:00, David G. Johnston wrote: > > On Thu, Jun 20, 2024 at 5:22 PM Amit Langote wrote: > > Soft error handling *was* used for catching cast errors in the very > early versions of this patch (long before I got involved and the > infrastructure you mention got added). It w

Re: ON ERROR in json_query and the like

> On 21.06.2024, at 06:46, David G. Johnston wrote: >> >> On Thursday, June 20, 2024, Pavel Stehule wrote: >> >> >> pá 21. 6. 2024 v 6:01 odesílatel Amit Langote >> napsal: >> On Fri, Jun 21, 2024 at 10:01 AM David G. Johnston >> wrote: >> >> > > By the standard, it is implementation-de

Re: ON ERROR in json_query and the like

> On 21.06.2024, at 07:38, David G. Johnston wrote: > > On Thursday, June 20, 2024, Markus Winand wrote: > > > > On 21.06.2024, at 06:46, David G. Johnston > > wrote: > >> > > > > > 2 also has the benefit of being standard conforming

ON ERROR in json_query and the like

Hi! I’ve noticed two “surprising” (to me) behaviors related to the “ON ERROR” clause of the new JSON query functions in 17beta1. 1. JSON parsing errors are not subject to ON ERROR Apparently, the functions expect JSONB so that a cast is implied when providing TEXT. However, the errors durin

Wrong command name in writeable-CTE related error messages

Hi! I noticed that errors due to writable CTEs in read-only or non-volatile context say the offensive command is SELECT. For example a writeable CTE in a IMMUTABLE function: CREATE TABLE t (x INTEGER); CREATE FUNCTION immutable_func() RETURNS INTEGER LANGUAGE SQL IMMUTABLE AS $$ WI

ERROR: wrong varnullingrels (b 3) (expected (b)) for Var 2/1

I found an error similar to others before ([1]) that is still persists as of head right now (0bcb3ca3b9). CREATE TABLE t ( n INTEGER ); SELECT * FROM (VALUES (1)) t(c) LEFT JOIN t ljl1 ON true LEFT JOIN LATERAL (WITH cte AS (SELECT * FROM t WHERE t.n = ljl1.n) SELECT * FROM cte) l

Re: ERROR: wrong varnullingrels (b 3) (expected (b)) for Var 2/1

> On 31.05.2023, at 08:36, Richard Guo wrote: > > Attached is a patch for that. Does this make sense? > > Thanks > Richard > All I can say is that it fixes the error for me — also for the non-simplified original query that I have. -markus

Conflict of implicit collations doesn't propagate out of subqueries

Hi! I think I’ve found a bug related to the strength of collations. Attached is a WIP patch, that address some other issues too. In this this example, the conflict of implicit collations propagates correctly: WITH data (c, posix) AS ( values ('a' COLLATE "C", 'b' COLLATE

Re: Conflict of implicit collations doesn't propagate out of subqueries

> On 28.05.2020, at 23:43, Tom Lane wrote: > > Markus Winand writes: >> However, if the conflict happens in a subquery, it doesn’t anymore: > >>WITH data (c, posix) AS ( >>values ('a' COLLATE "C", 'b' COLL

Index INCLUDE vs. Bitmap Index Scan

Hi! I think Bitmap Index Scan should take advantage of B-tree INCLUDE columns, which it doesn’t at the moment (tested on master as of yesterday). Consider this (find the setup at the bottom of this mail). CREATE INDEX idx ON tbl (a, b) INCLUDE (c); EXPLAIN (analyze, buffers) SELECT * FROM tb

Re: Index INCLUDE vs. Bitmap Index Scan

> On 27.02.2019, at 00:22, Tom Lane wrote: > > Markus Winand writes: >> I think Bitmap Index Scan should take advantage of B-tree INCLUDE columns, >> which it doesn’t at the moment (tested on master as of yesterday). > > Regular index scans don't do w

Re: Index INCLUDE vs. Bitmap Index Scan

> On 27.02.2019, at 02:00, Justin Pryzby wrote: > > On Tue, Feb 26, 2019 at 09:07:01PM +0100, Markus Winand wrote: >> CREATE INDEX idx ON tbl (a, b, c); >> Bitmap Heap Scan on tbl (cost=4.14..8.16 rows=1 width=7616) (actual >> time=0.021..0.021 rows=1 loops=1)

Re: XML/XPath issues: text/CDATA in XMLTABLE, XPath evaluated with wrong context

Hi and thanks for your efforts. > On 2018-04-26, at 21:18 , Alvaro Herrera wrote: > > Hello Markus, > > Markus Winand wrote: > >> * Patch 0001: Accept TEXT and CDATA nodes in XMLTABLE’s column_expression. >> >>> Column_expressions that match TEXT or C