[YSQL] CTE not included in final select statement being skipped

[albert-chang0]

Jira Link: DB-11306

Description

The sample_sql.txt attachment contains a schema, types, functions and sample data for the table. It's quite large since I'm not sure how much I could pare down the example while still exhibiting the same behavior.

This was tested against Yugabyte 2.20.1.3-b3.

The two functions in question are essentially object_close() and object_close_no_dangling_cte(). Essentially there are three tables, there's the object table which is just a table of objects, subobject table containing transient parts of an object, object_attr which contains attributes of an object and object_subobject_attr which similar to suboject is transient attributes of parts.

What the object_close() function does is essentially takes all the transient data that are to be included (some can be excluded) and includes the subobject information into the object table and merges the subobject attributes to the object attributes.

The CTE that is being skipped in object_close() is the one with the attrs alias. The same alias is included in the final select statement with a join in the object_close_no_dangling_cte() function. That is after executing object_close(), the object_attr table does not contain the attributes from the transient object_subobject_attr table, but it does when executing the object_close_no_dangling_cte() function. The question is why is this being skipped? Originally I thought all dangling CTEs were being skipped, but another function, object_subobject_close() contains a dangling CTE that is correctly being executed.

There was another issue I was running into, but can't seem to reproduce anymore, where a transaction that was being rolled back after calling either object_close() and object_close_no_dangling_cte() where the changes caused by object_subobject_close() function was being partially committed with the snapshot isolation level. I can't seem to reproduce what I was seeing here though, but thought I'd mention in.

Issue Type

kind/question

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.

[albert-chang0]

I realized I never included how to use the functions, so they're essentially used like so:

select * from object_close('obj-1712931449322aedrhpjfan'::varchar, '1f2ae97ebfa1fa9cdd936822a9ee3bbd', '{"(5796,\"1f23592a859183d4757fdbd5bb07c16c\")","(5797,\"47d26faa2f5188b76acd8f5d1e362ed4\")","(5798,\"2656c1dabbb6df0a943f4a3ff29598ee\")","(5799,\"cef1aca10963e18c65e377a78f91c06c\")","(5800,\"09775a7be75dceec8f38124d734a4b0f\")","(5801,\"d87a53580c77ad2a7eb6ab95bef6663b\")","(5802,\"cb426c59f285b8104778193bcd554ab0\")","(5803,\"4c3533d77aa8244474b4cbe06085ddf3\")"}'::object_subobject_wants[]);

Substitute object_close() with object_close_no_dangling_cte() for the other behavior.

[ddorian]

I just tried in 2.21. I get same error on both queries:

t1=# select * from object_close('obj-1712931449322aedrhpjfan'::varchar, '1f2ae97ebfa1fa9cdd936822a9ee3bbd', '{"(5796,\"1f23592a859183d4757fdbd5bb07c16c\")","(5797,\"47d26faa2f5188b76acd8f5d1e362ed4\")","(5
798,\"2656c1dabbb6df0a943f4a3ff29598ee\")","(5799,\"cef1aca10963e18c65e377a78f91c06c\")","(5800,\"09775a7be75dceec8f38124d734a4b0f\")","(5801,\"d87a53580c77ad2a7eb6ab95bef6663b\")","(5802,\"cb426c59f285b81
04778193bcd554ab0\")","(5803,\"4c3533d77aa8244474b4cbe06085ddf3\")"}'::object_subobject_wants[]);
ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned row contains 1 attribute, but query expects 5.
CONTEXT:  SQL function "object_close" statement 1

t1=# select * from object_close_no_dangling_cte('obj-1712931449322aedrhpjfan'::varchar, '1f2ae97ebfa1fa9cdd936822a9ee3bbd', '{"(5796,\"1f23592a859183d4757fdbd5bb07c16c\")","(5797,\"47d26faa2f5188b76acd8f5d
1e362ed4\")","(5798,\"2656c1dabbb6df0a943f4a3ff29598ee\")","(5799,\"cef1aca10963e18c65e377a78f91c06c\")","(5800,\"09775a7be75dceec8f38124d734a4b0f\")","(5801,\"d87a53580c77ad2a7eb6ab95bef6663b\")","(5802,\
"cb426c59f285b8104778193bcd554ab0\")","(5803,\"4c3533d77aa8244474b4cbe06085ddf3\")"}'::object_subobject_wants[]);
ERROR:  function return row and query-specified return row do not match
DETAIL:  Returned row contains 1 attribute, but query expects 5.
CONTEXT:  SQL function "object_close_no_dangling_cte" statement 1

What exact error(s) or output(s) are you getting?

[albert-chang0]

Specifically these are the discrepancies I'm seeing:

yugabyte=# select * from object_close('obj-1712931449322aedrhpjfan'::varchar, '1f2ae97ebfa1fa9cdd936822a9ee3bbd','{"(5796,\"1f23592a859183d4757fdbd5bb07c16c\")","(5797,\"47d26faa2f5188b76acd8f5d1e362ed4\")","(5798,\"2656c1dabbb6df0a943f4a3ff29598ee\")","(5799,\"cef1aca10963e18c65e377a78f91c06c\")","(5800,\"09775a7be75dceec8f38124d734a4b0f\")","(5801,\"d87a53580c77ad2a7eb6ab95bef6663b\")","(5802,\"cb426c59f285b8104778193bcd554ab0\")","(5803,\"4c3533d77aa8244474b4cbe06085d
df3\")"}'::object_subobject_wants[]);
            name             | subobject_seq | size_bytes |                id                | state
-----------------------------+---------------+------------+----------------------------------+--------
 obj-1712931449322aedrhpjfan |             1 |         28 | 1f23592a859183d4757fdbd5bb07c16c | ACTIVE
 obj-1712931449322aedrhpjfan |             2 |         23 | 47d26faa2f5188b76acd8f5d1e362ed4 | ACTIVE
 obj-1712931449322aedrhpjfan |             3 |         18 | cef1aca10963e18c65e377a78f91c06c | ACTIVE
 obj-1712931449322aedrhpjfan |             4 |         31 | 09775a7be75dceec8f38124d734a4b0f | ACTIVE
 obj-1712931449322aedrhpjfan |             5 |         29 | d87a53580c77ad2a7eb6ab95bef6663b | ACTIVE
 obj-1712931449322aedrhpjfan |             6 |         20 | cb426c59f285b8104778193bcd554ab0 | ACTIVE
 obj-1712931449322aedrhpjfan |             7 |         19 | 4c3533d77aa8244474b4cbe06085ddf3 | ACTIVE
(7 rows)

yugabyte=# select * from object_subobject_attr;
            name             | subobject_seq | attr_sequence_number | state  | size_bytes
-----------------------------+---------------+----------------------+--------+------------
 obj-1712931449322aedrhpjfan |          5795 |                    0 | ACTIVE |         18
 obj-1712931449322aedrhpjfan |          5796 |                    0 | ACTIVE |         28
 obj-1712931449322aedrhpjfan |          5797 |                    0 | ACTIVE |         23
 obj-1712931449322aedrhpjfan |          5798 |                    0 | ACTIVE |          5
 obj-1712931449322aedrhpjfan |          5799 |                    0 | ACTIVE |         18
 obj-1712931449322aedrhpjfan |          5800 |                    0 | ACTIVE |         31
 obj-1712931449322aedrhpjfan |          5801 |                    0 | ACTIVE |         29
 obj-1712931449322aedrhpjfan |          5802 |                    0 | ACTIVE |         20
 obj-1712931449322aedrhpjfan |          5803 |                    0 | ACTIVE |         19
(9 rows)

yugabyte=# select * from object_attr;
 name | attr_sequence_number | state | subobject_seq | size_bytes
------+----------------------+-------+---------------+------------
(0 rows)

Versus:

yugabyte=# select * from object_close_no_dangling_cte('obj-1712931449322aedrhpjfan'::varchar, '1f2ae97ebfa1fa9cdd936822a9ee3bbd', {"(5796,\"1f23592a859183d4757fdbd5bb07c16c\")","(5797,\"47d26faa2f5188b76acd8f5d1e362ed4\")","(5798,\"2656c1dabbb6df0a943f4a3ff29598ee\")","(5799,\"cef1aca10963e18c65e377a78f91c06c\")","(5800,\"09775a7be75dceec8f38124d734a4b0f\")","(5801,\"d87a53580c77ad2a7eb6ab95bef6663b\")","(5802,\"cb426c59f285b8104778193bcd554ab0\")","(5803,\"4c3533d77aa8244474b4cbe06085ddf3\")"}'::object_subobject_wants[]);
            name             | subobject_seq | size_bytes |                id                | state
-----------------------------+---------------+------------+----------------------------------+--------
 obj-1712931449322aedrhpjfan |             1 |         28 | 1f23592a859183d4757fdbd5bb07c16c | ACTIVE
 obj-1712931449322aedrhpjfan |             2 |         23 | 47d26faa2f5188b76acd8f5d1e362ed4 | ACTIVE
 obj-1712931449322aedrhpjfan |             3 |          5 | 2656c1dabbb6df0a943f4a3ff29598ee | ACTIVE
 obj-1712931449322aedrhpjfan |             4 |         18 | cef1aca10963e18c65e377a78f91c06c | ACTIVE
 obj-1712931449322aedrhpjfan |             5 |         31 | 09775a7be75dceec8f38124d734a4b0f | ACTIVE
 obj-1712931449322aedrhpjfan |             6 |         29 | d87a53580c77ad2a7eb6ab95bef6663b | ACTIVE
 obj-1712931449322aedrhpjfan |             7 |         20 | cb426c59f285b8104778193bcd554ab0 | ACTIVE
 obj-1712931449322aedrhpjfan |             8 |         19 | 4c3533d77aa8244474b4cbe06085ddf3 | ACTIVE
(8 rows)

yugabyte=# select * from object_subobject_attr;
            name             | subobject_seq | attr_sequence_number | state  | size_bytes
-----------------------------+---------------+----------------------+--------+------------
 obj-1712931449322aedrhpjfan |          5795 |                    0 | ACTIVE |         18
(1 row)

yugabyte=# select * from object_attr;
            name             | attr_sequence_number | state  | subobject_seq | size_bytes
-----------------------------+----------------------+--------+---------------+------------
 obj-1712931449322aedrhpjfan |                    0 | ACTIVE |             1 |         28
 obj-1712931449322aedrhpjfan |                    0 | ACTIVE |             2 |         23
 obj-1712931449322aedrhpjfan |                    0 | ACTIVE |             3 |          5
 obj-1712931449322aedrhpjfan |                    0 | ACTIVE |             4 |         18
 obj-1712931449322aedrhpjfan |                    0 | ACTIVE |             5 |         31
 obj-1712931449322aedrhpjfan |                    0 | ACTIVE |             6 |         29
 obj-1712931449322aedrhpjfan |                    0 | ACTIVE |             7 |         20
 obj-1712931449322aedrhpjfan |                    0 | ACTIVE |             8 |         19
(8 rows)

[ddorian]

@albert-chang0 my issue is that I get the errors #22403 (comment) when trying https://github.com/yugabyte/yugabyte-db/files/15319273/sample_sql.txt file.

Can you try again yourself in a new db or send the new data to reproduce?