[MDEV-2596] LP:843857 - Wrong result with views + specific join order - JIRA

Details

Type: Bug
Status: Closed
Resolution: Not a Bug
Affects Version/s: None
Fix Version/s: None
Component/s: None
Labels:
- Launchpad

Description

The following query:

SELECT STRAIGHT_JOIN *
FROM view_H
RIGHT JOIN ( view_DD LEFT JOIN BB ON view_DD.b = BB.a )
ON view_H.a = view_DD.c
WHERE view_H.b <> 102;

produces the following plan:

1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (incremental, BNL join)

and the following wrong result:

b

a

b

c

a

-----------------------------+

-312147968	0	7	h	0	know
-312147968	0	7	h	0	j

all other plans produce the following result:

b

a

b

c

a

-----------------------------+

-312147968

0

7

h

0

NULL

repeatable in maria-5.3. not repeatable in maria-5.2, mysql-5.5.

revision-id: <email address hidden>
date: 2011-09-06 20:59:29 +0400
build-date: 2011-09-07 15:56:33 +0300
revno: 3178
branch-nick: maria-5.3

switches:

--join_cache_level=8
--optimizer_switch=join_cache_incremental=ON,join_cache_bka=OFF,join_cache_hashed=OFF

test case:

CREATE TABLE t1 ( a varchar(32)) ;
INSERT INTO t1 VALUES ('know'),('j');

CREATE TABLE t2 ( b int, a int) ;
INSERT INTO t2 VALUES ('-312147968','but');

CREATE TABLE t3 ( a int NOT NULL , b varchar(32), c int) ;
INSERT INTO t3 VALUES (7,'t2',0);

CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2;
CREATE OR REPLACE VIEW v3 AS SELECT * FROM t3;

SELECT STRAIGHT_JOIN *
FROM v2
RIGHT JOIN ( v3 LEFT JOIN t1 ON v3.b = t1.a )
ON v2.a = v3.c
WHERE v2.b <> 102;

Gliffy Diagrams

Attachments

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

Attachments

LPexportBug843857.xml
17 kB
2012-10-03 11:32

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Igor Babaev added a comment - 2011-09-25 01:29

Re: Wrong result with views + specific join order
I failed to reproduce this bug with 5.3 rev.3178:

igor@sophia:~/maria/maria-5.3-r3178/mysql-test> ../client/mysql test
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.3.1-MariaDB-beta-debug Source distribution

This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> CREATE TABLE t1 ( a varchar(32)) ;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> INSERT INTO t1 VALUES ('know'),('j');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]>
MariaDB [test]> CREATE TABLE t2 ( b int, a int) ;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> INSERT INTO t2 VALUES ('-312147968','but');
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [test]>
MariaDB [test]> CREATE TABLE t3 ( a int NOT NULL , b varchar(32), c int) ;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> INSERT INTO t3 VALUES (7,'t2',0);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]>
MariaDB [test]> CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE OR REPLACE VIEW v3 AS SELECT * FROM t3;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]>
MariaDB [test]> SELECT STRAIGHT_JOIN *
-> FROM v2
-> RIGHT JOIN ( v3 LEFT JOIN t1 ON v3.b = t1.a )
-> ON v2.a = v3.c
-> WHERE v2.b <> 102;
-----------------------------+

b

a

b

c

a

-----------------------------+

-312147968

0

7

t2

0

NULL

-----------------------------+
1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED SELECT STRAIGHT_JOIN * FROM v2 RIGHT JOIN ( v3 LEFT JOIN t1 ON v3.b = t1.a ) ON v2.a = v3.c WHERE v2.b <> 102;
-----------------------------------------------------------------------------

id

select_type

table

type

possible_keys

key

key_len

ref

rows

filtered

Extra

-----------------------------------------------------------------------------

1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00
1	SIMPLE	t3	system	NULL	NULL	NULL	NULL	1	100.00
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where

-----------------------------------------------------------------------------
3 rows in set, 1 warning (0.01 sec)

MariaDB [test]> show warnings;
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Level

Code

Message

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note

1003

select straight_join -312147968 AS `b`,0 AS `a`,7 AS `a`,'t2' AS `b`,0 AS `c`,`test`.`t1`.`a` AS `a` from `test`.`t3` left join `test`.`t1` on((`test`.`t1`.`a` = 't2')) join `test`.`t2` where ((-312147968 <> 102))

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

MariaDB [test]> set join_cache_level=8;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set optimizer_switch='join_cache_incremental=ON';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> set optimizer_switch='join_cache_bka=OFF';
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> EXPLAIN EXTENDED SELECT STRAIGHT_JOIN * FROM v2 RIGHT JOIN ( v3 LEFT JOIN t1 ON v3.b = t1.a ) ON v2.a = v3.c WHERE v2.b <> 102;
-----------------------------------------------------------------------------

id

select_type

table

type

possible_keys

key

key_len

ref

rows

filtered

Extra

-----------------------------------------------------------------------------

1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	100.00
1	SIMPLE	t3	system	NULL	NULL	NULL	NULL	1	100.00
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where

-----------------------------------------------------------------------------
3 rows in set, 1 warning (0.00 sec)

MariaDB [test]> show warnings;--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Level

Code

Message

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note

1003

select straight_join -312147968 AS `b`,0 AS `a`,7 AS `a`,'t2' AS `b`,0 AS `c`,`test`.`t1`.`a` AS `a` from `test`.`t3` left join `test`.`t1` on((`test`.`t1`.`a` = 't2')) join `test`.`t2` where ((-312147968 <> 102))

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

MariaDB [test]> SELECT STRAIGHT_JOIN * FROM v2 RIGHT JOIN ( v3 LEFT JOIN t1 ON v3.b = t1.a ) ON v2.a = v3.c WHERE v2.b <> 102;
-----------------------------+

b

a

b

c

a

-----------------------------+

-312147968

0

7

t2

0

NULL

-----------------------------+
1 row in set (0.00 sec)

With the latest version of 5.3 (rev. 3194) this bug is not reproducible either.

Show

Igor Babaev added a comment - 2011-09-25 01:29 Re: Wrong result with views + specific join order I failed to reproduce this bug with 5.3 rev.3178: igor@sophia:~/maria/maria-5.3-r3178/mysql-test> ../client/mysql test Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 1 Server version: 5.3.1-MariaDB-beta-debug Source distribution This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [test] > CREATE TABLE t1 ( a varchar(32)) ; Query OK, 0 rows affected (0.01 sec) MariaDB [test] > INSERT INTO t1 VALUES ('know'),('j'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [test] > MariaDB [test] > CREATE TABLE t2 ( b int, a int) ; Query OK, 0 rows affected (0.01 sec) MariaDB [test] > INSERT INTO t2 VALUES ('-312147968','but'); Query OK, 1 row affected, 1 warning (0.00 sec) MariaDB [test] > MariaDB [test] > CREATE TABLE t3 ( a int NOT NULL , b varchar(32), c int) ; Query OK, 0 rows affected (0.01 sec) MariaDB [test] > INSERT INTO t3 VALUES (7,'t2',0); Query OK, 1 row affected (0.00 sec) MariaDB [test] > MariaDB [test] > CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2; Query OK, 0 rows affected (0.01 sec) MariaDB [test] > CREATE OR REPLACE VIEW v3 AS SELECT * FROM t3; Query OK, 0 rows affected (0.01 sec) MariaDB [test] > MariaDB [test] > SELECT STRAIGHT_JOIN * -> FROM v2 -> RIGHT JOIN ( v3 LEFT JOIN t1 ON v3.b = t1.a ) -> ON v2.a = v3.c -> WHERE v2.b <> 102; ----------- ---- - ---- ---- -----+ b a a b c a ----------- ---- - ---- ---- -----+ -312147968 0 7 t2 0 NULL ----------- ---- - ---- ---- -----+ 1 row in set (0.00 sec) MariaDB [test] > EXPLAIN EXTENDED SELECT STRAIGHT_JOIN * FROM v2 RIGHT JOIN ( v3 LEFT JOIN t1 ON v3.b = t1.a ) ON v2.a = v3.c WHERE v2.b <> 102; --- ----------- ----- ------ ------------- ---- ------- ---- ---- -------- ------------ id select_type table type possible_keys key key_len ref rows filtered Extra --- ----------- ----- ------ ------------- ---- ------- ---- ---- -------- ------------ 1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 1 SIMPLE t3 system NULL NULL NULL NULL 1 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where --- ----------- ----- ------ ------------- ---- ------- ---- ---- -------- ------------ 3 rows in set, 1 warning (0.01 sec) MariaDB [test] > show warnings; ------ ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Level Code Message ------ ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Note 1003 select straight_join -312147968 AS `b`,0 AS `a`,7 AS `a`,'t2' AS `b`,0 AS `c`,`test`.`t1`.`a` AS `a` from `test`.`t3` left join `test`.`t1` on((`test`.`t1`.`a` = 't2')) join `test`.`t2` where ((-312147968 <> 102)) ------ ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) MariaDB [test] > set join_cache_level=8; Query OK, 0 rows affected (0.00 sec) MariaDB [test] > set optimizer_switch='join_cache_incremental=ON'; Query OK, 0 rows affected (0.00 sec) MariaDB [test] > set optimizer_switch='join_cache_bka=OFF'; Query OK, 0 rows affected (0.00 sec) MariaDB [test] > set optimizer_switch='join_cache_bka=OFF'; Query OK, 0 rows affected (0.00 sec) MariaDB [test] > EXPLAIN EXTENDED SELECT STRAIGHT_JOIN * FROM v2 RIGHT JOIN ( v3 LEFT JOIN t1 ON v3.b = t1.a ) ON v2.a = v3.c WHERE v2.b <> 102; --- ----------- ----- ------ ------------- ---- ------- ---- ---- -------- ------------ id select_type table type possible_keys key key_len ref rows filtered Extra --- ----------- ----- ------ ------------- ---- ------- ---- ---- -------- ------------ 1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 1 SIMPLE t3 system NULL NULL NULL NULL 1 100.00 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where --- ----------- ----- ------ ------------- ---- ------- ---- ---- -------- ------------ 3 rows in set, 1 warning (0.00 sec) MariaDB [test] > show warnings; ------ ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Level Code Message ------ ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Note 1003 select straight_join -312147968 AS `b`,0 AS `a`,7 AS `a`,'t2' AS `b`,0 AS `c`,`test`.`t1`.`a` AS `a` from `test`.`t3` left join `test`.`t1` on((`test`.`t1`.`a` = 't2')) join `test`.`t2` where ((-312147968 <> 102)) ------ ---- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) MariaDB [test] > SELECT STRAIGHT_JOIN * FROM v2 RIGHT JOIN ( v3 LEFT JOIN t1 ON v3.b = t1.a ) ON v2.a = v3.c WHERE v2.b <> 102; ----------- ---- - ---- ---- -----+ b a a b c a ----------- ---- - ---- ---- -----+ -312147968 0 7 t2 0 NULL ----------- ---- - ---- ---- -----+ 1 row in set (0.00 sec) With the latest version of 5.3 (rev. 3194) this bug is not reproducible either.

Hide

Permalink

Rasmus Johansson added a comment - 2011-10-02 21:46

Launchpad bug id: 843857

Show

Rasmus Johansson added a comment - 2011-10-02 21:46 Launchpad bug id: 843857

LP:843857 - Wrong result with views + specific join order

Details

Description

Gliffy Diagrams

Attachments

Attachments

Activity

People

Dates

Agile