Skip to content

[Improvement]: Support more complex types in Query params #1107

Description

@kavirajk

Currently when trying to use ch.Parameters function with complex types like Array(String fails. Because query parameters are correctly encoded for these types (say in-correct escaping quotes in following example)

Example that breaks

ackage main

import (
	"context"
	"fmt"
	"strings"

	"github.com/ClickHouse/ch-go"
	"github.com/ClickHouse/ch-go/proto"
)

func main() {
	ctx := context.Background()
	c, err := ch.Dial(ctx, ch.Options{Address: "localhost:9000"})
	if err != nil {
		panic(err)
	}
	var (
		numbers int
		data    proto.ColUInt64
	)
	arr := new(proto.ColStr).Array()

	params := ch.Parameters(map[string]any{
		"array":    "['a', 'b', 'c', 'hello', 'testing']", 
		"column":   "number",
		"database": "system",
		"table":    "numbers",
	})

	if err := c.Do(ctx, ch.Query{
		// Body: "SELECT number FROM system.numbers LIMIT 500000000",
		Body: "SELECT {column:Identifier} v, {array:Array(String)} a FROM {database:Identifier}.{table:Identifier} LIMIT 1 OFFSET 100",
		Result: proto.Results{
			{Name: "v", Data: &data},
			{Name: "a", Data: arr},
		},
		// OnResult will be called on next received data block.
		OnResult: func(ctx context.Context, b proto.Block) error {
			numbers += len(data)
			return nil
		},
		Parameters: params,
	}); err != nil {
		panic(err)
	}
	fmt.Println("numbers:", numbers, "data", data)
	fmt.Println("arr")
	for i := 0; i < arr.Rows(); i++ {
		fmt.Printf("%v,", arr.Row(i))
	}
}

This would fail with error

$ go run query_params_chgo.go
panic: handle packet: CANNOT_PARSE_INPUT_ASSERTION_FAILED (27): DB::Exception: Cannot parse input: expected ']' at end of stream: value [ cannot be parsed as Array(String) for query parameter 'array'

goroutine 1 [running]:
main.main()
        /home/kavi/src/play-go2/query_params_chgo.go:45 +0x765
exit status 2

But manually escaping it before using ch.Parameters works

package main

import (
	"context"
	"fmt"
	"strings"

	"github.com/ClickHouse/ch-go"
	"github.com/ClickHouse/ch-go/proto"
)

func main() {
	ctx := context.Background()
	c, err := ch.Dial(ctx, ch.Options{Address: "localhost:9000"})
	if err != nil {
		panic(err)
	}
	var (
		numbers int
		data    proto.ColUInt64
	)
	arr := new(proto.ColStr).Array()

	params := ch.Parameters(map[string]any{
		"array":    strings.ReplaceAll("['a', 'b', 'c', 'hello', 'testing']", "'", "\\'"),
		"column":   "number",
		"database": "system",
		"table":    "numbers",
	})

	if err := c.Do(ctx, ch.Query{
		// Body: "SELECT number FROM system.numbers LIMIT 500000000",
		Body: "SELECT {column:Identifier} v, {array:Array(String)} a FROM {database:Identifier}.{table:Identifier} LIMIT 1 OFFSET 100",
		Result: proto.Results{
			{Name: "v", Data: &data},
			{Name: "a", Data: arr},
		},
		// OnResult will be called on next received data block.
		OnResult: func(ctx context.Context, b proto.Block) error {
			numbers += len(data)
			return nil
		},
		Parameters: params,
	}); err != nil {
		panic(err)
	}
	fmt.Println("numbers:", numbers, "data", data)
	fmt.Println("arr")
	for i := 0; i < arr.Rows(); i++ {
		fmt.Printf("%v,", arr.Row(i))
	}
}

This one works

$ go run query_params_chgo.go
numbers: 1 data [100]
arr
[a b c hello testing],⏎
$

The expectation is ch-go handle right encoding for different types.

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or request

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions