Incorrect results of a query with a filter on DateTime64 column

[vitalyisaev2]

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

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

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

[vitalyisaev2]

By the way, if you play with the timeValue, you'll face very strange behavior. In fact, we physically cannot construct a time.Time instance in Go that will satisfy the filter expression datetimeValue = ?.

The value 1988-11-20 12:55:28.999999999 +0000 UTC seems to be smaller (😫) than a value 1988-11-20T12:55:28.123456Z (stored in the database). At the same time, the next possible value 1988-11-20 12:55:29 +0000 UTC is already greater than 1988-11-20T12:55:28.123456Z.

Consider this code:

	// 4. Query the table with a filtering expression
	timeValues := []time.Time{
		time.Date(1988, 11, 20, 12, 55, 28, 123456000, time.UTC).Add(time.Nanosecond * 876543999),
		time.Date(1988, 11, 20, 12, 55, 28, 123456000, time.UTC).Add(time.Nanosecond * 876544000),
	}

	for _, timeValue := range timeValues {
		fmt.Printf("\n Checking time value: %v\n", timeValue)

		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 after query with a filter `datetimeValue > ?`:")
		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))
		}

		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 after query with a filter `datetimeValue < ?`:")
		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)
		}
	}

This results in:

Table dropped successfully.
Table created successfully.
Data inserted successfully.

 Checking time value: 1988-11-20 12:55:28.999999999 +0000 UTC
Rows filtered and fetched after query with a filter `datetimeValue > ?`:
ID: 1, DateTime: 1988-11-20T12:55:28.123456Z
Rows filtered and fetched after query with a filter `datetimeValue < ?`:

 Checking time value: 1988-11-20 12:55:29 +0000 UTC
Rows filtered and fetched after query with a filter `datetimeValue > ?`:
Rows filtered and fetched after query with a filter `datetimeValue < ?`:
ID: 1, DateTime: 1988-11-20T12:55:28.123456Z