server/src/test/java/com/cloud/api/query/dao/TemplateJoinDaoImplBypassExplainTest.java - cloudstack - Git at Google

 // Licensed to the Apache Software Foundation (ASF) under one
 // or more contributor license agreements.  See the NOTICE file
 // distributed with this work for additional information
 // regarding copyright ownership.  The ASF licenses this file
 // to you under the Apache License, Version 2.0 (the
 // "License"); you may not use this file except in compliance
 // with the License.  You may obtain a copy of the License at
 //
 //   http://www.apache.org/licenses/LICENSE-2.0
 //
 // Unless required by applicable law or agreed to in writing,
 // software distributed under the License is distributed on an
 // "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 // KIND, either express or implied.  See the License for the
 // specific language governing permissions and limitations
 // under the License.
 package com.cloud.api.query.dao;

 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.ResultSetMetaData;
 import java.sql.Statement;
 import java.util.LinkedHashMap;
 import java.util.Map;
 import java.util.Set;
 import java.util.TreeSet;
 import org.junit.After;
 import org.junit.Assume;
 import org.junit.Before;
 import org.junit.Ignore;
 import org.junit.Test;

 import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertFalse;
 import static org.junit.Assert.assertNotNull;
 import static org.junit.Assert.assertTrue;

 /**
  * Integration test that EXPLAINs the Phase-1 bypass SQL versus the legacy
  * template_view SQL, and asserts that the bypass plan no longer trips the
  * "Range checked for each record" path on data_center.
  *
  * Skipped unless a MySQL URL is provided via system properties:
  *   -Dtest.cloudstack.mysql.url=jdbc:mysql://host:3306/cloud
  *   -Dtest.cloudstack.mysql.user=root
  *   -Dtest.cloudstack.mysql.password=
  *
  */
 public class TemplateJoinDaoImplBypassExplainTest {

     private static final String BYPASS_SQL =
             "SELECT DISTINCT CONCAT(vt.id, '_', IFNULL(dc.id, 0)) AS pair " +
             "FROM cloud.vm_template vt " +
             "JOIN cloud.account a ON a.id = vt.account_id " +
             "LEFT JOIN cloud.template_store_ref tsr " +
             "  ON tsr.template_id = vt.id AND tsr.store_role = 'Image' AND tsr.destroyed = 0 " +
             "LEFT JOIN cloud.image_store img " +
             "  ON img.id = tsr.store_id AND img.removed IS NULL " +
             "LEFT JOIN cloud.template_zone_ref tzr " +
             "  ON tzr.template_id = vt.id AND tsr.store_id IS NULL AND tzr.removed IS NULL " +
             "LEFT JOIN cloud.data_center dc " +
             "  ON dc.id = COALESCE(img.data_center_id, tzr.zone_id) " +
             "WHERE vt.removed IS NULL " +
             "  AND vt.state IN ('Active','UploadAbandoned','UploadError','NotUploaded','UploadInProgress')";

     /**
      * Bypass SQL with the conditional `JOIN cloud.domain` added.
      * Triggered when the request needs domain.path scoping (DA self/selfexecutable
      * or non-admin all + SkipProjectResources). Should still use eq_ref / PRIMARY
      * on data_center; domain itself is small.
      */
     private static final String BYPASS_SQL_WITH_DOMAIN =
             "SELECT DISTINCT CONCAT(vt.id, '_', IFNULL(dc.id, 0)) AS pair " +
             "FROM cloud.vm_template vt " +
             "JOIN cloud.account a ON a.id = vt.account_id " +
             "LEFT JOIN cloud.template_store_ref tsr " +
             "  ON tsr.template_id = vt.id AND tsr.store_role = 'Image' AND tsr.destroyed = 0 " +
             "LEFT JOIN cloud.image_store img " +
             "  ON img.id = tsr.store_id AND img.removed IS NULL " +
             "LEFT JOIN cloud.template_zone_ref tzr " +
             "  ON tzr.template_id = vt.id AND tsr.store_id IS NULL AND tzr.removed IS NULL " +
             "LEFT JOIN cloud.data_center dc " +
             "  ON dc.id = COALESCE(img.data_center_id, tzr.zone_id) " +
             "JOIN cloud.domain d ON d.id = a.domain_id " +
             "WHERE vt.removed IS NULL " +
             "  AND vt.state IN ('Active','UploadAbandoned','UploadError','NotUploaded','UploadInProgress') " +
             "  AND d.path LIKE '/%' ";

     private static final String VIEW_SQL =
             "SELECT DISTINCT temp_zone_pair FROM cloud.template_view " +
             "WHERE template_state IN ('Active','UploadAbandoned','UploadError','NotUploaded','UploadInProgress')";

     private Connection conn;

     @Before
     public void setUp() throws Exception {
         String url = System.getProperty("test.cloudstack.mysql.url");
         String user = System.getProperty("test.cloudstack.mysql.user", "root");
         String pwd = System.getProperty("test.cloudstack.mysql.password", "");
         Assume.assumeNotNull("test.cloudstack.mysql.url not set; skipping EXPLAIN integration test", url);
         conn = DriverManager.getConnection(url, user, pwd);
     }

     @After
     public void tearDown() throws Exception {
         if (conn != null) {
             conn.close();
         }
     }

     @Test
     @Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
     public void bypass_dataCenter_isPkLookup_notRangeChecked() throws Exception {
         Map<String, Map<String, String>> rowsByTable = explain(BYPASS_SQL);
         Map<String, String> dc = rowsByTable.get("dc");
         assertNotNull("EXPLAIN row for `dc` (data_center) missing", dc);

         String type = dc.get("type");
         String key = dc.get("key");
         String extra = dc.get("Extra") == null ? "" : dc.get("Extra");

         // dc.id is the PK; COALESCE on the other side. Optimizer should pick eq_ref / ref / const.
         assertTrue("dc access type is " + type + ", expected eq_ref/ref/const",
                 "eq_ref".equals(type) || "ref".equals(type) || "const".equals(type));
         assertEquals("dc should use PRIMARY", "PRIMARY", key);
         assertFalse("dc should not use 'Range checked for each record'; Extra=" + extra,
                 extra.contains("Range checked for each record"));
     }

     /**
      * Asserts the bypass plan trims the join graph the legacy view drags along.
      *
      * Portable across CIB and prod
      *
      */
     @Test
     @Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
     public void view_joinsMoreTablesThanBypass() throws Exception {
         Map<String, Map<String, String>> view = explain(VIEW_SQL);
         Map<String, Map<String, String>> bypass = explain(BYPASS_SQL);
         assertTrue(
                 "expected legacy view plan (" + view.size() + " tables) to join more than bypass (" + bypass.size() + ")",
                 view.size() > bypass.size());
         // Row-multiplier tables that the bypass intentionally drops.
         assertTrue("expected view plan to include vm_template_details",
                 view.containsKey("vm_template_details"));
         assertTrue("expected view plan to include resource_tags",
                 view.containsKey("resource_tags"));
     }

     @Test
     @Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
     public void bypass_doesNotJoin_resourceTags_or_vmTemplateDetails() throws Exception {
         Map<String, Map<String, String>> rowsByTable = explain(BYPASS_SQL);
         assertFalse("bypass must not join resource_tags", rowsByTable.containsKey("resource_tags"));
         assertFalse("bypass must not join vm_template_details", rowsByTable.containsKey("vm_template_details"));
         assertFalse("bypass must not join launch_permission", rowsByTable.containsKey("launch_permission"));
     }

     @Test
     @Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
     public void bypassWithDomain_joinsDomainTable() throws Exception {
         Map<String, Map<String, String>> rowsByTable = explain(BYPASS_SQL_WITH_DOMAIN);
         assertTrue("bypass+domain plan must include domain table", rowsByTable.containsKey("d"));
     }

     @Test
     @Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
     public void bypassWithDomain_domain_isPkLookup() throws Exception {
         // domain.id is the PK; the join is on a.domain_id. Optimizer should pick eq_ref / ref / const.
         Map<String, Map<String, String>> rowsByTable = explain(BYPASS_SQL_WITH_DOMAIN);
         Map<String, String> d = rowsByTable.get("d");
         assertNotNull("EXPLAIN row for `d` (domain) missing", d);

         String type = d.get("type");
         String key = d.get("key");
         String extra = d.get("Extra") == null ? "" : d.get("Extra");

         assertTrue("domain access type is " + type + ", expected eq_ref/ref/const/index",
                 "eq_ref".equals(type) || "ref".equals(type) || "const".equals(type) || "index".equals(type));
         assertNotNull("domain should use SOME index", key);
         assertFalse("domain should not be 'Range checked for each record'; Extra=" + extra,
                 extra.contains("Range checked for each record"));
     }

     @Test
     @Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
     public void bypassWithDomain_dataCenter_stillPkLookup() throws Exception {
         // Adding the domain join must not regress the data_center PK-lookup gain.
         Map<String, Map<String, String>> rowsByTable = explain(BYPASS_SQL_WITH_DOMAIN);
         Map<String, String> dc = rowsByTable.get("dc");
         assertNotNull("EXPLAIN row for `dc` missing in bypass+domain plan", dc);
         String type = dc.get("type");
         assertTrue("dc access type with domain join is " + type + ", expected eq_ref/ref/const",
                 "eq_ref".equals(type) || "ref".equals(type) || "const".equals(type));
         assertEquals("PRIMARY", dc.get("key"));
     }

     @Test
     @Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
     public void bypassWithDomain_stillFewerTablesThanView() throws Exception {
         // Even with the conditional domain join, the bypass plan is still smaller than the legacy view.
         Map<String, Map<String, String>> bypass = explain(BYPASS_SQL_WITH_DOMAIN);
         Map<String, Map<String, String>> view = explain(VIEW_SQL);
         assertTrue(
                 "bypass+domain plan (" + bypass.size() + " tables) should still join fewer than the view (" + view.size() + ")",
                 bypass.size() < view.size());
     }

     @Test
     @Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
     public void bypassWithDomain_stillNoRowMultipliers() throws Exception {
         // Critical: adding the domain join must not pull in the row multipliers the bypass dropped.
         Map<String, Map<String, String>> rowsByTable = explain(BYPASS_SQL_WITH_DOMAIN);
         assertFalse("bypass+domain must not join resource_tags",       rowsByTable.containsKey("resource_tags"));
         assertFalse("bypass+domain must not join vm_template_details", rowsByTable.containsKey("vm_template_details"));
         assertFalse("bypass+domain must not join launch_permission",   rowsByTable.containsKey("launch_permission"));
     }

     // ============================================================================
     // Timing tests
     // ============================================================================

     /** Number of iterations per query; first 2 are warm-up. */
     private static final int TIMING_ITERATIONS = 5;
     /** Skip first N iterations from the average (warm-up). */
     private static final int TIMING_WARMUP = 2;
     /**
      * Tolerance for the timing assertion. With sparse test data both queries
      * complete in ~1 ms, where measurement noise can flip the order; allow the
      * bypass to be marginally slower without failing the test.
      */
     private static final double TIMING_TOLERANCE_FACTOR = 2.0;
     /** Floor below which we don't bother asserting (pure JDBC overhead). */
     private static final long TIMING_NOISE_FLOOR_NS = 5_000_000L; // 5 ms

     @Test
     @Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
     public void bypass_isFasterThanLegacyView() throws Exception {
         long legacyAvgNs = averageQueryNanos(VIEW_SQL);
         long bypassAvgNs = averageQueryNanos(BYPASS_SQL);

         System.out.println(String.format(
                 "Timing avg over %d iterations (warm-up %d): legacy=%.2f ms, bypass=%.2f ms (%.1fx speedup)",
                 TIMING_ITERATIONS, TIMING_WARMUP,
                 legacyAvgNs / 1_000_000.0,
                 bypassAvgNs / 1_000_000.0,
                 (double) legacyAvgNs / Math.max(bypassAvgNs, 1)));

         // Below the noise floor both queries are dominated by JDBC overhead;
         // the structural improvement is unmeasurable. Skip the strict comparison.
         if (legacyAvgNs < TIMING_NOISE_FLOOR_NS) {
             System.out.println("Skipping strict timing assertion: legacy duration below noise floor");
             return;
         }
         assertTrue(
                 String.format("bypass (%.2f ms) should be at most %.1fx legacy (%.2f ms)",
                         bypassAvgNs / 1_000_000.0, TIMING_TOLERANCE_FACTOR, legacyAvgNs / 1_000_000.0),
                 bypassAvgNs <= (long) (legacyAvgNs * TIMING_TOLERANCE_FACTOR));
     }

     @Test
     @Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
     public void bypassWithDomain_isFasterThanLegacyView() throws Exception {
         long legacyAvgNs = averageQueryNanos(VIEW_SQL);
         long bypassAvgNs = averageQueryNanos(BYPASS_SQL_WITH_DOMAIN);

         System.out.println(String.format(
                 "Timing avg (bypass+domain join): legacy=%.2f ms, bypass+domain=%.2f ms (%.1fx speedup)",
                 legacyAvgNs / 1_000_000.0,
                 bypassAvgNs / 1_000_000.0,
                 (double) legacyAvgNs / Math.max(bypassAvgNs, 1)));

         if (legacyAvgNs < TIMING_NOISE_FLOOR_NS) {
             System.out.println("Skipping strict timing assertion: legacy duration below noise floor");
             return;
         }
         assertTrue(
                 String.format("bypass+domain (%.2f ms) should be at most %.1fx legacy (%.2f ms)",
                         bypassAvgNs / 1_000_000.0, TIMING_TOLERANCE_FACTOR, legacyAvgNs / 1_000_000.0),
                 bypassAvgNs <= (long) (legacyAvgNs * TIMING_TOLERANCE_FACTOR));
     }

     // ============================================================================
     // Parity tests — bypass and legacy must return identical pair sets and counts
     // ============================================================================

     @Test
     @Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
     public void bypass_returnsSameTemplatePairSetAsLegacy() throws Exception {
         Set<String> legacy = collectColumn1(VIEW_SQL);
         Set<String> bypass = collectColumn1(BYPASS_SQL);

         assertEquals("legacy and bypass must return the same number of distinct pairs",
                 legacy.size(), bypass.size());
         assertEquals("legacy and bypass must return identical pair values",
                 legacy, bypass);
     }

     @Test
     @Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
     public void bypass_distinctCountMatchesLegacy() throws Exception {
         long legacyCount = scalarLong(
                 "SELECT COUNT(*) FROM (" + VIEW_SQL + ") AS t");
         long bypassCount = scalarLong(
                 "SELECT COUNT(*) FROM (" + BYPASS_SQL + ") AS t");

         assertEquals("legacy and bypass DISTINCT counts must match", legacyCount, bypassCount);
     }

     /** Collect column 1 of every row into a sorted set (for set equality assertion). */
     private Set<String> collectColumn1(String query) throws Exception {
         Set<String> out = new TreeSet<>();
         try (Statement st = conn.createStatement();
              ResultSet rs = st.executeQuery(query)) {
             while (rs.next()) {
                 out.add(rs.getString(1));
             }
         }
         return out;
     }

     /** Run a query that returns one numeric scalar in column 1. */
     private long scalarLong(String query) throws Exception {
         try (Statement st = conn.createStatement();
              ResultSet rs = st.executeQuery(query)) {
             if (rs.next()) {
                 return rs.getLong(1);
             }
             return 0L;
         }
     }

     /**
      * Run the query {@link #TIMING_ITERATIONS} times, drop the first
      * {@link #TIMING_WARMUP} as warm-up, return the average wall-clock duration
      * of the remaining iterations in nanoseconds. Each iteration drains the
      * ResultSet so we measure execution + transport, not just submission.
      */
     private long averageQueryNanos(String query) throws Exception {
         long total = 0;
         int counted = 0;
         for (int i = 0; i < TIMING_ITERATIONS; i++) {
             long start = System.nanoTime();
             try (Statement st = conn.createStatement();
                  ResultSet rs = st.executeQuery(query)) {
                 while (rs.next()) {
                     rs.getString(1);
                 }
             }
             long elapsed = System.nanoTime() - start;
             if (i >= TIMING_WARMUP) {
                 total += elapsed;
                 counted++;
             }
         }
         return counted == 0 ? 0 : total / counted;
     }

     /**
      * Run EXPLAIN and return one map per table alias (or table name) → column→value.
      * Uses traditional EXPLAIN format so column names match across MySQL versions.
      * Also logs the raw EXPLAIN rows so test output makes plan differences visible.
      */
     private Map<String, Map<String, String>> explain(String query) throws Exception {
         Map<String, Map<String, String>> out = new LinkedHashMap<>();
         try (Statement st = conn.createStatement();
              ResultSet rs = st.executeQuery("EXPLAIN " + query)) {
             ResultSetMetaData md = rs.getMetaData();
             int colCount = md.getColumnCount();

             StringBuilder log = new StringBuilder("\nEXPLAIN result for query:\n  ").append(query).append('\n');
             // header
             for (int i = 1; i <= colCount; i++) {
                 log.append(md.getColumnLabel(i)).append('\t');
             }
             log.append('\n');

             while (rs.next()) {
                 Map<String, String> row = new LinkedHashMap<>();
                 for (int i = 1; i <= colCount; i++) {
                     String val = rs.getString(i);
                     row.put(md.getColumnLabel(i), val);
                     log.append(val == null ? "NULL" : val).append('\t');
                 }
                 log.append('\n');
                 String tableKey = row.get("table");
                 if (tableKey != null) {
                     out.put(tableKey, row);
                 }
             }
             // uncomment if need verbose EXPLAIN output
 //            System.out.println(log.toString());
         }
         return out;
     }
 }
	// Licensed to the Apache Software Foundation (ASF) under one
	// or more contributor license agreements. See the NOTICE file
	// distributed with this work for additional information
	// regarding copyright ownership. The ASF licenses this file
	// to you under the Apache License, Version 2.0 (the
	// "License"); you may not use this file except in compliance
	// with the License. You may obtain a copy of the License at
	//
	// http://www.apache.org/licenses/LICENSE-2.0
	//
	// Unless required by applicable law or agreed to in writing,
	// software distributed under the License is distributed on an
	// "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
	// KIND, either express or implied. See the License for the
	// specific language governing permissions and limitations
	// under the License.
	package com.cloud.api.query.dao;

	import java.sql.Connection;
	import java.sql.DriverManager;
	import java.sql.ResultSet;
	import java.sql.ResultSetMetaData;
	import java.sql.Statement;
	import java.util.LinkedHashMap;
	import java.util.Map;
	import java.util.Set;
	import java.util.TreeSet;
	import org.junit.After;
	import org.junit.Assume;
	import org.junit.Before;
	import org.junit.Ignore;
	import org.junit.Test;

	import static org.junit.Assert.assertEquals;
	import static org.junit.Assert.assertFalse;
	import static org.junit.Assert.assertNotNull;
	import static org.junit.Assert.assertTrue;

	/**
	* Integration test that EXPLAINs the Phase-1 bypass SQL versus the legacy
	* template_view SQL, and asserts that the bypass plan no longer trips the
	* "Range checked for each record" path on data_center.
	*
	* Skipped unless a MySQL URL is provided via system properties:
	* -Dtest.cloudstack.mysql.url=jdbc:mysql://host:3306/cloud
	* -Dtest.cloudstack.mysql.user=root
	* -Dtest.cloudstack.mysql.password=
	*
	*/
	public class TemplateJoinDaoImplBypassExplainTest {

	private static final String BYPASS_SQL =
	"SELECT DISTINCT CONCAT(vt.id, '_', IFNULL(dc.id, 0)) AS pair " +
	"FROM cloud.vm_template vt " +
	"JOIN cloud.account a ON a.id = vt.account_id " +
	"LEFT JOIN cloud.template_store_ref tsr " +
	" ON tsr.template_id = vt.id AND tsr.store_role = 'Image' AND tsr.destroyed = 0 " +
	"LEFT JOIN cloud.image_store img " +
	" ON img.id = tsr.store_id AND img.removed IS NULL " +
	"LEFT JOIN cloud.template_zone_ref tzr " +
	" ON tzr.template_id = vt.id AND tsr.store_id IS NULL AND tzr.removed IS NULL " +
	"LEFT JOIN cloud.data_center dc " +
	" ON dc.id = COALESCE(img.data_center_id, tzr.zone_id) " +
	"WHERE vt.removed IS NULL " +
	" AND vt.state IN ('Active','UploadAbandoned','UploadError','NotUploaded','UploadInProgress')";

	/**
	* Bypass SQL with the conditional `JOIN cloud.domain` added.
	* Triggered when the request needs domain.path scoping (DA self/selfexecutable
	* or non-admin all + SkipProjectResources). Should still use eq_ref / PRIMARY
	* on data_center; domain itself is small.
	*/
	private static final String BYPASS_SQL_WITH_DOMAIN =
	"SELECT DISTINCT CONCAT(vt.id, '_', IFNULL(dc.id, 0)) AS pair " +
	"FROM cloud.vm_template vt " +
	"JOIN cloud.account a ON a.id = vt.account_id " +
	"LEFT JOIN cloud.template_store_ref tsr " +
	" ON tsr.template_id = vt.id AND tsr.store_role = 'Image' AND tsr.destroyed = 0 " +
	"LEFT JOIN cloud.image_store img " +
	" ON img.id = tsr.store_id AND img.removed IS NULL " +
	"LEFT JOIN cloud.template_zone_ref tzr " +
	" ON tzr.template_id = vt.id AND tsr.store_id IS NULL AND tzr.removed IS NULL " +
	"LEFT JOIN cloud.data_center dc " +
	" ON dc.id = COALESCE(img.data_center_id, tzr.zone_id) " +
	"JOIN cloud.domain d ON d.id = a.domain_id " +
	"WHERE vt.removed IS NULL " +
	" AND vt.state IN ('Active','UploadAbandoned','UploadError','NotUploaded','UploadInProgress') " +
	" AND d.path LIKE '/%' ";

	private static final String VIEW_SQL =
	"SELECT DISTINCT temp_zone_pair FROM cloud.template_view " +
	"WHERE template_state IN ('Active','UploadAbandoned','UploadError','NotUploaded','UploadInProgress')";

	private Connection conn;

	@Before
	public void setUp() throws Exception {
	String url = System.getProperty("test.cloudstack.mysql.url");
	String user = System.getProperty("test.cloudstack.mysql.user", "root");
	String pwd = System.getProperty("test.cloudstack.mysql.password", "");
	Assume.assumeNotNull("test.cloudstack.mysql.url not set; skipping EXPLAIN integration test", url);
	conn = DriverManager.getConnection(url, user, pwd);
	}

	@After
	public void tearDown() throws Exception {
	if (conn != null) {
	conn.close();
	}
	}

	@Test
	@Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
	public void bypass_dataCenter_isPkLookup_notRangeChecked() throws Exception {
	Map<String, Map<String, String>> rowsByTable = explain(BYPASS_SQL);
	Map<String, String> dc = rowsByTable.get("dc");
	assertNotNull("EXPLAIN row for `dc` (data_center) missing", dc);

	String type = dc.get("type");
	String key = dc.get("key");
	String extra = dc.get("Extra") == null ? "" : dc.get("Extra");

	// dc.id is the PK; COALESCE on the other side. Optimizer should pick eq_ref / ref / const.
	assertTrue("dc access type is " + type + ", expected eq_ref/ref/const",
	"eq_ref".equals(type) \|\| "ref".equals(type) \|\| "const".equals(type));
	assertEquals("dc should use PRIMARY", "PRIMARY", key);
	assertFalse("dc should not use 'Range checked for each record'; Extra=" + extra,
	extra.contains("Range checked for each record"));
	}

	/**
	* Asserts the bypass plan trims the join graph the legacy view drags along.
	*
	* Portable across CIB and prod
	*
	*/
	@Test
	@Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
	public void view_joinsMoreTablesThanBypass() throws Exception {
	Map<String, Map<String, String>> view = explain(VIEW_SQL);
	Map<String, Map<String, String>> bypass = explain(BYPASS_SQL);
	assertTrue(
	"expected legacy view plan (" + view.size() + " tables) to join more than bypass (" + bypass.size() + ")",
	view.size() > bypass.size());
	// Row-multiplier tables that the bypass intentionally drops.
	assertTrue("expected view plan to include vm_template_details",
	view.containsKey("vm_template_details"));
	assertTrue("expected view plan to include resource_tags",
	view.containsKey("resource_tags"));
	}

	@Test
	@Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
	public void bypass_doesNotJoin_resourceTags_or_vmTemplateDetails() throws Exception {
	Map<String, Map<String, String>> rowsByTable = explain(BYPASS_SQL);
	assertFalse("bypass must not join resource_tags", rowsByTable.containsKey("resource_tags"));
	assertFalse("bypass must not join vm_template_details", rowsByTable.containsKey("vm_template_details"));
	assertFalse("bypass must not join launch_permission", rowsByTable.containsKey("launch_permission"));
	}

	@Test
	@Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
	public void bypassWithDomain_joinsDomainTable() throws Exception {
	Map<String, Map<String, String>> rowsByTable = explain(BYPASS_SQL_WITH_DOMAIN);
	assertTrue("bypass+domain plan must include domain table", rowsByTable.containsKey("d"));
	}

	@Test
	@Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
	public void bypassWithDomain_domain_isPkLookup() throws Exception {
	// domain.id is the PK; the join is on a.domain_id. Optimizer should pick eq_ref / ref / const.
	Map<String, Map<String, String>> rowsByTable = explain(BYPASS_SQL_WITH_DOMAIN);
	Map<String, String> d = rowsByTable.get("d");
	assertNotNull("EXPLAIN row for `d` (domain) missing", d);

	String type = d.get("type");
	String key = d.get("key");
	String extra = d.get("Extra") == null ? "" : d.get("Extra");

	assertTrue("domain access type is " + type + ", expected eq_ref/ref/const/index",
	"eq_ref".equals(type) \|\| "ref".equals(type) \|\| "const".equals(type) \|\| "index".equals(type));
	assertNotNull("domain should use SOME index", key);
	assertFalse("domain should not be 'Range checked for each record'; Extra=" + extra,
	extra.contains("Range checked for each record"));
	}

	@Test
	@Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
	public void bypassWithDomain_dataCenter_stillPkLookup() throws Exception {
	// Adding the domain join must not regress the data_center PK-lookup gain.
	Map<String, Map<String, String>> rowsByTable = explain(BYPASS_SQL_WITH_DOMAIN);
	Map<String, String> dc = rowsByTable.get("dc");
	assertNotNull("EXPLAIN row for `dc` missing in bypass+domain plan", dc);
	String type = dc.get("type");
	assertTrue("dc access type with domain join is " + type + ", expected eq_ref/ref/const",
	"eq_ref".equals(type) \|\| "ref".equals(type) \|\| "const".equals(type));
	assertEquals("PRIMARY", dc.get("key"));
	}

	@Test
	@Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
	public void bypassWithDomain_stillFewerTablesThanView() throws Exception {
	// Even with the conditional domain join, the bypass plan is still smaller than the legacy view.
	Map<String, Map<String, String>> bypass = explain(BYPASS_SQL_WITH_DOMAIN);
	Map<String, Map<String, String>> view = explain(VIEW_SQL);
	assertTrue(
	"bypass+domain plan (" + bypass.size() + " tables) should still join fewer than the view (" + view.size() + ")",
	bypass.size() < view.size());
	}

	@Test
	@Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
	public void bypassWithDomain_stillNoRowMultipliers() throws Exception {
	// Critical: adding the domain join must not pull in the row multipliers the bypass dropped.
	Map<String, Map<String, String>> rowsByTable = explain(BYPASS_SQL_WITH_DOMAIN);
	assertFalse("bypass+domain must not join resource_tags", rowsByTable.containsKey("resource_tags"));
	assertFalse("bypass+domain must not join vm_template_details", rowsByTable.containsKey("vm_template_details"));
	assertFalse("bypass+domain must not join launch_permission", rowsByTable.containsKey("launch_permission"));
	}

	// ============================================================================
	// Timing tests
	// ============================================================================

	/** Number of iterations per query; first 2 are warm-up. */
	private static final int TIMING_ITERATIONS = 5;
	/** Skip first N iterations from the average (warm-up). */
	private static final int TIMING_WARMUP = 2;
	/**
	* Tolerance for the timing assertion. With sparse test data both queries
	* complete in ~1 ms, where measurement noise can flip the order; allow the
	* bypass to be marginally slower without failing the test.
	*/
	private static final double TIMING_TOLERANCE_FACTOR = 2.0;
	/** Floor below which we don't bother asserting (pure JDBC overhead). */
	private static final long TIMING_NOISE_FLOOR_NS = 5_000_000L; // 5 ms

	@Test
	@Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
	public void bypass_isFasterThanLegacyView() throws Exception {
	long legacyAvgNs = averageQueryNanos(VIEW_SQL);
	long bypassAvgNs = averageQueryNanos(BYPASS_SQL);

	System.out.println(String.format(
	"Timing avg over %d iterations (warm-up %d): legacy=%.2f ms, bypass=%.2f ms (%.1fx speedup)",
	TIMING_ITERATIONS, TIMING_WARMUP,
	legacyAvgNs / 1_000_000.0,
	bypassAvgNs / 1_000_000.0,
	(double) legacyAvgNs / Math.max(bypassAvgNs, 1)));

	// Below the noise floor both queries are dominated by JDBC overhead;
	// the structural improvement is unmeasurable. Skip the strict comparison.
	if (legacyAvgNs < TIMING_NOISE_FLOOR_NS) {
	System.out.println("Skipping strict timing assertion: legacy duration below noise floor");
	return;
	}
	assertTrue(
	String.format("bypass (%.2f ms) should be at most %.1fx legacy (%.2f ms)",
	bypassAvgNs / 1_000_000.0, TIMING_TOLERANCE_FACTOR, legacyAvgNs / 1_000_000.0),
	bypassAvgNs <= (long) (legacyAvgNs * TIMING_TOLERANCE_FACTOR));
	}

	@Test
	@Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
	public void bypassWithDomain_isFasterThanLegacyView() throws Exception {
	long legacyAvgNs = averageQueryNanos(VIEW_SQL);
	long bypassAvgNs = averageQueryNanos(BYPASS_SQL_WITH_DOMAIN);

	System.out.println(String.format(
	"Timing avg (bypass+domain join): legacy=%.2f ms, bypass+domain=%.2f ms (%.1fx speedup)",
	legacyAvgNs / 1_000_000.0,
	bypassAvgNs / 1_000_000.0,
	(double) legacyAvgNs / Math.max(bypassAvgNs, 1)));

	if (legacyAvgNs < TIMING_NOISE_FLOOR_NS) {
	System.out.println("Skipping strict timing assertion: legacy duration below noise floor");
	return;
	}
	assertTrue(
	String.format("bypass+domain (%.2f ms) should be at most %.1fx legacy (%.2f ms)",
	bypassAvgNs / 1_000_000.0, TIMING_TOLERANCE_FACTOR, legacyAvgNs / 1_000_000.0),
	bypassAvgNs <= (long) (legacyAvgNs * TIMING_TOLERANCE_FACTOR));
	}

	// ============================================================================
	// Parity tests — bypass and legacy must return identical pair sets and counts
	// ============================================================================

	@Test
	@Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
	public void bypass_returnsSameTemplatePairSetAsLegacy() throws Exception {
	Set<String> legacy = collectColumn1(VIEW_SQL);
	Set<String> bypass = collectColumn1(BYPASS_SQL);

	assertEquals("legacy and bypass must return the same number of distinct pairs",
	legacy.size(), bypass.size());
	assertEquals("legacy and bypass must return identical pair values",
	legacy, bypass);
	}

	@Test
	@Ignore("Integration test - requires -Dtest.cloudstack.mysql.url; run manually against a CloudStack DB")
	public void bypass_distinctCountMatchesLegacy() throws Exception {
	long legacyCount = scalarLong(
	"SELECT COUNT(*) FROM (" + VIEW_SQL + ") AS t");
	long bypassCount = scalarLong(
	"SELECT COUNT(*) FROM (" + BYPASS_SQL + ") AS t");

	assertEquals("legacy and bypass DISTINCT counts must match", legacyCount, bypassCount);
	}

	/** Collect column 1 of every row into a sorted set (for set equality assertion). */
	private Set<String> collectColumn1(String query) throws Exception {
	Set<String> out = new TreeSet<>();
	try (Statement st = conn.createStatement();
	ResultSet rs = st.executeQuery(query)) {
	while (rs.next()) {
	out.add(rs.getString(1));
	}
	}
	return out;
	}

	/** Run a query that returns one numeric scalar in column 1. */
	private long scalarLong(String query) throws Exception {
	try (Statement st = conn.createStatement();
	ResultSet rs = st.executeQuery(query)) {
	if (rs.next()) {
	return rs.getLong(1);
	}
	return 0L;
	}
	}

	/**
	* Run the query {@link #TIMING_ITERATIONS} times, drop the first
	* {@link #TIMING_WARMUP} as warm-up, return the average wall-clock duration
	* of the remaining iterations in nanoseconds. Each iteration drains the
	* ResultSet so we measure execution + transport, not just submission.
	*/
	private long averageQueryNanos(String query) throws Exception {
	long total = 0;
	int counted = 0;
	for (int i = 0; i < TIMING_ITERATIONS; i++) {
	long start = System.nanoTime();
	try (Statement st = conn.createStatement();
	ResultSet rs = st.executeQuery(query)) {
	while (rs.next()) {
	rs.getString(1);
	}
	}
	long elapsed = System.nanoTime() - start;
	if (i >= TIMING_WARMUP) {
	total += elapsed;
	counted++;
	}
	}
	return counted == 0 ? 0 : total / counted;
	}

	/**
	* Run EXPLAIN and return one map per table alias (or table name) → column→value.
	* Uses traditional EXPLAIN format so column names match across MySQL versions.
	* Also logs the raw EXPLAIN rows so test output makes plan differences visible.
	*/
	private Map<String, Map<String, String>> explain(String query) throws Exception {
	Map<String, Map<String, String>> out = new LinkedHashMap<>();
	try (Statement st = conn.createStatement();
	ResultSet rs = st.executeQuery("EXPLAIN " + query)) {
	ResultSetMetaData md = rs.getMetaData();
	int colCount = md.getColumnCount();

	StringBuilder log = new StringBuilder("\nEXPLAIN result for query:\n ").append(query).append('\n');
	// header
	for (int i = 1; i <= colCount; i++) {
	log.append(md.getColumnLabel(i)).append('\t');
	}
	log.append('\n');

	while (rs.next()) {
	Map<String, String> row = new LinkedHashMap<>();
	for (int i = 1; i <= colCount; i++) {
	String val = rs.getString(i);
	row.put(md.getColumnLabel(i), val);
	log.append(val == null ? "NULL" : val).append('\t');
	}
	log.append('\n');
	String tableKey = row.get("table");
	if (tableKey != null) {
	out.put(tableKey, row);
	}
	}
	// uncomment if need verbose EXPLAIN output
	// System.out.println(log.toString());
	}
	return out;
	}
	}