public static void doTestAddToMap(Map map, String description, PrecomputedTable pt, Query q, String expected) { try { BestQueryStorer bestQuery = new BestQueryStorer(); StringUtil.setNextUniqueNumber(42); QueryOptimiser.recursiveOptimiseCheckSubquery(Collections.singleton(pt), q, bestQuery); if (expected == null) { addIfNE(map, description, Collections.EMPTY_SET, bestQuery.getQueries()); } else { addIfNE(map, description, Collections.singleton(new Query(expected)), bestQuery.getQueries()); } } catch (Exception e) { StringWriter sw = new StringWriter(); PrintWriter pw = new PrintWriter(sw); e.printStackTrace(pw); pw.flush(); map.put(description, sw.toString()); } }
public void testRichardsBug() throws Exception { Query q1 = new Query("SELECT DISTINCT a1_.identifier AS a17_, a15_.primaryAccession AS a18_, a15_.identifier AS a19_, a12_.identifier AS a20_, a12_.primaryAccession AS a21_, a14_.shortName AS a22_ FROM Gene AS a1_, Orthologue AS a2_, Gene AS a3_, Protein AS a4_, ProteinInteractor AS a5_, ProteinInteraction AS a6_, ProteinInteractor AS a7_, Protein AS a8_, Gene AS a9_, Orthologue AS a10_, Gene AS a11_, Protein AS a12_, Organism AS a13_, Organism AS a14_, Protein AS a15_, Organism AS a16_, GeneOrthologues AS indirect0, GenesProteins AS indirect1, GenesProteins AS indirect2, GeneOrthologues AS indirect3, GenesProteins AS indirect4, GenesProteins AS indirect5 WHERE a8_.id != a4_.id AND a16_.id = a13_.id AND LOWER(a1_.identifier) = 'cg3481' AND a1_.id = indirect0.Orthologues AND indirect0.Gene = a2_.id AND a2_.subjectId = a3_.id AND a3_.id = indirect1.Proteins AND indirect1.Genes = a4_.id AND a4_.id = a5_.proteinId AND a5_.interactionId = a6_.id AND a6_.id = a7_.interactionId AND a7_.proteinId = a8_.id AND a8_.id = indirect2.Genes AND indirect2.Proteins = a9_.id AND a9_.id = indirect3.Orthologues AND indirect3.Gene = a10_.id AND a10_.subjectId = a11_.id AND a11_.id = indirect4.Proteins AND indirect4.Genes = a12_.id AND a12_.organismId = a13_.id AND a3_.organismId = a14_.id AND a1_.id = indirect5.Proteins AND indirect5.Genes = a15_.id AND a15_.organismId = a16_.id ORDER BY a1_.identifier, a15_.primaryAccession, a15_.identifier, a12_.identifier, a12_.primaryAccession, a14_.shortName"); Query pq1 = new Query("SELECT DISTINCT a1_.identifier AS a17_, a15_.primaryAccession AS a18_, a15_.identifier AS a19_, a12_.identifier AS a20_, a12_.primaryAccession AS a21_, a14_.shortName AS a22_, a1_.identifier AS a23_ FROM Gene AS a1_, Orthologue AS a2_, Gene AS a3_, Protein AS a4_, ProteinInteractor AS a5_, ProteinInteraction AS a6_, ProteinInteractor AS a7_, Protein AS a8_, Gene AS a9_, Orthologue AS a10_, Gene AS a11_, Protein AS a12_, Organism AS a13_, Organism AS a14_, Protein AS a15_, Organism AS a16_, GeneOrthologues AS indirect0, GenesProteins AS indirect1, GenesProteins AS indirect2, GeneOrthologues AS indirect3, GenesProteins AS indirect4, GenesProteins AS indirect5 WHERE a8_.id != a4_.id AND a16_.id = a13_.id AND a1_.id = indirect0.Orthologues AND indirect0.Gene = a2_.id AND a2_.subjectId = a3_.id AND a3_.id = indirect1.Proteins AND indirect1.Genes = a4_.id AND a4_.id = a5_.proteinId AND a5_.interactionId = a6_.id AND a6_.id = a7_.interactionId AND a7_.proteinId = a8_.id AND a8_.id = indirect2.Genes AND indirect2.Proteins = a9_.id AND a9_.id = indirect3.Orthologues AND indirect3.Gene = a10_.id AND a10_.subjectId = a11_.id AND a11_.id = indirect4.Proteins AND indirect4.Genes = a12_.id AND a12_.organismId = a13_.id AND a3_.organismId = a14_.id AND a1_.id = indirect5.Proteins AND indirect5.Genes = a15_.id AND a15_.organismId = a16_.id ORDER BY a1_.identifier, a15_.primaryAccession, a15_.identifier, a12_.identifier, a12_.primaryAccession, a14_.shortName, a1_.identifier"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); Set precomps = new HashSet(); precomps.add(pt1); StringUtil.setNextUniqueNumber(42); BestQueryStorer bestQuery = new BestQueryStorer(); QueryOptimiser.recursiveOptimiseCheckSubquery(precomps, q1, bestQuery); Set eSet = new HashSet(); eSet.add(new Query("SELECT DISTINCT P42.a23_ AS a17_, P42.a18_, P42.a19_, P42.a20_, P42.a21_, P42.a22_ FROM precomp1 AS P42 WHERE LOWER(P42.a23_) = 'cg3481' ORDER BY P42.a23_, P42.a18_, P42.a19_, P42.a20_, P42.a21_, P42.a22_")); assertEquals(eSet, bestQuery.getQueries()); }
public void testKimsBug5() throws Exception { try { con.createStatement().execute("CREATE TABLE SequenceFeature (id int NOT NULL)"); con.createStatement().execute("CREATE TABLE OverlapRelation (id int NOT NULL);"); Query q1 = new Query("SELECT a1_.id AS a1_id, a2_.id AS a2_id, a3_.id AS a3_id FROM SequenceFeature AS a1_, OverlapRelation AS a2_, SequenceFeature AS a3_, BioEntitiesRelations AS indirect0, BioEntitiesRelations AS indirect1 WHERE a2_.id = indirect0.BioEntities AND indirect0.Relations = a1_.id AND a2_.id = indirect1.BioEntities AND indirect1.Relations = a3_.id AND a1_.id > 24081631 ORDER BY a1_.id, a2_.id, a3_.id"); Query pq1 = new Query("SELECT a1_.id AS a1_id, a2_.id AS a2_id, a3_.id AS a3_id FROM SequenceFeature AS a1_, OverlapRelation AS a2_, SequenceFeature AS a3_, BioEntitiesRelations AS indirect0, BioEntitiesRelations AS indirect1 WHERE a2_.id = indirect0.BioEntities AND indirect0.Relations = a1_.id AND a2_.id = indirect1.BioEntities AND indirect1.Relations = a3_.id ORDER BY a1_.id, a2_.id, a3_.id"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); Set precomps = new LinkedHashSet(); precomps.add(pt1); //System.out.println(pt1.getSQLString() + " ---- " + pt1.getOrderByField()); StringUtil.setNextUniqueNumber(42); BestQueryStorer bestQuery = new BestQueryStorer(); QueryOptimiser.recursiveOptimiseCheckSubquery(precomps, q1, bestQuery); Set eSet = new ConsistentSet(); eSet.add(new Query("SELECT P46.a1_id, P46.a2_id, P46.a3_id FROM precomp1 AS P46 WHERE 240816315000000000000000000050000000000000000000 < P46.orderby_field ORDER BY P46.orderby_field")); eSet.add(new Query("SELECT P49.a3_id AS a1_id, P49.a2_id, P49.a1_id AS a3_id FROM precomp1 AS P49 WHERE 24081631 < P49.a3_id ORDER BY P49.a3_id, P49.a2_id, P49.a1_id")); assertEquals(eSet, bestQuery.getQueries()); } finally { try { con.createStatement().execute("DROP TABLE SequenceFeature"); } catch (SQLException e) { } try { con.createStatement().execute("DROP TABLE OverlapRelation"); } catch (SQLException e) { } } }
public void testKimsBug4() throws Exception { try { con.createStatement().execute("CREATE TABLE SequenceFeature (id int)"); con.createStatement().execute("CREATE TABLE OverlapRelation (id int);"); Query q1 = new Query("SELECT a1_.id AS a1_id, a2_.id AS a2_id, a3_.id AS a3_id FROM SequenceFeature AS a1_, OverlapRelation AS a2_, SequenceFeature AS a3_, BioEntitiesRelations AS indirect0, BioEntitiesRelations AS indirect1 WHERE a2_.id = indirect0.BioEntities AND indirect0.Relations = a1_.id AND a2_.id = indirect1.BioEntities AND indirect1.Relations = a3_.id AND a1_.id > 24081631 ORDER BY a1_.id, a2_.id, a3_.id"); Query pq1 = new Query("SELECT a1_.id AS a1_id, a2_.id AS a2_id, a3_.id AS a3_id FROM SequenceFeature AS a1_, OverlapRelation AS a2_, SequenceFeature AS a3_, BioEntitiesRelations AS indirect0, BioEntitiesRelations AS indirect1 WHERE a2_.id = indirect0.BioEntities AND indirect0.Relations = a1_.id AND a2_.id = indirect1.BioEntities AND indirect1.Relations = a3_.id ORDER BY a1_.id, a2_.id, a3_.id"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); Set precomps = new LinkedHashSet(); precomps.add(pt1); //System.out.println(pt1.getSQLString() + " ---- " + pt1.getOrderByField()); StringUtil.setNextUniqueNumber(42); BestQueryStorer bestQuery = new BestQueryStorer(); QueryOptimiser.recursiveOptimiseCheckSubquery(precomps, q1, bestQuery); Set eSet = new ConsistentSet(); eSet.add(new Query("SELECT P46.a1_id, P46.a2_id, P46.a3_id FROM precomp1 AS P46 WHERE 24081631 < P46.a1_id ORDER BY P46.orderby_field")); eSet.add(new Query("SELECT P49.a3_id AS a1_id, P49.a2_id, P49.a1_id AS a3_id FROM precomp1 AS P49 WHERE 24081631 < P49.a3_id ORDER BY P49.a3_id, P49.a2_id, P49.a1_id")); assertEquals(eSet, bestQuery.getQueries()); } finally { try { con.createStatement().execute("DROP TABLE SequenceFeature"); } catch (SQLException e) { } try { con.createStatement().execute("DROP TABLE OverlapRelation"); } catch (SQLException e) { } } }
public void testMergeGroupByFits() throws Exception { Query q1 = new Query("SELECT table1.a AS t1_a, table1.b AS t1_b, count(*) as stuff from table as table1, somethingelse as table2 WHERE table1.c = table2.a GROUP BY table1.a, table1.b, table1.d HAVING table1.d = 'five' ORDER BY table1.a LIMIT 100 OFFSET 0"); Query pq1 = new Query("SELECT table3.a AS sahjg, table3.b AS aytq, count(*) AS hksf, table3.d AS fdjsa FROM table AS table3, somethingelse AS table4 WHERE table3.c = table4.a GROUP BY table3.a, table3.b, table3.d"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); Query eq1 = new Query("SELECT P42.sahjg AS t1_a, P42.aytq AS t1_b, P42.hksf AS stuff from precomp1 AS P42 WHERE P42.fdjsa = 'five' ORDER BY P42.sahjg LIMIT 100 OFFSET 0"); Set eSet = new HashSet(); eSet.add(eq1); StringUtil.setNextUniqueNumber(42); Set newSet = QueryOptimiser.mergeGroupBy(pt1, q1, q1); assertEquals(eSet, newSet); }
public void testMergeSimple() throws Exception { Query q1 = new Query("SELECT table1.a AS t1_a, table1.b AS t1_b from table as table1 WHERE table1.c = 'five'"); Query pq1 = new Query("SELECT table2.a AS kjfd, table2.b AS ddfw FROM table as table2 WHERE table2.c = 'five'"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); Query eq1 = new Query("SELECT P42.kjfd AS t1_a, P42.ddfw AS t1_b FROM precomp1 AS P42"); Set eSet = new HashSet(); eSet.add(eq1); StringUtil.setNextUniqueNumber(42); Set newSet = QueryOptimiser.merge(pt1, q1, q1); assertEquals(eSet, newSet); }
public void testMergeOkayDistinct1() throws Exception { Query q1 = new Query("SELECT DISTINCT table1.a AS t1_a, table1.b AS t1_b from table as table1 WHERE table1.c = 'five'"); Query pq1 = new Query("SELECT table2.a AS kjfd, table2.b AS ddfw FROM table as table2 WHERE table2.c = 'five'"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); Query eq1 = new Query("SELECT DISTINCT P42.kjfd AS t1_a, P42.ddfw AS t1_b FROM precomp1 AS P42"); Set eSet = new HashSet(); eSet.add(eq1); StringUtil.setNextUniqueNumber(42); Set newSet = QueryOptimiser.merge(pt1, q1, q1); assertEquals(eSet, newSet); }
public void testMergeGroupByWithUselessConstraint() throws Exception { Query q1 = new Query("SELECT table1.a AS t1_a, table1.b AS t1_b, count(*) as stuff from table as table1, somethingelse as table2 WHERE table1.c = table2.a GROUP BY table1.a, table1.b, table1.d HAVING table1.d = 'five' ORDER BY table1.a LIMIT 100 OFFSET 0"); Query pq1 = new Query("SELECT table3.a AS sahjg, table3.b AS aytq, count(*) AS hksf, table3.d AS fdjsa FROM table AS table3, somethingelse AS table4 WHERE table3.c = table4.a GROUP BY table3.a, table3.b, table3.d HAVING table3.d = 'five'"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); Query eq1 = new Query("SELECT P42.sahjg AS t1_a, P42.aytq AS t1_b, P42.hksf AS stuff from precomp1 AS P42 ORDER BY P42.sahjg LIMIT 100 OFFSET 0"); Set eSet = new HashSet(); eSet.add(eq1); StringUtil.setNextUniqueNumber(42); Set newSet = QueryOptimiser.mergeGroupBy(pt1, q1, q1); assertEquals(eSet, newSet); }
public void testMergeOkayGroupBy() throws Exception { Query q1 = new Query("SELECT table1.a AS t1_a, table1.b AS t1_b from table as table1 WHERE table1.c = 'five' GROUP BY table1.a, table1.b HAVING table1.a = 'six'"); Query pq1 = new Query("SELECT table2.a AS kjfd, table2.b AS ddfw FROM table as table2 WHERE table2.c = 'five'"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); Query eq1 = new Query("SELECT P42.kjfd AS t1_a, P42.ddfw AS t1_b FROM precomp1 AS P42 GROUP BY P42.kjfd, P42.ddfw HAVING P42.kjfd = 'six'"); Set eSet = new HashSet(); eSet.add(eq1); StringUtil.setNextUniqueNumber(42); Set newSet = QueryOptimiser.merge(pt1, q1, q1); assertEquals(eSet, newSet); }
public void testMergeOkayDistinct2() throws Exception { Query q1 = new Query("SELECT DISTINCT table1.a AS t1_a, table1.b AS t1_b from table as table1 WHERE table1.c = 'five'"); Query pq1 = new Query("SELECT DISTINCT table2.a AS kjfd, table2.b AS ddfw FROM table as table2 WHERE table2.c = 'five'"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); Query eq1 = new Query("SELECT DISTINCT P42.kjfd AS t1_a, P42.ddfw AS t1_b FROM precomp1 AS P42"); Set eSet = new HashSet(); eSet.add(eq1); StringUtil.setNextUniqueNumber(42); Set newSet = QueryOptimiser.merge(pt1, q1, q1); assertEquals(eSet, newSet); }
public void testRemapAliasesToAvoidPrecomputePrefix() throws Exception { Query q1 = new Query("SELECT table1.a, Putty.b FROM table1, table AS Putty"); Query eq1 = new Query("SELECT table1.a, P42.b FROM table1, table AS P42"); StringUtil.setNextUniqueNumber(42); QueryOptimiser.remapAliasesToAvoidPrecomputePrefix(q1); assertEquals(eq1, q1); }
eSet.add(eq2); StringUtil.setNextUniqueNumber(42); Set newSet = QueryOptimiser.merge(pt1, q1, q1);
public void testMergeMultiple() throws Exception { Query q1 = new Query("SELECT table1.a AS t1_a, table1.b AS t1_b, table2.a AS t2_a, table2.b AS t2_b FROM table1, table2 WHERE table1.c = 'five' AND table2.c = 'six'"); Query pq1 = new Query("SELECT table1.a AS fhjs, table1.b AS sjhf FROM table1 WHERE table1.c = 'five'"); Query pq2 = new Query("SELECT table2.a AS kjsd, table2.b AS hjas FROM table2 WHERE table2.c = 'six'"); Query pq3 = new Query("SELECT table2.a AS kjsd, table2.b AS hjas FROM table2 WHERE table2.c = 'seven'"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); PrecomputedTable pt2 = new PrecomputedTable(pq2, pq2.getSQLString(), "precomp2", null, con); PrecomputedTable pt3 = new PrecomputedTable(pq3, pq3.getSQLString(), "precomp3", null, con); Set precomps = new LinkedHashSet(); precomps.add(pt1); precomps.add(pt2); precomps.add(pt3); Query eq1 = new Query("SELECT P42.fhjs AS t1_a, P42.sjhf AS t1_b, table2.a AS t2_a, table2.b AS t2_b FROM precomp1 AS P42, table2 WHERE table2.c = 'six'"); Query eq2 = new Query("SELECT table1.a AS t1_a, table1.b AS t1_b, P43.kjsd AS t2_a, P43.hjas AS t2_b FROM table1, precomp2 AS P43 WHERE table1.c = 'five'"); Map eMap = new HashMap(); eMap.put(pt1, Collections.singleton(eq1)); eMap.put(pt2, Collections.singleton(eq2)); StringUtil.setNextUniqueNumber(42); SortedMap newMap = QueryOptimiser.mergeMultiple(precomps, q1, q1); assertEquals(eMap, newMap); }
public void testRecursiveOptimise2() throws Exception { Query q1 = new Query("SELECT table1.a AS t1_a, table1.b AS t1_b, table2.a AS t2_a, table2.b AS t2_b FROM table AS table1, table AS table2 WHERE table1.c = 'five' AND table2.c = 'five'"); Query pq1 = new Query("SELECT table1.a AS fhjs, table1.b AS sjhf FROM table AS table1 WHERE table1.c = 'five'"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); Set precomps = new LinkedHashSet(); precomps.add(pt1); Query eq3 = new Query("SELECT P44.fhjs AS t1_a, P44.sjhf AS t1_b, table1.a AS t2_a, table1.b AS t2_b FROM precomp1 AS P44, table AS table1 WHERE table1.c = 'five'"); Query eq1 = new Query("SELECT P45.fhjs AS t1_a, P45.sjhf AS t1_b, P47.fhjs AS t2_a, P47.sjhf AS t2_b FROM precomp1 AS P47, precomp1 AS P45"); Query eq2 = new Query("SELECT P46.a AS t1_a, P46.b AS t1_b, P48.fhjs AS t2_a, P48.sjhf AS t2_b FROM precomp1 AS P48, table AS P46 WHERE P46.c = 'five'"); Set eSet = new ConsistentSet(); eSet.add(eq1); eSet.add(eq2); eSet.add(eq3); StringUtil.setNextUniqueNumber(42); BestQueryStorer bestQuery = new BestQueryStorer(); QueryOptimiser.recursiveOptimiseCheckSubquery(precomps, q1, bestQuery); assertEquals(eSet, bestQuery.getQueries()); }
eSet.add(eq3); StringUtil.setNextUniqueNumber(42); Set newSet = QueryOptimiser.merge(pt1, q1, q1);
public void testRecursiveOptimise1() throws Exception { Query q1 = new Query("SELECT table1.a AS t1_a, table1.b AS t1_b, table2.a AS t2_a, table2.b AS t2_b FROM table1, table2 WHERE table1.c = 'five' AND table2.c = 'six'"); Query pq1 = new Query("SELECT table1.a AS fhjs, table1.b AS sjhf FROM table1 WHERE table1.c = 'five'"); Query pq2 = new Query("SELECT table2.a AS kjsd, table2.b AS hjas FROM table2 WHERE table2.c = 'six'"); Query pq3 = new Query("SELECT table2.a AS kjsd, table2.b AS hjas FROM table2 WHERE table2.c = 'seven'"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); PrecomputedTable pt2 = new PrecomputedTable(pq2, pq2.getSQLString(), "precomp2", null, con); PrecomputedTable pt3 = new PrecomputedTable(pq3, pq3.getSQLString(), "precomp3", null, con); Set precomps = new LinkedHashSet(); precomps.add(pt1); precomps.add(pt2); precomps.add(pt3); Query eq1 = new Query("SELECT P42.fhjs AS t1_a, P42.sjhf AS t1_b, table2.a AS t2_a, table2.b AS t2_b FROM precomp1 AS P42, table2 WHERE table2.c = 'six'"); Query eq2 = new Query("SELECT table1.a AS t1_a, table1.b AS t1_b, P43.kjsd AS t2_a, P43.hjas AS t2_b FROM table1, precomp2 AS P43 WHERE table1.c = 'five'"); Query eq3 = new Query("SELECT P44.fhjs AS t1_a, P44.sjhf AS t1_b, P43.kjsd AS t2_a, P43.hjas AS t2_b FROM precomp1 AS P44, precomp2 AS P43"); Set eSet = new ConsistentSet(); eSet.add(eq1); eSet.add(eq2); eSet.add(eq3); StringUtil.setNextUniqueNumber(42); BestQueryStorer bestQuery = new BestQueryStorer(); QueryOptimiser.recursiveOptimiseCheckSubquery(precomps, q1, bestQuery); assertEquals(eSet, bestQuery.getQueries()); }
public void testRecursiveOptimise3() throws Exception { Query q1 = new Query("SELECT table1.a AS t1_a, table1.b AS t1_b, table2.a AS t2_a, table2.b AS t2_b FROM table1, table2 WHERE table1.c = 'five' AND table2.c = 'six' AND table1.d = table2.d"); Query pq1 = new Query("SELECT table1.a AS fhjs, table1.b AS sjhf, table1.d AS kjhds FROM table1 WHERE table1.c = 'five'"); Query pq2 = new Query("SELECT table2.a AS kjsd, table2.b AS hjas, table2.d AS kjhsd FROM table2 WHERE table2.c = 'six'"); Query pq3 = new Query("SELECT table2.a AS kjsd, table2.b AS hjas, table2.d AS jsdff FROM table2 WHERE table2.c = 'seven'"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); PrecomputedTable pt2 = new PrecomputedTable(pq2, pq2.getSQLString(), "precomp2", null, con); PrecomputedTable pt3 = new PrecomputedTable(pq3, pq3.getSQLString(), "precomp3", null, con); Set precomps = new LinkedHashSet(); precomps.add(pt1); precomps.add(pt2); precomps.add(pt3); Query eq1 = new Query("SELECT P42.fhjs AS t1_a, P42.sjhf AS t1_b, table2.a AS t2_a, table2.b AS t2_b FROM precomp1 AS P42, table2 WHERE table2.c = 'six' AND P42.kjhds = table2.d"); Query eq2 = new Query("SELECT table1.a AS t1_a, table1.b AS t1_b, P43.kjsd AS t2_a, P43.hjas AS t2_b FROM table1, precomp2 AS P43 WHERE table1.c = 'five' AND table1.d = P43.kjhsd"); Query eq3 = new Query("SELECT P44.fhjs AS t1_a, P44.sjhf AS t1_b, P43.kjsd AS t2_a, P43.hjas AS t2_b FROM precomp1 AS P44, precomp2 AS P43 WHERE P44.kjhds = P43.kjhsd"); Set eSet = new ConsistentSet(); eSet.add(eq1); eSet.add(eq2); eSet.add(eq3); StringUtil.setNextUniqueNumber(42); BestQueryStorer bestQuery = new BestQueryStorer(); QueryOptimiser.recursiveOptimiseCheckSubquery(precomps, q1, bestQuery); assertEquals(eSet, bestQuery.getQueries()); }
public void testKimsBug3() throws Exception { Query q1 = new Query("SELECT a1_.a AS a2_ FROM Chromosome AS a1_ ORDER BY a1_.a"); Query q2 = new Query("SELECT a1_.a AS a2_ FROM Chromosome AS a1_ WHERE a1_.a > 5325019 ORDER BY a1_.a"); Query pq1 = new Query("SELECT a1_.a AS a2_ FROM Chromosome AS a1_ ORDER BY a1_.a"); Query pq2 = new Query("SELECT a1_.a AS a2_ FROM Chromosome AS a1_ WHERE a1_.a = 10669827 ORDER BY a1_.a"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); PrecomputedTable pt2 = new PrecomputedTable(pq2, pq2.getSQLString(), "precomp2", null, con); Set precomps = new LinkedHashSet(); precomps.add(pt1); precomps.add(pt2); Query eq1 = new Query("SELECT P42.a2_ FROM precomp1 AS P42 ORDER BY P42.a2_"); Set eSet = new ConsistentSet(); eSet.add(eq1); StringUtil.setNextUniqueNumber(42); BestQueryStorer bestQuery = new BestQueryStorer(); QueryOptimiser.recursiveOptimiseCheckSubquery(precomps, q1, bestQuery); assertEquals(eSet, bestQuery.getQueries()); Query eq2 = new Query("SELECT P42.a2_ FROM precomp1 AS P42 WHERE P42.a2_ > 5325019 ORDER BY P42.a2_"); eSet = new ConsistentSet(); eSet.add(eq2); StringUtil.setNextUniqueNumber(42); bestQuery = new BestQueryStorer(); QueryOptimiser.recursiveOptimiseCheckSubquery(precomps, q2, bestQuery); assertEquals(eSet, bestQuery.getQueries()); }
public void testKimsBug() throws Exception { Query q1 = new Query("SELECT a1_.id AS a2_, a3_.OBJECT AS a3_, a3_.id AS a3_id, a4_.OBJECT AS a4_, a4_.id AS a4_id FROM Chromosome AS a1_, BioEntity AS a3_, Location AS a4_ WHERE a4_.locatedOnId = a1_.id AND a4_.featureId = a3_.id ORDER BY a1_.id, a3_.id, a4_.id"); Query q2 = new Query("SELECT a1_.id AS a2_, a3_.OBJECT AS a3_, a3_.id AS a3_id, a4_.OBJECT AS a4_, a4_.id AS a4_id FROM Chromosome AS a1_, BioEntity AS a3_, Location AS a4_ WHERE (a4_.locatedOnId = a1_.id AND a4_.featureId = a3_.id) AND a1_.id > 5325019 ORDER BY a1_.id, a3_.id, a4_.id"); Query pq1 = new Query("SELECT a1_.id AS a2_, a3_.OBJECT AS a3_, a3_.id AS a3_id, a4_.OBJECT AS a4_, a4_.id AS a4_id FROM Chromosome AS a1_, BioEntity AS a3_, Location AS a4_ WHERE a4_.locatedOnId = a1_.id AND a4_.featureId = a3_.id ORDER BY a1_.id, a3_.id, a4_.id"); Query pq2 = new Query("SELECT a1_.id AS a2_, a3_.OBJECT AS a3_, a3_.id AS a3_id, a4_.OBJECT AS a4_, a4_.id AS a4_id FROM Chromosome AS a1_, BioEntity AS a3_, Location AS a4_ WHERE a4_.locatedOnId = a1_.id AND a4_.featureId = a3_.id AND a1_.id = 10669827 ORDER BY a1_.id, a3_.id, a4_.id"); PrecomputedTable pt1 = new PrecomputedTable(pq1, pq1.getSQLString(), "precomp1", null, con); PrecomputedTable pt2 = new PrecomputedTable(pq2, pq2.getSQLString(), "precomp2", null, con); Set precomps = new LinkedHashSet(); precomps.add(pt1); precomps.add(pt2); Query eq1 = new Query("SELECT P42.a2_, P42.a3_, P42.a3_id, P42.a4_, P42.a4_id FROM precomp1 AS P42 ORDER BY P42.a2_, P42.a3_id, P42.a4_id"); Set eSet = new ConsistentSet(); eSet.add(eq1); StringUtil.setNextUniqueNumber(42); BestQueryStorer bestQuery = new BestQueryStorer(); QueryOptimiser.recursiveOptimiseCheckSubquery(precomps, q1, bestQuery); assertEquals(eSet, bestQuery.getQueries()); Query eq2 = new Query("SELECT P42.a2_, P42.a3_, P42.a3_id, P42.a4_, P42.a4_id FROM precomp1 AS P42 WHERE P42.a2_ > 5325019 ORDER BY P42.a2_, P42.a3_id, P42.a4_id"); eSet = new ConsistentSet(); eSet.add(eq2); StringUtil.setNextUniqueNumber(42); bestQuery = new BestQueryStorer(); QueryOptimiser.recursiveOptimiseCheckSubquery(precomps, q2, bestQuery); assertEquals(eSet, bestQuery.getQueries()); }
public void testCacheFlush() throws Throwable { //executeTest("table2Table3JoinOnCol1"); //testQueries(); StringUtil.setNextUniqueNumber(35); String sql1 = "SELECT table2.col1, table2.col2, table3.col1, table3.col2 FROM table2, table3 WHERE table2.col1 = table3.col1 ORDER BY table2.col1, table2.col2, table3.col1, table3.col2"; //String sql1 = "SELECT table3.col1 AS table3_col1, table3.col2 AS table3_col2 FROM table3 WHERE table3.col1 < " + (DATA_SIZE/2); String sqlOpt1 = QueryOptimiser.optimise(sql1, getDatabase()); assertEquals("SELECT P35.table2_col1 AS col1, P35.table2_col2 AS col2, P35.table3_col1 AS col1, P35.table3_col2 AS col2 FROM precomp_table2Table3onCol1 AS P35 ORDER BY P35.orderby_field", sqlOpt1); PrecomputedTableManager ptm = PrecomputedTableManager.getInstance(getDatabase()); ptm.delete(toDelete); String sqlOpt2 = QueryOptimiser.optimise(sql1, getDatabase()); assertEquals(sql1, sqlOpt2); } }