A little problem that I don't know how to solve

Community
1

Hi, I have a little problem that I don’t know how to solve.
A query that I will have to put into a union, but first I have to solve it.
Let me give you an example.
CREATE TABLE ‘Log_80003’ (
dateStart TIMESTAMP,
dateEnd TIMESTAMP,
c_id INT
) timestamp(dateStart) PARTITION BY DAY WAL;

the table is empty.

this query:
SELECT
10 as cn,
‘2025-01-01’::TIMESTAMP as di;

result:
cn=10(int), di=2025-01-01T00:00:00.000000Z(timestamp)

this query:
SELECT
10 as cn,
first(dateStart) as de from Log_80003 where c_id=0;

result:
cn=10(int), de=null(timestamp)

this query returns me nothing.
SELECT
10 as cn,
‘2025-01-01’::TIMESTAMP as di,
first(dateStart) as de from Log_80003 where c_id=0;

I was expecting:
cn=10(int), di=2025-01-01T00:00:00.000000Z(timestamp), de=null(timestamp)

Where am I wrong?
Thanks.

2

Hey, we will need to look into this a bit. From your description, it could be a bug. That being said, if there is no data in the dataset, then no row would be the appropriate output.

Bear with!

3

Hi, this is right, but in this case I expected the same empty result in the two queries with ‘first(dateStart) as de from Log_80003 where c_id=0;’

4

Okay, here are the plans:

Query 1:

VirtualRecord
  functions: [10,de]
    Async JIT Group By workers: 16
      values: [first(dateStart)]
      filter: c_id=0
        PageFrame
            Row forward scan
            Frame forward scan on: Log_80003

Query 2:

VirtualRecord
  functions: [10,di,de]
    Async JIT Group By workers: 16
      keys: [di]
      values: [first(dateStart)]
      filter: c_id=0
        PageFrame
            Row forward scan
            Frame forward scan on: Log_80003

The differenceis that your second query is ‘keyed’. This means that you group by di, running your values (i.e. the first aggregate) separately for each key.

But there are no rows, therefore no groups, therefore no output.

Whether this is semantically correct, I’m not sure. Let me ask the team.

5

Looks like it is intended :slight_smile:

6

I did some testing and it seems to reject the functions results.
Ex:
SELECT
10 as cn,
123 as di,
first(dateStart) as de from Log_80003 where c_id=0;

SELECT
10 as cn,
COALESCE(null, 123) as di,
first(dateStart) as de from Log_80003 where c_id=0;

SELECT
10 as cn,
4 as di,
first(dateStart) as de from Log_80003 where c_id=0;

SELECT
10 as cn,
greatest(1, 2, 3, 4) as di,
first(dateStart) as de from Log_80003 where c_id=0;

SELECT
10 as cn,
10.2 as di,
first(dateStart) as de from Log_80003 where c_id=0;

SELECT
10 as cn,
floor(10.2) as di,
first(dateStart) as de from Log_80003 where c_id=0;

SELECT
10 as cn,
ceil(10.2) as di,
first(dateStart) as de from Log_80003 where c_id=0;