[MDEV-746] LP:1002546 - Wrong result from a query with an empty derived table - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 5.5.28a, 5.3.10, 5.2.13
Component/s: None
Labels:
- Launchpad

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)

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

LPexportBug1002546.xml
4 kB
2012-10-03 11:32

Activity

Ascending order - Click to sort in descending order

Igor Babaev (Inactive) created issue - 2012-05-21 22:16

Rasmus Johansson (Inactive) added a comment - 2012-05-21 22:16

Launchpad bug id: 1002546

Rasmus Johansson (Inactive) added a comment - 2012-05-21 22:16 Launchpad bug id: 1002546

Rasmus Johansson (Inactive) made changes - 2012-10-04 11:35

Field	Original Value	New Value
Labels		Launchpad

Rasmus Johansson (Inactive) made changes - 2012-10-04 11:45

Fix Version/s	Maria 5.2 [ 11301 ]
Labels	Launchpad	Launchpad MariaDB_5.2

Rasmus Johansson (Inactive) made changes - 2012-10-04 13:14

Key	~~IMT-6571~~	~~MDEV-746~~
Project	ImportTest [ 10200 ]	MariaDB Development [ 10000 ]
Workflow	jira [ 20220 ]	defaullt [ 21590 ]

Sergei Golubchik made changes - 2012-10-04 14:59

Fix Version/s		5.5.29 [ 11701 ]
Fix Version/s		5.3.10 [ 11500 ]
Fix Version/s		5.2.13 [ 10800 ]

Sergei Golubchik made changes - 2012-10-06 15:26

Labels

Launchpad MariaDB_5.2

Launchpad

Oleksandr Byelkin made changes - 2012-10-14 12:00

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin made changes - 2012-10-14 12:50

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)

The following test case gives us a wrong result in MariaDB 5.2/5.5
{noformat}
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;
{noformat}
{noformat}
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 | | |
+-----------+----+---------------+-------------+--------------+-----------------+-------------------+
{noformat}
No rows are expected in the result here.

(see also bug #13651000 from mysql-trunk)

Oleksandr Byelkin added a comment - 2012-10-14 12:54

It is MySQL Bug#13651009 (last digit is nine)

Oleksandr Byelkin added a comment - 2012-10-14 12:54 It is MySQL Bug#13651009 (last digit is nine)

Oleksandr Byelkin added a comment - 2012-10-14 13:03

MySQL patch looks OK.

Oleksandr Byelkin added a comment - 2012-10-14 13:03 MySQL patch looks OK.

Oleksandr Byelkin made changes - 2012-10-14 13:47

Status

In Progress [ 3 ]

Open [ 1 ]

Oleksandr Byelkin made changes - 2012-10-14 19:00

Status

Open [ 1 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2012-10-14 19:30

Mysql patch checked and history of previous changes. Commited. pushed.

Oleksandr Byelkin added a comment - 2012-10-14 19:30 Mysql patch checked and history of previous changes. Commited. pushed.

Oleksandr Byelkin made changes - 2012-10-14 19:30

Status

In Progress [ 3 ]

Open [ 1 ]

Oleksandr Byelkin added a comment - 2012-10-15 13:49

checked by buildbot

Oleksandr Byelkin added a comment - 2012-10-15 13:49 checked by buildbot

Oleksandr Byelkin made changes - 2012-10-15 13:49

Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 21590 ]

MariaDB v2 [ 45755 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:51

Workflow

MariaDB v2 [ 45755 ]

MariaDB v3 [ 62955 ]

Sergei Golubchik made changes - 2021-12-06 21:37

Workflow

MariaDB v3 [ 62955 ]

MariaDB v4 [ 145037 ]

People

Assignee:: Oleksandr Byelkin

Reporter:: Igor Babaev (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2012-05-21 22:16

Updated:: 2012-10-15 13:49

Resolved:: 2012-10-15 13:49

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration