Re: BUG on 64bit if a value is quoted (4.0.17 / 4.1.2 / 5.0.0)
On Sat, Jan 31, 2004 at 10:06:04PM -0500, Michael Stassen wrote: Meanwhile, perhaps a simpler workaround would be to change how you build the query in Perl so as to avoid the quotes. Well, I know that this would be easy but I have good reasons to avoid this. The most important one is lru statement-caching in PApp::SQL (a DBI frontend that makes sense in many ways including proper utf-8 handling). The other funny thing I noted: errno was set on udf-entry and my function returned NULL because I checked errno after stroull... It may be a feature for others but I call this: bug. .) mysql select udf_errno(); +-+ | udf_errno() | +-+ | 22 | +-+ 1 row in set (0.00 sec) mysql BTW: This is EINVAL -- ciao - Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG on 64bit if a value is quoted (4.0.17 / 4.1.2 / 5.0.0)
WOW !!
The story goes on:
I wrote the UDF-Functions (and64/or64) and while and it seems
to work:
mysql select and64('9223372036854775809',127);
+--+
| and64('9223372036854775809',127) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
I noted that mysql can't really dial with unsigned:
(you can't even declare it unsigned)
mysql select or64('9223372036854775809',127);
+-+
| or64('9223372036854775809',127) |
+-+
|-9223372036854775681 |
+-+
1 row in set (0.00 sec)
On Sun, Feb 01, 2004 at 01:09:34AM +0100, Stefan Traby wrote:
Hi !
select 129 127; -- ok(1)
select '129' 127; -- ok(1)
select (0+'129') 127; -- ok(1)
select CAST('129' AS UNSIGNED INTEGER) 127; -- ok(1)
select CONVERT('129', UNSIGNED INTEGER) 127;-- ok(1)
Doing the same with 64 bit values gives strange results:
select 9223372036854775809 127; -- returns 1, correct
select '9223372036854775809' 127; -- ERROR: returns 127
select (0+'9223372036854775809') 127; -- ERROR: returns 0
select CAST('9223372036854775809' AS UNSIGNED INTEGER) 127; -- ERROR: returns 127
select CONVERT('9223372036854775809', UNSIGNED INTEGER) 127;-- ERROR: returns 127
So please tell me how to perform a bitwise 64bit-AND if
a value is quoted.
Never seen such a strange bug for a long time.
Well, I just found it because DBD::mysql quotes large integer
bind-variables even on perl int64...
So do I need to write an UDF-Function to get the correct behavior
or is there another work-arround to fix this?
--
ciao -
Stefan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
ciao -
Stefan
aclocal - emit a warning if -ac kernel is used.
Stefan TrabyLinux/ia32 office: +49-721-3523165
Mathystr. 18-20 V/8 Linux/alpha cell: +XX-XXX-XXX
76133 Karlsruhe Linux/sparc http://graz03.kwc.at
Germany Linux/arm mailto:[EMAIL PROTECTED]
Europe Linux/mips mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG on 64bit if a value is quoted (4.0.17 / 4.1.2 / 5.0.0)
I think the problem has nothing to do with . Taking the out of your
examples leaves you with:
mysql select '9223372036854775809' + 0;
+---+
| '9223372036854775809' + 0 |
+---+
| 9.2233720368548e+18 |
+---+
mysql select CAST('9223372036854775809' AS UNSIGNED INTEGER);
+-+
| CAST('9223372036854775809' AS UNSIGNED INTEGER) |
+-+
| 9223372036854775807 |
+-+
mysql select CONVERT('9223372036854775809', UNSIGNED INTEGER);
+--+
| CONVERT('9223372036854775809', UNSIGNED INTEGER) |
+--+
| 9223372036854775807 |
+--+
mysql select 9223372036854775807 127;
+---+
| 9223372036854775807 127 |
+---+
| 127 |
+---+
See, the works correctly. It's the conversion of
'9223372036854775809' to a number which causes your problem. I expect
the warnings about the range of BIGINT in the manual
http://www.mysql.com/doc/en/Column_types.html apply here. It says
All arithmetic is done using signed BIGINT or DOUBLE values, so you
shouldn't use unsigned big integers larger than 9223372036854775807 (63
bits) except with bit functions! If you do that, some of the last digits
in the result may be wrong because of rounding errors when converting
the BIGINT to a DOUBLE.
...
`-', `+', and `*' will use BIGINT arithmetic when both arguments are
integer values! This means that if you multiply two big integers (or
results from functions that return integers) you may get unexpected
results when the result is larger than 9223372036854775807.
Of course, it's not clear that CAST and CONVERT shouldn't have worked.
Meanwhile, perhaps a simpler workaround would be to change how you build
the query in Perl so as to avoid the quotes.
Michael
Stefan Traby wrote:
WOW !!
The story goes on:
I wrote the UDF-Functions (and64/or64) and while and it seems
to work:
mysql select and64('9223372036854775809',127);
+--+
| and64('9223372036854775809',127) |
+--+
|1 |
+--+
1 row in set (0.00 sec)
I noted that mysql can't really dial with unsigned:
(you can't even declare it unsigned)
mysql select or64('9223372036854775809',127);
+-+
| or64('9223372036854775809',127) |
+-+
|-9223372036854775681 |
+-+
1 row in set (0.00 sec)
On Sun, Feb 01, 2004 at 01:09:34AM +0100, Stefan Traby wrote:
Hi !
select 129 127; -- ok(1)
select '129' 127; -- ok(1)
select (0+'129') 127; -- ok(1)
select CAST('129' AS UNSIGNED INTEGER) 127; -- ok(1)
select CONVERT('129', UNSIGNED INTEGER) 127;-- ok(1)
Doing the same with 64 bit values gives strange results:
select 9223372036854775809 127; -- returns 1, correct
select '9223372036854775809' 127; -- ERROR: returns 127
select (0+'9223372036854775809') 127; -- ERROR: returns 0
select CAST('9223372036854775809' AS UNSIGNED INTEGER) 127; -- ERROR: returns 127
select CONVERT('9223372036854775809', UNSIGNED INTEGER) 127;-- ERROR: returns 127
So please tell me how to perform a bitwise 64bit-AND if
a value is quoted.
Never seen such a strange bug for a long time.
Well, I just found it because DBD::mysql quotes large integer
bind-variables even on perl int64...
So do I need to write an UDF-Function to get the correct behavior
or is there another work-arround to fix this?
--
ciao -
Stefan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BUG on 64bit if a value is quoted (4.0.17 / 4.1.2 / 5.0.0)
On Sat, Jan 31, 2004 at 10:06:04PM -0500, Michael Stassen [EMAIL PROTECTED] wrote: Meanwhile, perhaps a simpler workaround would be to change how you build the query in Perl so as to avoid the quotes. Unfortunately, the heuristic that DBD::mysql uses cannot be changed or forced. Currently, if the first execution uses an integer, it will never quote, even when you later use a string and vice versa. This is a correctness issue, as mysql (properly) treats string comparisons different to integer comparisons and thus will give different results. These problems, however, cannot be solved with the current mysql api, as (AFAICS), there is no type information. So interfaces to mysql from languages without strong typing have to second-guess, and do this often wrong. The only clean solution to this problem is providing type information, so DBD::mysql knows that e.g. an integer is expected. -- -==- | ==-- _ | ---==---(_)__ __ __ Marc Lehmann +-- --==---/ / _ \/ // /\ \/ / [EMAIL PROTECTED] |e| -=/_/_//_/\_,_/ /_/\_\ XX11-RIPE --+ The choice of a GNU generation | | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
