value out of range - insert 0x... into binary

[milahu]

i cannot insert *large* binary values, encoded as hex numbers

which dolt 
# /nix/store/8v2qqrzj27g9xxginzvrc94b9ccfi96v-dolt-0.32.1/bin/dolt

dolt init

dolt sql -q "create table releases (
  id int unsigned NOT NULL AUTO_INCREMENT,
  package_id int NOT NULL,
  version varchar(255) NOT NULL,
  sha256 binary(32),
  primary key (id)
)"

dolt sql -q "create index releases on releases (package_id, version)"

dolt sql -q "insert into releases (package_id, version, sha256) values
(1, '1.2.3', 0x148aa875c3cdb9af8919493926a3d7c6862fec7f330152f400c0aecb4467508a),
(1, '1.2.4', 0xed6e67f2e0f177cbe19c0dc4dd9a66c73e631cab9757f3d68f73369a3a824441)
"
# strconv.ParseUint: parsing "148aa875c3cdb9af8919493926a3d7c6862fec7f330152f400c0aecb4467508a": value out of range

dolt sql -q "select * from releases where package_id = 1"

the sha256 hashes were generated with

date | sha256sum -
# 04b019492e6f44fce27de2c649cef21699cce604d2ab57c13aa5e30cd620f8e7  -

32 bytes

printf 148aa875c3cdb9af8919493926a3d7c6862fec7f330152f400c0aecb4467508a | xxd -r -p | dd >/dev/null
# 32 bytes copied

also tried to insert base64 string

dolt sql -q "insert into releases (package_id, version, sha256) values
(1, '1.2.3', 'nPRmDyVPoRTJj00x9yS4yt5k0jr7nfrisTv1+33gKgg=')
"
# string is too large for column

maximum value for binary(32) is 0xffffffffffffffff

# python
format(0xffffffffffffffff, 'b') == '1111111111111111111111111111111111111111111111111111111111111111'
len(format(0xffffffffffffffff, 'b')) == 64 # 64 bit = 8 byte

first out-of-range vaue is 0x10000000000000000

growing the binary(32) to binary(128) or blob does not help
so this is a bug in the query parser

workaround

split the 256 bit value across 4 x 64 bit fields = BIGINT UNSIGNED type = uint64

trace

the error is thrown in this call to se.Query

dolt/go/cmd/dolt/commands/sql.go

Lines 1032 to 1033 in 71ebab5

	default:
	return se.Query(ctx, query)

leads to strconv.ParseUint in go-mysql-server ...?

func convertInt(value string, base int) (sql.Expression, error) {
        // ...
        ui64, err := strconv.ParseUint(value, base, 64)
        if err != nil {
                return nil, err
        }

convertInt is called by

	case sqlparser.HexNum:
		v := strings.ToLower(string(v.Val))
		if strings.HasPrefix(v, "0x") {
			v = v[2:]
		} else if strings.HasPrefix(v, "x") {
			v = strings.Trim(v[1:], "'")
		}

		return convertInt(v, 16)
	case sqlparser.HexVal:
		val, err := v.HexDecode()
		if err != nil {
			return nil, err
		}
		return expression.NewLiteral(val, sql.LongBlob), nil

→ sqlparser.HexNum vs sqlparser.HexVal = int vs binary
HexNum is limited by the CPU integer size, in my case 64 bit

sqlparser.HexNum leads to sqlparser ...?

[timsehn]

Thanks for the bug report. We will dig in Monday. @jcor11599 will dig in.

[bpf120]

Hi @milahu , thanks for filing and using Dolt! We'd love to hear about your use case. Feel free to email me (brianf@dolthub.com) or swing by our Discord.

https://discord.com/invite/RFwfYpu

[timsehn]

Thanks for the deeper debugging. This might actually be a @Hydrocharged problem.

I can reproduce on the tip of master as well.

[Hydrocharged]

@milahu Thanks for the bug report!

Your trace gave me a good starting off point, so much so that I had an idea of what was going on before digging in myself, so I appreciate that very much! 😄

Regarding the first with the BINARY column, I just submitted a pull request to fix this issue, and should be in the next Dolt release. The other attempt that was demonstrated (attempting to use a base64 number) will not work, as there's no automatic way to see that the string represents a base64 number (unlike the 0x prefix). The base64 attempt also fails in MySQL.

[milahu]

thanks : )

no need for base64, that was just my desperate attempt to insert 32 bytes