/*
 * Copyright (c) 2015, 2022, Oracle and/or its affiliates.
 *
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License, version 2.0, as published by the
 * Free Software Foundation.
 *
 * This program is also distributed with certain software (including but not
 * limited to OpenSSL) that is licensed under separate terms, as designated in a
 * particular file or component or in included license documentation. The
 * authors of MySQL hereby grant you an additional permission to link the
 * program and your derivative works with the separately licensed software that
 * they have included with MySQL.
 *
 * Without limiting anything contained in the foregoing, this file, which is
 * part of MySQL Connector/J, is also subject to the Universal FOSS Exception,
 * version 1.0, a copy of which can be found at
 * http://oss.oracle.com/licenses/universal-foss-exception.
 *
 * 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 General Public License, version 2.0,
 * for more details.
 *
 * You should have received a copy of the GNU General Public License along with
 * this program; if not, write to the Free Software Foundation, Inc.,
 * 51 Franklin St, Fifth Floor, Boston, MA 02110-1301  USA
 */

package testsuite.x.devapi;

import static com.mysql.cj.xdevapi.Expression.expr;
import static org.hamcrest.CoreMatchers.hasItems;
import static org.hamcrest.MatcherAssert.assertThat;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertFalse;
import static org.junit.jupiter.api.Assertions.assertNotEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
import static org.junit.jupiter.api.Assertions.assertNull;
import static org.junit.jupiter.api.Assertions.assertTrue;
import static org.junit.jupiter.api.Assertions.fail;
import static org.junit.jupiter.api.Assumptions.assumeTrue;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.Callable;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.TimeoutException;
import java.util.function.Function;
import java.util.stream.Collectors;

import org.hamcrest.CoreMatchers;
import org.junit.jupiter.api.Test;

import com.mysql.cj.ServerVersion;
import com.mysql.cj.exceptions.MysqlErrorNumbers;
import com.mysql.cj.exceptions.WrongArgumentException;
import com.mysql.cj.protocol.x.XProtocolError;
import com.mysql.cj.xdevapi.AddResult;
import com.mysql.cj.xdevapi.Collection;
import com.mysql.cj.xdevapi.DbDoc;
import com.mysql.cj.xdevapi.DbDocImpl;
import com.mysql.cj.xdevapi.DocResult;
import com.mysql.cj.xdevapi.FindStatement;
import com.mysql.cj.xdevapi.FindStatementImpl;
import com.mysql.cj.xdevapi.JsonArray;
import com.mysql.cj.xdevapi.JsonLiteral;
import com.mysql.cj.xdevapi.JsonNumber;
import com.mysql.cj.xdevapi.JsonString;
import com.mysql.cj.xdevapi.Result;
import com.mysql.cj.xdevapi.Row;
import com.mysql.cj.xdevapi.RowResult;
import com.mysql.cj.xdevapi.Session;
import com.mysql.cj.xdevapi.SessionFactory;
import com.mysql.cj.xdevapi.SessionImpl;
import com.mysql.cj.xdevapi.SqlResult;
import com.mysql.cj.xdevapi.Statement;
import com.mysql.cj.xdevapi.Table;
import com.mysql.cj.xdevapi.Warning;

/**
 * @todo
 */
public class CollectionFindTest extends BaseCollectionTestCase {

    @Test
    public void testProjection() {
        // TODO: the "1" is coming back from the server as a string. checking with xplugin team if this is ok
        this.collection.add("{\"_id\":\"the_id\",\"g\":1}").execute();

        DocResult docs = this.collection.find().fields("$._id as _id, $.g as g, 1 + 1 as q").execute();
        DbDoc doc = docs.next();
        assertEquals("the_id", ((JsonString) doc.get("_id")).getString());
        assertEquals(new Integer(1), ((JsonNumber) doc.get("g")).getInteger());
        assertEquals(new Integer(2), ((JsonNumber) doc.get("q")).getInteger());

        // multiple projection strings
        docs = this.collection.find().fields("$._id as _id", "$.g as g", "1 + 1 as q").execute();
        doc = docs.next();
        assertEquals("the_id", ((JsonString) doc.get("_id")).getString());
        assertEquals(new Integer(1), ((JsonNumber) doc.get("g")).getInteger());
        assertEquals(new Integer(2), ((JsonNumber) doc.get("q")).getInteger());
    }

    @Test
    public void testDocumentProjection() {
        // use a document as a projection
        this.collection.add("{\"_id\":\"the_id\",\"g\":1}").execute();

        DocResult docs = this.collection.find().fields(expr("{'_id':$._id, 'q':1 + 1, 'g2':-20*$.g}")).execute();
        DbDoc doc = docs.next();
        assertEquals("the_id", ((JsonString) doc.get("_id")).getString());
        assertEquals(-20, ((JsonNumber) doc.get("g2")).getBigDecimal().intValue());
        assertEquals(new Integer(2), ((JsonNumber) doc.get("q")).getInteger());
    }

    /**
     * MYSQLCONNJ-618
     */
    @Test
    public void outOfRange() {
        try {
            this.collection.add("{\"_id\": \"1\"}").execute();
            DocResult docs = this.collection.find().fields(expr(
                    mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.13")) ? "{'X':cast(pow(2,63) as signed)+1}" : "{'X':1-cast(pow(2,63) as signed)}"))
                    .execute();
            docs.next(); // we are getting valid data from xplugin before the error, need this call to force the error
            fail("Statement should raise an error");
        } catch (XProtocolError err) {
            assertEquals(MysqlErrorNumbers.ER_DATA_OUT_OF_RANGE, err.getErrorCode());
        }
    }

    /**
     * Test that {@link DocResult} implements {@link java.lang.Iterable}.
     */
    @Test
    public void testIterable() {
        if (!mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5"))) {
            this.collection.add("{\"_id\": \"1\"}").execute(); // Requires manual _id.
            this.collection.add("{\"_id\": \"2\"}").execute();
            this.collection.add("{\"_id\": \"3\"}").execute();
        } else {
            this.collection.add("{}").execute();
            this.collection.add("{}").execute();
            this.collection.add("{}").execute();
        }
        DocResult docs = this.collection.find().execute();
        int numDocs = 0;
        for (DbDoc d : docs) {
            if (d != null) {
                numDocs++;
            }
        }
        assertEquals(3, numDocs);
    }

    @Test
    public void basicCollectionAsTable() {
        if (!mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5"))) {
            this.collection.add("{\"_id\": \"1\", \"xyz\":1}").execute(); // Requires manual _id.
        } else {
            this.collection.add("{\"xyz\":1}").execute();
        }
        Table coll = this.schema.getCollectionAsTable(this.collection.getName());
        Row r = coll.select("doc").execute().next();
        DbDoc doc = r.getDbDoc("doc");
        assertEquals(new Integer(1), ((JsonNumber) doc.get("xyz")).getInteger());
    }

    @SuppressWarnings("deprecation")
    @Test
    public void testLimitOffset() {
        if (!mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5"))) {
            this.collection.add("{\"_id\": \"1\"}").execute(); // Requires manual _id.
            this.collection.add("{\"_id\": \"2\"}").execute();
            this.collection.add("{\"_id\": \"3\"}").execute();
            this.collection.add("{\"_id\": \"4\"}").execute();
        } else {
            this.collection.add("{}").execute();
            this.collection.add("{}").execute();
            this.collection.add("{}").execute();
            this.collection.add("{}").execute();
        }

        // limit 1, order by ID, save the first ID
        DocResult docs = this.collection.find().orderBy("$._id").limit(1).execute();
        assertTrue(docs.hasNext());
        String firstId = ((JsonString) docs.next().get("_id")).getString();
        if (mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5"))) {
            assertTrue(firstId.matches("[a-f0-9]{28}"));
        }
        assertFalse(docs.hasNext());

        // limit 3, offset 1, order by ID, make sure we don't see the first ID
        docs = this.collection.find().orderBy("$._id").limit(3).offset(1).execute();
        assertTrue(docs.hasNext());
        assertNotEquals(firstId, ((JsonString) docs.next().get("_id")).getString());
        assertTrue(docs.hasNext());
        assertNotEquals(firstId, ((JsonString) docs.next().get("_id")).getString());
        assertTrue(docs.hasNext());
        assertNotEquals(firstId, ((JsonString) docs.next().get("_id")).getString());
        assertFalse(docs.hasNext());

        // Test deprecated skip(n) alias for offset
        // limit 1, offset 1, order by ID, make sure we don't see the first ID
        docs = this.collection.find().orderBy("$._id").limit(1).skip(1).execute();
        assertTrue(docs.hasNext());
        assertNotEquals(firstId, ((JsonString) docs.next().get("_id")).getString());
        assertFalse(docs.hasNext());
    }

    @Test
    public void testNumericExpressions() {
        if (!mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5"))) {
            this.collection.add("{\"_id\": \"1\", \"x\":1, \"y\":2}").execute(); // Requires manual _id.
        } else {
            this.collection.add("{\"x\":1, \"y\":2}").execute();
        }

        DocResult docs;

        docs = this.collection.find("$.x + $.y = 3").execute();
        docs.next();
        docs = this.collection.find("$.y - $.x = 1").execute();
        docs.next();
        docs = this.collection.find("$.y = $.x * 2").execute();
        docs.next();
        docs = this.collection.find("$.x = $.y / 2").execute();
        docs.next();
        docs = this.collection.find("$.x = 3 % $.y").execute();
        docs.next();
        docs = this.collection.find("$.x != $.y").execute();
        docs.next();
        docs = this.collection.find("$.x < $.y").execute();
        docs.next();
        docs = this.collection.find("$.x <= $.y").execute();
        docs.next();
        docs = this.collection.find("$.y > $.x").execute();
        docs.next();
        docs = this.collection.find("$.y >= $.x").execute();
        docs.next();
        docs = this.collection.find("$.y > 1.9 and $.y < 2.1").execute();
        docs.next();
    }

    @Test
    public void testBitwiseExpressions() {
        if (!mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5"))) {
            this.collection.add("{\"_id\": \"1\", \"x1\":31, \"x2\":13, \"x3\":8, \"x4\":\"18446744073709551614\"}").execute(); // Requires manual _id.
        } else {
            this.collection.add("{\"x1\":31, \"x2\":13, \"x3\":8, \"x4\":\"18446744073709551614\"}").execute();
        }

        DocResult docs;

        docs = this.collection.find("$.x1 = 29 | 15").execute();
        docs.next();
        docs = this.collection.find("$.x2 = 29 & 15").execute();
        docs.next();
        docs = this.collection.find("$.x3 = 11 ^ 3").execute();
        docs.next();
        docs = this.collection.find("$.x3 = 1 << 3").execute();
        docs.next();
        docs = this.collection.find("$.x3 = 16 >> 1").execute();
        docs.next();
        docs = this.collection.find("cast($.x4 as unsigned) = ~1").execute();
        docs.next();
    }

    @Test
    public void testIntervalExpressions() {
        if (!mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5"))) {
            this.collection.add("{\"_id\": \"1\", \"aDate\":\"2000-01-01\", \"aDatetime\":\"2000-01-01 12:00:01\"}").execute(); // Requires manual _id.
        } else {
            this.collection.add("{\"aDate\":\"2000-01-01\", \"aDatetime\":\"2000-01-01 12:00:01\"}").execute();
        }

        DocResult docs;

        docs = this.collection.find("$.aDatetime + interval 1000000 microsecond = '2000-01-01 12:00:02'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval 1 second = '2000-01-01 12:00:02'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval 2 minute = '2000-01-01 12:02:01'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval 4 hour = '2000-01-01 16:00:01'").execute();
        docs.next();
        docs = this.collection.find("$.aDate + interval 10 day = '2000-01-11'").execute();
        docs.next();
        docs = this.collection.find("$.aDate + interval 2 week = '2000-01-15'").execute();
        docs.next();
        docs = this.collection.find("$.aDate - interval 2 month = '1999-11-01'").execute();
        docs.next();
        docs = this.collection.find("$.aDate + interval 2 quarter = '2000-07-01'").execute();
        docs.next();
        docs = this.collection.find("$.aDate - interval 1 year = '1999-01-01'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval '3.1000000' second_microsecond = '2000-01-01 12:00:05'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval '1:1.1' minute_microsecond = '2000-01-01 12:01:02.100000'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval '1:1' minute_second = '2000-01-01 12:01:02'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval '1:1:1.1' hour_microsecond = '2000-01-01 13:01:02.100000'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval '1:1:1' hour_second = '2000-01-01 13:01:02'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval '1:1' hour_minute = '2000-01-01 13:01:01'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval '2 3:4:5.600' day_microsecond = '2000-01-03 15:04:06.600000'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval '2 3:4:5' day_second = '2000-01-03 15:04:06'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval '2 3:4' day_minute = '2000-01-03 15:04:01'").execute();
        docs.next();
        docs = this.collection.find("$.aDatetime + interval '2 3' day_hour = '2000-01-03 15:00:01'").execute();
        docs.next();
        docs = this.collection.find("$.aDate + interval '2-3' year_month = '2002-04-01'").execute();
        docs.next();
    }

    @Test
    // these are important to test the "operator" (BETWEEN/REGEXP/etc) to function representation in the protocol
    public void testIlriExpressions() {
        if (!mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5"))) {
            this.collection.add("{\"_id\": \"1\", \"a\":\"some text with 5432\", \"b\":\"100\", \"c\":true}").execute(); // Requires manual _id.
        } else {
            this.collection.add("{\"a\":\"some text with 5432\", \"b\":\"100\", \"c\":true}").execute();
        }

        DocResult docs;

        // TODO: this is a known problem on the server with JSON value types (with IS NULL too)
        // docs = this.collection.find("$.c IS TRUE AND $.c IS NOT FALSE").execute();
        // docs.next();

        docs = this.collection.find("$.b IN (1,100) AND $.b NOT IN (2, 200)").execute();
        docs.next();

        docs = this.collection.find("$.a LIKE '%5432' AND $.a NOT LIKE '%xxx'").execute();
        docs.next();

        docs = this.collection.find("$.b between 99 and 101 and $.b not between 101 and 102").execute();
        docs.next();

        docs = this.collection.find("$.a REGEXP '5432'").execute();
        docs.next();
        docs = this.collection.find("$.a NOT REGEXP '5432'").execute();
        assertFalse(docs.hasNext());

        if (mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.2"))) {

            // cont_in

            docs = this.collection.find("CAST(JSON_UNQUOTE($.b) AS JSON) IN [100,101,102]").execute();
            assertEquals(1, docs.count());

            docs = this.collection.find("$.b IN ['100','101','102']").execute();
            assertEquals(1, docs.count());

            docs = this.collection.find("'some text with 5432' in $.a").execute();
            assertEquals(1, docs.count());

            docs = this.collection.find("1 in [1, 2, 4]").execute();
            assertEquals(1, docs.count());

            docs = this.collection.find("5 in [1, 2, 4]").execute();
            assertEquals(0, docs.count());

            docs = this.collection.find("{'a': 2} in {'a': 1, 'b': 2}").execute();
            assertEquals(0, docs.count());

            docs = this.collection.find("{'a': 1} in {'a': 1, 'b': 2}").execute();
            assertEquals(1, docs.count());

            docs = this.collection.find("{'a': 1} in {'a': 1, 'b': 2}").execute();
            assertEquals(1, docs.count());

            // not_cont_in

            docs = this.collection.find("3 not in [1, 2, 4]").execute();
            assertEquals(1, docs.count());

            docs = this.collection.find("'some text with 5432' not in $.a").execute();
            assertEquals(0, docs.count());

            docs = this.collection.find("'qqq' not in $.a").execute();
            assertEquals(1, docs.count());

            docs = this.collection.find("{'a': 1} not in {'a': 1, 'b': 2}").execute();
            assertEquals(0, docs.count());

            docs = this.collection.find("{'a': 2} not in {'a': 1, 'b': 2} AND $.b NOT IN (100, 200)").execute();
            assertEquals(0, docs.count());
        }
    }

    @Test
    public void cast() {
        if (!mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5"))) {
            this.collection.add("{\"_id\": \"1\", \"x\":100}").execute();
        } else {
            this.collection.add("{\"x\":100}").execute();
        }

        DbDoc d = this.collection.find().fields("CAST($.x as SIGNED) as x").execute().next();
        assertEquals(new Integer(100), ((JsonNumber) d.get("x")).getInteger());
    }

    @Test
    public void testOrderBy() {
        this.collection.add("{\"_id\":1, \"x\":20, \"y\":22}").execute();
        this.collection.add("{\"_id\":2, \"x\":20, \"y\":21}").execute();
        this.collection.add("{\"_id\":3, \"x\":10, \"y\":40}").execute();
        this.collection.add("{\"_id\":4, \"x\":10, \"y\":50}").execute();

        DocResult docs = this.collection.find().orderBy("$.x, $.y").execute();
        assertTrue(docs.hasNext());
        assertEquals(new Integer(3), ((JsonNumber) docs.next().get("_id")).getInteger());
        assertTrue(docs.hasNext());
        assertEquals(new Integer(4), ((JsonNumber) docs.next().get("_id")).getInteger());
        assertTrue(docs.hasNext());
        assertEquals(new Integer(2), ((JsonNumber) docs.next().get("_id")).getInteger());
        assertTrue(docs.hasNext());
        assertEquals(new Integer(1), ((JsonNumber) docs.next().get("_id")).getInteger());
        assertFalse(docs.hasNext());

        // multiple SortExprStr
        docs = this.collection.find().sort("$.x", "$.y").execute();
        assertTrue(docs.hasNext());
        assertEquals(new Integer(3), ((JsonNumber) docs.next().get("_id")).getInteger());
        assertTrue(docs.hasNext());
        assertEquals(new Integer(4), ((JsonNumber) docs.next().get("_id")).getInteger());
        assertTrue(docs.hasNext());
        assertEquals(new Integer(2), ((JsonNumber) docs.next().get("_id")).getInteger());
        assertTrue(docs.hasNext());
        assertEquals(new Integer(1), ((JsonNumber) docs.next().get("_id")).getInteger());
        assertFalse(docs.hasNext());
    }

    @Test
    public void testCollectionRowLocks() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.3")), "MySQL 8.0.3+ is required to run this test.");

        this.collection.add("{\"_id\":\"1\", \"a\":1}").execute();
        this.collection.add("{\"_id\":\"2\", \"a\":1}").execute();
        this.collection.add("{\"_id\":\"3\", \"a\":1}").execute();

        Session session1 = null;
        Session session2 = null;

        try {
            session1 = new SessionFactory().getSession(this.testProperties);
            Collection col1 = session1.getDefaultSchema().getCollection(this.collectionName);
            session2 = new SessionFactory().getSession(this.testProperties);
            Collection col2 = session2.getDefaultSchema().getCollection(this.collectionName);

            // test1: Shared Lock
            session1.startTransaction();
            col1.find("_id = '1'").lockShared().execute();

            session2.startTransaction();
            col2.find("_id = '2'").lockShared().execute(); // should return immediately

            CompletableFuture<DocResult> res1 = col2.find("_id = '1'").lockShared().executeAsync(); // should return immediately
            res1.get(5, TimeUnit.SECONDS);
            assertTrue(res1.isDone());

            session1.rollback();
            session2.rollback();

            // test2: Shared Lock after Exclusive
            session1.startTransaction();
            col1.find("_id = '1'").lockExclusive().execute();

            session2.startTransaction();
            col2.find("_id = '2'").lockShared().execute(); // should return immediately
            CompletableFuture<DocResult> res2 = col2.find("_id = '1'").lockShared().executeAsync(); // session2 blocks
            assertThrows(TimeoutException.class, new Callable<Void>() {
                public Void call() throws Exception {
                    res2.get(5, TimeUnit.SECONDS);
                    return null;
                }
            });

            session1.rollback(); // session2 should unblock now
            res2.get(5, TimeUnit.SECONDS);
            assertTrue(res2.isDone());
            session2.rollback();

            // test3: Exclusive after Shared
            session1.startTransaction();
            col1.find("_id = '1'").lockShared().execute();
            col1.find("_id = '3'").lockShared().execute();

            session2.startTransaction();
            col2.find("_id = '2'").lockExclusive().execute(); // should return immediately
            col2.find("_id = '3'").lockShared().execute(); // should return immediately
            CompletableFuture<DocResult> res3 = col2.find("_id = '1'").lockExclusive().executeAsync(); // session2 should block
            assertThrows(TimeoutException.class, new Callable<Void>() {
                public Void call() throws Exception {
                    res3.get(5, TimeUnit.SECONDS);
                    return null;
                }
            });

            session1.rollback(); // session2 should unblock now
            res3.get(5, TimeUnit.SECONDS);
            assertTrue(res3.isDone());
            session2.rollback();

            // test4: Exclusive after Exclusive
            session1.startTransaction();
            col1.find("_id = '1'").lockExclusive().execute();

            session2.startTransaction();
            col2.find("_id = '2'").lockExclusive().execute(); // should return immediately
            CompletableFuture<DocResult> res4 = col2.find("_id = '1'").lockExclusive().executeAsync(); // session2 should block
            assertThrows(TimeoutException.class, new Callable<Void>() {
                public Void call() throws Exception {
                    res4.get(5, TimeUnit.SECONDS);
                    return null;
                }
            });

            session1.rollback(); // session2 should unblock now
            res4.get(5, TimeUnit.SECONDS);
            assertTrue(res4.isDone());
            session2.rollback();

        } finally {
            if (session1 != null) {
                session1.close();
            }
            if (session2 != null) {
                session2.close();
            }
        }
    }

    @Test
    public void testCollectionRowLockOptions() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5")), "MySQL 8.0.5+ is required to run this test.");

        Function<DocResult, List<String>> asStringList = rr -> rr.fetchAll().stream().map(d -> ((JsonString) d.get("_id")).getString())
                .collect(Collectors.toList());

        this.collection.add("{\"_id\":\"1\", \"a\":1}").add("{\"_id\":\"2\", \"a\":1}").add("{\"_id\":\"3\", \"a\":1}").execute();

        Session session1 = null;
        Session session2 = null;

        try {
            session1 = new SessionFactory().getSession(this.testProperties);
            Collection col1 = session1.getDefaultSchema().getCollection(this.collectionName);
            session2 = new SessionFactory().getSession(this.testProperties);
            Collection col2 = session2.getDefaultSchema().getCollection(this.collectionName);
            DocResult res;
            CompletableFuture<DocResult> futRes;

            /*
             * 1. Shared Lock in both sessions.
             */

            // session2.lockShared() returns data immediately.
            session1.startTransaction();
            col1.find("_id = '1'").lockShared().execute();

            session2.startTransaction();
            res = col2.find("_id < '3'").lockShared().execute();
            assertEquals(2, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), hasItems("1", "2"));
            session2.rollback();

            session2.startTransaction();
            futRes = col2.find("_id < '3'").lockShared().executeAsync();
            res = futRes.get(3, TimeUnit.SECONDS);
            assertTrue(futRes.isDone());
            assertEquals(2, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
            session2.rollback();

            session1.rollback();

            // session2.lockShared(NOWAIT) returns data immediately.
            session1.startTransaction();
            col1.find("_id = '1'").lockShared().execute();

            session2.startTransaction();
            res = col2.find("_id < '3'").lockShared(Statement.LockContention.NOWAIT).execute();
            assertEquals(2, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
            session2.rollback();

            session2.startTransaction();
            futRes = col2.find("_id < '3'").lockShared(Statement.LockContention.NOWAIT).executeAsync();
            res = futRes.get(3, TimeUnit.SECONDS);
            assertTrue(futRes.isDone());
            assertEquals(2, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
            session2.rollback();

            session1.rollback();

            // session2.lockShared(SKIP_LOCK) returns data immediately.
            session1.startTransaction();
            col1.find("_id = '1'").lockShared().execute();

            session2.startTransaction();
            res = col2.find("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).execute();
            assertEquals(2, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
            session2.rollback();

            session2.startTransaction();
            futRes = col2.find("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).executeAsync();
            res = futRes.get(3, TimeUnit.SECONDS);
            assertTrue(futRes.isDone());
            assertEquals(2, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
            session2.rollback();

            session1.rollback();

            /*
             * 2. Shared Lock in first session and exclusive lock in second.
             */

            // session2.lockExclusive() blocks until session1 ends.
            session1.startTransaction();
            col1.find("_id = '1'").lockShared().execute();

            // session2.startTransaction();
            // res = col2.find("_id < '3'").lockExclusive().execute(); (Can't test)
            // session2.rollback();

            session2.startTransaction();
            futRes = col2.find("_id < '3'").lockExclusive().executeAsync();
            final CompletableFuture<DocResult> fr1 = futRes;
            assertThrows(TimeoutException.class, () -> fr1.get(3, TimeUnit.SECONDS));

            session1.rollback(); // Unlocks session2.

            res = futRes.get(3, TimeUnit.SECONDS);
            assertTrue(futRes.isDone());
            assertEquals(2, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
            session2.rollback();

            // session2.lockExclusive(NOWAIT) should return locking error.
            session1.startTransaction();
            col1.find("_id = '1'").lockShared().execute();

            session2.startTransaction();
            assertThrows(XProtocolError.class,
                    "ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.",
                    () -> col2.find("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).execute());
            session2.rollback();

            session2.startTransaction();
            futRes = col2.find("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).executeAsync();
            final CompletableFuture<DocResult> fr2 = futRes;
            assertThrows(ExecutionException.class,
                    ".*XProtocolError: ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.",
                    () -> fr2.get(3, TimeUnit.SECONDS));
            session2.rollback();

            session1.rollback();

            // session2.lockExclusive(SKIP_LOCK) should return (unlocked) data immediately.
            session1.startTransaction();
            col1.find("_id = '1'").lockShared().execute();

            session2.startTransaction();
            res = col2.find("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).execute();
            assertEquals(1, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("2"));
            session2.rollback();

            session2.startTransaction();
            futRes = col2.find("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).executeAsync();
            res = futRes.get(3, TimeUnit.SECONDS);
            assertTrue(futRes.isDone());
            assertEquals(1, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("2"));
            session2.rollback();

            session1.rollback();

            /*
             * 3. Exclusive Lock in first session and shared lock in second.
             */

            // session2.lockShared() blocks until session1 ends.
            session1.startTransaction();
            col1.find("_id = '1'").lockExclusive().execute();

            // session2.startTransaction();
            // res = col2.find("_id < '3'").lockShared().execute(); (Can't test)
            // session2.rollback();

            session2.startTransaction();
            futRes = col2.find("_id < '3'").lockShared().executeAsync();
            final CompletableFuture<DocResult> fr3 = futRes;
            assertThrows(TimeoutException.class, () -> fr3.get(3, TimeUnit.SECONDS));

            session1.rollback(); // Unlocks session2.

            res = futRes.get(3, TimeUnit.SECONDS);
            assertTrue(futRes.isDone());
            assertEquals(2, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
            session2.rollback();

            // session2.lockShared(NOWAIT) should return locking error.
            session1.startTransaction();
            col1.find("_id = '1'").lockExclusive().execute();

            session2.startTransaction();
            assertThrows(XProtocolError.class,
                    "ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.",
                    () -> col2.find("_id < '3'").lockShared(Statement.LockContention.NOWAIT).execute());
            session2.rollback();

            session2.startTransaction();
            futRes = col2.find("_id < '3'").lockShared(Statement.LockContention.NOWAIT).executeAsync();
            final CompletableFuture<DocResult> fr4 = futRes;
            assertThrows(ExecutionException.class,
                    ".*XProtocolError: ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.",
                    () -> fr4.get(3, TimeUnit.SECONDS));
            session2.rollback();

            session1.rollback();

            // session2.lockShared(SKIP_LOCK) should return (unlocked) data immediately.
            session1.startTransaction();
            col1.find("_id = '1'").lockExclusive().execute();

            session2.startTransaction();
            res = col2.find("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).execute();
            assertEquals(1, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("2"));
            session2.rollback();

            session2.startTransaction();
            futRes = col2.find("_id < '3'").lockShared(Statement.LockContention.SKIP_LOCKED).executeAsync();
            res = futRes.get(3, TimeUnit.SECONDS);
            assertTrue(futRes.isDone());
            assertEquals(1, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("2"));
            session2.rollback();

            session1.rollback();

            /*
             * 4. Exclusive Lock in both sessions.
             */

            // session2.lockExclusive() blocks until session1 ends.
            session1.startTransaction();
            col1.find("_id = '1'").lockExclusive().execute();

            // session2.startTransaction();
            // res = col2.find("_id < '3'").lockExclusive().execute(); (Can't test)
            // session2.rollback();

            session2.startTransaction();
            futRes = col2.find("_id < '3'").lockExclusive().executeAsync();
            final CompletableFuture<DocResult> fr5 = futRes;
            assertThrows(TimeoutException.class, () -> fr5.get(3, TimeUnit.SECONDS));

            session1.rollback(); // Unlocks session2.

            res = futRes.get(3, TimeUnit.SECONDS);
            assertTrue(futRes.isDone());
            assertEquals(2, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("1", "2"));
            session2.rollback();

            // session2.lockExclusive(NOWAIT) should return locking error.
            session1.startTransaction();
            col1.find("_id = '1'").lockExclusive().execute();

            session2.startTransaction();
            assertThrows(XProtocolError.class,
                    "ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.",
                    () -> col2.find("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).execute());
            session2.rollback();

            session2.startTransaction();
            futRes = col2.find("_id < '3'").lockExclusive(Statement.LockContention.NOWAIT).executeAsync();
            final CompletableFuture<DocResult> fr6 = futRes;
            assertThrows(ExecutionException.class,
                    ".*XProtocolError: ERROR 3572 \\(HY000\\) Statement aborted because lock\\(s\\) could not be acquired immediately and NOWAIT is set\\.",
                    () -> fr6.get(3, TimeUnit.SECONDS));
            session2.rollback();

            session1.rollback();

            // session2.lockExclusive(SKIP_LOCK) should return (unlocked) data immediately.
            session1.startTransaction();
            col1.find("_id = '1'").lockExclusive().execute();

            session2.startTransaction();
            res = col2.find("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).execute();
            assertEquals(1, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("2"));
            session2.rollback();

            session2.startTransaction();
            futRes = col2.find("_id < '3'").lockExclusive(Statement.LockContention.SKIP_LOCKED).executeAsync();
            res = futRes.get(3, TimeUnit.SECONDS);
            assertTrue(futRes.isDone());
            assertEquals(1, asStringList.apply(res).size());
            assertThat(asStringList.apply(res), CoreMatchers.hasItems("2"));
            session2.rollback();

            session1.rollback();
        } finally {
            if (session1 != null) {
                session1.close();
            }
            if (session2 != null) {
                session2.close();
            }
        }
    }

    @Test
    public void getOne() {
        if (!mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.5"))) {
            this.collection.add("{\"_id\": \"1\", \"a\":1}").execute();
            this.collection.add("{\"_id\": \"2\", \"a\":2}").execute();
        } else {
            this.collection.add("{\"a\":1}").execute();
            this.collection.add("{\"a\":2}").execute();
        }
        this.collection.add("{\"_id\":\"existingId\",\"a\":3}").execute();

        DbDoc doc = this.collection.getOne("existingId");
        assertNotNull(doc);
        assertEquals(new Integer(3), ((JsonNumber) doc.get("a")).getInteger());

        doc = this.collection.getOne("NotExistingId");
        assertNull(doc);

        doc = this.collection.getOne(null);
        assertNull(doc);
    }

    @Test
    public void testGroupingQuery() {
        this.collection.add("{\"_id\": \"01\", \"name\":\"Mamie\", \"age\":11, \"something\":0}").execute();
        this.collection.add("{\"_id\": \"02\", \"name\":\"Eulalia\", \"age\":11, \"something\":0}").execute();
        this.collection.add("{\"_id\": \"03\", \"name\":\"Polly\", \"age\":12, \"something\":0}").execute();
        this.collection.add("{\"_id\": \"04\", \"name\":\"Rufus\", \"age\":12, \"something\":0}").execute();
        this.collection.add("{\"_id\": \"05\", \"name\":\"Cassidy\", \"age\":13, \"something\":0}").execute();
        this.collection.add("{\"_id\": \"06\", \"name\":\"Olympia\", \"age\":14, \"something\":0}").execute();
        this.collection.add("{\"_id\": \"07\", \"name\":\"Lev\", \"age\":14, \"something\":0}").execute();
        this.collection.add("{\"_id\": \"08\", \"name\":\"Tierney\", \"age\":15, \"something\":0}").execute();
        this.collection.add("{\"_id\": \"09\", \"name\":\"Octavia\", \"age\":15, \"something\":0}").execute();
        this.collection.add("{\"_id\": \"10\", \"name\":\"Vesper\", \"age\":16, \"something\":0}").execute();
        this.collection.add("{\"_id\": \"11\", \"name\":\"Caspian\", \"age\":17, \"something\":0}").execute();
        this.collection.add("{\"_id\": \"12\", \"name\":\"Romy\", \"age\":17, \"something\":0}").execute();

        // Result:
        // age_group | cnt
        // 11        | 2   <-- filtered out by where
        // 12        | 2   <-- filtered out by limit
        // 13        | 1   <-- filtered out by having
        // 14        | 2   * second row in result
        // 15        | 2   * first row in result
        // 16        | 1   <-- filtered out by having
        // 17        | 2   <-- filtered out by offset
        DocResult res = this.collection.find("age > 11 and 1 < 2 and 40 between 30 and 900") //
                .fields("age as age_group, count(name) as cnt, something as something") //
                .groupBy("something, age") //
                .having("count(name) > 1") //
                .orderBy("age desc") //
                .limit(2).offset(1) //
                .execute();

        assertEquals(2, res.count());

        DbDoc doc = res.fetchOne();
        assertEquals(15, ((JsonNumber) doc.get("age_group")).getInteger().intValue());
        assertEquals(2, ((JsonNumber) doc.get("cnt")).getInteger().intValue());
        assertEquals(0, ((JsonNumber) doc.get("something")).getInteger().intValue());

        doc = res.fetchOne();
        assertEquals(14, ((JsonNumber) doc.get("age_group")).getInteger().intValue());
        assertEquals(2, ((JsonNumber) doc.get("cnt")).getInteger().intValue());
        assertEquals(0, ((JsonNumber) doc.get("something")).getInteger().intValue());
    }

    /**
     * Test for Bug#21921956, X DEVAPI: EXPRESSION PARSE ERROR WITH UNARY OPERATOR.
     */
    @Test
    public void testBug21921956() {
        this.collection.add("{\"_id\": \"1004\", \"F1\": 123}").execute();

        DocResult res = this.collection.find().fields(expr("{'X':4<< -(1-2)}")).execute();
        assertTrue(res.hasData());
        DbDoc doc = res.fetchOne();
        assertEquals(8, ((JsonNumber) doc.get("X")).getInteger().intValue());

        res = this.collection.find().fields(expr("{'X':4<< +(3-2)}")).execute();
        assertTrue(res.hasData());
        doc = res.fetchOne();
        assertEquals(8, ((JsonNumber) doc.get("X")).getInteger().intValue());

        res = this.collection.find().fields(expr("{'X':4<< !(2-2)}")).execute();
        assertTrue(res.hasData());
        doc = res.fetchOne();
        assertEquals(8, ((JsonNumber) doc.get("X")).getInteger().intValue());

        res = this.collection.find("F1 = -(-123)").execute();
        if (mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.14"))) {
            // TODO is probably a bug in MySQL 5.7 server:
            // the above request generates query like:
            // SELECT doc FROM `cjtest_5_1`.`CollectionTest-617` WHERE (JSON_EXTRACT(doc,'$.F1') = (--123))
            // which returns no records with MySQL 5.7.24 while returns a document with MySQL 8.0.14
            assertTrue(res.hasData());
            doc = res.fetchOne();
            assertEquals("1004", ((JsonString) doc.get("_id")).getString());
        }
    }

    @Test
    public void testPreparedStatements() {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.14")), "MySQL 8.0.14+ is required to run this test.");

        // Prepare test data.
        this.collection.add("{\"_id\":\"1\", \"ord\": 1}", "{\"_id\":\"2\", \"ord\": 2}", "{\"_id\":\"3\", \"ord\": 3}", "{\"_id\":\"4\", \"ord\": 4}",
                "{\"_id\":\"5\", \"ord\": 5}", "{\"_id\":\"6\", \"ord\": 6}", "{\"_id\":\"7\", \"ord\": 7}", "{\"_id\":\"8\", \"ord\": 8}").execute();

        SessionFactory sf = new SessionFactory();

        /*
         * Test common usage.
         */
        Session testSession = sf.getSession(this.testProperties);

        int sessionThreadId = getThreadId(testSession);
        assertPreparedStatementsCount(sessionThreadId, 0, 1);
        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);

        Collection testCol = testSession.getDefaultSchema().getCollection(this.collectionName);

        // Initialize several FindStatement objects.
        FindStatement testFind1 = testCol.find(); // Find all.
        FindStatement testFind2 = testCol.find("$.ord >= :n"); // Criteria with one placeholder.
        FindStatement testFind3 = testCol.find("$.ord >= :n AND $.ord <= :n + 3"); // Criteria with same placeholder repeated.
        FindStatement testFind4 = testCol.find("$.ord >= :n AND $.ord <= :m"); // Criteria with multiple placeholders.

        assertPreparedStatementsCountsAndId(testSession, 0, testFind1, 0, -1);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind2, 0, -1);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind3, 0, -1);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind4, 0, -1);

        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);

        // A. Set binds: 1st execute -> non-prepared.
        assertTestPreparedStatementsResult(testFind1.execute(), 1, 8);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind1, 0, -1);
        assertTestPreparedStatementsResult(testFind2.bind("n", 2).execute(), 2, 8);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind2, 0, -1);
        assertTestPreparedStatementsResult(testFind3.bind("n", 2).execute(), 2, 5);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind3, 0, -1);
        assertTestPreparedStatementsResult(testFind4.bind("n", 2).bind("m", 5).execute(), 2, 5);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind4, 0, -1);

        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);

        // B. Set sort resets execution count: 1st execute -> non-prepared.
        assertTestPreparedStatementsResult(testFind1.sort("$._id").execute(), 1, 8);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind1, 0, -1);
        assertTestPreparedStatementsResult(testFind2.sort("$._id").execute(), 2, 8);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind2, 0, -1);
        assertTestPreparedStatementsResult(testFind3.sort("$._id").execute(), 2, 5);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind3, 0, -1);
        assertTestPreparedStatementsResult(testFind4.sort("$._id").execute(), 2, 5);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind4, 0, -1);

        assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);

        // C. Set binds reuse statement: 2nd execute -> prepare + execute.
        assertTestPreparedStatementsResult(testFind1.execute(), 1, 8);
        assertPreparedStatementsCountsAndId(testSession, 1, testFind1, 1, 1);
        assertTestPreparedStatementsResult(testFind2.bind("n", 3).execute(), 3, 8);
        assertPreparedStatementsCountsAndId(testSession, 2, testFind2, 2, 1);
        assertTestPreparedStatementsResult(testFind3.bind("n", 3).execute(), 3, 6);
        assertPreparedStatementsCountsAndId(testSession, 3, testFind3, 3, 1);
        assertTestPreparedStatementsResult(testFind4.bind("m", 6).execute(), 2, 6);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind4, 4, 1);

        assertPreparedStatementsStatusCounts(testSession, 4, 4, 0);

        // D. Set binds reuse statement: 3rd execute -> execute.
        assertTestPreparedStatementsResult(testFind1.execute(), 1, 8);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind1, 1, 2);
        assertTestPreparedStatementsResult(testFind2.bind("n", 4).execute(), 4, 8);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind2, 2, 2);
        assertTestPreparedStatementsResult(testFind3.bind("n", 4).execute(), 4, 7);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind3, 3, 2);
        assertTestPreparedStatementsResult(testFind4.bind("n", 3).bind("m", 7).execute(), 3, 7);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind4, 4, 2);

        assertPreparedStatementsStatusCounts(testSession, 4, 8, 0);

        // E. Set sort deallocates and resets execution count: 1st execute -> deallocate + non-prepared.
        assertTestPreparedStatementsResult(testFind1.sort("$._id").execute(), 1, 8);
        assertPreparedStatementsCountsAndId(testSession, 3, testFind1, 0, -1);
        assertTestPreparedStatementsResult(testFind2.sort("$._id").bind("n", 4).execute(), 4, 8);
        assertPreparedStatementsCountsAndId(testSession, 2, testFind2, 0, -1);
        assertTestPreparedStatementsResult(testFind3.sort("$._id").bind("n", 4).execute(), 4, 7);
        assertPreparedStatementsCountsAndId(testSession, 1, testFind3, 0, -1);
        assertTestPreparedStatementsResult(testFind4.sort("$._id").bind("n", 3).bind("m", 7).execute(), 3, 7);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind4, 0, -1);

        assertPreparedStatementsStatusCounts(testSession, 4, 8, 4);

        // F. No Changes: 2nd execute -> prepare + execute.
        assertTestPreparedStatementsResult(testFind1.execute(), 1, 8);
        assertPreparedStatementsCountsAndId(testSession, 1, testFind1, 1, 1);
        assertTestPreparedStatementsResult(testFind2.bind("n", 4).execute(), 4, 8);
        assertPreparedStatementsCountsAndId(testSession, 2, testFind2, 2, 1);
        assertTestPreparedStatementsResult(testFind3.bind("n", 4).execute(), 4, 7);
        assertPreparedStatementsCountsAndId(testSession, 3, testFind3, 3, 1);
        assertTestPreparedStatementsResult(testFind4.bind("n", 3).bind("m", 7).execute(), 3, 7);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind4, 4, 1);

        assertPreparedStatementsStatusCounts(testSession, 8, 12, 4);

        // G. Set limit for the first time deallocates and re-prepares: 1st execute -> re-prepare + execute.
        assertTestPreparedStatementsResult(testFind1.limit(2).execute(), 1, 2);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind1, 1, 1);
        assertTestPreparedStatementsResult(testFind2.limit(2).execute(), 4, 5);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind2, 2, 1);
        assertTestPreparedStatementsResult(testFind3.limit(2).execute(), 4, 5);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind3, 3, 1);
        assertTestPreparedStatementsResult(testFind4.limit(2).execute(), 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind4, 4, 1);

        assertPreparedStatementsStatusCounts(testSession, 12, 16, 8);

        // H. Set limit and offset reuse prepared statement: 2nd execute -> execute.
        assertTestPreparedStatementsResult(testFind1.limit(1).offset(1).execute(), 2, 2);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind1, 1, 2);
        assertTestPreparedStatementsResult(testFind2.limit(1).offset(1).execute(), 5, 5);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind2, 2, 2);
        assertTestPreparedStatementsResult(testFind3.limit(1).offset(1).execute(), 5, 5);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind3, 3, 2);
        assertTestPreparedStatementsResult(testFind4.limit(1).offset(1).execute(), 4, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind4, 4, 2);

        assertPreparedStatementsStatusCounts(testSession, 12, 20, 8);

        // I. Set sort deallocates and resets execution count, set limit and bind has no effect: 1st execute -> deallocate + non-prepared.
        assertTestPreparedStatementsResult(testFind1.sort("$._id").limit(2).execute(), 2, 3);
        assertPreparedStatementsCountsAndId(testSession, 3, testFind1, 0, -1);
        assertTestPreparedStatementsResult(testFind2.sort("$._id").limit(2).bind("n", 4).execute(), 5, 6);
        assertPreparedStatementsCountsAndId(testSession, 2, testFind2, 0, -1);
        assertTestPreparedStatementsResult(testFind3.sort("$._id").limit(2).bind("n", 4).execute(), 5, 6);
        assertPreparedStatementsCountsAndId(testSession, 1, testFind3, 0, -1);
        assertTestPreparedStatementsResult(testFind4.sort("$._id").limit(2).bind("n", 3).bind("m", 7).execute(), 4, 5);
        assertPreparedStatementsCountsAndId(testSession, 0, testFind4, 0, -1);

        assertPreparedStatementsStatusCounts(testSession, 12, 20, 12);

        // J. Set offset reuse statement: 2nd execute -> prepare + execute.
        assertTestPreparedStatementsResult(testFind1.offset(0).execute(), 1, 2);
        assertPreparedStatementsCountsAndId(testSession, 1, testFind1, 1, 1);
        assertTestPreparedStatementsResult(testFind2.offset(0).execute(), 4, 5);
        assertPreparedStatementsCountsAndId(testSession, 2, testFind2, 2, 1);
        assertTestPreparedStatementsResult(testFind3.offset(0).execute(), 4, 5);
        assertPreparedStatementsCountsAndId(testSession, 3, testFind3, 3, 1);
        assertTestPreparedStatementsResult(testFind4.offset(0).execute(), 3, 4);
        assertPreparedStatementsCountsAndId(testSession, 4, testFind4, 4, 1);

        assertPreparedStatementsStatusCounts(testSession, 16, 24, 12);

        testSession.close();
        assertPreparedStatementsCount(sessionThreadId, 0, 10); // Prepared statements won't live past the closing of the session.

        /*
         * Test falling back onto non-prepared statements.
         */
        testSession = sf.getSession(this.testProperties);
        int origMaxPrepStmtCount = this.session.sql("SELECT @@max_prepared_stmt_count").execute().fetchOne().getInt(0);

        try {
            // Allow preparing only one more statement.
            this.session.sql("SET GLOBAL max_prepared_stmt_count = ?").bind(getPreparedStatementsCount() + 1).execute();

            sessionThreadId = getThreadId(testSession);
            assertPreparedStatementsCount(sessionThreadId, 0, 1);
            assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);

            testCol = testSession.getDefaultSchema().getCollection(this.collectionName);

            testFind1 = testCol.find();
            testFind2 = testCol.find();

            // 1st execute -> don't prepare.
            assertTestPreparedStatementsResult(testFind1.execute(), 1, 8);
            assertPreparedStatementsCountsAndId(testSession, 0, testFind1, 0, -1);
            assertTestPreparedStatementsResult(testFind2.execute(), 1, 8);
            assertPreparedStatementsCountsAndId(testSession, 0, testFind2, 0, -1);

            assertPreparedStatementsStatusCounts(testSession, 0, 0, 0);

            // 2nd execute -> prepare + execute.
            assertTestPreparedStatementsResult(testFind1.execute(), 1, 8);
            assertPreparedStatementsCountsAndId(testSession, 1, testFind1, 1, 1);
            assertTestPreparedStatementsResult(testFind2.execute(), 1, 8); // Fails preparing, execute as non-prepared.
            assertPreparedStatementsCountsAndId(testSession, 1, testFind2, 0, -1);

            assertPreparedStatementsStatusCounts(testSession, 2, 1, 0); // Failed prepare also counts.

            // 3rd execute -> execute.
            assertTestPreparedStatementsResult(testFind1.execute(), 1, 8);
            assertPreparedStatementsCountsAndId(testSession, 1, testFind1, 1, 2);
            assertTestPreparedStatementsResult(testFind2.execute(), 1, 8); // Execute as non-prepared.
            assertPreparedStatementsCountsAndId(testSession, 1, testFind2, 0, -1);

            assertPreparedStatementsStatusCounts(testSession, 2, 2, 0);

            testSession.close();
            assertPreparedStatementsCount(sessionThreadId, 0, 10); // Prepared statements won't live past the closing of the session.
        } finally {
            this.session.sql("SET GLOBAL max_prepared_stmt_count = ?").bind(origMaxPrepStmtCount).execute();
        }
    }

    private void assertTestPreparedStatementsResult(DocResult res, int expectedMin, int expectedMax) {
        for (DbDoc d : res.fetchAll()) {
            assertEquals(expectedMin++, ((JsonNumber) d.get("ord")).getInteger().intValue());
        }
        assertEquals(expectedMax, expectedMin - 1);
    }

    @Test
    @SuppressWarnings("deprecation")
    public void testDeprecateWhere() throws Exception {
        this.collection.add("{\"_id\":\"1\", \"ord\": 1}", "{\"_id\":\"2\", \"ord\": 2}", "{\"_id\":\"3\", \"ord\": 3}", "{\"_id\":\"4\", \"ord\": 4}",
                "{\"_id\":\"5\", \"ord\": 5}", "{\"_id\":\"6\", \"ord\": 6}", "{\"_id\":\"7\", \"ord\": 7}", "{\"_id\":\"8\", \"ord\": 8}").execute();

        FindStatement testFind = this.collection.find("$.ord <= 2");

        assertTrue(testFind.getClass().getMethod("where", String.class).isAnnotationPresent(Deprecated.class));

        assertEquals(2, testFind.execute().count());
        assertEquals(4, ((FindStatementImpl) testFind).where("$.ord > 4").execute().count());
    }

    @Test
    public void testOverlaps() {
        if (!mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.17"))) {
            // TSFR6
            assertThrows(XProtocolError.class, "ERROR 5150 \\(HY000\\) Invalid operator overlaps",
                    () -> this.collection.find("[1, 2, 3] OVERLAPS $.list").execute());
            return;
        }

        this.collection.add("{\"_id\": \"01\", \"list\":[2,3], \"age\":21, \"overlaps\":true}").execute();
        this.collection.add("{\"_id\": \"02\", \"list\":[3,4], \"age\":22, \"overlaps\":false}").execute();
        this.collection.add("{\"_id\": \"03\", \"list\":[5,6], \"age\":23, \"overlaps\":false}").execute();

        // TSFR1
        DocResult res = this.collection.find("[1, 2, 3] OVERLAPS $.list").orderBy("_id").execute();
        assertEquals(2, res.count());
        DbDoc doc = res.fetchOne();
        assertEquals("01", ((JsonString) doc.get("_id")).getString());
        doc = res.fetchOne();
        assertEquals("02", ((JsonString) doc.get("_id")).getString());

        res = this.collection.find("$.list OVERLAPS [4]").orderBy("_id").execute();
        assertEquals(1, res.count());
        doc = res.fetchOne();
        assertEquals("02", ((JsonString) doc.get("_id")).getString());

        res = this.collection.find("[1, 2, 3] NOT OVERLAPS $.list").execute();
        assertEquals(1, res.count());
        doc = res.fetchOne();
        assertEquals("03", ((JsonString) doc.get("_id")).getString());

        res = this.collection.find("$.list NOT OVERLAPS [4]").orderBy("_id").execute();
        doc = res.fetchOne();
        assertEquals("01", ((JsonString) doc.get("_id")).getString());
        doc = res.fetchOne();
        assertEquals("03", ((JsonString) doc.get("_id")).getString());

        // TSFR2
        res = this.collection.find("$.list OverLaps [1, 2]").execute();
        assertEquals(1, res.count());
        doc = res.fetchOne();
        assertEquals("01", ((JsonString) doc.get("_id")).getString());

        res = this.collection.find("$.list noT overLAPS [1, 2]").orderBy("_id").execute();
        assertEquals(2, res.count());
        doc = res.fetchOne();
        assertEquals("02", ((JsonString) doc.get("_id")).getString());
        doc = res.fetchOne();
        assertEquals("03", ((JsonString) doc.get("_id")).getString());

        res = this.collection.find("[3] overlaps $.list").orderBy("_id").execute();
        assertEquals(2, res.count());
        doc = res.fetchOne();
        assertEquals("01", ((JsonString) doc.get("_id")).getString());
        doc = res.fetchOne();
        assertEquals("02", ((JsonString) doc.get("_id")).getString());

        res = this.collection.find("[3] Not Overlaps $.list").execute();
        assertEquals(1, res.count());
        doc = res.fetchOne();
        assertEquals("03", ((JsonString) doc.get("_id")).getString());

        // TSFR3
        // C/J lexer mistakes if ident equals to reserved word, thus escaped idents are required in this case.
        res = this.collection.find().fields("$.`overlaps` as `overlaps`").orderBy("_id").execute();
        assertEquals(3, res.count());
        doc = res.fetchOne();
        assertTrue(((JsonLiteral) doc.get("overlaps")).equals(JsonLiteral.TRUE));
        doc = res.fetchOne();
        assertTrue(((JsonLiteral) doc.get("overlaps")).equals(JsonLiteral.FALSE));
        doc = res.fetchOne();
        assertTrue(((JsonLiteral) doc.get("overlaps")).equals(JsonLiteral.FALSE));

        // TSFR4
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token position 0",
                () -> this.collection.find("overlaps $.list").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token position 4",
                () -> this.collection.find("$.list OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token position 0", () -> this.collection.find("overlaps").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token position 1", () -> this.collection.find("NOT overlaps").execute());
        assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token position 5",
                () -> this.collection.find("$.list NOT OVERLAPS").execute());
        assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token position 1", () -> this.collection.find("not overlaps").execute());

        // TSFR5
        res = this.collection.find("[1, 2, 3] OVERLAPS $.age").execute();
        assertEquals(0, res.count());

        res = this.collection.find("['21', '2', '3'] OVERLAPS $.age").execute();
        assertEquals(0, res.count());
    }

    @Test
    public void testCollectionFindInSanity() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        int i = 0, maxrec = 10;
        DbDoc doc = null;
        DocResult docs = null;
        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
            newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 - i)));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        /* find without Condition */
        docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        assertEquals((maxrec), i);

        /* find with single element IN which uses json_contains */
        docs = this.collection.find("'1001' in $._id").execute();
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with multiple IN which uses json_contains */
        String findCond = "";
        for (i = 1; i < maxrec; i++) {
            findCond = findCond + "'";
            findCond = findCond + String.valueOf(i + 1000) + "' not in $._id";
            if (i != maxrec - 1) {
                findCond = findCond + " and ";
            }
        }

        docs = this.collection.find(findCond).execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with single IN for string with orderBy */
        docs = this.collection.find("'Field-1-Data-2' in $.F1").orderBy("CAST($.F4 as SIGNED)").execute();
        doc = docs.next();
        assertEquals(String.valueOf(1002), (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with single IN for numeric with orderBy */
        docs = this.collection.find("10000 in $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
        doc = docs.next();
        assertEquals(String.valueOf(1000), (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with single IN for float with orderBy */
        docs = this.collection.find("20.1234 in $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
        doc = docs.next();
        assertEquals(String.valueOf(1001), (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* Testing with table */
        Table table = this.schema.getCollectionAsTable(this.collectionName);

        /* find with single IN for string */
        RowResult rows = table.select("doc->$._id as _id").where("'1001' in doc->$._id").execute();
        Row r = rows.next();
        assertEquals(r.getString("_id"), "\"1001\"");
        assertFalse(rows.hasNext());

        /* find with multiple IN in single select */
        findCond = "";
        for (i = 1; i < maxrec; i++) {
            findCond = findCond + "'";
            findCond = findCond + String.valueOf(i + 1000) + "' not in doc->$._id";
            if (i != maxrec - 1) {
                findCond = findCond + " and ";
            }
        }

        /* find with single IN for string */
        rows = table.select("doc->$._id as _id").where(findCond).execute();
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1000\"");
        assertFalse(rows.hasNext());

        /* find with single IN for float */
        rows = table.select("doc->$._id as _id").where("20.1234 in doc->$.F2").execute();
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1001\"");
        assertFalse(rows.hasNext());

        /* find with single IN for string */
        rows = table.select("doc->$._id as _id").where("'Field-1-Data-2' in doc->$.F1").execute();
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1002\"");
        assertFalse(rows.hasNext());

        /* find with single IN for numeric */
        rows = table.select("doc->$._id as _id").where("10000 in doc->$.F4").execute();
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1000\"");
        assertFalse(rows.hasNext());
    }

    @Test
    public void testCollectionFindInValidArray() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        int i = 0, j = 0, maxrec = 8, minArraySize = 3;
        DbDoc doc = null;
        DocResult docs = null;
        long l3 = 2147483647;
        double d1 = 1000.1234;

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));

            JsonArray jarray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                jarray.addValue(new JsonNumber().setValue(String.valueOf((l3 + j + i))));
            }
            newDoc2.add("ARR1", jarray);

            JsonArray karray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                karray.addValue(new JsonNumber().setValue(String.valueOf((d1 + j + i))));
            }
            newDoc2.add("ARR2", karray);
            JsonArray larray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                larray.addValue(new JsonString().setValue("St_" + i + "_" + j));
            }
            newDoc2.add("ARR3", larray);
            this.collection.add(newDoc2).execute();
            newDoc2 = null;
            jarray = null;
        }

        assertEquals((maxrec), this.collection.count());

        /* find with single IN in array */
        docs = this.collection.find("2147483647 in $.ARR1").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with array IN array */
        docs = this.collection.find("[2147483647, 2147483648, 2147483649] in $.ARR1").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with array IN array with orderBy */
        docs = this.collection.find("[2147483648, 2147483648, 2147483649] in $.ARR1").orderBy("_id").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with NULL IN array */
        docs = this.collection.find("NULL in $.ARR1").execute();
        assertFalse(docs.hasNext());
    }

    @Test
    public void testCollectionFindInValidMax() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        int i = 0, j = 0, maxFld = 100;
        DbDoc doc = null;
        DocResult docs = null;
        String json = "";
        this.session.startTransaction();

        //Max depth N records

        //Insert and Find max array
        int maxdepth = 10;//97
        json = "{\"_id\":\"1002\",\"XYZ\":1111";
        for (j = 0; j < maxFld; j++) {
            json = json + ",\"ARR" + j + "\":[";
            for (i = 0; i < maxdepth; i++) {
                json = json + i + ",[";
            }
            json = json + i;
            for (i = maxdepth - 1; i >= 0; i--) {
                json = json + "]," + i;
            }
            json = json + "]";
        }
        json = json + "}";
        this.collection.add(json).execute();

        json = "{\"_id\":\"1003\",\"XYZ\":2222";
        //maxdepth = 4;
        for (j = 0; j < maxFld; j++) {
            json = json + ",\"DATAX" + j + "\":";
            for (i = 0; i < maxdepth; i++) {
                json = json + "{\"D" + i + "\":";
            }
            json = json + maxdepth;
            for (i = maxdepth - 1; i >= 0; i--) {
                json = json + "}";
            }
        }
        json = json + "}";
        this.collection.add(json).execute();

        // Both arrays and many {}
        //maxdepth = 4;
        json = "{\"_id\":\"1001\",\"XYZ\":3333";
        for (j = 0; j < maxFld; j++) {
            json = json + ",\"ARR" + j + "\":[";
            for (i = 0; i < maxdepth; i++) {
                json = json + i + ",[";
            }
            json = json + i;
            for (i = maxdepth - 1; i >= 0; i--) {
                json = json + "]," + i;
            }
            json = json + "]";
        }

        for (j = 0; j < maxFld; j++) {
            json = json + ",\"DATAX" + j + "\":";
            for (i = 0; i < maxdepth; i++) {
                json = json + "{\"D" + i + "\":";
            }
            json = json + maxdepth;
            for (i = maxdepth - 1; i >= 0; i--) {
                json = json + "}";
            }
        }
        json = json + "}";
        this.collection.add(json).execute();

        /* find with single IN in array with max depth */
        docs = this.collection.find("10 in $.ARR0").orderBy("_id").execute();
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        json = "";
        json = json + "[";
        for (i = 0; i < maxdepth; i++) {
            json = json + i + ",[";
        }
        json = json + i;
        for (i = maxdepth - 1; i >= 0; i--) {
            json = json + "]," + i;
        }
        json = json + "] in $.ARR0";

        /* find with single IN in array's max depth - 1 element */
        docs = this.collection.find(json).orderBy("_id").execute();
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        json = "";
        for (i = 0; i < maxdepth; i++) {
            json = json + "{\"D" + i + "\":";
        }
        json = json + maxdepth;
        for (i = maxdepth - 1; i >= 0; i--) {
            json = json + "}";
        }

        json = json + " in $.DATAX0";

        /* find with single IN in Document */
        docs = this.collection.find(json).orderBy("_id").execute();
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1003", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        Table table = this.schema.getCollectionAsTable(this.collectionName);

        json = "";
        json = json + "[";
        for (i = 0; i < maxdepth; i++) {
            json = json + i + ",[";
        }
        json = json + i;
        for (i = maxdepth - 1; i >= 0; i--) {
            json = json + "]," + i;
        }
        json = json + "] in doc->$.ARR0";

        /* find with single IN in max depth Document */
        RowResult rows = table.select("doc->$._id as _id").where(json).execute();
        Row r = rows.next();
        assertEquals(r.getString("_id"), "\"1001\"");
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1002\"");
        assertFalse(rows.hasNext());

        json = "";
        for (i = 0; i < maxdepth; i++) {
            json = json + "{\"D" + i + "\":";
        }
        json = json + maxdepth;
        for (i = maxdepth - 1; i >= 0; i--) {
            json = json + "}";
        }

        json = json + " in doc->$.DATAX0";

        /* find with single IN in max depth Document */
        rows = table.select("doc->$._id as _id").where(json).execute();
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1001\"");
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1003\"");
        assertFalse(rows.hasNext());

        rows = table.select("doc->$._id as _id").where("10 in doc->$.ARR0").execute();
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1001\"");
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1002\"");
        assertFalse(rows.hasNext());
    }

    @Test
    public void testCollectionFindInValidFunction() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        DbDoc doc = null;
        DocResult docs = null;

        this.collection.add("{\"_id\": \"1001\", \"ARR\":[1,1,2], \"ARR1\":[\"name1\", \"name2\", \"name3\"]}").execute();
        this.collection.add("{\"_id\": \"1002\", \"ARR\":[1,2,3], \"ARR1\":[\"name4\", \"name5\", \"name6\"]}").execute();
        this.collection.add("{\"_id\": \"1003\", \"ARR\":[1,4,5], \"ARR1\":[\"name1\", \"name1\", \"name5\"]}").execute();

        docs = this.collection.find("[1,1,3] in $.ARR").execute();
        doc = docs.next();
        assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("[2,5] in $.ARR").execute();
        assertFalse(docs.hasNext());

        docs = this.collection.find("(1+2) in (1, 2, 3)").execute();
        doc = docs.next();

        docs = this.collection.find("concat('name', '6') in ('name1', 'name2', 'name6')").execute();
        doc = docs.next();

        Table tabNew = this.schema.getCollectionAsTable(this.collectionName);

        RowResult rows = tabNew.select("doc->$._id as _id").where("(1+2) in (1, 2, 3)").execute();
        rows.next();

        assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires operator that produce a JSON value\\.",
                () -> tabNew.select("doc->$._id as _id").where("(1+2) in [1, 2, 3]").execute());

        assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires operator that produce a JSON value\\.",
                () -> tabNew.select("doc->$._id as _id").where("(1+2) in doc->$.ARR").execute());

        assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires function that produce a JSON value\\.",
                () -> this.collection.find("concat('name', '6') in ['name1', 'name2', 'name6']").execute());

        assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires operator that produce a JSON value\\.",
                () -> this.collection.find("(1+2) in $.ARR").execute());

        assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) CONT_IN expression requires function that produce a JSON value\\.",
                () -> this.collection.find("concat('name', '6') in $.ARR1").execute());
    }

    @Test
    public void testCollectionFindInValidMix() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        int i = 0, j = 0, maxrec = 8, minArraySize = 3;
        DbDoc doc = null;
        DocResult docs = null;

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));

            JsonArray jarray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                jarray.addValue(new JsonString().setValue("Field-1-Data-" + i));
            }
            newDoc2.add("ARR1", jarray);

            this.collection.add(newDoc2).execute();
            newDoc2 = null;
            jarray = null;
        }

        assertEquals((maxrec), this.collection.count());

        /* add(DbDoc[] docs) */
        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1100)));
            newDoc2.add("ARR1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonString().setValue("10-15-201" + i));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        /* find with IN for key having mixture of value and array */
        docs = this.collection.find("\"10-15-2017\" in $.F2").execute();
        doc = docs.next();
        assertEquals("1107", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with NULL IN in key having mix of array and string */
        docs = this.collection.find("NULL in $.ARR1").execute();
        assertFalse(docs.hasNext());
    }

    @Test
    public void testCollectionFindInInvalid() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        int i = 0, j = 0, maxrec = 8, minArraySize = 3;
        DocResult docs = null;
        String json = "";

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));

            JsonArray jarray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                jarray.addValue(new JsonString().setValue("Field-1-Data-" + i));
            }
            newDoc2.add("ARR1", jarray);

            this.collection.add(newDoc2).execute();
            newDoc2 = null;
            jarray = null;
        }

        assertEquals((maxrec), this.collection.count());

        /* add(DbDoc[] docs) */
        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1100)));
            newDoc2.add("ARR1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonString().setValue("10-15-201" + i));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        json = "{\"_id\":\"1201\",\"XYZ\":2222, \"DATAX\":{\"D1\":1, \"D2\":2, \"D3\":3}}";
        this.collection.add(json).execute();

        /* find with invalid IN in document */
        try {
            docs = this.collection.find("{\"D1\":3, \"D2\":2, \"D3\":3} in $.DATAX").execute();
            assertFalse(docs.hasNext());
        } catch (XProtocolError Ex) {
            Ex.printStackTrace();
            if (Ex.getErrorCode() != MysqlErrorNumbers.ER_BAD_NULL_ERROR) {
                throw Ex;
            }
        }

        /* find with IN that does not match */
        docs = this.collection.find("\"2222\" in $.XYZ").execute();
        assertFalse(docs.hasNext());

        /* find with NULL IN */
        docs = this.collection.find("NULL in $.ARR1").execute();
        assertFalse(docs.hasNext());

        /* find with NULL IN */
        docs = this.collection.find("NULL in $.DATAX").execute();
        assertFalse(docs.hasNext());

        /* find with IN for non existant key */
        docs = this.collection.find("\"ABC\" in $.nonexistant").execute();
        assertFalse(docs.hasNext());
    }

    @Test
    public void testCollectionFindInUpdate() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        int i = 0, maxrec = 10;
        DbDoc doc = null;
        DocResult docs = null;

        /* add(DbDoc[] docs) */
        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
            newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 - i)));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        /* find without Condition */
        docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        assertEquals((maxrec), i);

        /* modify with single IN */
        Result res = this.collection.modify("'1001' in $._id").set("$.F1", "Data_New").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find("'1001' in $._id").execute();
        doc = docs.next();
        assertEquals("Data_New", (((JsonString) doc.get("F1")).getString()));
        assertFalse(docs.hasNext());

        /* find with = Condition and fetchAll() keyword */
        String findCond = "";
        for (i = 1; i < maxrec; i++) {
            findCond = findCond + "'";
            findCond = findCond + String.valueOf(i + 1000) + "' not in $._id";
            if (i != maxrec - 1) {
                findCond = findCond + " and ";
            }
        }

        /* modify with multiple IN */
        res = this.collection.modify(findCond).set("$.F1", "Data_New_1").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find(findCond).execute();
        doc = docs.next();
        assertEquals("Data_New_1", (((JsonString) doc.get("F1")).getString()));
        assertFalse(docs.hasNext());

        /* modify with single IN and sort */
        res = this.collection.modify("10000 in $.F4").set("$.F1", "Data_New_2").sort("CAST($.F4 as SIGNED)").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find("10000 in $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
        doc = docs.next();
        assertEquals("Data_New_2", (((JsonString) doc.get("F1")).getString()));
        assertFalse(docs.hasNext());

        /* modify with single IN and sort */
        res = this.collection.modify("20.1234 in $.F2").set("$.F1", "Data_New_3").sort("CAST($.F4 as SIGNED)").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find("20.1234 in $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
        doc = docs.next();
        assertEquals("Data_New_3", (((JsonString) doc.get("F1")).getString()));
        assertFalse(docs.hasNext());

        Table table = this.schema.getCollectionAsTable(this.collectionName);

        /* update with single IN */
        String toUpdate = String.format("JSON_REPLACE(doc, \"$.F1\", \"Data_New_4\")");
        res = table.update().set("doc", expr(toUpdate)).where("'1001' in doc->$._id").execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        RowResult rows = table.select("doc->$.F1 as F1").where("'1001' in doc->$._id").execute();
        Row r = rows.next();
        assertEquals(r.getString("F1"), "\"Data_New_4\"");
        assertFalse(rows.hasNext());

        findCond = "";
        for (i = 1; i < maxrec; i++) {
            findCond = findCond + "'";
            findCond = findCond + String.valueOf(i + 1000) + "' not in doc->$._id";
            if (i != maxrec - 1) {
                findCond = findCond + " and ";
            }
        }

        /* update with multiple IN */
        toUpdate = String.format("JSON_REPLACE(doc, \"$.F1\", \"Data_New_5\")");
        res = table.update().set("doc", expr(toUpdate)).where(findCond).execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        rows = table.select("doc->$.F1 as F1").where(findCond).execute();
        r = rows.next();
        assertEquals(r.getString("F1"), "\"Data_New_5\"");
        assertFalse(rows.hasNext());

        /* update with single IN for float */
        toUpdate = String.format("JSON_REPLACE(doc, \"$.F1\", \"Data_New_6\")");
        res = table.update().set("doc", expr(toUpdate)).where("20.1234 in doc->$.F2").execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        rows = table.select("doc->$.F1 as F1").where("20.1234 in doc->$.F2").execute();
        r = rows.next();
        assertEquals(r.getString("F1"), "\"Data_New_6\"");
        assertFalse(rows.hasNext());

        /* update with single IN for int */
        toUpdate = String.format("JSON_REPLACE(doc, \"$.F1\", \"Data_New_7\")");
        res = table.update().set("doc", expr(toUpdate)).where("10000 in doc->$.F4").execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        rows = table.select("doc->$.F1 as F1").where("10000 in doc->$.F4").execute();
        r = rows.next();
        assertEquals(r.getString("F1"), "\"Data_New_7\"");
        assertFalse(rows.hasNext());
    }

    @SuppressWarnings("deprecation")
    @Test
    public void testCollectionFindInDelete() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        int i = 0, maxrec = 10;
        DocResult docs = null;
        Result res = null;

        /* add(DbDoc[] docs) */
        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
            newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 + i)));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        /* find without Condition */
        docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
        i = 0;
        while (docs.hasNext()) {
            docs.next();
            i++;
        }
        assertEquals((maxrec), i);

        /* remove with single IN */
        res = this.collection.remove("'1001' in $._id").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find("'1001' in $._id").execute();
        assertFalse(docs.hasNext());

        /* remove with mulltiple IN */
        String findCond = "";
        for (i = 1; i < maxrec; i++) {
            findCond = findCond + "'";
            findCond = findCond + String.valueOf(i + 1000) + "' not in $._id";
            if (i != maxrec - 1) {
                findCond = findCond + " and ";
            }
        }

        res = this.collection.remove(findCond).execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find(findCond).execute();
        assertFalse(docs.hasNext());

        /* remove with single IN */
        res = this.collection.remove("10004 in $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find("10004 in $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
        assertFalse(docs.hasNext());

        /* remove with single IN for float */
        res = this.collection.remove("30.1234 in $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find("30.1234 in $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
        assertFalse(docs.hasNext());

        res = this.collection.remove("true").execute();

        jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
            newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 + i)));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        Table table = this.schema.getCollectionAsTable(this.collectionName);

        /* delete with single IN */
        res = table.delete().where("'1001' in doc->$._id").execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        RowResult rows = table.select("doc->$.F1 as _id").where("'1001' in doc->$._id").execute();
        assertFalse(rows.hasNext());

        /* delete with multiple IN */
        findCond = "";
        for (i = 1; i < maxrec; i++) {
            findCond = findCond + "'";
            findCond = findCond + String.valueOf(i + 1000) + "' not in doc->$._id";
            if (i != maxrec - 1) {
                findCond = findCond + " and ";
            }
        }

        res = table.delete().where(findCond).execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        rows = table.select("doc->$.F1 as _id").where(findCond).execute();
        assertFalse(rows.hasNext());

        /* delete with single IN for float */
        res = table.delete().where("30.1234 in doc->$.F2").execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        rows = table.select("doc->$.F1 as F1").where("30.1234 in doc->$.F2").execute();
        assertFalse(rows.hasNext());

        /* delete with single IN for int */
        res = table.delete().where("10004 in doc->$.F4").execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        rows = table.select("doc->$.F1 as F1").where("10004 in doc->$.F4").execute();
        assertFalse(rows.hasNext());
    }

    @Test
    public void testCollectionFindOverlapsSanity() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        int i = 0, maxrec = 10;
        DbDoc doc = null;
        DocResult docs = null;

        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
            newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 - i)));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        /* find without Condition */
        docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        assertEquals((maxrec), i);

        /* find with single element OVERLAPS which uses json_overlaps */
        docs = this.collection.find("'1001' overlaps $._id").execute();
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with multiple OVERLAPS which uses json_overlaps */
        String findCond = "";
        for (i = 1; i < maxrec; i++) {
            findCond = findCond + "'";
            findCond = findCond + String.valueOf(i + 1000) + "' not overlaps $._id";
            if (i != maxrec - 1) {
                findCond = findCond + " and ";
            }
        }

        docs = this.collection.find(findCond).execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with single OVERLAPS for string with orderBy */
        docs = this.collection.find("'Field-1-Data-2' overlaps $.F1").orderBy("CAST($.F4 as SIGNED)").execute();
        doc = docs.next();
        assertEquals(String.valueOf(1002), (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with single OVERLAPS for numeric with orderBy */
        docs = this.collection.find("10000 overlaps $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
        doc = docs.next();
        assertEquals(String.valueOf(1000), (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with single OVERLAPS for float with orderBy */
        docs = this.collection.find("20.1234 overlaps $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
        doc = docs.next();
        assertEquals(String.valueOf(1001), (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* Testing with table */
        Table table = this.schema.getCollectionAsTable(this.collectionName);

        /* find with single OVERLAPS for string */
        RowResult rows = table.select("doc->$._id as _id").where("'1001' overlaps doc->$._id").execute();
        Row r = rows.next();
        assertEquals(r.getString("_id"), "\"1001\"");
        assertFalse(rows.hasNext());

        /* find with multiple OVERLAPS in single select */
        findCond = "";
        for (i = 1; i < maxrec; i++) {
            findCond = findCond + "'";
            findCond = findCond + String.valueOf(i + 1000) + "' not overlaps doc->$._id";
            if (i != maxrec - 1) {
                findCond = findCond + " and ";
            }
        }

        /* find with single OVERLAPS for string */
        rows = table.select("doc->$._id as _id").where(findCond).execute();
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1000\"");
        assertFalse(rows.hasNext());

        /* find with single OVERLAPS for float */
        rows = table.select("doc->$._id as _id").where("20.1234 overlaps doc->$.F2").execute();
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1001\"");
        assertFalse(rows.hasNext());

        /* find with single OVERLAPS for string */
        rows = table.select("doc->$._id as _id").where("'Field-1-Data-2' overlaps doc->$.F1").execute();
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1002\"");
        assertFalse(rows.hasNext());

        /* find with single OVERLAPS for numeric */
        rows = table.select("doc->$._id as _id").where("10000 overlaps doc->$.F4").execute();
        r = rows.next();
        assertEquals(r.getString("_id"), "\"1000\"");
        assertFalse(rows.hasNext());
    }

    @Test
    public void testCollectionFindOverlaps() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        try {
            int i = 0, j = 0, maxrec = 8, minArraySize = 3;
            DbDoc doc = null;
            DocResult docs = null;
            long l3 = 2147483647;
            double d1 = 1000.1234;

            for (i = 0; i < maxrec; i++) {
                DbDoc newDoc2 = new DbDocImpl();
                newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
                newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));

                JsonArray jarray = new JsonArray();
                for (j = 0; j < (minArraySize + i); j++) {
                    jarray.addValue(new JsonNumber().setValue(String.valueOf((l3 + j + i))));
                }
                newDoc2.add("ARR1", jarray);

                JsonArray karray = new JsonArray();
                for (j = 0; j < (minArraySize + i); j++) {
                    karray.addValue(new JsonNumber().setValue(String.valueOf((d1 + j + i))));
                }
                newDoc2.add("ARR2", karray);
                JsonArray larray = new JsonArray();
                for (j = 0; j < (minArraySize + i); j++) {
                    larray.addValue(new JsonString().setValue("St_" + i + "_" + j));
                }
                newDoc2.add("ARR3", larray);
                this.collection.add(newDoc2).execute();
                newDoc2 = null;
                jarray = null;
            }

            assertEquals((maxrec), this.collection.count());

            /* find with single OVERLAPS in array */
            docs = this.collection.find("2147483647 overlaps $.ARR1").execute();
            doc = docs.next();
            assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
            assertFalse(docs.hasNext());

            Table table = this.schema.getCollectionAsTable(this.collectionName);
            RowResult rows = table.select("doc->$._id as _id").where("2147483647 overlaps $.ARR1").execute();
            Row r = rows.next();
            assertEquals("\"1000\"", r.getString("_id"));
            assertFalse(rows.hasNext());

            /* find with array OVERLAPS array */
            docs = this.collection.find("[2147483647, 2147483648, 2147483649] overlaps $.ARR1").execute();
            doc = docs.next();
            assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
            doc = docs.next();
            assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
            doc = docs.next();
            assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
            assertFalse(docs.hasNext());

            rows = table.select("doc->$._id as _id").where("[2147483647, 2147483648, 2147483649] overlaps $.ARR1").execute();
            r = rows.next();
            assertEquals("\"1000\"", r.getString("_id"));
            r = rows.next();
            assertEquals("\"1001\"", r.getString("_id"));
            r = rows.next();
            assertEquals("\"1002\"", r.getString("_id"));
            assertFalse(rows.hasNext());

            /* find with array OVERLAPS array with orderBy */
            docs = this.collection.find("[2147483648, 2147483648, 2147483649] overlaps $.ARR1").orderBy("_id").execute();
            doc = docs.next();
            assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
            doc = docs.next();
            assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
            doc = docs.next();
            assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
            assertFalse(docs.hasNext());

            /* */
            docs = this.collection.find("[!false && true] OVERLAPS [true]").execute();
            assertEquals(maxrec, docs.count());

            /* Overlaps with NULL */
            docs = this.collection.find("NULL overlaps $.ARR1").execute();
            assertFalse(docs.hasNext());

            rows = table.select("doc->$._id as _id").where("NULL overlaps $.ARR1").execute();
            assertFalse(rows.hasNext());

            docs = this.collection.find("$.ARR1 overlaps null").execute();
            assertFalse(docs.hasNext());

            rows = table.select("doc->$._id as _id").where("$.ARR1 overlaps NULL").execute();
            assertFalse(rows.hasNext());

            /* Not Overlaps with NULL */
            docs = this.collection.find("NULL not overlaps $.ARR1").execute();
            assertTrue(docs.hasNext());
            assertEquals(maxrec, docs.count());

            rows = table.select("doc->$._id as _id").where("NULL not overlaps $.ARR1").execute();
            assertTrue(rows.hasNext());
            assertEquals(maxrec, docs.count());

            docs = this.collection.find("$.ARR1 not overlaps null").execute();
            assertTrue(docs.hasNext());
            assertEquals(maxrec, docs.count());

            rows = table.select("doc->$._id as _id").where("$.ARR1 not overlaps null").execute();
            assertTrue(rows.hasNext());
            assertEquals(maxrec, docs.count());

            /* Test OVERLAPS/NOT OVERLAPS with empty array - Expected to pass, though the array is empty but still valid */
            docs = this.collection.find("[] Overlaps $.ARR1").execute(); //checking the case insensitivity as well
            assertFalse(docs.hasNext());

            rows = table.select().where("[] ovErlaps $.ARR1").execute();
            assertFalse(rows.hasNext());

            docs = this.collection.find("$.ARR1 overlapS []").execute(); //checking the case insensitivity as well
            assertFalse(docs.hasNext());

            rows = table.select().where("$.ARR1 ovErlaps []").execute();
            assertFalse(rows.hasNext());

            docs = this.collection.find("[] not overlaps $.ARR1").execute();
            assertTrue(docs.hasNext());
            assertEquals(maxrec, docs.count());

            rows = table.select().where("[] not overlaps $.ARR1").execute();
            assertTrue(rows.hasNext());
            assertEquals(maxrec, docs.count());

            docs = this.collection.find("$.ARR1 not oveRlaps []").execute(); //checking the case insensitivity as well
            assertTrue(docs.hasNext());
            assertEquals(maxrec, docs.count());

            rows = table.select().where("$.ARR1 not overlaps []").execute();
            assertTrue(rows.hasNext());
            assertEquals(maxrec, docs.count());

            /* When the right number of operands are not provided - error should be thrown */
            assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token position 0",
                    () -> this.collection.find("overlaps $.ARR1").execute());
            assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token position 4",
                    () -> this.collection.find("$.ARR1 OVERLAPS").execute());
            assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token position 0", () -> this.collection.find("OVERLAPS").execute());
            assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token position 1",
                    () -> this.collection.find("not overlaps $.ARR1").execute());
            assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token position 5",
                    () -> this.collection.find("$.ARR1 NOT OVERLAPS").execute());
            assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token position 1",
                    () -> this.collection.find("not OVERLAPS").execute());

            final Table table1 = this.schema.getCollectionAsTable(this.collectionName);

            assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token position 0",
                    () -> table1.select().where("overlaps $.ARR1").execute());
            assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token position 4",
                    () -> table1.select().where("$.ARR1 OVERLAPS").execute());
            assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token position 0", () -> table1.select().where("OVERLAPS").execute());
            assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token position 1",
                    () -> table1.select().where("not overlaps $.ARR1").execute());
            assertThrows(WrongArgumentException.class, "No more tokens when expecting one at token position 5",
                    () -> table1.select().where("$.ARR1 NOT OVERLAPS").execute());
            assertThrows(WrongArgumentException.class, "Cannot find atomic expression at token position 1",
                    () -> table1.select().where("not OVERLAPS").execute());

            /* invalid criteria, e.g. .find("[1, 2, 3] OVERLAPS $.age") . where $.age is atomic value */
            dropCollection("coll2");
            Collection coll2 = this.schema.createCollection("coll2", true);
            coll2.add("{ \"_id\": \"1\", \"name\": \"nonjson\", \"age\": \"50\",\"arrayField\":[1,[7]]}").execute();
            //The below command should give exception, but X-plugin doesn't return any error
            docs = coll2.find("[1,2,3] overlaps $.age").execute();
            assertEquals(0, docs.count());
            docs = coll2.find("arrayField OVERLAPS [7]").execute();
            assertEquals(0, docs.count());

            docs = coll2.find("arrayField[1] OVERLAPS [7]").execute();
            assertEquals(1, docs.count());

            table = this.schema.getCollectionAsTable("coll2");
            rows = table.select().where("[1,2,3] overlaps $.age").execute();
            assertEquals(0, rows.count());

            /* Test with empty spaces */
            dropCollection("coll3");
            Collection coll3 = this.schema.createCollection("coll3", true);
            coll3.add("{ \"_id\":1, \"name\": \"Record1\",\"list\":[\"\"], \"age\":15, \"intList\":[1,2,3] }").execute();//List contains an array without any space
            coll3.add("{ \"_id\":2, \"name\": \"overlaps\",\"list\":[\" \"],\"age\":24}").execute();//List contains an array with space
            coll3.add("{ \"_id\":3, \"overlaps\": \"overlaps\",\"age\":30}").execute();
            docs = coll3.find("[''] OVERLAPS $.list").execute();
            assertEquals(1, docs.count());
            assertEquals(new Integer(1), ((JsonNumber) docs.next().get("_id")).getInteger());

            table = this.schema.getCollectionAsTable("coll3");
            rows = table.select("doc->$._id as _id").where("[''] overlaps $.list").execute();
            r = rows.next();
            assertEquals("1", r.getString("_id"));

            docs = coll3.find("[' '] OVERLAPS $.list").execute();
            assertEquals(1, docs.count());
            assertEquals(new Integer(2), ((JsonNumber) docs.next().get("_id")).getInteger());

            rows = table.select("doc->$._id as _id").where("[' '] overlaps $.list").execute();
            r = rows.next();
            assertEquals("2", r.getString("_id"));

            docs = coll3.find("'overlaps' OVERLAPS $.name").execute();
            assertEquals(1, docs.count());
            assertEquals(new Integer(2), ((JsonNumber) docs.next().get("_id")).getInteger());

            rows = table.select("doc->$._id as _id").where("'overlaps' overlaps $.name").execute();
            r = rows.next();
            assertEquals(1, docs.count());
            assertEquals("2", r.getString("_id"));

            docs = coll3.find("[3] OVERLAPS $.intList").execute();
            assertEquals(1, docs.count());

            rows = table.select().where("[3] overlaps $.intList").execute();
            assertEquals(1, rows.count());

            /* Escape the keyword, to use it as identifier */
            docs = coll3.find("`overlaps` OVERLAPS $.`overlaps`").execute();
            assertEquals(1, docs.count());

            rows = table.select().where("'overlaps' overlaps $.`overlaps`").execute();
            assertEquals(1, rows.count());

            docs = coll3.find("$.`overlaps` OVERLAPS `overlaps`").execute();
            assertEquals(1, docs.count());

            rows = table.select().where("$.`overlaps` overlaps 'overlaps'").execute();
            assertEquals(1, rows.count());

            dropCollection("coll4");
            Collection coll4 = this.schema.createCollection("coll4", true);
            coll4.add("{\"overlaps\":{\"one\":1, \"two\":2, \"three\":3},\"list\":{\"one\":1, \"two\":2, \"three\":3},\"name\":\"one\"}").execute();
            coll4.add("{\"overlaps\":{\"one\":1, \"two\":2, \"three\":3},\"list\":{\"four\":4, \"five\":5, \"six\":6},\"name\":\"two\"}").execute();
            coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"two\":2, \"four\":4, \"six\":6},\"name\":\"three\"}").execute();
            coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"three\":3, \"six\":9, \"nine\":9},\"name\":\"four\"}").execute();
            coll4.add("{\"overlaps\":{\"one\":1, \"three\":3, \"five\":5},\"list\":{\"three\":6, \"six\":12, \"nine\":18},\"name\":\"five\"}").execute();
            coll4.add("{\"overlaps\":{\"one\":[1,2,3]}, \"list\":{\"one\":[3,4,5]}, \"name\":\"six\"}").execute();
            coll4.add("{\"overlaps\":{\"one\":[1,2,3]}, \"list\":{\"one\":[1,2,3]}, \"name\":\"seven\"}").execute();

            docs = coll4.find("`overlaps` OVERLAPS `list`").execute();
            assertEquals(3, docs.count());
            doc = docs.fetchOne();
            assertEquals("one", (((JsonString) doc.get("name")).getString()));
            doc = docs.fetchOne();
            assertEquals("four", (((JsonString) doc.get("name")).getString()));
            doc = docs.fetchOne();
            assertEquals("seven", (((JsonString) doc.get("name")).getString()));

            table = this.schema.getCollectionAsTable("coll4");
            rows = table.select("doc->$.name as name").where("$.`overlaps` OVERLAPS $.`list`").execute();
            assertEquals(3, rows.count());
            r = rows.next();
            assertEquals("\"one\"", r.getString("name"));
        } finally {
            dropCollection("coll4");
            dropCollection("coll3");
            dropCollection("coll2");
        }
    }

    @Test
    public void testCollectionFindOverlapsWithExpr() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        DbDoc doc = null;
        DocResult docs = null;

        this.collection.add("{\"_id\": \"1001\", \"ARR\":[1,1,2], \"ARR1\":[\"name1\", \"name2\", \"name3\"]}").execute();
        this.collection.add("{\"_id\": \"1002\", \"ARR\":[1,2,3], \"ARR1\":[\"name4\", \"name5\", \"name6\"]}").execute();
        this.collection.add("{\"_id\": \"1003\", \"ARR\":[1,4,5], \"ARR1\":[\"name1\", \"name1\", \"name5\"]}").execute();

        docs = this.collection.find("[1,1,3] overlaps $.ARR").execute();
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1003", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("[2,5] overlaps $.ARR").execute();
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
        doc = docs.next();
        assertEquals("1003", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("1 overlaps [1, 2, 3]").execute();
        assertEquals(3, docs.count());

        docs = this.collection.find("'name6' overlaps ['name1', 'name2', 'name6']").execute();
        assertEquals(3, docs.count());

        docs = this.collection.find("cast((1+6) AS JSON) OVERLAPS [2,3,7]").execute();
        assertEquals(3, docs.count());

        docs = this.collection.find("[(1+2)] overlaps [1, 2, 3]").execute();
        assertEquals(3, docs.count());

        docs = this.collection.find("[concat('name', '6')] overlaps ['name1', 'name2', 'name6']").execute();
        assertEquals(3, docs.count());

        docs = this.collection.find("[CURDATE()] overlaps ['2019-16-05','2018-16-05']").execute();
        assertEquals(0, docs.count());

        docs = this.collection.find("true overlaps [2]").execute();
        assertEquals(0, docs.count());

        assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) OVERLAPS expression requires operator that produce a JSON value\\.",
                () -> this.collection.find("[1,2,3] overlaps $.ARR overlaps [2]").execute());

        assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) OVERLAPS expression requires operator that produce a JSON value\\.",
                () -> this.collection.find("(1+2) overlaps [1, 2, 3]").execute());

        assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) OVERLAPS expression requires function that produce a JSON value\\.",
                () -> this.collection.find("concat('name', '6') overlaps ['name1', 'name2', 'name6']").execute());

        assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) OVERLAPS expression requires operator that produce a JSON value\\.",
                () -> this.collection.find("(1+2) overlaps $.ARR").execute());

        assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) OVERLAPS expression requires function that produce a JSON value\\.",
                () -> this.collection.find("concat('name', '6') overlaps $.ARR1").execute());

        Table tabNew = this.schema.getCollectionAsTable(this.collectionName);
        RowResult rows = tabNew.select("doc->$._id as _id").where("[(1+2)] overlaps [1, 2, 3]").execute();
        rows.next();
        assertEquals(3, rows.count());

        rows = tabNew.select("doc->$._id as _id").where("[concat('name', '6')] overlaps ['name1', 'name2', 'name6']").execute();
        rows.next();
        assertEquals(3, rows.count());

        assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) OVERLAPS expression requires function that produce a JSON value\\.",
                () -> tabNew.select("doc->$._id as _id").where("expr(1+2) overlaps [1, 2, 3]").execute());

        assertThrows(XProtocolError.class, "ERROR 5154 \\(HY000\\) OVERLAPS expression requires operator that produce a JSON value\\.",
                () -> tabNew.select("doc->$._id as _id").where("(1+2) overlaps doc->$.ARR").execute());
    }

    @Test
    public void testCollectionFindOverlapsValidMix() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        int i = 0, j = 0, maxrec = 8, minArraySize = 3;
        DbDoc doc = null;
        DocResult docs = null;

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));

            JsonArray jarray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                jarray.addValue(new JsonString().setValue("Field-1-Data-" + i));
            }
            newDoc2.add("ARR1", jarray);

            this.collection.add(newDoc2).execute();
            newDoc2 = null;
            jarray = null;
        }

        assertEquals((maxrec), this.collection.count());

        /* add(DbDoc[] docs) */
        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1100)));
            newDoc2.add("ARR1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonString().setValue("10-15-201" + i));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        /* find with OVERLAPS for key having mixture of value and array */
        docs = this.collection.find("\"10-15-2017\" OVERLAPS $.F2").execute();
        doc = docs.next();
        assertEquals("1107", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("JSON_TYPE($.ARR1) = 'ARRAY' AND \"Field-1-Data-0\" OVERLAPS $.ARR1").execute();
        doc = docs.next();
        assertEquals("1000", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with NULL OVERLAPS in key having mix of array and string */
        docs = this.collection.find("NULL OVERLAPS $.ARR1").execute();
        assertFalse(docs.hasNext());
    }

    @Test
    public void testCollModifyTabUpdateWithOverlaps() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        int i = 0, maxrec = 10;
        DbDoc doc = null;
        DocResult docs = null;
        Result res = null;

        /* add(DbDoc[] docs) */
        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
            newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 - i)));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        /* find without Condition */
        docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
        }
        assertEquals((maxrec), i);

        /* modify with single OVERLAPS */
        res = this.collection.modify("'1001' overlaps $._id").set("$.F1", "Data_New").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find("'1001' overlaps $._id").execute();
        doc = docs.next();
        assertEquals("Data_New", (((JsonString) doc.get("F1")).getString()));
        assertFalse(docs.hasNext());

        /* find with = Condition and fetchAll() keyword */
        String findCond = "";
        for (i = 1; i < maxrec; i++) {
            findCond = findCond + "'";
            findCond = findCond + String.valueOf(i + 1000) + "' not overlaps $._id";
            if (i != maxrec - 1) {
                findCond = findCond + " and ";
            }
        }

        /* modify with multiple overlaps */
        res = this.collection.modify(findCond).set("$.F1", "Data_New_1").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find(findCond).execute();
        doc = docs.next();
        assertEquals("Data_New_1", (((JsonString) doc.get("F1")).getString()));
        assertFalse(docs.hasNext());

        /* modify with single overlaps and sort */
        res = this.collection.modify("10000 overlaps $.F4").set("$.F1", "Data_New_2").sort("CAST($.F4 as SIGNED)").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find("10000 overlaps $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
        doc = docs.next();
        assertEquals("Data_New_2", (((JsonString) doc.get("F1")).getString()));
        assertFalse(docs.hasNext());

        /* modify with single overlaps and sort */
        res = this.collection.modify("20.1234 overlaps $.F2").set("$.F1", "Data_New_3").sort("CAST($.F4 as SIGNED)").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find("20.1234 overlaps $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
        doc = docs.next();
        assertEquals("Data_New_3", (((JsonString) doc.get("F1")).getString()));
        assertFalse(docs.hasNext());

        Table table = this.schema.getCollectionAsTable(this.collectionName);

        /* update with single OVERLAPS */
        String toUpdate = String.format("JSON_REPLACE(doc, \"$.F1\", \"Data_New_4\")");
        res = table.update().set("doc", expr(toUpdate)).where("'1001' overlaps doc->$._id").execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        RowResult rows = table.select("doc->$.F1 as F1").where("'1001' overlaps doc->$._id").execute();
        Row r = rows.next();
        assertEquals(r.getString("F1"), "\"Data_New_4\"");
        assertFalse(rows.hasNext());

        findCond = "";
        for (i = 1; i < maxrec; i++) {
            findCond = findCond + "'";
            findCond = findCond + String.valueOf(i + 1000) + "' not overlaps doc->$._id";
            if (i != maxrec - 1) {
                findCond = findCond + " and ";
            }
        }

        /* update with multiple OVERLAPS */
        toUpdate = String.format("JSON_REPLACE(doc, \"$.F1\", \"Data_New_5\")");
        res = table.update().set("doc", expr(toUpdate)).where(findCond).execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        rows = table.select("doc->$.F1 as F1").where(findCond).execute();
        r = rows.next();
        assertEquals(r.getString("F1"), "\"Data_New_5\"");
        assertFalse(rows.hasNext());

        /* update with single OVERLAPS for float */
        toUpdate = String.format("JSON_REPLACE(doc, \"$.F1\", \"Data_New_6\")");
        res = table.update().set("doc", expr(toUpdate)).where("20.1234 overlaps doc->$.F2").execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        rows = table.select("doc->$.F1 as F1").where("20.1234 overlaps doc->$.F2").execute();
        r = rows.next();
        assertEquals(r.getString("F1"), "\"Data_New_6\"");
        assertFalse(rows.hasNext());

        /* update with single OVERLAPS for int */
        toUpdate = String.format("JSON_REPLACE(doc, \"$.F1\", \"Data_New_7\")");
        res = table.update().set("doc", expr(toUpdate)).where("10000 overlaps doc->$.F4").execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        rows = table.select("doc->$.F1 as F1").where("10000 overlaps doc->$.F4").execute();
        r = rows.next();
        assertEquals(r.getString("F1"), "\"Data_New_7\"");
        assertFalse(rows.hasNext());
    }

    @SuppressWarnings("deprecation")
    @Test
    public void testCollRemoveTabDeleteWithOverlaps() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        int i = 0, maxrec = 10;
        DocResult docs = null;
        Result res = null;

        /* add(DbDoc[] docs) */
        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
            newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 + i)));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        /* find without Condition */
        docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
        i = 0;
        while (docs.hasNext()) {
            docs.next();
            i++;
        }
        assertEquals((maxrec), i);

        /* remove with single OVERLAPS */
        res = this.collection.remove("'1001' overlaps $._id").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find("'1001' overlaps $._id").execute();
        assertFalse(docs.hasNext());

        /* remove with mulltiple OVERLAPS */
        String findCond = "";
        for (i = 1; i < maxrec; i++) {
            findCond = findCond + "'";
            findCond = findCond + String.valueOf(i + 1000) + "' not overlaps $._id";
            if (i != maxrec - 1) {
                findCond = findCond + " and ";
            }
        }

        res = this.collection.remove(findCond).execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find(findCond).execute();
        assertFalse(docs.hasNext());

        /* remove with single OVERLAPS */
        res = this.collection.remove("10004 overlaps $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find("10004 overlaps $.F4").orderBy("CAST($.F4 as SIGNED)").execute();
        assertFalse(docs.hasNext());

        /* remove with single OVERLAPS for float */
        res = this.collection.remove("30.1234 overlaps $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
        assertEquals(1, res.getAffectedItemsCount());
        docs = this.collection.find("30.1234 overlaps $.F2").orderBy("CAST($.F4 as SIGNED)").execute();
        assertFalse(docs.hasNext());

        res = this.collection.remove("true").execute();

        jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1) + 0.1234)));
            newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 + i)));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        Table table = this.schema.getCollectionAsTable(this.collectionName);

        /* delete with single OVERLAPS */
        res = table.delete().where("'1001' overlaps doc->$._id").execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        RowResult rows = table.select("doc->$.F1 as _id").where("'1001' overlaps doc->$._id").execute();
        assertFalse(rows.hasNext());

        /* delete with multiple OVERLAPS */
        findCond = "";
        for (i = 1; i < maxrec; i++) {
            findCond = findCond + "'";
            findCond = findCond + String.valueOf(i + 1000) + "' not overlaps doc->$._id";
            if (i != maxrec - 1) {
                findCond = findCond + " and ";
            }
        }

        res = table.delete().where(findCond).execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        rows = table.select("doc->$.F1 as _id").where(findCond).execute();
        assertFalse(rows.hasNext());

        /* delete with single OVERLAPS for float */
        res = table.delete().where("30.1234 overlaps doc->$.F2").execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        rows = table.select("doc->$.F1 as F1").where("30.1234 overlaps doc->$.F2").execute();
        assertFalse(rows.hasNext());

        /* delete with single OVERLAPS for int */
        res = table.delete().where("10004 overlaps doc->$.F4").execute();
        assertEquals(res.getAffectedItemsCount(), 1);
        rows = table.select("doc->$.F1 as F1").where("10004 overlaps doc->$.F4").execute();
        assertFalse(rows.hasNext());
    }

    /* 64k length key */
    @Test
    public void testCollectionFindStress_002() throws Exception {
        int i = 0, maxrec = 5;
        int maxLen = 1024 * 64 - 1;
        SqlResult res1 = null;
        Session tmpSess = null;
        Row r = null;
        int defXPackLen = 0;
        int defPackLen = 0;
        try {
            tmpSess = new SessionFactory().getSession(this.baseUrl);
            res1 = tmpSess.sql("show variables like 'mysqlx_max_allowed_packet'").execute();
            r = res1.next();
            defXPackLen = Integer.parseInt(r.getString("Value"));
            res1 = tmpSess.sql("show variables like 'max_allowed_packet'").execute();
            r = res1.next();
            defPackLen = Integer.parseInt(r.getString("Value"));

            tmpSess.sql("set Global mysqlx_max_allowed_packet=128*1024*1024 ").execute();
            tmpSess.sql("set Global max_allowed_packet=128*1024*1024 ").execute();

            String s1 = "";

            /* max+1 key length --> Expect error */
            s1 = buildString(maxLen + 1, 'q');
            DbDoc[] jsonlist = new DbDocImpl[maxrec];
            for (i = 0; i < maxrec; i++) {
                DbDoc newDoc2 = new DbDocImpl();
                newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1 + 1000)));
                newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));
                newDoc2.add(s1, new JsonString().setValue("Data_1" + i));
                jsonlist[i] = newDoc2;
                newDoc2 = null;
            }

            assertThrows(XProtocolError.class, "ERROR 3151 \\(22032\\) The JSON object contains a key name that is too long\\.",
                    () -> this.collection.add(jsonlist).execute());

            /* With Max Keysize */
            s1 = buildString(maxLen - 1, 'q');
            for (i = 0; i < maxrec; i++) {
                DbDoc newDoc2 = new DbDocImpl();
                newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1 + 1000)));
                newDoc2.add(s1 + "1", new JsonNumber().setValue(String.valueOf(i + 1)));
                newDoc2.add(s1 + "2", new JsonString().setValue("Data_1" + i));
                //jsonlist[i]=newDoc2;
                this.collection.add(newDoc2).execute();
                newDoc2 = null;
            }
            //coll.add(jsonlist).execute();

            DocResult docs0 = this.collection.find("$._id= '1001'").fields("$._id as _id, $." + s1 + "1 as " + s1 + "X, $." + s1 + "2 as " + s1 + "Y")
                    .execute();
            DbDoc doc0 = docs0.next();
            assertEquals(String.valueOf(1 + 1000), (((JsonString) doc0.get("_id")).getString()));

        } finally {
            if (tmpSess != null) {
                tmpSess.sql("set Global mysqlx_max_allowed_packet=" + defXPackLen).execute();
                tmpSess.sql("set Global max_allowed_packet=" + defPackLen).execute();
                tmpSess.close();
            }
        }
    }

    /* Large Data */
    @Test
    //@Ignore("Wait for 1M Data issue Fix in Plugin")
    public void testCollectionFindStress_003() throws Exception {
        int i = 0, maxrec = 5;
        int maxLen = 1024 * 1024 + 4;
        SqlResult res1 = null;
        Session tmpSess = null;
        Row r = null;
        int defPackLen = 0;
        int defXPackLen = 0;

        try {
            tmpSess = new SessionFactory().getSession(this.baseUrl);
            res1 = tmpSess.sql("show variables like 'mysqlx_max_allowed_packet'").execute();
            r = res1.next();
            defXPackLen = Integer.parseInt(r.getString("Value"));

            res1 = tmpSess.sql("show variables like 'max_allowed_packet'").execute();
            r = res1.next();
            defPackLen = Integer.parseInt(r.getString("Value"));

            tmpSess.sql("set Global mysqlx_max_allowed_packet=128*1024*1024 ").execute();
            tmpSess.sql("set Global max_allowed_packet=128*1024*1024 ").execute();
            ((SessionImpl) this.session).getSession().getProtocol().setMaxAllowedPacket(128 * 1024 * 1024);

            String s1 = "";

            /* maxLen Data length */
            s1 = buildString(maxLen + 1, 'q');
            for (i = 0; i < maxrec; i++) {
                DbDoc newDoc2 = new DbDocImpl();
                newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1 + 1000)));
                newDoc2.add("F1", new JsonString().setValue(s1 + i));
                newDoc2.add("F2", new JsonString().setValue(s1 + i));
                this.collection.add(newDoc2).execute();
                newDoc2 = null;
            }

            DocResult docs0 = this.collection.find("$._id= '1001'").fields("$._id as _id, $.F1 as f1, $.F2 as f2").execute();
            DbDoc doc0 = docs0.next();
            assertEquals(String.valueOf(1 + 1000), (((JsonString) doc0.get("_id")).getString()));
        } finally {
            if (tmpSess != null) {
                tmpSess.sql("set Global mysqlx_max_allowed_packet=" + defXPackLen).execute();
                tmpSess.sql("set Global max_allowed_packet=" + defPackLen).execute();
                tmpSess.close();
            }
        }
    }

    /*
     * Many Keys
     * Issue : Hangs when maxKey > 7K
     */
    @Test
    public void testCollectionFindStress_004() throws Exception {
        int i = 0, j = 0, maxrec = 5;
        int maxKey = 1024 * 8;
        String key, key_sub = "key_", query;
        String data, data_sub = "data_";
        DocResult docs = null;
        DbDoc doc = null;
        String json = "";
        for (i = 0; i < maxrec; i++) {
            StringBuilder b = new StringBuilder("{'_id':'" + (1000 + i) + "'");
            for (j = 0; j < maxKey; j++) {
                key = key_sub + j;
                data = data_sub + i + "_" + j;
                b.append(",'").append(key).append("':'").append(data).append("'");
            }

            json = b.append("}").toString();
            json = json.replaceAll("'", "\"");
            this.collection.add(json).execute();
            b = null;
        }

        /* Inserting maxKey (key,data) pair */
        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1 + 2000)));
            for (j = 0; j < maxKey; j++) {
                key = key_sub + j;
                data = data_sub + i + "_" + j;
                newDoc2.add(key, new JsonString().setValue(data));
            }

            this.collection.add(newDoc2).execute();
            newDoc2 = null;
        }

        assertEquals((maxrec * 2), this.collection.count());
        /* Select All Keys */
        query = "$._id as _id";
        for (j = 0; j < maxKey; j++) {
            key = key_sub + j;
            query = query + ",$." + key + " as " + key;
        }
        docs = this.collection.find("$._id= '1001'").fields(query).orderBy(key_sub + (maxKey - 1)).execute();
        doc = docs.next();
        assertEquals(String.valueOf(1001), (((JsonString) doc.get("_id")).getString()));
        assertEquals(data_sub + "1_" + (maxKey - 1), (((JsonString) doc.get(key_sub + (maxKey - 1))).getString()));
    }

    /**
     * Bigint,Double, Date data CAST Operator
     * 
     * @throws Exception
     */
    @Test
    public void testCollectionFindDatatypes() throws Exception {
        int i = 0, maxrec = 10;
        DbDoc doc = null;
        DbDoc[] jsonlist = new DbDocImpl[maxrec];
        long l1 = Long.MAX_VALUE, l2 = Long.MIN_VALUE, l3 = 2147483647;
        double d1 = 100.4567;
        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(d1 + i)));
            newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(l1 - i)));
            newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(l2 + i)));
            newDoc2.add("F5", new JsonNumber().setValue(String.valueOf(l3 + i)));
            newDoc2.add("F6", new JsonString().setValue((2000 + i) + "-02-" + (i * 2 + 10)));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        /* Compare Big Int */
        DocResult docs = this.collection.find("CAST($.F3 as SIGNED) =" + l1).fields("$._id as _id, $.F3 as f3, $.F3 as f3").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals(l1, (((JsonNumber) doc.get("f3")).getBigDecimal().longValue()));
            i++;
        }
        assertEquals((1), i);

        /* Compare Big Int */
        docs = this.collection.find("CAST($.F5 as SIGNED) =" + l3 + "+" + 3).fields("$._id as _id, $.F1 as f1, $.F5 as f5").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals(l3 + 3, (((JsonNumber) doc.get("f5")).getBigDecimal().longValue()));
            i++;
        }
        assertEquals((1), i);

        /* CAST in Order By */
        docs = this.collection.find("CAST($.F5 as SIGNED) < " + l3 + "+" + 5).fields("$._id as _id, $.F1 as f1, $.F5 as f5").orderBy("CAST($.F5 as SIGNED) asc")
                .execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals(l3 + i, (((JsonNumber) doc.get("f5")).getBigDecimal().longValue()));
            i++;
        }
        assertEquals((5), i);

        docs = this.collection.find("CAST($.F4 as SIGNED) < " + l2 + "+" + 5).fields("$._id as _id, $.F1 as f1, $.F4 as f4")
                .orderBy("CAST($.F4 as SIGNED) desc").execute();
        i = 4;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals(l2 + i, (((JsonNumber) doc.get("f4")).getBigDecimal().longValue()));
            i--;
        }
        assertEquals((-1), i);

        /* Compare Double */
        docs = this.collection.find("CAST($.F2 as DECIMAL(10,4)) =" + d1).fields("$._id as _id, $.F1 as f1, $.F2 as f2").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals(d1, (((JsonNumber) doc.get("f2")).getBigDecimal().doubleValue()));
            i++;
        }
        assertEquals((1), i);
    }

    /* OPerators =,!=,<,>,<=,>= IN, NOT IN,Like , Not Like, Between, REGEXP,NOT REGEXP , interval,|,&,^,<<,>>,~ */
    @Test
    public void testCollectionFindBasic() throws Exception {
        int i = 0, maxrec = 10;
        DbDoc doc = null;
        DocResult docs = null;

        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1))));
            newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 - i)));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        /* find without Condition */
        docs = this.collection.find().fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3,$.F2/10 as tmp1,1/2 as tmp2").orderBy("$.F3").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals((long) (i + 1), (long) (((JsonNumber) doc.get("f3")).getInteger()));
            assertEquals((((JsonNumber) doc.get("f3")).getInteger()), (((JsonNumber) doc.get("tmp1")).getInteger()));
            assertEquals(new BigDecimal("0.500000000"), (((JsonNumber) doc.get("tmp2")).getBigDecimal()));

            i++;
        }
        assertEquals((maxrec), i);

        /* find with = Condition and fetchAll() keyword */
        docs = this.collection.find("$._id = '1001'").execute();
        doc = docs.next();
        assertEquals("1001", (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with order by and condition */
        docs = this.collection.find("$.F3 > 1").orderBy("CAST($.F4 as SIGNED)").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
            assertEquals(String.valueOf(1000 + maxrec - i), (((JsonString) doc.get("_id")).getString()));
            assertEquals((long) (10000 - maxrec + i), (long) (((JsonNumber) doc.get("F4")).getInteger()));

        }
        assertEquals(i, (maxrec - 1));

        /* find with order by and limit with condition */
        docs = this.collection.find("$._id > 1001").orderBy("CAST($.F4 as SIGNED)").limit(1).execute();
        doc = docs.next();
        assertEquals(String.valueOf(1000 + maxrec - 1), (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());

        /* find with order by limit and offset with condition */
        docs = this.collection.find("$.F3 > 2").orderBy("CAST($.F4 as SIGNED)").fields("$._id as _id, $.F2/$.F3 as f1,$.F4 as f4").limit(10).offset(2)
                .execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
            assertEquals(String.valueOf(1000 + maxrec - i - 2), (((JsonString) doc.get("_id")).getString()));
            assertEquals((long) (10000 - maxrec + i + 2), (long) (((JsonNumber) doc.get("f4")).getInteger()));
            assertEquals((long) 10, (long) (((JsonNumber) doc.get("f1")).getInteger()));
        }
        assertEquals(i, (maxrec - 4));
    }

    @Test
    public void testCollectionFindGroupBy() throws Exception {
        int i = 0, j = 0, maxrec = 10, grpcnt = 50;
        DbDoc doc = null;

        DbDoc[] jsonlist = new DbDocImpl[maxrec];
        for (j = 1; j <= grpcnt; j++) {
            for (i = 0; i < maxrec; i++) {
                DbDoc newDoc2 = new DbDocImpl();
                newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + (1000 * j))));
                newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(10 * (i + 1))));
                newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(i + 1)));
                newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(100 * (i + 1))));
                newDoc2.add("F4", new JsonString().setValue(buildString(8192 + i, 'X')));
                jsonlist[i] = newDoc2;
                newDoc2 = null;
            }
            this.collection.add(jsonlist).execute();
        }
        assertEquals((maxrec * grpcnt), this.collection.count());

        /* find with groupBy (Sum) with Having Clause */
        DocResult docs = this.collection.find().fields("sum($.F1) as sum_f1, sum($.F2) as sum_f2, sum($.F3) as sum_f3,Max($.F4) as max_f4 ").groupBy("$.F1")
                .having("MIn($.F1) > 10").orderBy("sum($.F1)").execute();
        i = 1;
        while (docs.hasNext()) {
            i++;
            doc = docs.next();
            assertEquals((long) (i * grpcnt * 10), (long) (((JsonNumber) doc.get("sum_f1")).getInteger()));
            assertEquals((long) (i * grpcnt), (long) (((JsonNumber) doc.get("sum_f2")).getInteger()));
            assertEquals((long) (i * grpcnt * 100), (long) (((JsonNumber) doc.get("sum_f3")).getInteger()));
            //assertEquals((buildString(10+i,'X')), (((JsonString) doc.get("max_f4")).getString()));
        }
        assertEquals((maxrec), i);

        /* find with groupBy (Max) with Having Clause on String */
        docs = this.collection.find().fields("max($.F1) as max_f1, max($.F2) as max_f2, max($.F3) as max_f3,max($.F4) as max_f4 ").groupBy("$.F4")
                .having("max($.F1) > 20").execute();
        i = 2;
        while (docs.hasNext()) {
            i++;
            doc = docs.next();
            assertEquals(String.valueOf(i * 10), (((JsonString) doc.get("max_f1")).getString()));
            assertEquals(String.valueOf(i), (((JsonString) doc.get("max_f2")).getString()));
            assertEquals(String.valueOf(i * 100), (((JsonString) doc.get("max_f3")).getString()));
            //assertEquals((buildString(10+i,'X')), (((JsonString) doc.get("max_f4")).getString()));
        }
        assertEquals((maxrec), i);

        docs = this.collection.find().fields("max($.F1) as max_f1, max($.F2) as max_f2, max($.F3) as max_f3,max($.F4) as max_f4max_f4").groupBy("$.F4")
                .having("max($.F1) > 20").orderBy("$.F4").execute();
        //docs = coll.find().fields("max($.F4) as max_f4,$.F4 as f4").groupBy("$.F4").having("max($.F1) > 20").orderBy("$.F4").execute();
        i = 2;
        while (docs.hasNext()) {
            i++;
            doc = docs.next();
            assertEquals(String.valueOf(i * 10), (((JsonString) doc.get("max_f1")).getString()));
            assertEquals(String.valueOf(i), (((JsonString) doc.get("max_f2")).getString()));
            assertEquals(String.valueOf(i * 100), (((JsonString) doc.get("max_f3")).getString()));
            //assertEquals((buildString(10+i,'X')), (((JsonString) doc.get("max_f4")).getString()));
        }
        assertEquals((maxrec), i);

        docs = this.collection.find().fields("max($.F1) as max_f1, max($.F2) as max_f2, max($.F3) as max_f3,max($.F4) as max_f4max_f4").groupBy("$.F4")
                .having("max($.F1) > 20").orderBy("$.F4").limit(1).offset(1).execute();
        doc = docs.next();
        assertEquals(String.valueOf(40), (((JsonString) doc.get("max_f1")).getString()));
        assertEquals(String.valueOf(4), (((JsonString) doc.get("max_f2")).getString()));
        assertEquals(String.valueOf(400), (((JsonString) doc.get("max_f3")).getString()));
        assertFalse(docs.hasNext());
    }

    @SuppressWarnings("deprecation")
    @Test
    public void testCollectionFindSkipWarning() throws Exception {
        int i = 0, maxrec = 10;
        DbDoc doc = null;
        DocResult docs = null;

        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(10 * (i + 1))));
            newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F4", new JsonNumber().setValue(String.valueOf(10000 - i)));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        /* find with order by and condition */
        docs = this.collection.find("$.F3 > 1").orderBy("CAST($.F4 as SIGNED)").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            i++;
            assertEquals(String.valueOf(1000 + maxrec - i), (((JsonString) doc.get("_id")).getString()));
            assertEquals((long) (10000 - maxrec + i), (long) (((JsonNumber) doc.get("F4")).getInteger()));

        }
        assertEquals(i, (maxrec - 1));

        /* find with order by and limit with condition */
        docs = this.collection.find("$._id > 1001").orderBy("CAST($.F4 as SIGNED)").limit(1).skip(2).execute();

        doc = docs.next();
        assertEquals(String.valueOf(1000 + maxrec - 3), (((JsonString) doc.get("_id")).getString()));
        assertFalse(docs.hasNext());
    }

    /* OPerators =,!=,<,>,<=,>= IN, NOT IN,Like , Not Like, Between, REGEXP,NOT REGEXP , interval,|,&,^,<<,>>,~ */
    /* REGEXP,NOT REGEXP,LIKE, NOT LIKE, */
    @Test
    public void testCollectionFindWithStringComparison() throws Exception {
        int i = 0, maxrec = 10;
        int SLen = 1024;
        DbDoc doc = null;

        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1 + 1000)));
            newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(10 * (i + 1))));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F3", new JsonString().setValue(buildString(SLen + i + 1, 'q') + buildString(1 + i, 'X') + buildString(SLen + i + 1, '#')));
            //    newDoc2.add("F3", new JsonString().setValue("?????"));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        /* find With REGEXP Condition */
        DocResult docs = this.collection.find("$.F3  REGEXP 'q'").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals(buildString(SLen + i + 1, 'q') + buildString(1 + i, 'X') + buildString(SLen + i + 1, '#'), (((JsonString) doc.get("f3")).getString()));
            i++;
        }
        assertEquals((maxrec), i);

        /* find With REGEXP Condition */
        docs = this.collection.find("$.F3 REGEXP 'qXX#'").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3").execute();
        doc = docs.next();
        assertEquals(buildString(SLen + 2, 'q') + buildString(2, 'X') + buildString(SLen + 2, '#'), (((JsonString) doc.get("f3")).getString()));
        assertFalse(docs.hasNext());

        /* find With Not REGEXP Condition */
        docs = this.collection.find("$.F3 NOT REGEXP 'qX*#'").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3").execute();
        i = 0;
        assertFalse(docs.hasNext());

        /* find With REGEXP Condition */
        docs = this.collection.find("$.F3 REGEXP 'qXXXX#'").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3").execute();
        doc = docs.next();
        assertEquals(buildString(SLen + 4, 'q') + buildString(4, 'X') + buildString(SLen + 4, '#'), (((JsonString) doc.get("f3")).getString()));
        assertFalse(docs.hasNext());

        /* find With Like Condition */
        docs = this.collection.find("$.F3  like '%q_X_#%'").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3").orderBy("CAST($.F2 as SIGNED)")
                .execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals(buildString(SLen + i + 1, 'q') + buildString(1 + i, 'X') + buildString(SLen + i + 1, '#'), (((JsonString) doc.get("f3")).getString()));
            i++;
        }
        assertEquals(3, i);

        /* find With Not Like Condition */
        docs = this.collection.find("$.F3 Like '%qX#%'").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3").execute();
        doc = docs.next();
        assertEquals(buildString(SLen + 1, 'q') + buildString(1, 'X') + buildString(SLen + 1, '#'), (((JsonString) doc.get("f3")).getString()));
        assertFalse(docs.hasNext());

        /* find With Like and NOT REGEXP Condition */
        docs = this.collection.find("$.F3 NOT REGEXP 'qqX##' and $.F3  like '%q_X_#%' ").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3")
                .orderBy("CAST($.F2 as SIGNED)").execute();
        i = 0;
        while (docs.hasNext()) {
            i++;
            doc = docs.next();
            assertEquals(buildString(SLen + i + 1, 'q') + buildString(1 + i, 'X') + buildString(SLen + i + 1, '#'), (((JsonString) doc.get("f3")).getString()));

        }
        assertEquals(2, i);

        /* find With Like , NOT REGEXP and between Condition */
        docs = this.collection.find("$.F3 NOT REGEXP 'qqX##' and $.F3  like '%q_X_#%' and $.F1 between 21 and 31")
                .fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3").orderBy("CAST($.F2 as SIGNED)").execute();
        doc = docs.next();
        assertEquals(buildString(SLen + 3, 'q') + buildString(3, 'X') + buildString(SLen + 3, '#'), (((JsonString) doc.get("f3")).getString()));
        assertEquals((long) 30, (long) (((JsonNumber) doc.get("f1")).getInteger()));
        assertFalse(docs.hasNext());
    }

    /* |,&,^,<<,>>,~ */
    @Test
    public void testCollectionFindWithBitOperation() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        int i = 0, maxrec = 10;
        int SLen = 1;
        DbDoc doc = null;

        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1 + 1000)));
            newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf((int) Math.pow(2, (i + 1)))));
            newDoc2.add("F3", new JsonString().setValue(buildString(SLen + i, 'q')));
            //    newDoc2.add("F3", new JsonString().setValue("?????"));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        /* find With bitwise | Condition */
        DocResult docs = this.collection.find("CAST($.F2 as SIGNED) | pow(2,$.F1) = $.F2 ")
                .fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3 , $.F2 | pow(2,$.F1) as tmp").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals(String.valueOf(i + 1 + 1000), (((JsonString) doc.get("_id")).getString()));
            assertEquals((long) (i + 1), (long) (((JsonNumber) doc.get("f1")).getInteger()));
            assertEquals((long) ((int) Math.pow(2, (i + 1))), (long) (((JsonNumber) doc.get("f2")).getInteger()));
            assertEquals(buildString(SLen + i, 'q'), (((JsonString) doc.get("f3")).getString()));
            assertEquals((long) Math.pow(2, (i + 1)), (long) (((JsonNumber) doc.get("tmp")).getInteger()));
            i++;
        }
        assertEquals((maxrec), i);

        /* find With bitwise & Condition */
        docs = this.collection.find("CAST($.F2 as SIGNED) & 64 ").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3 , $.F2 & 64 as tmp").execute();
        doc = docs.next();
        assertEquals((long) 64, (long) (((JsonNumber) doc.get("f2")).getInteger()));
        assertEquals((long) 64, (long) (((JsonNumber) doc.get("tmp")).getInteger()));
        assertFalse(docs.hasNext());

        /* find With bitwise | Condition */
        docs = this.collection.find("CAST($.F2 as SIGNED) | $.F1 = 37 ").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3 ,$.F2 | $.F1  as tmp")
                .execute();
        doc = docs.next();
        assertEquals((long) 32, (long) (((JsonNumber) doc.get("f2")).getInteger()));
        assertEquals((long) 37, (long) (((JsonNumber) doc.get("tmp")).getInteger()));
        assertFalse(docs.hasNext());

        /* find With bitwise << Condition */
        docs = this.collection.find("CAST($.F2 as SIGNED) = 1<<4 ").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3 ,  1<<4  as tmp").execute();
        doc = docs.next();
        assertEquals((long) 16, (long) (((JsonNumber) doc.get("f2")).getInteger()));
        assertEquals((long) 16, (long) (((JsonNumber) doc.get("tmp")).getInteger()));
        assertFalse(docs.hasNext());

        /* find With bitwise >> Condition */
        docs = this.collection.find("CAST($.F2 as SIGNED) = 32>>4 ").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3 , 32>>4  as tmp").execute();
        doc = docs.next();
        assertEquals((long) 2, (long) (((JsonNumber) doc.get("f2")).getInteger()));
        assertEquals((long) 2, (long) (((JsonNumber) doc.get("tmp")).getInteger()));
        assertFalse(docs.hasNext());

        /* find With bitwise ^ Condition */
        docs = this.collection.find("CAST($.F2 as SIGNED) ^ 1 = 17").fields("$._id as _id,$.F2 as f2, $.F2 ^ 1 as tmp").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals((long) 16, (long) (((JsonNumber) doc.get("f2")).getInteger()));
            assertEquals((long) 17, (long) (((JsonNumber) doc.get("tmp")).getInteger()));
            i++;
        }
        assertFalse(docs.hasNext());
        this.collection.add("{\"x1\":\"31\", \"x2\":\"13\", \"x3\":\"8\", \"x4\":\"18446744073709551614\"}").execute();

        /* find With bitwise ~ Condition **********FAILING************ */
        docs = this.collection.find("~16 = ~CAST($.F2 as SIGNED)").fields("$._id as _id,$.F2 as f2, ~1 as tmp").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals((long) 16, (long) (((JsonNumber) doc.get("f2")).getInteger()));
            //assertEquals(17, (((JsonNumber) doc.get("tmp")).getInteger()));
            i++;
        }
        assertFalse(docs.hasNext());
    }

    /* interval, In, NOT IN */
    @Test
    public void testCollectionFindWithIntervalOperation() throws Exception {
        int i = 0, maxrec = 10;
        int SLen = 1;
        DbDoc doc = null;

        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1 + 1000)));
            newDoc2.add("dt", new JsonString().setValue((2000 + i) + "-02-" + (i * 2 + 10)));
            newDoc2.add("dtime", new JsonString().setValue((2000 + i) + "-02-01 12:" + (i + 10) + ":01"));
            newDoc2.add("str", new JsonString().setValue(buildString(SLen + i, 'q')));
            newDoc2.add("ival", new JsonNumber().setValue(String.valueOf((int) Math.pow(2, (i + 1)))));
            if (maxrec - 1 == i) {
                newDoc2.add("ival", new JsonNumber().setValue(String.valueOf(-2147483648)));
            }
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();
        assertEquals((maxrec), this.collection.count());

        /* find With bitwise | Condition */
        DocResult docs = this.collection.find("CAST($.ival as SIGNED)>1 ")
                .fields("$._id as _id, $.dt as f1, $.dtime as f2, $.str as f3 , $.ival  as f4,$.dt - interval 25 day as tmp").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals(String.valueOf(i + 1 + 1000), (((JsonString) doc.get("_id")).getString()));
            assertEquals(((2000 + i) + "-02-" + (i * 2 + 10)), (((JsonString) doc.get("f1")).getString()));
            assertEquals((2000 + i) + "-02-01 12:" + (i + 10) + ":01", (((JsonString) doc.get("f2")).getString()));
            assertEquals(buildString(SLen + i, 'q'), (((JsonString) doc.get("f3")).getString()));
            assertEquals((long) ((int) Math.pow(2, (i + 1))), (long) (((JsonNumber) doc.get("f4")).getInteger()));
            i++;
        }

        assertEquals((maxrec - 1), i);
        /* find With bitwise interval Condition */
        docs = this.collection.find("$.dt + interval 6 day = '2007-03-02' ").fields("$._id as _id, $.dt as f1 ").execute();
        doc = docs.next();
        assertEquals("2007-02-24", (((JsonString) doc.get("f1")).getString()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("$.dt + interval 1 month = '2006-03-22' ").fields("$._id as _id, $.dt as f1 ").execute();
        doc = docs.next();
        assertEquals("2006-02-22", (((JsonString) doc.get("f1")).getString()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("$.dt + interval 1 year = '2010-02-28' ").fields("$._id as _id, $.dt as f1 ").execute();
        doc = docs.next();
        assertEquals("2009-02-28", (((JsonString) doc.get("f1")).getString()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("$.dt - interval 1 year = '2008-02-28' ").fields("$._id as _id, $.dt as f1 ").execute();
        doc = docs.next();
        assertEquals("2009-02-28", (((JsonString) doc.get("f1")).getString()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("$.dt - interval 25 day = '2007-01-30' ").fields("$._id as _id, $.dt as f1 ").execute();
        doc = docs.next();
        assertEquals("2007-02-24", (((JsonString) doc.get("f1")).getString()));
        assertFalse(docs.hasNext());

        /* Between */
        docs = this.collection.find("CAST($.ival as SIGNED) between 65 and 128 ").fields("$._id as _id, $.ival as f1 ").execute();
        doc = docs.next();
        assertEquals((long) 128, (long) (((JsonNumber) doc.get("f1")).getInteger()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("$.dt between '2006-01-28' and '2007-02-01' ").fields("$._id as _id, $.dt as f1 ").execute();
        doc = docs.next();
        assertEquals("2006-02-22", (((JsonString) doc.get("f1")).getString()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("CAST($.ival as SIGNED) <0 ").fields("$._id as _id, $.ival as f1 ").execute();
        doc = docs.next();
        assertEquals((long) -2147483648, (long) (((JsonNumber) doc.get("f1")).getInteger()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("(CAST($.ival as SIGNED) between 9 and 31) or (CAST($.ival as SIGNED) between 65 and 128)")
                .fields("$._id as _id, $.ival as f1 ").orderBy("CAST($.ival as SIGNED) asc").execute();
        doc = docs.next();
        assertEquals((long) 16, (long) (((JsonNumber) doc.get("f1")).getInteger()));
        doc = docs.next();
        assertEquals((long) 128, (long) (((JsonNumber) doc.get("f1")).getInteger()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("(CAST($.ival as SIGNED) between 9 and 31) and (CAST($.ival as SIGNED) between 65 and 128)")
                .fields("$._id as _id, $.ival as f1 ").orderBy("CAST($.ival as SIGNED)").execute();
        assertFalse(docs.hasNext());

        docs = this.collection.find("CAST($.ival as SIGNED) in (20,NULL,31.5,'17',16,CAST($.ival as SIGNED)+1) ").fields("$._id as _id, $.ival as f1 ")
                .execute();
        doc = docs.next();
        assertEquals((long) 16, (long) (((JsonNumber) doc.get("f1")).getInteger()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("(CAST($.ival as SIGNED) in (16,32,4,256,512)) and ($.dt - interval 25 day = '2007-01-30' ) and  ($.ival not in(2,4))")
                .fields("$._id as _id, $.ival as f1 ").execute();
        doc = docs.next();
        assertEquals((long) 256, (long) (((JsonNumber) doc.get("f1")).getInteger()));
        assertFalse(docs.hasNext());
    }

    /**
     * Issue : in orderBy all values are treated as string
     * : bind() with Map Fails
     * 
     * @throws Exception
     */
    @Test
    public void testCollectionFindWithBind() throws Exception {
        int i = 0, maxrec = 15;
        int SLen = 500;
        DbDoc doc = null;
        DbDoc[] jsonlist = new DbDocImpl[maxrec];

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1 + 1000)));
            newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf((int) Math.pow(2, (i + 1)))));
            newDoc2.add("F3", new JsonString().setValue(buildString(SLen + i, 'q')));
            jsonlist[i] = newDoc2;
            newDoc2 = null;
        }
        this.collection.add(jsonlist).execute();

        assertEquals((maxrec), this.collection.count());

        /* find all */
        DocResult docs = this.collection.find("CAST($.F2 as SIGNED) > ? ").bind(new Object[] { 1 }).fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3")
                .execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals(String.valueOf(i + 1 + 1000), (((JsonString) doc.get("_id")).getString()));
            assertEquals((long) (i + 1), (long) (((JsonNumber) doc.get("f1")).getInteger()));
            assertEquals((long) ((int) Math.pow(2, (i + 1))), (long) (((JsonNumber) doc.get("f2")).getInteger()));
            //    assertEquals(buildString(SLen+i,'q'), (((JsonString) doc.get("f3")).getString()));
            i++;
        }
        assertEquals((maxrec), i);

        docs = this.collection.find("CAST($.F2 as SIGNED) = ?").bind(new Object[] { 32 }).execute();
        doc = docs.next();
        assertEquals((long) 32, (long) (((JsonNumber) doc.get("F2")).getInteger()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("CAST($.F2 as SIGNED) between ? and ?").bind(new Object[] { 10, 17 }).execute();
        doc = docs.next();
        assertEquals((long) 16, (long) (((JsonNumber) doc.get("F2")).getInteger()));
        assertFalse(docs.hasNext());

        docs = this.collection.find("CAST($.F2 as SIGNED) in(?,?,?,?,?,?,?,?,?,?,?+1,?-1)").bind(new Object[] { 1, 3, 5, 7, 9, 11, 13, 15, 17, 19, 31, 129 })
                .orderBy("CAST($.F2 as SIGNED)").execute();
        doc = docs.next();
        assertEquals((long) 32, (long) (((JsonNumber) doc.get("F2")).getInteger()));
        doc = docs.next();
        assertEquals((long) 128, (long) (((JsonNumber) doc.get("F2")).getInteger()));
        assertFalse(docs.hasNext());

        Object[] tmp = new Object[maxrec];
        String q = "CAST($.F2 as SIGNED) in(";
        for (i = 0; i < maxrec; i++) {
            if (i > 0) {
                q = q + ",";
            }
            q = q + "?";
            tmp[i] = (int) Math.pow(2, (i + 1));
        }
        q = q + ")";

        docs = this.collection.find(q).bind(tmp).orderBy("CAST($.F2 as SIGNED) asc").execute();
        //    docs= coll.find(q).bind(tmp).orderBy("$.F2").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals((long) Math.pow(2, i + 1), (long) (((JsonNumber) doc.get("F2")).getInteger()));
            i++;
        }
        assertEquals(maxrec, i);
        assertFalse(docs.hasNext());
        //tmp =null;
        tmp = new Object[maxrec];
        q = "$.F3 in(";
        for (i = 0; i < maxrec; i++) {
            if (i > 0) {
                q = q + ",";
            }
            q = q + "?";
            tmp[i] = buildString(SLen + i, 'q');
        }
        q = q + ")";

        docs = this.collection.find(q).bind(tmp).fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3 ").orderBy("$.F3 asc").execute();
        //docs= coll.find("$.F3 not in('dd')").fields("$._id as _id, $.F1 as f1, $.F2 as f2, $.F3 as f3 ").orderBy("$.F3 asc").execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals(String.valueOf(i + 1 + 1000), (((JsonString) doc.get("_id")).getString()));
            assertEquals((long) (i + 1), (long) (((JsonNumber) doc.get("f1")).getInteger()));
            assertEquals((long) ((int) Math.pow(2, (i + 1))), (long) (((JsonNumber) doc.get("f2")).getInteger()));
            i++;
        }
        assertEquals((maxrec), i);

        /* With Map */
        Map<String, Object> params = new HashMap<>();
        params.put("thePlaceholder", 32);
        params.put("thePlaceholder2", 2);
        docs = this.collection.find("CAST($.F2 as SIGNED) = :thePlaceholder or CAST($.F2 as SIGNED) = :thePlaceholder2")
                .fields("$._id as _id, $.F1 as f1, $.F2 as f2").bind(params).orderBy("CAST($.F2 as SIGNED) desc").execute();
        doc = docs.next();
        assertEquals(String.valueOf(1005), (((JsonString) doc.get("_id")).getString()));
        assertEquals((long) (32), (long) (((JsonNumber) doc.get("f2")).getInteger()));
        doc = docs.next();
        assertEquals(String.valueOf(1001), (((JsonString) doc.get("_id")).getString()));
        assertEquals((long) (2), (long) (((JsonNumber) doc.get("f2")).getInteger()));

        assertFalse(docs.hasNext());

        q = "";
        params.clear();
        for (i = 0; i < maxrec; i++) {
            params.put("thePlaceholder" + i, (int) Math.pow(2, (i + 1)));
            if (i > 0) {
                q = q + " or ";
            }
            q = q + "CAST($.F2 as SIGNED) =:thePlaceholder" + i + " ";
        }

        docs = this.collection.find(q).fields("$._id as _id, $.F1 as f1, $.F2 as f2").bind(params).execute();
        i = 0;
        while (docs.hasNext()) {
            doc = docs.next();
            assertEquals((long) ((int) Math.pow(2, (i + 1))), (long) (((JsonNumber) doc.get("f2")).getInteger()));
            i++;
        }
        assertEquals((maxrec), i);
    }

    @Test
    public void testCollectionFindArray() throws Exception {
        int i = 0, j = 0, maxrec = 8, minArraySize = 3;
        JsonArray yArray = null;
        DbDoc doc = null;
        DocResult ddoc = null;
        long l3 = 2147483647;
        double d1 = 1000.1234;

        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonNumber().setValue(String.valueOf(i + 1)));

            JsonArray jarray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                jarray.addValue(new JsonNumber().setValue(String.valueOf((l3 + j + i))));
            }
            newDoc2.add("ARR1", jarray);

            JsonArray karray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                karray.addValue(new JsonNumber().setValue(String.valueOf((d1 + j + i))));
            }
            newDoc2.add("ARR2", karray);
            JsonArray larray = new JsonArray();
            for (j = 0; j < (minArraySize + i); j++) {
                larray.addValue(new JsonString().setValue("St_" + i + "_" + j));
            }
            newDoc2.add("ARR3", larray);
            this.collection.add(newDoc2).execute();
            newDoc2 = null;
            jarray = null;
        }

        assertEquals((maxrec), this.collection.count());

        ddoc = this.collection.find("CAST($.F1 as SIGNED) > 1").orderBy("$._id").execute();
        i = 1;
        while (ddoc.hasNext()) {
            doc = ddoc.next();
            yArray = (JsonArray) doc.get("ARR1");
            assertEquals(minArraySize + i, yArray.size());
            for (j = 0; j < yArray.size(); j++) {
                assertEquals(new BigDecimal(String.valueOf(l3 + j + i)), (((JsonNumber) yArray.get(j)).getBigDecimal()));
            }
            i++;
        }
        assertEquals((maxrec), i);

        /* Condition On Array(int) field */
        ddoc = this.collection.find("CAST($.ARR1[1] as SIGNED) > 2147483650").orderBy("CAST($.ARR1[1] as SIGNED)").execute();
        i = 0;
        while (ddoc.hasNext()) {
            doc = ddoc.next();
            assertEquals((long) (i + 4), (long) (((JsonNumber) doc.get("F1")).getInteger()));
            i++;
        }
        assertEquals((maxrec - 3), i);

        /* Condition On Array(String) field */
        ddoc = this.collection.find("$.ARR3[1] = 'St_3_1'").orderBy("$.ARR3[1]").execute();
        doc = ddoc.next();
        assertEquals((long) (4), (long) (((JsonNumber) doc.get("F1")).getInteger()));
        assertFalse(ddoc.hasNext());

        /* Multiple Condition On Array(String) field */
        ddoc = this.collection.find("$.ARR3[1] in ('St_3_1','St_2_1','St_4_1')and $.ARR3[2] in ('St_3_2','St_4_2')").orderBy("$.ARR3[1]").execute();
        doc = ddoc.next();
        assertEquals((long) (4), (long) (((JsonNumber) doc.get("F1")).getInteger()));
        doc = ddoc.next();
        assertEquals((long) (5), (long) (((JsonNumber) doc.get("F1")).getInteger()));
        assertFalse(ddoc.hasNext());
    }

    /**
     * Checks getWarningsCount and getWarnings APIs
     * 
     * @throws Exception
     */
    @Test
    public void testGetWarningsFromCollection() throws Exception {
        assumeTrue(mysqlVersionMeetsMinimum(ServerVersion.parseVersion("8.0.0")), "MySQL 8.0+ is required to run this test.");

        String collname = "coll1";
        Collection coll = null;
        Warning w = null;
        DocResult docs = null;
        int i = 0;
        try {
            this.session.sql("set  max_error_count=20000").execute();
            dropCollection(collname);
            coll = this.schema.createCollection(collname, true);
            for (i = 1; i <= 10; i++) {
                if (i % 2 == 0) {
                    coll.add("{\"X\":" + i + ",\"Y\":" + (i + 1000) + "}").execute();
                } else {
                    coll.add("{\"X\":0,\"Y\":0}").execute();
                }
            }
            docs = coll.find().fields("1/$.X as col1,1/$.Y as col2").execute();
            assertEquals(10, docs.getWarningsCount());
            i = 0;
            for (Iterator<Warning> warn = docs.getWarnings(); warn.hasNext();) {
                w = warn.next();
                assertEquals("Division by 0", w.getMessage());
                assertEquals(2, w.getLevel());
                assertEquals(1365, w.getCode());
                i++;
            }
            this.schema.dropCollection(collname);
            coll = this.schema.createCollection(collname, true);
            coll.add("{\"X\":1}").execute();
            String s = "";
            for (i = 1; i <= 10000; i++) {
                if (i > 1) {
                    s = s + ",";
                }
                if (i % 2 == 0) {
                    s = s + "1/$.X as col" + i;
                } else {
                    s = s + "$.X/0 as col1" + i;
                }
            }
            docs = coll.find().fields(s).execute();
            assertEquals(5000, docs.getWarningsCount());
            i = 0;
            for (Iterator<Warning> warn = docs.getWarnings(); warn.hasNext();) {
                w = warn.next();
                assertEquals("Division by 0", w.getMessage());
                assertEquals(2, w.getLevel());
                assertEquals(1365, w.getCode());
                i++;
            }
            this.schema.dropCollection(collname);
        } finally {
            if (this.session != null) {
                this.session.close();
            }
        }
    }

    @Test
    public void testCollectionFindAsyncMany() throws Exception {
        int i = 0, maxrec = 10;
        int NUMBER_OF_QUERIES = 1000;
        DbDoc doc = null;
        AddResult res = null;
        CompletableFuture<AddResult> asyncRes = null;
        DocResult docs = null;

        double d = 100.123;

        /* add().executeAsync() maxrec num of records */
        for (i = 0; i < maxrec; i++) {
            DbDoc newDoc2 = new DbDocImpl();
            newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
            newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
            newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(d + i)));
            newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(100 + i)));

            asyncRes = this.collection.add(newDoc2).executeAsync();
            res = asyncRes.get();

            assertEquals(1, res.getAffectedItemsCount());
            newDoc2 = null;
        }

        assertEquals((maxrec), this.collection.count());

        List<CompletableFuture<DocResult>> futures = new ArrayList<>();
        for (i = 0; i < NUMBER_OF_QUERIES; ++i) {
            if (i % 3 == 0) {
                futures.add(this.collection.find("F1  like '%Field%-5'").fields("$._id as _id, $.F1 as F1, $.F2 as F2, $.F3 as F3").executeAsync());
            } else if (i % 3 == 1) {
                futures.add(this.collection.find("NON_EXISTING_FUNCTION()").fields("$._id as _id, $.F1 as F1, $.F2 as F2, $.F3 as F3").executeAsync()); //Error
            } else {
                futures.add(this.collection.find("F3 = ?").bind(106).executeAsync());
            }
        }

        for (i = 0; i < NUMBER_OF_QUERIES; ++i) {
            if (i % 3 == 0) {
                docs = futures.get(i).get();
                doc = docs.next();
                assertEquals((long) 105, (long) (((JsonNumber) doc.get("F3")).getInteger()));
                assertEquals("1005", (((JsonString) doc.get("_id")).getString()));
                assertFalse(docs.hasNext());
            } else if (i % 3 == 1) {
                final int i1 = i;
                assertThrows(ExecutionException.class, "com.mysql.cj.protocol.x.XProtocolError: ERROR 1305 \\(42000\\) FUNCTION " + this.schema.getName()
                        + ".NON_EXISTING_FUNCTION does not exist", () -> futures.get(i1).get());
            } else {
                docs = futures.get(i).get();
                doc = docs.next();
                assertEquals((long) 106, (long) (((JsonNumber) doc.get("F3")).getInteger()));
                assertEquals("1006", (((JsonString) doc.get("_id")).getString()));
                assertFalse(docs.hasNext());
            }
        }

        final CompletableFuture<DocResult> asyncDocs = this.collection.find("F3 > ? and F3 < ?").bind(102, 106).fields(expr("{'_id':$._id,'X':sleep(1)}"))
                .executeAsync();
        assertThrows(java.util.concurrent.TimeoutException.class, () -> asyncDocs.get(2, TimeUnit.SECONDS));
    }

    @Test
    public void testCollectionFindAsyncExt() throws Exception {
        int i = 0, maxrec = 10;
        DbDoc doc = null;
        AddResult res = null;
        CompletableFuture<AddResult> asyncRes = null;
        CompletableFuture<DocResult> asyncDocs = null;
        DocResult docs = null;
        try {
            double d = 100.123;
            DbDoc[] jsonlist = new DbDocImpl[maxrec];

            /* add().executeAsync() with JsonList,DbDoc and JsonString */
            for (i = 0; i < maxrec; i++) {
                DbDoc newDoc2 = new DbDocImpl();
                newDoc2.add("_id", new JsonString().setValue(String.valueOf(i + 1000)));
                newDoc2.add("F1", new JsonString().setValue("Field-1-Data-" + i));
                newDoc2.add("F2", new JsonNumber().setValue(String.valueOf(d + i)));
                newDoc2.add("F3", new JsonNumber().setValue(String.valueOf(100 + i)));
                jsonlist[i] = newDoc2;
                newDoc2 = null;
            }

            asyncRes = this.collection.add(jsonlist).executeAsync();
            res = asyncRes.get();
            assertEquals(maxrec, res.getAffectedItemsCount());

            /* With Bind,fields and orderBy */
            asyncDocs = this.collection.find("$.F3  < ? and $.F3 > ? and $.F3 != ?").bind(105, 101, 103).fields("$._id as _id, $.F1 as f1, $.F3 as f3")
                    .orderBy("$.F3 asc").executeAsync();
            docs = asyncDocs.get();
            doc = docs.next();
            assertEquals((long) 102, (long) (((JsonNumber) doc.get("f3")).getInteger()));
            assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
            doc = docs.next();
            assertEquals((long) 104, (long) (((JsonNumber) doc.get("f3")).getInteger()));
            assertEquals("1004", (((JsonString) doc.get("_id")).getString()));
            assertFalse(docs.hasNext());

            /* find with groupBy with Having Clause */
            asyncDocs = this.collection.find("$.F3 > ? and $.F3 < ?").fields("max($.F1) as max_f1, sum($.F2) as max_f2, sum($.F3) as max_f3 ").groupBy("$.F3")
                    .having("max($.F3) > 105 and max($.F3) < 107 ").bind(104, 108).executeAsync();
            docs = asyncDocs.get();
            doc = docs.next();
            assertEquals("Field-1-Data-6", (((JsonString) doc.get("max_f1")).getString()));
            assertEquals(new BigDecimal(String.valueOf(d + 6)), (((JsonNumber) doc.get("max_f2")).getBigDecimal()));
            assertEquals((long) (106), (long) (((JsonNumber) doc.get("max_f3")).getInteger()));
            assertFalse(docs.hasNext());

            sqlUpdate("drop function if exists abcd");
            sqlUpdate(
                    "CREATE FUNCTION abcd (`p1 col1` CHAR(20)) RETURNS ENUM('YES','NO')  COMMENT 'Sample Function abcd'  DETERMINISTIC RETURN  IF(EXISTS(SELECT 1 ), 'YES', 'NO' )");

            /* execute Function */
            asyncDocs = this.collection.find("$.F1 like ? and $.F1  not like ? and $.F1 not like ?")
                    .bind(new Object[] { ("%Fie%-2"), ("%Fie%-1"), ("%Fie%-3") }).fields(expr("{'_id':$._id,'F3':$.F3,'X': abcd('S')}")).executeAsync();
            docs = asyncDocs.get();
            doc = docs.next();
            assertEquals((long) 102, (long) (((JsonNumber) doc.get("F3")).getInteger()));
            assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
            assertEquals("YES", (((JsonString) doc.get("X")).getString()));
            assertFalse(docs.hasNext());

            /* Map */
            Map<String, Object> params = new HashMap<>();
            params.put("namedParam10000", "%Fie%-2");
            params.put("namedParam10001", "%Fie%-3");
            params.put("namedParam10002", 102);
            asyncDocs = this.collection.find("($.F1 like :namedParam10000 OR $.F1 not like :namedParam10001) and $.F3  = :namedParam10002").bind(params)
                    .fields(expr("{'_id':$._id,'F3':$.F3,'X': abcd('S')}")).executeAsync();
            docs = asyncDocs.get();
            doc = docs.next();
            assertEquals((long) 102, (long) (((JsonNumber) doc.get("F3")).getInteger()));
            assertEquals("1002", (((JsonString) doc.get("_id")).getString()));
            assertEquals("YES", (((JsonString) doc.get("X")).getString()));
            assertFalse(docs.hasNext());
        } finally {
            sqlUpdate("drop function if exists abcd");

        }
    }
}
