| Server IP : 185.86.78.101 / Your IP : 216.73.216.124 Web Server : Apache System : Linux 675867-vds-valikoshka1996.gmhost.pp.ua 5.4.0-150-generic #167-Ubuntu SMP Mon May 15 17:35:05 UTC 2023 x86_64 User : www ( 1000) PHP Version : 7.4.33 Disable Function : passthru,exec,system,putenv,chroot,chgrp,chown,shell_exec,popen,proc_open,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : OFF | Sudo : ON | Pkexec : OFF Directory : /www/server/mysql/mysql-test/t/ |
Upload File : |
# The include statement below is a temp one for tests that are yet to
#be ported to run with InnoDB,
#but needs to be kept for tests that would need MyISAM in future.
--source include/force_myisam_default.inc
#
# Run join_nested.test with BKA enabled
#
set optimizer_switch='batched_key_access=on,mrr_cost_based=off';
--source t/join_nested.test
#
# BUG#35835: queries with nested outer joins with BKA enabled
#
CREATE TABLE t5 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
CREATE TABLE t6 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
CREATE TABLE t7 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
CREATE TABLE t8 (a int, b int, c int, PRIMARY KEY(a), KEY b_i (b));
INSERT INTO t5 VALUES (1,1,0), (2,2,0), (3,3,0);
INSERT INTO t6 VALUES (1,2,0), (3,2,0), (6,1,0);
INSERT INTO t7 VALUES (1,1,0), (2,2,0);
INSERT INTO t8 VALUES (0,2,0), (1,2,0);
EXPLAIN
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5
LEFT JOIN
(
(t6, t7)
LEFT JOIN
t8
ON t7.b=t8.b AND t6.b < 10
)
ON t6.b >= 2 AND t5.b=t7.b AND
(t8.a > 0 OR t8.c IS NULL);
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5
LEFT JOIN
(
(t6, t7)
LEFT JOIN
t8
ON t7.b=t8.b AND t6.b < 10
)
ON t6.b >= 2 AND t5.b=t7.b AND
(t8.a > 0 OR t8.c IS NULL);
DELETE FROM t5;
DELETE FROM t6;
DELETE FROM t7;
DELETE FROM t8;
INSERT INTO t5 VALUES (1,3,0), (3,2,0);
INSERT INTO t6 VALUES (3,3,0);
INSERT INTO t7 VALUES (1,2,0);
INSERT INTO t8 VALUES (1,1,0);
EXPLAIN
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5 LEFT JOIN
(t6 LEFT JOIN t7 ON t7.a=1, t8)
ON (t5.b=t8.b);
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5 LEFT JOIN
(t6 LEFT JOIN t7 ON t7.a=1, t8)
ON (t5.b=t8.b);
EXPLAIN
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5 LEFT JOIN
(t6 LEFT JOIN t7 ON t7.b=2, t8)
ON (t5.b=t8.b);
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5 LEFT JOIN
(t6 LEFT JOIN t7 ON t7.b=2, t8)
ON (t5.b=t8.b);
EXPLAIN
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5 LEFT JOIN
(t8, t6 LEFT JOIN t7 ON t7.a=1)
ON (t5.b=t8.b);
SELECT t5.a,t5.b,t6.a,t6.b,t7.a,t7.b,t8.a,t8.b
FROM t5 LEFT JOIN
(t8, t6 LEFT JOIN t7 ON t7.a=1)
ON (t5.b=t8.b);
DROP TABLE t5,t6,t7,t8;
set optimizer_switch=default;