Wrong result from a query with an empty derived table

Bug #1002546 reported by Igor Babaev on 2012-05-21
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Confirmed
High
Oleksandr "Sanja" Byelkin

Bug Description

The following test case gives us a wrong result in MariaDB 5.2/5.5

CREATE TABLE t1 (
  pk int NOT NULL,
  col_int_nokey int NOT NULL,
  col_int_key int NOT NULL,
  col_time_key time NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  col_varchar_nokey varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_time_key (col_time_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=MyISAM;

CREATE TABLE t2 (
  pk int NOT NULL AUTO_INCREMENT,
  col_int_nokey int NOT NULL,
  col_int_key int NOT NULL,
  col_time_key time NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  col_varchar_nokey varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_time_key (col_time_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b');

SET @var2:=4, @var3:=8;

MariaDB [test]> SELECT * FROM ( SELECT @var3:=12, sq4_alias1.* FROM t1 AS sq4_alias1 WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR sq4_alias1.col_varchar_key = @var3 ) AS alias3;
+-----------+----+---------------+-------------+--------------+-----------------+-------------------+
| @var3:=12 | pk | col_int_nokey | col_int_key | col_time_key | col_varchar_key | col_varchar_nokey |
+-----------+----+---------------+-------------+--------------+-----------------+-------------------+
| 12 | 0 | 0 | 0 | 00:00:00 | | |
+-----------+----+---------------+-------------+--------------+-----------------+-------------------+

No rows are expected in the result here.

(see also bug #13651000 from mysql-trunk)

Changed in maria:
status: New → Confirmed
importance: Undecided → High
assignee: nobody → Oleksandr "Sanja" Byelkin (sanja-byelkin)
milestone: none → 5.2
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers