Re: possible BUG in 'between' comparisons
I have confirmed that this problem exhibits itself on the Mysql AB
compiled binaries that are compiled with gcc, both Standard and Max, but
does _not_ exhibit itself on the Mysql AB binary built with the Intel C++
Compiler 8.1 (mysql-standard-4.1.12-pc-linux-gnu-i686-icc-glibc23.tar.gz).
I have also verifed that the problem does _not_ exhibit on the mysql-4.12
server in Debian stable (Sarge), nor in Gentoo's latest ebuild.
I believe this problem likely stems from a compiler problem with the version
(2.95.3 20010315 SuSE) of GCC that Mysql AB compiles with.
# GCC: mysql-standard-4.1.12-pc-linux-gnu-i686.tar.gz
[EMAIL PROTECTED]:/usr/local# ls -l mysql
lrwxrwxrwx 1 root root 9 Jul 8 21:25 /usr/local/mysql - mysql-gcc/
[EMAIL PROTECTED]:/usr/local# ./mysql/bin/mysqld_safe --user=mysql
[EMAIL PROTECTED]:/# /usr/local/mysql/bin/mysql test
[...snip...]
mysql select * from foo1 left join foo2 on foo2.test1 between foo1.test1 and
foo1.test2;
+---++---+---+
| test1 | test2 | test1 | test2 |
+---++---+---+
| 97.50 | 154.30 | NULL | NULL |
+---++---+---+
1 row in set (0.00 sec)
[EMAIL PROTECTED]:/usr/local# ./mysql/bin/mysqladmin shutdown
STOPPING server from pid file /usr/local/mysql/data/ponybox.pid
050708 21:30:36 mysqld ended
# ICC: mysql-standard-4.1.12-pc-linux-gnu-i686-icc-glibc23.tar.gz
[EMAIL PROTECTED]:/usr/local# rm mysql ln -s mysql-icc mysql
[EMAIL PROTECTED]:/usr/local# ./mysql/bin/mysqld_safe --user=mysql
[EMAIL PROTECTED]:/# /usr/local/mysql/bin/mysql test
[...snip...]
mysql select * from foo1 left join foo2 on foo2.test1 between foo1.test1 and
foo1.test2;
+---++++
| test1 | test2 | test1 | test2 |
+---++++
| 97.50 | 154.30 | 154.30 | 154.30 |
+---++++
--
Lester Hightower [EMAIL PROTECTED]
10East Corp.
On Fri, 8 Jul 2005 [EMAIL PROTECTED] wrote:
Description:
There appears to be a type promotion problem involving sql statements which
include a 'between' comparison and a decimal type field is one of the operands.
How-To-Repeat:
CREATE TABLE `foo1` (
`test1` decimal(7,2) NOT NULL default '0.00',
`test2` decimal(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo1` (`test1`, `test2`) VALUES ('97.50','154.30');
CREATE TABLE `foo2` (
`test1` decimal(7,2) NOT NULL default '0.00',
`test2` decimal(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo2` (`test1`, `test2`) VALUES ('154.30','154.30');
CREATE TABLE `foo3` (
`test1` float(7,2) NOT NULL default '0.00',
`test2` float(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo3` (`test1`, `test2`) VALUES (97.50,154.30);
CREATE TABLE `foo4` (
`test1` float(7,2) NOT NULL default '0.00',
`test2` float(7,2) NOT NULL default '0.00'
);
INSERT INTO `foo4` (`test1`, `test2`) VALUES (154.30,154.30);
select * from foo1 left join foo2 on foo2.test1 between foo1.test1 and
foo1.test2;
+---++---+---+
| test1 | test2 | test1 | test2 |
+---++---+---+
| 97.50 | 154.30 | NULL | NULL |
+---++---+---+
1 row in set (0.00 sec)
select * from foo3 left join foo4 on foo4.test2 between foo3.test1 and
foo3.test2;
+---++++
| test1 | test2 | test1 | test2 |
+---++++
| 97.50 | 154.30 | 154.30 | 154.30 |
+---++++
1 row in set (0.00 sec)
mysql select * from foo1 where test1 between 97.50 and 154.30;
+---++
| test1 | test2 |
+---++
| 97.50 | 154.30 |
+---++
1 row in set (0.00 sec)
mysql select * from foo1 where 154.30 between test1 and test2;
Empty set (0.00 sec)
mysql select * from foo1 where test2 between 154.30 and test2;
Empty set (0.00 sec)
Fix:
A work around is to include OR conditions that are equal comparisons to the
boundary condition of the BETWEEN. This query demonstrates a work around.
mysql select * from foo1 where test2 between 154.30 and test2 or 154.30=test2;
+---++
| test1 | test2 |
+---++
| 97.50 | 154.30 |
+---++
1 row in set (0.00 sec)
Originator: Tom Allen
Organization:
10 East Corp
MySQL support: none
Synopsis: BETWEEN comparisons with one or more DECIMAL type fields as
operands don't handle boundary conditions properly
Severity: [ non-critical | serious | critical ] (one line)
Priority: [ low | medium | high ] (one line)
Category: mysql
Class: [ sw-bug | doc-bug | change-request | support ] (one line)
Release:mysql-4.1.12-max (MySQL Community Edition - Experimental (GPL))
C compiler:2.95.3
C++ compiler: 2.95.3
Environment:
machine, os, target, libraries (multiple lines)
System: Linux docs2 2.6.12 #1 SMP Mon Jun 20 12:08:43 EDT 2005 i686 unknown
unknown GNU/Linux
Architecture: i686
Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs
Feature request related to COMPRESS and UNCOMPRESS functions
To whom it may concern at Mysql AB: I see from the Mysql 4.1.1 CHANGELOG that new COMPRESS(), UNCOMPRESS(), and UNCOMPRESSED_LENGTH() functions were added. That is great news, and something I have been very interested in for a long time, as evidenced by this mysql mailing list thread, dating back to 12/18/2001: http://marc.10east.com/?t=10086980305r=1w=2 The MARC system (marc.10east.com) was one of the primary reasons for me requesting that this feature be added to Mysql. There is one short-coming in the new COMPRESS()/UNCOMPRESS() functionality that I would like to point out, and request that you address. Here is the background: Anyone that is running a huge system like MARC that has millions of uncompressed blob records in huge tables, needs to be able to migrate, in real-time and without down-time, to compressed blobs. Therefore, we need a way to know if a given field is compressed or not. Running alter table on our tables, to add an am_i_compressed boolean, not only takes an excruciating amount of time and resources, but adds bits to each record that, from a disk-space perspective, we cannot afford. Instead, I would like to be able to run a query like: select IFCOMPRESSED(msg_body, UNCOMPRESS(msg_body), msg_body) from msg_bodies_200402 where clause Note that the IFCOMPRESSED() function is the key, and what I am requesting be added to future versions of Mysql. That function has to be possible. Hopefully you are storing a header with your compressed data, and if so, then the IFCOMPRESSED() is trivial to implement. If you are not storing a header with your compressed data, then this might be more complicated. Adding a header might be a possibility -- which is why I am trying to point this out _EARLY_ in the process before lots of people start using COMPRESS()/UNCOMPRESS(), or maybe zlib, lzo, or whatever library you are using can let you know if the data is compressed -- maybe they store a small header themselves. Anyway, that is the issue that I want to point out and ask for assistance on. Thank you very much for listening to the user community and adding COMPRESS()/UNCOMPRESS(), and please seriously consider this request. Sincerely, -- Lester H. Hightower [EMAIL PROTECTED] Chief Technology Officer, 10 East Corp. p.s. Could someone at Mysql AB update the URL at the bottom of this page, http://lists.mysql.com/, to point to http://marc.10east.com/ instead of http://marc.theaimsgroup.com/? That is our old company name, and we are always trying to reduce usage on that domain name. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Help w/UDFs, compress_gz(string,level) uncompress_gz(string)
I just discovered that @lists.mysql.com accepts no attachments... Please disregard the comments below regarding the code being attached, and instead please find it at this URL: http://www.10east.com/~hightowe/mysqld_udf_zlib.cc -- Lester Hightower [EMAIL PROTECTED] -- Forwarded message -- Date: Tue, 5 Mar 2002 10:47:40 -0500 (EST) From: Lester Hightower [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Help with UDFs, compress_gz(string,level) uncompress_gz(string) On December 18, 2001 I emailed [EMAIL PROTECTED] regarding a pair of user defined functions, compress_gz() and uncompress_gz() that I wrote. That email indicated that, after a few days of testing and cleaning up the code, that I would contribute it. During said testing I discovered that the code is not thread-safe. The code works perfectly as long as it is only used by one thread; it reliably fails, and takes the mysqld with it, if used by 1 threads concurrently. I must first admit that I do very little C programming now-a-days, and even far less multi-threaded programming. That being the case, I am afraid that I am ill-equipped to debug this code, at least not in a timely enough fashion to allow me to ever actually get it done... My suspicion is that my problem might be in zlib. The zlib docs say that it is thread-safe, so maybe I am compiling it incorrectly. I asked a friend, who felt absolutely positive that he had a thread-safe zlib, to try this code. His results differed from mine -- the mysqld did not crash, but he produced dramatic table corruption. This code is just not that complex, and it surprises me that it has been such a pain. I really need this functionality and hope that maybe someone else on this list, with more experience in this area, either could use this functionality also (and can spend some time finding the problem), or can just look at the code for a minute and, by experience, just see the problem. ...and please email a patch to me! :) The code is attached as an Unix ASCII text file, mysqld_udf_zlib.cc. Thanks in advance to anyone who might be able to help. Sincerely, -- Lester Hightower [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Posibility of adding compress_*()/decompress_*() functions
On Wed, 19 Dec 2001, Sinisa Milivojevic wrote: Lester Hightower writes: Hi all, I would like to propose that mysql have compress_*()/decompress_*() functions added that would be similar to des_encrypt()/des_decrypt(). My idea would be to have functions available like: compress_gz()/decompress_gz() compress_bz()/decompress_bz() ...etc. [skip] Sincerely, -- Lester Hightower TheAIMSGroup.com Hi! This is, of course, doable especially .._gz() functions. Our TODO list is quite long, so this will not be done soon. Sinisa, Thanks for the reply. You may have not seen it yet, but I sent a followup email stating that, after sending my first email, I had discovered mysqld user-defined-functions (which I did not know existed) and then went ahead and coded compress_gz()/decompress_gz() myself, yesterday afternoon, as UDFs in a .so library. In that email I also offered to contribute the code to Mysql AB, if you guys would like to have it. Maybe you would like to offer it in your cntributed software area. Are you interested? If so, email it to you? Sincerely, -- Lester H. Hightower TheAIMSGroup.com Chief Technology Officer The Advanced Integrated Management Solutions Group - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Posibility of adding compress_*()/decompress_*() functions
Hi all, I would like to propose that mysql have compress_*()/decompress_*() functions added that would be similar to des_encrypt()/des_decrypt(). My idea would be to have functions available like: compress_gz()/decompress_gz() compress_bz()/decompress_bz() ...etc. I have read of people doing this in their applications: http://marc.theaimsgroup.com/?l=mysqlm=100823186816632w=2 ...but I could really use the feature directly in SQL. The ability to do something like: mysql SELECT event_id,decompress_gz(event_text) from events where event_date between 20011201 and 20011210; assuming that event_text is a *char and that records are always inserted using the compress_gz() function. Doing it this way (as a function) would allow people to still use software algorithms outside of MySQL if they wanted/when appropriate. The disadvantage is that you must be consistent in your use of the fields, and Mysql will need a defined behavior for when that fails (when one tries to decompress_gz a non-gz-compressed field). The other option is to have extended string types that are defined like: event_text varchar(255) not null default '' compress_gz, event_blob text not null default '' compress_bz, and then have Mysql handle the compression/decompression internally, and disallow using those fields in WHERE clauses. I personally prefer the former option (compress/decompress functions) because I prefer having the flexibility in exchange for taking the responsibility, but either method would be useful to me. Here is what prompted this request: root@# ls -l events.* events_raw.* | cut -b30-500 63571071 Dec 18 12:29 events.MYD 63397888 Dec 18 12:29 events.MYI 9119 Dec 6 18:15 events.frm 92184576 Dec 18 12:29 events_raw.MYD 12948480 Dec 18 12:29 events_raw.MYI 8602 Nov 21 00:19 events_raw.frm I have been working on a project where this events table, which has ~1.5 million records and grows by ~100k/per day, started out at about 600MB. I rearranged the data, moved large text strings out into associated tables with numeric IDs and joins, etc. Now the events table is much more scalable, and the system it supports much faster. The events_raw table, which has two fields (event_id,event_text), holds the raw event message that was received (before it was parsed and stored) which is extremely useful for debugging. The events_raw table is now the scalability problem, as it will hit a file size limit long before events. The events_raw table is only searched using a join on events.event_id=events_raw.event_id, so if I could compress the events_raw.event_text field in the table, problem solved. So, my example above would be more accurate for my case if written as: mysql SELECT e.event_id, decompress_gz(r.event_text) as original_event_text from events as e left join events_raw as r on e.event_id=r.event_id where e.event_date between 20011201 and 20011210; I think we could benefit from this feature in our MARC system, as well. marc.theaimsgroup.com Thanks for your time and consideration. Sincerely, -- Lester Hightower TheAIMSGroup.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql compress_gz()/uncompress_gz() user defined functions
After sending my earlier email regarding compress_gz()/uncompress_gz() in
mysqld, I discovered user defined functions -- something I had never used
in mysqld until today.
So, I took a couple of hours and wrote a Mysql UDF to do what I requested:
# gcc -shared -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\
-DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\
-DHAVE_CONFIG_H -I./../include -I./../regex -I. -I../include -I.. -I.
-I/usr/local/mysql/include -O3 -DDBUG_OFF -export-dynamic -fPIC
-fno-implicit-templates -o mysqld_udf_zlib.so mysqld_udf_zlib.cc /usr/lib/libz.a
# cp ./mysqld_udf_zlib.so /usr/lib; ldconfig
# mysql mysql
mysql CREATE FUNCTION compress_gz RETURNS STRING SONAME mysqld_udf_zlib.so; CREATE
FUNCTION uncompress_gz RETURNS STRING SONAME mysqld_udf_zlib.so;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql select compress_gz('lester');
+---+
| compress_gz('lester') |
+---+
| xËI-.I-a |
+---+
1 row in set (0.00 sec)
mysql select uncompress_gz(compress_gz('lester'));
+--+
| uncompress_gz(compress_gz('lester')) |
+--+
| lester |
+--+
1 row in set (0.01 sec)
mysql drop function compress_gz; drop function uncompress_gz;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
It is late and I am going home for the evening. Tomorrow I will review
the code carefully, clean it up a bit, comment it well, and will then be
happy to contribute it to MySQL AB, if you guys are interested in having
it in the contributed software page. Is there a procedure for contribing
the code to you guys, or just email it to the mysql list, or what?
--
Lester H. Hightower TheAIMSGroup.com
Chief Technology Officer
The Advanced Integrated Management Solutions Group
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
