ClickHouse / ClickHouse/clickhouse-go
Incorrect results of a query with a filter on `DateTime64` column
- Dominant language
- Go
- Stars
- 3.3k
- Forks
- 684
- Avg merge
- 2d 3h
- Merged PRs (30d)
- 14
Description
## Observed
Consider a table with a `DateTime64` column:
```
CREATE TABLE IF NOT EXISTS example_table (
id UInt32,
datetimeValue DateTime64(8, 'UTC')
) ENGINE = MergeTree()
PRIMARY KEY id;
INSERT INTO example_table (*) VALUES
(1, '1988-11-20 12:55:28.123456000');
```
I would like to filter this table by a `DateTime64` column value, but when I pass `time.Date(1988, 11, 20, 12, 55, 28, 123456000, time.UTC)` object as an argument for a query `SELECT id, datetimeValue FROM example_table WHERE datetimeValue = ?`, it returns empty result.
## Expected behaviour
In pure SQL the equivalent query works as expected:
```
SELECT
id,
datetimeValue
FROM example_table
WHERE datetimeValue = parseDateTime64BestEffort('1988-11-20T12:55:28.123456000Z', 8)
┌─id─┬────────────────datetimeValue─┐
1. │ 1 │ 1988-11-20 12:55:28.12345600 │
└────┴──────────────────────────────┘
```
## Code example
```go
package main
import (
"context"
"database/sql"
"fmt"
"log"
"time"
_ "github.com/ClickHouse/clickhouse-go/v2"
)
func main() {
// Define ClickHouse connection info
connStr := "clickhouse://admin:password@localhost:9000"
db, err := sql.Open("clickhouse", connStr)
if err != nil {
log.Fatalf("failed to open connection: %v", err)
}
defer db.Close()
// Check the connection
if err := db.Ping(); err != nil {
log.Fatalf("failed to ping database: %v", err)
}
// Create context
ctx := context.Background()
// 1. Drop previously created table
dropTableQuery := `DROP TABLE IF EXISTS example_table;`
_, err = db.ExecContext(ctx, dropTableQuery)
if err != nil {
log.Fatalf("failed to drop table: %v", err)
}
fmt.Println("Table dropped successfully.")
// 2. Create a table
createTableQuery := `
CREATE TABLE IF NOT EXISTS example_table (
id UInt32,
datetimeValue DateTime64(8, 'UTC')
) ENGINE = MergeTree()
PRIMARY KEY id;`
_, err = db.ExecContext(ctx, createTableQuery)
if err != nil {
log.Fatalf("failed to create table: %v", err)
}
fmt.Println("Table created successfully.")
// 3. Insert some data into the table
insertQuery := `
INSERT INTO example_table (*) VALUES
(1, '1988-11-20 12:55:28.123456000')
`
_, err = db.ExecContext(ctx, insertQuery)
if err != nil {
log.Fatalf("failed to insert data: %v", err)
}
fmt.Println("Data inserted successfully.")
// 4. Query the table with a filtering expression
timeValue := time.Date(1988, 11, 20, 12, 55, 28, 123456000, time.UTC)
rows, err := db.QueryContext(ctx, "SELECT id, datetimeValue FROM example_table WHERE datetimeValue = ?", timeValue)
if err != nil {
log.Fatalf("failed to execute query: %v", err)
}
defer rows.Close()
fmt.Println("Rows filtered and fetched:")
for rows.Next() {
var id uint32
var datetimeValue time.Time
if err := rows.Scan(&id, &datetimeValue); err != nil {
log.Fatalf("failed to scan row: %v", err)
}
fmt.Printf("ID: %d, DateTime: %s\n", id, datetimeValue.Format(time.RFC3339Nano))
}
if err := rows.Err(); err != nil {
log.Fatalf("rows iteration error: %v", err)
}
}
```
## Error log
```log
Table dropped successfully.
Table created successfully.
Data inserted successfully.
Rows filtered and fetched:
```
## Details
### Environment
* [ ] `clickhouse-go` version: 2.18.0, 2.30.1
* [ ] Interface: ClickHouse API / `database/sql` compatible driver: `database/sql`
* [ ] Go version: 1.22
* [ ] Operating system: Ubuntu 20.04
* [ ] ClickHouse version: 24.10.2.80
* [ ] Is it a ClickHouse Cloud? No
* [ ] ClickHouse Server non-default settings, if any:
* [ ] `CREATE TABLE` statements for tables involved: see above
* [ ] Sample data for all these tables, use [clickhouse-obfuscator] (https://github.com/ClickHouse/ClickHouse/blob/master/programs/obfuscator/Obfuscator.cpp#L42-L80) if necessary
Contributor guide
Research direction
Start with the supplied Go database/sql reproduction and inspect how the driver encodes time.Time arguments for DateTime64 comparisons. Verify the behavior against the shown table and query, then confirm the fix by rerunning the example and checking that row 1 is returned.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100