
USE semijoin_test;

SET SESSION optimizer_switch ='semijoin=on';

SELECT 'semijoin=ON, optimizer_search_depth=1' AS test_type;

SET SESSION optimizer_search_depth = 1;

EXPLAIN SELECT SQL_NO_CACHE 
	T2_0_.T1IdRef,
	T2_0_.T2Id
	FROM
		T2 T2_0_ 
	LEFT OUTER JOIN
		T1 T1_0_ 
		ON T2_0_.T1IdRef=T1_0_.T1Id    
	WHERE
		T2_0_.T1IdRef IN (
			SELECT
				T1_1_.T1Id 
			FROM
				T3 T3_0_ 
			INNER JOIN
				T2 T2_1_ 
					ON T3_0_.T2IdRef=T2_1_.T2Id 
			 LEFT OUTER JOIN
				T1 T1_1_ 
					ON T2_1_.T1IdRef=T1_1_.T1Id            
			WHERE
				T3_0_.T3IdRef= 1
	);

SELECT SQL_NO_CACHE 
	T2_0_.T1IdRef,
	T2_0_.T2Id
	FROM
		T2 T2_0_ 
	LEFT OUTER JOIN
		T1 T1_0_ 
		ON T2_0_.T1IdRef=T1_0_.T1Id    
	WHERE
		T2_0_.T1IdRef IN (
			SELECT
				T1_1_.T1Id 
			FROM
				T3 T3_0_ 
			INNER JOIN
				T2 T2_1_ 
					ON T3_0_.T2IdRef=T2_1_.T2Id 
			 LEFT OUTER JOIN
				T1 T1_1_ 
					ON T2_1_.T1IdRef=T1_1_.T1Id            
			WHERE
				T3_0_.T3IdRef= 1
	);
		
SELECT 'semijoin=ON, optimizer_search_depth=3' AS test_type;

SET SESSION optimizer_search_depth = 3;

EXPLAIN SELECT SQL_NO_CACHE 
	T2_0_.T1IdRef,
	T2_0_.T2Id
	FROM
		T2 T2_0_ 
	LEFT OUTER JOIN
		T1 T1_0_ 
		ON T2_0_.T1IdRef=T1_0_.T1Id    
	WHERE
		T2_0_.T1IdRef IN (
			SELECT
				T1_1_.T1Id 
			FROM
				T3 T3_0_ 
			INNER JOIN
				T2 T2_1_ 
					ON T3_0_.T2IdRef=T2_1_.T2Id 
			 LEFT OUTER JOIN
				T1 T1_1_ 
					ON T2_1_.T1IdRef=T1_1_.T1Id            
			WHERE
				T3_0_.T3IdRef= 1
	);

SELECT SQL_NO_CACHE 
	T2_0_.T1IdRef,
	T2_0_.T2Id
	FROM
		T2 T2_0_ 
	LEFT OUTER JOIN
		T1 T1_0_ 
		ON T2_0_.T1IdRef=T1_0_.T1Id    
	WHERE
		T2_0_.T1IdRef IN (
			SELECT
				T1_1_.T1Id 
			FROM
				T3 T3_0_ 
			INNER JOIN
				T2 T2_1_ 
					ON T3_0_.T2IdRef=T2_1_.T2Id 
			 LEFT OUTER JOIN
				T1 T1_1_ 
					ON T2_1_.T1IdRef=T1_1_.T1Id            
			WHERE
				T3_0_.T3IdRef= 1
	);

SET SESSION optimizer_switch ='semijoin=off';

SELECT 'semijoin=OFF, optimizer_search_depth=1' AS test_type;

SET SESSION optimizer_search_depth = 1;

EXPLAIN SELECT SQL_NO_CACHE 
	T2_0_.T1IdRef,
	T2_0_.T2Id
	FROM
		T2 T2_0_ 
	LEFT OUTER JOIN
		T1 T1_0_ 
		ON T2_0_.T1IdRef=T1_0_.T1Id    
	WHERE
		T2_0_.T1IdRef IN (
			SELECT
				T1_1_.T1Id 
			FROM
				T3 T3_0_ 
			INNER JOIN
				T2 T2_1_ 
					ON T3_0_.T2IdRef=T2_1_.T2Id 
			 LEFT OUTER JOIN
				T1 T1_1_ 
					ON T2_1_.T1IdRef=T1_1_.T1Id            
			WHERE
				T3_0_.T3IdRef= 1
	);

SELECT SQL_NO_CACHE 
	T2_0_.T1IdRef,
	T2_0_.T2Id
	FROM
		T2 T2_0_ 
	LEFT OUTER JOIN
		T1 T1_0_ 
		ON T2_0_.T1IdRef=T1_0_.T1Id    
	WHERE
		T2_0_.T1IdRef IN (
			SELECT
				T1_1_.T1Id 
			FROM
				T3 T3_0_ 
			INNER JOIN
				T2 T2_1_ 
					ON T3_0_.T2IdRef=T2_1_.T2Id 
			 LEFT OUTER JOIN
				T1 T1_1_ 
					ON T2_1_.T1IdRef=T1_1_.T1Id            
			WHERE
				T3_0_.T3IdRef= 1
	);
		
SELECT 'semijoin=OFF, optimizer_search_depth=3' AS test_type;

SET SESSION optimizer_search_depth = 3;

EXPLAIN SELECT SQL_NO_CACHE 
	T2_0_.T1IdRef,
	T2_0_.T2Id
	FROM
		T2 T2_0_ 
	LEFT OUTER JOIN
		T1 T1_0_ 
		ON T2_0_.T1IdRef=T1_0_.T1Id    
	WHERE
		T2_0_.T1IdRef IN (
			SELECT
				T1_1_.T1Id 
			FROM
				T3 T3_0_ 
			INNER JOIN
				T2 T2_1_ 
					ON T3_0_.T2IdRef=T2_1_.T2Id 
			 LEFT OUTER JOIN
				T1 T1_1_ 
					ON T2_1_.T1IdRef=T1_1_.T1Id            
			WHERE
				T3_0_.T3IdRef= 1
	);

SELECT SQL_NO_CACHE 
	T2_0_.T1IdRef,
	T2_0_.T2Id
	FROM
		T2 T2_0_ 
	LEFT OUTER JOIN
		T1 T1_0_ 
		ON T2_0_.T1IdRef=T1_0_.T1Id    
	WHERE
		T2_0_.T1IdRef IN (
			SELECT
				T1_1_.T1Id 
			FROM
				T3 T3_0_ 
			INNER JOIN
				T2 T2_1_ 
					ON T3_0_.T2IdRef=T2_1_.T2Id 
			 LEFT OUTER JOIN
				T1 T1_1_ 
					ON T2_1_.T1IdRef=T1_1_.T1Id            
			WHERE
				T3_0_.T3IdRef= 1
	);
	