<commit_before>package com.tesora.dve.sql.transform;

/*
 * #%L
 * Tesora Inc.
 * Database Virtualization Engine
 * %%
 * Copyright (C) 2011 - 2014 Tesora Inc.
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License, version 3,
 * as published by the Free Software Foundation.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 * #L%
 */

import org.junit.Assert;
import org.junit.Test;

import com.tesora.dve.distribution.BroadcastDistributionModel;
import com.tesora.dve.distribution.StaticDistributionModel;
import com.tesora.dve.sql.SchemaTest;
import com.tesora.dve.sql.schema.PEPersistentGroup;
import com.tesora.dve.sql.schema.SchemaContext;
import com.tesora.dve.sql.statement.dml.SelectStatement;
import com.tesora.dve.sql.transexec.TransientExecutionEngine;
import com.tesora.dve.sql.transform.execution.DMLExplainReason;
import com.tesora.dve.sql.transform.execution.DMLExplainRecord;
import com.tesora.dve.sql.transform.execution.ExecutionType;
import com.tesora.dve.sql.util.TestName;

public class AggregationTransformTest extends TransformTest {

	public AggregationTransformTest() {
		super("AggregationTransformTest");
	}
	
	private static final String[] leftySchema = new String[] {
		"create table `titles` (`id` int unsigned not null, `name` varchar(50) not null) static distribute on (`id`)",
		"create table `states` (`id` int unsigned not null, `name` varchar(50) not null, `tag` varchar(50)) static distribute on (`id`)",
		"create table `laws` (`id` int unsigned not null, `state_id` int unsigned not null, `title_id` int unsigned not null, " 
			+ "`status` varchar(16) not null default 'unpublished', `version` int unsigned not null, `law` varchar(100)) "
			+ "static distribute on (`id`)",
		"create table `counties` (`id` int unsigned not null, `name` varchar(50) not null, `state_id` int unsigned not null) " +
			"static distribute on (`id`)",
		"create table `courts` (`id` int unsigned not null, `county_id` int unsigned not null, `address` varchar(50) not null) " +
			"static distribute on (`id`)"
	};
	
	private static final String[] aggTestSchema = new String[] {
		"create table `B` ( `id` int, `sid` int, `pa` int(10), `pb` int(10), primary key (`id`) ) broadcast distribute",
		"create table `S` ( `id` int, `sid` int, `pa` int(10), `pb` int(10), primary key (`id`) ) static distribute on (`id`)",
		"create table `A` ( `id` int, `sid` int, `pa` int(10), `pb` int(10), primary key (`id`) ) random distribute",
		"create table `R` ( `id` int, `sid` int, `pa` int(10), `pb` int(10), primary key (`id`) ) range distribute on (`id`) using openrange",
	};
	
	@Test
	public void simpleMultiMaxTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select MAX(version) from laws",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT MAX( `laws`.`version` )  AS func_4 FROM `laws`",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT MAX( temp1.func_4 )  AS func FROM temp1",
								null)));
	}
	
	
	@Test
	public void simpleMultiMinTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select MIN ( version ) from laws",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT MIN( `laws`.`version` )  AS func_4 FROM `laws`",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT MIN( temp1.func_4 )  AS func FROM temp1",
								null)));
		
	}
	
	
	@Test
	public void simpleMultiCountTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select count(*) from laws",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT COUNT( * )  AS func_4 FROM `laws`",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT CONVERT( SUM( temp1.func_4 ) ,SIGNED )  AS func FROM temp1",
								null)));
		
	}

	
	@Test
	public void simpleMultiSumTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select SUM(version) from laws",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT SUM( `laws`.`version` )  AS func_4 FROM `laws`",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT SUM( temp1.func_4 )  AS func FROM temp1",
								null)));
		
	}

	
	@Test
	public void simpleMultiAvgTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select avg(version) from laws",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT SUM( `laws`.`version` )  AS func_4,COUNT( `laws`.`version` )  AS func_5 FROM `laws`",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT  (SUM( temp1.func_4 )  / SUM( temp1.func_5 ) )  AS func FROM temp1",
								null)
				));
	}

	
	@Test
	public void simpleSingleSumTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		stmtTest(db,
				"select SUM(version) from laws where id = 15",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT SUM( `laws`.`version` )  AS func_5 FROM `laws` WHERE `laws`.`id` = 15",
								null )));
	}

	
	@Test
	public void simpleSingleCountTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		stmtTest(db,
				"select count(*) from laws where id = 15",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT COUNT( * )  AS func_5 FROM `laws` WHERE `laws`.`id` = 15",
								null )));
	}

	
	@Test
	public void simpleSingleMaxTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		stmtTest(db,
				"select MAX(version) from laws where id = 15",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT MAX( `laws`.`version` )  AS func_5 FROM `laws` WHERE `laws`.`id` = 15",
								null )));
	}

	
	
	@Test
	public void simpleSingleAvgTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		stmtTest(db,
				"select avg(version) from laws where id = 15",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT  (SUM( `laws`.`version` )  / COUNT( `laws`.`version` ) )  AS func_5 FROM `laws` WHERE `laws`.`id` = 15",
								null)));
	}
	
	
	@Test 
	public void testPE396A() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select l.state_id + l.title_id + MAX(l.version) from laws l",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT l.state_id + l.title_id AS func_6,MAX( l.version )  AS func_7 FROM `laws` AS l",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT temp1.func_6 + MAX( temp1.func_7 )  AS func FROM temp1",
								null)));
	}
	
	
	@Test
	public void testComplexMultiTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select MIN(version), MAX(version), avg(version), MIN(version) + MAX(version) from laws l",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(ExecutionType.SELECT,
							group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME,new String[] { }, new String[][] { },
						  "SELECT MIN( l.`version` )  AS func_7,MAX( l.`version` )  AS func_8,SUM( l.`version` )  AS func_9,COUNT( l.`version` )  AS func_10,MIN( l.`version` )  AS func_11,MAX( l.`version` )  AS func_12",
						  "FROM `laws` AS l"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							null,
						  "SELECT MIN( temp1.func_7 )  AS func,MAX( temp1.func_8 )  AS func_14, (SUM( temp1.func_9 )  / SUM( temp1.func_10 ) )  AS func_15,MIN( temp1.func_11 )  + MAX( temp1.func_12 )  AS func_16",
						  "FROM temp1"
						)
					)
				);
	}

	
	@Test
	public void testRedistA() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select count(*) from (select 1 as expression from laws where version > 1) subquery",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT COUNT( * )  AS func_5 FROM `laws` WHERE `laws`.`version` > 1",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT CONVERT( SUM( temp1.func_5 ) ,SIGNED )  AS func FROM temp1",
								null)));
	}
	
	@Test
	public void testPE396B() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select avg(version) + state_id, law from laws l group by law",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
						"SELECT SUM( l.`version` )  AS func_8,COUNT( l.`version` )  AS func_9,l.`state_id` AS ls1_10,l.`law` AS ll5_11 FROM `laws` AS l GROUP BY ll5_11 ASC",
							group,"temp1",TransientExecutionEngine.LARGE,StaticDistributionModel.MODEL_NAME,new String[] {"ll5_11" }),
						new ProjectingExpectedStep(
						"SELECT  (SUM( temp1.func_8 )  / SUM( temp1.func_9 ) )  + temp1.ls1_10 AS func,temp1.ll5_11 AS t2l3_10 FROM temp1 GROUP BY t2l3_10 ASC",
							null)
					));
	}

	@Test
	public void testPE396C() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		stmtTest(db,
				"select avg(version) + state_id, law from laws l group by id",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT  (SUM( l.`version` )  / COUNT( l.`version` ) )  + l.`state_id` AS func_11,l.`law` AS ll5_12 FROM `laws` AS l GROUP BY l.`id` ASC",
								null)));
	}

	// take the new plan here too
	@Test
	public void testAggTestNonGrandAggFunsE() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select count(pb) + 15, sid, pa from R group by pa",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(ExecutionType.SELECT,
							group,"temp1",TransientExecutionEngine.LARGE,StaticDistributionModel.MODEL_NAME,new String[] {"R1p2_11" }, new String[][] {{"R1p2_11"} },
						  "SELECT COUNT( `R`.`pb` )  AS func_9,`R`.`sid` AS R1s1_10,`R`.`pa` AS R1p2_11",
						  "FROM `R`",
						  "GROUP BY R1p2_11 ASC"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							TransientExecutionEngine.LARGE,"temp2",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME,new String[] { }, new String[][] { },
						  "SELECT CONVERT( SUM( temp1.func_9 ) ,SIGNED )  + 15 AS func,temp1.R1s1_10 AS t2R1_8,temp1.R1p2_11 AS t2R2_9",
						  "FROM temp1",
						  "GROUP BY t2R2_9 ASC"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							null,
						  "SELECT temp2.func AS t3f0,temp2.t2R1_8 AS t3t1,temp2.t2R2_9 AS t3t2",
						  "FROM temp2",
						  "ORDER BY t3t2 ASC"
						)
					)
					);
	}

	@Test
	public void testAggTestDistinctGrandAggA() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select count(distinct id) from A",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(ExecutionType.SELECT,
							group,"temp1",TransientExecutionEngine.LARGE,StaticDistributionModel.MODEL_NAME,new String[] {"A1i0_4" }, new String[][] {{"A1i0_4"} },
						  "SELECT DISTINCT `A`.`id` AS A1i0_4",
						  "FROM `A`"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							TransientExecutionEngine.LARGE,"temp2",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME,new String[] { }, new String[][] { },
						  "SELECT COUNT( DISTINCT temp1.A1i0_4 )  AS func",
						  "FROM temp1"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							null,
						  "SELECT CONVERT( SUM( temp2.func ) ,SIGNED )  AS func_3",
						  "FROM temp2"
						)
					)
					);
	}

	@Test
	public void testAggTestGrandAggA() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select count(*) from A",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT COUNT( * )  AS func_4 FROM `A`",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME,new String[] {}),
						new ProjectingExpectedStep(
								"SELECT CONVERT( SUM( temp1.func_4 ) ,SIGNED )  AS func FROM temp1",
								null)));
	}

	@Test
	public void testAggTestEntityApi45355() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select distinct s.pa, a.pb from S s inner join A a on s.sid = a.sid where a.pb > 5 and s.pb > 5",
				SelectStatement.class,
				bes(
						bpes(
							bes(
								new ProjectingExpectedStep(
								"SELECT s.`pa` AS sp2_3,s.`sid` AS ss1_4 FROM `S` AS s WHERE s.`pb` > 5",
									group,"temp1",TransientExecutionEngine.MEDIUM,StaticDistributionModel.MODEL_NAME,new String[] {"ss1_4" })
							),
							bes(
								new ProjectingExpectedStep(
								"SELECT a.`pb` AS ap3_3,a.`sid` AS as1_4 FROM `A` AS a WHERE a.`pb` > 5",
									group,"temp2",TransientExecutionEngine.MEDIUM,StaticDistributionModel.MODEL_NAME,new String[] {"as1_4" })
							)
						),
						new ProjectingExpectedStep(
						"SELECT temp1.sp2_3 AS t3s0_9,temp2.ap3_3 AS t4a0_10 FROM temp1 INNER JOIN temp2 ON temp1.ss1_4 = temp2.as1_4",
							TransientExecutionEngine.MEDIUM,"temp3",TransientExecutionEngine.MEDIUM,StaticDistributionModel.MODEL_NAME,new String[] {"t3s0_9","t4a0_10" }),
						new ProjectingExpectedStep(
						"SELECT DISTINCT temp3.t3s0_9 AS t5t0,temp3.t4a0_10 AS t5t1 FROM temp3 GROUP BY t5t0 ASC, t5t1 ASC",
							null)
					));
	}
	
	@Test
	public void testHavingA() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select MAX(pa), sid from A a group by sid having avg(pb) > 4",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(ExecutionType.SELECT,
							group,"temp1",TransientExecutionEngine.LARGE,StaticDistributionModel.MODEL_NAME,new String[] {"as1_11" }, new String[][] {{"as1_11"} },
						  "SELECT MAX( a.`pa` )  AS func_10,a.`sid` AS as1_11,SUM( a.`pb` )  AS func_12,COUNT( a.`pb` )  AS func_13",
						  "FROM `A` AS a",
						  "GROUP BY as1_11 ASC"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							null,
						  "SELECT MAX( temp1.func_10 )  AS func,temp1.as1_11 AS t2a1_10",
						  "FROM temp1",
						  "GROUP BY t2a1_10 ASC",
						  "HAVING  (SUM( temp1.func_12 )  / SUM( temp1.func_13 ) )  > 4"
						)
					)
					);
	}
	
	@Test
	public void testHavingB() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);		
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select MAX(pa), sid, avg(pb) hc from A a group by sid having hc > 6",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(ExecutionType.SELECT,
							group,"temp1",TransientExecutionEngine.LARGE,StaticDistributionModel.MODEL_NAME,new String[] {"as1_12" }, new String[][] {{"as1_12"} },
						  "SELECT MAX( a.`pa` )  AS func_11,a.`sid` AS as1_12,SUM( a.`pb` )  AS func_13,COUNT( a.`pb` )  AS func_14",
						  "FROM `A` AS a",
						  "GROUP BY as1_12 ASC"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							null,
						  "SELECT MAX( temp1.func_11 )  AS func,temp1.as1_12 AS t2a1_10, (SUM( temp1.func_13 )  / SUM( temp1.func_14 ) )  AS func_12",
						  "FROM temp1",
						  "GROUP BY t2a1_10 ASC",
						  "HAVING func_12 > 6"
						)
					)
					);
	}

	@Test
	public void testHavingC() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);		
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"Select pa, pb, sid from A a group by sid having pa < 0",<commit_msg>Fixing unit tests.<commit_after>package com.tesora.dve.sql.transform;

/*
 * #%L
 * Tesora Inc.
 * Database Virtualization Engine
 * %%
 * Copyright (C) 2011 - 2014 Tesora Inc.
 * %%
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU Affero General Public License, version 3,
 * as published by the Free Software Foundation.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License
 * along with this program. If not, see <http://www.gnu.org/licenses/>.
 * #L%
 */

import org.junit.Assert;
import org.junit.Test;

import com.tesora.dve.distribution.BroadcastDistributionModel;
import com.tesora.dve.distribution.StaticDistributionModel;
import com.tesora.dve.sql.SchemaTest;
import com.tesora.dve.sql.schema.PEPersistentGroup;
import com.tesora.dve.sql.schema.SchemaContext;
import com.tesora.dve.sql.statement.dml.SelectStatement;
import com.tesora.dve.sql.transexec.TransientExecutionEngine;
import com.tesora.dve.sql.transform.execution.DMLExplainReason;
import com.tesora.dve.sql.transform.execution.DMLExplainRecord;
import com.tesora.dve.sql.transform.execution.ExecutionType;
import com.tesora.dve.sql.util.TestName;

public class AggregationTransformTest extends TransformTest {

	public AggregationTransformTest() {
		super("AggregationTransformTest");
	}
	
	private static final String[] leftySchema = new String[] {
		"create table `titles` (`id` int unsigned not null, `name` varchar(50) not null) static distribute on (`id`)",
		"create table `states` (`id` int unsigned not null, `name` varchar(50) not null, `tag` varchar(50)) static distribute on (`id`)",
		"create table `laws` (`id` int unsigned not null, `state_id` int unsigned not null, `title_id` int unsigned not null, " 
			+ "`status` varchar(16) not null default 'unpublished', `version` int unsigned not null, `law` varchar(100)) "
			+ "static distribute on (`id`)",
		"create table `counties` (`id` int unsigned not null, `name` varchar(50) not null, `state_id` int unsigned not null) " +
			"static distribute on (`id`)",
		"create table `courts` (`id` int unsigned not null, `county_id` int unsigned not null, `address` varchar(50) not null) " +
			"static distribute on (`id`)"
	};
	
	private static final String[] aggTestSchema = new String[] {
		"create table `B` ( `id` int, `sid` int, `pa` int(10), `pb` int(10), primary key (`id`) ) broadcast distribute",
		"create table `S` ( `id` int, `sid` int, `pa` int(10), `pb` int(10), primary key (`id`) ) static distribute on (`id`)",
		"create table `A` ( `id` int, `sid` int, `pa` int(10), `pb` int(10), primary key (`id`) ) random distribute",
		"create table `R` ( `id` int, `sid` int, `pa` int(10), `pb` int(10), primary key (`id`) ) range distribute on (`id`) using openrange",
	};
	
	@Test
	public void simpleMultiMaxTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select MAX(version) from laws",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT MAX( `laws`.`version` )  AS func_4 FROM `laws`",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT MAX( temp1.func_4 )  AS func FROM temp1",
								null)));
	}
	
	
	@Test
	public void simpleMultiMinTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select MIN ( version ) from laws",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT MIN( `laws`.`version` )  AS func_4 FROM `laws`",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT MIN( temp1.func_4 )  AS func FROM temp1",
								null)));
		
	}
	
	
	@Test
	public void simpleMultiCountTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select count(*) from laws",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT COUNT( * )  AS func_4 FROM `laws`",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT CONVERT( SUM( temp1.func_4 ) ,SIGNED )  AS func FROM temp1",
								null)));
		
	}

	
	@Test
	public void simpleMultiSumTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select SUM(version) from laws",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT SUM( `laws`.`version` )  AS func_4 FROM `laws`",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT SUM( temp1.func_4 )  AS func FROM temp1",
								null)));
		
	}

	
	@Test
	public void simpleMultiAvgTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select avg(version) from laws",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT SUM( `laws`.`version` )  AS func_4,COUNT( `laws`.`version` )  AS func_5 FROM `laws`",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT  (SUM( temp1.func_4 )  / SUM( temp1.func_5 ) )  AS func FROM temp1",
								null)
				));
	}

	
	@Test
	public void simpleSingleSumTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		stmtTest(db,
				"select SUM(version) from laws where id = 15",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT SUM( `laws`.`version` )  AS func_5 FROM `laws` WHERE `laws`.`id` = 15",
								null )));
	}

	
	@Test
	public void simpleSingleCountTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		stmtTest(db,
				"select count(*) from laws where id = 15",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT COUNT( * )  AS func_5 FROM `laws` WHERE `laws`.`id` = 15",
								null )));
	}

	
	@Test
	public void simpleSingleMaxTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		stmtTest(db,
				"select MAX(version) from laws where id = 15",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT MAX( `laws`.`version` )  AS func_5 FROM `laws` WHERE `laws`.`id` = 15",
								null )));
	}

	
	
	@Test
	public void simpleSingleAvgTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		stmtTest(db,
				"select avg(version) from laws where id = 15",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT  (SUM( `laws`.`version` )  / COUNT( `laws`.`version` ) )  AS func_5 FROM `laws` WHERE `laws`.`id` = 15",
								null)));
	}
	
	
	@Test 
	public void testPE396A() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select l.state_id + l.title_id + MAX(l.version) from laws l",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT l.`state_id` + l.`title_id` AS func_5,MAX( l.`version` )  AS func_6 FROM `laws` AS l",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT temp1.func_5 + MAX( temp1.func_6 )  AS func FROM temp1",
								null)));
	}
	
	
	@Test
	public void testComplexMultiTest() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select MIN(version), MAX(version), avg(version), MIN(version) + MAX(version) from laws l",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(ExecutionType.SELECT,
							group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME,new String[] { }, new String[][] { },
						  "SELECT MIN( l.`version` )  AS func_7,MAX( l.`version` )  AS func_8,SUM( l.`version` )  AS func_9,COUNT( l.`version` )  AS func_10,MIN( l.`version` )  AS func_11,MAX( l.`version` )  AS func_12",
						  "FROM `laws` AS l"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							null,
						  "SELECT MIN( temp1.func_7 )  AS func,MAX( temp1.func_8 )  AS func_14, (SUM( temp1.func_9 )  / SUM( temp1.func_10 ) )  AS func_15,MIN( temp1.func_11 )  + MAX( temp1.func_12 )  AS func_16",
						  "FROM temp1"
						)
					)
				);
	}

	
	@Test
	public void testRedistA() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select count(*) from (select 1 as expression from laws where version > 1) subquery",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT COUNT( * )  AS func_5 FROM `laws` WHERE `laws`.`version` > 1",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME, new String[] {}),
						new ProjectingExpectedStep(
								"SELECT CONVERT( SUM( temp1.func_5 ) ,SIGNED )  AS func FROM temp1",
								null)));
	}
	
	@Test
	public void testPE396B() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select avg(version) + state_id, law from laws l group by law",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
						"SELECT SUM( l.`version` )  AS func_6,COUNT( l.`version` )  AS func_7,l.`state_id` AS ls1_8,l.`law` AS ll5_9 FROM `laws` AS l GROUP BY ll5_9 ASC",
							group,"temp1",TransientExecutionEngine.LARGE,StaticDistributionModel.MODEL_NAME,new String[] {"ll5_9" }),
						new ProjectingExpectedStep(
						"SELECT  (SUM( temp1.func_6 )  / SUM( temp1.func_7 ) )  + temp1.ls1_8 AS func,temp1.ll5_9 AS t2l3_10 FROM temp1 GROUP BY t2l3_10 ASC",
							null)
					));
	}

	@Test
	public void testPE396C() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,leftySchema);
		stmtTest(db,
				"select avg(version) + state_id, law from laws l group by id",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT  (SUM( l.`version` )  / COUNT( l.`version` ) )  + l.`state_id` AS func_9,l.`law` AS ll5_10 FROM `laws` AS l GROUP BY l.`id` ASC",
								null)));
	}

	// take the new plan here too
	@Test
	public void testAggTestNonGrandAggFunsE() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select count(pb) + 15, sid, pa from R group by pa",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(ExecutionType.SELECT,
							group,"temp1",TransientExecutionEngine.LARGE,StaticDistributionModel.MODEL_NAME,new String[] {"R1p2_11" }, new String[][] {{"R1p2_11"} },
						  "SELECT COUNT( `R`.`pb` )  AS func_9,`R`.`sid` AS R1s1_10,`R`.`pa` AS R1p2_11",
						  "FROM `R`",
						  "GROUP BY R1p2_11 ASC"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							TransientExecutionEngine.LARGE,"temp2",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME,new String[] { }, new String[][] { },
						  "SELECT CONVERT( SUM( temp1.func_9 ) ,SIGNED )  + 15 AS func,temp1.R1s1_10 AS t2R1_8,temp1.R1p2_11 AS t2R2_9",
						  "FROM temp1",
						  "GROUP BY t2R2_9 ASC"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							null,
						  "SELECT temp2.func AS t3f0,temp2.t2R1_8 AS t3t1,temp2.t2R2_9 AS t3t2",
						  "FROM temp2",
						  "ORDER BY t3t2 ASC"
						)
					)
					);
	}

	@Test
	public void testAggTestDistinctGrandAggA() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select count(distinct id) from A",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(ExecutionType.SELECT,
							group,"temp1",TransientExecutionEngine.LARGE,StaticDistributionModel.MODEL_NAME,new String[] {"A1i0_4" }, new String[][] {{"A1i0_4"} },
						  "SELECT DISTINCT `A`.`id` AS A1i0_4",
						  "FROM `A`"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							TransientExecutionEngine.LARGE,"temp2",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME,new String[] { }, new String[][] { },
						  "SELECT COUNT( DISTINCT temp1.A1i0_4 )  AS func",
						  "FROM temp1"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							null,
						  "SELECT CONVERT( SUM( temp2.func ) ,SIGNED )  AS func_3",
						  "FROM temp2"
						)
					)
					);
	}

	@Test
	public void testAggTestGrandAggA() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select count(*) from A",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(
								"SELECT COUNT( * )  AS func_4 FROM `A`",
								group,"temp1",TransientExecutionEngine.AGGREGATION,StaticDistributionModel.MODEL_NAME,new String[] {}),
						new ProjectingExpectedStep(
								"SELECT CONVERT( SUM( temp1.func_4 ) ,SIGNED )  AS func FROM temp1",
								null)));
	}

	@Test
	public void testAggTestEntityApi45355() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select distinct s.pa, a.pb from S s inner join A a on s.sid = a.sid where a.pb > 5 and s.pb > 5",
				SelectStatement.class,
				bes(
						bpes(
							bes(
								new ProjectingExpectedStep(
								"SELECT s.`pa` AS sp2_3,s.`sid` AS ss1_4 FROM `S` AS s WHERE s.`pb` > 5",
									group,"temp1",TransientExecutionEngine.MEDIUM,StaticDistributionModel.MODEL_NAME,new String[] {"ss1_4" })
							),
							bes(
								new ProjectingExpectedStep(
								"SELECT a.`pb` AS ap3_3,a.`sid` AS as1_4 FROM `A` AS a WHERE a.`pb` > 5",
									group,"temp2",TransientExecutionEngine.MEDIUM,StaticDistributionModel.MODEL_NAME,new String[] {"as1_4" })
							)
						),
						new ProjectingExpectedStep(
						"SELECT temp1.sp2_3 AS t3s0_9,temp2.ap3_3 AS t4a0_10 FROM temp1 INNER JOIN temp2 ON temp1.ss1_4 = temp2.as1_4",
							TransientExecutionEngine.MEDIUM,"temp3",TransientExecutionEngine.MEDIUM,StaticDistributionModel.MODEL_NAME,new String[] {"t3s0_9","t4a0_10" }),
						new ProjectingExpectedStep(
						"SELECT DISTINCT temp3.t3s0_9 AS t5t0,temp3.t4a0_10 AS t5t1 FROM temp3 GROUP BY t5t0 ASC, t5t1 ASC",
							null)
					));
	}
	
	@Test
	public void testHavingA() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select MAX(pa), sid from A a group by sid having avg(pb) > 4",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(ExecutionType.SELECT,
							group,"temp1",TransientExecutionEngine.LARGE,StaticDistributionModel.MODEL_NAME,new String[] {"as1_11" }, new String[][] {{"as1_11"} },
						  "SELECT MAX( a.`pa` )  AS func_10,a.`sid` AS as1_11,SUM( a.`pb` )  AS func_12,COUNT( a.`pb` )  AS func_13",
						  "FROM `A` AS a",
						  "GROUP BY as1_11 ASC"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							null,
						  "SELECT MAX( temp1.func_10 )  AS func,temp1.as1_11 AS t2a1_10",
						  "FROM temp1",
						  "GROUP BY t2a1_10 ASC",
						  "HAVING  (SUM( temp1.func_12 )  / SUM( temp1.func_13 ) )  > 4"
						)
					)
					);
	}
	
	@Test
	public void testHavingB() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);		
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"select MAX(pa), sid, avg(pb) hc from A a group by sid having hc > 6",
				SelectStatement.class,
				bes(
						new ProjectingExpectedStep(ExecutionType.SELECT,
							group,"temp1",TransientExecutionEngine.LARGE,StaticDistributionModel.MODEL_NAME,new String[] {"as1_11" }, new String[][] {{"as1_11"} },
						  "SELECT MAX( a.`pa` )  AS func_10,a.`sid` AS as1_11,SUM( a.`pb` )  AS func_12,COUNT( a.`pb` )  AS func_13",
						  "FROM `A` AS a",
						  "GROUP BY as1_11 ASC"
						),
						new ProjectingExpectedStep(ExecutionType.SELECT,
							null,
						  "SELECT MAX( temp1.func_10 )  AS func,temp1.as1_11 AS t2a1_10, (SUM( temp1.func_12 )  / SUM( temp1.func_13 ) )  AS func_11",
						  "FROM temp1",
						  "GROUP BY t2a1_10 ASC",
						  "HAVING func_11 > 6"
						)
					)
					);
	}

	@Test
	public void testHavingC() throws Throwable {
		SchemaContext db = buildSchema(TestName.MULTI,aggTestSchema);		
		PEPersistentGroup group = db.getCurrentDatabase().getDefaultStorage(db);
		stmtTest(db,
				"Select pa, pb, sid from A a group by sid having pa < 0",
{'fraction_non_alphanumeric': 0.12118097989112321, 'fraction_numerical': 0.02413620708810132, 'mean_word_length': 3.625513874614594, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 25, 'https://': 0, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '684142', 'n_tokens_mistral': 14903, 'n_tokens_neox': 11825, 'n_words': 2931}