Date: 2014-11-11 12:16:58 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>
To: SQL devs <>
Version: -- development
CC: @drstmane

Last updated: 2014-11-28 10:03:20 +0100

Comment 20436

Date: 2014-11-11 12:16:58 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:33.0) Gecko/20100101 Firefox/33.0
Build Identifier:

Not sure if this is a bug. Shouldn't the hexadecimal literal be treated the same as decimal one?

I was trying to insert hexadecimal constants for hugeint, but while no errors was reported, I discovered that it was silently truncated to 64 bits.

sql>select 18446744073709551616; -- 0x10000000000000000 in hex
+----------------------+
| single_value |
+======================+
| 18446744073709551616 |
+----------------------+
1 tuple (0.381ms)

sql>select 0x10000000000000000;
+--------------+
| single_value |
+==============+
| 0 |
+--------------+
1 tuple (0.362ms)

Reproducible: Always

Comment 20437

Date: 2014-11-12 10:53:21 +0100
From: @sjoerdmullender

Are you sure you're using the Jan2014-SP3 release for this?
The value 18446744073709551616 does not fit in a 64-bit integer, so it is not accepted by the server:
sql>select 18446744073709551616;
integer value too large or not a number (18446744073709551616) in: "select 18446744073709551616"

This is also true for the Oct2014 release.

In the default branch, which you would have to compile for yourself, there is initial support for 128-bit integers, so there it works:
sql>select 18446744073709551616;
+----------------------+
| single_value |
+======================+
| 18446744073709551616 |
+----------------------+
1 tuple (0.632ms)

But it looks like there, the support for 128-bit hexadecimal values isn't there yet:
sql>select 0x10000000000000000;
+--------------+
| single_value |
+==============+
| 0 |
+--------------+
1 tuple (0.629ms)

Comment 20438

Date: 2014-11-12 10:58:26 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

Seems like I chosen the wrong version when creating this entry. It is for the development branch actually. (We are using hugeint to store IPv6.)

Comment 20440

Date: 2014-11-14 09:50:50 +0100
From: @drstmane

Hi,

thanks for reporting!

Indeed, parsing / converting >64 bit hexadecimal literals in SQL is broken in the default branch.

I have a fix pending, but need to discuss with Niels & Sjoerd before committing,
as I see more potential inconsistencies with parsing/converting hexadecimal literals in SQL also in the Oct2014 (and earlier) branch(es).

Stefan

Comment 20441

Date: 2014-11-14 19:01:49 +0100
From: MonetDB Mercurial Repository <>

Changeset d9e7bfe93ede made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=d9e7bfe93ede

Changeset description:

fixed handling of hexadeximal literals in SQL:

- type "long (int)" is EVIL --- we need to use type "lng" instead !

- determine smallest suitable type based on actualy value,
  not on number of hexadecimal digits
  (i.e., in particular ignore/skip leading zeros)

- use tinyint as smallest type (not only smallint)

- avoid treating valid hex values as NULL:
  hex                 was                      is
  0x8000              NULL (smallint)       32768 (int)
  0x80000000          NULL (int)       2147483648 (bigint)
  0x8000000000000000  NULL (bigint)    ERROR (value too large)

- avoid negative values:
  hex                                  was                     is
  0x8001                            -32767 (smallint)       32769 (int)
  0xffff                                -1 (smallint)       65535 (int)
  0x80000001                   -2147483647 (int)       2147483649 (bigint)
  0xffffffff                            -1 (int)       4294967295 (bigint)
  0x8000000000000001  -9223372036854775807 (bigint)    ERROR (value too large)
  0xffffffffffffffff                    -1 (bigint)    ERROR (value too large)

  (leading sign (-0x...) is handled separatelyu elsewhere)


(These fixed have been triggered by bug #3621.)

Comment 20444

Date: 2014-11-17 17:35:02 +0100
From: @drstmane

This (and more) should be fixed in both the Oct2014 branch (up to 64-bit) and the default branch (also incl. 128-bit) as by above mentioned checking (and it's merge into the default branch).

The up to 64-bit fix will be released with the next Oct2014 bug-fix release; the 128-bit fix will be released with the next feature release.

Comment 20471

Date: 2014-11-28 10:03:20 +0100
From: @sjoerdmullender

Oct2014-SP1 has been released.