2 messages in com.mysql.lists.bugsBUG: Probem with nested string functions| From | Sent On | Attachments |
|---|---|---|
| eb...@pemic.de | 10 Apr 2000 04:55 | |
| Michael Widenius | 10 Apr 2000 06:07 |
| Subject: | BUG: Probem with nested string functions |
|---|---|
| From: | eb...@pemic.de (eb...@pemic.de) |
| Date: | 04/10/2000 04:55:25 AM |
| List: | com.mysql.lists.bugs |
Description:
When I nest string expressions in the WHERE part of my query, it has some strange behaviour. For example for expressions like: concat('@', trim(leading '.' from concat('.', domain))) where "domain" is a field of a table.
How-To-Repeat:
mysql> create table test ( domain char(50) ); mysql> insert into test VALUES ("hello.de" ); mysql> insert into test VALUES ("test.de" );
The first query, works as intended:
mysql> select domain from test -> where concat('@', trim(leading '.' from concat('.', domain))) = -> '@hello.de'; +----------+ | domain | +----------+ | hello.de | +----------+ 1 row in set (0.00 sec)
Now here comes a second, almost identical query, but querying for another (the second) row:
mysql> select domain from test -> where concat('@', trim(leading '.' from concat('.', domain))) -> = '@test.de'; Empty set (0.00 sec)
WOW! Empty! What now? I can add as much rows as I want to this table, but with this query I just can get a positive result for the FIRST row.
Very strange, and just simple changes to the query make it work again. Removing the '@':
mysql> select domain from test -> where concat('', trim(leading '.' from concat('.', domain))) -> = 'test.de'; +---------+ | domain | +---------+ | test.de | +---------+ 1 row in set (0.00 sec)
Or the "." from the second concat:
mysql> select domain from test -> where concat('@', trim(leading '.' from concat('', domain))) -> = '@test.de'; +---------+ | domain | +---------+ | test.de | +---------+ 1 row in set (0.00 sec)
Fix:
I have no idea, sorry! :)
Submitter-Id: <submitter ID>
Originator: Ernesto Baschny
Organization: PEM InterComputing GmbH
MySQL support: none
Synopsis: Problem with WHERE clauses with nested string functions (CONCAT()
and TRIM())
Severity: non-critical
Priority: low
(but HIGH for me, of course :)) )
Category: mysql
Class: sw-bug
Release: mysql-3.22.30 (Source distribution)
Server: /usr/bin/mysqladmin Ver 7.15 Distrib 3.22.30, for pc-linux-gnu on i686
TCX Datakonsult AB, by Monty
Server version 3.22.30 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 13 days 3 hours 28 min 47 sec
Threads: 1 Questions: 1033558 Slow queries: 0 Opens: 4847 Flush tables: 1
Open tables: 63
Environment:
SuSE Linux 6.3 System: Linux tux 2.2.13 #4 Mon Mar 6 13:38:44 MET 2000 i686 unknown Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/egcs-2.91.66/specs
gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
Configure command: ./configure --enable-shared --enable-assembler --prefix=/usr
--libdir=/usr/lib --libexecdir=/usr/sbin --sysconfdir=/etc
--localstatedir=/var/mysql --infodir=/usr/info --without-debug
--with-mysqld-user=mysql
Perl: This is perl, version 5.005_03 built for i586-linux