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]
BUG on 64bit if a value is quoted (4.0.17 / 4.1.2 / 5.0.0)
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]
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]
optimizer: GROUP BY unnessesary slow using ANSI notation for redundant group by's.
Hi ! For my forum system, I use the following query to generate the main-overview: (using 4.0.16-log) select f1.id as BoardId, f1.name as Board, f1.more as BoardDesc, f2.id as AreaId, f2.name as Area, f2.more as AreaDesc, count(distinct f3.id) as ThemenCount, count(distinct m1.ctime) as MessageCount, max(m1.ctime) as LastMessageStamp from forum as f1, forum as f2 left join forum as f3 on (f3.rid = f2.id) left join forum_msg as m1 on (m1.fid = f3.id) where f1.rid = 0 and f2.rid = f1.id group by AreaId -- note ANSI: group by AreaId, Area, AreaDesc order by BoardId, AreaId; ANSI requires to use group by AreaId, Area, AreaDesc instead of group by AreaId (which is a documented MySQL shortcut against this redundancy) but the ANSI notation is ~4 times slower. This performance penalty is really unnessesary because the optimizer could detect this kind of redundancy in many cases, especially this simple case because group by f2.id generates clearly the same results as group by f2.id, f2.name, f2.more does. I think it's really *bad* to allow the non-ANSI shortcut and to not detect the ANSI-notation as redundant! shame on you :) :) -- 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]
[mysql 5.0, connection loss, procedure-related, easy to reproduce, detailed report] what you ever wanted to know about trees
Hi !
mysql 5.0 is _really_ cool.
this bug should be reproducable:
( cat table.sql ; ./populate.pl ; ./perfect_game.pl 32 ) | mysql test
after that executing the same procedure with same parameter again,
the connection is lost:
--
mysql call self_and_up(1);
+---+
| id|
+---+
| 1 |
| 1999 |
| 399 |
|79 |
|15 |
| 2 |
+---+
6 rows in set (0.20 sec)
Query OK, 0 rows affected (0.20 sec)
mysql call self_and_up(1);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql
--
use cut and paste:
==
table.sql
==
-- table.sql
drop table if exists node;
create table node ( id int unsigned not null,
rid int unsigned not null,
msg text not null,
primary key(id),
unique index(rid,id)
);
--
==
populate.pl
==
#!/usr/bin/perl
# populate.pl - [EMAIL PROTECTED]
our $level = 0;
our $parent = 0;
our @nodes;
sub rec() {
for my $i (1..$lim) {
my $node = $parent*$lim+$i;
$nodes[$level] = $node;
print insert into node (id, rid, msg) values ($node, $parent, \.join(-, 0,
@nodes[0..$level]).\);\n;
{
local $parent = $node;
local $level = $level+1;
rec() if $level $rec_depth;
}
}
}
sub populate_table(@) {
my %args = @_;
{
local $lim = $args{count} || 2;
local $rec_depth = $args{depth} || 2;
rec;
}
}
populate_table count = 5, depth = 6;
--
==
perfect_game.pl
==
#!/usr/bin/perl
# perfect_game.pl - [EMAIL PROTECTED]
sub query_parents_union(@) {
my %a = @_;
my $level = $a{max_level} || 0;
my $proc = $a{proc} || dummy;
my $s = select . join(,, map( { n0.$_ } @{$a{fields}}));
my $o = drop procedure if exists $proc;\n
.delimiter |\n
.create procedure $proc (IN v INT)\n
.begin\n;
for(my $x = 0; $x = $level; $x++) {
$o .= \n($s\n\tfrom .join(, , map( { $a{table} as n$_ } (
0..$x))).\n\twhere (
.\n\t\t(
.join( and , map( { n$_.$a{id} = .(($_ = $x) ? v :
n.($_+1)..$a{rid}) } (0..$x))).)
.\n\t\t)\n);
$o .=\nunion if $x != $level;
}
$o
.;\n
.end |\n
.delimiter ;\n
}
my $limit = $ARGV[0] || 0;
print query_parents_union(table = 'node', id = 'id', rid = 'rid', fields = [
'id' ], proc = self_and_up, max_level = $limit);
--
--
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]
