context: ///////////////////////////////////////////////////////////////////////////////////////
  // Copyright (C) 2006-2015 Esper Team. All rights reserved.                           /
  // http://esper.codehaus.org                                                          /
  // ---------------------------------------------------------------------------------- /
  // The software in this package is published under the terms of the GPL license       /
  // a copy of which has been included with this distribution in the license.txt file.  /
  ///////////////////////////////////////////////////////////////////////////////////////
  
  using System;
  using System.Collections.Generic;
  
  using com.espertech.esper.common.client.fireandforget;
  using com.espertech.esper.common.client.scopetest;
  using com.espertech.esper.common.@internal.support;
  using com.espertech.esper.compat;
  using com.espertech.esper.compat.collections;
  using com.espertech.esper.regressionlib.framework;
  using com.espertech.esper.regressionlib.support.util;
  
  using NUnit.Framework;
  
  using static com.espertech.esper.regressionlib.framework.SupportMessageAssertUtil;
  
  namespace com.espertech.esper.regressionlib.suite.infra.nwtable
  {
  	public class InfraNWTableFAFSubquery : IndexBackingTableInfo
  	{
  
  		public static ICollection<RegressionExecution> Executions()
  		{
  			var execs = new List<RegressionExecution>();
  			WithSimple(execs);
  			WithSimpleJoin(execs);
  			WithInsert(execs);
  			WithUpdateUncorrelated(execs);
  			WithDeleteUncorrelated(execs);
  			WithSelectCorrelated(execs);
  			WithUpdateCorrelatedSet(execs);
  			WithUpdateCorrelatedWhere(execs);
  			WithDeleteCorrelatedWhere(execs);
  			WithContextBothWindows(execs);
  			WithContextSelect(execs);
  			WithSelectWhere(execs);
  			WithSelectGroupBy(execs);
  			WithSelectIndexPerfWSubstitution(execs);
  			WithSelectIndexPerfCorrelated(execs);
  			WithInvalid(execs);
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithInvalid(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubqueryInvalid());
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithSelectIndexPerfCorrelated(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubquerySelectIndexPerfCorrelated(true));
  			execs.Add(new InfraFAFSubquerySelectIndexPerfCorrelated(false));
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithSelectIndexPerfWSubstitution(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubquerySelectIndexPerfWSubstitution(true));
  			execs.Add(new InfraFAFSubquerySelectIndexPerfWSubstitution(false));
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithSelectGroupBy(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubquerySelectGroupBy());
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithSelectWhere(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubquerySelectWhere());
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithContextSelect(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubqueryContextSelect());
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithContextBothWindows(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubqueryContextBothWindows());
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithDeleteCorrelatedWhere(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubqueryDeleteCorrelatedWhere());
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithUpdateCorrelatedWhere(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubqueryUpdateCorrelatedWhere());
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithUpdateCorrelatedSet(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubqueryUpdateCorrelatedSet());
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithSelectCorrelated(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubquerySelectCorrelated());
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithDeleteUncorrelated(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubqueryDeleteUncorrelated());
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithUpdateUncorrelated(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubqueryUpdateUncorrelated());
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithInsert(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubqueryInsert(true));
  			execs.Add(new InfraFAFSubqueryInsert(false));
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithSimpleJoin(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubquerySimpleJoin());
  			return execs;
  		}
  
  		public static IList<RegressionExecution> WithSimple(IList<RegressionExecution> execs = null)
  		{
  			execs = execs ?? new List<RegressionExecution>();
  			execs.Add(new InfraFAFSubquerySimple(true));
  			execs.Add(new InfraFAFSubquerySimple(false));
  			return execs;
  		}
  
  		public class InfraFAFSubqueryInvalid : RegressionExecution
  		{
  			public void Run(RegressionEnvironment env)
  			{
  				var path = new RegressionPath();
  				var epl = "@public create window WinSB#keepall as SupportBean;\n" +
  				          "create context MyContext partition by Id from SupportBean_S0;\n" +
  				          "context MyContext create window PartitionedWinS0#keepall as SupportBean_S0;\n";
  				env.Compile(epl, path);
  
  				TryInvalidFAFCompile(
  					env,
  					path,
  					"select (select * from SupportBean#lastevent) from WinSB",
  					"Fire-and-forget queries only allow subqueries against named windows and tables");
  
  				TryInvalidFAFCompile(
  					env,
  					path,
  					"select (select * from WinSB(TheString='x')) from WinSB",
  					"Failed to plan subquery number 1 querying WinSB: Subqueries in fire-and-forget queries do not allow filter expressions");
  
  				TryInvalidFAFCompile(
  					env,
  					path,
  					"select (select * from PartitionedWinS0) from WinSB",
  					"Failed to plan subquery number 1 querying PartitionedWinS0: Mismatch in context specification, the context for the named window 'PartitionedWinS0' is 'MyContext' and the query specifies no context");
  			}
  		}
  
  		private class InfraFAFSubquerySelectIndexPerfCorrelated : RegressionExecution
  		{
  			private bool namedWindow;
  
  			public InfraFAFSubquerySelectIndexPerfCorrelated(bool namedWindow)
  			{
  				this.namedWindow = namedWindow;
  			}
  
  			public void Run(RegressionEnvironment env)
  			{
  				var path = new RegressionPath();
  				var epl =
  					"@public create window WinSB#keepall as SupportBean;\n" +
  					"insert into WinSB select * from SupportBean;\n";
  				if (namedWindow) {
  					epl += "@public create window Infra#unique(Id) as (Id int, Value string);\n";
  				}
  				else {
  					epl += "@public create table Infra(Id int primary key, Value string);\n";
  				}
  
  				epl += "@public create index InfraIndex on Infra(Value);\n" +
  				       "insert into Infra select Id, P00 as Value from SupportBean_S0;\n";
  				env.CompileDeploy(epl, path);
  
  				var numRows = 10000; // less than 1M
  				for (var i = 0; i < numRows; i++) {
  					SendSB(env, "v" + i, 0);
  					SendS0(env, -1 * i, "v" + i);
  				}
  
  				EPFireAndForgetQueryResult result = null;
  
  				var delta = PerformanceObserver.TimeMillis(
  					() => {
  						var query = "select (select Id from Infra as i where i.Value = wsb.TheString) as c0 from WinSB as wsb";
  						result = CompileExecute(env, path, query);
  					});
  
  				Assert.That(delta, Is.LessThan(1000), "delta is " + delta);
  				Assert.AreEqual(numRows, result.Array.Length);
  				for (var i = 0; i < numRows; i++) {
  					Assert.AreEqual(-1 * i, result.Array[i].Get("c0"));
  				}
  
  				env.UndeployAll();
  			}
  		}
  
  		private class InfraFAFSubquerySelectIndexPerfWSubstitution : RegressionExecution
  		{
  			private bool namedWindow;
  
  			public InfraFAFSubquerySelectIndexPerfWSubstitution(bool namedWindow)
  			{
  				this.namedWindow = namedWindow;
  			}
  
  			public void Run(RegressionEnvironment env)
  			{
  				var path = new RegressionPath();
  				var epl =
  					"@public create window WinSB#lastevent as SupportBean;\n" +
  					"insert into WinSB select * from SupportBean;\n";
  				if (namedWindow) {
  					epl += "@public create window Infra#unique(Id) as (Id int, Value string);\n";
  				}
  				else {
  					epl += "@public create table Infra(Id int primary key, Value string);\n";
  				}
  
  				epl += "@public create index InfraIndex on Infra(Value);\n" +
  				       "insert into Infra select Id, P00 as Value from SupportBean_S0;\n";
  				env.CompileDeploy(epl, path);
  
  				SendSB(env, "E1", -1);
  				for (var i = 0; i < 10000; i++) {
  					SendS0(env, i, "v" + i);
  				}
  
  				var query = "select (select Id from Infra as i where i.Value = ?:p0:string) as c0 from WinSB";
  				var compiled = env.CompileFAF(query, path);
  				var prepared = env.Runtime.FireAndForgetService.PrepareQueryWithParameters(compiled);
  
  				var delta = PerformanceObserver.TimeMillis(
  					() => {
  						for (var i = 5000; i < 6000; i++) {
  							prepared.SetObject("p0", "v" + i);
  							var result = env.Runtime.FireAndForgetService.ExecuteQuery(prepared);
  							Assert.AreEqual(1, result.Array.Length);
  							Assert.AreEqual(i, result.Array[0].Get("c0"));
  						}
  					});
  
  				Assert.That(delta, Is.LessThan(1000), "delta is " + delta);
  
  				env.UndeployAll();
  			}
  		}
  
  		private class InfraFAFSubquerySelectWhere : RegressionExecution
  		{
  			public void Run(RegressionEnvironment env)
  			{
  				var path = new RegressionPath();
  				var epl =
  					"@public create window WinS0#keepall as SupportBean_S0;\n" +
  					"@public create window WinSB#keepall as SupportBean;\n" +
  					"insert into WinS0 select * from SupportBean_S0;\n" +
  					"insert into WinSB select * from SupportBean;\n";
  				env.CompileDeploy(epl, path);
  
  				var query = "select (select IntPrimitive from WinSB where TheString = 'x') as c0 from WinS0";
  				SendS0(env, 0, null);
  				AssertQuerySingle(env, path, query, null);
  
  				SendSB(env, "E1", 1);
  				AssertQuerySingle(env, path, query, null);
  
  				SendSB(env, "x", 2);
  				AssertQuerySingle(env, path, query, 2);
  
  				SendSB(env, "x", 3);
  				AssertQuerySingle(env, path, query, null);
  
  				env.UndeployAll();
  			}
  		}
  
  		private class InfraFAFSubquerySelectGroupBy : RegressionExecution
  		{
  			public void Run(RegressionEnvironment env)
  			{
  				var path = new RegressionPath();
  				var epl =
  					"@public create window WinS0#keepall as SupportBean_S0;\n" +
  					"@public create window WinSB#keepall as SupportBean;\n" +
  					"insert into WinS0 select * from SupportBean_S0;\n" +
  					"insert into WinSB select * from SupportBean;\n";
  				env.CompileDeploy(epl, path);
  
  				var query = "select (select TheString, sum(IntPrimitive) as thesum from WinSB group by TheString) as c0 from WinS0";
  				SendS0(env, 0, null);
  
  				SendSB(env, "E1", 10);
  				SendSB(env, "E1", 11);
  				var result = (IDictionary<string, object>) RunQuerySingle(env, path, query);
  				Assert.AreEqual("E1", result.Get("TheString"));
  				Assert.AreEqual(21, result.Get("thesum"));
  
  				env.UndeployAll();
  			}
  		}
  
  		private class InfraFAFSubqueryContextSelect : RegressionExecution
  		{
  			public void Run(RegressionEnvironment env)
  			{
  				var path = new RegressionPath();
  				var epl =
  					"create context MyContext partition by Id from SupportBean_S0;\n" +
  					"@public context MyContext create window WinS0#keepall as SupportBean_S0;\n" +
  					"context MyContext on SupportBean_S0 as s0 merge WinS0 insert select *;\n" +
  					"@public create window WinSB#lastevent as SupportBean;\n" +
  					"insert into WinSB select * from SupportBean;\n";
  				env.CompileDeploy(epl, path);
  
  				SendS0(env, 1, "a");
  				SendS0(env, 2, "b");
  				SendSB(env, "E1", 1);
  
  				var query = "context MyContext select P00, (select TheString from WinSB) as TheString from WinS0";
  				AssertQueryMultirowAnyOrder(
  					env,
  					path,
  					query,
  					"P00,TheString",
  					new object[][] {
  						new object[] {"a", "E1"},
  						new object[] {"b", "E1"}
  					});
  
  				env.UndeployAll();
  			}
  		}
  
  		private class InfraFAFSubqueryContextBothWindows : RegressionExecution
  		{
  			public void Run(RegressionEnvironment env)
  			{
  				var path = new RegressionPath();
  				var epl =
  					"create context MyContext partition by Id from SupportBean_S0, Id from SupportBean_S1;\n" +
  					"@public context MyContext create window WinS0#keepall as SupportBean_S0;\n" +
  					"@public context MyContext create window WinS1#keepall as SupportBean_S1;\n" +
  					"context MyContext on SupportBean_S0 as s0 merge WinS0 insert select *;\n" +
  					"context MyContext on SupportBean_S1 as s1 merge WinS1 insert select *;\n";
  				env.CompileDeploy(epl, path);
  
  				SendS0(env, 1, "a");
  				SendS0(env, 2, "b");
  				SendS0(env, 3, "c");
  				SendS1(env, 1, "X");
  				SendS1(env, 2, "Y");
  				SendS1(env, 3, "Z");
  
  				var query = "context MyContext select P00, (select P10 from WinS1) as P10 from WinS0";
  				AssertQueryMultirowAnyOrder(
  					env,
  					path,
  					query,
  					"P00,P10",
  					new object[][] {
  						new object[] {"a", "X"},
  						new object[] {"b", "Y"},
  						new object[] {"c", "Z"}
  					});
  
  				env.UndeployAll();
  			}
  		}
  
  		private class InfraFAFSubqueryDeleteCorrelatedWhere : RegressionExecution
  		{
  			public void Run(RegressionEnvironment env)
  			{
  				var path = new RegressionPath();
  				var epl =
  					"@public create window WinS0#keepall as SupportBean_S0;\n" +
  					"@public create window WinSB#unique(IntPrimitive) as SupportBean;\n" +
  					"insert into WinS0 select * from SupportBean_S0;\n" +
  					"insert into WinSB select * from SupportBean;\n";
  				env.CompileDeploy(epl, path);
  
  				SendS0(env, 1, "a");
  				SendS0(env, 2, "b");
  				SendS0(env, 3, "c");
  
  				SendSB(env, "a", 0);
  				SendSB(env, "b", 2);
  
  				var update = "delete from WinS0 as wins0 where Id = (select IntPrimitive from WinSB winsb where winsb.TheString = wins0.P00)";
  				CompileExecute(env, path, update);
  
  				var query = "select * from WinS0";
  				AssertQueryMultirowAnyOrder(
  					env,
  					path,
  					query,
  					"Id,P00",
  					new object[][] {
  						new object[] {1, "a"},
  						new object[] {3, "c"}
  					});
  
  				env.UndeployAll();
  			}
  		}
  
  		private class InfraFAFSubqueryUpdateCorrelatedWhere : RegressionExecution
  		{
  			public void Run(RegressionEnvironment env)
  			{
  				var path = new RegressionPath();
  				var epl =
  					"@public create window WinS0#keepall as SupportBean_S0;\n" +
  					"@public create window WinSB#unique(IntPrimitive) as SupportBean;\n" +
  					"insert into WinS0 select * from SupportBean_S0;\n" +
  					"insert into WinSB select * from SupportBean;\n";
  				env.CompileDeploy(epl, path);
  
  				SendS0(env, 1, "a");
  				SendS0(env, 2, "b");
  				SendS0(env, 3, "c");
  
  				SendSB(env, "a", 0);
  				SendSB(env, "b", 2);
  
  				var update = "update WinS0 as wins0 set P00 = 'x' where Id = (select IntPrimitive from WinSB winsb where winsb.TheString = wins0.P00)";
  				CompileExecute(env, path, update);
  
  				var query = "select * from WinS0";
  				AssertQueryMultirowAnyOrder(
  					env,
  					path,
  					query,
  					"Id,P00",
  					new object[][] {
  						new object[] {1, "a"},
  						new object[] {2, "x"},
  						new object[] {3, "c"}
  					});
  
  				env.UndeployAll();
  			}
  		}
  
  		private class InfraFAFSubqueryUpdateCorrelatedSet : RegressionExecution
  		{
  			public void Run(RegressionEnvironment env)
  			{
  				var path = new RegressionPath();
  				var epl =
  					"@public create window WinS0#keepall as SupportBean_S0;\n" +
  					"@public create window WinSB#unique(IntPrimitive) as SupportBean;\n" +
  					"insert into WinS0 select * from SupportBean_S0;\n" +
  					"insert into WinSB select * from SupportBean;\n";
  				env.CompileDeploy(epl, path);
  
  				SendS0(env, 1, "a");
  				SendS0(env, 2, "b");
  				SendS0(env, 3, "c");
  
  				SendSB(env, "X", 2);
  				SendSB(env, "Y", 1);
  				SendSB(env, "Z", 3);
  
  				var update = "update WinS0 as wins0 set P00 = (select TheString from WinSB winsb where winsb.IntPrimitive = wins0.Id)";
  				CompileExecute(env, path, update);
  
  				var query = "select * from WinS0";
  				AssertQueryMultirowAnyOrder(
  					env,
  					path,
  					query,
  					"Id,P00",
  
gt: 					new object[][] {