Esempio n. 1
0
        private SqlSelect CreateSelectForUniqueMatchNoneOrFull(SqlRow row, SqlSelect query)
        {
            /*
             * select exists(select 1
             *      from (original subquery) x
             *      where x.a1=r.a1 and x.a2=r.a2
             *      group by x.a1, x.a2
             *      having count(*)=1
             *      )                    }
             */
            SqlSelect q0 = SqlDml.Select();

            {
                SqlQueryRef originalQuery = SqlDml.QueryRef(query);
                SqlSelect   q1            = SqlDml.Select(originalQuery);
                q1.Columns.Add(1);
                q1.Where = true; //initially true
                {
                    int index = 0;
                    foreach (SqlColumn col in originalQuery.Columns)
                    {
                        q1.Where = q1.Where && col == row[index];
                        q1.GroupBy.Add(col);
                        index++;
                    }
                    q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                }
                q0.Columns.Add(SqlDml.Exists(q1));
            }
            return(q0);
        }
Esempio n. 2
0
        public void ModifyQueryTest()
        {
            using (var session = Domain.OpenSession())
                using (var tx = session.OpenTransaction()) {
                    var builder = session.Services.Get <QueryBuilder>();
                    Assert.That(builder, Is.Not.Null);

                    var query      = Query.All <CheckEntity>();
                    var translated = builder.TranslateQuery(query);
                    Assert.That(translated, Is.Not.Null);
                    Assert.That(translated.Query, Is.Not.Null);
                    Assert.That(translated.ParameterBindings, Is.Not.Null);

                    var select = (SqlSelect)translated.Query;
                    select.Columns.Clear();
                    select.Columns.Add(SqlDml.Count());
                    var compiled = builder.CompileQuery(translated.Query);
                    Assert.That(compiled, Is.Not.Null);

                    var request = builder.CreateRequest(compiled, translated.ParameterBindings);
                    Assert.That(request, Is.Not.Null);

                    var command = builder.CreateCommand(request);
                    Assert.That(command, Is.Not.Null);

                    using (command) {
                        var result = Convert.ToInt32(command.ExecuteScalar());
                        Assert.That(result, Is.EqualTo(1));
                    }

                    tx.Complete();
                }
        }
        public void MultiColumnDynamicFilterTest()
        {
            var dynamicFilterId = new object();
            var tableRef        = SqlDml.TableRef(Catalog.Schemas["Person"].Tables["Contact"]);
            var select          = SqlDml.Select(tableRef);

            select.Columns.Add(SqlDml.Count());
            var filter = SqlDml.DynamicFilter(dynamicFilterId);

            select.Where = filter;
            filter.Expressions.Add(tableRef["FirstName"]);
            filter.Expressions.Add(tableRef["LastName"]);
            var result = sqlConnection.Driver.Compile(select);

            using (var command = sqlConnection.CreateCommand()) {
                var values = new List <string[]> {
                    new[] { "'Gustavo'", "'Achong'" }, new[] { "'Catherine'", "'Abel'" }
                };
                var configuration = new SqlPostCompilerConfiguration {
                    DynamicFilterValues = { { dynamicFilterId, values } }
                };
                command.CommandText = result.GetCommandText(configuration);
                int count = Convert.ToInt32(command.ExecuteScalar());
                Assert.AreEqual(2, count);
            }
        }
Esempio n. 4
0
        public override string Translate(SqlCompilerContext context, SqlUnary node, NodeSection section)
        {
            //substitute UNIQUE predicate with a more complex EXISTS predicate,
            //because UNIQUE is not supported

            if (node.NodeType == SqlNodeType.Unique)
            {
                if (node.Operand is SqlSubQuery origSubselect)
                {
                    var origQuery = SqlDml.QueryRef(origSubselect.Query);
                    var existsOp  = SqlDml.Select(origQuery);
                    existsOp.Columns.Add(1);
                    existsOp.Where = true;
                    foreach (SqlColumn col in origQuery.Columns)
                    {
                        existsOp.Where = existsOp.Where && SqlDml.IsNotNull(col);
                        existsOp.GroupBy.Add(col);
                    }
                    existsOp.Having = SqlDml.Count(SqlDml.Asterisk) > 1;
                    existsOp.Limit  = 1;

                    node.ReplaceWith(SqlDml.Not(SqlDml.Exists(existsOp)));
                }
            }
            return(base.Translate(context, node, section));
        }
 private ISqlCompileUnit GetQuery(Table generatorTable)
 {
   var select = SqlDml.Select();
   select.Columns.Add(SqlDml.Count());
   select.From = SqlDml.TableRef(generatorTable);
   return select;
 }
        public void Test024()
        {
            string nativeSql = @"SELECT YEAR(r.rental_date) as Year, COUNT(*) Rented
                                    FROM rental r
                                    GROUP BY YEAR(r.rental_date)";

            SqlTableRef rental = SqlDml.TableRef(schema.Tables["rental"], "r");

            SqlSelect select = SqlDml.Select(rental);

            select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Year, rental["rental_date"]), "Year");
            select.Columns.Add(SqlDml.Count(), "Rented");

            select.GroupBy.Add(SqlDml.Extract(SqlDateTimePart.Year, rental["rental_date"]));

            Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
        }
        protected override void  TestFixtureSetUp()
        {
            base.TestFixtureSetUp();
            var testSchema = ExtractDefaultSchema();

            EnsureTableNotExists(testSchema, TestTable);
            var table = testSchema.CreateTable(TestTable);

            table.CreateColumn(IdColumn, new SqlValueType(SqlType.Decimal, 10, 0));
            ExecuteNonQuery(SqlDdl.Create(table));
            tableRef = SqlDml.TableRef(table);

            var select = SqlDml.Select(tableRef);

            select.Columns.Add(SqlDml.Count());
            countQuery = Connection.Driver.Compile(select).GetCommandText();
        }
Esempio n. 8
0
        public void Test024()
        {
            string nativeSql = @"SELECT 
                                  strftime('%Y', PaymentDate) as Year,
                                  COUNT(*) Required
                           FROM invoice r
                           GROUP BY strftime('%Y', PaymentDate)";

            SqlTableRef invoice = SqlDml.TableRef(schema.Tables["invoice"], "r");

            SqlSelect select = SqlDml.Select(invoice);

            select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Year, invoice["PaymentDate"]), "Year");
            select.Columns.Add(SqlDml.Count(), "Required");

            select.GroupBy.Add(SqlDml.Extract(SqlDateTimePart.Year, invoice["PaymentDate"]));

            Assert.IsTrue(CompareExecuteDataReader(nativeSql, select));
        }
Esempio n. 9
0
        public void SqlAggregateReplacingTest()
        {
            SqlAggregate a          = SqlDml.Count();
            SqlAggregate aReplacing = SqlDml.Avg(1, true);

            a.ReplaceWith(aReplacing);

            bool passed = false;

            try {
                a.ReplaceWith(1);
            }
            catch {
                passed = true;
            }

            Assert.IsTrue(passed);
            Assert.AreNotEqual(a, aReplacing);
            Assert.AreEqual(a.NodeType, aReplacing.NodeType);
            Assert.AreEqual(a.Distinct, aReplacing.Distinct);
            Assert.AreEqual(a.Expression, aReplacing.Expression);
        }
Esempio n. 10
0
        public void BatchTest()
        {
            var table = testSchema.CreateTable(BatchTestTable);

            CreateIdColumn(table);
            ExecuteNonQuery(SqlDdl.Create(table));
            var tableRef = SqlDml.TableRef(table);

            var singleStatementBatch = SqlDml.Batch();

            singleStatementBatch.Add(CreateInsert(tableRef));
            ExecuteNonQuery(singleStatementBatch);

            var multiStatementBatch = SqlDml.Batch();

            multiStatementBatch.Add(CreateInsert(tableRef));
            multiStatementBatch.Add(CreateInsert(tableRef));
            ExecuteNonQuery(multiStatementBatch);

            var innerEmptyBatch           = SqlDml.Batch();
            var innerSingleStatementBatch = SqlDml.Batch();

            innerSingleStatementBatch.Add(CreateInsert(tableRef));
            var innerMultiStatementBatch = SqlDml.Batch();

            innerMultiStatementBatch.Add(CreateInsert(tableRef));
            var outerBatch = SqlDml.Batch();

            outerBatch.Add(innerEmptyBatch);
            outerBatch.Add(innerSingleStatementBatch);
            outerBatch.Add(multiStatementBatch);
            ExecuteNonQuery(outerBatch);

            var countQuery = SqlDml.Select(tableRef);

            countQuery.Columns.Add(SqlDml.Count());
            Assert.AreEqual(6, Convert.ToInt32(ExecuteScalar(countQuery)));
        }
        /// <summary>
        /// Translates <see cref="AggregateColumn"/> to corresponding <see cref="SqlExpression"/>.
        /// </summary>
        /// <param name="source">The source <see cref="SqlProvider"/>.</param>
        /// <param name="sourceColumns">The source columns.</param>
        /// <param name="aggregateColumn">The aggregate column.</param>
        /// <returns>Aggregate processing result (expression).</returns>
        protected virtual SqlExpression ProcessAggregate(SqlProvider source, List <SqlExpression> sourceColumns, AggregateColumn aggregateColumn)
        {
            switch (aggregateColumn.AggregateType)
            {
            case AggregateType.Avg:
                return(SqlDml.Avg(sourceColumns[aggregateColumn.SourceIndex]));

            case AggregateType.Count:
                return(SqlDml.Count(SqlDml.Asterisk));

            case AggregateType.Max:
                return(SqlDml.Max(sourceColumns[aggregateColumn.SourceIndex]));

            case AggregateType.Min:
                return(SqlDml.Min(sourceColumns[aggregateColumn.SourceIndex]));

            case AggregateType.Sum:
                return(SqlDml.Sum(sourceColumns[aggregateColumn.SourceIndex]));

            default:
                throw new ArgumentException();
            }
        }
Esempio n. 12
0
        public void SqlAggregateCloneTest()
        {
            {
                SqlAggregate a      = SqlDml.Count();
                SqlAggregate aClone = (SqlAggregate)a.Clone();

                Assert.AreNotEqual(a, aClone);
                Assert.AreEqual(a.NodeType, aClone.NodeType);
                Assert.AreEqual(a.Distinct, aClone.Distinct);
                Assert.AreEqual(aClone.Expression.NodeType, SqlNodeType.Native);
            }
            Console.WriteLine();
            {
                SqlAggregate a      = SqlDml.Sum(1);
                SqlAggregate aClone = (SqlAggregate)a.Clone();

                Assert.AreNotEqual(a, aClone);
                Assert.AreNotEqual(a.Expression, aClone.Expression);
                Assert.AreEqual(a.NodeType, aClone.NodeType);
                Assert.AreEqual(a.Distinct, aClone.Distinct);
                Assert.AreEqual(a.Expression.NodeType, aClone.Expression.NodeType);
            }
        }
Esempio n. 13
0
        private void ValidateIgnoredData(Catalog catalog)
        {
            var validatableSchemas = new List <Schema>();

            if (nodeToSchemaMap.Count > 0)
            {
                validatableSchemas.Add(catalog.Schemas[nodeToSchemaMap[MainNodeId]]);
                validatableSchemas.Add(catalog.Schemas[nodeToSchemaMap[AdditionalNodeId]]);
            }
            else
            {
                validatableSchemas.Add(catalog.DefaultSchema);
            }

            foreach (var schema in validatableSchemas)
            {
                using (var connection = driver.CreateConnection()) {
                    connection.Open();

                    var productTable = schema.Tables["Product"];
                    var @ref         = SqlDml.TableRef(productTable);
                    var selectAll    = SqlDml.Select(@ref);
                    selectAll.Columns.Add(SqlDml.Column(SqlDml.Count()), "c0unt");

                    var selectWhere = SqlDml.Select(@ref);
                    selectWhere.Columns.Add(SqlDml.Column(SqlDml.Count()), "c0unt");
                    selectWhere.Where = @ref["HiddenName"] == SqlDml.Literal("Hidden name");
                    using (var expectedCountCommand = connection.CreateCommand(selectAll))
                        using (var actualCountCommand = connection.CreateCommand(selectWhere)) {
                            var expectedCount = Convert.ToInt64(expectedCountCommand.ExecuteScalar());
                            var actualCount   = Convert.ToInt64(actualCountCommand.ExecuteScalar());

                            Assert.That(actualCount, Is.EqualTo(expectedCount));
                        }

                    var priceListTable = schema.Tables["PriceList"];
                    @ref      = SqlDml.TableRef(priceListTable);
                    selectAll = SqlDml.Select(@ref);
                    selectAll.Columns.Add(SqlDml.Column(SqlDml.Count()), "c0unt");

                    selectWhere = SqlDml.Select(@ref);
                    selectWhere.Columns.Add(SqlDml.Column(SqlDml.Count()), "c0unt");
                    selectWhere.Where = @ref["HiddenComment"] == SqlDml.Literal("Some hidden comment");
                    using (var expectedCountCommand = connection.CreateCommand(selectAll))
                        using (var actualCountCommand = connection.CreateCommand(selectWhere)) {
                            var expectedCount = Convert.ToInt64(expectedCountCommand.ExecuteScalar());
                            var actualCount   = Convert.ToInt64(actualCountCommand.ExecuteScalar());

                            Assert.That(actualCount, Is.EqualTo(expectedCount));
                        }

                    var currencyTable = schema.Tables["Currency"];
                    @ref      = SqlDml.TableRef(currencyTable);
                    selectAll = SqlDml.Select(@ref);
                    selectAll.Columns.Add(SqlDml.Column(SqlDml.Count()), "c0unt");

                    selectWhere = SqlDml.Select(@ref);
                    selectWhere.Columns.Add(SqlDml.Column(SqlDml.Count()), "c0unt");
                    selectWhere.Where = @ref["NotInDomainColumn1"] == "Not in domain" && @ref["NotInDomainColumn1"] == "Not in domain" && @ref["NotInDomainColumn1"] == "Not in domain";

                    using (var expectedCountCommand = connection.CreateCommand(selectAll))
                        using (var actualCountCommand = connection.CreateCommand(selectWhere)) {
                            var expectedCount = Convert.ToInt64(expectedCountCommand.ExecuteScalar());
                            var actualCount   = Convert.ToInt64(actualCountCommand.ExecuteScalar());

                            Assert.That(actualCount, Is.EqualTo(expectedCount));
                        }
                }
            }
        }
Esempio n. 14
0
        private void BuildSelectForUniqueMatchPartial(SqlMatch node, SqlRow row, SqlSelect finalQuery)
        {
            bool          allNull = true;
            var           @case   = SqlDml.Case();
            SqlExpression when    = true;

            //if all row elements are null then true
            if (row.Count > 0)
            {
                var whenNotNeeded = false;
                for (var i = 0; i < row.Count; i++)
                {
                    //if any row element is surely not the NULL value
                    if (row[i].NodeType == SqlNodeType.Literal)
                    {
                        allNull       = false;
                        whenNotNeeded = true;
                        break;
                    }
                    if (allNull && row[i].NodeType != SqlNodeType.Null)
                    {
                        allNull = false;
                    }
                    when = i == 0
            ? SqlDml.IsNull(row[i])
            : when && SqlDml.IsNull(row[i]);
                }
                if (allNull)
                {
                    when = true;
                }
                if (!whenNotNeeded)
                {
                    _ = @case.Add(when, true);
                }
            }
            //otherwise
            if (!allNull)
            {
                //find row in subquery
                var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
                var subQuery      = SqlDml.Select(originalQuery);
                subQuery.Columns.Add(8);
                var columns = originalQuery.Columns;
                SqlExpression where = null;
                for (var i = 0; i < columns.Count; i++)
                {
                    //if row[i] would be NULL then c3 would result in true,
                    if (row[i].NodeType != SqlNodeType.Null)
                    {
                        SqlCase c3 = SqlDml.Case();
                        _       = c3.Add(SqlDml.IsNull(row[i]), true);
                        c3.Else = row[i] == columns[i];

                        where = where == null ? c3 : where && c3;
                    }
                    if (node.Unique)
                    {
                        var c4 = SqlDml.Case();
                        _       = c4.Add(SqlDml.IsNull(row[i]), 0);
                        c4.Else = columns[i];
                        subQuery.GroupBy.Add(c4);
                    }
                }
                subQuery.Where = where;
                if (node.Unique)
                {
                    subQuery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                }
                @case.Else = SqlDml.Exists(subQuery);
            }
            if (@case.Else == null)
            {
                @case.Else = false;
            }
            if (allNull)
            {
                finalQuery.Where = null;
            }
            else if (@case.Count > 0)
            {
                finalQuery.Where = @case;
            }
            else
            {
                finalQuery.Where = @case.Else;
            }
        }
Esempio n. 15
0
        private void BuildSelectForUniqueMatchFull(SqlMatch node, SqlRow row, SqlSelect finalQuery)
        {
            var           @case            = SqlDml.Case();
            bool          noMoreWhenNeeded = false;
            bool          allNull          = true;
            SqlExpression when1            = true;

            //if all row elements are null then true
            if (row.Count > 0)
            {
                var whenNotNeeded = false;
                for (var i = 0; i < row.Count; i++)
                {
                    //if any row element is surely not the NULL value
                    if (row[i].NodeType == SqlNodeType.Literal)
                    {
                        whenNotNeeded = true;
                        break;
                    }
                    if (allNull && row[i].NodeType != SqlNodeType.Null)
                    {
                        allNull = false;
                    }
                    if (i == 0)
                    {
                        when1 = SqlDml.IsNull(row[i]);
                    }
                    else
                    {
                        when1 = when1 && SqlDml.IsNull(row[i]);
                    }
                }
                if (allNull)
                {
                    when1 = true;
                }
                if (!whenNotNeeded)
                {
                    _ = @case.Add(when1, true);
                }
            }
            if (!noMoreWhenNeeded)
            {
                var           whenNotNeeded = false;
                var           allLiteral    = true;
                SqlExpression when2         = true;
                //if no row elements are null then subcase
                for (var i = 0; i < row.Count; i++)
                {
                    if (row[i].NodeType == SqlNodeType.Null)
                    {
                        whenNotNeeded = true;
                        when2         = false;
                        break;
                    }
                    if (allLiteral && row[i].NodeType != SqlNodeType.Literal)
                    {
                        allLiteral = false;
                    }
                    if (i == 0)
                    {
                        when2 = SqlDml.IsNotNull(row[i]);
                    }
                    else
                    {
                        when2 = when2 && SqlDml.IsNotNull(row[i]);
                    }
                }
                if (allLiteral)
                {
                    when2 = true;
                }
                if (!whenNotNeeded)
                {
                    //find row in subquery
                    var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
                    var subQuery      = SqlDml.Select(originalQuery);
                    subQuery.Columns.Add(1);

                    var columns = originalQuery.Columns;
                    SqlExpression where = null;
                    for (int i = 0; i < columns.Count; i++)
                    {
                        if (i == 0)
                        {
                            where = columns[i] == row[i];
                        }
                        else
                        {
                            where = where && columns[i] == row[i];
                        }
                        if (node.Unique)
                        {
                            subQuery.GroupBy.Add(columns[i]);
                        }
                    }
                    subQuery.Where = where;
                    if (node.Unique)
                    {
                        subQuery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                    }
                    _ = @case.Add(when2, SqlDml.Exists(subQuery));
                }
            }
            //else false
            @case.Else       = false;
            finalQuery.Where = @case.Count > 0 ? @case : (SqlExpression)false;
        }
Esempio n. 16
0
        private void BuildSelectForUniqueMatchNone(SqlMatch node, SqlRow row, SqlSelect finalQuery)
        {
            var existsNull     = false;
            var @case          = SqlDml.Case();
            var subQueryNeeded = true;

            //if any of the row elements is NULL then true
            if (row.Count > 0)
            {
                var           allLiteral = true; //if true then there is no NULL element
                SqlExpression when       = null;
                for (var i = 0; i < row.Count; i++)
                {
                    var elementIsNotLiteral = row[i].NodeType != SqlNodeType.Literal;
                    //if the row element is the NULL value
                    if (row[i].NodeType == SqlNodeType.Null)
                    {
                        existsNull = true;
                        break;
                    }
                    if (allLiteral && elementIsNotLiteral)
                    {
                        allLiteral = false;
                    }
                    if (elementIsNotLiteral)
                    {
                        when = when == null
              ? SqlDml.IsNull(row[i])
              : when || SqlDml.IsNull(row[i]);
                    }
                }
                if (existsNull)
                {
                    //Some row element is the NULL value, MATCH result is true
                    subQueryNeeded = false;
                }
                else if (allLiteral)
                {
                    //No row element is the NULL value
                    subQueryNeeded = true;
                }
                else //(!whenNotNeeded)
                //Check if any row element is NULL
                {
                    _ = @case.Add(when == null ? true : when, true);
                    subQueryNeeded = true;
                }
            }
            //find row in subquery
            if (subQueryNeeded)
            {
                var originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
                var subquery      = SqlDml.Select(originalQuery);
                subquery.Columns.Add(1);
                var columns = originalQuery.Columns;
                SqlExpression where = null;
                for (var i = 0; i < columns.Count; i++)
                {
                    if (i == 0)
                    {
                        where = columns[i] == row[i];
                    }
                    else
                    {
                        where = where && columns[i] == row[i];
                    }
                    if (node.Unique)
                    {
                        subquery.GroupBy.Add(columns[i]);
                    }
                }
                subquery.Where = where;
                if (node.Unique)
                {
                    subquery.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                }
                //c.Add(Sql.Exists(q1), true);
                @case.Else = SqlDml.Exists(subquery);
            }
            if (@case.Else == null)
            {
                @case.Else = false;
            }
            if (existsNull)
            {
                finalQuery.Where = null;
            }
            else if (@case.Count > 0)
            {
                finalQuery.Where = @case;
            }
            else
            {
                finalQuery.Where = @case.Else;
            }
        }
Esempio n. 17
0
        public override string Translate(SqlCompilerContext context, SqlMatch node, MatchSection section)
        {
            switch (section)
            {
            case MatchSection.Entry:
                //MATCH is not supported by PostgreSQL, we need some workaround
                SqlRow row = node.Value as SqlRow;
                if (row != null)
                {
                    SqlSelect finalQuery = SqlDml.Select();
                    finalQuery.Columns.Add(5);
                    switch (node.MatchType)
                    {
                        #region SIMPLE

                    case SqlMatchType.None: {
                        bool    existsNull = false;
                        SqlCase c          = SqlDml.Case();
                        {
                            bool subQueryNeeded = true;
                            //if any of the row elements is NULL then true
                            if (row.Count > 0)
                            {
                                bool          allLiteral = true; //if true then there is no NULL element
                                SqlExpression when1      = null;
                                for (int i = 0; i < row.Count; i++)
                                {
                                    bool elementIsNotLiteral = row[i].NodeType != SqlNodeType.Literal;
                                    //if the row element is the NULL value
                                    if (row[i].NodeType == SqlNodeType.Null)
                                    {
                                        existsNull = true;
                                        break;
                                    }
                                    if (allLiteral && elementIsNotLiteral)
                                    {
                                        allLiteral = false;
                                    }
                                    if (elementIsNotLiteral)
                                    {
                                        if (when1 == null)
                                        {
                                            when1 = SqlDml.IsNull(row[i]);
                                        }
                                        else
                                        {
                                            when1 = when1 || SqlDml.IsNull(row[i]);
                                        }
                                    }
                                }
                                if (existsNull)
                                {
                                    //Some row element is the NULL value, MATCH result is true
                                    subQueryNeeded = false;
                                }
                                else if (allLiteral)
                                {
                                    //No row element is the NULL value
                                    subQueryNeeded = true;
                                }
                                else //(!whenNotNeeded)
                                {
                                    //Check if any row element is NULL
                                    c.Add(when1 == null ? true : when1, true);
                                    subQueryNeeded = true;
                                }
                            }
                            //find row in subquery
                            if (subQueryNeeded)
                            {
                                SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
                                SqlSelect   q1            = SqlDml.Select(originalQuery);
                                q1.Columns.Add(1);
                                {
                                    SqlTableColumnCollection columns = originalQuery.Columns;
                                    SqlExpression where = null;
                                    for (int i = 0; i < columns.Count; i++)
                                    {
                                        if (i == 0)
                                        {
                                            where = columns[i] == row[i];
                                        }
                                        else
                                        {
                                            where = where && columns[i] == row[i];
                                        }
                                        if (node.Unique)
                                        {
                                            q1.GroupBy.Add(columns[i]);
                                        }
                                    }
                                    q1.Where = where;
                                    if (node.Unique)
                                    {
                                        q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                                    }
                                }
                                //c.Add(Sql.Exists(q1), true);
                                c.Else = SqlDml.Exists(q1);
                            }
                        }
                        if (c.Else == null)
                        {
                            c.Else = false;
                        }
                        if (existsNull)
                        {
                            finalQuery.Where = null;
                        }
                        else if (c.Count > 0)
                        {
                            finalQuery.Where = c;
                        }
                        else
                        {
                            finalQuery.Where = c.Else;
                        }
                        break;
                    }

                        #endregion

                        #region FULL

                    case SqlMatchType.Full: {
                        SqlCase c1 = SqlDml.Case();
                        {
                            bool          noMoreWhenNeeded = false;
                            bool          allNull          = true;
                            SqlExpression when1            = true;
                            //if all row elements are null then true
                            if (row.Count > 0)
                            {
                                bool whenNotNeeded = false;
                                for (int i = 0; i < row.Count; i++)
                                {
                                    //if any row element is surely not the NULL value
                                    if (row[i].NodeType == SqlNodeType.Literal)
                                    {
                                        whenNotNeeded = true;
                                        break;
                                    }
                                    if (allNull && row[i].NodeType != SqlNodeType.Null)
                                    {
                                        allNull = false;
                                    }
                                    if (i == 0)
                                    {
                                        when1 = SqlDml.IsNull(row[i]);
                                    }
                                    else
                                    {
                                        when1 = when1 && SqlDml.IsNull(row[i]);
                                    }
                                }
                                if (allNull)
                                {
                                    when1 = true;
                                }
                                if (!whenNotNeeded)
                                {
                                    c1.Add(when1, true);
                                }
                            }
                            if (!noMoreWhenNeeded)
                            {
                                bool          whenNotNeeded = false;
                                bool          allLiteral    = true;
                                SqlExpression when2         = true;
                                //if no row elements are null then subcase
                                for (int i = 0; i < row.Count; i++)
                                {
                                    if (row[i].NodeType == SqlNodeType.Null)
                                    {
                                        whenNotNeeded = true;
                                        when2         = false;
                                        break;
                                    }
                                    if (allLiteral && row[i].NodeType != SqlNodeType.Literal)
                                    {
                                        allLiteral = false;
                                    }
                                    if (i == 0)
                                    {
                                        when2 = SqlDml.IsNotNull(row[i]);
                                    }
                                    else
                                    {
                                        when2 = when2 && SqlDml.IsNotNull(row[i]);
                                    }
                                }
                                if (allLiteral)
                                {
                                    when2 = true;
                                }
                                if (!whenNotNeeded)
                                {
                                    //find row in subquery
                                    SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
                                    SqlSelect   q1            = SqlDml.Select(originalQuery);
                                    q1.Columns.Add(1);
                                    {
                                        SqlTableColumnCollection columns = originalQuery.Columns;
                                        SqlExpression where = null;
                                        for (int i = 0; i < columns.Count; i++)
                                        {
                                            if (i == 0)
                                            {
                                                where = columns[i] == row[i];
                                            }
                                            else
                                            {
                                                where = where && columns[i] == row[i];
                                            }
                                            if (node.Unique)
                                            {
                                                q1.GroupBy.Add(columns[i]);
                                            }
                                        }
                                        q1.Where = where;
                                        if (node.Unique)
                                        {
                                            q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                                        }
                                    }
                                    c1.Add(when2, SqlDml.Exists(q1));
                                }
                            }
                            //else false
                            c1.Else = false;
                        }
                        if (c1.Count > 0)
                        {
                            finalQuery.Where = c1;
                        }
                        else
                        {
                            finalQuery.Where = false;
                        }
                        break;
                    }

                        #endregion

                        #region PARTIAL

                    case SqlMatchType.Partial: {
                        bool    allNull = true;
                        SqlCase c1      = SqlDml.Case();
                        {
                            SqlExpression when1 = true;
                            //if all row elements are null then true
                            if (row.Count > 0)
                            {
                                bool whenNotNeeded = false;
                                for (int i = 0; i < row.Count; i++)
                                {
                                    //if any row element is surely not the NULL value
                                    if (row[i].NodeType == SqlNodeType.Literal)
                                    {
                                        allNull       = false;
                                        whenNotNeeded = true;
                                        break;
                                    }
                                    if (allNull && row[i].NodeType != SqlNodeType.Null)
                                    {
                                        allNull = false;
                                    }
                                    if (i == 0)
                                    {
                                        when1 = SqlDml.IsNull(row[i]);
                                    }
                                    else
                                    {
                                        when1 = when1 && SqlDml.IsNull(row[i]);
                                    }
                                }
                                if (allNull)
                                {
                                    when1 = true;
                                }
                                if (!whenNotNeeded)
                                {
                                    c1.Add(when1, true);
                                }
                            }
                            //otherwise
                            if (!allNull)
                            {
                                //find row in subquery
                                SqlQueryRef originalQuery = SqlDml.QueryRef(node.SubQuery.Query);
                                SqlSelect   q1            = SqlDml.Select(originalQuery);
                                q1.Columns.Add(8);
                                {
                                    SqlTableColumnCollection columns = originalQuery.Columns;
                                    SqlExpression where = null;
                                    for (int i = 0; i < columns.Count; i++)
                                    {
                                        //if row[i] would be NULL then c3 would result in true,
                                        if (row[i].NodeType != SqlNodeType.Null)
                                        {
                                            SqlCase c3 = SqlDml.Case();
                                            c3.Add(SqlDml.IsNull(row[i]), true);
                                            c3.Else = row[i] == columns[i];

                                            if (where == null)
                                            {
                                                where = c3;
                                            }
                                            else
                                            {
                                                where = where && c3;
                                            }
                                        }
                                        if (node.Unique)
                                        {
                                            SqlCase c4 = SqlDml.Case();
                                            c4.Add(SqlDml.IsNull(row[i]), 0);
                                            c4.Else = columns[i];
                                            q1.GroupBy.Add(c4);
                                        }
                                    }
                                    q1.Where = where;
                                    if (node.Unique)
                                    {
                                        q1.Having = SqlDml.Count(SqlDml.Asterisk) == 1;
                                    }
                                }
                                c1.Else = SqlDml.Exists(q1);
                            }
                        }
                        if (c1.Else == null)
                        {
                            c1.Else = false;
                        }
                        if (allNull)
                        {
                            finalQuery.Where = null;
                        }
                        else if (c1.Count > 0)
                        {
                            finalQuery.Where = c1;
                        }
                        else
                        {
                            finalQuery.Where = c1.Else;
                        }
                    }
                    break;

                        #endregion
                    }
                    SqlMatch newNode = SqlDml.Match(SqlDml.Row(), SqlDml.SubQuery(finalQuery).Query, node.Unique, node.MatchType);
                    node.ReplaceWith(newNode);
                    return("EXISTS(SELECT '");
                }
                else
                {
                    throw new InvalidOperationException(Strings.ExSqlMatchValueMustBeAnSqlRowInstance);
                }

            case MatchSection.Specification:
                return("' WHERE EXISTS");

            case MatchSection.Exit:
                return(")");
            }
            return(string.Empty);
        }