| Bug #20835 | Subqueries: literal string with '=any' fails | ||
|---|---|---|---|
| Submitted: | 3 Jul 2006 23:10 | Modified: | 14 Dec 2006 3:03 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1.12-beta-debug | OS: | Linux (SUSE 10.0 / 64-bit) |
| Assigned to: | Alexey Botchkov | CPU Architecture: | Any |
[4 Jul 2006 17:58]
Tonci Grgin
Hi Peter. I was unable to repeat this behavior. Server is 5.1.12 BK on FC5 x64 compiled with compile-amd64-debug-max-no-ndb script. Started with --no-defaults option. Unfortunately I don't have Suse 10 x64 installed.
bk changes | head
ChangeSet@1.2231, 2006-07-01 19:47:24-04:00, mikael@dator5.(none)
Merge mronstrom@bk-internal.mysql.com:/home/bk/mysql-5.1
into dator5.(none):/home/pappa/bug17138
ChangeSet@1.2226.2.2, 2006-07-01 00:28:04-04:00, mikael@dator5.(none)
Merge dator5.(none):/home/pappa/bug20583
into dator5.(none):/home/pappa/bug17138
ChangeSet@1.2226.3.1, 2006-07-01 00:22:25-04:00, mikael@dator5.(none)
Merge dator5.(none):/home/pappa/clean-mysql-5.1
More OS info:
[root@FC5X64 mysql51server]# uname --all
Linux FC5X64 2.6.16-1.2111_FC5 #1 SMP Thu May 4 21:16:04 EDT 2006 x86_64 x86_64 x86_64 GNU/Linux
[root@FC5X64 mysql51server]# /lib/libc.so.6
GNU C Library development release version 2.4, by Roland McGrath et al.
Copyright (C) 2006 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE.
Compiled by GNU CC version 4.1.0 20060304 (Red Hat 4.1.0-2).
Compiled on a Linux 2.6.9 system on 2006-03-07.
Available extensions:
The C stubs add-on version 2.1.2.
crypt add-on version 2.1 by Michael Glad and others
GNU Libidn by Simon Josefsson
GNU libio by Per Bothner
NIS(YP)/NIS+ NSS modules 0.19 by Thorsten Kukuk
Native POSIX Threads Library by Ulrich Drepper et al
BIND-8.2.3-T5B
Thread-local storage support included.
Test:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.1.12-beta-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select version();
+-------------------+
| version() |
+-------------------+
| 5.1.12-beta-debug |
+-------------------+
1 row in set (0.00 sec)
mysql> drop table if exists t;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-------------------+
| Level | Code | Message |
+-------+------+-------------------+
| Note | 1051 | Unknown table 't' |
+-------+------+-------------------+
1 row in set (0.00 sec)
mysql> create table t (s1 char(1));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t values ('a');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t where 'a' = any (select s1 from t);
+------+
| s1 |
+------+
| a |
+------+
1 row in set (0.00 sec)
mysql>
[5 Jul 2006 16:48]
MySQL Verification Team
latest 5.1.12 source on 32-bit linux: <cut> mysql> select * from t where 'a' = any (select s1 from t); +------+ | s1 | +------+ | a | +------+ 1 row in set (0.00 sec)
[5 Jul 2006 16:54]
Peter Gulutzan
Tonci Grgin made the excellent suggestion that I should look at my.cnf.
Thanks, Tonci. This is what's in /etc/my.cnf:
"
[server]
default-storage-engine=myisam
[client]
default-character-set=utf8
"
So now I think I can see why the bug couldn't be repeated
earlier, and here is a new test case with more complete
information.
mysql> show variables like '%char%';
+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/local/mysql/share/mysql/charsets/ |
+--------------------------+----------------------------------------+
8 rows in set (0.00 sec)
mysql> create table t (s1 char(1));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t values ('a');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t where 'a' = any (select s1 from t);
Empty set (0.01 sec)
mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where 'a' = any (select s1 from t);
+------+
| s1 |
+------+
| a |
+------+
1 row in set (0.00 sec)
[10 Jul 2006 7:43]
Tonci Grgin
Analyzing with new info provided.
[10 Jul 2006 8:45]
Tonci Grgin
Verified as described by reporter, *ANY* processing seems to be broken in SB server / MB client configuration.
C:\mysql507\bin>mysql -uroot -hmunja --port=3307 --default-character-set=utf8
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show variables like "%char%";
+--------------------------+----------------------------------------------------
-------+
| Variable_name | Value
|
+--------------------------+----------------------------------------------------
-------+
| character_set_client | utf8
|
| character_set_connection | utf8
|
| character_set_database | latin1
|
| character_set_filesystem | binary
|
| character_set_results | utf8
|
| character_set_server | latin1
|
| character_set_system | utf8
|
| character_sets_dir | /home/Tonci/bkwork/copyto/mysql-5-1/share/mysql/cha
rsets/ |
+--------------------------+----------------------------------------------------
-------+
8 rows in set (0.00 sec)
In addition, following tests succede:
mysql> select 8 from t where s1 = 'a';
+---+
| 8 |
+---+
| 8 |
+---+
1 row in set (0.00 sec)
mysql> select * from t where s1 = 'a';
+------+
| s1 |
+------+
| a |
+------+
1 row in set (0.00 sec)
mysql> select * from t where 'a' = 'a';
+------+
| s1 |
+------+
| a |
+------+
1 row in set (0.00 sec)
mysql> select * from t where s1 in (select * from t);
+------+
| s1 |
+------+
| a |
+------+
1 row in set (0.00 sec)
while reported one fails:
mysql> select * from t where 'a' = any (select s1 from t);
Empty set (0.00 sec)
Last test conducted on 32bit Suse 10 so it's safe to assume it has nothing to do with x64 platform.
[24 Jul 2006 17:14]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/9513
[3 Dec 2006 17:16]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/16357 ChangeSet@1.2380, 2006-12-03 21:49:26+04:00, holyfoot@mysql.com +3 -0 bug #20835 (Subqueries: literal string with =any fails) We create Item_cache_* object for each operand for each left operand of a subquery predicate. We also create Item_func_conv_charset for each string constant that needs charset conversion. So here we have Item_cache wrapped into Item_func_conv_charset. When Item_func_conv_charset wraps an constant Item it gets it's value in constructor. The problem is that Item_cache is ready to be used only at execution time, which is too late. The fix makes Item_cache wrapping constant to get ready at fix_fields() time.
[13 Dec 2006 12:32]
Sergei Glukhov
Fixed in 5.1.15-beta
[14 Dec 2006 3:03]
Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.
If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at
http://dev.mysql.com/doc/en/installing-source.html
Documented bugfix in 5.1.15 changelog.
[31 Oct 2008 13:13]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/57555 2715 Sergey Glukhov 2008-10-31 Bug#34760 Character set autodetection appears to fail the problem is the same as reported in bug#20835, so the fix is backport of bug#20835 patch.
[21 Nov 2008 12:41]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/59513 2720 Sergey Glukhov 2008-11-21 Bug#34760 Character set autodetection appears to fail the problem is the same as reported in bug#20835, so the fix is backport of bug#20835 patch.
[2 Dec 2008 13:01]
Bugs System
Pushed into 5.0.74 (revid:sergey.glukhov@sun.com-20081121123959-58ffhp2nitg7f40h) (version source revid:ramil@mysql.com-20081121132058-12sfo07z1r1yvelk) (pib:5)
[8 Dec 2008 10:21]
Bugs System
Pushed into 5.1.31 (revid:sergey.glukhov@sun.com-20081121123959-58ffhp2nitg7f40h) (version source revid:patrick.crews@sun.com-20081126180318-v685u61mpgoc176x) (pib:5)
[8 Dec 2008 11:32]
Bugs System
Pushed into 6.0.9-alpha (revid:sergey.glukhov@sun.com-20081121123959-58ffhp2nitg7f40h) (version source revid:ingo.struewing@sun.com-20081121151447-dtf2ofz2ys0zqed1) (pib:5)
[19 Jan 2009 11:28]
Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:06]
Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:12]
Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)
Description: If I say "integer_literal = any (select ...)", I get results.. If I say "string_literal = (select ...)", I get results. If I say "string_literal = any (select ...)", I get no results. The "=ANY" condition should work with CHAR, VARCHAR, BINARY, VARBINARY. How to repeat: mysql> create table t (s1 char(1)); Query OK, 0 rows affected (0.00 sec) mysql> insert into t values ('a'); Query OK, 1 row affected (0.00 sec) mysql> select * from t where 'a' = any (select s1 from t); Empty set (0.00 sec)