Ambiguous Column References Should Error

[danfrankj]

What happens?

When referencing an ambiguous column, it's safer to error than to silently rename.

Did a small survey:
MySQL & PostgreSQL error
sqlite does not

If default sqlite compat is desired then we'd love to be able to set a flag to error on these cases e.g.

/set DISALLLOW_AMBIGUOUS_COLUMNS 

To Reproduce

This returns 1 and should instead error

   SELECT foo FROM (
     SELECT *, 2 AS foo
     FROM (
       SELECT 1 AS foo
     )
   )

OS:

iOS

DuckDB Version:

0.7.0

DuckDB Client:

Python

Full Name:

Daniel Frank

Affiliation:

Watershed

Have you tried this on the latest master branch?

• I agree

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• I agree

[Mytherin]

I'm not opposed to adding a strict mode to disable column renaming (and perhaps a few other things). It's worth noting that the reason we opted for this behavior is because it is very easy to get duplicate columns in the event of joins, which then prevents you from running statements such as CREATE TABLE on them. For example, this errors in Postgres and MySQL as well for the same reason:

CREATE TABLE t1(id INT);
CREATE TABLE t2(id INT);
CREATE TABLE t3 as SELECT * FROM t1 JOIN t2 ON (t1.id=t2.id);
-- ERROR:  column "id" specified more than once

[danfrankj]

We'd be very happy with a strict mode! And I suspect the number of bugs that could be found earlier with a strict mode would be very non-trivial.

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[github-actions]

This issue was closed because it has been stale for 30 days with no activity.

[danfrankj]

I'd still like to see this feature :) !

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[github-actions]

This issue was closed because it has been stale for 30 days with no activity.