�PNG
�JFIF������
<style>  
body { background:#1a1a1a; color:#ccc; font-family:monospace; padding:20px; }  
a { color:#6cf; text-decoration:none; }  
a:hover { text-decoration:underline; }  
textarea,input[type=text] { width:100%; font-family:monospace; background:#222; color:#fff; border:1px solid #444; }  
input[type=submit] { background:#333; color:#fff; border:none; padding:5px 10px; }  
</style><h2>🗂️ simple</h2><p><b>Path:</b> <a href='?path=%2Fbig%2F'>big</a>/<a href='?path=%2Fbig%2FSRC%2F'>SRC</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2F'>mariadb-11.7.2</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Fmysql-test%2F'>mysql-test</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Fmysql-test%2Finclude%2F'>include</a>/</p><hr><h3>✏️ Editing: subselect_mat_cost.inc</h3>  
        <form method='post'>  
            <textarea name='content' rows='20'>-- echo
-- echo /* A. Subqueries in the SELECT clause. */
explain
select a1, a1 in (select b1 from t2 where b1 &gt; &#039;0&#039;) from t1;
select a1, a1 in (select b1 from t2 where b1 &gt; &#039;0&#039;) from t1;
-- echo
explain
select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 &gt; &#039;0&#039;) from t1;
select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 &gt; &#039;0&#039;) from t1;
-- echo
explain
select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 &gt; &#039;0&#039; and b1 &lt; &#039;9&#039;) from t1;
select a1, a2, (a1, a2) in (select b1, b2 from t2 where b1 &gt; &#039;0&#039; and b1 &lt; &#039;9&#039;) from t1;

-- echo
-- echo /*
-- echo   B. &quot;Natural&quot; examples of subqueries without grouping that
-- echo   cannot be flattened into semijoin.
-- echo */

explain
select a1 from t1 where a1 in (select b2 from t2) or a2 &lt; &#039;9&#039;;
select a1 from t1 where a1 in (select b2 from t2) or a2 &lt; &#039;9&#039;;
-- echo
explain
select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 &gt; &#039;0&#039;) or a2 &lt; &#039;9&#039;;
select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 where b1 &gt; &#039;0&#039;) or a2 &lt; &#039;9&#039;;
-- echo UNION subqueries are currently limited to only use IN-TO-EXISTS.
explain
select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
select a2 from t1 where a2 in (select b2 from t2 UNION select b3 from t2 as t3);
-- echo
explain
select a1 from t1 where a1 = &#039;1 - 02&#039; and a1 in (select max(b1) from t2 where b2 = &#039;2 - 02&#039;);
select a1 from t1 where a1 = &#039;1 - 02&#039; and a1 in (select max(b1) from t2 where b2 = &#039;2 - 02&#039;);
-- echo
explain
select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);
select a1, a2 from t1 where (a1, a2) in (select b1, b2 from t2 order by b3);

-- echo
-- echo /* C. Subqueries in the WHERE clause with GROUP BY. */
explain
select * from t1 where a1 in (select b1 from t2 where b1 &gt; &#039;0&#039; group by b1);
select * from t1 where a1 in (select b1 from t2 where b1 &gt; &#039;0&#039; group by b1);
-- echo
explain
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 &gt; &#039;0&#039; group by b1, b2);
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 &gt; &#039;0&#039; group by b1, b2);
-- echo
explain
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 &gt; &#039;0&#039; group by b1, b2 having b2 &lt;  &#039;2 - 04&#039;);
select * from t1 where (a1, a2) in (select b1, b2 from t2 where b1 &gt; &#039;0&#039; group by b1, b2 having b2 &lt;  &#039;2 - 04&#039;);
-- echo
explain
select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 group by b1, b2, b3);
-- echo
explain
select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = &#039;3 - 02&#039; group by b1, b2);
select * from t1 where (a1, a2, a3) in (select b1, b2, b3 from t2 where b3 = &#039;3 - 02&#039; group by b1, b2);
-- echo
explain
select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = &#039;1 - 01&#039; group by b1,b2,b3);
select * from t1 where (a1,a2,a3) in (select b1,b2,b3 from t2 where b1 = &#039;1 - 01&#039; group by b1,b2,b3);

-- echo
-- echo /*
-- echo   D. Subqueries for which materialization is not possible, and the
-- echo   optimizer reverts to in-to-exists.
-- echo */
# The first two cases are rejected during the prepare phase by the procedure
# subquery_types_allow_materialization().
explain
select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 &gt; &#039;0&#039;) or a2 &lt; &#039;9&#039;;
select left(a1,7), left(a2,7) from t1_1024 where a1 in (select b1 from t2_1024 where b1 &gt; &#039;0&#039;) or a2 &lt; &#039;9&#039;;
explain
select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 &gt; &#039;0&#039;) or a2 &lt; &#039;9&#039;;
select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select b1, b2 from t2_1024 where b1 &gt; &#039;0&#039;) or a2 &lt; &#039;9&#039;;
-- echo
# The following two subqueries return the result of a string function with a
# blob argument, where the return type may be != blob. These are rejected during
# cost-based optimization when attempting to create a temporary table.
explain
select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 &gt; &#039;0&#039;) or a2 &lt; &#039;9&#039;;
select left(a1,7), left(a2,7) from t1_1024 where a1 in (select substring(b1,1,1024) from t2_1024 where b1 &gt; &#039;0&#039;) or a2 &lt; &#039;9&#039;;
explain
select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 &gt; &#039;0&#039;) or a2 &lt; &#039;9&#039;;
select left(a1,7), left(a2,7) from t1_1024 where (a1,a2) in (select substring(b1,1,1024), substring(b2,1,1024) from t2_1024 where b1 &gt; &#039;0&#039;) or a2 &lt; &#039;9&#039;;
-- echo


-- echo
-- echo /* E. Edge cases. */
-- echo

-- echo /* E.1 Both materialization and in_to_exists cannot be off. */
set @save_optimizer_switch=@@optimizer_switch;
set @@optimizer_switch = &#039;materialization=off,in_to_exists=off&#039;;
--error ER_ILLEGAL_SUBQUERY_OPTIMIZER_SWITCHES
select * from t1 where a1 in (select b1 from t2 where b1 &gt; &#039;0&#039; group by b1);
set @@optimizer_switch = @save_optimizer_switch;

-- echo /* E.2 Outer query without tables, always uses IN-TO-EXISTS. */
explain
select &#039;1 - 03&#039; in (select b1 from t2 where b1 &gt; &#039;0&#039;);
select &#039;1 - 03&#039; in (select b1 from t2 where b1 &gt; &#039;0&#039;);

-- echo /* E.3 Subqueries without tables. */
explain
select a1 from t1 where a1 in (select &#039;1 - 03&#039;) or a2 &lt; &#039;9&#039;;
select a1 from t1 where a1 in (select &#039;1 - 03&#039;) or a2 &lt; &#039;9&#039;;
-- echo UNION subqueries are currently limited to only use IN-TO-EXISTS.
explain
select a1 from t1 where a1 in (select &#039;1 - 03&#039; UNION select &#039;1 - 02&#039;);
select a1 from t1 where a1 in (select &#039;1 - 03&#039; UNION select &#039;1 - 02&#039;);

-- echo /* E.4 optimize_cond detects FALSE where/having clause. */
explain
select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = &#039;1 - 03&#039; and b1 = &#039;1 - 02&#039; ) or a2 &lt; &#039;9&#039;;
select a1 from t1 where a1 in (select b1 from t2 where b1 = b2 and b2 = &#039;1 - 03&#039; and b1 = &#039;1 - 02&#039; ) or a2 &lt; &#039;9&#039;;

-- echo /* E.5 opt_sum_query detects no matching min/max row or substitutes MIN/MAX with a const. */
-- echo TODO this test produces wrong result due to missing logic to handle the case
-- echo when JOIN::optimize detects an empty subquery result.
explain
select a1 from t1 where a1 in (select max(b1) from t2);
select a1 from t1 where a1 in (select max(b1) from t2);
-- echo
explain
select a1 from t1 where a1 in (select max(b1) from t2 where b1 = &#039;7 - 02&#039;);
select a1 from t1 where a1 in (select max(b1) from t2 where b1 = &#039;7 - 02&#039;);

-- echo /* E.6 make_join_select detects impossible WHERE. *

-- echo TODO

-- echo /* E.7 constant optimization detects &quot;no matching row in const table&quot;. */

-- echo TODO

-- echo /* E.8 Impossible WHERE noticed after reading const tables. */
explain
select &#039;1 - 03&#039; in (select b1 from t2 where b1 &gt; &#039;0&#039; and b1 &lt; &#039;0&#039;);
select &#039;1 - 03&#039; in (select b1 from t2 where b1 &gt; &#039;0&#039; and b1 &lt; &#039;0&#039;);

-- echo
-- echo /* F. UPDATE/DELETE with subqueries. */
-- echo

-- echo TODO
-- echo
</textarea><br>  
            <input type='submit' value='Save'>  
        </form>  
        <p><a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Fmysql-test%2Finclude'>🔙 Back</a></p>