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:
None 
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

[3 Jul 2006 23:10] Peter Gulutzan
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)
[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)