[sqlite] hexadecimal conversion on select query

[Cezary H. Noweta]

On 2016-03-13 23:14, jungle Boogie wrote:
> Hi Keith,
> On 13 March 2016 at 13:31, Keith Medcalf  wrote:
>> On Sunday, 13 March, 2016 13:36 -07:00, jungle Boogie > gmail.com> wrote:
>>> Here it is in decimal: (select code from sidemeters)
>>> "0"
>>> "1"
>>> "2"
>>> "5"
>>> "6"
>>> "7"
>>> "28"
>>> "29"
>>> "30"
>>> "31"
>>
>> Assuming that your quotes mean that the value is TEXT, not a number.
>> You can check this by running:
>>
>>   select code, typeof(code) from sidemeters;
>
> It's an integer. The quotes come from copying a sample from sqlite
> manager. Sorry to have omitted that from my previous message.

It does not matter.

>> So really the question is, what is the declared column affinity of
>> the "code" column in you table definition and what is the actual
>> type of the data stored?

``hex'' does not make use of an affinity - it ignores an affinity of an 
argument.

>> Based on the results you have obtained, I would suspect that the
>> column affinity is "integer" and you are storing either text or
>> integer (it is immaterial which in this particular case).

Column with any affinity can contain TEXT (or INTEGER disjointly)
storage. There is no affinity which cannot contain TEXT nor INTEGER.
Affinity does not matter --- whatever affinity, the results of ``hex'' 
will be the same.

>> In order for hex() to generate output, it "converts" the integer
>> into a blob (text) and outputs the hexified result.

> So does that mean it can't convert it from the decimal integer to a
> hexadecimal result, like I'm doing with printf?

Yes, and ``printf'' is probably the best method to achieve the desired 
results.

-- best regards

Cezary H. Noweta

[sqlite] hexadecimal conversion on select query

[jungle Boogie]

Hi Keith,
On 13 March 2016 at 13:31, Keith Medcalf  wrote:
> On Sunday, 13 March, 2016 13:36 -07:00, jungle Boogie  gmail.com> wrote:
>> Here it is in decimal: (select code from sidemeters)
>> "0"
>> "1"
>> "2"
>> "5"
>> "6"
>> "7"
>> "28"
>> "29"
>> "30"
>> "31"
>
> Assuming that your quotes mean that the value is TEXT, not a number.
> You can check this by running:
>
>  select code, typeof(code) from sidemeters;

It's an integer. The quotes come from copying a sample from sqlite
manager. Sorry to have omitted that from my previous message.

>
> You will note that the printf function wants to cast the "code" to an 
> integer, then outputs the hex representation of the integer.
> The hex() function treats the item as a "blob" and converts the actual bytes 
> stored into hex.
>
> They are two entirely different things.
>
> So really the question is, what is the declared column affinity of the "code" 
> column in you table definition and what is the actual type of the data stored?
>
> Based on the results you have obtained, I would suspect that the column 
> affinity is "integer" and you are storing either text or integer (it is 
> immaterial which in this particular case).
>
> In order for hex() to generate output, it "converts" the integer into a blob 
> (text) and outputs the hexified result.
>
>

So does that mean it can't convert it from the decimal integer to a
hexadecimal result, like I'm doing with printf?

-- 
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si

[sqlite] hexadecimal conversion on select query

[Keith Medcalf]

On Sunday, 13 March, 2016 13:36 -07:00, jungle Boogie  wrote:
> On 12 March 2016 at 22:07, J Decker  wrote:

> > maybe ?
> > https://www.sqlite.org/lang_corefunc.html

> > hex(X)The hex() function interprets its argument as a BLOB and returns
> > a string which is the upper-case hexadecimal rendering of the content
> > of that blob.

> That's what I first tried but not working as I expected.

> Here it is in decimal: (select code from sidemeters)
> "0"
> "1"
> "2"
> "5"
> "6"
> "7"
> "28"
> "29"
> "30"
> "31"

Assuming that your quotes mean that the value is TEXT, not a number.
You can check this by running:

 select code, typeof(code) from sidemeters;

You will note that the printf function wants to cast the "code" to an integer, 
then outputs the hex representation of the integer.
The hex() function treats the item as a "blob" and converts the actual bytes 
stored into hex.

They are two entirely different things.


> And in hex with my attempt of printf: (SELECT printf("%x",code)  FROM
> sidemeters)
> "0"
> "1"
> "2"
> "5"
> "6"
> "7"
> "1c"
> "1d"
> "1e"
> "1f"
> 
> Here is with hex(code): (SELECT hex(code) FROM sidemeters)
> "30"
> "31"
> "32"
> "35"
> "36"
> "37"
> "3238"
> "3239"
> "3330"
> "3331"
> 
> 
> Am I trying to use hex() incorrectly?
> 
> Thanks!

So really the question is, what is the declared column affinity of the "code" 
column in you table definition and what is the actual type of the data stored?

Based on the results you have obtained, I would suspect that the column 
affinity is "integer" and you are storing either text or integer (it is 
immaterial which in this particular case).

In order for hex() to generate output, it "converts" the integer into a blob 
(text) and outputs the hexified result.

[sqlite] hexadecimal conversion on select query

[jungle Boogie]

On 12 March 2016 at 22:07, J Decker  wrote:
> maybe ?
> https://www.sqlite.org/lang_corefunc.html
>
> hex(X)The hex() function interprets its argument as a BLOB and returns
> a string which is the upper-case hexadecimal rendering of the content
> of that blob.

That's what I first tried but not working as I expected.

Here it is in decimal: (select code from sidemeters)
"0"
"1"
"2"
"5"
"6"
"7"
"28"
"29"
"30"
"31"

And in hex with my attempt of printf: (SELECT printf("%x",code)  FROM
sidemeters)
"0"
"1"
"2"
"5"
"6"
"7"
"1c"
"1d"
"1e"
"1f"

Here is with hex(code): (SELECT hex(code) FROM sidemeters)
"30"
"31"
"32"
"35"
"36"
"37"
"3238"
"3239"
"3330"
"3331"


Am I trying to use hex() incorrectly?

Thanks!

-- 
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si

[sqlite] Hexadecimal literals

[Cezary H. Noweta]

Hello,

According to ``lang_expr.html#hexint'':

``...0x8000 means the same as -9223372036854775808.
Hexadecimal integer literals are interpreted as 64-bit two's-complement
integers...''.

Not necessary:

``SELECT - -9223372036854775808;'' => 9.22337203685478e+18. Ok.
``SELECT -0x8000;'' => -9223372036854775808. Error.

Ad-hoc solution: src/expr.c, codeInteger(), replace lines:

==
   if( negFlag ){ value = c==2 ? SMALLEST_INT64 : -value; }
   sqlite3VdbeAddOp4Dup8(v, OP_Int64, 0, iMem, 0, (u8*), 
P4_INT64);
==

with

==
   if( negFlag ) {
 if ( 0 == c && SMALLEST_INT64 == value ) {
#ifdef SQLITE_OMIT_FLOATING_POINT
   sqlite3ErrorMsg(pParse, "oversized integer: %s%s", negFlag ? 
"-" : "", z);
#else
   double dValue = -(double)value;
   sqlite3VdbeAddOp4Dup8(v, OP_Real, 0, iMem, 0, (u8*), 
P4_REAL);
#endif
   return;
 }
 value = c==2 ? SMALLEST_INT64 : -value;
   }
   sqlite3VdbeAddOp4Dup8(v, OP_Int64, 0, iMem, 0, (u8*), 
P4_INT64);
==

-- best regards

Cezary H. Noweta

[sqlite] hexadecimal conversion on select query

[J Decker]

maybe ?
https://www.sqlite.org/lang_corefunc.html

hex(X)The hex() function interprets its argument as a BLOB and returns
a string which is the upper-case hexadecimal rendering of the content
of that blob.

On Sat, Mar 12, 2016 at 12:18 PM, jungle Boogie  
wrote:
> Hello All,
>
> I'm curious to know if there's a better way to run a query and convert
> the results to hexadecimal.
>
>  This is what I have so far:
> SELECT printf("%x %d",code,code), printf("%x", denom)  FROM sidemeters
> order by denom
>
> The data is already in decimal so I don't necessarily need to include
> the extra %d, but it's nice for comparison.
>
>
> Thanks for any input!
>
> --
> ---
> inum: 883510009027723
> sip: jungleboogie at sip2sip.info
> xmpp: jungle-boogie at jit.si
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] hexadecimal conversion on select query

[jungle Boogie]

Hello All,

I'm curious to know if there's a better way to run a query and convert
the results to hexadecimal.

 This is what I have so far:
SELECT printf("%x %d",code,code), printf("%x", denom)  FROM sidemeters
order by denom

The data is already in decimal so I don't necessarily need to include
the extra %d, but it's nice for comparison.


Thanks for any input!

-- 
---
inum: 883510009027723
sip: jungleboogie at sip2sip.info
xmpp: jungle-boogie at jit.si

Re: [sqlite] Hexadecimal integer literals

[Scott Robison]

Right, I've seen the 0o prefix syntax, I just figured I was already pushing
my luck with the 0b prefix. Regardless, I wanted to speak against the idea
of true C style octal constants before someone else asked for them. :)

Apologies for top posting, on my phone.
On Jul 24, 2014 2:58 AM, "Markus Schaber"  wrote:

> Hi,
>
> Von: Scott Robison
>
> > On Wed, Jul 23, 2014 at 9:46 PM, J Decker  wrote:
> >
> > > Seems like adding hex interpreting is just adding code for the sake of
> > > adding code.
> > > Unless the data is coming from some pre written text file, isn't just
> > > just as easy to format an into into decimal as it is for hex without
> > > having to add extra characters for the prefix?
> > >
> >
> > One desirable aspect of code is that it is easily understood, that future
> > modifications / changes be as easy as possible. It is usually much
> easier to
> > see that 0x4000 is a single bit set in a 32 bit integer vs seeing the
> > number 1073741824 and wondering exactly what it means. At least it is to
> me.
> >
> > For that reason, I think adding hex constants to the parser is a good
> idea.
> > If I were expressing wishes, I'd suggest adding binary constants (with a
> 0b
> > prefix) but that's probably pushing luck. :)
> >
> > But no octal! At least not using the C syntax. I like being able to
> prefix a
> > decimal integer with 0 and not having it change the meaning (which can't
> be
> > done in C).
>
> Python 3, Rust and others adopted the syntax 0o123 for octal literals.
>
> The IEC 61131 languages use the syntax base#value, so 16#12ab is a hex
> number,
> and 8#123 is an octal number.
>
>
> Best regards
>
> Markus Schaber
>
> CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH
>
> Inspiring Automation Solutions
>
> 3S-Smart Software Solutions GmbH
> Dipl.-Inf. Markus Schaber | Product Development Core Technology
> Memminger Str. 151 | 87439 Kempten | Germany
> Tel. +49-831-54031-979 | Fax +49-831-54031-50
>
> E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS
> store: http://store.codesys.com
> CODESYS forum: http://forum.codesys.com
>
> Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner |
> Trade register: Kempten HRB 6186 | Tax ID No.: DE 167014915
>
> This e-mail may contain confidential and/or privileged information. If you
> are not the intended recipient (or have received
> this e-mail in error) please notify the sender immediately and destroy
> this e-mail. Any unauthorised copying, disclosure
> or distribution of the material in this e-mail is strictly forbidden.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Markus Schaber]

Hi,

Von: Scott Robison

> On Wed, Jul 23, 2014 at 9:46 PM, J Decker  wrote:
> 
> > Seems like adding hex interpreting is just adding code for the sake of
> > adding code.
> > Unless the data is coming from some pre written text file, isn't just
> > just as easy to format an into into decimal as it is for hex without
> > having to add extra characters for the prefix?
> >
> 
> One desirable aspect of code is that it is easily understood, that future
> modifications / changes be as easy as possible. It is usually much easier to
> see that 0x4000 is a single bit set in a 32 bit integer vs seeing the
> number 1073741824 and wondering exactly what it means. At least it is to me.
> 
> For that reason, I think adding hex constants to the parser is a good idea.
> If I were expressing wishes, I'd suggest adding binary constants (with a 0b
> prefix) but that's probably pushing luck. :)
> 
> But no octal! At least not using the C syntax. I like being able to prefix a
> decimal integer with 0 and not having it change the meaning (which can't be
> done in C).

Python 3, Rust and others adopted the syntax 0o123 for octal literals.

The IEC 61131 languages use the syntax base#value, so 16#12ab is a hex number,
and 8#123 is an octal number.


Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: 
http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade 
register: Kempten HRB 6186 | Tax ID No.: DE 167014915

This e-mail may contain confidential and/or privileged information. If you are 
not the intended recipient (or have received
this e-mail in error) please notify the sender immediately and destroy this 
e-mail. Any unauthorised copying, disclosure
or distribution of the material in this e-mail is strictly forbidden.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Markus Schaber]

Hi, Eric,

Von: Eric Rubin-Smith
> 
> So far no one has raised the idea of using a "big int" layer to implement
> proper integer arithmetic past 64 bits.  The fact that it hasn't been
> mentioned makes me worry that it's a blatantly silly idea for SQLite for some
> reason -- but I'm tossing it out there on the off chance that it's useful.

Actually, I did: 
http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-July/054285.html

 
> Personally, I would have loved to be able to use 128-bit integer math a few
> weeks ago when I was investigating the idea of implementing a longest-prefix
> search capability for IPv6 networks using SQLite's R*Tree.
>  I had to implement all the shifting and such in C, which is fine, but it
> would have been pretty sexy to have gotten it all done in SQL.

I'm not sure whether such functionalities are a must for SQLite core, but
I think that extensions providing such functionalities should be possible.

I'm not that much into SQLite internals yet, but I know that PostgreSQL allows
extensions to define their own data types with their own parsers and syntax,
which is used e. G. by the PostGIS extension. On the other hand, PostgreSQL
has built-in "decimal" and "numeric" types which allow up to 131072 digits
before and 16383 digits after the decimal point.
http://www.postgresql.org/docs/current/static/datatype-numeric.html

So one might argue such datatypes could also fit well into SQLite :-)


Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: 
http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade 
register: Kempten HRB 6186 | Tax ID No.: DE 167014915

This e-mail may contain confidential and/or privileged information. If you are 
not the intended recipient (or have received
this e-mail in error) please notify the sender immediately and destroy this 
e-mail. Any unauthorised copying, disclosure
or distribution of the material in this e-mail is strictly forbidden.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Scott Robison]

On Wed, Jul 23, 2014 at 9:46 PM, J Decker  wrote:

> Seems like adding hex interpreting is just adding code for the sake of
> adding code.
> Unless the data is coming from some pre written text file, isn't just just
> as easy to format an into into decimal as it is for hex without having to
> add extra characters for the prefix?
>

One desirable aspect of code is that it is easily understood, that future
modifications / changes be as easy as possible. It is usually much easier
to see that 0x4000 is a single bit set in a 32 bit integer vs seeing
the number 1073741824 and wondering exactly what it means. At least it is
to me.

For that reason, I think adding hex constants to the parser is a good idea.
If I were expressing wishes, I'd suggest adding binary constants (with a 0b
prefix) but that's probably pushing luck. :)

But no octal! At least not using the C syntax. I like being able to prefix
a decimal integer with 0 and not having it change the meaning (which can't
be done in C).
-- 
Scott Robison
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[J Decker]

Seems like adding hex interpreting is just adding code for the sake of
adding code.
Unless the data is coming from some pre written text file, isn't just just
as easy to format an into into decimal as it is for hex without having to
add extra characters for the prefix?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Eric Rubin-Smith]

So far no one has raised the idea of using a "big int" layer to implement
proper integer arithmetic past 64 bits.  The fact that it hasn't been
mentioned makes me worry that it's a blatantly silly idea for SQLite for
some reason -- but I'm tossing it out there on the off chance that it's
useful.

Personally, I would have loved to be able to use 128-bit integer math a few
weeks ago when I was investigating the idea of implementing a
longest-prefix search capability for IPv6 networks using SQLite's R*Tree.
 I had to implement all the shifting and such in C, which is fine, but it
would have been pretty sexy to have gotten it all done in SQL.

Eric
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[James K. Lowden]

On Wed, 23 Jul 2014 07:07:25 -0400
Richard Hipp  wrote:

> Hex literals are useful in conjunction with the bit-wise AND and OR
> operators (& and |) and in applications that make use of bit fields.
...
> The current SQLite implementation (on the "hex-literal" branch) works
> by converting hex literals of 64 bits or less into a signed 64-bit
> integer. Hex literals of 65 bits or more are approximated by a nearby
> floating-point number.  

Hex literals are used in an integer context.  Converting them to
floating point is counter-productive.  If someone gives you a
17-digit hexadecimal literal, he doesn't want a floating point
approximation.  He wants an error message.  

I would raise an error for anything over 64 bits, or just ignore it,
until you support a processor with a wider integral type.  I don't see
a need for it except as input to the parser.  I guess that puts me in
the #6 camp, too.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Zsbán Ambrus]

Hello!


Just like the others in this conversation, I also believe that you
must not change the rules how strings are converted to integers by
type affinity, or by type conversions of arithmetic operators.  Thus,
you must not add hexadecimal representation to conversions (nor hex
floats or 'inf' or any other new numeric representation).

Let me explain why.  I create the following table in a database:

CREATE TABLE tb(cl NUMERIC PRIMARY KEY);
INSERT INTO tb VALUES('3.50');
INSERT INTO tb VALUES('0x20');

This inserts the real 3.5 and the string '0x20' into the table.  Then,
any of the following queries finds a row from that table.

SELECT cl FROM tb WHERE cl = 3.5;
SELECT cl FROM tb WHERE cl = '3.5';
SELECT cl FROM tb WHERE cl = '3.50';
SELECT cl FROM tb WHERE cl = '3.500';
SELECT cl FROM tb WHERE cl = '0x20';

Now suppose hypothetically that you changed a future version of sqlite
to convert the string '0x20' to the number 32.  Load the existing
database into this new version.  This table will then have a strange
state: it will have a row with the string ('0x20') which is something
you couldn't insert with the new version of sqlite, because if you
tried to insert it, the new version would convert it to a number.
Further, the queries

SELECT cl FROM tb WHERE cl = '0x20';
SELECT cl FROM tb WHERE cl = 32;

would now either not find that row, or would have to search in
multiple places in the index, because '0x20' is still indexed as a
string, not as a number.

Similarly, a table could have a check constraint that the previous
version of sqlite had validated at insertion, but is no longer true in
the hypothetical new version; or a partial index that used to be
correct in a previous version of sqlite but is now suddenly missing
rows that should be in there in the hypothetical new version.  (I
don't know whether there could be a problem with foreign keys.)


Now of course adding hexadecimal literals in the parser doesn't cause
any of these problems, as long as you make sure any statement or
schema with such a hexadecimal literal definitely gives an error in
previous versions of sqlite.  Similarly, if you believe that parsing
numbers from a hexadecimal representation is useful at runtime, you
could add an ordinary SQL function that does this.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Klaas V]

Like some others I vote for solution 6.
In general accepting hexadecimal notation for floating point values exceeding 
64 bits is too developer/scientist friendly. Who needs so much precision use 
Fortran or another specialized language rather than SQL of any flavor. My € 
2E-2 [±$0,026930006220831 ;-)]
 

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Jean-Christophe Deschamps]

Conversion of oversized hex into FP would break easily and reveal 
hardly reproductible across many platforms. Being a support for some 
languages fora I observe daily how FP inaccuracies is a real-world 
problem in simple-looking code.


The only reasonable thing I can foresee is treat hex as signed int64 
inside limits and as blobs outside int64 limits, all without a warning 
or error raised.


This way no new behavior is needed: what happens today when a hex blob 
appears somewhere? Just like when a column typed as INT holds few 
blobs: does the index build/search bomb: no. Oversize hex literals will 
simply behave the same.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Gerry Snyder]

I can think of situations where I would want the result to be truncated 
to 64 bits.


I can think of situations where I would want SQLite to raise an error.

I cannot imagine wanting a floating point result.

Gerry Snyder
-
On 7/23/2014 4:07 AM, Richard Hipp wrote:

We are looking into adding hexadecimal integer literals to SQLite.  In
other words, we are looking to enhance SQLite to understand 0x1234 as
another way of writing 4660.  Hex literals are useful in conjunction with
the bit-wise AND and OR operators (& and |) and in applications that make
use of bit fields.

The question is what to do with hex literals that are larger than 64 bits.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Tim Streater]

On 23 Jul 2014 at 12:07, Richard Hipp  wrote: 

> We are looking into adding hexadecimal integer literals to SQLite.  In
> other words, we are looking to enhance SQLite to understand 0x1234 as
> another way of writing 4660.  Hex literals are useful in conjunction with
> the bit-wise AND and OR operators (& and |) and in applications that make
> use of bit fields.
>
> The question is what to do with hex literals that are larger than 64 bits.
> In other words, what number is represented by (say):
>
>  0x123456789abcdef01

I would say take up to 64 bits. More than that to be handled as an error in the 
same way that a too-long string of decimal digits would be for an integer, or a 
floating value that won't fit because the exponent is too large.

Equally, if the string contains a character invalid for the type, handle all in 
the same way. E.g.:

  0x1234zz
  1234zz
  1.234zz

should have the same error treatment, if any. I note that "select 1234zz;" in 
the shell gives a good error message but I don't know if that's just the shell 
being clever or the library is picking it up.

Regarding signed/unsigned, what is done for integers? Perhaps if a hex literal 
is prefixed with a minus, that simply forces the sign bit on.

--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Doug Currie]

>
> > Here's an analogy: a sequence of decimal digits is unsigned; it only
> > becomes negative when you put a "-" in front of it.
> >
> > Why shouldn't hex work the same way? (to eliminate the discombobulating
> > segment)
> >
>
> Because then you would not be able to write (in hex) a 64-bit bitmap that
> had the most significant bit set.
>

Ah, you want convenience! You could write -0x8000, but that
does become a hassle.

e
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Richard Hipp]

On Wed, Jul 23, 2014 at 10:22 AM, Doug Currie  wrote:

> > Why are hex literals interpreted as signed at all? You could simply
> > > consider all hex literals as unsigned values. If you need a negative
> > value,
> > > prefix it with the - operator, e.g., -0x77.
> > >
> > > With this approach (a) there is no discombobulating segment, (b) all 64
> > bit
> > > bit-masks are supported, and (c) the gradual overflow to double makes
> > > sense.
> >
> >
> > Because SQLite only supports signed integers internally.  If hex literals
> > must be unsigned, that limits them to 63 bits.
> >
>
> Here's an analogy: a sequence of decimal digits is unsigned; it only
> becomes negative when you put a "-" in front of it.
>
> Why shouldn't hex work the same way? (to eliminate the discombobulating
> segment)
>

Because then you would not be able to write (in hex) a 64-bit bitmap that
had the most significant bit set.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Doug Currie]

> Why are hex literals interpreted as signed at all? You could simply
> > consider all hex literals as unsigned values. If you need a negative
> value,
> > prefix it with the - operator, e.g., -0x77.
> >
> > With this approach (a) there is no discombobulating segment, (b) all 64
> bit
> > bit-masks are supported, and (c) the gradual overflow to double makes
> > sense.
>
>
> Because SQLite only supports signed integers internally.  If hex literals
> must be unsigned, that limits them to 63 bits.
>

Here's an analogy: a sequence of decimal digits is unsigned; it only
becomes negative when you put a "-" in front of it.

Why shouldn't hex work the same way? (to eliminate the discombobulating
segment)

e
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Richard Hipp]

On Wed, Jul 23, 2014 at 9:59 AM, Doug Currie  wrote:

> > There is this range of negative
> > values smack in the middle of an otherwise uniformly increasing sequence
> of
> > positive numbers.  That negative range seems discombobulating.
>
>
> Why are hex literals interpreted as signed at all? You could simply
> consider all hex literals as unsigned values. If you need a negative value,
> prefix it with the - operator, e.g., -0x77.
>
> With this approach (a) there is no discombobulating segment, (b) all 64 bit
> bit-masks are supported, and (c) the gradual overflow to double makes
> sense.
>


Because SQLite only supports signed integers internally.  If hex literals
must be unsigned, that limits them to 63 bits.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Doug Currie]

> There is this range of negative
> values smack in the middle of an otherwise uniformly increasing sequence of
> positive numbers.  That negative range seems discombobulating.


Why are hex literals interpreted as signed at all? You could simply
consider all hex literals as unsigned values. If you need a negative value,
prefix it with the - operator, e.g., -0x77.

With this approach (a) there is no discombobulating segment, (b) all 64 bit
bit-masks are supported, and (c) the gradual overflow to double makes sense.

e
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Markus Schaber]

Hi,

Von: Im Auftrag von Dominique Devienne
> On Wed, Jul 23, 2014 at 1:07 PM, Richard Hipp  wrote:
> 
> > (6) Do not support hexadecimal integer literals for casts and affinity
> > coercions.  Only support hex literals in the SQL parser, and throw
> > errors for oversized hex literals in that context.
> >
> 
> +1. --DD

I'm also in favour of (6) for now.

This still leaves room for upwards compatible behavior should SQLite 
(version 4, maybe) or an extension add support for larger integers.


Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: 
http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade 
register: Kempten HRB 6186 | Tax ID No.: DE 167014915

This e-mail may contain confidential and/or privileged information. If you are 
not the intended recipient (or have received
this e-mail in error) please notify the sender immediately and destroy this 
e-mail. Any unauthorised copying, disclosure
or distribution of the material in this e-mail is strictly forbidden.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Dominique Devienne]

On Wed, Jul 23, 2014 at 2:15 PM, Simon Slavin  wrote:

> > (3) Convert hex literals of 63-bits or less into integers and convert
> > 64-bit or larger hex literals into a floating-point approximation.
>
> BLOBs.  Anything longer than 64 bits should be BLOBs.  Code which compares
> two values for identity (y == x) will still work correctly, but most other
> things will fail.
>

We already have hex-string literals for blobs, x"ff", so I don't think it's
a good idea.

Someone could accidentally type one too many hex-digit and silently switch
from integer to blob, which might not be immediately apparent, unlike an
error. Which is why I prefer (6). --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Simon Slavin]

On 23 Jul 2014, at 12:07pm, Richard Hipp  wrote:

> (3) Convert hex literals of 63-bits or less into integers and convert
> 64-bit or larger hex literals into a floating-point approximation.

BLOBs.  Anything longer than 64 bits should be BLOBs.  Code which compares two 
values for identity (y == x) will still work correctly, but most other things 
will fail.

I don't think you need to be considerate to anyone who uses a 65-bit hex 
literal.  Hex is not a natural counting language for humans and anyone who uses 
it needs to be a programmer and think like a programmer.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Paul]

 
> (6) Do not support hexadecimal integer literals for casts and affinity
> coercions. Only support hex literals in the SQL parser, and throw errors
> for oversized hex literals in that context.

I vote for (6) as for being the most predictable behaviour that has no 
possibility to break any existing logic.


Paul
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Stephan Beal]

On Wed, Jul 23, 2014 at 1:07 PM, Richard Hipp  wrote:

> The current SQLite implementation (on the "hex-literal" branch) works by
> converting hex literals of 64 bits or less into a signed 64-bit integer.
>

Overflow/underflow are unspecified for signed types, and the <> ops
could certainly overflow.


> 1.84467440737096e+19. This approach means that all hexadecimal literals are
> positive numbers, except literals 0x8000 through
> 0x which are negative.  There is this range of negative
> values smack in the middle of an otherwise uniformly increasing sequence of
> positive numbers.  That negative range seems discombobulating.
>

Indeed.

(1) Keep the current prototype behavior.  Hex literals of 64 bits or less
> are converted into twos-complement 64-bit integers, meaning that some
> values are negative.  Hex literals of 65 bits or more are converted into a
> floating-point approximation.
>

Sounds reasonable.


> (2) Declare that the values of hex literals greater than 64 bits are
> "undefined" and convert them into random 64-bit integers.

 Seriously: use
> the random number generator to convert oversized hex literals into
> integers, and thereby discourage programmers from using oversized hex
> literals.
>

i wouln't go quite that far, but converting to NULL might be a middle
ground.


> (4) Silently truncate all hexadecimal literals to 64-bits, like SQL Server
> does.
>

Is reasonable, given that overflow for signed types is undefined (so any
approach is "not wrong").


> (7) Have the parser convert oversized hex literals into NULL, or throw
> errors, and have attempts to coerce oversized hex literals strings into
> numeric values fail, thus preventing affinity conversions.
>

+1 for NULL (sounds simplest and least surprising)


> (8) Work like PostgreSQL and Oracle and legacy SQLite and simply do not
> support hexadecimal integer literals.
>

Also good. Maybe, instead, add functions which take hex strings and return
an int. Then you've got much more freedom in terms of error reporting, as
it all happens via the function:

select hexint('0x10')
16


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Clemens Ladisch]

Richard Hipp wrote:
> Hex literals are useful in conjunction with
> the bit-wise AND and OR operators (& and |) and in applications that make
> use of bit fields.
>
> The question is what to do with hex literals that are larger than 64 bits.

There are no bit operations on values larger than 64 bits, so such
literals should be rejected.

> (6) Do not support hexadecimal integer literals for casts and affinity
> coercions.

There is a compatibiliy constraint: parsing hexadecimal values out of
strings would change the documented behaviour of casts and coercions:
| When casting a TEXT value to INTEGER, the longest possible prefix of
| the value that can be interpreted as an integer number is extracted
| from the TEXT value and the remainder ignored.

So (6) appears to be the only viable choice.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal integer literals

[Dominique Devienne]

On Wed, Jul 23, 2014 at 1:07 PM, Richard Hipp  wrote:

> (6) Do not support hexadecimal integer literals for casts and affinity
> coercions.  Only support hex literals in the SQL parser, and throw errors
> for oversized hex literals in that context.
>

+1. --DD
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Hexadecimal integer literals

[Richard Hipp]

We are looking into adding hexadecimal integer literals to SQLite.  In
other words, we are looking to enhance SQLite to understand 0x1234 as
another way of writing 4660.  Hex literals are useful in conjunction with
the bit-wise AND and OR operators (& and |) and in applications that make
use of bit fields.

The question is what to do with hex literals that are larger than 64 bits.
In other words, what number is represented by (say):

  0x123456789abcdef01

The current SQLite implementation (on the "hex-literal" branch) works by
converting hex literals of 64 bits or less into a signed 64-bit integer.
Hex literals of 65 bits or more are approximated by a nearby floating-point
number.  So, for example, the 65-bit hex literal example above would become
2.09882954794206e+19.  However, this approach creates a discontinuity at
the boundary between 64-bit and 65-bit literals.  The value
0x is equal to -1 but 0x1 is
1.84467440737096e+19. This approach means that all hexadecimal literals are
positive numbers, except literals 0x8000 through
0x which are negative.  There is this range of negative
values smack in the middle of an otherwise uniformly increasing sequence of
positive numbers.  That negative range seems discombobulating.

What Do Other SQL Database Engines Do?

Oracle and PostgreSQL do not support hexadecimal integer literals. MySQL
supports hex literals, but the support appears to be buggy.  In MySQL
5.6.6m9 and 5.5.32 the statement "SELECT 0 + 0x7fff;" yields
9223372036854776000 when the correct answer is 9223372036854775807.  If you
say just "SELECT 0x7fff", without the "0 +" part, then
sqlfiddle gives no answer at all, suggesting that MySQL is crashing.  SQL
Server also supports hex literals, and seems to do so with fewer bugs than
MySQL.  SQL Server appears to silently truncate hex integer to 32 or 64
bits (depending on context), ignoring more significant bits.  Thus in SQL
Server, "SELECT 0x10001" yields just "1".

Possible Solutions:

(1) Keep the current prototype behavior.  Hex literals of 64 bits or less
are converted into twos-complement 64-bit integers, meaning that some
values are negative.  Hex literals of 65 bits or more are converted into a
floating-point approximation.

(2) Declare that the values of hex literals greater than 64 bits are
"undefined" and convert them into random 64-bit integers.  Seriously: use
the random number generator to convert oversized hex literals into
integers, and thereby discourage programmers from using oversized hex
literals.

(3) Convert hex literals of 63-bits or less into integers and convert
64-bit or larger hex literals into a floating-point approximation.  This
means that all hex literals will be non-negative and it eliminates the
discontinuities. But it also means that you can only use the lower 63 bits
of a 64-bit integer as a bitmask.

(4) Silently truncate all hexadecimal literals to 64-bits, like SQL Server
does.

(5) Throw an error when a hex literal larger than 64-bits is seen.  This is
harder than it sounds, since literals can occur in so many different
contexts.  There is the parser, of course.  But also literals can be parsed
when doing string-to-numeric coercion via the CAST operator, or in order to
do arithmetic, or when inserting string values into columns with NUMERIC
affinity.  There are many cases that would need to be coded and tested,
some of which have never before had the capability to throw an error.

(6) Do not support hexadecimal integer literals for casts and affinity
coercions.  Only support hex literals in the SQL parser, and throw errors
for oversized hex literals in that context.

(7) Have the parser convert oversized hex literals into NULL, or throw
errors, and have attempts to coerce oversized hex literals strings into
numeric values fail, thus preventing affinity conversions.

(8) Work like PostgreSQL and Oracle and legacy SQLite and simply do not
support hexadecimal integer literals.

Other ideas?  Comments?  Criticism?
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Hexadecimal Inequalities Failing?

[Igor Tandetnik]

Clay Baenziger <[EMAIL PROTECTED]> wrote:
> Let's try a small number:
> sqlite> SELECT HEX(minMAC) FROM manifests WHERE minMAC <= X'04';
> 00C1
> [Wrong -- x'04' < x'00C1']

Blobs are not compared as if they are very large integers, but rather 
lexicographically, byte by byte (the way memcmp would). Under such a 
comparison, x'04' is greater than x'00C1' - for the same reason 
that 'z' is greater than 'abcd'.

> Let's try another small number:
> sqlite> SELECT HEX(minMAC) FROM manifests WHERE minMAC <= X'04421';
> SQL error: unrecognized token: "X'04421'"
> [What's malformed about this compared to above?]

A hex literal has to contain an even number of hex digits.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Hexadecimal Inequalities Failing?

[Clay Baenziger]

Hello,
I'm trying to use hexadecimal numbers in a where clause and it
seems things aren't quite working as expected. I get unknown token
errors or inequalities don't return the correct answer.

I have one value set:

sqlite> SELECT HEX(minMAC) FROM manifests;
00C1


All works as expected with some simple tests:
sqlite> SELECT HEX(minMAC) FROM manifests WHERE minMAC <= 
X'00D1';
00C1
sqlite>  SELECT HEX(minMAC) FROM manifests WHERE minMAC >=
X'00D1';

Let's try a small number:
sqlite> SELECT HEX(minMAC) FROM manifests WHERE minMAC <= X'04';
00C1
[Wrong -- x'04' < x'00C1']

Let's try another small number:
sqlite> SELECT HEX(minMAC) FROM manifests WHERE minMAC <= X'04421';
SQL error: unrecognized token: "X'04421'"
[What's malformed about this compared to above?]

So, can anyone explain to me how I can do inequalities with hexadecimal
values; and why some values cause an "unrecognized token" error?

Thank you,
Clay
P.S.
Double escaping as is sometimes needed for BLOB objects as per:
http://www.mail-archive.com/sqlite-users@sqlite.org/msg27502.html
This seems wrong from this output though:

sqlite> SELECT HEX(minMAC) FROM manifests WHERE minMAC <=
"X'00D1'";
[Wrong as I would expect x'00C1' < "x'00D1'" (Is this a
string interpretation?)]

sqlite> SELECT HEX(minMAC) FROM manifests WHERE minMAC >=
"X'00D1'";
00C1
[But at least it seems that x'00C1' > "x'00D1'"
consistently]

P.P.S.
I've tested this with minMAC being an integer or a blob type.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Hexadecimal literals or MAX_INTEGER definition?

[Brodie Thiesfield]

Does sqlite support numeric literals in hexadecimal?
e.g.INSERT INTO table(mask) VALUES (0x);

Additionally, is there a constant like MAX_INTEGER defined which I can
use as the maximum value that an INTEGER field supports (assuming that
types actually exist)?

Regards,
Brodie

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] hexadecimal

[Kees Nuyt]

Hi Lloyd,

On Wed, 25 Oct 2006 20:11:49 +0530, you wrote:

> Hi list,
>  can I insert a hexadecimal value to an integer field? 

Yes.

> if yes How can do that?

Convert it to an integer in your host language first.
The X'hexstring' syntax is only for BLOBs.

> Thanks,
>   Lloyd
-- 
  (  Kees Nuyt
  )
c[_]

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] hexadecimal

[Lloyd]

Hi list,
  can I insert a hexadecimal value to an integer field? if yes How can
do that?

Thanks,
  Lloyd


__
Scanned and protected by Email scanner

-
To unsubscribe, send email to [EMAIL PROTECTED]
-