|
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;
-----------
-----------
-----------
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;
------
------
| 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;------
------
| 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;
-----------
-----------
-----------
1 row in set (0.00 sec)
With the latest version of 5.3 (rev. 3194) this bug is not reproducible either.
| 
LPexportBug843857.xml