On Jun 28, 2023, 17:26 +0800, Julien Rouhaud <rjuju123@gmail.com>, wrote:
On Wed, Jun 28, 2023 at 05:17:14PM +0800, Julien Rouhaud wrote:
Table t1 and t2 both has 2 columns: c1, c2, when CTE join select *, the result target list seems to lost one’s column c1. But it looks good when select cte1.* and t1.* explicitly .
Is it a bug?
This is working as intended. When using a USING clause you "merge" both columns so the final target list only contain one version of the merged columns, which doesn't happen if you use e.g. ON instead. I'm assuming that what the SQL standard says, but I don't have a copy to confirm.
I forgot to mention that this is actually documented:
Furthermore, the output of JOIN USING suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While JOIN ON produces all columns from T1 followed by all columns from T2, JOIN USING produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from T1, followed by any remaining columns from T2.
