You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "schizophrenicmaniac (via GitHub)" <gi...@apache.org> on 2026/10/19 19:18:18 UTC

[PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

schizophrenicmaniac opened a new pull request, #40294:
URL: https://github.com/apache/shardingsphere/pull/40294

   Fixes #40279.
   
   Changes proposed in this pull request:
     - In `FirebirdPrepareStatementCommandExecutor#processReturnValues`, map `OLD.` and `NEW.` columns in `RETURNING` to the target table instead of looking up tables named OLD and NEW.
     - Describe a CTE column that has no physical table (for example `N` in `WITH RECURSIVE R (N) AS (SELECT 1 ... UNION ALL ...)`) from the matching projection of the CTE's anchor select. The column is matched by the CTE column list, or by the anchor select's labels when there is no list, and keeps its own label as the alias.
     - Add unit tests for a recursive CTE, a CTE without a column list, and `UPDATE ... RETURNING OLD.id, NEW.id`.
   
   ---
   
   Before submitting this PR, I confirm that:
   - [x] My code follows the [code of conduct](https://shardingsphere.apache.org/community/en/involved/conduct/code/) of this project.
   - [x] I have reviewed my changes.
   - [ ] I have (or in comment I request) added corresponding labels for the pull request.
   - [x] I have passed the required local checks: `./mvnw clean install -B -T1C -Pcheck` (run for the affected module `proxy/frontend/dialect/firebird`).
   - [ ] I have updated the relevant documentation, if applicable.
   - [x] I have added or updated unit tests for my changes, if applicable.
   - [ ] I have updated the Release Notes of the current development version, if applicable. For more details, see [Update Release Note](https://shardingsphere.apache.org/community/en/involved/contribute/contributor/).
   
   AI disclosure: drafted with Claude Code (Claude Opus 5.5); files: FirebirdPrepareStatementCommandExecutor.java, FirebirdPrepareStatementCommandExecutorTest.java.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub)" <gi...@apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6384273473

   @terrymanu Thanks, both are fixed in the latest commit.
   
   `getProjections` now always uses the outer SELECT's expanded projections, so the column count, order and labels come from the outer query. Columns that the binder turns into temporary columns of a derived table are resolved through the same origin lookup used for CTEs. It no longer needs a WITH clause and starts from the outer SELECT's FROM, so `d.n` is traced into the derived table's projection while the scalar subquery stays as its own column. I added your query as a regression test, with `'foo'` as the derived column so it also checks that the two columns keep their order and types.
   
   The two test assignments are on one line now, and the production call wraps after a comma.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub) via notifications" <no...@shardingsphere.apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6802623562

   @terrymanu One more change besides the Firebird executor: since #40456, ReturningSegmentBinder only mapped OLD/NEW for `OLD.*`/`NEW.*`, so `RETURNING OLD.id, NEW.id` failed in the binder with an unknown column error. It now maps OLD/NEW owners on column projections to the target table the same way, with a case added to ReturningSegmentBinderTest.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub)" <gi...@apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6341636655

   @terrymanu Thanks, all four are fixed in the latest commit.
   
   For the collision, the lookup now checks whether the column is one of the actual columns of a `*` in that SELECT. Those columns carry the CTE name as owner, so for them the owner is matched against the CTE name of each FROM source rather than its alias. Explicit columns are still matched against the alias or name, and the old fallback loop is gone. I added tests for both `foo_alias.*` and `foo_cte.n` with `FROM foo_cte foo_alias JOIN bar_cte foo_cte`.
   
   The two comments are removed, `isTemporaryColumnWithoutTable` now comes before `getProjectionSelect`, and `getProjections` uses the derived table's subquery context directly without the null fallback.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub)" <gi...@apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6460803699

   @terrymanu Thanks, all three are fixed in the latest commit.
   
   The label check compared the outer label to the physical column name ignoring case, so `"ID"` over `id` counted as unchanged and fell back to `id`. It now uses the column's bound info instead: any column that comes from a derived table or CTE keeps its exact outer label, and plain physical columns behave as before. I added a test for `SELECT d."ID", d.* FROM (SELECT id AS "ID" FROM foo_tbl) d` that checks both columns are described as `ID`.
   
   `getTable` has `@HighFrequencyInvocation` again, and `findSourceProjectionSegment` is declared on one line.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub)" <gi...@apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6360177493

   @terrymanu Merged master again and resolved the conflicts. The Firebird module builds with -Pcheck and the tests pass.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6307533269

   Please resolve conflicts.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub)" <gi...@apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6449916726

   @terrymanu Thanks, both are fixed in the latest commit.
   
   When a derived table renames a physical column, as in `SELECT d.n FROM (SELECT id AS n FROM foo_tbl) d`, the outer column doesn't go through the origin lookup, so it fell back to the physical name. Now, if the outer column label differs from the original column name, it's sent as the alias. I added a test that selects `d.n` and `d.*` from that derived table and checks both are described as `n`.
   
   For the cost, the shorthand-expanded column set and a label to projection map are now built once per source select and cached for the prepare request. The per-column lookup no longer rebuilds or scans the source projection list. I compared `SELECT d.* FROM (SELECT 0 AS c0, ...) d` with the same select without the derived table: the extra allocation was about 1.8, 3.4 and 6.9 MB at 640, 1280 and 2560 columns, so it grows linearly now.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub)" <gi...@apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6474875432

   @terrymanu Thanks, all three are fixed in the latest commit.
   
   The label is now taken from the projection's own identifier (its alias, or the column name) instead of rebuilding an IdentifierValue from the decoded string, so `"[n]"` stays `[n]`.
   
   The origin lookup now matches the exact label first and only falls back to a case-insensitive match when nothing matches exactly, so `d."a"` and `d."A"` resolve to their own projections and unquoted names still match as before.
   
   For the wildcard, the binder's derived-table context is keyed case-insensitively by label, so `"a"` and `"A"` are already collapsed in the outer shorthand's actual columns. For `*` or `alias.*` over a derived table without a column list, the columns are now rebuilt from the derived table's own projections with `SubqueryTableBindUtils.createSubqueryProjections`, the same way the binder creates them, so the full sequence is kept. Other shorthands still use the binder's expanded columns.
   
   I added tests for `SELECT d."[n]", d.* FROM (SELECT id AS "[n]" FROM foo_tbl) d`, `SELECT d."a", d."A"` and `SELECT *` over `(SELECT 1 AS "a", 'foo' AS "A" FROM foo_tbl) d`.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6343434611

   Please resolve the conflicts


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub) via notifications" <no...@shardingsphere.apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6938290271

   @terrymanu Thanks, fixed in the latest commit.
   
   Derived-table and CTE columns that the binder had already bound to a physical column skipped the origin lookup, so `d."a"` and `d."A"` both kept the binder's case-insensitive match on `content`. The lookup now also runs for those columns (and when following an inner derived column), so each one resolves by its exact quoted label to `id` or `content`. If nothing is found, the binder's column is used as before. I added your query as a test, checking INTEGER/BLOB and the labels `a` and `A`.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub)" <gi...@apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6321187992

   @terrymanu Merged master and resolved the conflicts. The describe lookup now goes through the new `getTable` from #40300, and the Firebird tests pass.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub) via notifications" <no...@shardingsphere.apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6594588875

   @terrymanu Thanks, all four are fixed in the latest commit.
   
   With a datasource hint the statement isn't bound, so `d.id` kept `d` as its table and failed. The origin lookup now runs for any column without a bound original table, not only temporary ones. If the inner projection can't be traced any further, it is used as is, so `id` resolves against `foo_tbl` like it did before.
   
   Names are now compared the way Firebird stores them: unquoted names are upper-cased and quoted names are kept exact, on both the source and the reference side. So `a` and `A` both resolve to `"A"` in `c("a","A")`, and `"a"` still resolves to `"a"`. The case-insensitive match is only the last fallback.
   
   For `*` and `alias.*` over a CTE, the columns are now rebuilt from the CTE's column list and anchor select, the same way as for derived tables, so `"a"` and `"A"` are both kept. An unqualified `*` over a join is expanded source by source: CTEs and derived tables are rebuilt, and physical tables keep the binder's columns. Joins with USING or NATURAL still use the binder's list, because it reorders the columns there.
   
   The `Map.Entry` variable is now `entry`. I added tests for `SELECT d.id` with a datasource hint, `SELECT d.a` over `"a"`/`"A"`, `SELECT a, A FROM c`, `SELECT c.* FROM c`, and `SELECT * FROM c JOIN foo_tbl ON 1 = 1`.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub)" <gi...@apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6282784920

   @terrymanu Thanks for the detailed review. All five points are addressed in the latest commit.
   
   The CTE is now found from the actual FROM source. The column owner is matched against the table alias or name in FROM (joins included), and only that table's CTE definition is used. So `SELECT q.n FROM foo_cte q` works, and the case where `a` and `b` both have `n` now resolves to `b`. The CTE column list is matched against the anchor projections after `*` is expanded, so `c(a, b, n) AS (SELECT foo_tbl.*, 'foo' FROM foo_tbl)` picks the literal for `n`. If the matched projection is itself a column of another expression-backed CTE, the lookup follows the anchor select's FROM into the CTEs declared before it, until it reaches an expression or a physical column. The outer column label is kept as the alias.
   
   The new lookup is marked with `@HighFrequencyInvocation` and uses plain loops instead of streams, and the new `columnAlias` parameter is now `@Nullable`.
   
   I added tests for the same column name in two CTEs, an aliased CTE reference, `foo_tbl.*` inside a CTE, and the chained `bar_cte (m) AS (SELECT n FROM foo_cte)` case, which also checks that the `m` alias is kept.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub)" <gi...@apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6338703748

   @terrymanu Thanks, all three are fixed in the latest commit.
   
   For `SELECT a.* FROM foo_cte a`, the expanded columns carry the CTE name as owner rather than the alias. The lookup still matches the owner against the alias or name first. Only when nothing matches does it fall back to the table name of an aliased source, so explicit aliases keep priority.
   
   Derived tables are now followed like CTEs. The column is matched in the subquery's projections and traced further through its FROM with the same CTE scope, and the outer label is kept. For the outer case, `getProjections` used to merge the projections of every subquery context, CTE bodies included, so `SELECT d.n FROM (SELECT n FROM foo_cte) d` described two columns. It now only takes the context of the derived table in FROM, and the CTE lookup resolves against that subquery's SELECT. I added tests for `foo_alias.*` on a CTE, a derived table inside a CTE, and a derived table selecting from a CTE.
   
   The `flatMap` lambdas no longer use `optional`.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [PR] Fix Firebird prepare describe for recursive CTE columns and RETURNING OLD/NEW columns [shardingsphere]

Posted by "schizophrenicmaniac (via GitHub) via notifications" <no...@shardingsphere.apache.org>.
schizophrenicmaniac commented on PR #40294:
URL: https://github.com/apache/shardingsphere/pull/40294#issuecomment-6772133241

   @terrymanu Thanks, all six are fixed in the latest commit, along with the derived-column label case from your follow-up review.
   
   Source aliases are now compared the way Firebird stores names, both when matching `alias.*` to a source and in the origin lookup, so `"A".*` picks the `"A"` derived table.
   
   Wildcards are now expanded from each source's own projections, recursively, so a `*` inside a CTE over another CTE keeps both `"a"` and `"A"`, and the origin lookup indexes that full list. USING and NATURAL joins go through the same expansion: the joined columns come first, taken from the left source (or the right one for a RIGHT JOIN, like the binder does), then the remaining columns of each side. Your USING and NATURAL queries now describe four columns.
   
   A derived table's own WITH is now added to the CTE scope before the lookup, and its definitions shadow outer ones with the same name.
   
   The source list, the CTE scope and the expanded projections are built once per select and cached for the prepare request. `expandShorthandProjections` is now marked with `@HighFrequencyInvocation`.
   
   For the label, when an outer derived column has no alias, is unquoted and has the same name as the physical column it comes from, no alias is sent, so the label comes from the metadata like a direct query (`ID`). Renamed and quoted columns still keep their exact outer label.
   
   I added tests for each of these, including one against a table whose metadata column is `ID`.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org