MySQL Bugs: #69319: SELECT with LEFT JOIN return 0

Bug #69319	SELECT with LEFT JOIN return 0 - records
Submitted:	25 May 2013 18:08	Modified:	27 May 2013 9:04
Reporter:	Tomasz Gawron	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.6.11-embedded	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
I recently installed mysql version 5.6.11

SELECT with LEFT JOIN and one WHERE <conditons_1> return 2 records
SELECT with LEFT JOIN and other WHERE <conditon_2> return 2 the same records
but
SELECT with LEFT JOIN and WHERE <conditon_1> AND <conditon_2> return 0 records !

There was no problem in version 4.1

How to repeat:
CREATE TABLE a (
	a int not null,
	b int,
	c int,
	d int,
	PRIMARY KEY(a),
	KEY(b,c)
	)

CREATE TABLE b (
	e int not null auto_increment,
	f int,
	PRIMARY KEY(e),
	KEY(f)
	)

INSERT INTO a (a,b,c,d) VALUES (1,2,1,0)
INSERT INTO a (a,b,c,d) VALUES (2,4,1,0)

INSERT INTO b (f) VALUES (1)
INSERT INTO b (f) VALUES (1)
INSERT INTO b (f) VALUES (1)
INSERT INTO b (f) VALUES (1)

SELECT a.*,b.* FROM a left join b on b.e=a.b WHERE a.c=1
-- First select return 2 records from a

SELECT a.*,b.* FROM a left join b on b.e=a.b WHERE b.f=1
-- second return 2 records from a

SELECT a.*,b.* FROM a left join b on b.e=a.b WHERE a.c=1 and b.f=1
-- last one return 0 records !

Suggested fix:
SELECT a.*,b.* FROM a left join b on b.e=a.b WHERE a.c=1 and ifnull(b.f,0)=1

That select return 2 records but this is not a solution.
This is a big problem to find where else can occur.
Plese help, I'm losing my trust to mysql.

Or is it a problem of optimizing query ?
Is it only in embedded version ? (I tested my software in embedded version
before I send it to my clients)

Could you please provide the output of:

SHOW VARIABLES LIKE "%VERSION%";

mysql> SHOW VARIABLES LIKE "%VERSION%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.11                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.6.11                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE a (a int not null, b int, c int, d int, PRIMARY KEY(a),KEY(b,c));
Query OK, 0 rows affected (0.58 sec)

mysql> CREATE TABLE b (e int not null auto_increment,f int,PRIMARY KEY(e),KEY(f));
Query OK, 0 rows affected (0.53 sec)

mysql> INSERT INTO a (a,b,c,d) VALUES (1,2,1,0);
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO a (a,b,c,d) VALUES (2,4,1,0);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO b (f) VALUES (1);
Query OK, 1 row affected (0.33 sec)

mysql> INSERT INTO b (f) VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO b (f) VALUES (1);
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO b (f) VALUES (1);
Query OK, 1 row affected (0.05 sec)

mysql>
mysql> SELECT a.*,b.* FROM a left join b on b.e=a.b WHERE a.c=1;
+---+------+------+------+------+------+
| a | b    | c    | d    | e    | f    |
+---+------+------+------+------+------+
| 1 |    2 |    1 |    0 |    2 |    1 |
| 2 |    4 |    1 |    0 |    4 |    1 |
+---+------+------+------+------+------+
2 rows in set (0.03 sec)

mysql> -- First select return 2 records from a
mysql>
mysql> SELECT a.*,b.* FROM a left join b on b.e=a.b WHERE b.f=1;
+---+------+------+------+------+------+
| a | b    | c    | d    | e    | f    |
+---+------+------+------+------+------+
| 1 |    2 |    1 |    0 |    2 |    1 |
| 2 |    4 |    1 |    0 |    4 |    1 |
+---+------+------+------+------+------+
2 rows in set (0.06 sec)

mysql> -- second return 2 records from a
mysql>
mysql> SELECT a.*,b.* FROM a left join b on b.e=a.b WHERE a.c=1 and b.f=1;
+---+------+------+------+------+------+
| a | b    | c    | d    | e    | f    |
+---+------+------+------+------+------+
| 1 |    2 |    1 |    0 |    2 |    1 |
| 2 |    4 |    1 |    0 |    4 |    1 |
+---+------+------+------+------+------+
2 rows in set (0.00 sec)

mysql> -- last one return 0 records !
mysql>

mysql> SELECT a.*,b.* FROM a left join b on b.e=a.b WHERE a.c=1;
+---+------+------+------+------+------+
| a | b    | c    | d    | e    | f    |
+---+------+------+------+------+------+
| 1 |    2 |    1 |    0 |    2 |    1 |
| 2 |    4 |    1 |    0 |    4 |    1 |
+---+------+------+------+------+------+
2 rows in set (0.00 sec)

mysql> -- First select return 2 records from a
mysql>
mysql> SELECT a.*,b.* FROM a left join b on b.e=a.b WHERE b.f=1;
+---+------+------+------+------+------+
| a | b    | c    | d    | e    | f    |
+---+------+------+------+------+------+
| 1 |    2 |    1 |    0 |    2 |    1 |
| 2 |    4 |    1 |    0 |    4 |    1 |
+---+------+------+------+------+------+
2 rows in set (0.00 sec)

mysql> -- second return 2 records from a
mysql>
mysql> SELECT a.*,b.* FROM a left join b on b.e=a.b WHERE a.c=1 and b.f=1;
+---+------+------+------+------+------+
| a | b    | c    | d    | e    | f    |
+---+------+------+------+------+------+
| 1 |    2 |    1 |    0 |    2 |    1 |
| 2 |    4 |    1 |    0 |    4 |    1 |
+---+------+------+------+------+------+
2 rows in set (0.00 sec)

mysql> -- last one return 0 records !
mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.6.11                       |
| protocol_version        | 10                           |
| version                 | 5.6.11-embedded              |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
6 rows in set (0.00 sec)

mysql>

I don't now how to use mysql.exe command line tool with embedded server.
If is this possible, please help.

Here some information about my enviroment
System - Windows 7 Professional 64

Base: digsys
File: db.opt
default-character-set=cp1250
default-collation=cp1250_polish_ci

This is list variables from my program

innodb_version              5.6.11
protocol_version            10
version                     5.6.11-embedded
version_comment             MySQL Community Server (GPL)
version_compile_machine     x86
version_compile_os          Win32

structure folders on disk
.\databases\client1\mysql
.\databases\client1\performace_schema
                .........
           (all files copy from folder data after pure instalation .msi
            mysql 5.6.11 x86 after stop instance of server
            as files from data folder in zip generate errors inodb)
.\share\charsets          - from zip (no install)
.\share\polish            - from zip (no install)
.\libmysqld.dll           - (2013-04-05 15:33:36) from zip (no install)
.\myappl.exe

I don't add any other folders like .\plugin (do I have to ?)
And some connection settings

CREATE DATABASE `digsys` CHARACTER SET cp1250 COLLATE cp1250_polish_ci

SET NAMES 'cp1250'
SET BIG_TABLES=1
SET SQL_BIG_SELECTS=1
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

It's all. (sorry my english)

Yes! I worked out the lessons and found mysql-embedded.exe.
In this program, everything works fine!
but the code in mysql-embedded.exe is the same as in libmysqld.dll?
I programm in VisualObjects.
Do I have to write a small application in C to test this error?
Maybe someone can test it for me?
(English - google translator)

Maybe somewhere is version of mysql-embedded-dll.exe uses libmysqld.dll.
It would be helpful to test server library libmysqld.dll