SQLite Forum

bug report : adding constant to GROUP BY leads to different output
Login
Hello everyone,

Consider the following example:

```
-- SQLite version 3.35.5
CREATE TABLE t0 (c0 REAL);
INSERT INTO t0(c0) VALUES (1), (NULL), (1);
CREATE UNIQUE INDEX idx ON t0(NULL DESC);

SELECT * FROM t0 GROUP BY c0;
```
Since the two "1" is equal, the expected output should contain two lines: one is null and the other is 1.0. And yes, the output is exactly what I expect.

But when we add a constant to the GROUP BY clause, just like:

```
SELECT * FROM t0 GROUP BY c0, NULL;
```

The "GROUP BY constant" means to group all the candidate records into a group by the constant provided. It's like adding a column to the candidate records, and values this constant. So, there should still be 2 lines.

But what we get from the output contains 3 lines. It looks like that SQLite takes the two 1.0 as not equal.

If we annotate the CREATE INDEX statement, the output turns to be two lines again.

I wonder whether it's a bug.

Looking forward to your early reply!