Different returned datatype of a CASE statement in different query.

[DerZc]

What happens?

Consider the following program:

CREATE TABLE t0(c0 DATE);
INSERT INTO t0(c0) VALUES ((DATE '1969-12-10'));

SELECT t0.c0 FROM t0 WHERE (((CASE (1) WHEN (2) THEN (TIMESTAMP '1969-12-21 06:13:06') ELSE t0.c0 END )) NOT LIKE((DATE '1969-12-10')));

SELECT t0.c0 FROM t0 WHERE (((CASE (1) WHEN (2) THEN (TIMESTAMP '1969-12-21 06:13:06') ELSE t0.c0 END )) NOT LIKE(SELECT t0.c0 FROM t0));

DuckDB produces the following results:

┌────────┐
│   c0   │
│  date  │
├────────┤
│ 0 rows │
└────────┘
┌────────────┐
│     c0     │
│    date    │
├────────────┤
│ 1969-12-10 │
└────────────┘

As there is only one row in t0, so the second query is equivalent to the first one. But they have different results.

Then I tried this query SELECT CASE (1) WHEN (2) THEN (TIMESTAMP '1969-12-21 06:13:06') ELSE t0.c0 END FROM t0; and got this result:

┌─────────────────────────────────────────────────────────────────────────────────────┐
│ CASE  WHEN ((1 = 2)) THEN (CAST('1969-12-21 06:13:06' AS TIMESTAMP)) ELSE t0.c0 END │
│                                      timestamp                                      │
├─────────────────────────────────────────────────────────────────────────────────────┤
│ 1969-12-10 00:00:00                                                                 │
└─────────────────────────────────────────────────────────────────────────────────────┘

means the DATA value was cast to a TIMESTAMP value.

And for this query SELECT t0.c0 FROM t0 WHERE (((CASE (1) WHEN (2) THEN (TIMESTAMP '1969-12-21 06:13:06') ELSE t0.c0 END )) == ((DATE '1969-12-10')));, I got the results:

┌────────────┐
│     c0     │
│    date    │
├────────────┤
│ 1969-12-10 │
└────────────┘

Shoule this CASE statement produce a consistent result?

To Reproduce

I build DuckDB from source code (d8b4c83), and run this program with CLI.

OS:

ubuntu 22.04

DuckDB Version:

commit version d8b4c83

DuckDB Client:

CLI

Full Name:

Chi Zhang

Affiliation:

Nanjing University, National University of Singapore

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

[Tishj]

Hmm it looks like the CASE statement in the second one doesn't get optimized properly.
Here is a manually reduced version:

SELECT t0.c0 FROM t0 WHERE (
	(
		t0.c0
	) NOT LIKE(
		SELECT t0.c0 FROM t0
	)
);

Which does produce the right result

┌────────┐
│   c0   │
│  date  │
├────────┤
│ 0 rows │
└────────┘

[Tishj]

Manually reduced version physical plan:

┌─────────────────────────────┐
│┌───────────────────────────┐│
││       Physical Plan       ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐                             
│         PROJECTION        │                             
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             
│             c0            │                             
└─────────────┬─────────────┘                                                          
┌─────────────┴─────────────┐                             
│     BLOCKWISE_NL_JOIN     │                             
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             
│           INNER           ├──────────────┐              
│  (CAST(c0 AS VARCHAR) !~~ │              │              
│ CAST(SUBQUERY AS VARCHAR))│              │              
└─────────────┬─────────────┘              │                                           
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│          SEQ_SCAN         ││    UNGROUPED_AGGREGATE    │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             t0            ││         first(#0)         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││                           │
│             c0            ││                           │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││                           │
│           EC: 0           ││                           │
└───────────────────────────┘└─────────────┬─────────────┘                             
                             ┌─────────────┴─────────────┐
                             │         PROJECTION        │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │             #0            │
                             └─────────────┬─────────────┘                             
                             ┌─────────────┴─────────────┐
                             │           LIMIT           │
                             └─────────────┬─────────────┘                             
                             ┌─────────────┴─────────────┐
                             │          SEQ_SCAN         │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │             t0            │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │             c0            │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │           EC: 0           │
                             └───────────────────────────┘

Original (second query) physical plan:

┌─────────────────────────────┐
│┌───────────────────────────┐│
││       Physical Plan       ││
│└───────────────────────────┘│
└─────────────────────────────┘
┌───────────────────────────┐                             
│         PROJECTION        │                             
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             
│             c0            │                             
└─────────────┬─────────────┘                                                          
┌─────────────┴─────────────┐                             
│     BLOCKWISE_NL_JOIN     │                             
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             
│           INNER           │                             
│(CAST(CAST(c0 AS TIMESTAMP)├──────────────┐              
│    AS VARCHAR) !~~ CAST   │              │              
│   (SUBQUERY AS VARCHAR))  │              │              
└─────────────┬─────────────┘              │                                           
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│          SEQ_SCAN         ││    UNGROUPED_AGGREGATE    │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│             t0            ││         first(#0)         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││                           │
│             c0            ││                           │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││                           │
│           EC: 0           ││                           │
└───────────────────────────┘└─────────────┬─────────────┘                             
                             ┌─────────────┴─────────────┐
                             │         PROJECTION        │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │             #0            │
                             └─────────────┬─────────────┘                             
                             ┌─────────────┴─────────────┐
                             │           LIMIT           │
                             └─────────────┬─────────────┘                             
                             ┌─────────────┴─────────────┐
                             │          SEQ_SCAN         │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │             t0            │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │             c0            │
                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
                             │           EC: 0           │
                             └───────────────────────────┘

[DerZc]

I got it! Thank you very much for such a quick reply.

[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.