/*****************************************************************
 *   Licensed to the Apache Software Foundation (ASF) under one
 *  or more contributor license agreements.  See the NOTICE file
 *  distributed with this work for additional information
 *  regarding copyright ownership.  The ASF licenses this file
 *  to you under the Apache License, Version 2.0 (the
 *  "License"); you may not use this file except in compliance
 *  with the License.  You may obtain a copy of the License at
 *
 *    https://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing,
 *  software distributed under the License is distributed on an
 *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 *  KIND, either express or implied.  See the License for the
 *  specific language governing permissions and limitations
 *  under the License.
 ****************************************************************/

package org.apache.cayenne.access.translator.select;

import org.apache.cayenne.access.DataContext;
import org.apache.cayenne.access.DataNode;
import org.apache.cayenne.access.jdbc.ColumnDescriptor;
import org.apache.cayenne.di.Inject;
import org.apache.cayenne.exp.ExpressionException;
import org.apache.cayenne.exp.ExpressionFactory;
import org.apache.cayenne.log.JdbcEventLogger;
import org.apache.cayenne.map.DbAttribute;
import org.apache.cayenne.map.DbEntity;
import org.apache.cayenne.query.ObjectSelect;
import org.apache.cayenne.query.PrefetchTreeNode;
import org.apache.cayenne.test.jdbc.DBHelper;
import org.apache.cayenne.testdo.testmap.ArtGroup;
import org.apache.cayenne.testdo.testmap.Artist;
import org.apache.cayenne.testdo.testmap.ArtistExhibit;
import org.apache.cayenne.testdo.testmap.Award;
import org.apache.cayenne.testdo.testmap.CompoundPainting;
import org.apache.cayenne.testdo.testmap.Exhibit;
import org.apache.cayenne.testdo.testmap.Gallery;
import org.apache.cayenne.testdo.testmap.Painting;
import org.apache.cayenne.unit.UnitDbAdapter;
import org.apache.cayenne.unit.di.server.CayenneProjects;
import org.apache.cayenne.unit.di.server.ServerCase;
import org.apache.cayenne.unit.di.server.UseServerRuntime;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.List;

import static org.junit.Assert.*;

@UseServerRuntime(CayenneProjects.TESTMAP_PROJECT)
public class DefaultSelectTranslatorIT extends ServerCase {

	@Inject
	private DataContext context;

	@Inject
	private UnitDbAdapter unitAdapter;

	@Inject
	private DBHelper dbHelper;

	@Inject
	private JdbcEventLogger logger;

	@Inject
	private DataNode dataNode;

	/**
	 * Tests query creation with qualifier and ordering.
	 */
	@Test
	public void testCreateSqlString1() throws Exception {
		// query with qualifier and ordering
		ObjectSelect<Artist> q = ObjectSelect.query(Artist.class, Artist.ARTIST_NAME.like("a%"))
				.orderBy(Artist.DATE_OF_BIRTH.asc());

		SelectTranslator defaultSelectTranslator = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver());
		String generatedSql = defaultSelectTranslator.getSql();

		// do some simple assertions to make sure all parts are in
		assertNotNull(generatedSql);
		assertFalse(defaultSelectTranslator.hasJoins());
		assertTrue(generatedSql.startsWith("SELECT "));
		assertTrue(generatedSql.indexOf(" FROM ") > 0);
		assertTrue(generatedSql.indexOf(" WHERE ") > generatedSql.indexOf(" FROM "));
		assertTrue(generatedSql.indexOf(" ORDER BY ") > generatedSql.indexOf(" WHERE "));
	}

	/**
	 * Tests query creation with qualifier and ordering.
	 */
	@Test
	public void testDbEntityQualifier() throws Exception {

		ObjectSelect<Artist> q = ObjectSelect.query(Artist.class);
		final DbEntity entity = context.getEntityResolver().getDbEntity("ARTIST");
		final DbEntity middleEntity = context.getEntityResolver().getDbEntity("ARTIST_GROUP");

		SelectTranslator transl = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver());

		entity.setQualifier(ExpressionFactory.exp("ARTIST_NAME = \"123\""));
		middleEntity.setQualifier(ExpressionFactory.exp("GROUP_ID = 1987"));

		try {

			String generatedSql = transl.getSql();

			// do some simple assertions to make sure all parts are in
			assertNotNull(generatedSql);
			assertTrue(generatedSql.startsWith("SELECT "));
			assertTrue(generatedSql.indexOf(" FROM ") > 0);
			if (generatedSql.contains("RTRIM")) {
				assertTrue(generatedSql.indexOf("ARTIST_NAME) =") > generatedSql.indexOf("RTRIM("));
			} else if (generatedSql.contains("TRIM")) {
				assertTrue(generatedSql.indexOf("ARTIST_NAME) =") > generatedSql.indexOf("TRIM("));
			} else {
				assertTrue(generatedSql.indexOf("ARTIST_NAME =") > 0);
			}
		} finally {
			entity.setQualifier(null);
			middleEntity.setQualifier(null);
		}
	}

	@Test
	public void testDbEntityQualifier_OuterJoin() throws Exception {

		ObjectSelect<Painting> q = ObjectSelect.query(Painting.class)
				.orderBy(Painting.TO_ARTIST.outer().dot(Artist.ARTIST_NAME).asc());

		final DbEntity entity = context.getEntityResolver().getDbEntity("ARTIST");
		final DbEntity middleEntity = context.getEntityResolver().getDbEntity("ARTIST_GROUP");

		SelectTranslator transl = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver());

		entity.setQualifier(ExpressionFactory.exp("ARTIST_NAME = \"123\""));
		middleEntity.setQualifier(ExpressionFactory.exp("GROUP_ID = 1987"));

		try {

			String generatedSql = transl.getSql();

			// do some simple assertions to make sure all parts are in
			assertNotNull(generatedSql);
			assertTrue(generatedSql.startsWith("SELECT "));
			assertTrue(generatedSql.indexOf(" FROM ") > 0);
			if (generatedSql.contains("RTRIM")) {
				assertTrue(generatedSql.indexOf("ARTIST_NAME) =") > generatedSql.indexOf("RTRIM("));
			} else if (generatedSql.contains("TRIM")) {
				assertTrue(generatedSql.indexOf("ARTIST_NAME) =") > generatedSql.indexOf("TRIM("));
			} else {
				assertTrue(generatedSql.indexOf("ARTIST_NAME =") > 0);
			}

		} finally {
			entity.setQualifier(null);
			middleEntity.setQualifier(null);
		}
	}

	@Test
	public void testDbEntityQualifier_FlattenedRel() throws Exception {

		ObjectSelect<Artist> q = ObjectSelect.query(Artist.class, Artist.GROUP_ARRAY.dot(ArtGroup.NAME).eq("bar"));

		final DbEntity entity = context.getEntityResolver().getDbEntity("ARTIST");
		final DbEntity middleEntity = context.getEntityResolver().getDbEntity("ARTIST_GROUP");

		SelectTranslator transl = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver());

		entity.setQualifier(ExpressionFactory.exp("ARTIST_NAME = \"123\""));
		middleEntity.setQualifier(ExpressionFactory.exp("GROUP_ID = 1987"));

		try {

			String generatedSql = transl.getSql();

			// do some simple assertions to make sure all parts are in
			assertNotNull(generatedSql);
			assertTrue(generatedSql.startsWith("SELECT "));
			assertTrue(generatedSql.indexOf(" FROM ") > 0);
			if (generatedSql.contains("RTRIM")) {
				assertTrue(generatedSql.indexOf("ARTIST_NAME) =") > generatedSql.indexOf("RTRIM("));
			} else if (generatedSql.contains("TRIM")) {
				assertTrue(generatedSql.indexOf("ARTIST_NAME) =") > generatedSql.indexOf("TRIM("));
			} else {
				assertTrue(generatedSql.indexOf("ARTIST_NAME =") > 0);
			}

		} finally {
			entity.setQualifier(null);
			middleEntity.setQualifier(null);
		}
	}

	@Test
	public void testDbEntityQualifier_RelatedMatch() throws Exception {

		ObjectSelect<Painting> q = ObjectSelect.query(Painting.class, Painting.TO_ARTIST.dot(Artist.ARTIST_NAME).eq("foo"));

		final DbEntity entity = context.getEntityResolver().getDbEntity("ARTIST");
		final DbEntity middleEntity = context.getEntityResolver().getDbEntity("ARTIST_GROUP");

		SelectTranslator transl = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver());

		entity.setQualifier(ExpressionFactory.exp("ARTIST_NAME = \"123\""));
		middleEntity.setQualifier(ExpressionFactory.exp("GROUP_ID = 1987"));

		try {

			String generatedSql = transl.getSql();

			// do some simple assertions to make sure all parts are in
			assertNotNull(generatedSql);
			assertTrue(generatedSql.startsWith("SELECT "));
			assertTrue(generatedSql.indexOf(" FROM ") > 0);
			if (generatedSql.contains("RTRIM")) {
				assertTrue(generatedSql.indexOf("ARTIST_NAME) =") > generatedSql.indexOf("RTRIM("));
			} else if (generatedSql.contains("TRIM")) {
				assertTrue(generatedSql.indexOf("ARTIST_NAME) =") > generatedSql.indexOf("TRIM("));
			} else {
				assertTrue(generatedSql.indexOf("ARTIST_NAME =") > 0);
			}
		} finally {
			entity.setQualifier(null);
			middleEntity.setQualifier(null);
		}
	}

	/**
	 * Tests query creation with "distinct" specified.
	 */
	@Test
	public void testCreateSqlString2() throws Exception {
		// query with "distinct" set
		ObjectSelect<Artist> q = ObjectSelect.query(Artist.class);
		q.distinct();

		String generatedSql = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver()).getSql();

		// do some simple assertions to make sure all parts are in
		assertNotNull(generatedSql);
		assertTrue(generatedSql.startsWith("SELECT DISTINCT"));
	}

	/**
	 * Test aliases when the same table used in more then 1 relationship. Check
	 * translation of relationship path "ArtistExhibit.toArtist.artistName" and
	 * "ArtistExhibit.toExhibit.toGallery.paintingArray.toArtist.artistName".
	 */
	@Test
	public void testCreateSqlString5() throws Exception {
		ObjectSelect<ArtistExhibit> q = ObjectSelect.query(ArtistExhibit.class)
				.where(ArtistExhibit.TO_ARTIST
						.dot(Artist.ARTIST_NAME).like( "a%"))
				.and(ArtistExhibit.TO_EXHIBIT
						.dot(Exhibit.TO_GALLERY)
						.dot(Gallery.PAINTING_ARRAY)
						.dot(Painting.TO_ARTIST)
						.dot(Artist.ARTIST_NAME).like("a%"));

		String generatedSql = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver()).getSql();
		// logObj.warn("Query: " + generatedSql);

		// do some simple assertions to make sure all parts are in
		assertNotNull(generatedSql);
		assertTrue(generatedSql.startsWith("SELECT "));
		assertTrue(generatedSql.indexOf(" FROM ") > 0);
		assertTrue(generatedSql.indexOf(" WHERE ") > generatedSql.indexOf(" FROM "));

		// check that there are 2 distinct aliases for the ARTIST table
		int ind1 = generatedSql.indexOf("ARTIST t", generatedSql.indexOf(" FROM "));
		assertTrue(ind1 > 0);

		int ind2 = generatedSql.indexOf("ARTIST t", ind1 + 1);
		assertTrue(ind2 > 0);

		assertTrue(generatedSql.charAt(ind1 + "ARTIST t".length()) != generatedSql.charAt(ind2 + "ARTIST t".length()));
	}

	/**
	 * Test aliases when the same table used in more then 1 relationship. Check
	 * translation of relationship path "ArtistExhibit.toArtist.artistName" and
	 * "ArtistExhibit.toArtist.paintingArray.paintingTitle".
	 */
	@Test
	public void testCreateSqlString6() throws Exception {
		ObjectSelect<ArtistExhibit> q = ObjectSelect.query(ArtistExhibit.class)
				.where(ArtistExhibit.TO_ARTIST.dot(Artist.ARTIST_NAME).like("a%"))
				.and(ArtistExhibit.TO_ARTIST.dot(Artist.PAINTING_ARRAY).dot(Painting.PAINTING_TITLE).like("p%"));

		String generatedSql = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver())
				.getSql();

		// do some simple assertions to make sure all parts are in
		assertNotNull(generatedSql);
		assertTrue(generatedSql.startsWith("SELECT "));
		assertTrue(generatedSql.indexOf(" FROM ") > 0);
		assertTrue(generatedSql.indexOf(" WHERE ") > generatedSql.indexOf(" FROM "));

		// check that there is only one distinct alias for the ARTIST
		// table
		int ind1 = generatedSql.indexOf("ARTIST t", generatedSql.indexOf(" FROM "));
		assertTrue(ind1 > 0);

		int ind2 = generatedSql.indexOf("ARTIST t", ind1 + 1);
		assertTrue(generatedSql, ind2 < 0);
	}

	/**
	 * Test query when qualifying on the same attribute more than once. Check
	 * translation "Artist.dateOfBirth > ? AND Artist.dateOfBirth < ?".
	 */
	@Test
	public void testCreateSqlString7() throws Exception {
		ObjectSelect<Artist> q = ObjectSelect.query(Artist.class)
				.where(Artist.DATE_OF_BIRTH.gt(new Date()))
				.and(Artist.DATE_OF_BIRTH.lt(new Date()));

		String generatedSql = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver())
				.getSql();
		// logObj.warn("Query: " + generatedSql);

		// do some simple assertions to make sure all parts are in
		assertNotNull(generatedSql);
		assertTrue(generatedSql.startsWith("SELECT "));

		int i1 = generatedSql.indexOf(" FROM ");
		assertTrue(i1 > 0);

		int i2 = generatedSql.indexOf(" WHERE ");
		assertTrue(i2 > i1);

		int i3 = generatedSql.indexOf("DATE_OF_BIRTH", i2 + 1);
		assertTrue(i3 > i2);

		int i4 = generatedSql.indexOf("DATE_OF_BIRTH", i3 + 1);
		assertTrue("No second DOB comparison: " + i4 + ", " + i3, i4 > i3);
	}

	/**
	 * Test query when qualifying on the same attribute accessed over
	 * relationship, more than once. Check translation
	 * "Painting.toArtist.dateOfBirth > ? AND Painting.toArtist.dateOfBirth <
	 * ?".
	 */
	@Test
	public void testCreateSqlString8() throws Exception {
		ObjectSelect<Painting> q = ObjectSelect.query(Painting.class)
				.where(Painting.TO_ARTIST.dot(Artist.DATE_OF_BIRTH).gt(new Date()))
				.and(Painting.TO_ARTIST.dot(Artist.DATE_OF_BIRTH).lt(new Date()));

		String generatedSql = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver()).getSql();

		// do some simple assertions to make sure all parts are in
		assertNotNull(generatedSql);
		assertTrue(generatedSql.startsWith("SELECT "));

		int i1 = generatedSql.indexOf(" FROM ");
		assertTrue(i1 > 0);

		int i2 = generatedSql.indexOf(" WHERE ");
		assertTrue(i2 > i1);

		int i3 = generatedSql.indexOf("DATE_OF_BIRTH", i2 + 1);
		assertTrue(i3 > i2);

		int i4 = generatedSql.indexOf("DATE_OF_BIRTH", i3 + 1);
		assertTrue("No second DOB comparison: " + i4 + ", " + i3, i4 > i3);
	}

	@Test
	public void testCreateSqlString9() throws Exception {
		// query for a compound ObjEntity with qualifier
		ObjectSelect<CompoundPainting> q = ObjectSelect.query(CompoundPainting.class, CompoundPainting.ARTIST_NAME.like("a%"));

		String sql = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver()).getSql();

		// do some simple assertions to make sure all parts are in
		assertNotNull(sql);
		assertTrue(sql.startsWith("SELECT "));

		int i1 = sql.indexOf(" FROM ");
		assertTrue(i1 > 0);

		int i2 = sql.indexOf("PAINTING");
		assertTrue(i2 > 0);

		int i3 = sql.indexOf("ARTIST");
		assertTrue(i3 > 0);

		int i4 = sql.indexOf("GALLERY");
		assertTrue(i4 > 0);

		int i5 = sql.indexOf("PAINTING_INFO");
		assertTrue(i5 > 0);

		int i6 = sql.indexOf("ARTIST_NAME");
		assertTrue(i6 > 0);

		int i7 = sql.indexOf("ESTIMATED_PRICE");
		assertTrue(i7 > 0);

		int i8 = sql.indexOf("GALLERY_NAME");
		assertTrue(i8 > 0);

		int i9 = sql.indexOf("PAINTING_TITLE");
		assertTrue(i9 > 0);

		int i10 = sql.indexOf("TEXT_REVIEW");
		assertTrue(i10 > 0);

		int i11 = sql.indexOf("PAINTING_ID");
		assertTrue(i11 > 0);

		int i12 = sql.indexOf("ARTIST_ID");
		assertTrue(i12 > 0);

		int i13 = sql.indexOf("GALLERY_ID");
		assertTrue(i13 > 0);

	}

	@Test
	public void testCreateSqlString10() throws Exception {
		// query with to-many joint prefetches
		ObjectSelect<Artist> q = ObjectSelect.query(Artist.class).prefetch(Artist.PAINTING_ARRAY.joint());

		SelectTranslator transl = new DefaultSelectTranslator(q, dataNode.getAdapter(),
				dataNode.getEntityResolver());
		String sql = transl.getSql();
		assertNotNull(sql);
		assertTrue(sql.startsWith("SELECT "));

		int i1 = sql.indexOf("ARTIST_ID");
		assertTrue(sql, i1 > 0);

		int i2 = sql.indexOf("FROM");
		assertTrue(sql, i2 > 0);

		assertTrue(sql, sql.indexOf("PAINTING_ID") > 0);

		// assert we have one join
		assertTrue(transl.hasJoins());
	}

	@Test
	public void testCreateSqlString11() throws Exception {
		// query with joint prefetches and other joins
		ObjectSelect q = ObjectSelect.query(Artist.class)
				.where(Artist.PAINTING_ARRAY.dot(Painting.PAINTING_TITLE).eq("a"))
				.prefetch(Artist.PAINTING_ARRAY.joint());

		SelectTranslator transl = new DefaultSelectTranslator(q, dataNode.getAdapter(),
				dataNode.getEntityResolver());

		transl.getSql();

		// assert we only have one join
		assertTrue(transl.hasJoins());
	}

	@Test
	public void testCreateSqlString12() throws Exception {
		// query with to-one joint prefetches
		ObjectSelect<Painting> q = ObjectSelect.query(Painting.class).prefetch(Painting.TO_ARTIST.joint());

		SelectTranslator transl = new DefaultSelectTranslator(q, dataNode.getAdapter(),
				dataNode.getEntityResolver());

		String sql = transl.getSql();
		assertNotNull(sql);
		assertTrue(sql.startsWith("SELECT "));

		int i1 = sql.indexOf("ARTIST_ID");
		assertTrue(sql, i1 > 0);

		int i2 = sql.indexOf("FROM");
		assertTrue(sql, i2 > 0);

		assertTrue(sql, sql.indexOf("PAINTING_ID") > 0);

		// assert we have one join
		assertTrue(transl.hasJoins());
	}

	@Test
	public void testCreateSqlString13() throws Exception {
		// query with invalid joint prefetches
		ObjectSelect<Painting> q = ObjectSelect.query(Painting.class)
				.prefetch("invalid.invalid", PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
		try {
			new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver()).getSql();
			fail("Invalid jointPrefetch must have thrown...");
		} catch (ExpressionException e) {
			// expected
		}
	}

	@Test
	public void testCreateSqlStringWithQuoteSqlIdentifiers() throws Exception {

		try {
			ObjectSelect<Artist> q = ObjectSelect.query(Artist.class)
					.orderBy(Artist.DATE_OF_BIRTH.asc());
			DbEntity entity = context.getEntityResolver().getDbEntity("ARTIST");
			entity.getDataMap().setQuotingSQLIdentifiers(true);

			String charStart = unitAdapter.getIdentifiersStartQuote();
			String charEnd = unitAdapter.getIdentifiersEndQuote();

			String s = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver()).getSql();
			assertTrue(s.startsWith("SELECT "));
			int iFrom = s.indexOf(" FROM ");
			assertTrue(iFrom > 0);
			int artistName = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "ARTIST_NAME" + charEnd);
			assertTrue(artistName > 0 && artistName < iFrom);
			int artistId = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "ARTIST_ID" + charEnd);
			assertTrue(artistId > 0 && artistId < iFrom);
			int dateOfBirth = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "DATE_OF_BIRTH" + charEnd);
			assertTrue(dateOfBirth > 0 && dateOfBirth < iFrom);
			int iArtist = s.indexOf(charStart + "ARTIST" + charEnd + " " + charStart + "t0" + charEnd);
			assertTrue(iArtist > iFrom);
			int iOrderBy = s.indexOf(" ORDER BY ");
			int dateOfBirth2 = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "DATE_OF_BIRTH" + charEnd,
					iOrderBy);
			assertTrue(iOrderBy > iArtist);
			assertTrue(dateOfBirth2 > iOrderBy);

		} finally {
			DbEntity entity = context.getEntityResolver().getDbEntity("ARTIST");
			entity.getDataMap().setQuotingSQLIdentifiers(false);
		}

	}

	@Test
	public void testCreateSqlStringWithQuoteSqlIdentifiers2() throws Exception {

		try {
			ObjectSelect<Artist> q = ObjectSelect.query(Artist.class)
					.where(Artist.DATE_OF_BIRTH.gt(new Date()))
					.and(Artist.DATE_OF_BIRTH.lt(new Date()));

			DbEntity entity = context.getEntityResolver().getDbEntity("ARTIST");
			entity.getDataMap().setQuotingSQLIdentifiers(true);

			String charStart = unitAdapter.getIdentifiersStartQuote();
			String charEnd = unitAdapter.getIdentifiersEndQuote();

			String s = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver()).getSql();

			assertTrue(s.startsWith("SELECT "));
			int iFrom = s.indexOf(" FROM ");
			assertTrue(iFrom > 0);
			int artistName = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "ARTIST_NAME" + charEnd);
			assertTrue(artistName > 0 && artistName < iFrom);
			int artistId = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "ARTIST_ID" + charEnd);
			assertTrue(artistId > 0 && artistId < iFrom);
			int dateOfBirth = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "DATE_OF_BIRTH" + charEnd);
			assertTrue(dateOfBirth > 0 && dateOfBirth < iFrom);
			int iArtist = s.indexOf(charStart + "ARTIST" + charEnd + " " + charStart + "t0" + charEnd);
			assertTrue(iArtist > iFrom);
			int iWhere = s.indexOf(" WHERE ");
			assertTrue(iWhere > iArtist);

			int dateOfBirth2 = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "DATE_OF_BIRTH" + charEnd + " > ?");
			assertTrue(dateOfBirth2 > iWhere);

			int iAnd = s.indexOf(" AND ");
			assertTrue(iAnd > iWhere);
			int dateOfBirth3 = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "DATE_OF_BIRTH" + charEnd + " < ?");
			assertTrue(dateOfBirth3 > iAnd);

		} finally {
			DbEntity entity = context.getEntityResolver().getDbEntity("ARTIST");
			entity.getDataMap().setQuotingSQLIdentifiers(false);
		}
	}

	@Test
	public void testCreateSqlStringWithQuoteSqlIdentifiers3() throws Exception {

		// query with joint prefetches and other joins
		// and with QuoteSqlIdentifiers = true
		try {
			ObjectSelect<Artist> q = ObjectSelect.query(Artist.class)
					.where(Artist.PAINTING_ARRAY.dot(Painting.PAINTING_TITLE).eq("a"))
					.prefetch(Artist.PAINTING_ARRAY.joint());

			DbEntity entity = context.getEntityResolver().getDbEntity("ARTIST");
			entity.getDataMap().setQuotingSQLIdentifiers(true);

			String charStart = unitAdapter.getIdentifiersStartQuote();
			String charEnd = unitAdapter.getIdentifiersEndQuote();

			String s = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver()).getSql();

			assertTrue(s, s.startsWith("SELECT DISTINCT "));
			int iFrom = s.indexOf(" FROM ");
			assertTrue(s, iFrom > 0);
			int artistName = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "ARTIST_NAME" + charEnd);
			assertTrue(s, artistName > 0 && artistName < iFrom);
			int artistId = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "ARTIST_ID" + charEnd);
			assertTrue(s, artistId > 0 && artistId < iFrom);
			int dateOfBirth = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "DATE_OF_BIRTH" + charEnd);
			assertTrue(s, dateOfBirth > 0 && dateOfBirth < iFrom);
			int estimatedPrice = s.indexOf(charStart + "t1" + charEnd + "." + charStart + "ESTIMATED_PRICE" + charEnd);
			assertTrue(s, estimatedPrice > 0 && estimatedPrice < iFrom);
			int paintingDescription = s.indexOf(charStart + "t1" + charEnd + "." + charStart + "PAINTING_DESCRIPTION"
					+ charEnd);
			assertTrue(s, paintingDescription > 0 && paintingDescription < iFrom);
			int paintingTitle = s.indexOf(charStart + "t1" + charEnd + "." + charStart + "PAINTING_TITLE" + charEnd);
			assertTrue(s, paintingTitle > 0 && paintingTitle < iFrom);
			int artistIdT1 = s.indexOf(charStart + "t1" + charEnd + "." + charStart + "ARTIST_ID" + charEnd);
			assertTrue(s, artistIdT1 > 0 && artistIdT1 < iFrom);
			int galleryId = s.indexOf(charStart + "t1" + charEnd + "." + charStart + "GALLERY_ID" + charEnd);
			assertTrue(s, galleryId > 0 && galleryId < iFrom);
			int paintingId = s.indexOf(charStart + "t1" + charEnd + "." + charStart + "PAINTING_ID" + charEnd);
			assertTrue(s, paintingId > 0 && paintingId < iFrom);
			int iArtist = s.indexOf(charStart + "ARTIST" + charEnd + " " + charStart + "t0" + charEnd);
			assertTrue(s, iArtist > iFrom);
			int iLeftJoin = s.indexOf("LEFT JOIN");
			assertTrue(s, iLeftJoin > iFrom);
			int iPainting = s.indexOf(charStart + "PAINTING" + charEnd + " " + charStart + "t1" + charEnd);
			assertTrue(s, iPainting > iLeftJoin);
			int iOn = s.indexOf(" ON ");
			assertTrue(s, iOn > iLeftJoin);
			int iArtistId = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "ARTIST_ID" + charEnd, iLeftJoin);
			assertTrue(s, iArtistId > iOn);
			int iArtistIdT1 = s
					.indexOf(charStart + "t1" + charEnd + "." + charStart + "ARTIST_ID" + charEnd, iLeftJoin);
			assertTrue(s, iArtistIdT1 > iOn);
			int i = s.indexOf("=", iLeftJoin);
			assertTrue(s, iArtistIdT1 > i || iArtistId > i);
			int iJoin = s.indexOf("JOIN");
			assertTrue(s, iJoin > iLeftJoin);
			int iPainting2 = s.indexOf(charStart + "PAINTING" + charEnd + " " + charStart + "t2" + charEnd);
			assertTrue(s, iPainting2 > iJoin);
			int iOn2 = s.indexOf(" ON ");
			assertTrue(s, iOn2 > iJoin);
			int iArtistId2 = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "ARTIST_ID" + charEnd, iJoin);
			assertTrue(s, iArtistId2 > iOn2);
			int iArtistId2T2 = s.indexOf(charStart + "t2" + charEnd + "." + charStart + "ARTIST_ID" + charEnd, iJoin);
			assertTrue(s, iArtistId2T2 > iOn2);
			int i2 = s.indexOf("=", iJoin);
			assertTrue(s, iArtistId2T2 > i2 || iArtistId2 > i2);
			int iWhere = s.indexOf(" WHERE ");
			assertTrue(s, iWhere > iJoin);

			int paintingTitle2 = s.indexOf(charStart + "t2" + charEnd + "." + charStart + "PAINTING_TITLE" + charEnd + " = ?");
			assertTrue(s, paintingTitle2 > iWhere);

		} finally {
			DbEntity entity = context.getEntityResolver().getDbEntity("ARTIST");
			entity.getDataMap().setQuotingSQLIdentifiers(false);
		}
	}

	@Test
	public void testCreateSqlStringWithQuoteSqlIdentifiers4() throws Exception {

		// query with to-one joint prefetches
		// and with QuoteSqlIdentifiers = true
		try {
			ObjectSelect<Painting> q = ObjectSelect.query(Painting.class).prefetch(Painting.TO_ARTIST.joint());

			DbEntity entity = context.getEntityResolver().getDbEntity("PAINTING");
			entity.getDataMap().setQuotingSQLIdentifiers(true);

			String charStart = unitAdapter.getIdentifiersStartQuote();
			String charEnd = unitAdapter.getIdentifiersEndQuote();

			String s = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver()).getSql();

			assertTrue(s, s.startsWith("SELECT "));
			int iFrom = s.indexOf(" FROM ");
			assertTrue(s, iFrom > 0);

			int paintingDescription = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "PAINTING_DESCRIPTION"
					+ charEnd);
			assertTrue(s, paintingDescription > 0 && paintingDescription < iFrom);
			int paintingTitle = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "PAINTING_TITLE" + charEnd);
			assertTrue(s, paintingTitle > 0 && paintingTitle < iFrom);
			int artistIdT1 = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "ARTIST_ID" + charEnd);
			assertTrue(s, artistIdT1 > 0 && artistIdT1 < iFrom);
			int estimatedPrice = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "ESTIMATED_PRICE" + charEnd);
			assertTrue(s, estimatedPrice > 0 && estimatedPrice < iFrom);
			int galleryId = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "GALLERY_ID" + charEnd);
			assertTrue(s, galleryId > 0 && galleryId < iFrom);
			int paintingId = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "PAINTING_ID" + charEnd);
			assertTrue(s, paintingId > 0 && paintingId < iFrom);
			int artistName = s.indexOf(charStart + "t1" + charEnd + "." + charStart + "ARTIST_NAME" + charEnd);
			assertTrue(s, artistName > 0 && artistName < iFrom);
			int artistId = s.indexOf(charStart + "t1" + charEnd + "." + charStart + "ARTIST_ID" + charEnd);
			assertTrue(s, artistId > 0 && artistId < iFrom);
			int dateOfBirth = s.indexOf(charStart + "t1" + charEnd + "." + charStart + "DATE_OF_BIRTH" + charEnd);
			assertTrue(s, dateOfBirth > 0 && dateOfBirth < iFrom);
			int iPainting = s.indexOf(charStart + "PAINTING" + charEnd + " " + charStart + "t0" + charEnd);
			assertTrue(s, iPainting > iFrom);

			int iLeftJoin = s.indexOf("LEFT JOIN");
			assertTrue(s, iLeftJoin > iFrom);
			int iArtist = s.indexOf(charStart + "ARTIST" + charEnd + " " + charStart + "t1" + charEnd);
			assertTrue(s, iArtist > iLeftJoin);
			int iOn = s.indexOf(" ON ");
			assertTrue(s, iOn > iLeftJoin);
			int iArtistId = s.indexOf(charStart + "t0" + charEnd + "." + charStart + "ARTIST_ID" + charEnd, iLeftJoin);
			assertTrue(s, iArtistId > iOn);
			int iArtistIdT1 = s
					.indexOf(charStart + "t1" + charEnd + "." + charStart + "ARTIST_ID" + charEnd, iLeftJoin);
			assertTrue(s, iArtistIdT1 > iOn);
			int i = s.indexOf("=", iLeftJoin);
			assertTrue(s, iArtistIdT1 > i || iArtistId > i);

		} finally {
			DbEntity entity = context.getEntityResolver().getDbEntity("PAINTING");
			entity.getDataMap().setQuotingSQLIdentifiers(false);
		}
	}

	/**
	 * Tests columns generated for a simple object query.
	 */
	@Test
	public void testBuildResultColumns1() throws Exception {
		ObjectSelect<Painting> q = ObjectSelect.query(Painting.class);
		SelectTranslator tr = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver());

		tr.getSql();

		List<ColumnDescriptor> columns = Arrays.asList(tr.getResultColumns());
		columns.sort(Comparator.comparing(ColumnDescriptor::getName));

		DbEntity entity = context.getEntityResolver().getDbEntity("PAINTING");
		List<DbAttribute> attributes = new ArrayList<>(entity.getAttributes());
		attributes.sort(Comparator.comparing(DbAttribute::getName));

		// all DbAttributes must be included
		assertEquals(attributes.size(), columns.size());

		for(int i=0; i<attributes.size(); i++) {
			DbAttribute attribute = attributes.get(i);
			ColumnDescriptor descriptor = columns.get(i);
			assertEquals(attribute, descriptor.getAttribute());
			assertEquals(attribute.getName(), descriptor.getName());
			assertEquals(attribute.getName(), descriptor.getDataRowKey());
			assertEquals(attribute.getType(), descriptor.getJdbcType());
		}
	}

	/**
	 * Tests columns generated for an object query with joint prefetch.
	 */
	@Test
	public void testBuildResultColumns2() throws Exception {
		ObjectSelect<Painting> q = ObjectSelect.query(Painting.class).prefetch(Painting.TO_ARTIST.joint());
		SelectTranslator tr = new DefaultSelectTranslator(q, dataNode.getAdapter(), dataNode.getEntityResolver());

		tr.getSql();

		List<ColumnDescriptor> columns = Arrays.asList(tr.getResultColumns());
		columns.sort(Comparator.comparing(ColumnDescriptor::getName));

		DbEntity rootEntity = context.getEntityResolver().getDbEntity("PAINTING");
		List<DbAttribute> attributes = new ArrayList<>(rootEntity.getAttributes());
		DbEntity joinedEntity = context.getEntityResolver().getDbEntity("ARTIST");
		attributes.addAll(joinedEntity.getAttributes());
		attributes.sort(Comparator.comparing(DbAttribute::getName));

		for(int i=0; i<attributes.size(); i++) {
			DbAttribute attribute = attributes.get(i);
			ColumnDescriptor descriptor = columns.get(i);
			assertEquals(attribute, descriptor.getAttribute());
			assertEquals(attribute.getName(), descriptor.getName());
			if("ARTIST".equals(attribute.getEntity().getName())) {
				assertEquals("toArtist." + attribute.getName(), descriptor.getDataRowKey());
			} else {
				assertEquals(attribute.getName(), descriptor.getDataRowKey());
			}
			assertEquals(attribute.getType(), descriptor.getJdbcType());
		}
	}

	@Test
	public void testAliasedJoins() {
		ObjectSelect<Artist> query = ObjectSelect.query(Artist.class)
				.where(ExpressionFactory.and(
						ExpressionFactory.matchAllExp("|awardArray", 1, 2),
						Artist.AWARD_ARRAY.alias("aw1").dot(Award.NAME).eq("123")
				));
		query.select(context);

		DefaultSelectTranslator translator = new DefaultSelectTranslator(query, dataNode.getAdapter(),
																		 context.getEntityResolver());
		translator.translate();

		int totalJoins = translator.getContext().getTableCount() - 1;
		assertEquals(3, totalJoins);
	}

	@Test
	public void testAliasedJoins_DifferentAliases() {
		ObjectSelect<Artist> query = ObjectSelect.query(Artist.class)
				.where(ExpressionFactory.and(
						Artist.AWARD_ARRAY.alias("aw1").containsId(1),
						Artist.AWARD_ARRAY.alias("aw2").dot(Award.NAME).eq("123")
				));
		query.select(context);

		DefaultSelectTranslator translator = new DefaultSelectTranslator(query, dataNode.getAdapter(),
																		 context.getEntityResolver());
		translator.translate();

		int totalJoins = translator.getContext().getTableCount() - 1;
		assertEquals(2, totalJoins);
	}

	@Test
	public void testAliasedJoins_DifferentProperties() {
		ObjectSelect<Artist> query = ObjectSelect.query(Artist.class)
				.where(ExpressionFactory.and(
						Artist.AWARD_ARRAY.alias("aw1").containsId(1),
						Artist.PAINTING_ARRAY.alias("aw2").dot(Painting.PAINTING_TITLE).eq("123")
				));
		query.select(context);

		DefaultSelectTranslator translator = new DefaultSelectTranslator(query, dataNode.getAdapter(),
																		 context.getEntityResolver());
		translator.translate();

		int totalJoins = translator.getContext().getTableCount() - 1;
		assertEquals(2, totalJoins);
	}

	@Test
	public void testAliasedJoins_FlattenedRelationship() {
		ObjectSelect<Artist> query = ObjectSelect.query(Artist.class)
				.where(ExpressionFactory.and(
						ExpressionFactory.matchAllExp("|groupArray.name", "ag1", "ag2")
				));
		query.select(context);

		DefaultSelectTranslator translator = new DefaultSelectTranslator(query, dataNode.getAdapter(),
																		 context.getEntityResolver());
		translator.translate();

		int totalJoins = translator.getContext().getTableCount() - 1;
		assertEquals(4, totalJoins);
	}
}
