Analyzer: EXCEPT clause ignores unused projections in subqueries.

[l1t1]

Please make sure that the version you're using is still supported (you can find the list here).

You have to provide the following information whenever possible.

Company or project name

study

Describe what's wrong

count() return 0 when the result has some rows when except is used.

A link to reproducer in https://fiddle.clickhouse.com/.
https://fiddle.clickhouse.com/9f186eb5-b8a3-460d-9bbb-7424275bc16f
Does it reproduce on the most recent release?

The list of releases
yes, in ClickHouse local version 24.12.1.1417 (official build)
Enable crash reporting

Change "enabled" to true in "send_crash_reports" section in config.xml:

<send_crash_reports>
        <!-- Changing <enabled> to true allows sending crash reports to -->
        <!-- the ClickHouse core developers team via Sentry https://sentry.io -->
        <enabled>false</enabled>

How to reproduce

• Which ClickHouse server version to use 24.12.1.1417
• Which interface to use, if it matters
• Non-default settings, if any
• CREATE TABLE statements for all tables involved
• Sample data for all these tables, use clickhouse-obfuscator if necessary
• Queries to run that lead to an unexpected result
  select count(a)from(select 1 a,2 b union all select 1,1 except select 1,2);
  Expected behavior

count() return 1.

Error message and/or stacktrace

If applicable, add screenshots to help explain your problem.

Additional context

Add any other context about the problem here.

[Algunenano]

Only with the analyzer. It seems the projection of the subquery is broken:

SELECT *
FROM
(
    SELECT
        1 AS a,
        2 AS b
    UNION ALL
    SELECT
        1,
        1
    EXCEPT
    SELECT
        1,
        2
)

Query id: 1200a288-5b77-4579-9700-1713a32fa62e

   ┌─a─┬─b─┐
1. │ 1 │ 1 │
   └───┴───┘

1 row in set. Elapsed: 0.001 sec. 

:) select a from(select 1 a,2 b union all select 1,1 except select 1,2);

SELECT a
FROM
(
    SELECT
        1 AS a,
        2 AS b
    UNION ALL
    SELECT
        1,
        1
    EXCEPT
    SELECT
        1,
        2
)

Query id: 0cc50f7d-1f48-4539-9424-4bff68c0906c

Ok.

0 rows in set. Elapsed: 0.001 sec. 

[novikd]

It's likely related to RemoveUnusedProjectionColumnsPass. We can't remove unused projections in the presence of EXCEPT.

TBH, I think I've already seen a similar issue, it's probably a duplicate. Need to check it.

[novikd]

It's a duplicate of #66465