/*
 * JBoss, Home of Professional Open Source.
 * See the COPYRIGHT.txt file distributed with this work for information
 * regarding copyright ownership.  Some portions may be licensed
 * to Red Hat, Inc. under one or more contributor license agreements.
 * 
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This library 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
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
 * 02110-1301 USA.
 */

package org.teiid.query.parser;

import static org.junit.Assert.*;

import java.io.StringReader;
import java.io.UnsupportedEncodingException;
import java.math.BigInteger;
import java.sql.Date;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.List;

import org.junit.Ignore;
import org.junit.Test;
import org.teiid.api.exception.query.QueryParserException;
import org.teiid.client.metadata.ParameterInfo;
import org.teiid.core.TeiidException;
import org.teiid.core.types.DataTypeManager;
import org.teiid.language.SQLConstants;
import org.teiid.language.SQLConstants.NonReserved;
import org.teiid.language.SQLConstants.Reserved;
import org.teiid.language.SortSpecification.NullOrdering;
import org.teiid.query.sql.lang.*;
import org.teiid.query.sql.lang.SetQuery.Operation;
import org.teiid.query.sql.lang.TextTable.TextColumn;
import org.teiid.query.sql.proc.*;
import org.teiid.query.sql.proc.BranchingStatement.BranchingMode;
import org.teiid.query.sql.symbol.*;
import org.teiid.query.sql.util.SymbolMap;

@SuppressWarnings({"nls", "unchecked"})
public class TestParser {

    static void helpTest(String sql, String expectedString, Command expectedCommand) {
        helpTest(sql, expectedString, expectedCommand, new ParseInfo());
    }
	static void helpTest(String sql, String expectedString, Command expectedCommand, ParseInfo info) {
		Command actualCommand = null;
		String actualString = null;
		try {
			actualCommand = QueryParser.getQueryParser().parseCommand(sql, info);
			actualString = actualCommand.toString();
		} catch(Throwable e) { 
		    throw new RuntimeException(e);
		}

		assertEquals("Parse string does not match: ", expectedString, actualString); //$NON-NLS-1$
		assertEquals("Command objects do not match: ", expectedCommand, actualCommand);				 //$NON-NLS-1$
		assertEquals("Cloned command objects do not match: ", expectedCommand, actualCommand.clone());				 //$NON-NLS-1$
	}

	static void helpTest(String sql, String expectedString, Command expectedCommand, ParseInfo info,
			String vdbName, String vdbVersion, String schemaName) throws QueryParserException {
		Command actualCommand = QueryParser.getQueryParser().parseCommand(sql, info, false, vdbName, vdbVersion,
                schemaName, null);
		String actualString = actualCommand.toString();

		assertEquals("Parse string does not match: ", expectedString, actualString); //$NON-NLS-1$
		assertEquals("Command objects do not match: ", expectedCommand, actualCommand);				 //$NON-NLS-1$
		assertEquals("Cloned command objects do not match: ", expectedCommand, actualCommand.clone());				 //$NON-NLS-1$
	}	
	public static void helpTestExpression(String sql, String expectedString, Expression expected) throws QueryParserException {
		Expression	actual = QueryParser.getQueryParser().parseExpression(sql);
		String actualString = actual.toString();

		assertEquals("Parse string does not match: ", expectedString, actualString); //$NON-NLS-1$
		assertEquals("Command objects do not match: ", expected, actual);				 //$NON-NLS-1$
		assertEquals("Cloned command objects do not match: ", expected, actual.clone());				 //$NON-NLS-1$
	}

    static void helpException(String sql) {
        helpException(sql, null);
    }
    
    static void helpException(String sql, String expected){
        try {
            QueryParser.getQueryParser().parseCommand(sql);
            fail("Expected exception for parsing " + sql); //$NON-NLS-1$
        } catch(TeiidException e) {
            if (expected != null) {
                assertEquals(expected, e.getMessage());
            }
        }       
    }
    
    private void helpBlockTest(String block, String expectedString, Block expectedBlock) throws ParseException {
        Block actualBlock = SQLParserUtil.asBlock(new SQLParser(new StringReader(block)).statement(new ParseInfo()));
        String actualString = actualBlock.toString();
        assertEquals("Parse string does not match: ", expectedString, actualString); //$NON-NLS-1$
        assertEquals("Block does not match: ", expectedBlock, actualBlock);              //$NON-NLS-1$
    }
    
    private void helpStmtTest(String stmt, String expectedString, Statement expectedStmt) throws ParseException {
        Statement actualStmt = new SQLParser(new StringReader(stmt)).statement(new ParseInfo());
        String actualString = actualStmt.toString();
        assertEquals("Parse string does not match: ", expectedString, actualString); //$NON-NLS-1$
        assertEquals("Language objects do not match: ", expectedStmt, actualStmt);              //$NON-NLS-1$
    }
        
	// ################################## ACTUAL TESTS ################################

    // ======================== Joins ===============================================

	/** SELECT * FROM g1 inner join g2 on g1.a1=g2.a2 */
	@Test public void testInnerJoin() {
		UnaryFromClause g1 = new UnaryFromClause(new GroupSymbol("g1")); //$NON-NLS-1$
		UnaryFromClause g2 = new UnaryFromClause(new GroupSymbol("g2"));		 //$NON-NLS-1$
		CompareCriteria jcrit = new CompareCriteria(
			new ElementSymbol("g1.a1"), //$NON-NLS-1$
			CompareCriteria.EQ,
			new ElementSymbol("g2.a2")); //$NON-NLS-1$
		ArrayList<Criteria> crits = new ArrayList<Criteria>();
		crits.add(jcrit);		
		JoinPredicate jp = new JoinPredicate(g1, g2, JoinType.JOIN_INNER, crits);
		From from = new From();
		from.addClause(jp);

		MultipleElementSymbol all = new MultipleElementSymbol();
		Select select = new Select();
		select.addSymbol(all);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT * FROM g1 inner join g2 on g1.a1=g2.a2",  //$NON-NLS-1$
				 "SELECT * FROM g1 INNER JOIN g2 ON g1.a1 = g2.a2",  //$NON-NLS-1$
				 query);
	}

	/** SELECT * FROM g1 cross join g2 */
	@Test public void testCrossJoin() {
		UnaryFromClause g1 = new UnaryFromClause(new GroupSymbol("g1")); //$NON-NLS-1$
		UnaryFromClause g2 = new UnaryFromClause(new GroupSymbol("g2")); //$NON-NLS-1$
		JoinPredicate jp = new JoinPredicate(g1, g2, JoinType.JOIN_CROSS);		
		From from = new From();
		from.addClause(jp);	

		MultipleElementSymbol all = new MultipleElementSymbol();
		Select select = new Select();
		select.addSymbol(all);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT * FROM g1 cross join g2",  //$NON-NLS-1$
				 "SELECT * FROM g1 CROSS JOIN g2",  //$NON-NLS-1$
				 query);
	}
	
	/** SELECT * FROM (g1 cross join g2), g3 */
	@Test public void testFromClauses() {
		UnaryFromClause g1 = new UnaryFromClause(new GroupSymbol("g1")); //$NON-NLS-1$
		UnaryFromClause g2 = new UnaryFromClause(new GroupSymbol("g2")); //$NON-NLS-1$
		JoinPredicate jp = new JoinPredicate(g1, g2, JoinType.JOIN_CROSS);		
		From from = new From();
		from.addClause(jp);	
		from.addClause(new UnaryFromClause(new GroupSymbol("g3"))); //$NON-NLS-1$

		MultipleElementSymbol all = new MultipleElementSymbol();
		Select select = new Select();
		select.addSymbol(all);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT * FROM (g1 cross join g2), g3",  //$NON-NLS-1$
				 "SELECT * FROM g1 CROSS JOIN g2, g3",  //$NON-NLS-1$
				 query);
	}

	/** SELECT * FROM g1 inner join g2 */
	@Test public void testInvalidInnerJoin() {
		helpException("SELECT * FROM g1 inner join g2");		 //$NON-NLS-1$
	}

	/** SELECT * FROM (g1 cross join g2) cross join g3 */
	@Test public void testMultiCrossJoin() {
		UnaryFromClause g1 = new UnaryFromClause(new GroupSymbol("g1")); //$NON-NLS-1$
		UnaryFromClause g2 = new UnaryFromClause(new GroupSymbol("g2"));		 //$NON-NLS-1$
		JoinPredicate jp = new JoinPredicate(g1, g2, JoinType.JOIN_CROSS);
		JoinPredicate jp2 = new JoinPredicate(jp, new UnaryFromClause(new GroupSymbol("g3")), JoinType.JOIN_CROSS);		 //$NON-NLS-1$
		From from = new From();
		from.addClause(jp2);	

		MultipleElementSymbol all = new MultipleElementSymbol();
		Select select = new Select();
		select.addSymbol(all);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT * FROM (g1 cross join g2) cross join g3",  //$NON-NLS-1$
				 "SELECT * FROM (g1 CROSS JOIN g2) CROSS JOIN g3",  //$NON-NLS-1$
				 query);
	}

	/** SELECT * FROM (g1 cross join g2) cross join (g3 cross join g4) */
	@Test public void testMultiCrossJoin2() {
		UnaryFromClause g1 = new UnaryFromClause(new GroupSymbol("g1")); //$NON-NLS-1$
		UnaryFromClause g2 = new UnaryFromClause(new GroupSymbol("g2"));		 //$NON-NLS-1$
		JoinPredicate jp = new JoinPredicate(g1, g2, JoinType.JOIN_CROSS);
		UnaryFromClause g3 = new UnaryFromClause(new GroupSymbol("g3")); //$NON-NLS-1$
		UnaryFromClause g4 = new UnaryFromClause(new GroupSymbol("g4"));		 //$NON-NLS-1$
		JoinPredicate jp2 = new JoinPredicate(g3, g4, JoinType.JOIN_CROSS);
		JoinPredicate jp3 = new JoinPredicate(jp, jp2, JoinType.JOIN_CROSS);		
		From from = new From();
		from.addClause(jp3);	

		MultipleElementSymbol all = new MultipleElementSymbol();
		Select select = new Select();
		select.addSymbol(all);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT * FROM (g1 cross join g2) cross join (g3 cross join g4)",  //$NON-NLS-1$
				 "SELECT * FROM (g1 CROSS JOIN g2) CROSS JOIN (g3 CROSS JOIN g4)",  //$NON-NLS-1$
				 query);
	}

	/** SELECT * FROM g1 cross join (g2 cross join g3) */
	@Test public void testMultiCrossJoin3() {
		UnaryFromClause g1 = new UnaryFromClause(new GroupSymbol("g1")); //$NON-NLS-1$
		UnaryFromClause g2 = new UnaryFromClause(new GroupSymbol("g2"));		 //$NON-NLS-1$
		UnaryFromClause g3 = new UnaryFromClause(new GroupSymbol("g3")); //$NON-NLS-1$
		JoinPredicate jp = new JoinPredicate(g2, g3, JoinType.JOIN_CROSS);
		JoinPredicate jp2 = new JoinPredicate(g1, jp, JoinType.JOIN_CROSS);
		From from = new From();
		from.addClause(jp2);	

		MultipleElementSymbol all = new MultipleElementSymbol();
		Select select = new Select();
		select.addSymbol(all);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT * FROM g1 cross join (g2 cross join g3)",  //$NON-NLS-1$
				 "SELECT * FROM g1 CROSS JOIN (g2 CROSS JOIN g3)",  //$NON-NLS-1$
				 query);
	}

	/** SELECT * FROM g1 cross join (g2 cross join g3), g4 */
	@Test public void testMixedJoin() {
		UnaryFromClause g1 = new UnaryFromClause(new GroupSymbol("g1")); //$NON-NLS-1$
		UnaryFromClause g2 = new UnaryFromClause(new GroupSymbol("g2"));		 //$NON-NLS-1$
		UnaryFromClause g3 = new UnaryFromClause(new GroupSymbol("g3")); //$NON-NLS-1$
		JoinPredicate jp = new JoinPredicate(g2, g3, JoinType.JOIN_CROSS);
		JoinPredicate jp2 = new JoinPredicate(g1, jp, JoinType.JOIN_CROSS);
		From from = new From();
		from.addClause(jp2);
		from.addClause(new UnaryFromClause(new GroupSymbol("g4")));	 //$NON-NLS-1$

		MultipleElementSymbol all = new MultipleElementSymbol();
		Select select = new Select();
		select.addSymbol(all);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT * FROM g1 cross join (g2 cross join g3), g4",  //$NON-NLS-1$
				 "SELECT * FROM g1 CROSS JOIN (g2 CROSS JOIN g3), g4",  //$NON-NLS-1$
				 query);
	}

	/** SELECT * FROM g1 cross join (g2 cross join g3), g4, g5 cross join g6 */
	@Test public void testMixedJoin2() {
		UnaryFromClause g1 = new UnaryFromClause(new GroupSymbol("g1")); //$NON-NLS-1$
		UnaryFromClause g2 = new UnaryFromClause(new GroupSymbol("g2"));		 //$NON-NLS-1$
		UnaryFromClause g3 = new UnaryFromClause(new GroupSymbol("g3")); //$NON-NLS-1$
		UnaryFromClause g4 = new UnaryFromClause(new GroupSymbol("g4")); //$NON-NLS-1$
		UnaryFromClause g5 = new UnaryFromClause(new GroupSymbol("g5")); //$NON-NLS-1$
		UnaryFromClause g6 = new UnaryFromClause(new GroupSymbol("g6"));		 //$NON-NLS-1$
		JoinPredicate jp = new JoinPredicate(g2, g3, JoinType.JOIN_CROSS);
		JoinPredicate jp2 = new JoinPredicate(g1, jp, JoinType.JOIN_CROSS);
		JoinPredicate jp3 = new JoinPredicate(g5, g6, JoinType.JOIN_CROSS);
		From from = new From();
		from.addClause(jp2);
		from.addClause(g4);
		from.addClause(jp3);	

		MultipleElementSymbol all = new MultipleElementSymbol();
		Select select = new Select();
		select.addSymbol(all);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT * FROM g1 cross join (g2 cross join g3), g4, g5 cross join g6",  //$NON-NLS-1$
				 "SELECT * FROM g1 CROSS JOIN (g2 CROSS JOIN g3), g4, g5 CROSS JOIN g6",  //$NON-NLS-1$
				 query);
	}
	
	/** SELECT * FROM g1, g2 inner join g3 on g2.a=g3.a */
	@Test public void testMixedJoin3() {
		UnaryFromClause g1 = new UnaryFromClause(new GroupSymbol("g1")); //$NON-NLS-1$
		UnaryFromClause g2 = new UnaryFromClause(new GroupSymbol("g2"));		 //$NON-NLS-1$
		UnaryFromClause g3 = new UnaryFromClause(new GroupSymbol("g3")); //$NON-NLS-1$
		CompareCriteria jcrit = new CompareCriteria(
			new ElementSymbol("g2.a"), //$NON-NLS-1$
			CompareCriteria.EQ,
			new ElementSymbol("g3.a")); //$NON-NLS-1$
		ArrayList<Criteria> crits = new ArrayList<Criteria>();
		crits.add(jcrit);		
		JoinPredicate jp = new JoinPredicate(g2, g3, JoinType.JOIN_INNER, crits);
		From from = new From();
		from.addClause(g1);
		from.addClause(jp);

		MultipleElementSymbol all = new MultipleElementSymbol();
		Select select = new Select();
		select.addSymbol(all);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT * FROM g1, g2 inner join g3 on g2.a=g3.a",  //$NON-NLS-1$
				 "SELECT * FROM g1, g2 INNER JOIN g3 ON g2.a = g3.a",  //$NON-NLS-1$
				 query);
	}
	
	/** Select myG.a myA, myH.b from g myG right outer join h myH on myG.x=myH.x */
	@Test public void testRightOuterJoinWithAliases() {
		UnaryFromClause g = new UnaryFromClause(new GroupSymbol("myG", "g")); //$NON-NLS-1$ //$NON-NLS-2$
		UnaryFromClause h = new UnaryFromClause(new GroupSymbol("myH", "h"));		 //$NON-NLS-1$ //$NON-NLS-2$
		CompareCriteria jcrit = new CompareCriteria(
			new ElementSymbol("myG.x"), //$NON-NLS-1$
			CompareCriteria.EQ,
			new ElementSymbol("myH.x")); //$NON-NLS-1$
		ArrayList<Criteria> crits = new ArrayList<Criteria>();
		crits.add(jcrit);		
		JoinPredicate jp = new JoinPredicate(g, h, JoinType.JOIN_RIGHT_OUTER, crits);
		From from = new From();
		from.addClause(jp);

		AliasSymbol as = new AliasSymbol("myA", new ElementSymbol("myG.a")); //$NON-NLS-1$ //$NON-NLS-2$
		Select select = new Select();
		select.addSymbol(as);
		select.addSymbol(new ElementSymbol("myH.b")); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("Select myG.a myA, myH.b from g myG right outer join h myH on myG.x=myH.x",  //$NON-NLS-1$
				 "SELECT myG.a AS myA, myH.b FROM g AS myG RIGHT OUTER JOIN h AS myH ON myG.x = myH.x",  //$NON-NLS-1$
				 query);
	}
	
	/** Select myG.x myX, myH.y from g myG right join h myH on myG.x=myH.x */
	@Test public void testRightJoinWithAliases() {
		UnaryFromClause g = new UnaryFromClause(new GroupSymbol("myG", "g")); //$NON-NLS-1$ //$NON-NLS-2$
		UnaryFromClause h = new UnaryFromClause(new GroupSymbol("myH", "h"));		 //$NON-NLS-1$ //$NON-NLS-2$
		CompareCriteria jcrit = new CompareCriteria(
			new ElementSymbol("myG.x"), //$NON-NLS-1$
			CompareCriteria.EQ,
			new ElementSymbol("myH.x")); //$NON-NLS-1$
		ArrayList<Criteria> crits = new ArrayList<Criteria>();
		crits.add(jcrit);		
		JoinPredicate jp = new JoinPredicate(g, h, JoinType.JOIN_RIGHT_OUTER, crits);
		From from = new From();
		from.addClause(jp);

		AliasSymbol as = new AliasSymbol("myA", new ElementSymbol("myG.a")); //$NON-NLS-1$ //$NON-NLS-2$
		Select select = new Select();
		select.addSymbol(as);
		select.addSymbol(new ElementSymbol("myH.b")); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("Select myG.a myA, myH.b from g myG right join h myH on myG.x=myH.x",  //$NON-NLS-1$
				 "SELECT myG.a AS myA, myH.b FROM g AS myG RIGHT OUTER JOIN h AS myH ON myG.x = myH.x",  //$NON-NLS-1$
				 query);
	}
	
	/** Select myG.a myA, myH.b from g myG left outer join h myH on myG.x=myH.x */
	@Test public void testLeftOuterJoinWithAliases() {
		UnaryFromClause g = new UnaryFromClause(new GroupSymbol("myG", "g")); //$NON-NLS-1$ //$NON-NLS-2$
		UnaryFromClause h = new UnaryFromClause(new GroupSymbol("myH", "h"));		 //$NON-NLS-1$ //$NON-NLS-2$
		CompareCriteria jcrit = new CompareCriteria(
			new ElementSymbol("myG.x"), //$NON-NLS-1$
			CompareCriteria.EQ,
			new ElementSymbol("myH.x")); //$NON-NLS-1$
		ArrayList<Criteria> crits = new ArrayList<Criteria>();
		crits.add(jcrit);		
		JoinPredicate jp = new JoinPredicate(g, h, JoinType.JOIN_LEFT_OUTER, crits);
		From from = new From();
		from.addClause(jp);

		AliasSymbol as = new AliasSymbol("myA", new ElementSymbol("myG.a")); //$NON-NLS-1$ //$NON-NLS-2$
		Select select = new Select();
		select.addSymbol(as);
		select.addSymbol(new ElementSymbol("myH.b")); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("Select myG.a myA, myH.b from g myG left outer join h myH on myG.x=myH.x",  //$NON-NLS-1$
				 "SELECT myG.a AS myA, myH.b FROM g AS myG LEFT OUTER JOIN h AS myH ON myG.x = myH.x",  //$NON-NLS-1$
				 query);
	}	

	/** Select myG.a myA, myH.b from g myG left join h myH on myG.x=myH.x */
	@Test public void testLeftJoinWithAliases() {
		UnaryFromClause g = new UnaryFromClause(new GroupSymbol("myG", "g")); //$NON-NLS-1$ //$NON-NLS-2$
		UnaryFromClause h = new UnaryFromClause(new GroupSymbol("myH", "h"));		 //$NON-NLS-1$ //$NON-NLS-2$
		CompareCriteria jcrit = new CompareCriteria(
			new ElementSymbol("myG.x"), //$NON-NLS-1$
			CompareCriteria.EQ,
			new ElementSymbol("myH.x")); //$NON-NLS-1$
		ArrayList<Criteria> crits = new ArrayList<Criteria>();
		crits.add(jcrit);		
		JoinPredicate jp = new JoinPredicate(g, h, JoinType.JOIN_LEFT_OUTER, crits);
		From from = new From();
		from.addClause(jp);

		AliasSymbol as = new AliasSymbol("myA", new ElementSymbol("myG.a")); //$NON-NLS-1$ //$NON-NLS-2$
		Select select = new Select();
		select.addSymbol(as);
		select.addSymbol(new ElementSymbol("myH.b")); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("Select myG.a myA, myH.b from g myG left join h myH on myG.x=myH.x",  //$NON-NLS-1$
				 "SELECT myG.a AS myA, myH.b FROM g AS myG LEFT OUTER JOIN h AS myH ON myG.x = myH.x",  //$NON-NLS-1$
				 query);
	}	
	
	/** Select myG.a myA, myH.b from g myG full outer join h myH on myG.x=myH.x */
	@Test public void testFullOuterJoinWithAliases() {
		UnaryFromClause g = new UnaryFromClause(new GroupSymbol("myG", "g")); //$NON-NLS-1$ //$NON-NLS-2$
		UnaryFromClause h = new UnaryFromClause(new GroupSymbol("myH", "h"));		 //$NON-NLS-1$ //$NON-NLS-2$
		CompareCriteria jcrit = new CompareCriteria(
			new ElementSymbol("myG.x"), //$NON-NLS-1$
			CompareCriteria.EQ,
			new ElementSymbol("myH.x")); //$NON-NLS-1$
		ArrayList<Criteria> crits = new ArrayList<Criteria>();
		crits.add(jcrit);		
		JoinPredicate jp = new JoinPredicate(g, h, JoinType.JOIN_FULL_OUTER, crits);
		From from = new From();
		from.addClause(jp);

		AliasSymbol as = new AliasSymbol("myA", new ElementSymbol("myG.a")); //$NON-NLS-1$ //$NON-NLS-2$
		Select select = new Select();
		select.addSymbol(as);
		select.addSymbol(new ElementSymbol("myH.b")); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("Select myG.a myA, myH.b from g myG full outer join h myH on myG.x=myH.x",  //$NON-NLS-1$
				 "SELECT myG.a AS myA, myH.b FROM g AS myG FULL OUTER JOIN h AS myH ON myG.x = myH.x",  //$NON-NLS-1$
				 query);
	}
	
	/** Select g.a, h.b from g full join h on g.x=h.x */
	@Test public void testFullJoin() {
		UnaryFromClause g = new UnaryFromClause(new GroupSymbol("g")); //$NON-NLS-1$
		UnaryFromClause h = new UnaryFromClause(new GroupSymbol("h"));		 //$NON-NLS-1$
		CompareCriteria jcrit = new CompareCriteria(
			new ElementSymbol("g.x"), //$NON-NLS-1$
			CompareCriteria.EQ,
			new ElementSymbol("h.x")); //$NON-NLS-1$
		ArrayList<Criteria> crits = new ArrayList<Criteria>();
		crits.add(jcrit);		
		JoinPredicate jp = new JoinPredicate(g, h, JoinType.JOIN_FULL_OUTER, crits);
		From from = new From();
		from.addClause(jp);

		Select select = new Select();
		select.addSymbol(new ElementSymbol("g.a")); //$NON-NLS-1$
		select.addSymbol(new ElementSymbol("h.b")); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("Select g.a, h.b from g full join h on g.x=h.x",  //$NON-NLS-1$
				 "SELECT g.a, h.b FROM g FULL OUTER JOIN h ON g.x = h.x",  //$NON-NLS-1$
				 query);
	}
	
    // ======================= Convert ==============================================

	/** SELECT CONVERT(a, string) FROM g */
	@Test public void testConversionFunction() {
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Function f = new Function("CONVERT", new Expression[] {new ElementSymbol("a", false), new Constant("string")}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
		Select select = new Select();
		select.addSymbol(f);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT CONVERT(a, string) FROM g",  //$NON-NLS-1$
				 "SELECT CONVERT(a, string) FROM g",  //$NON-NLS-1$
				 query);
	}

	/** SELECT CONVERT(CONVERT(a, timestamp), string) FROM g */
	@Test public void testConversionFunction2() {
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Function f = new Function("CONVERT", new Expression[] {new ElementSymbol("a", false), new Constant("timestamp")}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
		Function f2 = new Function("CONVERT", new Expression[] {f, new Constant("string")}); //$NON-NLS-1$ //$NON-NLS-2$
		Select select = new Select();
		select.addSymbol(f2);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT CONVERT(CONVERT(a, timestamp), string) FROM g",  //$NON-NLS-1$
				 "SELECT CONVERT(CONVERT(a, timestamp), string) FROM g",  //$NON-NLS-1$
				 query);
	}

    // ======================= Functions ==============================================

	/** SELECT 5 + length(concat(a, 'x')) FROM g */
	@Test public void testMultiFunction() {
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Function f = new Function("concat", new Expression[] {new ElementSymbol("a", false), new Constant("x")}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
		Function f2 = new Function("length", new Expression[] {f}); //$NON-NLS-1$
		Function f3 = new Function("+", new Expression[] {new Constant(new Integer(5)), f2}); //$NON-NLS-1$
		Select select = new Select();
		select.addSymbol(f3);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT 5 + length(concat(a, 'x')) FROM g",  //$NON-NLS-1$
				 "SELECT (5 + length(concat(a, 'x'))) FROM g",  //$NON-NLS-1$
				 query);
	}
	
	@Test public void testSignedExpression() {
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Function f = new Function("*", new Expression[] {new Constant(-1), new ElementSymbol("x")});
		Select select = new Select();
		select.addSymbol(f);
		select.addSymbol(new ElementSymbol("x"));
		select.addSymbol(new Constant(5));

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT -x, +x, +5 FROM g",  //$NON-NLS-1$
				 "SELECT (-1 * x), x, 5 FROM g",  //$NON-NLS-1$
				 query);
	}

	/** SELECT REPLACE(a, 'x', 'y') AS y FROM g */
	@Test public void testAliasedFunction() {
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Function f = new Function("replace", new Expression[] {new ElementSymbol("a", false), new Constant("x"), new Constant("y")}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
		AliasSymbol as = new AliasSymbol("y", f); //$NON-NLS-1$
		Select select = new Select();
		select.addSymbol(as);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT REPLACE(a, 'x', 'y') AS y FROM g",  //$NON-NLS-1$
				 "SELECT REPLACE(a, 'x', 'y') AS y FROM g",  //$NON-NLS-1$
				 query);
	}

	/** SELECT cast(a as string) FROM g */
	@Test public void testCastFunction() {
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Function f = new Function("cast", new Expression[] {new ElementSymbol("a", false), new Constant("string")}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
		Select select = new Select();
		select.addSymbol(f);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT cast(a as string) FROM g",  //$NON-NLS-1$
				 "SELECT cast(a AS string) FROM g",  //$NON-NLS-1$
				 query);
	}

	/** SELECT cast(cast(a as timestamp) as string) FROM g */
	@Test public void testMultiCastFunction() {
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Function f = new Function("cast", new Expression[] {new ElementSymbol("a", false), new Constant("timestamp")}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ 
		Function f2 = new Function("cast", new Expression[] {f, new Constant("string")}); //$NON-NLS-1$ //$NON-NLS-2$
		Select select = new Select();
		select.addSymbol(f2);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT cast(cast(a as timestamp) as string) FROM g",  //$NON-NLS-1$
				 "SELECT cast(cast(a AS timestamp) AS string) FROM g",  //$NON-NLS-1$
				 query);
	}

    /** SELECT left(fullname, 3) as x FROM sys.groups */
    @Test public void testLeftFunction() {
        GroupSymbol g = new GroupSymbol("sys.groups"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("left", new Expression[] {new ElementSymbol("fullname", false), new Constant(new Integer(3))}); //$NON-NLS-1$ //$NON-NLS-2$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT left(fullname, 3) as x FROM sys.groups",  //$NON-NLS-1$
                 "SELECT left(fullname, 3) AS x FROM sys.groups",  //$NON-NLS-1$
                 query);
    }

    /** SELECT right(fullname, 3) as x FROM sys.groups */
    @Test public void testRightFunction() {
        GroupSymbol g = new GroupSymbol("sys.groups"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("right", new Expression[] {new ElementSymbol("fullname", false), new Constant(new Integer(3))}); //$NON-NLS-1$ //$NON-NLS-2$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT right(fullname, 3) as x FROM sys.groups",  //$NON-NLS-1$
                 "SELECT right(fullname, 3) AS x FROM sys.groups",  //$NON-NLS-1$
                 query);
    }

    /** SELECT char('x') AS x FROM sys.groups */
    @Test public void testCharFunction() {
        GroupSymbol g = new GroupSymbol("sys.groups"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("char", new Expression[] { new Constant("x")}); //$NON-NLS-1$ //$NON-NLS-2$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT char('x') AS x FROM sys.groups",  //$NON-NLS-1$
                 "SELECT char('x') AS x FROM sys.groups",  //$NON-NLS-1$
                 query);
    }

    /** SELECT insert('x', 1, 'a') as x FROM sys.groups */
    @Test public void testInsertFunction() {
        GroupSymbol g = new GroupSymbol("sys.groups"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("insert", new Expression[] { new Constant("x"), new Constant(new Integer(1)), new Constant("a")}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT insert('x', 1, 'a') AS x FROM sys.groups",  //$NON-NLS-1$
                 "SELECT insert('x', 1, 'a') AS x FROM sys.groups",  //$NON-NLS-1$
                 query);
    }


    
    @Test public void testInsertIntoSelect() {
        GroupSymbol g = new GroupSymbol("sys.groups"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Insert insert = new Insert();
        GroupSymbol groupSymbol = new GroupSymbol( "tempA" );   //$NON-NLS-1$
        insert.setGroup(groupSymbol);
        
        Select select = new Select();
        select.addSymbol( new Constant( 1 ) );    //$NON-NLS-1$

        Query query = new Query();
        query.setSelect(select);
        
        insert.setQueryExpression( query );        

        helpTest("insert into tempA SELECT 1",  //$NON-NLS-1$
                 "INSERT INTO tempA SELECT 1",  //$NON-NLS-1$
                 insert);
    }

    /** SELECT translate('x', 'x', 'y') FROM sys.groups */
    @Test public void testTranslateFunction() {
        GroupSymbol g = new GroupSymbol("sys.groups"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("translate", new Expression[] { new Constant("x"), new Constant("x"), new Constant("y")}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
        Select select = new Select();
        select.addSymbol(f);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT translate('x', 'x', 'y') FROM sys.groups",  //$NON-NLS-1$
                 "SELECT translate('x', 'x', 'y') FROM sys.groups",  //$NON-NLS-1$
                 query);
    }

    /** SELECT timestampadd(SQL_TSI_FRAC_SECOND, 10, '2003-05-01 10:20:30') as x FROM my.group1 */
    @Test public void testTimestampaddFunctionFracSecond() {
        GroupSymbol g = new GroupSymbol("my.group1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("timestampadd", new Expression[] { //$NON-NLS-1$
            new Constant("SQL_TSI_FRAC_SECOND"), new Constant(new Integer(10)), new Constant("2003-05-01 10:20:30")}); //$NON-NLS-1$ //$NON-NLS-2$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT timestampadd(SQL_TSI_FRAC_SECOND, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 "SELECT timestampadd(SQL_TSI_FRAC_SECOND, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 query);
    }

    /** SELECT timestampadd(SQL_TSI_SECOND, 10, '2003-05-01 10:20:30') as x FROM my.group1 */
    @Test public void testTimestampaddFunctionSecond() {
        GroupSymbol g = new GroupSymbol("my.group1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("timestampadd", new Expression[] { //$NON-NLS-1$
            new Constant("SQL_TSI_SECOND"), new Constant(new Integer(10)), new Constant("2003-05-01 10:20:30")}); //$NON-NLS-1$ //$NON-NLS-2$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT timestampadd(SQL_TSI_SECOND, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 "SELECT timestampadd(SQL_TSI_SECOND, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 query);
    }

    /** SELECT timestampadd(SQL_TSI_MINUTE, 10, '2003-05-01 10:20:30') as x FROM my.group1 */
    @Test public void testTimestampaddFunctionMinute() {
        GroupSymbol g = new GroupSymbol("my.group1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("timestampadd", new Expression[] { //$NON-NLS-1$
            new Constant("SQL_TSI_MINUTE"), new Constant(new Integer(10)), new Constant("2003-05-01 10:20:30")}); //$NON-NLS-1$ //$NON-NLS-2$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT timestampadd(SQL_TSI_MINUTE, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 "SELECT timestampadd(SQL_TSI_MINUTE, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 query);
    }

    /** SELECT timestampadd(SQL_TSI_HOUR, 10, '2003-05-01 10:20:30') as x FROM my.group1 */
    @Test public void testTimestampaddFunctionHour() {
        GroupSymbol g = new GroupSymbol("my.group1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("timestampadd", new Expression[] { //$NON-NLS-1$
            new Constant("SQL_TSI_HOUR"), new Constant(new Integer(10)), new Constant("2003-05-01 10:20:30")}); //$NON-NLS-1$ //$NON-NLS-2$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT timestampadd(SQL_TSI_HOUR, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 "SELECT timestampadd(SQL_TSI_HOUR, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 query);
    }

    /** SELECT timestampadd(SQL_TSI_DAY, 10, '2003-05-01 10:20:30') as x FROM my.group1 */
    @Test public void testTimestampaddFunctionDay() {
        GroupSymbol g = new GroupSymbol("my.group1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("timestampadd", new Expression[] { //$NON-NLS-1$
            new Constant("SQL_TSI_DAY"), new Constant(new Integer(10)), new Constant("2003-05-01 10:20:30")}); //$NON-NLS-1$ //$NON-NLS-2$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT timestampadd(SQL_TSI_DAY, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 "SELECT timestampadd(SQL_TSI_DAY, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 query);
    }

    /** SELECT timestampadd(SQL_TSI_WEEK, 10, '2003-05-01 10:20:30') as x FROM my.group1 */
    @Test public void testTimestampaddFunctionWeek() {
        GroupSymbol g = new GroupSymbol("my.group1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("timestampadd", new Expression[] { //$NON-NLS-1$
            new Constant("SQL_TSI_WEEK"), new Constant(new Integer(10)), new Constant("2003-05-01 10:20:30")}); //$NON-NLS-1$ //$NON-NLS-2$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT timestampadd(SQL_TSI_WEEK, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 "SELECT timestampadd(SQL_TSI_WEEK, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 query);
    }

    /** SELECT timestampadd(SQL_TSI_QUARTER, 10, '2003-05-01 10:20:30') as x FROM my.group1 */
    @Test public void testTimestampaddFunctionQuarter() {
        GroupSymbol g = new GroupSymbol("my.group1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("timestampadd", new Expression[] { //$NON-NLS-1$
            new Constant("SQL_TSI_QUARTER"), new Constant(new Integer(10)), new Constant("2003-05-01 10:20:30")}); //$NON-NLS-1$ //$NON-NLS-2$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT timestampadd(SQL_TSI_QUARTER, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 "SELECT timestampadd(SQL_TSI_QUARTER, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 query);
    }

    /** SELECT timestampadd(SQL_TSI_YEAR, 10, '2003-05-01 10:20:30') as x FROM my.group1 */
    @Test public void testTimestampaddFunctionYear() {
        GroupSymbol g = new GroupSymbol("my.group1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("timestampadd", new Expression[] { //$NON-NLS-1$
            new Constant("SQL_TSI_YEAR"), new Constant(new Integer(10)), new Constant("2003-05-01 10:20:30")}); //$NON-NLS-1$ //$NON-NLS-2$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT timestampadd(SQL_TSI_YEAR, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 "SELECT timestampadd(SQL_TSI_YEAR, 10, '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 query);
    }

    /** SELECT timestampdiff(SQL_TSI_FRAC_SECOND, '2003-05-01 10:20:10', '2003-05-01 10:20:30') as x FROM my.group1 */
    @Test public void testTimestampdiffFunctionFracSecond() {
        GroupSymbol g = new GroupSymbol("my.group1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("timestampdiff", new Expression[] { //$NON-NLS-1$
            new Constant("SQL_TSI_FRAC_SECOND"), new Constant("2003-05-01 10:20:10"), new Constant("2003-05-01 10:20:30")}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        AliasSymbol as = new AliasSymbol("x", f); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(as);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT timestampdiff(SQL_TSI_FRAC_SECOND, '2003-05-01 10:20:10', '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 "SELECT timestampdiff(SQL_TSI_FRAC_SECOND, '2003-05-01 10:20:10', '2003-05-01 10:20:30') AS x FROM my.group1",  //$NON-NLS-1$
                 query);
    }
    
    /** SELECT 5 + 2 + 3 FROM g */
    @Test public void testArithmeticOperatorPrecedence1() {
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("+", new Expression[] {new Constant(new Integer(5)), new Constant(new Integer(2))}); //$NON-NLS-1$
        Function f2 = new Function("+", new Expression[] {f, new Constant(new Integer(3))}); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(f2);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT 5 + 2 + 3 FROM g",  //$NON-NLS-1$
                 "SELECT ((5 + 2) + 3) FROM g",  //$NON-NLS-1$
                 query);
    }

    /** SELECT 5 + 2 - 3 FROM g */
    @Test public void testArithmeticOperatorPrecedence2() {
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("+", new Expression[] {new Constant(new Integer(5)), new Constant(new Integer(2))}); //$NON-NLS-1$
        Function f2 = new Function("-", new Expression[] {f, new Constant(new Integer(3))}); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(f2);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT 5 + 2 - 3 FROM g",  //$NON-NLS-1$
                 "SELECT ((5 + 2) - 3) FROM g",  //$NON-NLS-1$
                 query);
    }

    /** SELECT 5 + 2 * 3 FROM g */
    @Test public void testArithmeticOperatorPrecedence3() {
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("*", new Expression[] {new Constant(new Integer(2)), new Constant(new Integer(3))}); //$NON-NLS-1$
        Function f2 = new Function("+", new Expression[] {new Constant(new Integer(5)), f}); //$NON-NLS-1$
        
        Select select = new Select();
        select.addSymbol(f2);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT 5 + 2 * 3 FROM g",  //$NON-NLS-1$
                 "SELECT (5 + (2 * 3)) FROM g",  //$NON-NLS-1$
                 query);
    }

    /** SELECT 5 * 2 + 3 FROM g */
    @Test public void testArithmeticOperatorPrecedence4() {
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("*", new Expression[] {new Constant(new Integer(5)), new Constant(new Integer(2))}); //$NON-NLS-1$
        Function f2 = new Function("+", new Expression[] {f, new Constant(new Integer(3))}); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(f2);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT 5 * 2 + 3 FROM g",  //$NON-NLS-1$
                 "SELECT ((5 * 2) + 3) FROM g",  //$NON-NLS-1$
                 query);
    }

    /** SELECT 5 * 2 * 3 FROM g */
    @Test public void testArithmeticOperatorPrecedence5() {
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("*", new Expression[] {new Constant(new Integer(5)), new Constant(new Integer(2))}); //$NON-NLS-1$
        Function f2 = new Function("*", new Expression[] {f, new Constant(new Integer(3))}); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(f2);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT 5 * 2 * 3 FROM g",  //$NON-NLS-1$
                 "SELECT ((5 * 2) * 3) FROM g",  //$NON-NLS-1$
                 query);
    }

    /** SELECT 1 + 2 * 3 + 4 * 5 FROM g */
    @Test public void testArithmeticOperatorPrecedenceMixed1() {
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("*", new Expression[] {new Constant(new Integer(2)), new Constant(new Integer(3))}); //$NON-NLS-1$
        Function f2 = new Function("*", new Expression[] {new Constant(new Integer(4)), new Constant(new Integer(5))}); //$NON-NLS-1$
        Function f3 = new Function("+", new Expression[] {new Constant(new Integer(1)), f}); //$NON-NLS-1$
        Function f4 = new Function("+", new Expression[] {f3, f2}); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(f4);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT 1 + 2 * 3 + 4 * 5 FROM g",  //$NON-NLS-1$
                 "SELECT ((1 + (2 * 3)) + (4 * 5)) FROM g",  //$NON-NLS-1$
                 query);
    }

    /** SELECT 1 * 2 + 3 * 4 + 5 FROM g */
    @Test public void testArithmeticOperatorPrecedenceMixed2() {
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("*", new Expression[] {new Constant(new Integer(1)), new Constant(new Integer(2))}); //$NON-NLS-1$
        Function f2 = new Function("*", new Expression[] {new Constant(new Integer(3)), new Constant(new Integer(4))}); //$NON-NLS-1$
        Function f3 = new Function("+", new Expression[] {f, f2}); //$NON-NLS-1$
        Function f4 = new Function("+", new Expression[] {f3, new Constant(new Integer(5))}); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(f4);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT 1 * 2 + 3 * 4 + 5 FROM g",  //$NON-NLS-1$
                 "SELECT (((1 * 2) + (3 * 4)) + 5) FROM g",  //$NON-NLS-1$
                 query);
    }

    /** SELECT 5 - 4 - 3 - 2 FROM g --> SELECT ((5 - 4) - 3) - 2 FROM g */
    @Test public void testLeftAssociativeExpressions1() {
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("-", new Expression[] {new Constant(new Integer(5)), new Constant(new Integer(4))}); //$NON-NLS-1$
        Function f2 = new Function("-", new Expression[] {f, new Constant(new Integer(3))}); //$NON-NLS-1$
        Function f3 = new Function("-", new Expression[] {f2, new Constant(new Integer(2))}); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(f3);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT 5 - 4 - 3 - 2 FROM g",  //$NON-NLS-1$
                 "SELECT (((5 - 4) - 3) - 2) FROM g",  //$NON-NLS-1$
                 query);
    }

    /** SELECT 5 / 4 / 3 / 2 FROM g --> SELECT ((5 / 4) / 3) / 2 FROM g */
    @Test public void testLeftAssociativeExpressions2() {
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("/", new Expression[] {new Constant(new Integer(5)), new Constant(new Integer(4))}); //$NON-NLS-1$
        Function f2 = new Function("/", new Expression[] {f, new Constant(new Integer(3))}); //$NON-NLS-1$
        Function f3 = new Function("/", new Expression[] {f2, new Constant(new Integer(2))}); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(f3);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT 5 / 4 / 3 / 2 FROM g",  //$NON-NLS-1$
                 "SELECT (((5 / 4) / 3) / 2) FROM g",  //$NON-NLS-1$
                 query);
    }

    /** SELECT 'a' || 'b' || 'c' FROM g */
    @Test public void testConcatOperator1() {
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("||", new Expression[] {new Constant("a"), new Constant("b")}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        Function f2 = new Function("||", new Expression[] {f, new Constant("c")}); //$NON-NLS-1$ //$NON-NLS-2$
        Select select = new Select();
        select.addSymbol(f2);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT 'a' || 'b' || 'c' FROM g",  //$NON-NLS-1$
                 "SELECT (('a' || 'b') || 'c') FROM g",  //$NON-NLS-1$
                 query);
    }

    /** SELECT 2 + 3 || 5 + 1 * 2 FROM g */
    @Test public void testMixedOperators1() {
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Function f = new Function("*", new Expression[] {new Constant(new Integer(1)), new Constant(new Integer(2))}); //$NON-NLS-1$
        Function f2 = new Function("+", new Expression[] {new Constant(new Integer(5)), f}); //$NON-NLS-1$
        Function f3 = new Function("+", new Expression[] {new Constant(new Integer(2)), new Constant(new Integer(3))}); //$NON-NLS-1$
        Function f4 = new Function("||", new Expression[] {f3, f2}); //$NON-NLS-1$
        Select select = new Select();
        select.addSymbol(f4);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT 2 + 3 || 5 + 1 * 2 FROM g",  //$NON-NLS-1$
                 "SELECT ((2 + 3) || (5 + (1 * 2))) FROM g",  //$NON-NLS-1$
                 query);
    }

    // ======================= Group By ==============================================
	
	/** SELECT a FROM m.g GROUP BY b, c */
	@Test public void testGroupBy() {
		GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new ElementSymbol("a", false)); //$NON-NLS-1$
		
		GroupBy groupBy = new GroupBy();
		groupBy.addSymbol(new ElementSymbol("b", false));		 //$NON-NLS-1$
		groupBy.addSymbol(new ElementSymbol("c", false)); //$NON-NLS-1$
		

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setGroupBy(groupBy);
		helpTest("SELECT a FROM m.g GROUP BY b, c",  //$NON-NLS-1$
				 "SELECT a FROM m.g GROUP BY b, c",  //$NON-NLS-1$
				 query);
	}
	
	@Test public void testGroupByRollup() {
		GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new ElementSymbol("a", false)); //$NON-NLS-1$
		
		GroupBy groupBy = new GroupBy();
		groupBy.setRollup(true);
		groupBy.addSymbol(new ElementSymbol("b", false));		 //$NON-NLS-1$
		groupBy.addSymbol(new ElementSymbol("c", false)); //$NON-NLS-1$
		

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setGroupBy(groupBy);
		helpTest("SELECT a FROM m.g GROUP BY rollup(b, c)",  //$NON-NLS-1$
				 "SELECT a FROM m.g GROUP BY ROLLUP(b, c)",  //$NON-NLS-1$
				 query);
	}

	/** SELECT a FROM m.g GROUP BY b, c HAVING b=5*/
	@Test public void testGroupByHaving() {
		GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new ElementSymbol("a", false)); //$NON-NLS-1$
		
		GroupBy groupBy = new GroupBy();
		groupBy.addSymbol(new ElementSymbol("b", false));		 //$NON-NLS-1$
		groupBy.addSymbol(new ElementSymbol("c", false)); //$NON-NLS-1$
		
		CompareCriteria having = new CompareCriteria(new ElementSymbol("b", false), CompareCriteria.EQ, new Constant(new Integer(5))); //$NON-NLS-1$
		
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setGroupBy(groupBy);
		query.setHaving(having);
		helpTest("SELECT a FROM m.g GROUP BY b, c HAVING b=5",  //$NON-NLS-1$
				 "SELECT a FROM m.g GROUP BY b, c HAVING b = 5",  //$NON-NLS-1$
				 query);
	}
	
	/** SELECT COUNT(a) AS c FROM m.g */
	@Test public void testAggregateFunction() {
		GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new AliasSymbol("c",  //$NON-NLS-1$
			new AggregateSymbol("COUNT", false, new ElementSymbol("a", false)))); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
		
				
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT COUNT(a) AS c FROM m.g",  //$NON-NLS-1$
				 "SELECT COUNT(a) AS c FROM m.g",  //$NON-NLS-1$
				 query);
	}

    /** SELECT (COUNT(a)) AS c FROM m.g - this kind of query is generated by ODBC sometimes */
    @Test public void testAggregateFunctionWithParens() {
        GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new AliasSymbol("c",  //$NON-NLS-1$
            new AggregateSymbol("COUNT", false, new ElementSymbol("a", false)))); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        
                
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT (COUNT(a)) AS c FROM m.g",  //$NON-NLS-1$
                 "SELECT COUNT(a) AS c FROM m.g",  //$NON-NLS-1$
                 query);
    }

	/** SELECT a FROM m.g GROUP BY a HAVING COUNT(b) > 0*/
	@Test public void testHavingFunction() {
		GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$
		
		GroupBy groupBy = new GroupBy();
		groupBy.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$
		
		Criteria having = new CompareCriteria(
			new AggregateSymbol("COUNT", false, new ElementSymbol("b", false)), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
			CompareCriteria.GT,
			new Constant(new Integer(0)) );
				
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setGroupBy(groupBy);
		query.setHaving(having);
		
		helpTest("SELECT a FROM m.g GROUP BY a HAVING COUNT(b) > 0",  //$NON-NLS-1$
				 "SELECT a FROM m.g GROUP BY a HAVING COUNT(b) > 0",  //$NON-NLS-1$
				 query);
	}

	/** SELECT a FROM m.g GROUP BY a, b HAVING COUNT(b) > 0 AND b+5 > 0 */
	@Test public void testCompoundHaving() {
		GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$
		
		GroupBy groupBy = new GroupBy();
		groupBy.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$
		groupBy.addSymbol(new ElementSymbol("b")); //$NON-NLS-1$
		
		CompoundCriteria having = new CompoundCriteria();
		having.setOperator(CompoundCriteria.AND);
		having.addCriteria(new CompareCriteria(
			new AggregateSymbol("COUNT", false, new ElementSymbol("b", false)), //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
			CompareCriteria.GT,
			new Constant(new Integer(0)) ));
		having.addCriteria(new CompareCriteria(
			new Function("+", new Expression[] { new ElementSymbol("b", false), new Constant(new Integer(5)) }), //$NON-NLS-1$ //$NON-NLS-2$
			CompareCriteria.GT,
			new Constant(new Integer(0)) ));
				
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setGroupBy(groupBy);
		query.setHaving(having);
		
		helpTest("SELECT a FROM m.g GROUP BY a, b HAVING COUNT(b) > 0 AND b+5 > 0",  //$NON-NLS-1$
				 "SELECT a FROM m.g GROUP BY a, b HAVING (COUNT(b) > 0) AND ((b + 5) > 0)",  //$NON-NLS-1$
				 query);
	}

	/** SELECT a FROM m.g GROUP BY a, b HAVING COUNT(AVG(b)) */
	@Test public void testFailNestedAggregateInHaving() {
		helpException("SELECT a FROM m.g GROUP BY a, b HAVING COUNT(b) AS x = 5");		 //$NON-NLS-1$
	}

	/** SELECT a FROM m.g GROUP BY a, b AS x */
	@Test public void testFailAliasInHaving() {
		helpException("SELECT a FROM m.g GROUP BY a, b AS x");		 //$NON-NLS-1$
	}
	
	@Test(expected=QueryParserException.class) public void testExceptionLength() throws Exception {
        String sql = "SELECT * FROM Customer where Customer.Name = (select lastname from CUSTOMER where acctid = 9"; ////$NON-NLS-1$
        QueryParser.getQueryParser().parseCommand(sql);
    }

    @Test public void testFunctionOfAggregates() {
        GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        AggregateSymbol agg1 = new AggregateSymbol("COUNT", false, new ElementSymbol("a", false)); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        AggregateSymbol agg2 = new AggregateSymbol("SUM", false, new ElementSymbol("a", false)); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        Function f = new Function("*", new Expression[] { agg1, agg2 }); //$NON-NLS-1$
        AliasSymbol alias = new AliasSymbol("c", f); //$NON-NLS-1$
        select.addSymbol(alias);        
                
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT COUNT(a) * SUM(a) AS c FROM m.g",  //$NON-NLS-1$
                 "SELECT (COUNT(a) * SUM(a)) AS c FROM m.g",  //$NON-NLS-1$
                 query);
        
    }
	
	/** SELECT 5-null, a.g1.c1 FROM a.g1 */
	@Test public void testArithmeticNullFunction() { 
		GroupSymbol g = new GroupSymbol("a.g1"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new Function("-", new Expression[] { new Constant(new Integer(5)), new Constant(null) }) ); //$NON-NLS-1$
		select.addSymbol(new ElementSymbol("a.g1.c1")); //$NON-NLS-1$
						
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		
		helpTest("SELECT 5-null, a.g1.c1 FROM a.g1",  //$NON-NLS-1$
				 "SELECT (5 - null), a.g1.c1 FROM a.g1",  //$NON-NLS-1$
				 query);
	}

	/** SELECT 'abc' FROM a.g1 */
	@Test public void testStringLiteral() { 
		GroupSymbol g = new GroupSymbol("a.g1"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new Constant("abc")); //$NON-NLS-1$ //$NON-NLS-2$
						
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		
		helpTest("SELECT 'abc' FROM a.g1",  //$NON-NLS-1$
				 "SELECT 'abc' FROM a.g1",  //$NON-NLS-1$
				 query);
	}


	/** SELECT 'O''Leary' FROM a.g1 */
	@Test public void testStringLiteralEscapedTick() { 
		GroupSymbol g = new GroupSymbol("a.g1"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new Constant("O'Leary")); //$NON-NLS-1$ //$NON-NLS-2$
						
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		
		helpTest("SELECT 'O''Leary' FROM a.g1",  //$NON-NLS-1$
				 "SELECT 'O''Leary' FROM a.g1",  //$NON-NLS-1$
				 query);
	}

	/** SELECT '''abc''' FROM a.g1 */
	@Test public void testStringLiteralEscapedTick2() { 
		GroupSymbol g = new GroupSymbol("a.g1"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new Constant("'abc'")); //$NON-NLS-1$ //$NON-NLS-2$
						
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		
		helpTest("SELECT '''abc''' FROM a.g1",  //$NON-NLS-1$
				 "SELECT '''abc''' FROM a.g1",  //$NON-NLS-1$
				 query);
	}

	/** SELECT 'a''b''c' FROM a.g1 */
	@Test public void testStringLiteralEscapedTick3() { 
		GroupSymbol g = new GroupSymbol("a.g1"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new Constant("a'b'c")); //$NON-NLS-1$ //$NON-NLS-2$
						
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		
		helpTest("SELECT 'a''b''c' FROM a.g1",  //$NON-NLS-1$
				 "SELECT 'a''b''c' FROM a.g1",  //$NON-NLS-1$
				 query);
	}

	/** SELECT " "" " FROM a.g1 */
	@Test public void testStringLiteralEscapedTick4() { 
		GroupSymbol g = new GroupSymbol("a.g1"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new ElementSymbol(" \" ")); //$NON-NLS-1$ 
						
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		
		helpTest("SELECT \" \"\" \" FROM a.g1",  //$NON-NLS-1$
				 "SELECT \" \"\" \" FROM a.g1",  //$NON-NLS-1$
				 query);
	}
	
	/** SELECT 123456789012 FROM a.g1 */
	@Test public void testLongLiteral() {
		GroupSymbol g = new GroupSymbol("a.g1"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new Constant(new Long(123456789012L))); //$NON-NLS-1$
						
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		
		helpTest("SELECT 123456789012 FROM a.g1",  //$NON-NLS-1$
				 "SELECT 123456789012 FROM a.g1",  //$NON-NLS-1$
				 query);		    
	}
	
	/** SELECT 1000000000000000000000000 FROM a.g1 */
	@Test public void testBigIntegerLiteral() {
		GroupSymbol g = new GroupSymbol("a.g1"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new Constant(new BigInteger("1000000000000000000000000"))); //$NON-NLS-1$ //$NON-NLS-2$
						
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		
		helpTest("SELECT 1000000000000000000000000 FROM a.g1",  //$NON-NLS-1$
				 "SELECT 1000000000000000000000000 FROM a.g1",  //$NON-NLS-1$
				 query);		    
	}
	
	/** SELECT 1.3e8 FROM a.g1 */
	@Test public void testFloatWithE() {
		GroupSymbol g = new GroupSymbol("a.g1"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new Constant(new Double(1.3e8))); //$NON-NLS-1$
		select.addSymbol(new Constant(new Double(-1.3e+8))); //$NON-NLS-1$
		select.addSymbol(new Constant(new Double(+1.3e-8))); //$NON-NLS-1$
						
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		
		helpTest("SELECT 1.3e8, -1.3e+8, +1.3e-8 FROM a.g1",  //$NON-NLS-1$
				 "SELECT 1.3E8, -1.3E8, 1.3E-8 FROM a.g1",  //$NON-NLS-1$
				 query);		    
	}	
	
    /** SELECT {d'2002-10-02'} FROM m.g1 */
    @Test public void testDateLiteral1() {
        GroupSymbol g = new GroupSymbol("m.g1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new Constant(java.sql.Date.valueOf("2002-10-02"))); //$NON-NLS-1$ //$NON-NLS-2$
                        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        
        helpTest("SELECT {d'2002-10-02'} FROM m.g1",  //$NON-NLS-1$
                 "SELECT {d'2002-10-02'} FROM m.g1",  //$NON-NLS-1$
                 query);                    
    }

    /** SELECT {d'2002-9-1'} FROM m.g1 */
    @Test public void testDateLiteral2() {
        GroupSymbol g = new GroupSymbol("m.g1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new Constant(java.sql.Date.valueOf("2002-09-01"))); //$NON-NLS-1$ //$NON-NLS-2$
                        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        
        helpTest("SELECT {d'2002-09-01'} FROM m.g1",  //$NON-NLS-1$
                 "SELECT {d'2002-09-01'} FROM m.g1",  //$NON-NLS-1$
                 query);                    
    }

    /** SELECT {d'bad'} FROM m.g1 */
    @Test public void testDateLiteralFail() {
        helpException("SELECT {d'bad'} FROM m.g1"); //$NON-NLS-1$
    }
        
    /** SELECT {t '11:10:00' } FROM m.g1 */
    @Test public void testTimeLiteral1() {
        GroupSymbol g = new GroupSymbol("m.g1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new Constant(java.sql.Time.valueOf("11:10:00"))); //$NON-NLS-1$ //$NON-NLS-2$
                        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        
        helpTest("SELECT {t '11:10:00' } FROM m.g1",  //$NON-NLS-1$
                 "SELECT {t'11:10:00'} FROM m.g1",  //$NON-NLS-1$
                 query);                    
    }

    /** SELECT {t '5:10:00'} FROM m.g1 */
    @Test public void testTimeLiteral2() {
        GroupSymbol g = new GroupSymbol("m.g1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new Constant(java.sql.Time.valueOf("5:10:00"))); //$NON-NLS-1$ //$NON-NLS-2$
                        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        
        helpTest("SELECT {t '05:10:00'} FROM m.g1",  //$NON-NLS-1$
                 "SELECT {t'05:10:00'} FROM m.g1",  //$NON-NLS-1$
                 query);                    
    }

    /** SELECT {t 'xyz'} FROM m.g1 */
    @Test public void testTimeLiteralFail() {
        helpException("SELECT {t 'xyz'} FROM m.g1"); //$NON-NLS-1$
    }
    
    /** SELECT {ts'2002-10-02 19:00:02.50'} FROM m.g1 */
    @Test public void testTimestampLiteral() {
        GroupSymbol g = new GroupSymbol("m.g1"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new Constant(java.sql.Timestamp.valueOf("2002-10-02 19:00:02.50"))); //$NON-NLS-1$ //$NON-NLS-2$
                        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        
        helpTest("SELECT {ts'2002-10-02 19:00:02.50'} FROM m.g1",  //$NON-NLS-1$
                 "SELECT {ts'2002-10-02 19:00:02.5'} FROM m.g1",  //$NON-NLS-1$
                 query);                    
    }

    /** SELECT {b'true'} FROM m.g1 */
    @Test public void testBooleanLiteralTrue() {
    	Boolean expected = Boolean.TRUE;
    	Class<?> expectedType = DataTypeManager.DefaultDataClasses.BOOLEAN;
    	String sql = "SELECT {b'true'}";  //$NON-NLS-1$
    	String expectedSql = "SELECT TRUE";  //$NON-NLS-1$
    	
        helpTestLiteral(expected, expectedType, sql, expectedSql);                    
    }

	private void helpTestLiteral(Boolean expected, Class<?> expectedType,
			String sql, String expectedSql) {
		Select select = new Select();
        select.addSymbol(new Constant(expected, expectedType)); //$NON-NLS-1$
                        
        Query query = new Query();
        query.setSelect(select);
        
        helpTest(sql,
                 expectedSql,
                 query);
	}
    /** SELECT TRUE FROM m.g1 */
    @Test public void testBooleanLiteralTrue2() {
    	Boolean expected = Boolean.TRUE;
    	Class<?> expectedType = DataTypeManager.DefaultDataClasses.BOOLEAN;
    	String sql = "SELECT TRUE";  //$NON-NLS-1$
    	String expectedSql = "SELECT TRUE";  //$NON-NLS-1$
    	
        helpTestLiteral(expected, expectedType, sql, expectedSql);                    
    }
  
    /** SELECT {b'false'} FROM m.g1 */
    @Test public void testBooleanLiteralFalse() {
    	Boolean expected = Boolean.FALSE;
    	Class<?> expectedType = DataTypeManager.DefaultDataClasses.BOOLEAN;
    	String sql = "SELECT {b'false'}";  //$NON-NLS-1$
    	String expectedSql = "SELECT FALSE";  //$NON-NLS-1$
    	
        helpTestLiteral(expected, expectedType, sql, expectedSql);                    
    }
    
    /** SELECT FALSE FROM m.g1 */
    @Test public void testBooleanLiteralFalse2() {
    	Boolean expected = Boolean.FALSE;
    	Class<?> expectedType = DataTypeManager.DefaultDataClasses.BOOLEAN;
    	String sql = "SELECT {b'false'}";  //$NON-NLS-1$
    	String expectedSql = "SELECT FALSE";  //$NON-NLS-1$
    	
        helpTestLiteral(expected, expectedType, sql, expectedSql);                    
    }
    
    @Test public void testBooleanLiteralUnknown() {
    	Boolean expected = null;
    	Class<?> expectedType = DataTypeManager.DefaultDataClasses.BOOLEAN;
    	String sql = "SELECT {b'unknown'}";  //$NON-NLS-1$
    	String expectedSql = "SELECT UNKNOWN";  //$NON-NLS-1$
    	
        helpTestLiteral(expected, expectedType, sql, expectedSql);
    }
    
    @Test public void testBooleanLiteralUnknown2() {
    	Boolean expected = null;
    	Class<?> expectedType = DataTypeManager.DefaultDataClasses.BOOLEAN;
    	String sql = "SELECT UNKNOWN";  //$NON-NLS-1$
    	String expectedSql = "SELECT UNKNOWN";  //$NON-NLS-1$
    	
        helpTestLiteral(expected, expectedType, sql, expectedSql);
    }
        
	/** SELECT DISTINCT a FROM g */
	@Test public void testSelectDistinct(){
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$
		select.setDistinct(true);
		
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT DISTINCT a FROM g",  //$NON-NLS-1$
				 "SELECT DISTINCT a FROM g",  //$NON-NLS-1$
				 query);
	}

    /** SELECT ALL a FROM g */
    @Test public void testSelectAll(){
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$
        select.setDistinct(false);
        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT ALL a FROM g",  //$NON-NLS-1$
                 "SELECT a FROM g",  //$NON-NLS-1$
                 query);
    }
	
    //=========================Aliasing==============================================

	/** SELECT a AS myA, b FROM g */
	@Test public void testAliasInSelect(){
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		AliasSymbol as = new AliasSymbol("myA", new ElementSymbol("a")); //$NON-NLS-1$ //$NON-NLS-2$
		Select select = new Select();
		select.addSymbol(as);
		select.addSymbol(new ElementSymbol("b")); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT a AS myA, b FROM g",  //$NON-NLS-1$
				 "SELECT a AS myA, b FROM g",  //$NON-NLS-1$
				 query);
	}

	/** SELECT a myA, b FROM g, h */
	@Test public void testAliasInSelect2(){
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		GroupSymbol h = new GroupSymbol("h"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);
		from.addGroup(h);

		AliasSymbol as = new AliasSymbol("myA", new ElementSymbol("a")); //$NON-NLS-1$ //$NON-NLS-2$
		Select select = new Select();
		select.addSymbol(as);
		select.addSymbol(new ElementSymbol("b")); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT a myA, b FROM g, h",  //$NON-NLS-1$
				 "SELECT a AS myA, b FROM g, h",  //$NON-NLS-1$
				 query);	    
	}
	
	/** SELECT myG.a FROM g AS myG */
	@Test public void testAliasInFrom(){
		GroupSymbol g = new GroupSymbol("myG", "g"); //$NON-NLS-1$ //$NON-NLS-2$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new ElementSymbol("myG.a")); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT myG.a FROM g AS myG",  //$NON-NLS-1$
				 "SELECT myG.a FROM g AS myG",  //$NON-NLS-1$
				 query);
	}

	/** SELECT myG.*, myH.b FROM g AS myG, h AS myH */
	@Test public void testAliasesInFrom(){
		GroupSymbol g = new GroupSymbol("myG", "g"); //$NON-NLS-1$ //$NON-NLS-2$
		GroupSymbol h = new GroupSymbol("myH", "h"); //$NON-NLS-1$ //$NON-NLS-2$
		From from = new From();
		from.addGroup(g);
		from.addGroup(h);

		Select select = new Select();
		MultipleElementSymbol myG = new MultipleElementSymbol("myG"); //$NON-NLS-1$
		select.addSymbol(myG);
		select.addSymbol(new ElementSymbol("myH.b")); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT myG.*, myH.b FROM g AS myG, h AS myH",  //$NON-NLS-1$
				 "SELECT myG.*, myH.b FROM g AS myG, h AS myH",  //$NON-NLS-1$
				 query);	    
	}

	/** SELECT myG.a, myH.b FROM g myG, h myH */
	@Test public void testHiddenAliasesInFrom(){
		GroupSymbol g = new GroupSymbol("myG", "g"); //$NON-NLS-1$ //$NON-NLS-2$
		GroupSymbol h = new GroupSymbol("myH", "h"); //$NON-NLS-1$ //$NON-NLS-2$
		From from = new From();
		from.addGroup(g);
		from.addGroup(h);

		Select select = new Select();
		MultipleElementSymbol myG = new MultipleElementSymbol("myG"); //$NON-NLS-1$
		select.addSymbol(myG);
		select.addSymbol(new ElementSymbol("myH.b")); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT myG.*, myH.b FROM g myG, h myH",  //$NON-NLS-1$
				 "SELECT myG.*, myH.b FROM g AS myG, h AS myH",  //$NON-NLS-1$
				 query);
	}

	/** SELECT a AS or FROM g */
	@Test public void testAliasInSelectUsingKeywordFails(){
		helpException("SELECT a AS or FROM g");		 //$NON-NLS-1$
	}

	/** SELECT or.a FROM g AS or */
	@Test public void testAliasInFromUsingKeywordFails(){
		helpException("SELECT or.a FROM g AS or");		 //$NON-NLS-1$
	}

    // ======================= Misc ==============================================

    /** Select a From db.g Where a IS NULL */
    @Test public void testIsNullCriteria1(){
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
        select.addSymbol(a);

        Criteria crit = new IsNullCriteria(a);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("Select a From db.g Where a IS NULL",  //$NON-NLS-1$
                 "SELECT a FROM db.g WHERE a IS NULL",  //$NON-NLS-1$
                 query);
    }
    
    /** Select a From db.g Where a IS NOT NULL */
    @Test public void testIsNullCriteria2(){
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
        select.addSymbol(a);

        IsNullCriteria crit = new IsNullCriteria(a);
        crit.setNegated(true);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("Select a From db.g Where a IS NOT NULL",  //$NON-NLS-1$
                 "SELECT a FROM db.g WHERE a IS NOT NULL",  //$NON-NLS-1$
                 query);
    }
    
	/** Select a From db.g Where Not a IS NULL */
	@Test public void testNotIsNullCriteria(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);

		Criteria crit = new NotCriteria(new IsNullCriteria(a));

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setCriteria(crit);
		helpTest("Select a From db.g Where Not a IS NULL",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE NOT (a IS NULL)",  //$NON-NLS-1$
				 query);
	}

	/** SELECT a from db.g where a <> "value" */
	@Test public void testStringNotEqualDoubleTicks(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);

		Expression ex = new ElementSymbol("value"); //$NON-NLS-1$
		Criteria crit = new CompareCriteria(a, CompareCriteria.NE, ex);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setCriteria(crit);
		helpTest("SELECT a from db.g where a <> \"value\"",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE a <> \"value\"",  //$NON-NLS-1$
				 query);
	}

    /** SELECT a from db.g where a != "value" */
    @Test public void testNotEquals2(){
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
        select.addSymbol(a);

        Expression constant = new Constant("value"); //$NON-NLS-1$
        Criteria crit = new CompareCriteria(a, CompareCriteria.NE, constant);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("SELECT a from db.g where a != 'value'",  //$NON-NLS-1$
                 "SELECT a FROM db.g WHERE a <> 'value'",  //$NON-NLS-1$
                 query);
    }

	/** SELECT a from db."g" where a = 5 */
	@Test public void testPartlyQuotedGroup(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);
		
		Criteria crit = new CompareCriteria(a, CompareCriteria.EQ, new Constant(new Integer(5)));

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setCriteria(crit);
		helpTest("SELECT a from db.\"g\" where a = 5",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE a = 5",  //$NON-NLS-1$
				 query);
	}

	/** SELECT a from "db"."g" where a = 5 */
	@Test public void testFullyQuotedGroup(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);
		
		Criteria crit = new CompareCriteria(a, CompareCriteria.EQ, new Constant(new Integer(5)));

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setCriteria(crit);
		helpTest("SELECT a from \"db\".\"g\" where a = 5",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE a = 5",  //$NON-NLS-1$
				 query);
	}
	
	/** SELECT "db".g.a from db.g */
	@Test public void testPartlyQuotedElement1(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("db.g.a");  //$NON-NLS-1$
		select.addSymbol(a);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT \"db\".g.a from db.g",  //$NON-NLS-1$
				 "SELECT db.g.a FROM db.g",  //$NON-NLS-1$
				 query);
	}

	/** SELECT "db"."g".a from db.g */
	@Test public void testPartlyQuotedElement2(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("db.g.a");  //$NON-NLS-1$
		select.addSymbol(a);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT \"db\".\"g\".a from db.g",  //$NON-NLS-1$
				 "SELECT db.g.a FROM db.g",  //$NON-NLS-1$
				 query);
	}

	/** SELECT "db"."g"."a" from db.g */
	@Test public void testPartlyQuotedElement3(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("db.g.a");  //$NON-NLS-1$
		select.addSymbol(a);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT \"db\".\"g\".\"a\" from db.g",  //$NON-NLS-1$
				 "SELECT db.g.a FROM db.g",  //$NON-NLS-1$
				 query);
	}

	/** SELECT ""g"".""a" from db.g */
	@Test public void testStringLiteralLikeQuotedElement(){
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new ElementSymbol("g\".\"a")); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT \"g\"\".\"\"a\" from g",  //$NON-NLS-1$
				 "SELECT \"g\"\"\".\"\"\"a\" FROM g",  //$NON-NLS-1$
				 query);
	}
	
	/** SELECT ""g"".""a" from db.g */
	@Test public void testStringLiteralLikeQuotedElement1(){
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new Constant("g\".\"a")); //$NON-NLS-1$ //$NON-NLS-2$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		ParseInfo info = new ParseInfo();
		info.ansiQuotedIdentifiers = false;
		helpTest("SELECT \"g\"\".\"\"a\" from g",  //$NON-NLS-1$
				 "SELECT 'g\".\"a' FROM g",  //$NON-NLS-1$
				 query, info);
	}

	/** SELECT g.x AS "select" FROM g */
	@Test public void testQuotedAlias(){
		GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		AliasSymbol a = new AliasSymbol("select", new ElementSymbol("g.x"));  //$NON-NLS-1$ //$NON-NLS-2$
		select.addSymbol(a);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT g.x AS \"select\" FROM g",  //$NON-NLS-1$
				 "SELECT g.x AS \"select\" FROM g",  //$NON-NLS-1$
				 query);
	}

    /** SELECT g.x AS year FROM g */
    @Test public void testQuotedAlias2(){
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        AliasSymbol a = new AliasSymbol("year", new ElementSymbol("g.x"));  //$NON-NLS-1$ //$NON-NLS-2$
        select.addSymbol(a);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);

        helpTest("SELECT g.x AS \"year\" FROM g",  //$NON-NLS-1$
                 "SELECT g.x AS \"year\" FROM g",  //$NON-NLS-1$
                 query);
    }

    @Test public void testQuotedAlias3(){
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        AliasSymbol a = new AliasSymbol("some year", new ElementSymbol("g.x"));  //$NON-NLS-1$ //$NON-NLS-2$
        select.addSymbol(a);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);

        helpTest("SELECT g.x AS \"some year\" FROM g",  //$NON-NLS-1$
                 "SELECT g.x AS \"some year\" FROM g",  //$NON-NLS-1$
                 query);
    }

    
    /** SELECT g."select" FROM g */
    @Test public void testReservedWordElement1(){
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol a = new ElementSymbol("g.select");  //$NON-NLS-1$
        select.addSymbol(a);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);

        helpTest("SELECT g.\"select\" FROM g",  //$NON-NLS-1$
                 "SELECT g.\"select\" FROM g",  //$NON-NLS-1$
                 query);
    }

    /** SELECT newModel5.ResultSetDocument.MappingClasses.from.from.Query1InputSet.x FROM newModel5.ResultSetDocument.MappingClasses.from.from.Query1InputSet */
    @Test public void testReservedWordElement2() {
        GroupSymbol g = new GroupSymbol("newModel5.ResultSetDocument.MappingClasses.from.from.Query1InputSet"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol a = new ElementSymbol("newModel5.ResultSetDocument.MappingClasses.from.from.Query1InputSet.x");  //$NON-NLS-1$
        select.addSymbol(a);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);

        helpTest("SELECT newModel5.ResultSetDocument.MappingClasses.from.from.Query1InputSet.x FROM newModel5.ResultSetDocument.MappingClasses.from.from.Query1InputSet",  //$NON-NLS-1$
                 "SELECT newModel5.ResultSetDocument.MappingClasses.\"from\".\"from\".Query1InputSet.x FROM newModel5.ResultSetDocument.MappingClasses.\"from\".\"from\".Query1InputSet",  //$NON-NLS-1$
                 query);        
    }
    
    /** SELECT * FROM newModel5.ResultSetDocument.MappingClasses.from.from.Query1InputSet  */
    @Test public void testReservedWordGroup1(){
        GroupSymbol g = new GroupSymbol("newModel5.ResultSetDocument.MappingClasses.from.from.Query1InputSet"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);

        helpTest("SELECT * FROM newModel5.ResultSetDocument.MappingClasses.from.from.Query1InputSet",  //$NON-NLS-1$
                 "SELECT * FROM newModel5.ResultSetDocument.MappingClasses.\"from\".\"from\".Query1InputSet",  //$NON-NLS-1$
                 query);
    }

    /** SELECT * FROM newModel5."ResultSetDocument.MappingClasses.from.from.Query1InputSet"  */
    @Test public void testReservedWordGroup2(){
        GroupSymbol g = new GroupSymbol("newModel5.ResultSetDocument.MappingClasses.from.from.Query1InputSet"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);

        helpTest("SELECT * FROM newModel5.\"ResultSetDocument.MappingClasses.from.from.Query1InputSet\"",  //$NON-NLS-1$
                 "SELECT * FROM newModel5.ResultSetDocument.MappingClasses.\"from\".\"from\".Query1InputSet",  //$NON-NLS-1$
                 query);
    }    
    
    /** SELECT * FROM model.doc WHERE ab.cd.@ef = 'abc' */
    @Test public void testXMLCriteriaWithAttribute() {
        GroupSymbol g = new GroupSymbol("model.doc"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        
        ElementSymbol elem = new ElementSymbol("ab.cd.@ef"); //$NON-NLS-1$
        query.setCriteria(new CompareCriteria(elem, CompareCriteria.EQ, new Constant("abc"))); //$NON-NLS-1$

        helpTest("SELECT * FROM model.doc WHERE ab.cd.@ef = 'abc'",  //$NON-NLS-1$
                 "SELECT * FROM model.doc WHERE ab.cd.@ef = 'abc'",  //$NON-NLS-1$
                 query);   
    }

	/** SELECT a from db.g where a <> 'value' */
	@Test public void testStringNotEqual(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);

		Expression constant = new Constant("value"); //$NON-NLS-1$
		Criteria crit = new CompareCriteria(a, CompareCriteria.NE, constant);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setCriteria(crit);
		helpTest("SELECT a from db.g where a <> 'value'",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE a <> 'value'",  //$NON-NLS-1$
				 query);
	}	

    /** SELECT a from db.g where a BETWEEN 1000 AND 2000 */
    @Test public void testBetween1(){
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
        select.addSymbol(a);

        Expression constant1 = new Constant(new Integer(1000));
        Expression constant2 = new Constant(new Integer(2000));
        Criteria crit = new BetweenCriteria(a, constant1, constant2);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("SELECT a from db.g where a BETWEEN 1000 AND 2000",  //$NON-NLS-1$
                 "SELECT a FROM db.g WHERE a BETWEEN 1000 AND 2000",  //$NON-NLS-1$
                 query);
    }   
    
    /** SELECT a from db.g where a NOT BETWEEN 1000 AND 2000 */
    @Test public void testBetween2(){
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
        select.addSymbol(a);

        Expression constant1 = new Constant(new Integer(1000));
        Expression constant2 = new Constant(new Integer(2000));
        BetweenCriteria crit = new BetweenCriteria(a, constant1, constant2);
        crit.setNegated(true);
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("SELECT a from db.g where a NOT BETWEEN 1000 AND 2000",  //$NON-NLS-1$
                 "SELECT a FROM db.g WHERE a NOT BETWEEN 1000 AND 2000",  //$NON-NLS-1$
                 query);
    }   
    
	/** SELECT a from db.g where a < 1000 */
	@Test public void testCompareLT(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);

		Expression constant = new Constant(new Integer(1000));
		Criteria crit = new CompareCriteria(a, CompareCriteria.LT, constant);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setCriteria(crit);
		helpTest("SELECT a from db.g where a < 1000",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE a < 1000",  //$NON-NLS-1$
				 query);
	}	
	
	/** SELECT a from db.g where a > 1000 */
	@Test public void testCompareGT(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);

		Expression constant = new Constant(new Integer(1000));
		Criteria crit = new CompareCriteria(a, CompareCriteria.GT, constant);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setCriteria(crit);
		helpTest("SELECT a from db.g where a > 1000",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE a > 1000",  //$NON-NLS-1$
				 query);
	}
	
	/** SELECT a from db.g where a <= 1000 */
	@Test public void testCompareLE(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);

		Expression constant = new Constant(new Integer(1000));
		Criteria crit = new CompareCriteria(a, CompareCriteria.LE, constant);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setCriteria(crit);
		helpTest("SELECT a from db.g where a <= 1000",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE a <= 1000",  //$NON-NLS-1$
				 query);
	}		

	/** SELECT a from db.g where a >= 1000 */
	@Test public void testCompareGE(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);

		Expression constant = new Constant(new Integer(1000));
		Criteria crit = new CompareCriteria(a, CompareCriteria.GE, constant);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setCriteria(crit);
		helpTest("SELECT a from db.g where a >= 1000",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE a >= 1000",  //$NON-NLS-1$
				 query);
	}

	/** SELECT a from db.g where b = x and a = 1000 */
	@Test public void testCompoundCompare1(){
	    helpTestCompoundCompare("SELECT a from db.g where b = x and a = 1000"); //$NON-NLS-1$
	}

	/** SELECT a from db.g where (b = x and a = 1000) */
	@Test public void testCompoundCompare2(){
	    helpTestCompoundCompare("SELECT a from db.g where (b = x and a = 1000)"); //$NON-NLS-1$
	}

	/** SELECT a from db.g where ((b = x) and (a = 1000)) */
	@Test public void testCompoundCompare3(){
	    helpTestCompoundCompare("SELECT a from db.g where ((b = x) and (a = 1000))"); //$NON-NLS-1$
	}

	/** SELECT a from db.g where (((b = x) and (a = 1000))) */
	@Test public void testCompoundCompare4(){
	    helpTestCompoundCompare("SELECT a from db.g where (((b = x) and (a = 1000)))"); //$NON-NLS-1$
	}

	/** SELECT a FROM db.g WHERE (b = x) AND (a = 1000) */
	private void helpTestCompoundCompare(String testSQL){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);

		Criteria crit1 = new CompareCriteria(new ElementSymbol("b"), CompareCriteria.EQ, new ElementSymbol("x")); //$NON-NLS-1$ //$NON-NLS-2$
		Expression constant = new Constant(new Integer(1000));
		Criteria crit2 = new CompareCriteria(a, CompareCriteria.EQ, constant);
		Criteria crit = new CompoundCriteria(CompoundCriteria.AND, crit1, crit2);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setCriteria(crit);
		helpTest(testSQL, 
				 "SELECT a FROM db.g WHERE (b = x) AND (a = 1000)",  //$NON-NLS-1$
				 query);
	}
	
    /** SELECT a FROM db.g WHERE b IN (1000,5000)*/
    @Test public void testSetCriteria0(){
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$

        Expression constant1 = new Constant(new Integer(1000));
        Expression constant2 = new Constant(new Integer(5000));
        Collection<Expression> constants = new ArrayList<Expression>(2);
        constants.add(constant1);
        constants.add(constant2);
        Criteria crit = new SetCriteria(new ElementSymbol("b"), constants); //$NON-NLS-1$

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("SELECT a FROM db.g WHERE b IN (1000,5000)",  //$NON-NLS-1$
                 "SELECT a FROM db.g WHERE b IN (1000, 5000)",  //$NON-NLS-1$
                 query);
    }   
    
    /** SELECT a FROM db.g WHERE b NOT IN (1000,5000)*/
    @Test public void testSetCriteria1(){
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$

        Expression constant1 = new Constant(new Integer(1000));
        Expression constant2 = new Constant(new Integer(5000));
        Collection<Expression> constants = new ArrayList<Expression>(2);
        constants.add(constant1);
        constants.add(constant2);
        SetCriteria crit = new SetCriteria(new ElementSymbol("b"), constants); //$NON-NLS-1$
        crit.setNegated(true);
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("SELECT a FROM db.g WHERE b NOT IN (1000,5000)",  //$NON-NLS-1$
                 "SELECT a FROM db.g WHERE b NOT IN (1000, 5000)",  //$NON-NLS-1$
                 query);
    }   
    
	// ================================== order by ==================================
	
	/** SELECT a FROM db.g WHERE b = aString order by c*/
	@Test public void testOrderBy(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);

		Criteria crit = new CompareCriteria(new ElementSymbol("b"), CompareCriteria.EQ, new ElementSymbol("aString")); //$NON-NLS-1$ //$NON-NLS-2$

		ArrayList<ElementSymbol> elements = new ArrayList<ElementSymbol>();
		elements.add(new ElementSymbol("c")); //$NON-NLS-1$
		OrderBy orderBy = new OrderBy(elements);

		Query query = new Query(select, from, crit, orderBy, null);
		helpTest("SELECT a FROM db.g WHERE b = aString ORDER BY c",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE b = aString ORDER BY c",  //$NON-NLS-1$
				 query);
	}	

	/** SELECT a FROM db.g WHERE b = aString order by c desc*/
	@Test public void testOrderByDesc(){
		ArrayList<ElementSymbol> elements = new ArrayList<ElementSymbol>();
		elements.add(new ElementSymbol("c")); //$NON-NLS-1$
		ArrayList<Boolean> orderTypes = new ArrayList<Boolean>();
		orderTypes.add(Boolean.FALSE);
		OrderBy orderBy = new OrderBy(elements, orderTypes);
		
		Query query = getOrderByQuery(orderBy);
		helpTest("SELECT a FROM db.g WHERE b = aString ORDER BY c desc",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE b = aString ORDER BY c DESC",  //$NON-NLS-1$
				 query);
	}
	private Query getOrderByQuery(OrderBy orderBy) {
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);

		Criteria crit = new CompareCriteria(new ElementSymbol("b"), CompareCriteria.EQ, new ElementSymbol("aString")); //$NON-NLS-1$ //$NON-NLS-2$

		Query query = new Query(select, from, crit, orderBy, null);
		return query;
	}	

	/** SELECT a FROM db.g WHERE b = aString order by c,d*/
	@Test public void testOrderBys(){
		ArrayList<ElementSymbol> elements = new ArrayList<ElementSymbol>();
		elements.add(new ElementSymbol("c")); //$NON-NLS-1$
		elements.add(new ElementSymbol("d")); //$NON-NLS-1$
		OrderBy orderBy = new OrderBy(elements);

		Query query = getOrderByQuery(orderBy);
		helpTest("SELECT a FROM db.g WHERE b = aString ORDER BY c,d",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE b = aString ORDER BY c, d",  //$NON-NLS-1$
				 query);
	}	

	/** SELECT a FROM db.g WHERE b = aString order by c desc,d desc*/
	@Test public void testOrderBysDesc(){
		ArrayList<ElementSymbol> elements = new ArrayList<ElementSymbol>();
		elements.add(new ElementSymbol("c")); //$NON-NLS-1$
		elements.add(new ElementSymbol("d")); //$NON-NLS-1$
		ArrayList<Boolean> orderTypes = new ArrayList<Boolean>();
		orderTypes.add(Boolean.FALSE);
		orderTypes.add(Boolean.FALSE);
		OrderBy orderBy = new OrderBy(elements, orderTypes);

		Query query = getOrderByQuery(orderBy);
		helpTest("SELECT a FROM db.g WHERE b = aString ORDER BY c desc,d desc",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE b = aString ORDER BY c DESC, d DESC",  //$NON-NLS-1$
				 query);
	}
	
	/** SELECT a FROM db.g WHERE b = aString order by c desc,d*/
	@Test public void testMixedOrderBys(){
		ArrayList<ElementSymbol> elements = new ArrayList<ElementSymbol>();
		elements.add(new ElementSymbol("c")); //$NON-NLS-1$
		elements.add(new ElementSymbol("d")); //$NON-NLS-1$
		ArrayList<Boolean> orderTypes = new ArrayList<Boolean>();
		orderTypes.add(Boolean.FALSE);
		orderTypes.add(Boolean.TRUE);
		OrderBy orderBy = new OrderBy(elements, orderTypes);

		Query query = getOrderByQuery(orderBy);
		helpTest("SELECT a FROM db.g WHERE b = aString ORDER BY c desc,d",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE b = aString ORDER BY c DESC, d",  //$NON-NLS-1$
				 query);
	}	
	
	@Test public void testOrderByNullOrdering(){
		OrderBy orderBy = new OrderBy();
		OrderByItem item = new OrderByItem(new ElementSymbol("c"), true);
		item.setNullOrdering(NullOrdering.FIRST);
		orderBy.getOrderByItems().add(item);
		item = new OrderByItem(new ElementSymbol("d"), false);
		item.setNullOrdering(NullOrdering.LAST);
		orderBy.getOrderByItems().add(item);

		Query query = getOrderByQuery(orderBy);
		helpTest("SELECT a FROM db.g WHERE b = aString ORDER BY c NULLS FIRST,d desc nulls last",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE b = aString ORDER BY c NULLS FIRST, d DESC NULLS LAST",  //$NON-NLS-1$
				 query);
	}

	// ================================== match ====================================

    /** SELECT a FROM db.g WHERE b LIKE 'aString'*/
    @Test public void testLike0(){
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
        select.addSymbol(a);

        Expression string1 = new Constant("aString"); //$NON-NLS-1$
        Criteria crit = new MatchCriteria(new ElementSymbol("b"), string1); //$NON-NLS-1$

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("SELECT a FROM db.g WHERE b LIKE 'aString'",  //$NON-NLS-1$
                 "SELECT a FROM db.g WHERE b LIKE 'aString'",  //$NON-NLS-1$
                 query);
    }   
    
    @Test public void testPgLike(){
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
        select.addSymbol(a);

        Expression string1 = new Constant("\\_aString"); //$NON-NLS-1$
        Criteria crit = new MatchCriteria(new ElementSymbol("b"), string1, '\\'); //$NON-NLS-1$

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("SELECT a FROM db.g WHERE b LIKE E'\\\\_aString'",  //$NON-NLS-1$
                 "SELECT a FROM db.g WHERE b LIKE '\\_aString' ESCAPE '\\'",  //$NON-NLS-1$
                 query);
    }

    /** SELECT a FROM db.g WHERE b NOT LIKE 'aString'*/
    @Test public void testLike1(){
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
        select.addSymbol(a);

        Expression string1 = new Constant("aString"); //$NON-NLS-1$
        MatchCriteria crit = new MatchCriteria(new ElementSymbol("b"), string1); //$NON-NLS-1$
        crit.setNegated(true);
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("SELECT a FROM db.g WHERE b NOT LIKE 'aString'",  //$NON-NLS-1$
                 "SELECT a FROM db.g WHERE b NOT LIKE 'aString'",  //$NON-NLS-1$
                 query);
    }   

	/** SELECT a from db.g where b like '#String' escape '#'*/
	@Test public void testLikeWithEscape(){
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
		select.addSymbol(a);

		Expression string1 = new Constant("#String"); //$NON-NLS-1$
		Criteria crit = new MatchCriteria(new ElementSymbol("b"), string1, '#'); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setCriteria(crit);
		helpTest("SELECT a from db.g where b like '#String' escape '#'",  //$NON-NLS-1$
				 "SELECT a FROM db.g WHERE b LIKE '#String' ESCAPE '#'",  //$NON-NLS-1$
				 query);
	}
	
    @Test public void testLikeWithEscapeException(){
        helpException("SELECT a from db.g where b like '#String' escape '#1'", "TEIID31100 Parsing error: Encountered \"like '#String' escape [*]'#1'[*]\" at line 1, column 50.\nTEIID30398 LIKE/SIMILAR TO ESCAPE value must be a single character: [#1].");  //$NON-NLS-1$ //$NON-NLS-2$
    }   

	/** SELECT "date"."time" from db.g */
	@Test public void testReservedWordsInElement() {
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		ElementSymbol a = new ElementSymbol("date.time");  //$NON-NLS-1$
		select.addSymbol(a);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		helpTest("SELECT \"date\".\"time\" from db.g",  //$NON-NLS-1$
				 "SELECT \"date\".\"time\" FROM db.g",  //$NON-NLS-1$
				 query);
	    
	}
    
    /** SELECT a */
    @Test public void testNoFromClause(){
        Select select = new Select();
        ElementSymbol a = new ElementSymbol("a"); //$NON-NLS-1$
        select.addSymbol(a);
        select.addSymbol(new Constant(new Integer(5), Integer.class));
        Query query = new Query();
        query.setSelect(select);
        helpTest("SELECT a, 5", "SELECT a, 5", query);       //$NON-NLS-1$ //$NON-NLS-2$
    }
    
	// ==================== misc queries that should fail ===========================

	/** FROM g WHERE a = 'aString' */
	@Test public void testFailsNoSelectClause(){
		helpException("FROM g WHERE a = 'aString'");		 //$NON-NLS-1$
	}
	
	/** SELECT a WHERE a = 'aString' */
	@Test public void testFailsNoFromClause(){
		helpException("SELECT a WHERE a = 'aString'");		 //$NON-NLS-1$
	}
	
	/** SELECT xx.yy%.a from xx.yy */
	@Test public void testFailsWildcardInSelect(){
		helpException("SELECT xx.yy%.a from xx.yy", "TEIID31100 Parsing error: Encountered \"SELECT xx.yy[*]%[*].a\" at line 1, column 13.\nLexical error. Character is not a valid token: % ");		 //$NON-NLS-1$
	}
	
	@Test public void testFailsWildcardInSelect1(){
		helpException("SELECT % from xx.yy", "TEIID31100 Parsing error: Encountered \"SELECT [*]%[*] from xx.yy\" at line 1, column 8.\nLexical error. Character is not a valid token: % ");		 //$NON-NLS-1$
	}
	
	@Test public void testInvalidToken(){
		helpException("%", "TEIID31100 Parsing error: Encountered \"[*]%[*]\" at line 1, column 1.\nLexical error. Character is not a valid token: % ");
	}

	/** SELECT a or b from g */
	@Test public void testOrInSelect(){
		Query query = new Query();
		query.setSelect(new Select(Arrays.asList(new CompoundCriteria(CompoundCriteria.OR, Arrays.asList(new ExpressionCriteria(new ElementSymbol("a")), new ExpressionCriteria(new ElementSymbol("b"))))))); 
		helpTest("select a or b", "SELECT (a) OR (b)", query);
	}
	
	/** SELECT a FROM g WHERE a LIKE x*/
	@Test public void testLikeWOConstant(){
        GroupSymbol g = new GroupSymbol("g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
        select.addSymbol(a);

        ElementSymbol x = new ElementSymbol("x"); //$NON-NLS-1$
        Criteria crit = new MatchCriteria(a, x);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("SELECT a FROM g WHERE a LIKE x",  //$NON-NLS-1$
                 "SELECT a FROM g WHERE a LIKE x",  //$NON-NLS-1$
                 query);        
	}

	/** SELECT a from g ORDER BY b DSC*/
	@Test public void testFailsDSCMisspelled(){
		helpException("SELECT a from g ORDER BY b DSC");		 //$NON-NLS-1$
	}
	
	/** Test reusability of parser */	
	@Test public void testReusabilityOfParserObject() {
		GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
		From from = new From();
		from.addGroup(g);

		Select select = new Select();
		select.addSymbol(new ElementSymbol("a", false)); //$NON-NLS-1$
		
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);

		helpTest("SELECT a FROM m.g",  //$NON-NLS-1$
				 "SELECT a FROM m.g",  //$NON-NLS-1$
				 query);		    

		helpTest("SELECT a FROM m.g",  //$NON-NLS-1$
				 "SELECT a FROM m.g",  //$NON-NLS-1$
				 query);		    
	}
	
    /** SELECT a from db.g where b LIKE ? */
    @Test public void testParameter1() {
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol a = new ElementSymbol("a");  //$NON-NLS-1$
        select.addSymbol(a);

        Reference ref1 = new Reference(0);
        Criteria crit = new MatchCriteria(new ElementSymbol("b"), ref1); //$NON-NLS-1$

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("SELECT a from db.g where b LIKE ?",  //$NON-NLS-1$
                 "SELECT a FROM db.g WHERE b LIKE ?",  //$NON-NLS-1$
                 query);        
    }

    /** SELECT a from db.g where b LIKE ? */
    @Test public void testParameter2() {
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        Reference ref0 = new Reference(0);
        select.addSymbol(ref0);

        Reference ref1 = new Reference(1);
        Criteria crit = new MatchCriteria(new ElementSymbol("b"), ref1); //$NON-NLS-1$

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(crit);
        helpTest("SELECT ? from db.g where b LIKE ?",  //$NON-NLS-1$
                 "SELECT ? FROM db.g WHERE b LIKE ?",  //$NON-NLS-1$
                 query);        
    }

    /** SELECT a, b FROM (SELECT c FROM m.g) AS y */
    @Test public void testSubquery1() {
        GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol symbol = new ElementSymbol("c"); //$NON-NLS-1$
        select.addSymbol(symbol);
        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        
        SubqueryFromClause sfc = new SubqueryFromClause("y", query); //$NON-NLS-1$
        From from2 = new From();
        from2.addClause(sfc);
        
        Select select2 = new Select();
        select2.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$
        select2.addSymbol(new ElementSymbol("b")); //$NON-NLS-1$
        
        Query query2 = new Query();
        query2.setSelect(select2);
        query2.setFrom(from2);
        
        helpTest("SELECT a, b FROM (SELECT c FROM m.g) AS y",  //$NON-NLS-1$
                 "SELECT a, b FROM (SELECT c FROM m.g) AS y",  //$NON-NLS-1$
                 query2);             
    }
    
    /** SELECT a, b FROM ((SELECT c FROM m.g)) AS y */
    @Test public void testSubquery1a() {
        GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol symbol = new ElementSymbol("c"); //$NON-NLS-1$
        select.addSymbol(symbol);
        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        
        SubqueryFromClause sfc = new SubqueryFromClause("y", query); //$NON-NLS-1$
        From from2 = new From();
        from2.addClause(sfc);
        
        Select select2 = new Select();
        select2.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$
        select2.addSymbol(new ElementSymbol("b")); //$NON-NLS-1$
        
        Query query2 = new Query();
        query2.setSelect(select2);
        query2.setFrom(from2);
        
        helpTest("SELECT a, b FROM ((SELECT c FROM m.g)) AS y",  //$NON-NLS-1$
                 "SELECT a, b FROM (SELECT c FROM m.g) AS y",  //$NON-NLS-1$
                 query2);             
    }    

    /** SELECT a, b FROM m.g1 JOIN (SELECT c FROM m.g2) AS y ON m.g1.a = y.c */
    @Test public void testSubquery2() {
        GroupSymbol g = new GroupSymbol("m.g2"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        ElementSymbol symbol = new ElementSymbol("c"); //$NON-NLS-1$
        select.addSymbol(symbol);
        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        
        UnaryFromClause ufc = new UnaryFromClause(new GroupSymbol("m.g1")); //$NON-NLS-1$
        SubqueryFromClause sfc = new SubqueryFromClause("y", query); //$NON-NLS-1$
        CompareCriteria join = new CompareCriteria(new ElementSymbol("m.g1.a"), CompareCriteria.EQ, new ElementSymbol("y.c")); //$NON-NLS-1$ //$NON-NLS-2$
        List crits = new ArrayList();
        crits.add(join);
        JoinPredicate jp = new JoinPredicate(ufc, sfc, JoinType.JOIN_INNER, crits);
        From from2 = new From();
        from2.addClause(jp);
                        
        Select select2 = new Select();
        select2.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$
        select2.addSymbol(new ElementSymbol("b")); //$NON-NLS-1$
        
        Query query2 = new Query();
        query2.setSelect(select2);
        query2.setFrom(from2);
        
        helpTest("SELECT a, b FROM m.g1 JOIN (SELECT c FROM m.g2) AS y ON m.g1.a = y.c",  //$NON-NLS-1$
                 "SELECT a, b FROM m.g1 INNER JOIN (SELECT c FROM m.g2) AS y ON m.g1.a = y.c",  //$NON-NLS-1$
                 query2);             
    }

	/** SELECT a, b FROM (SELECT c FROM m.g2) */
	@Test public void testSubqueryInvalid() {
		helpException("SELECT a, b FROM (SELECT c FROM m.g2)"); //$NON-NLS-1$
	}
		    
    /** INSERT INTO m.g (a) VALUES (?) */
    @Test public void testInsertWithReference() {
        Insert insert = new Insert();
        insert.setGroup(new GroupSymbol("m.g")); //$NON-NLS-1$
        List<ElementSymbol> vars = new ArrayList<ElementSymbol>();
        vars.add(new ElementSymbol("a"));         //$NON-NLS-1$
        insert.setVariables(vars);
        List<Reference> values = new ArrayList<Reference>();
        values.add(new Reference(0));
        insert.setValues(values);
        helpTest("INSERT INTO m.g (a) VALUES (?)",  //$NON-NLS-1$
                 "INSERT INTO m.g (a) VALUES (?)",  //$NON-NLS-1$
                 insert);                     
    }
        
    @Test public void testStoredQueryWithNoParameter(){
    	StoredProcedure storedQuery = new StoredProcedure();
    	storedQuery.setProcedureName("proc1"); //$NON-NLS-1$
    	helpTest("exec proc1()", "EXEC proc1()", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
    	helpTest("execute proc1()", "EXEC proc1()", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testStoredQueryWithNoParameter2(){
    	StoredProcedure storedQuery = new StoredProcedure();
    	storedQuery.setProcedureName("proc1"); //$NON-NLS-1$
    	
        From from = new From();
        SubqueryFromClause sfc = new SubqueryFromClause("x", storedQuery); //$NON-NLS-1$
        from.addClause(sfc);
        
        Select select = new Select();
        select.addSymbol(new ElementSymbol("X.A")); //$NON-NLS-1$
        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
    	helpTest("SELECT X.A FROM (exec proc1()) AS X", "SELECT X.A FROM (EXEC proc1()) AS X", query); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testStoredQuery(){
    	StoredProcedure storedQuery = new StoredProcedure();
    	storedQuery.setProcedureName("proc1"); //$NON-NLS-1$
    	SPParameter parameter = new SPParameter(1, new Constant("param1")); //$NON-NLS-1$
        parameter.setParameterType(ParameterInfo.IN);
    	storedQuery.setParameter(parameter);
    	helpTest("Exec proc1('param1')", "EXEC proc1('param1')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
    	helpTest("execute proc1('param1')", "EXEC proc1('param1')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testStoredQuery2(){
    	StoredProcedure storedQuery = new StoredProcedure();
    	storedQuery.setProcedureName("proc1"); //$NON-NLS-1$
    	SPParameter parameter = new SPParameter(1, new Constant("param1")); //$NON-NLS-1$
    	storedQuery.setParameter(parameter);
    	From from = new From();
        SubqueryFromClause sfc = new SubqueryFromClause("x", storedQuery); //$NON-NLS-1$
        from.addClause(sfc);
        
        Select select = new Select();
        select.addSymbol(new ElementSymbol("X.A")); //$NON-NLS-1$
        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
    	helpTest("SELECT X.A FROM (exec proc1('param1')) AS X", "SELECT X.A FROM (EXEC proc1('param1')) AS X", query); //$NON-NLS-1$ //$NON-NLS-2$
    }

    @Test public void testStoredQuery2SanityCheck(){
        StoredProcedure storedQuery = new StoredProcedure();
        storedQuery.setProcedureName("proc1"); //$NON-NLS-1$
        SPParameter parameter = new SPParameter(1, new Constant("param1")); //$NON-NLS-1$
        storedQuery.setParameter(parameter);
        From from = new From();
        SubqueryFromClause sfc = new SubqueryFromClause("x", storedQuery); //$NON-NLS-1$
        from.addClause(sfc);
        
        Select select = new Select();
        select.addSymbol(new ElementSymbol("x.a")); //$NON-NLS-1$

        helpTest("exec proc1('param1')", "EXEC proc1('param1')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
    }    
    
    /** 
     * Try nesting subquery in double parentheses - parsing fails.  'exec' is not handled as
     * robustly as other types of commands that can appear in a from clause subquery. 
     */
    public void testStoredQuerySubqueryMultipleParens(){
        StoredProcedure storedQuery = new StoredProcedure();
        storedQuery.setProcedureName("proc1"); //$NON-NLS-1$
        SPParameter parameter = new SPParameter(1, new Constant("param1")); //$NON-NLS-1$
        storedQuery.setParameter(parameter);
        From from = new From();
        SubqueryFromClause sfc = new SubqueryFromClause("x", storedQuery); //$NON-NLS-1$
        from.addClause(sfc);
        
        Select select = new Select();
        select.addSymbol(new ElementSymbol("x.a")); //$NON-NLS-1$
        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        helpTest("SELECT X.A FROM ((exec proc1('param1'))) AS X", "SELECT X.A FROM (EXEC proc1('param1')) AS X", query); //$NON-NLS-1$ //$NON-NLS-2$
    }    
    
    @Test public void testErrorStatement() throws Exception {
    	ExceptionExpression ee = new ExceptionExpression();
    	ee.setMessage(new Constant("Test only"));
        RaiseStatement errStmt = new RaiseStatement(ee);
                 
        helpStmtTest("ERROR 'Test only';", "RAISE SQLEXCEPTION 'Test only';", //$NON-NLS-1$ //$NON-NLS-2$
            errStmt);           
    }
    
    @Test public void testRaiseErrorStatement() throws Exception {
    	ExceptionExpression ee = new ExceptionExpression();
    	ee.setMessage(new Constant("Test only"));
    	ee.setSqlState(new Constant("100"));
    	ee.setParent(new ElementSymbol("e"));
        RaiseStatement errStmt = new RaiseStatement(ee, true);
                 
        helpStmtTest("RAISE SQLWARNING SQLEXCEPTION 'Test only' SQLSTATE '100' chain e;", "RAISE SQLWARNING SQLEXCEPTION 'Test only' SQLSTATE '100' CHAIN e;", //$NON-NLS-1$ //$NON-NLS-2$
            errStmt);           
    }
    
    @Test public void testIfStatement() throws Exception {
        ElementSymbol a = new ElementSymbol("a"); //$NON-NLS-1$
        String shortType = new String("short"); //$NON-NLS-1$
        Statement ifStmt = new DeclareStatement(a, shortType);

        ElementSymbol b = new ElementSymbol("b"); //$NON-NLS-1$
        Statement elseStmt = new DeclareStatement(b, shortType);
              
        Block ifBlock = new Block();
        ifBlock.addStatement(ifStmt); 
      
        Block elseBlock = new Block();
        elseBlock.addStatement(elseStmt);
    
        ElementSymbol c = new ElementSymbol("c");     //$NON-NLS-1$
        Criteria crit = new CompareCriteria(c, CompareCriteria.EQ, 
            new Constant(new Integer(5)));
            
        IfStatement stmt = new IfStatement(crit, ifBlock, elseBlock);
                        
        helpStmtTest("IF(c = 5) BEGIN DECLARE short a; END ELSE BEGIN DECLARE short b; END", //$NON-NLS-1$
             "IF(c = 5)"+"\n"+ "BEGIN"+"\n"+"DECLARE short a;"+"\n"+"END"+"\n"+ //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ //$NON-NLS-8$
             "ELSE"+"\n"+"BEGIN"+"\n"+"DECLARE short b;"+"\n"+"END", //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$
             stmt);     
    }    

    @Test public void testAssignStatement() throws Exception {
        ElementSymbol a = new ElementSymbol("a"); //$NON-NLS-1$
       
        List<ElementSymbol> symbols = new ArrayList<ElementSymbol>();
        symbols.add(new ElementSymbol("a1"));  //$NON-NLS-1$
        Select select = new Select(symbols);       
        
        From from = new From();
        from.addGroup(new GroupSymbol("g")); //$NON-NLS-1$
        
        Criteria criteria = new CompareCriteria(new ElementSymbol("a2"), CompareCriteria.EQ,  //$NON-NLS-1$
            new Constant(new Integer(5)));
        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(criteria);
        
        Expression expr = new Constant("aString"); //$NON-NLS-1$
        
        AssignmentStatement queryStmt = new AssignmentStatement(a, query);
        AssignmentStatement exprStmt = new AssignmentStatement(a, expr);
        
        helpStmtTest("a = SELECT a1 FROM g WHERE a2 = 5;", "a = (SELECT a1 FROM g WHERE a2 = 5);", //$NON-NLS-1$ //$NON-NLS-2$
            queryStmt);
        
        helpStmtTest("a = 'aString';", "a = 'aString';", exprStmt);      //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testDeclareStatement() throws Exception {
        ElementSymbol a = new ElementSymbol("a"); //$NON-NLS-1$
        String type = new String("short"); //$NON-NLS-1$
        DeclareStatement stmt = new DeclareStatement(a, type);
    
        helpStmtTest("DECLARE short a;","DECLARE short a;", stmt); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testDeclareStatementWithAssignment() throws Exception {
        ElementSymbol a = new ElementSymbol("a"); //$NON-NLS-1$
        String type = new String("short"); //$NON-NLS-1$
        DeclareStatement stmt = new DeclareStatement(a, type, new Constant(null));
    
        helpStmtTest("DECLARE short a = null;","DECLARE short a = null;", stmt); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testDeclareStatementWithAssignment1() throws Exception {
        ElementSymbol a = new ElementSymbol("a"); //$NON-NLS-1$
        String type = new String("string"); //$NON-NLS-1$
        DeclareStatement stmt = new DeclareStatement(a, type, new ScalarSubquery(sampleQuery()));
    
        helpStmtTest("DECLARE string a = SELECT a1 FROM g WHERE a2 = 5;","DECLARE string a = (SELECT a1 FROM g WHERE a2 = 5);", stmt); //$NON-NLS-1$ //$NON-NLS-2$
    }
      
    @Test public void testStatement() throws Exception {
        ElementSymbol a = new ElementSymbol("a"); //$NON-NLS-1$
        String type = new String("short"); //$NON-NLS-1$
        DeclareStatement declStmt = new DeclareStatement(a, type);
        Statement stmt = declStmt;
       
        helpStmtTest("DECLARE short a;", "DECLARE short a;", //$NON-NLS-1$ //$NON-NLS-2$
            stmt);
    }
        
    @Test public void testBlock() throws Exception {
        ElementSymbol a = new ElementSymbol("a"); //$NON-NLS-1$
        String type = new String("short"); //$NON-NLS-1$
        DeclareStatement declStmt = new DeclareStatement(a, type);
        Statement stmt = declStmt;
        Block block = new Block(stmt);       
       
        helpBlockTest("BEGIN DECLARE short a; END", "BEGIN"+"\n"+"DECLARE short a;"+"\n"+"END", //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
            block);
    }
       
    @Test public void testCommandStatement() throws Exception {
        Query query = sampleQuery();
        
        Command sqlCmd = query;
        CommandStatement cmdStmt = new CommandStatement(sqlCmd);
   
        helpStmtTest("SELECT a1 FROM g WHERE a2 = 5;", "SELECT a1 FROM g WHERE a2 = 5;", //$NON-NLS-1$ //$NON-NLS-2$
        cmdStmt);       
    }

    /** 
     * @return
     */
    private Query sampleQuery() {
        List<ElementSymbol> symbols = new ArrayList<ElementSymbol>();
        symbols.add(new ElementSymbol("a1"));  //$NON-NLS-1$
        Select select = new Select(symbols);
           
        From from = new From();
        from.addGroup(new GroupSymbol("g")); //$NON-NLS-1$
        
        Criteria criteria = new CompareCriteria(new ElementSymbol("a2"), CompareCriteria.EQ,  //$NON-NLS-1$
            new Constant(new Integer(5)));
        
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(criteria);
        return query;
    }
    
    @Test public void testDynamicCommandStatement() throws Exception {
        List<ElementSymbol> symbols = new ArrayList<ElementSymbol>();

        ElementSymbol a1 = new ElementSymbol("a1"); //$NON-NLS-1$
        a1.setType(DataTypeManager.DefaultDataClasses.STRING);
        symbols.add(a1);  
        
        DynamicCommand sqlCmd = new DynamicCommand();
        Expression sql = new Constant("SELECT a1 FROM g WHERE a2 = 5"); //$NON-NLS-1$
        
        sqlCmd.setSql(sql);
        sqlCmd.setAsColumns(symbols);
        sqlCmd.setAsClauseSet(true);
        
        sqlCmd.setIntoGroup(new GroupSymbol("#g")); //$NON-NLS-1$
        
        CommandStatement cmdStmt = new CommandStatement(sqlCmd);
   
        helpStmtTest("exec string 'SELECT a1 FROM g WHERE a2 = 5' as a1 string into #g;", "EXECUTE IMMEDIATE 'SELECT a1 FROM g WHERE a2 = 5' AS a1 string INTO #g;", //$NON-NLS-1$ //$NON-NLS-2$
        cmdStmt);       
    }
    
    //sql is a variable, also uses the as, into, and update clauses
    @Test public void testDynamicCommandStatement1() throws Exception {
        List<ElementSymbol> symbols = new ArrayList<ElementSymbol>();
        
        ElementSymbol a1 = new ElementSymbol("a1"); //$NON-NLS-1$
        a1.setType(DataTypeManager.DefaultDataClasses.STRING);
        symbols.add(a1);  
        
        ElementSymbol a2 = new ElementSymbol("a2"); //$NON-NLS-1$
        a1.setType(DataTypeManager.DefaultDataClasses.INTEGER);
        symbols.add(a2);  
        
        DynamicCommand sqlCmd = new DynamicCommand();
        Expression sql = new ElementSymbol("z"); //$NON-NLS-1$
        
        sqlCmd.setSql(sql);
        sqlCmd.setAsColumns(symbols);
        sqlCmd.setAsClauseSet(true);
        
        sqlCmd.setIntoGroup(new GroupSymbol("#g")); //$NON-NLS-1$
        
        sqlCmd.setUpdatingModelCount(1);
        
        CommandStatement cmdStmt = new CommandStatement(sqlCmd);
   
        helpStmtTest("execute IMMEDIATE z as a1 string, a2 integer into #g update 1;", "EXECUTE IMMEDIATE z AS a1 string, a2 integer INTO #g UPDATE 1;", //$NON-NLS-1$ //$NON-NLS-2$
        cmdStmt);       
    }
    
    @Test public void testDynamicCommandStatementWithUsing() throws Exception {
        SetClauseList using = new SetClauseList();
        
        ElementSymbol a = new ElementSymbol("a"); //$NON-NLS-1$
        using.addClause(a, new ElementSymbol("b"));  //$NON-NLS-1$
                
        DynamicCommand sqlCmd = new DynamicCommand();
        Expression sql = new ElementSymbol("z"); //$NON-NLS-1$
        
        sqlCmd.setSql(sql);
        sqlCmd.setUsing(using);
        
        CommandStatement cmdStmt = new CommandStatement(sqlCmd);
   
        helpStmtTest("execute immediate z using a=b;", "EXECUTE IMMEDIATE z USING a = b;", //$NON-NLS-1$ //$NON-NLS-2$
        cmdStmt);       
    }

    //as clause should use short names
    @Test public void testDynamicCommandStatement2(){
        helpException("create virtual procedure begin execute string z as variables.a1 string, a2 integer into #g; end"); //$NON-NLS-1$       
    }
    
    //using clause should use short names
    @Test public void testDynamicCommandStatement3(){
        helpException("create virtual procedure begin execute string z as a1 string, a2 integer into #g using variables.x=variables.y; end", "TEIID31100 Parsing error: Encountered \"into #g using [*]variables.x[*]=variables.y\" at line 1, column 88.\nInvalid simple identifier format: [variables.x]"); //$NON-NLS-1$ //$NON-NLS-2$       
    }
    
    //into clause requires as clause
    @Test public void testDynamicCommandStatement4(){
        helpException("create virtual procedure begin execute string z into #g using x=variables.y; end"); //$NON-NLS-1$       
    }
    
    @Test public void testSubquerySetCriteria0() { 
        //test wrap up command with subquerySetCriteria
        Query outer = exampleIn(false);
                     
        helpTest("SELECT a FROM db.g WHERE b IN (SELECT a FROM db.g WHERE a2 = 5)", //$NON-NLS-1$
            "SELECT a FROM db.g WHERE b IN (SELECT a FROM db.g WHERE a2 = 5)", //$NON-NLS-1$
             outer);        
    }
    
	static Query exampleIn(boolean semiJoin) {
		GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$

        Expression expr = new ElementSymbol("b"); //$NON-NLS-1$
              
        Criteria criteria = new CompareCriteria(new ElementSymbol("a2"), CompareCriteria.EQ,  //$NON-NLS-1$
            new Constant(new Integer(5)));
            
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(criteria);
        SubquerySetCriteria subCrit = new SubquerySetCriteria(expr, query);
        subCrit.getSubqueryHint().setMergeJoin(semiJoin);
        Query outer = new Query();
        outer.setSelect(select);
        outer.setFrom(from);
        outer.setCriteria(subCrit);
		return outer;
	}     
               
    @Test public void testSubquerySetCriteria1() { 

        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$

        Expression expr = new ElementSymbol("b"); //$NON-NLS-1$
              
        Criteria criteria = new CompareCriteria(new ElementSymbol("a2"), CompareCriteria.EQ,  //$NON-NLS-1$
            new Constant(new Integer(5)));
            
        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setCriteria(criteria);
        SubquerySetCriteria subCrit = new SubquerySetCriteria(expr, query);
        subCrit.setNegated(true);
        Query outer = new Query();
        outer.setSelect(select);
        outer.setFrom(from);
        outer.setCriteria(subCrit);
                     
        helpTest("SELECT a FROM db.g WHERE b NOT IN (SELECT a FROM db.g WHERE a2 = 5)", //$NON-NLS-1$
            "SELECT a FROM db.g WHERE b NOT IN (SELECT a FROM db.g WHERE a2 = 5)", //$NON-NLS-1$
             outer);        
    }                

    @Test public void testSubquerySetCriteriaWithExec() { 
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$

        Expression expr = new ElementSymbol("b"); //$NON-NLS-1$
              
        StoredProcedure exec = new StoredProcedure();
        exec.setProcedureName("m.sq1");               //$NON-NLS-1$
        Query query = new Query(new Select(Arrays.asList(new MultipleElementSymbol())), new From(Arrays.asList(new SubqueryFromClause("x", exec))), null, null, null);
        SubquerySetCriteria subCrit = new SubquerySetCriteria(expr, query);
       
        Query outer = new Query();
        outer.setSelect(select);
        outer.setFrom(from);
        outer.setCriteria(subCrit);
                     
        helpTest("SELECT a FROM db.g WHERE b IN (EXEC m.sq1())", //$NON-NLS-1$
            "SELECT a FROM db.g WHERE b IN (SELECT * FROM (EXEC m.sq1()) AS x)", //$NON-NLS-1$
             outer);        
    }          

    @Test public void testSubquerySetCriteriaWithUnion() { 
        GroupSymbol g = new GroupSymbol("db.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        Select select = new Select();
        select.addSymbol(new ElementSymbol("a")); //$NON-NLS-1$

        Expression expr = new ElementSymbol("b"); //$NON-NLS-1$

        Query u1 = new Query();
        Select u1s = new Select();
        u1s.addSymbol(new ElementSymbol("x1")); //$NON-NLS-1$
        u1.setSelect(u1s);
        From u1f = new From();
        u1f = new From();
        u1f.addClause(new UnaryFromClause(new GroupSymbol("db.g2"))); //$NON-NLS-1$
        u1.setFrom(u1f);

        Query u2 = new Query();
        Select u2s = new Select();
        u2s.addSymbol(new ElementSymbol("x2")); //$NON-NLS-1$
        u2.setSelect(u2s);
        From u2f = new From();
        u2f = new From();
        u2f.addClause(new UnaryFromClause(new GroupSymbol("db.g3"))); //$NON-NLS-1$
        u2.setFrom(u2f);

        SetQuery union = new SetQuery(Operation.UNION, true, u1, u2);        
                       
        SubquerySetCriteria subCrit = new SubquerySetCriteria(expr, union);
       
        Query outer = new Query();
        outer.setSelect(select);
        outer.setFrom(from);
        outer.setCriteria(subCrit);
                     
        helpTest("SELECT a FROM db.g WHERE b IN (SELECT x1 FROM db.g2 UNION ALL SELECT x2 FROM db.g3)", //$NON-NLS-1$
            "SELECT a FROM db.g WHERE b IN (SELECT x1 FROM db.g2 UNION ALL SELECT x2 FROM db.g3)", //$NON-NLS-1$
             outer);        
    }          
        
    @Test public void testVariablesInExec(){
        StoredProcedure storedQuery = new StoredProcedure();
        storedQuery.setProcedureName("proc1"); //$NON-NLS-1$
        SPParameter parameter = new SPParameter(1, new ElementSymbol("param1")); //$NON-NLS-1$
        parameter.setParameterType(ParameterInfo.IN);
        storedQuery.setParameter(parameter);
        helpTest("Exec proc1(param1)", "EXEC proc1(param1)", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
        helpTest("execute proc1(param1)", "EXEC proc1(param1)", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
    }
                
    @Test public void testExecSubquery(){
        Query query = new Query();
        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());
        query.setSelect(select);
        From from = new From();
        from.addClause(new UnaryFromClause(new GroupSymbol("newModel2.Table1")));         //$NON-NLS-1$
        StoredProcedure subquery = new StoredProcedure();
        subquery.setProcedureName("NewVirtual.StoredQuery");
        from.addClause(new SubqueryFromClause("a", subquery)); //$NON-NLS-1$
        query.setFrom(from);
                        
        helpTest("SELECT * FROM newModel2.Table1, (EXEC NewVirtual.StoredQuery()) AS a",  //$NON-NLS-1$
            "SELECT * FROM newModel2.Table1, (EXEC NewVirtual.StoredQuery()) AS a", //$NON-NLS-1$
            query);
    }    
    
    @Test public void testUnicode1() {
        try {
            byte[] data = { (byte)0xd0, (byte)0x9c, (byte)0xd0, (byte)0xbe, (byte)0xd1, (byte)0x81, (byte)0xd0, (byte)0xba, (byte)0xd0, (byte)0xb2, (byte)0xd0, (byte)0xb0};
    
            String string = new String(data, "UTF-8");  //$NON-NLS-1$
            String sql = "SELECT * FROM TestDocument.TestDocument WHERE Subject='" + string + "'";  //$NON-NLS-1$ //$NON-NLS-2$

            Query query = new Query();
            Select select = new Select();
            select.addSymbol(new MultipleElementSymbol());
            query.setSelect(select);
            From from = new From();
            from.addGroup(new GroupSymbol("TestDocument.TestDocument")); //$NON-NLS-1$
            query.setFrom(from);            
            CompareCriteria crit = new CompareCriteria(new ElementSymbol("Subject"), CompareCriteria.EQ, new Constant(string)); //$NON-NLS-1$
            query.setCriteria(crit);

            helpTest(sql, query.toString(), query);

        } catch(UnsupportedEncodingException e)   {
            fail(e.getMessage());                 
        }
    }

    @Test public void testUnicode2() {
        String sql = "SELECT * FROM TestDocument.TestDocument WHERE Subject='\u0041\u005a'";  //$NON-NLS-1$

        Query query = new Query();
        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());
        query.setSelect(select);
        From from = new From();
        from.addGroup(new GroupSymbol("TestDocument.TestDocument")); //$NON-NLS-1$
        query.setFrom(from);           
        CompareCriteria crit = new CompareCriteria(new ElementSymbol("Subject"), CompareCriteria.EQ, new Constant("AZ")); //$NON-NLS-1$ //$NON-NLS-2$
        query.setCriteria(crit);

        helpTest(sql, query.toString(), query);
    }
 
	@Test public void testUnicode3() {
		String sql = "SELECT '\u05e0'";  //$NON-NLS-1$

		Query query = new Query();
		Select select = new Select();
		Constant c = new Constant("\u05e0"); //$NON-NLS-1$
		select.addSymbol(c); //$NON-NLS-1$
		query.setSelect(select);

		helpTest(sql, query.toString(), query);
	}

	@Test public void testUnicode4() {
		String sql = "SELECT \u05e0 FROM g";  //$NON-NLS-1$

		Query query = new Query();
		Select select = new Select();
		ElementSymbol e = new ElementSymbol("\u05e0"); //$NON-NLS-1$
		select.addSymbol(e);
		From from = new From();
		from.addGroup(new GroupSymbol("g")); //$NON-NLS-1$
		query.setSelect(select);
		query.setFrom(from);

		helpTest(sql, query.toString(), query);
	}
    
    @Test public void testEscapedFunction1() {
        String sql = "SELECT * FROM a.thing WHERE e1 = {fn concat('a', 'b')}"; //$NON-NLS-1$
        
        Query query = new Query();
        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());
        query.setSelect(select);
        From from = new From();
        from.addGroup(new GroupSymbol("a.thing")); //$NON-NLS-1$
        query.setFrom(from);           
        Function function = new Function("concat", new Expression[] { new Constant("a"), new Constant("b")}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        CompareCriteria crit = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.EQ, function); //$NON-NLS-1$
        query.setCriteria(crit);

        helpTest(sql, 
            "SELECT * FROM a.thing WHERE e1 = concat('a', 'b')",  //$NON-NLS-1$
            query);        
    }

    @Test public void testEscapedFunction2() {
        String sql = "SELECT * FROM a.thing WHERE e1 = {fn convert(5, string)}"; //$NON-NLS-1$
        
        Query query = new Query();
        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());
        query.setSelect(select);
        From from = new From();
        from.addGroup(new GroupSymbol("a.thing")); //$NON-NLS-1$
        query.setFrom(from);           
        Function function = new Function("convert", new Expression[] { new Constant(new Integer(5)), new Constant("string")}); //$NON-NLS-1$ //$NON-NLS-2$
        CompareCriteria crit = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.EQ, function); //$NON-NLS-1$
        query.setCriteria(crit);

        helpTest(sql, 
            "SELECT * FROM a.thing WHERE e1 = convert(5, string)",  //$NON-NLS-1$
            query);        
    }

    @Test public void testEscapedFunction3() {
        String sql = "SELECT * FROM a.thing WHERE e1 = {fn cast(5 as string)}"; //$NON-NLS-1$
        
        Query query = new Query();
        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());
        query.setSelect(select);
        From from = new From();
        from.addGroup(new GroupSymbol("a.thing")); //$NON-NLS-1$
        query.setFrom(from);           
        Function function = new Function("cast", new Expression[] { new Constant(new Integer(5)), new Constant("string")}); //$NON-NLS-1$ //$NON-NLS-2$
        CompareCriteria crit = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.EQ, function); //$NON-NLS-1$
        query.setCriteria(crit);

        helpTest(sql, "SELECT * FROM a.thing WHERE e1 = cast(5 AS string)", query);         //$NON-NLS-1$
    }

    @Test public void testEscapedFunction4() {
        String sql = "SELECT * FROM a.thing WHERE e1 = {fn concat({fn concat('a', 'b')}, 'c')}"; //$NON-NLS-1$
        
        Query query = new Query();
        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());
        query.setSelect(select);
        From from = new From();
        from.addGroup(new GroupSymbol("a.thing")); //$NON-NLS-1$
        query.setFrom(from);           
        Function func1 = new Function("concat", new Expression[] { new Constant("a"), new Constant("b")}); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        Function func2 = new Function("concat", new Expression[] { func1, new Constant("c")}); //$NON-NLS-1$ //$NON-NLS-2$
        CompareCriteria crit = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.EQ, func2); //$NON-NLS-1$
        query.setCriteria(crit);

        helpTest(sql, "SELECT * FROM a.thing WHERE e1 = concat(concat('a', 'b'), 'c')", query);         //$NON-NLS-1$
    }

    @Test public void testFunctionWithUnderscore() {
        String sql = "SELECT yowza_yowza() FROM a.thing"; //$NON-NLS-1$
        
        Query query = new Query();
        Select select = new Select();
        Function func1 = new Function("yowza_yowza", new Expression[] { }); //$NON-NLS-1$
        select.addSymbol(func1);        
        query.setSelect(select);
        
        From from = new From();
        from.addGroup(new GroupSymbol("a.thing")); //$NON-NLS-1$
        query.setFrom(from);
        
        helpTest(sql, "SELECT yowza_yowza() FROM a.thing", query);         //$NON-NLS-1$
    }

    @Test public void testManyInnerJoins1() {
        String sql = "SELECT * " + //$NON-NLS-1$
            "FROM SQL1.dbo.Customers INNER JOIN SQL1.dbo.Orders " + //$NON-NLS-1$
            "ON SQL1.dbo.Customers.CustomerID = SQL1.dbo.Orders.CustomerID " + //$NON-NLS-1$
            "INNER JOIN SQL1.dbo.order_details " + //$NON-NLS-1$
            "ON SQL1.dbo.Orders.OrderID = SQL1.dbo.order_details.OrderID";            //$NON-NLS-1$

        String sqlExpected = "SELECT * " + //$NON-NLS-1$
            "FROM (SQL1.dbo.Customers INNER JOIN SQL1.dbo.Orders " + //$NON-NLS-1$
            "ON SQL1.dbo.Customers.CustomerID = SQL1.dbo.Orders.CustomerID) " + //$NON-NLS-1$
            "INNER JOIN SQL1.dbo.order_details " + //$NON-NLS-1$
            "ON SQL1.dbo.Orders.OrderID = SQL1.dbo.order_details.OrderID";            //$NON-NLS-1$
        
        Query query = new Query();
        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());
        query.setSelect(select);
        From from = new From();
        
        GroupSymbol g1 = new GroupSymbol("SQL1.dbo.Customers"); //$NON-NLS-1$
        GroupSymbol g2 = new GroupSymbol("SQL1.dbo.Orders"); //$NON-NLS-1$
        GroupSymbol g3 = new GroupSymbol("SQL1.dbo.order_details"); //$NON-NLS-1$
        
        ElementSymbol e1 = new ElementSymbol("SQL1.dbo.Customers.CustomerID"); //$NON-NLS-1$
        ElementSymbol e2 = new ElementSymbol("SQL1.dbo.Orders.CustomerID"); //$NON-NLS-1$
        ElementSymbol e3 = new ElementSymbol("SQL1.dbo.Orders.OrderID"); //$NON-NLS-1$
        ElementSymbol e4 = new ElementSymbol("SQL1.dbo.order_details.OrderID"); //$NON-NLS-1$
        
        List jcrits1 = new ArrayList();
        jcrits1.add(new CompareCriteria(e1, CompareCriteria.EQ, e2));
        List jcrits2 = new ArrayList();
        jcrits2.add(new CompareCriteria(e3, CompareCriteria.EQ, e4));

        JoinPredicate jp1 = new JoinPredicate(new UnaryFromClause(g1), new UnaryFromClause(g2), JoinType.JOIN_INNER, jcrits1); 
        JoinPredicate jp2 = new JoinPredicate(jp1, new UnaryFromClause(g3), JoinType.JOIN_INNER, jcrits2);       
        
        from.addClause(jp2);
        query.setFrom(from);           

        helpTest(sql, sqlExpected, query);        
    }
  
    @Test public void testManyInnerJoins2() {
        String sql = "SELECT * " + //$NON-NLS-1$
            "FROM A INNER JOIN (B RIGHT OUTER JOIN C ON b1 = c1) " + //$NON-NLS-1$
            "ON a1 = b1 " + //$NON-NLS-1$
            "INNER JOIN D " + //$NON-NLS-1$
            "ON a1 = d1";            //$NON-NLS-1$

        String sqlExpected = "SELECT * " + //$NON-NLS-1$
            "FROM (A INNER JOIN (B RIGHT OUTER JOIN C ON b1 = c1) " + //$NON-NLS-1$
            "ON a1 = b1) " + //$NON-NLS-1$
            "INNER JOIN D " + //$NON-NLS-1$
            "ON a1 = d1";            //$NON-NLS-1$
        
        Query query = new Query();
        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());
        query.setSelect(select);
        From from = new From();
        
        UnaryFromClause g1 = new UnaryFromClause(new GroupSymbol("A")); //$NON-NLS-1$
        UnaryFromClause g2 = new UnaryFromClause(new GroupSymbol("B")); //$NON-NLS-1$
        UnaryFromClause g3 = new UnaryFromClause(new GroupSymbol("C")); //$NON-NLS-1$
        UnaryFromClause g4 = new UnaryFromClause(new GroupSymbol("D")); //$NON-NLS-1$
        
        ElementSymbol e1 = new ElementSymbol("a1"); //$NON-NLS-1$
        ElementSymbol e2 = new ElementSymbol("b1"); //$NON-NLS-1$
        ElementSymbol e3 = new ElementSymbol("c1"); //$NON-NLS-1$
        ElementSymbol e4 = new ElementSymbol("d1"); //$NON-NLS-1$
        
        List jcrits1 = new ArrayList();
        jcrits1.add(new CompareCriteria(e1, CompareCriteria.EQ, e2));
        List jcrits2 = new ArrayList();
        jcrits2.add(new CompareCriteria(e2, CompareCriteria.EQ, e3));
        List jcrits3 = new ArrayList();
        jcrits3.add(new CompareCriteria(e1, CompareCriteria.EQ, e4));

        JoinPredicate jp1 = new JoinPredicate(g2, g3, JoinType.JOIN_RIGHT_OUTER, jcrits2); 
        JoinPredicate jp2 = new JoinPredicate(g1, jp1, JoinType.JOIN_INNER, jcrits1);       
        JoinPredicate jp3 = new JoinPredicate(jp2, g4, JoinType.JOIN_INNER, jcrits3);       
        
        from.addClause(jp3);
        query.setFrom(from);           

        helpTest(sql, sqlExpected, query);        
    }
  
    @Test public void testManyInnerJoins3() {
        String sql = "SELECT * " + //$NON-NLS-1$
            "FROM A INNER JOIN " + //$NON-NLS-1$
            "(B RIGHT OUTER JOIN C ON b1 = c1 " + //$NON-NLS-1$
            "CROSS JOIN D) " + //$NON-NLS-1$
            "ON a1 = d1";            //$NON-NLS-1$

        String sqlExpected = "SELECT * " + //$NON-NLS-1$
            "FROM A INNER JOIN " + //$NON-NLS-1$
            "((B RIGHT OUTER JOIN C ON b1 = c1) " + //$NON-NLS-1$
            "CROSS JOIN D) " + //$NON-NLS-1$
            "ON a1 = d1";            //$NON-NLS-1$
        
        Query query = new Query();
        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());
        query.setSelect(select);
        From from = new From();
        
        UnaryFromClause g1 = new UnaryFromClause(new GroupSymbol("A")); //$NON-NLS-1$
        UnaryFromClause g2 = new UnaryFromClause(new GroupSymbol("B")); //$NON-NLS-1$
        UnaryFromClause g3 = new UnaryFromClause(new GroupSymbol("C")); //$NON-NLS-1$
        UnaryFromClause g4 = new UnaryFromClause(new GroupSymbol("D")); //$NON-NLS-1$
        
        ElementSymbol e1 = new ElementSymbol("a1"); //$NON-NLS-1$
        ElementSymbol e2 = new ElementSymbol("b1"); //$NON-NLS-1$
        ElementSymbol e3 = new ElementSymbol("c1"); //$NON-NLS-1$
        ElementSymbol e4 = new ElementSymbol("d1"); //$NON-NLS-1$
        
        List jcrits1 = new ArrayList();
        jcrits1.add(new CompareCriteria(e2, CompareCriteria.EQ, e3));
        List jcrits2 = new ArrayList();
        jcrits2.add(new CompareCriteria(e1, CompareCriteria.EQ, e4));

        JoinPredicate jp1 = new JoinPredicate(g2, g3, JoinType.JOIN_RIGHT_OUTER, jcrits1); 
        JoinPredicate jp2 = new JoinPredicate(jp1, g4, JoinType.JOIN_CROSS);       
        JoinPredicate jp3 = new JoinPredicate(g1, jp2, JoinType.JOIN_INNER, jcrits2);       
        
        from.addClause(jp3);
        query.setFrom(from);           

        helpTest(sql, sqlExpected, query);        
    }      
      
    @Test public void testLoopStatement() throws Exception {
        GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);
        
        Select select = new Select();
        ElementSymbol c1 = new ElementSymbol("c1", false); //$NON-NLS-1$
        select.addSymbol(c1);
        select.addSymbol(new ElementSymbol("c2", false));         //$NON-NLS-1$

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        
        ElementSymbol x = new ElementSymbol("x"); //$NON-NLS-1$
        String intType = new String("integer"); //$NON-NLS-1$
        Statement dStmt = new DeclareStatement(x, intType);
        c1 = new ElementSymbol("mycursor.c1", true); //$NON-NLS-1$
        Statement assignmentStmt = new AssignmentStatement(x, c1);
        Block block = new Block();
        block.addStatement(dStmt); 
        block.addStatement(assignmentStmt);
        
        String cursor = "mycursor"; //$NON-NLS-1$
               
        LoopStatement loopStmt = new LoopStatement(block, query, cursor);
                        
        helpStmtTest("LOOP ON (SELECT c1, c2 FROM m.g) AS mycursor BEGIN DECLARE integer x; x=mycursor.c1; END", //$NON-NLS-1$
             "LOOP ON (SELECT c1, c2 FROM m.g) AS mycursor"+"\n"+ "BEGIN"+"\n"+"DECLARE integer x;"+"\n"+"x = mycursor.c1;" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$
             +"\n"+"END", loopStmt);      //$NON-NLS-1$ //$NON-NLS-2$
    }  

    @Test public void testLoopStatementWithOrderBy() throws Exception {
        GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);
        
        Select select = new Select();
        ElementSymbol c1 = new ElementSymbol("c1", false); //$NON-NLS-1$
        select.addSymbol(c1);
        select.addSymbol(new ElementSymbol("c2", false));         //$NON-NLS-1$

        OrderBy orderBy = new OrderBy();
        orderBy.addVariable(c1);

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        query.setOrderBy(orderBy);
        
        ElementSymbol x = new ElementSymbol("x"); //$NON-NLS-1$
        String intType = new String("integer"); //$NON-NLS-1$
        Statement dStmt = new DeclareStatement(x, intType);
        c1 = new ElementSymbol("mycursor.c1", true); //$NON-NLS-1$
        Statement assignmentStmt = new AssignmentStatement(x, c1);
        Block block = new Block();
        block.addStatement(dStmt); 
        block.addStatement(assignmentStmt);
        
        String cursor = "mycursor"; //$NON-NLS-1$
               
        LoopStatement loopStmt = new LoopStatement(block, query, cursor);
                        
        helpStmtTest("LOOP ON (SELECT c1, c2 FROM m.g ORDER BY c1) AS mycursor BEGIN DECLARE integer x; x=mycursor.c1; END", //$NON-NLS-1$
             "LOOP ON (SELECT c1, c2 FROM m.g ORDER BY c1) AS mycursor"+"\n"+ "BEGIN"+"\n"+"DECLARE integer x;"+"\n"+"x = mycursor.c1;" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$
             +"\n"+"END", loopStmt);      //$NON-NLS-1$ //$NON-NLS-2$
    }  
    
    @Test public void testWhileStatement() throws Exception {
        ElementSymbol x = new ElementSymbol("x", false); //$NON-NLS-1$
        Function f = new Function("+", new Expression[] { x, new Constant(new Integer(1)) }); //$NON-NLS-1$
        Statement assignmentStmt = new AssignmentStatement(x, f);
        Block block = new Block();
        block.addStatement(assignmentStmt);
        Criteria crit = new CompareCriteria(x, CompareCriteria.LT, 
                    new Constant(new Integer(100)));
        WhileStatement whileStmt = new WhileStatement(crit, block);
        helpStmtTest("WHILE (x < 100) BEGIN x=x+1; END", //$NON-NLS-1$
                     "WHILE(x < 100)"+"\n"+ "BEGIN"+"\n"+"x = (x + 1);" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$
                     +"\n"+"END", whileStmt); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testWhileStatement1() throws Exception {
        ElementSymbol x = new ElementSymbol("x", false); //$NON-NLS-1$
        Function f = new Function("+", new Expression[] { x, new Constant(new Integer(1)) }); //$NON-NLS-1$
        Statement assignmentStmt = new AssignmentStatement(x, f);
        Block block = new Block();
        block.setAtomic(true);
        block.setLabel("1y");
        block.addStatement(assignmentStmt);
        BranchingStatement bs = new BranchingStatement(BranchingMode.CONTINUE);
        bs.setLabel("1y");
        block.addStatement(bs);
        Criteria crit = new CompareCriteria(x, CompareCriteria.LT, 
                    new Constant(new Integer(100)));
        WhileStatement whileStmt = new WhileStatement(crit, block);
        helpStmtTest("WHILE (x < 100) \"1y\": BEGIN ATOMIC x=x+1; CONTINUE \"1y\"; END", //$NON-NLS-1$
                     "WHILE(x < 100)"+"\n"+ "\"1y\" : BEGIN ATOMIC"+"\n"+"x = (x + 1);\nCONTINUE \"1y\";" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$
                     +"\n"+"END", whileStmt); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testBreakStatement() throws Exception {
        Statement breakStmt = new BranchingStatement();
        helpStmtTest("break;", "BREAK;", breakStmt); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testContinueStatement() throws Exception {
    	BranchingStatement contStmt = new BranchingStatement(BranchingMode.CONTINUE);
        helpStmtTest("continue;", "CONTINUE;", contStmt); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testContinueStatement1() throws Exception {
    	BranchingStatement contStmt = new BranchingStatement(BranchingMode.CONTINUE);
	    contStmt.setLabel("x");
        helpStmtTest("continue x;", "CONTINUE x;", contStmt); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testVirtualProcedure(){        
        ElementSymbol x = new ElementSymbol("x"); //$NON-NLS-1$
        String intType = new String("integer"); //$NON-NLS-1$
        Statement dStmt = new DeclareStatement(x, intType);
        
        GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);
        
        Select select = new Select();
        ElementSymbol c1 = new ElementSymbol("c1", false); //$NON-NLS-1$
        select.addSymbol(c1);
        select.addSymbol(new ElementSymbol("c2", false));         //$NON-NLS-1$

        Query query = new Query();
        query.setSelect(select);
        query.setFrom(from);
        
        x = new ElementSymbol("x"); //$NON-NLS-1$
        c1 = new ElementSymbol("mycursor.c1", true); //$NON-NLS-1$
        Statement assignmentStmt = new AssignmentStatement(x, c1);
        Block block = new Block(); 
        block.addStatement(assignmentStmt);
        
        Block ifBlock = new Block();
        Statement continueStmt = new BranchingStatement(BranchingMode.CONTINUE);
        ifBlock.addStatement(continueStmt);
        Criteria crit = new CompareCriteria(x, CompareCriteria.GT, 
        new Constant(new Integer(5)));
        IfStatement ifStmt = new IfStatement(crit, ifBlock);
        block.addStatement(ifStmt); 
        
        String cursor = "mycursor";                //$NON-NLS-1$
        LoopStatement loopStmt = new LoopStatement(block, query, cursor);
        
        block = new Block();        
        block.addStatement(dStmt);
        block.addStatement(loopStmt);
        CommandStatement cmdStmt = new CommandStatement(query);
        block.addStatement(cmdStmt);
        
        CreateProcedureCommand virtualProcedureCommand = new CreateProcedureCommand();
        virtualProcedureCommand.setBlock(block);
        
        helpTest("BEGIN DECLARE integer x; LOOP ON (SELECT c1, c2 FROM m.g) AS mycursor BEGIN x=mycursor.c1; IF(x > 5) BEGIN CONTINUE; END END SELECT c1, c2 FROM m.g; END", //$NON-NLS-1$
        "BEGIN\nDECLARE integer x;\n" //$NON-NLS-1$
        + "LOOP ON (SELECT c1, c2 FROM m.g) AS mycursor\nBEGIN\n" //$NON-NLS-1$
        + "x = mycursor.c1;\nIF(x > 5)\nBEGIN\nCONTINUE;\nEND\nEND\n" //$NON-NLS-1$
        + "SELECT c1, c2 FROM m.g;\nEND", virtualProcedureCommand); //$NON-NLS-1$

    }
    
    @Test public void testScalarSubqueryExpressionInSelect(){

        Select s1 = new Select();
        s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f1 = new From();
        f1.addGroup(new GroupSymbol("m.g1"));        //$NON-NLS-1$
        Query q1 = new Query();
        q1.setSelect(s1);
        q1.setFrom(f1);

        Select s2 = new Select();
        s2.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        s2.addSymbol(new ScalarSubquery(q1)); //$NON-NLS-1$
        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);

        helpTest("SELECT e1, (SELECT e1 FROM m.g1) FROM m.g2",  //$NON-NLS-1$
                 "SELECT e1, (SELECT e1 FROM m.g1) FROM m.g2", //$NON-NLS-1$
                 q2);
    }    

    @Test public void testScalarSubqueryExpressionInSelect2(){

        Select s1 = new Select();
        s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f1 = new From();
        f1.addGroup(new GroupSymbol("m.g1"));        //$NON-NLS-1$
        Query q1 = new Query();
        q1.setSelect(s1);
        q1.setFrom(f1);

        Select s2 = new Select();
        s2.addSymbol(new ScalarSubquery(q1)); //$NON-NLS-1$
        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);

        helpTest("SELECT (SELECT e1 FROM m.g1) FROM m.g2",  //$NON-NLS-1$
                 "SELECT (SELECT e1 FROM m.g1) FROM m.g2", //$NON-NLS-1$
                 q2);
    }   

    @Test public void testScalarSubqueryExpressionInSelect3(){

        Select s1 = new Select();
        s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f1 = new From();
        f1.addGroup(new GroupSymbol("m.g1"));        //$NON-NLS-1$
        Query q1 = new Query();
        q1.setSelect(s1);
        q1.setFrom(f1);

        Select s2 = new Select();
        s2.addSymbol(new ScalarSubquery(q1)); //$NON-NLS-1$
        s2.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);

        helpTest("SELECT (SELECT e1 FROM m.g1), e1 FROM m.g2",  //$NON-NLS-1$
                 "SELECT (SELECT e1 FROM m.g1), e1 FROM m.g2", //$NON-NLS-1$
                 q2);
    }   

    @Test public void testScalarSubqueryExpressionWithAlias(){

        Select s1 = new Select();
        s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f1 = new From();
        f1.addGroup(new GroupSymbol("m.g1"));        //$NON-NLS-1$
        Query q1 = new Query();
        q1.setSelect(s1);
        q1.setFrom(f1);

        Select s2 = new Select();
        s2.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        s2.addSymbol(new AliasSymbol("X", new ScalarSubquery(q1))); //$NON-NLS-1$ //$NON-NLS-2$
        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);

        helpTest("SELECT e1, (SELECT e1 FROM m.g1) as X FROM m.g2",  //$NON-NLS-1$
                 "SELECT e1, (SELECT e1 FROM m.g1) AS X FROM m.g2", //$NON-NLS-1$
                 q2);
    }   

    @Test public void testScalarSubqueryExpressionInComplexExpression() throws QueryParserException {
        Select s2 = new Select();
        s2.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
       
        s2.addSymbol(new AliasSymbol("X", QueryParser.getQueryParser().parseExpression("(SELECT e1 FROM m.g1) + 2"))); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);

        helpTest("SELECT e1, ((SELECT e1 FROM m.g1) + 2) as X FROM m.g2",  //$NON-NLS-1$
                 "SELECT e1, ((SELECT e1 FROM m.g1) + 2) AS X FROM m.g2", //$NON-NLS-1$
                 q2);
    }

    @Test public void testScalarSubqueryExpressionInComplexExpression2() throws QueryParserException{
        Select s2 = new Select();
        s2.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        
        s2.addSymbol(new AliasSymbol("X", QueryParser.getQueryParser().parseExpression("3 + (SELECT e1 FROM m.g1)"))); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);

        helpTest("SELECT e1, (3 + (SELECT e1 FROM m.g1)) as X FROM m.g2",  //$NON-NLS-1$
                 "SELECT e1, (3 + (SELECT e1 FROM m.g1)) AS X FROM m.g2", //$NON-NLS-1$
                 q2);
    } 

    @Test public void testScalarSubqueryExpressionInComplexExpression3() throws QueryParserException{
        Select s2 = new Select();
        s2.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        
        s2.addSymbol(new AliasSymbol("X", QueryParser.getQueryParser().parseExpression("(SELECT e1 FROM m.g1) + (SELECT e3 FROM m.g3)"))); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);

        helpTest("SELECT e1, ((SELECT e1 FROM m.g1) + (SELECT e3 FROM m.g3)) as X FROM m.g2",  //$NON-NLS-1$
                 "SELECT e1, ((SELECT e1 FROM m.g1) + (SELECT e3 FROM m.g3)) AS X FROM m.g2", //$NON-NLS-1$
                 q2);
    }

    @Test public void testScalarSubqueryExpressionInFunction() throws QueryParserException{
        Select s2 = new Select();
        s2.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        
        s2.addSymbol(new AliasSymbol("X", QueryParser.getQueryParser().parseExpression("length((SELECT e1 FROM m.g1))"))); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);

        helpTest("SELECT e1, length((SELECT e1 FROM m.g1)) as X FROM m.g2",  //$NON-NLS-1$
                 "SELECT e1, length((SELECT e1 FROM m.g1)) AS X FROM m.g2", //$NON-NLS-1$
                 q2);
    } 
    
    @Test public void testBadScalarSubqueryExpression() {
        helpException("SELECT e1, length(SELECT e1 FROM m.g1) as X FROM m.g2"); //$NON-NLS-1$
    }

    @Test public void testExistsPredicateCriteria(){

        Query q2 = exampleExists(false);

        helpTest("SELECT e1 FROM m.g2 WHERE Exists (SELECT e1 FROM m.g1)",  //$NON-NLS-1$
                 "SELECT e1 FROM m.g2 WHERE EXISTS (SELECT e1 FROM m.g1)", //$NON-NLS-1$
                 q2);            
    }
	static Query exampleExists(boolean semiJoin) {
		Select s1 = new Select();
        s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f1 = new From();
        f1.addGroup(new GroupSymbol("m.g1"));        //$NON-NLS-1$
        Query q1 = new Query();
        q1.setSelect(s1);
        q1.setFrom(f1);

        Select s2 = new Select();
        s2.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        ExistsCriteria existsCrit = new ExistsCriteria(q1);
        existsCrit.getSubqueryHint().setMergeJoin(semiJoin);
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);
        q2.setCriteria(existsCrit);
		return q2;
	}
    
    @Test public void testAnyQuantifierSubqueryComparePredicate(){

        Select s1 = new Select();
        s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f1 = new From();
        f1.addGroup(new GroupSymbol("m.g1"));        //$NON-NLS-1$
        Query q1 = new Query();
        q1.setSelect(s1);
        q1.setFrom(f1);

        Select s2 = new Select();
        s2.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        Criteria left = new SubqueryCompareCriteria(new ElementSymbol("e3"), q1, SubqueryCompareCriteria.GE, SubqueryCompareCriteria.ANY); //$NON-NLS-1$
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);
        q2.setCriteria(left);

        helpTest("SELECT e1 FROM m.g2 WHERE e3 >= ANY (SELECT e1 FROM m.g1)",  //$NON-NLS-1$
                 "SELECT e1 FROM m.g2 WHERE e3 >= ANY (SELECT e1 FROM m.g1)", //$NON-NLS-1$
                 q2);            

    }    

    @Test public void testSomeQuantifierSubqueryComparePredicate(){

        Select s1 = new Select();
        s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f1 = new From();
        f1.addGroup(new GroupSymbol("m.g1"));        //$NON-NLS-1$
        Query q1 = new Query();
        q1.setSelect(s1);
        q1.setFrom(f1);

        Select s2 = new Select();
        s2.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        Criteria left = new SubqueryCompareCriteria(new ElementSymbol("e3"), q1, SubqueryCompareCriteria.GT, SubqueryCompareCriteria.SOME); //$NON-NLS-1$
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);
        q2.setCriteria(left);

        helpTest("SELECT e1 FROM m.g2 WHERE e3 > some (SELECT e1 FROM m.g1)",  //$NON-NLS-1$
                 "SELECT e1 FROM m.g2 WHERE e3 > SOME (SELECT e1 FROM m.g1)", //$NON-NLS-1$
                 q2);            

    }  

    @Test public void testAllQuantifierSubqueryComparePredicate(){

        Select s1 = new Select();
        s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f1 = new From();
        f1.addGroup(new GroupSymbol("m.g1"));        //$NON-NLS-1$
        Query q1 = new Query();
        q1.setSelect(s1);
        q1.setFrom(f1);

        Select s2 = new Select();
        s2.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        Criteria left = new SubqueryCompareCriteria(new ElementSymbol("e3"), q1, SubqueryCompareCriteria.EQ, SubqueryCompareCriteria.ALL); //$NON-NLS-1$
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);
        q2.setCriteria(left);

        helpTest("SELECT e1 FROM m.g2 WHERE e3 = all (SELECT e1 FROM m.g1)",  //$NON-NLS-1$
                 "SELECT e1 FROM m.g2 WHERE e3 = ALL (SELECT e1 FROM m.g1)", //$NON-NLS-1$
                 q2);            

    } 
    
    @Test public void testScalarSubqueryComparePredicate(){

        Select s1 = new Select();
        s1.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f1 = new From();
        f1.addGroup(new GroupSymbol("m.g1"));        //$NON-NLS-1$
        Query q1 = new Query();
        q1.setSelect(s1);
        q1.setFrom(f1);

        Select s2 = new Select();
        s2.addSymbol(new ElementSymbol("e1")); //$NON-NLS-1$
        From f2 = new From();
        f2.addGroup(new GroupSymbol("m.g2"));        //$NON-NLS-1$
        Criteria left = new CompareCriteria(new ElementSymbol("e3"), SubqueryCompareCriteria.LT, new ScalarSubquery(q1)); //$NON-NLS-1$
        Query q2 = new Query();
        q2.setSelect(s2);
        q2.setFrom(f2);
        q2.setCriteria(left);

        helpTest("SELECT e1 FROM m.g2 WHERE e3 < (SELECT e1 FROM m.g1)",  //$NON-NLS-1$
                 "SELECT e1 FROM m.g2 WHERE e3 < (SELECT e1 FROM m.g1)", //$NON-NLS-1$
                 q2);            

    }

    @Test public void testSelectInto(){
        GroupSymbol g = new GroupSymbol("m.g"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);
        
        Select select = new Select();
        ElementSymbol c1 = new ElementSymbol("c1", false); //$NON-NLS-1$
        select.addSymbol(c1);
        select.addSymbol(new ElementSymbol("c2", false));   //$NON-NLS-1$
        
        Into into = new Into(new GroupSymbol("#temp")); //$NON-NLS-1$
        Query q = new Query();
        q.setSelect(select);
        q.setFrom(from);
        q.setInto(into);
        helpTest("SELECT c1, c2 INTO #temp FROM m.g",  //$NON-NLS-1$
                 "SELECT c1, c2 INTO #temp FROM m.g", //$NON-NLS-1$
                 q);  
    }
    
    @Test public void testCaseExpression1() {
        CaseExpression expr = TestCaseExpression.example(4);
        Select select = new Select();
        select.addSymbol(new ElementSymbol("y")); //$NON-NLS-1$
        select.addSymbol(new ElementSymbol("z")); //$NON-NLS-1$
        // The parser hard-codes the name "expr"
        select.addSymbol(expr); //$NON-NLS-1$
        From from = new From();
        from.addGroup(new GroupSymbol("m.g")); //$NON-NLS-1$
        Query q = new Query();
        q.setSelect(select);
        q.setFrom(from);
        
        String query = new StringBuffer("SELECT y, z, ") //$NON-NLS-1$
            .append("CASE x") //$NON-NLS-1$
            .append(" WHEN 'a' THEN 0") //$NON-NLS-1$
            .append(" WHEN 'b' THEN 1") //$NON-NLS-1$
            .append(" WHEN 'c' THEN 2") //$NON-NLS-1$
            .append(" WHEN 'd' THEN 3") //$NON-NLS-1$
            .append(" ELSE 9999") //$NON-NLS-1$
            .append(" END") //$NON-NLS-1$
            .append(" FROM m.g").toString(); //$NON-NLS-1$
        
        helpTest(query, query, q);
    }
    
    @Test public void testCaseExpression2() {
        CaseExpression expr = TestCaseExpression.example(4);
        expr.setElseExpression(null);
        Select select = new Select();
        select.addSymbol(new ElementSymbol("y")); //$NON-NLS-1$
        select.addSymbol(new ElementSymbol("z")); //$NON-NLS-1$
        // The parser hard-codes the name "expr"
        select.addSymbol(expr); //$NON-NLS-1$
        From from = new From();
        from.addGroup(new GroupSymbol("m.g")); //$NON-NLS-1$
        Query q = new Query();
        q.setSelect(select);
        q.setFrom(from);
        
        String query = new StringBuffer("SELECT y, z, ") //$NON-NLS-1$
            .append("CASE x") //$NON-NLS-1$
            .append(" WHEN 'a' THEN 0") //$NON-NLS-1$
            .append(" WHEN 'b' THEN 1") //$NON-NLS-1$
            .append(" WHEN 'c' THEN 2") //$NON-NLS-1$
            .append(" WHEN 'd' THEN 3") //$NON-NLS-1$
            .append(" END") //$NON-NLS-1$
            .append(" FROM m.g").toString(); //$NON-NLS-1$
       
        helpTest(query, query, q);
    }
    
    @Test public void testCaseExpression3() {
        SearchedCaseExpression expr = TestSearchedCaseExpression.example2(4);
        Select select = new Select();
        select.addSymbol(new ElementSymbol("y")); //$NON-NLS-1$
        From from = new From();
        from.addGroup(new GroupSymbol("m.g")); //$NON-NLS-1$
        CompareCriteria where = new CompareCriteria(new ElementSymbol("z"), CompareCriteria.EQ, expr); //$NON-NLS-1$
        Query q = new Query();
        q.setSelect(select);
        q.setFrom(from);
        q.setCriteria(where);
        
        String query = new StringBuffer("SELECT y FROM m.g ") //$NON-NLS-1$
            .append("WHERE z = CASE") //$NON-NLS-1$
            .append(" WHEN x = 'a' THEN 0") //$NON-NLS-1$
            .append(" WHEN x = 'b' THEN 1") //$NON-NLS-1$
            .append(" WHEN x = 'c' THEN 2") //$NON-NLS-1$
            .append(" WHEN x = 'd' THEN 3") //$NON-NLS-1$
            .append(" ELSE 9999") //$NON-NLS-1$
            .append(" END").toString(); //$NON-NLS-1$
        helpTest(query, query, q);
    }
    
    @Test public void testSearchedCaseExpression1() {
        SearchedCaseExpression expr = TestSearchedCaseExpression.example(4);
        Select select = new Select();
        select.addSymbol(new ElementSymbol("y")); //$NON-NLS-1$
        select.addSymbol(new ElementSymbol("z")); //$NON-NLS-1$
        // The parser hard-codes the name "expr"
        select.addSymbol(expr); //$NON-NLS-1$
        From from = new From();
        from.addGroup(new GroupSymbol("m.g")); //$NON-NLS-1$
        Query q = new Query();
        q.setSelect(select);
        q.setFrom(from);
        
        String query = new StringBuffer("SELECT y, z, ") //$NON-NLS-1$
            .append("CASE") //$NON-NLS-1$
            .append(" WHEN x = 0 THEN 0") //$NON-NLS-1$
            .append(" WHEN x = 1 THEN 1") //$NON-NLS-1$
            .append(" WHEN x = 2 THEN 2") //$NON-NLS-1$
            .append(" WHEN x = 3 THEN 3") //$NON-NLS-1$
            .append(" ELSE 9999") //$NON-NLS-1$
            .append(" END") //$NON-NLS-1$
            .append(" FROM m.g").toString(); //$NON-NLS-1$
        helpTest(query, query, q);
    }
    
    @Test public void testSearchedCaseExpression2() {
        SearchedCaseExpression expr = TestSearchedCaseExpression.example(4);
        expr.setElseExpression(null);
        Select select = new Select();
        select.addSymbol(new ElementSymbol("y")); //$NON-NLS-1$
        select.addSymbol(new ElementSymbol("z")); //$NON-NLS-1$
        // The parser hard-codes the name "expr"
        select.addSymbol(expr); //$NON-NLS-1$
        From from = new From();
        from.addGroup(new GroupSymbol("m.g")); //$NON-NLS-1$
        Query q = new Query();
        q.setSelect(select);
        q.setFrom(from);
        
        String query = new StringBuffer("SELECT y, z, ") //$NON-NLS-1$
            .append("CASE") //$NON-NLS-1$
            .append(" WHEN x = 0 THEN 0") //$NON-NLS-1$
            .append(" WHEN x = 1 THEN 1") //$NON-NLS-1$
            .append(" WHEN x = 2 THEN 2") //$NON-NLS-1$
            .append(" WHEN x = 3 THEN 3") //$NON-NLS-1$
            .append(" END") //$NON-NLS-1$
            .append(" FROM m.g").toString(); //$NON-NLS-1$
        helpTest(query, query, q);
    }
    
    @Test public void testSearchedCaseExpression3() {
        SearchedCaseExpression expr = TestSearchedCaseExpression.example(4);
        Select select = new Select();
        select.addSymbol(new ElementSymbol("y")); //$NON-NLS-1$
        From from = new From();
        from.addGroup(new GroupSymbol("m.g")); //$NON-NLS-1$
        CompareCriteria where = new CompareCriteria(new ElementSymbol("z"), CompareCriteria.EQ, expr); //$NON-NLS-1$
        Query q = new Query();
        q.setSelect(select);
        q.setFrom(from);
        q.setCriteria(where);
        
        String query = new StringBuffer("SELECT y FROM m.g ") //$NON-NLS-1$
            .append("WHERE z = CASE") //$NON-NLS-1$
            .append(" WHEN x = 0 THEN 0") //$NON-NLS-1$
            .append(" WHEN x = 1 THEN 1") //$NON-NLS-1$
            .append(" WHEN x = 2 THEN 2") //$NON-NLS-1$
            .append(" WHEN x = 3 THEN 3") //$NON-NLS-1$
            .append(" ELSE 9999") //$NON-NLS-1$
            .append(" END").toString(); //$NON-NLS-1$
        helpTest(query, query, q);
    }

    @Test public void testAndOrPrecedence_1575() {
        Select s = new Select();
        s.addSymbol(new MultipleElementSymbol());
        From f = new From();
        f.addGroup(new GroupSymbol("m.g1")); //$NON-NLS-1$
        CompareCriteria c1 = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.EQ, new Constant(new Integer(0))); //$NON-NLS-1$
        CompareCriteria c2 = new CompareCriteria(new ElementSymbol("e2"), CompareCriteria.EQ, new Constant(new Integer(1))); //$NON-NLS-1$
        CompareCriteria c3 = new CompareCriteria(new ElementSymbol("e3"), CompareCriteria.EQ, new Constant(new Integer(3))); //$NON-NLS-1$
        CompoundCriteria cc1 = new CompoundCriteria(CompoundCriteria.AND, c2, c3);        
        CompoundCriteria cc2 = new CompoundCriteria(CompoundCriteria.OR, c1, cc1);
        Query q = new Query();
        q.setSelect(s);
        q.setFrom(f);
        q.setCriteria(cc2);
        
        helpTest("SELECT * FROM m.g1 WHERE e1=0 OR e2=1 AND e3=3", //$NON-NLS-1$
        "SELECT * FROM m.g1 WHERE (e1 = 0) OR ((e2 = 1) AND (e3 = 3))", q);                          //$NON-NLS-1$
    }

    @Test public void testAndOrPrecedence2_1575() {
        Select s = new Select();
        s.addSymbol(new MultipleElementSymbol());
        From f = new From();
        f.addGroup(new GroupSymbol("m.g1")); //$NON-NLS-1$
        CompareCriteria c1 = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.EQ, new Constant(new Integer(0))); //$NON-NLS-1$
        CompareCriteria c2 = new CompareCriteria(new ElementSymbol("e2"), CompareCriteria.EQ, new Constant(new Integer(1))); //$NON-NLS-1$
        CompareCriteria c3 = new CompareCriteria(new ElementSymbol("e3"), CompareCriteria.EQ, new Constant(new Integer(3))); //$NON-NLS-1$
        CompoundCriteria cc1 = new CompoundCriteria(CompoundCriteria.AND, c1, c2);        
        CompoundCriteria cc2 = new CompoundCriteria(CompoundCriteria.OR, cc1, c3);
        Query q = new Query();
        q.setSelect(s);
        q.setFrom(f);
        q.setCriteria(cc2);
        
        helpTest("SELECT * FROM m.g1 WHERE e1=0 AND e2=1 OR e3=3", //$NON-NLS-1$
        "SELECT * FROM m.g1 WHERE ((e1 = 0) AND (e2 = 1)) OR (e3 = 3)", q);                          //$NON-NLS-1$
    }

    /** 
     * 
     * @since 4.2
     */
    private void helpTestCompoundNonJoinCriteria(String sqlPred, PredicateCriteria predCrit) {
        Select s = new Select();
        s.addSymbol(new MultipleElementSymbol());
        From f = new From();
        
        CompareCriteria c1 = new CompareCriteria(new ElementSymbol("e1"), CompareCriteria.EQ, new Constant(new Integer(0))); //$NON-NLS-1$
        CompoundCriteria cc1 = new CompoundCriteria(CompoundCriteria.AND, c1, predCrit);
        JoinPredicate jp = new JoinPredicate(new UnaryFromClause(new GroupSymbol("m.g1")), new UnaryFromClause(new GroupSymbol("m.g2")), JoinType.JOIN_INNER, cc1); //$NON-NLS-1$ //$NON-NLS-2$
        f.addClause(jp);
        
        Query q = new Query();
        q.setSelect(s);
        q.setFrom(f);
        
        helpTest("SELECT * FROM m.g1 JOIN m.g2 ON e1=0 AND " + sqlPred, //$NON-NLS-1$
        "SELECT * FROM m.g1 INNER JOIN m.g2 ON e1 = 0 AND " + sqlPred, q); //$NON-NLS-1$

    }


    @Test public void testCompoundNonJoinCriteriaInFromWithComparisonCriteria() {        
        CompareCriteria c2 = new CompareCriteria(new ElementSymbol("e2"), CompareCriteria.EQ, new Constant(new Integer(1))); //$NON-NLS-1$
        helpTestCompoundNonJoinCriteria("e2 = 1", c2);     //$NON-NLS-1$
    }
    
    @Test public void testCompoundNonJoinCriteriaInFromWithIsNull() {        
        helpTestCompoundNonJoinCriteria("e2 IS NULL", new IsNullCriteria(new ElementSymbol("e2")));     //$NON-NLS-1$ //$NON-NLS-2$
    }

    @Test public void testCompoundNonJoinCriteriaInFromUWithIN() {        
        Collection<Expression> values = new ArrayList<Expression>();
        values.add(new Constant(new Integer(0)));
        values.add(new Constant(new Integer(1)));
        PredicateCriteria crit = new SetCriteria(new ElementSymbol("e2"), values); //$NON-NLS-1$
        helpTestCompoundNonJoinCriteria("e2 IN (0, 1)", crit);     //$NON-NLS-1$
    }

    @Test public void testCompoundNonJoinCriteriaInFromUWithLIKE() {        
        PredicateCriteria crit = new MatchCriteria(new ElementSymbol("e2"), new Constant("%")); //$NON-NLS-1$ //$NON-NLS-2$
        helpTestCompoundNonJoinCriteria("e2 LIKE '%'", crit);     //$NON-NLS-1$
    }

    @Test public void testCompoundNonJoinCriteria_defect15167_1() throws Exception {   
        QueryParser.getQueryParser().parseCommand("SELECT A.alert_id, A.primary_entity_name, A.primary_entity_level_code, A.alert_description, A.create_date, A.alert_risk_score, S.scenario_name, A.alert_status_code, A.process_id, A.actual_values_text, S.SCENARIO_CATEGORY_DESC, A.primary_entity_number, A.scenario_id, A.primary_entity_key FROM (FSK_ALERT AS A LEFT OUTER JOIN FSK_SCENARIO AS S ON A.scenario_id = S.scenario_id) INNER JOIN FSC_ACCOUNT_DIM AS C ON A.primary_entity_key = C.ACCOUNT_KEY  AND ((S.current_ind = 'Y') OR (S.current_ind IS NULL)) WHERE (A.primary_entity_level_code = 'ACC') AND (C.ACCOUNT_KEY = 23923) AND (A.logical_delete_ind = 'N')"); //$NON-NLS-1$
    }

    @Test public void testCompoundNonJoinCriteria_defect15167_2() throws Exception {   
        QueryParser.getQueryParser().parseCommand("SELECT A.alert_id, A.primary_entity_name, A.primary_entity_level_code, A.alert_description, A.create_date, A.alert_risk_score, S.scenario_name, A.alert_status_code, A.process_id, A.actual_values_text, S.SCENARIO_CATEGORY_DESC, A.primary_entity_number, A.scenario_id, A.primary_entity_key FROM (FSK_ALERT AS A LEFT OUTER JOIN FSK_SCENARIO AS S ON A.scenario_id = S.scenario_id) INNER JOIN FSC_ACCOUNT_DIM AS C ON A.primary_entity_key = C.ACCOUNT_KEY  AND (S.current_ind = 'Y' OR S.current_ind IS NULL) WHERE (A.primary_entity_level_code = 'ACC') AND (C.ACCOUNT_KEY = 23923) AND (A.logical_delete_ind = 'N')"); //$NON-NLS-1$
    }

    @Test public void testCompoundNonJoinCriteria_defect15167_3() throws Exception {   
        QueryParser.getQueryParser().parseCommand("SELECT A.alert_id, A.primary_entity_name, A.primary_entity_level_code, A.alert_description, A.create_date, A.alert_risk_score, S.scenario_name, A.alert_status_code, A.process_id, A.actual_values_text, S.SCENARIO_CATEGORY_DESC, A.primary_entity_number, A.scenario_id, A.primary_entity_key FROM (FSK_ALERT AS A LEFT OUTER JOIN FSK_SCENARIO AS S ON A.scenario_id = S.scenario_id) INNER JOIN FSC_ACCOUNT_DIM AS C ON (A.primary_entity_key = C.ACCOUNT_KEY AND (S.current_ind = 'Y' OR S.current_ind IS NULL)) WHERE (A.primary_entity_level_code = 'ACC') AND (C.ACCOUNT_KEY = 23923) AND (A.logical_delete_ind = 'N')"); //$NON-NLS-1$
    }

    @Test public void testCompoundNonJoinCriteria_defect15167_4() throws Exception {   
        QueryParser.getQueryParser().parseCommand("SELECT A.alert_id, A.primary_entity_name, A.primary_entity_level_code, A.alert_description, A.create_date, A.alert_risk_score, S.scenario_name, A.alert_status_code, A.process_id, A.actual_values_text, S.SCENARIO_CATEGORY_DESC, A.primary_entity_number, A.scenario_id, A.primary_entity_key FROM (FSK_ALERT AS A LEFT OUTER JOIN FSK_SCENARIO AS S ON A.scenario_id = S.scenario_id) INNER JOIN FSC_ACCOUNT_DIM AS C ON (A.primary_entity_key = C.ACCOUNT_KEY AND S.current_ind = 'Y' OR S.current_ind IS NULL) WHERE (A.primary_entity_level_code = 'ACC') AND (C.ACCOUNT_KEY = 23923) AND (A.logical_delete_ind = 'N')"); //$NON-NLS-1$
    }
    
    @Test public void testFunctionInGroupBy() throws Exception {
        QueryParser.getQueryParser().parseCommand("SELECT SUM(s), elem+1 FROM m.g GROUP BY elem+1"); //$NON-NLS-1$
    }

    @Test public void testCaseInGroupBy() throws Exception {
        QueryParser.getQueryParser().parseCommand("SELECT SUM(elem+1), CASE elem WHEN 0 THEN 1 ELSE 2 END AS c FROM m.g GROUP BY CASE elem WHEN 0 THEN 1 ELSE 2 END"); //$NON-NLS-1$
    }

    @Test public void testNationCharString() throws Exception {
        Query query = (Query) QueryParser.getQueryParser().parseCommand("SELECT N'blah' FROM m.g"); //$NON-NLS-1$
        Select select = query.getSelect();
        Constant c = (Constant) SymbolMap.getExpression(select.getSymbol(0));
        assertEquals(c, new Constant("blah")); //$NON-NLS-1$
    }

    @Test public void testNationCharString2() throws Exception {
        Query query = (Query) QueryParser.getQueryParser().parseCommand("SELECT DISTINCT TABLE_QUALIFIER, NULL AS TABLE_OWNER, NULL AS TABLE_NAME, NULL AS TABLE_TYPE, NULL AS REMARKS FROM ATIODBCSYSTEM.OA_TABLES  WHERE TABLE_QUALIFIER LIKE N'%'  ESCAPE '\\'  ORDER BY TABLE_QUALIFIER  "); //$NON-NLS-1$
        MatchCriteria matchCrit = (MatchCriteria) query.getCriteria();
        Constant c = (Constant) matchCrit.getRightExpression();
        assertEquals(c, new Constant("%")); //$NON-NLS-1$
    }
    
    @Test public void testScalarSubquery() throws Exception {
        QueryParser.getQueryParser().parseCommand("SELECT (SELECT 1) FROM x"); //$NON-NLS-1$
    }

    @Test public void testElementInDoubleQuotes() throws Exception {
        GroupSymbol g = new GroupSymbol("x"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        ElementSymbol e =  new ElementSymbol("foo"); //$NON-NLS-1$ 
		Select select = new Select();
		select.addSymbol(e);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		
		helpTest("SELECT \"foo\" FROM x",  //$NON-NLS-1$
				 "SELECT foo FROM x",  //$NON-NLS-1$
				 query);                
    }
    
    @Test public void testElementInDoubleQuotes_Insert() throws Exception {
        GroupSymbol g = new GroupSymbol("x"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        ElementSymbol e =  new ElementSymbol("foo"); //$NON-NLS-1$ 

        Insert query = new Insert(g, new ArrayList<ElementSymbol>(), new ArrayList());
        query.addVariable(e);
        query.addValue(new Constant("bar", String.class)); //$NON-NLS-1$
        
        helpTest("insert into x (\"foo\") values ('bar')",  //$NON-NLS-1$
                 "INSERT INTO x (foo) VALUES ('bar')",  //$NON-NLS-1$
                 query);                
    }
    
    @Test public void testElementInDoubleQuotes_Update() throws Exception {
        GroupSymbol g = new GroupSymbol("x"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        ElementSymbol e =  new ElementSymbol("foo"); //$NON-NLS-1$ 
        Update query = new Update();
        query.setGroup(g);
        query.addChange(e, new Constant("bar", String.class)); //$NON-NLS-1$
        
        helpTest("update x set \"foo\"='bar'",  //$NON-NLS-1$
                 "UPDATE x SET foo = 'bar'",  //$NON-NLS-1$
                 query);                
    }  
    
    @Test public void testElementInDoubleQuotes_delete() throws Exception {
        GroupSymbol g = new GroupSymbol("x"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);

        ElementSymbol e =  new ElementSymbol("foo"); //$NON-NLS-1$ 
        CompareCriteria c = new CompareCriteria(e, CompareCriteria.EQ, new Constant("bar", String.class)); //$NON-NLS-1$
        Delete query = new Delete(g,c);
        
        helpTest("delete from x where \"foo\"='bar'",  //$NON-NLS-1$
                 "DELETE FROM x WHERE foo = 'bar'",  //$NON-NLS-1$
                 query);                
    }    
    
    @Test public void testAliasInDoubleQuotes() throws Exception {        
        GroupSymbol g = new GroupSymbol("x"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);
        
        AliasSymbol as = new AliasSymbol("fooAlias", new ElementSymbol("fooKey")); //$NON-NLS-1$ //$NON-NLS-2$
		Select select = new Select();
		select.addSymbol(as);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		
		helpTest("SELECT fooKey AS \"fooAlias\" FROM x",  //$NON-NLS-1$
				 "SELECT fooKey AS fooAlias FROM x",  //$NON-NLS-1$
				 query);		
    }
    
    @Test public void testAliasInDoubleQuotesWithQuotedGroup() throws Exception {
        
        GroupSymbol g = new GroupSymbol("x.y.z"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);
        
        AliasSymbol as = new AliasSymbol("fooAlias", new ElementSymbol("fooKey")); //$NON-NLS-1$ //$NON-NLS-2$
		Select select = new Select();
		select.addSymbol(as);
		
		ElementSymbol a = new ElementSymbol("x.y.z.id");         //$NON-NLS-1$
		Constant c = new Constant(new Integer(10));
        Criteria crit = new CompareCriteria(a, CompareCriteria.EQ, c);		

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setCriteria(crit);
		
		helpTest("SELECT fooKey AS \"fooAlias\" FROM \"x.y\".z where x.\"y.z\".id = 10",  //$NON-NLS-1$
		         "SELECT fooKey AS fooAlias FROM x.y.z WHERE x.y.z.id = 10",  //$NON-NLS-1$
				 query);		        
    }

    @Test public void testSingleQuotedConstant() throws Exception {        
        
        GroupSymbol g = new GroupSymbol("x.y.z"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);
                
        Constant as = new Constant("fooString"); //$NON-NLS-1$
		Select select = new Select();
		select.addSymbol(as); //$NON-NLS-1$
		
		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
				        
		helpTest("SELECT 'fooString' FROM \"x.y.z\"",  //$NON-NLS-1$
		        "SELECT 'fooString' FROM x.y.z",  //$NON-NLS-1$
				 query);        
    }

    @Test public void testAliasInSingleQuotes() throws Exception {
        
        GroupSymbol g = new GroupSymbol("x.y.z"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);
        
        AliasSymbol as = new AliasSymbol("fooAlias", new ElementSymbol("fooKey")); //$NON-NLS-1$ //$NON-NLS-2$
		Select select = new Select();
		select.addSymbol(as);

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		
        helpException("SELECT fooKey 'fooAlias' FROM x.\"y\".z"); //$NON-NLS-1$
    }
    
    /** QUERY Tool Format*/
    @Test public void testQueryWithQuotes_MSQuery() throws Exception {
        QueryParser.getQueryParser().parseCommand("SELECT \"PART_COLOR\", \"PART_ID\", \"PART_NAME\", \"PART_WEIGHT\" FROM \"VirtualParts.base\".\"Parts\""); //$NON-NLS-1$
    }
     
    /** MS Access Format**/
    @Test public void testQueryWithQuotes_MSAccess() throws Exception {
        QueryParser.getQueryParser().parseCommand("SELECT \"PART_COLOR\" ,\"PART_ID\" ,\"PART_NAME\" ,\"PART_WEIGHT\"  FROM \"parts_oracle.DEV_RRAMESH\".\"PARTS\""); //$NON-NLS-1$
    }

    /** BO Business View Manager**/
    @Test public void testQueryWithQuotes_BODesigner() throws Exception {
        QueryParser.getQueryParser().parseCommand("SELECT DISTINCT \"PARTS\".\"PART_NAME\" FROM   \"parts_oracle.DEV_RRAMESH\".\"PARTS\" \"PARTS\""); //$NON-NLS-1$
    }

    /** Crystal Reports **/
    @Test public void testQueryWithQuotes_CrystalReports() throws Exception {
        QueryParser.getQueryParser().parseCommand("SELECT \"Oracle_PARTS\".\"PART_COLOR\", \"Oracle_PARTS\".\"PART_ID\", \"Oracle_PARTS\".\"PART_NAME\", \"Oracle_PARTS\".\"PART_WEIGHT\", \"SQL_PARTS\".\"PART_COLOR\", \"SQL_PARTS\".\"PART_ID\", \"SQL_PARTS\".\"PART_NAME\", \"SQL_PARTS\".\"PART_WEIGHT\" FROM   \"parts_oracle.DEV_RRAMESH\".\"PARTS\" \"Oracle_PARTS\", \"parts_sqlserver.dv_rreddy.dv_rreddy\".\"PARTS\" \"SQL_PARTS\" WHERE  (\"Oracle_PARTS\".\"PART_ID\"=\"SQL_PARTS\".\"PART_ID\")"); //$NON-NLS-1$
    }

    @Test public void testOrderByWithNumbers_InQuotes() throws Exception {       
        GroupSymbol g = new GroupSymbol("z"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);
        
		Select select = new Select();
		select.addSymbol(new ElementSymbol("x")); //$NON-NLS-1$
		select.addSymbol(new ElementSymbol("y")); //$NON-NLS-1$

		OrderBy orderby = new OrderBy();
		orderby.addVariable(new ElementSymbol("1"), true); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setOrderBy(orderby);
		
		helpTest("SELECT x, y from z order by \"1\"", "SELECT x, y FROM z ORDER BY \"1\"", query); //$NON-NLS-1$ //$NON-NLS-2$
    }

    @Test public void testOrderByWithNumbers_AsInt() throws Exception {
        GroupSymbol g = new GroupSymbol("z"); //$NON-NLS-1$
        From from = new From();
        from.addGroup(g);
        
		Select select = new Select();
		select.addSymbol(new ElementSymbol("x")); //$NON-NLS-1$
		select.addSymbol(new ElementSymbol("y")); //$NON-NLS-1$

		OrderBy orderby = new OrderBy();
		orderby.addVariable(new Constant(1), true); //$NON-NLS-1$

		Query query = new Query();
		query.setSelect(select);
		query.setFrom(from);
		query.setOrderBy(orderby);        
                
		helpTest("SELECT x, y FROM z order by 1", "SELECT x, y FROM z ORDER BY 1", query); //$NON-NLS-1$ //$NON-NLS-2$        
    }
   
    @Test(expected=QueryParserException.class) public void testOrderByWithNumbers_AsNegitiveInt() throws Exception {
        QueryParser.getQueryParser().parseCommand("SELECT x, y FROM z order by -1"); //$NON-NLS-1$
    } 
    
    @Test public void testEmptyAndNullInputsGiveSameErrorMessage() throws Exception {
        String emptyMessage = null;
        try {
            QueryParser.getQueryParser().parseCommand(""); //$NON-NLS-1$
            fail("Expected exception for parsing empty string"); //$NON-NLS-1$
        } catch(TeiidException e) { 
            emptyMessage = e.getMessage();
        }       

        String nullMessage = null;
        try {
            QueryParser.getQueryParser().parseCommand(null);
            fail("Expected exception for parsing null string"); //$NON-NLS-1$
        } catch(TeiidException e) { 
            nullMessage = e.getMessage();
        }     
        
        assertTrue("Expected same message for empty and null cases", emptyMessage.equals(nullMessage)); //$NON-NLS-1$
    }

    @Test public void testCase3281NamedVariable() {
        StoredProcedure storedQuery = new StoredProcedure();
        storedQuery.setDisplayNamedParameters(true);
        storedQuery.setProcedureName("proc1"); //$NON-NLS-1$
        SPParameter parameter = new SPParameter(1, new Constant("paramValue1")); //$NON-NLS-1$ 
        parameter.setName("param1"); //$NON-NLS-1$
        parameter.setParameterType(ParameterInfo.IN);
        storedQuery.setParameter(parameter);
        helpTest("Exec proc1(param1 = 'paramValue1')", "EXEC proc1(param1 => 'paramValue1')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
        helpTest("execute proc1(param1 = 'paramValue1')", "EXEC proc1(param1 => 'paramValue1')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
    }

    @Test public void testCase3281NamedVariables() {
        StoredProcedure storedQuery = new StoredProcedure();
        storedQuery.setDisplayNamedParameters(true);
        storedQuery.setProcedureName("proc1"); //$NON-NLS-1$
        SPParameter param1 = new SPParameter(1, new Constant("paramValue1")); //$NON-NLS-1$ 
        param1.setName("param1"); //$NON-NLS-1$
        param1.setParameterType(ParameterInfo.IN);
        storedQuery.setParameter(param1);
        SPParameter param2 = new SPParameter(2, new Constant("paramValue2")); //$NON-NLS-1$ 
        param2.setName("param2"); //$NON-NLS-1$
        param2.setParameterType(ParameterInfo.IN);
        storedQuery.setParameter(param2);
        helpTest("Exec proc1(param1 = 'paramValue1', param2 = 'paramValue2')", "EXEC proc1(param1 => 'paramValue1', param2 => 'paramValue2')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
        helpTest("execute proc1(param1 = 'paramValue1', param2 = 'paramValue2')", "EXEC proc1(param1 => 'paramValue1', param2 => 'paramValue2')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
    }

    @Test public void testCase3281QuotedNamedVariableFails2() {
    	StoredProcedure storedQuery = new StoredProcedure();
        storedQuery.setProcedureName("proc1"); //$NON-NLS-1$
        SPParameter param1 = new SPParameter(1, new CompareCriteria(new Constant("a"), CompareCriteria.EQ, new Constant("b"))); //$NON-NLS-1$ 
        param1.setParameterType(ParameterInfo.IN);
        storedQuery.setParameter(param1);
        helpTest("Exec proc1('a' = 'b')", "EXEC proc1(('a' = 'b'))", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
    }

    /** Test what happens if the name of a parameter is a reserved word.  It must be quoted (double-ticks). */
    @Test public void testCase3281NamedVariablesReservedWords() {
        StoredProcedure storedQuery = new StoredProcedure();
        storedQuery.setDisplayNamedParameters(true);
        storedQuery.setProcedureName("proc1"); //$NON-NLS-1$
        SPParameter param1 = new SPParameter(1, new Constant("paramValue1")); //$NON-NLS-1$ 
        param1.setName("in"); //$NON-NLS-1$ //<---RESERVED WORD
        param1.setParameterType(ParameterInfo.IN);
        storedQuery.setParameter(param1);
        SPParameter param2 = new SPParameter(2, new Constant("paramValue2")); //$NON-NLS-1$ 
        param2.setName("in2"); //$NON-NLS-1$
        param2.setParameterType(ParameterInfo.IN);
        storedQuery.setParameter(param2);
        helpTest("Exec proc1(\"in\" = 'paramValue1', in2 = 'paramValue2')", "EXEC proc1(\"in\" => 'paramValue1', in2 => 'paramValue2')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
        helpTest("execute proc1(\"in\" = 'paramValue1', in2 = 'paramValue2')", "EXEC proc1(\"in\" => 'paramValue1', in2 => 'paramValue2')", storedQuery); //$NON-NLS-1$ //$NON-NLS-2$
    }    
    
    @Test public void testExceptionMessageWithLocation() {
        try {
            QueryParser.getQueryParser().parseCommand("SELECT FROM"); //$NON-NLS-1$
        } catch(QueryParserException e) {
            assertTrue(e.getMessage(), e.getMessage().startsWith("TEIID31100 Parsing error: Encountered \"SELECT [*]FROM[*]\" at line 1, column 8.")); //$NON-NLS-1$
        }
    }
    
    @Ignore
    @Test public void testEmptyOuterJoinCriteria() {
        helpException("select a from b left outer join c on ()"); //$NON-NLS-1$
    }
    
    @Test public void testEscapedOuterJoin() {
        String sql = "SELECT * FROM {oj A LEFT OUTER JOIN B ON (A.x=B.x)}"; //$NON-NLS-1$
        String expected = "SELECT * FROM A LEFT OUTER JOIN B ON A.x = B.x"; //$NON-NLS-1$
        
        Query query = new Query();
        Select select = new Select();
        query.setSelect(select);
        select.addSymbol(new MultipleElementSymbol());
        From from = new From();
        query.setFrom(from);
        Criteria compareCriteria = new CompareCriteria(new ElementSymbol("A.x"), CompareCriteria.EQ, new ElementSymbol("B.x")); //$NON-NLS-1$ //$NON-NLS-2$
        FromClause f1 = new UnaryFromClause(new GroupSymbol("A")); //$NON-NLS-1$
        FromClause f2 = new UnaryFromClause(new GroupSymbol("B")); //$NON-NLS-1$
        JoinPredicate jp = new JoinPredicate(f1, f2, JoinType.JOIN_LEFT_OUTER, Arrays.asList(new Object[] {compareCriteria}));
        from.addClause(jp);
        
        helpTest(sql, expected, query);
    } 
    
    @Test public void testBadAlias() {
        String sql = "select a as a.x from foo"; //$NON-NLS-1$
        
        helpException(sql, "TEIID31100 Parsing error: Encountered \"select a as [*]a.x[*] from foo\" at line 1, column 13.\nInvalid alias format: [a.x]"); //$NON-NLS-1$
    }
    
    @Test public void testNameSpacedFunctionName() {
        String sql = "select a.x()"; //$NON-NLS-1$
        
        Query query = new Query();
        Select select = new Select();
        Function func1 = new Function("a.x", new Expression[] { }); //$NON-NLS-1$
        select.addSymbol(func1);        
        query.setSelect(select);
        
        helpTest(sql, "SELECT a.x()", query); //$NON-NLS-1$
    }
    
    @Test public void testUnionJoin() {
        String sql = "select * from pm1.g1 union join pm1.g2 where g1.e1 = 1"; //$NON-NLS-1$
        String expected = "SELECT * FROM pm1.g1 UNION JOIN pm1.g2 WHERE g1.e1 = 1"; //$NON-NLS-1$
        
        Select select = new Select();
        select.addSymbol(new MultipleElementSymbol());
        
        From from = new From();
        from.addClause(new JoinPredicate(new UnaryFromClause(new GroupSymbol("pm1.g1")), new UnaryFromClause(new GroupSymbol("pm1.g2")), JoinType.JOIN_UNION)); //$NON-NLS-1$ //$NON-NLS-2$
        
        Criteria crit = new CompareCriteria(new ElementSymbol("g1.e1"), CompareCriteria.EQ, new Constant(new Integer(1))); //$NON-NLS-1$
        
        Query command = new Query(select, from, crit, null, null);
        helpTest(sql, expected, command);
    }
    
    @Test public void testUnionJoin1() {
        String sql = "select * from pm1.g1 union all join pm1.g2 where g1.e1 = 1"; //$NON-NLS-1$
        
        helpException(sql);
    }
    
    @Test public void testIfElseWithoutBeginEnd() {
        String sql = "CREATE VIRTUAL PROCEDURE BEGIN IF (x > 1) select 1; IF (x > 1) select 1; ELSE select 1; END"; //$NON-NLS-1$
        String expected = "BEGIN\nIF(x > 1)\nBEGIN\nSELECT 1;\nEND\nIF(x > 1)\nBEGIN\nSELECT 1;\nEND\nELSE\nBEGIN\nSELECT 1;\nEND\nEND"; //$NON-NLS-1$
        
        Query query = new Query();
        query.setSelect(new Select(Arrays.asList(new Constant(1)))); //$NON-NLS-1$
        CommandStatement commandStmt = new CommandStatement(query);
        CompareCriteria criteria = new CompareCriteria(new ElementSymbol("x"), CompareCriteria.GT, new Constant(1)); //$NON-NLS-1$
        Block block = new Block();
        block.addStatement(commandStmt);
        IfStatement ifStmt = new IfStatement(criteria, block);
        IfStatement ifStmt1 = (IfStatement)ifStmt.clone();
        Block block2 = new Block();
        block2.addStatement(commandStmt);
        ifStmt1.setElseBlock(block2);
        Block block3 = new Block();
        block3.addStatement(ifStmt);
        block3.addStatement(ifStmt1);
        CreateProcedureCommand command = new CreateProcedureCommand(block3);
        
        helpTest(sql, expected, command);
    }
    
    @Test public void testCommandWithSemicolon() throws Exception {
        helpTest("select * from pm1.g1;", "SELECT * FROM pm1.g1", QueryParser.getQueryParser().parseCommand("select * from pm1.g1")); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
    }
    
    @Test public void testLOBTypes() throws Exception {
        Function convert = new Function("convert", new Expression[] {new Constant(null), new Constant("blob")}); //$NON-NLS-1$ //$NON-NLS-2$
        Function convert1 = new Function("convert", new Expression[] {new Constant(null), new Constant("clob")}); //$NON-NLS-1$ //$NON-NLS-2$
        Function convert2 = new Function("convert", new Expression[] {new Constant(null), new Constant("xml")}); //$NON-NLS-1$ //$NON-NLS-2$
        Select select = new Select(Arrays.asList(convert, convert1, convert2)); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        Query query = new Query();
        query.setSelect(select);
        
        helpTest("select convert(null, blob), convert(null, clob), convert(null, xml)", "SELECT convert(null, blob), convert(null, clob), convert(null, xml)", query); //$NON-NLS-1$ //$NON-NLS-2$
    }

    @Test public void testInsertWithoutColumns() {
        Insert insert = new Insert();
        insert.setGroup(new GroupSymbol("m.g")); //$NON-NLS-1$
        insert.addValue(new Constant("a")); //$NON-NLS-1$
        insert.addValue(new Constant("b")); //$NON-NLS-1$
        helpTest("INSERT INTO m.g VALUES ('a', 'b')",  //$NON-NLS-1$
                 "INSERT INTO m.g VALUES ('a', 'b')",  //$NON-NLS-1$
                 insert);
    }
    
    @Test public void testXmlElement() throws Exception {
    	XMLElement f = new XMLElement("table", Arrays.asList((Expression)new Constant("x")));
    	helpTestExpression("xmlelement(name \"table\", 'x')", "XMLELEMENT(NAME \"table\", 'x')", f);
    }

    @Test public void testXmlElement1() throws Exception {
    	XMLElement f = new XMLElement("table", Arrays.asList((Expression)new Constant("x")));
    	helpTestExpression("xmlelement(\"table\", 'x')", "XMLELEMENT(NAME \"table\", 'x')", f);
    }
    
    @Test public void testXmlElementWithAttributes() throws Exception {
    	XMLElement f = new XMLElement("y", new ArrayList<Expression>());
    	f.setAttributes(new XMLAttributes(Arrays.asList(new DerivedColumn("val", new Constant("a")))));
    	helpTestExpression("xmlelement(y, xmlattributes('a' as val))", "XMLELEMENT(NAME y, XMLATTRIBUTES('a' AS val))", f);
    }
    
    @Test public void testXmlForest() throws Exception {
    	XMLForest f = new XMLForest(Arrays.asList(new DerivedColumn("table", new ElementSymbol("a"))));
    	helpTestExpression("xmlforest(a as \"table\")", "XMLFOREST(a AS \"table\")", f);
    }
    
    @Test public void testXmlPi() throws Exception {
    	Function f = new Function("xmlpi", new Expression[] {new Constant("a"), new ElementSymbol("val")});
    	helpTestExpression("xmlpi(NAME a, val)", "xmlpi(NAME a, val)", f);
    }
    
    @Test public void testXmlNamespaces() throws Exception {
    	XMLForest f = new XMLForest(Arrays.asList(new DerivedColumn("table", new ElementSymbol("a"))));
    	f.setNamespaces(new XMLNamespaces(Arrays.asList(new XMLNamespaces.NamespaceItem(), new XMLNamespaces.NamespaceItem("http://foo", "x"))));
    	helpTestExpression("xmlforest(xmlnamespaces(no default, 'http://foo' as x), a as \"table\")", "XMLFOREST(XMLNAMESPACES(NO DEFAULT, 'http://foo' AS x), a AS \"table\")", f);
    }
    
    @Test public void testXmlAggWithOrderBy() throws Exception {
        String sql = "SELECT xmlAgg(1 order by e2)"; //$NON-NLS-1$
        AggregateSymbol as = new AggregateSymbol(Reserved.XMLAGG, false, new Constant(1));
        as.setOrderBy(new OrderBy(Arrays.asList(new ElementSymbol("e2"))));
        Query query = new Query();
        query.setSelect(new Select(Arrays.asList(as)));
        helpTest(sql, "SELECT XMLAGG(1 ORDER BY e2)", query);
    }
    
    @Test public void testTextAggWithOrderBy() throws Exception {
    	List<DerivedColumn> expressions = new ArrayList<DerivedColumn>();
    	expressions.add(new DerivedColumn("col1", new ElementSymbol("e1")));
    	expressions.add(new DerivedColumn("col2", new ElementSymbol("e2")));
                
        TextLine tf = new TextLine();
        tf.setExpressions(expressions);
        tf.setDelimiter(new Character(','));
        tf.setIncludeHeader(true);
        
        AggregateSymbol as = new AggregateSymbol(NonReserved.TEXTAGG, false, tf);
        as.setOrderBy(new OrderBy(Arrays.asList(new ElementSymbol("e2"))));
        
        Query query = new Query();
        query.setSelect(new Select(Arrays.asList(as)));
        
        String sql = "SELECT TextAgg(FOR e1 as col1, e2 as col2 delimiter ',' header order by e2)"; //$NON-NLS-1$
        helpTest(sql, "SELECT TEXTAGG(FOR e1 AS col1, e2 AS col2 DELIMITER ',' HEADER ORDER BY e2)", query);
    }    
    
    @Test public void testArrayAggWithOrderBy() throws Exception {
        String sql = "SELECT array_agg(1 order by e2)"; //$NON-NLS-1$
        AggregateSymbol as = new AggregateSymbol(Reserved.ARRAY_AGG, false, new Constant(1));
        as.setOrderBy(new OrderBy(Arrays.asList(new ElementSymbol("e2"))));
        Query query = new Query();
        query.setSelect(new Select(Arrays.asList(as)));
        helpTest(sql, "SELECT ARRAY_AGG(1 ORDER BY e2)", query);
    }
    
    @Test public void testArrayAggWithIndexing() throws Exception {
        String sql = "SELECT (array_agg(1))[1]"; //$NON-NLS-1$
        AggregateSymbol as = new AggregateSymbol(Reserved.ARRAY_AGG, false, new Constant(1));
        Query query = new Query();
        query.setSelect(new Select(Arrays.asList(new Function("array_get", new Expression[] {as, new Constant(1)}))));
        helpTest(sql, "SELECT array_get(ARRAY_AGG(1), 1)", query);
    } 
    
    @Test public void testNestedTable() throws Exception {
        String sql = "SELECT * from TABLE(exec foo()) as x"; //$NON-NLS-1$
        Query query = new Query();
        query.setSelect(new Select(Arrays.asList(new MultipleElementSymbol())));
        StoredProcedure sp = new StoredProcedure();
        sp.setProcedureName("foo");
        SubqueryFromClause sfc = new SubqueryFromClause("x", sp);
        sfc.setLateral(true);
        query.setFrom(new From(Arrays.asList(sfc)));
        helpTest(sql, "SELECT * FROM LATERAL(EXEC foo()) AS x", query);
    }
    
    @Test public void testTextTable() throws Exception {
    	String sql = "SELECT * from texttable(file columns x string WIDTH 1, y date width 10 skip 10) as x"; //$NON-NLS-1$
        Query query = new Query();
        query.setSelect(new Select(Arrays.asList(new MultipleElementSymbol())));
        TextTable tt = new TextTable();
        tt.setFile(new ElementSymbol("file"));
        List<TextTable.TextColumn> columns = new ArrayList<TextTable.TextColumn>();
        columns.add(new TextTable.TextColumn("x", "string", 1, false));
        columns.add(new TextTable.TextColumn("y", "date", 10, false));
        tt.setColumns(columns);
        tt.setSkip(10);
        tt.setName("x");
        query.setFrom(new From(Arrays.asList(tt)));
        helpTest(sql, "SELECT * FROM TEXTTABLE(file COLUMNS x string WIDTH 1, y date WIDTH 10 SKIP 10) AS x", query);
        
        sql = "SELECT * from texttable(file columns x string, y date delimiter ',' escape '\"' header skip 10) as x"; //$NON-NLS-1$
        tt.setDelimiter(',');
        tt.setQuote('"');
        tt.setEscape(true);
        tt.setHeader(1);
        for (TextColumn textColumn : columns) {
			textColumn.setWidth(null);
		}
        helpTest(sql, "SELECT * FROM TEXTTABLE(file COLUMNS x string, y date DELIMITER ',' ESCAPE '\"' HEADER SKIP 10) AS x", query);
    }
    
    @Test public void testTextTableColumns() throws Exception {
        helpException("SELECT * from texttable(foo x string)");
    }
    
    @Test public void testXMLTable() throws Exception {
    	String sql = "SELECT * from xmltable(xmlnamespaces(no default), '/' columns x for ordinality, y date default {d'2000-01-01'} path '@date') as x"; //$NON-NLS-1$
        Query query = new Query();
        query.setSelect(new Select(Arrays.asList(new MultipleElementSymbol())));
        XMLTable xt = new XMLTable();
        xt.setName("x");
        xt.setNamespaces(new XMLNamespaces(Arrays.asList(new XMLNamespaces.NamespaceItem())));
        xt.setXquery("/");
        List<XMLTable.XMLColumn> columns = new ArrayList<XMLTable.XMLColumn>();
        columns.add(new XMLTable.XMLColumn("x"));
        columns.add(new XMLTable.XMLColumn("y", "date", "@date", new Constant(Date.valueOf("2000-01-01"))));
        xt.setColumns(columns);
        query.setFrom(new From(Arrays.asList(xt)));
        helpTest(sql, "SELECT * FROM XMLTABLE(XMLNAMESPACES(NO DEFAULT), '/' COLUMNS x FOR ORDINALITY, y date DEFAULT {d'2000-01-01'} PATH '@date') AS x", query);
    }
    
    @Test public void testObjectTable() throws Exception {
    	Query actualCommand = (Query)QueryParser.getQueryParser().parseCommand("SELECT * from objecttable(LANGUAGE 'foo' 'x' columns y date 'row.date' default {d'2000-01-01'}) as x", new ParseInfo());
		assertEquals("SELECT * FROM OBJECTTABLE(LANGUAGE 'foo' 'x' COLUMNS y date 'row.date' DEFAULT {d'2000-01-01'}) AS x", actualCommand.toString());
    }
    
    @Test public void testObjectTable1() throws Exception {
    	Query query = new Query();
    	query.setSelect(new Select(Arrays.asList(new MultipleElementSymbol())));
    	ObjectTable objectTable = new ObjectTable();
    	objectTable.setRowScript("y");
    	objectTable.setPassing(Arrays.asList(new DerivedColumn("y", new ElementSymbol("e1"))));
    	objectTable.setColumns(Arrays.asList(new ObjectTable.ObjectColumn("z", "time", "now()", null)));
    	objectTable.setName("x");
		query.setFrom(new From(Arrays.asList(objectTable)));
    	helpTest("select * from objecttable('y' passing e1 as y columns z time 'now()') as x", "SELECT * FROM OBJECTTABLE('y' PASSING e1 AS y COLUMNS z time 'now()') AS x", query);
    }
    
    @Test public void testXmlSerialize() throws Exception {
    	XMLSerialize f = new XMLSerialize();
    	f.setDocument(true);
    	f.setExpression(new ElementSymbol("x"));
    	f.setTypeString("CLOB");
    	helpTestExpression("xmlserialize(document x as CLOB)", "XMLSERIALIZE(DOCUMENT x AS CLOB)", f);
    }
    
    @Test public void testXmlQuery() throws Exception {
    	XMLQuery f = new XMLQuery();
    	f.setXquery("/x");
    	f.setEmptyOnEmpty(false);
    	f.setPassing(Arrays.asList(new DerivedColumn(null, new ElementSymbol("foo"))));
    	helpTestExpression("xmlquery('/x' passing foo null on empty)", "XMLQUERY('/x' PASSING foo NULL ON EMPTY)", f);
    }
    
    @Test public void testXmlParse() throws Exception {
    	XMLParse f = new XMLParse();
    	f.setDocument(true);
    	f.setExpression(new ElementSymbol("x"));
    	f.setWellFormed(true);
    	helpTestExpression("xmlparse(document x wellformed)", "XMLPARSE(DOCUMENT x WELLFORMED)", f);
    }
    
    @Test public void testXmlSerialize1() throws Exception {
    	XMLSerialize f = new XMLSerialize();
    	f.setExpression(new ElementSymbol("x"));
    	f.setTypeString("CLOB");
    	helpTestExpression("xmlserialize(x as CLOB)", "XMLSERIALIZE(x AS CLOB)", f);
    }
    
    @Test public void testXmlSerialize2() throws Exception {
    	XMLSerialize f = new XMLSerialize();
    	f.setExpression(new ElementSymbol("x"));
    	f.setTypeString("BLOB");
    	f.setDeclaration(Boolean.TRUE);
    	f.setVersion("1.0");
    	f.setEncoding("UTF-8");
    	helpTestExpression("xmlserialize(x as BLOB encoding \"UTF-8\" version '1.0' INCLUDING xmldeclaration)", "XMLSERIALIZE(x AS BLOB ENCODING \"UTF-8\" VERSION '1.0' INCLUDING XMLDECLARATION)", f);
    }
    
    @Test public void testExpressionCriteria() throws Exception {
    	SearchedCaseExpression sce = new SearchedCaseExpression(Arrays.asList(new ExpressionCriteria(new ElementSymbol("x"))), Arrays.asList(new ElementSymbol("y")));
    	helpTestExpression("case when x then y end", "CASE WHEN x THEN y END", sce);
    }
    
    @Test public void testExpressionCriteria1() throws Exception {
    	SearchedCaseExpression sce = new SearchedCaseExpression(Arrays.asList(new NotCriteria(new ExpressionCriteria(new ElementSymbol("x")))), Arrays.asList(new ElementSymbol("y")));
    	helpTestExpression("case when not x then y end", "CASE WHEN NOT (x) THEN y END", sce);
    }
    
    @Test public void testWithClause() throws Exception {
    	Query query = getOrderByQuery(null);
    	query.setWith(Arrays.asList(new WithQueryCommand(new GroupSymbol("x"), null, getOrderByQuery(null))));
        helpTest("WITH x AS (SELECT a FROM db.g WHERE b = aString) SELECT a FROM db.g WHERE b = aString", "WITH x AS (SELECT a FROM db.g WHERE b = aString) SELECT a FROM db.g WHERE b = aString", query); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testExplicitTable() throws Exception {
    	Query query = new Query();
        Select select = new Select();
        query.setSelect(select);
        select.addSymbol(new MultipleElementSymbol());
        From from = new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("X"))));
        query.setFrom(from);
    	helpTest("TABLE X", "SELECT * FROM X", query);
    }
    
    @Test public void testArrayTable() throws Exception {
    	String sql = "SELECT * from arraytable(null columns x string, y date) as x"; //$NON-NLS-1$
        Query query = new Query();
        query.setSelect(new Select(Arrays.asList(new MultipleElementSymbol())));
        ArrayTable tt = new ArrayTable();
        tt.setArrayValue(new Constant(null, DataTypeManager.DefaultDataClasses.NULL));
        List<TableFunctionReference.ProjectedColumn> columns = new ArrayList<TableFunctionReference.ProjectedColumn>();
        columns.add(new TableFunctionReference.ProjectedColumn("x", "string"));
        columns.add(new TableFunctionReference.ProjectedColumn("y", "date"));
        tt.setColumns(columns);
        tt.setName("x");
        query.setFrom(new From(Arrays.asList(tt)));
        helpTest(sql, "SELECT * FROM ARRAYTABLE(null COLUMNS x string, y date) AS x", query);
    }
    
    @Test public void testPositionalReference() throws Exception {
    	String sql = "select $1";
    	Query query = new Query();
    	query.setSelect(new Select(Arrays.asList(new Reference(0))));
        helpTest(sql, "SELECT ?", query);
    }
    
    @Test public void testNonReserved() throws Exception {
    	String sql = "select count";
    	Query query = new Query();
    	query.setSelect(new Select(Arrays.asList(new ElementSymbol("count"))));
        helpTest(sql, "SELECT count", query);
    }
    
    @Test public void testAggFilter() throws Exception {
    	String sql = "select count(*) filter (where x = 1) from g";
    	Query query = new Query();
    	AggregateSymbol aggregateSymbol = new AggregateSymbol(AggregateSymbol.Type.COUNT.name(), false, null);
    	aggregateSymbol.setCondition(new CompareCriteria(new ElementSymbol("x"), CompareCriteria.EQ, new Constant(1)));
    	query.setSelect(new Select(Arrays.asList(aggregateSymbol)));
    	query.setFrom(new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("g")))));
        helpTest(sql, "SELECT COUNT(*) FILTER(WHERE x = 1) FROM g", query);
    }
    
    @Test public void testWindowFunction() throws Exception {
    	String sql = "select row_number() over (partition by x order by y) from g";
    	Query query = new Query();
    	WindowFunction wf = new WindowFunction();
    	wf.setFunction(new AggregateSymbol("ROW_NUMBER", false, null));
    	WindowSpecification ws = new WindowSpecification();
    	ws.setPartition(new ArrayList<Expression>(Arrays.asList(new ElementSymbol("x"))));
    	ws.setOrderBy(new OrderBy(Arrays.asList(new ElementSymbol("y"))));
    	wf.setWindowSpecification(ws);
    	query.setSelect(new Select(Arrays.asList(wf)));
    	query.setFrom(new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("g")))));
        helpTest(sql, "SELECT ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) FROM g", query);
    }
    
    @Test public void testTrim1() {
    	helpException("select trim('xy' from e1) from pm1.g1");
    }
    
    @Test public void testSubString() throws QueryParserException {
		Query actualCommand = (Query)QueryParser.getQueryParser().parseCommand("SELECT substring(RTRIM(MED.BATDAT), 4, 4) FROM FCC.MEDMAS AS MED", new ParseInfo());
		String actualString = actualCommand.toString();
		assertEquals("SELECT substring(RTRIM(MED.BATDAT), 4, 4) FROM FCC.MEDMAS AS MED", actualString);
    }
    
    @Test public void testExactFixedPoint() throws QueryParserException {
		Query actualCommand = (Query)QueryParser.getQueryParser().parseCommand("SELECT 1.1", new ParseInfo());
		assertEquals(DataTypeManager.DefaultDataClasses.BIG_DECIMAL, actualCommand.getSelect().getSymbol(0).getType());
    }
    
    @Test public void testBinaryStringLiteral() throws QueryParserException {
		Query actualCommand = (Query)QueryParser.getQueryParser().parseCommand("SELECT x'AABBCC0a'", new ParseInfo());
		assertEquals(DataTypeManager.DefaultDataClasses.VARBINARY, actualCommand.getSelect().getSymbol(0).getType());
		assertEquals("SELECT X'AABBCC0A'", actualCommand.toString());
    }
    
    @Test public void testUserDefinedAggregateParsing() throws QueryParserException {
		Query actualCommand = (Query)QueryParser.getQueryParser().parseCommand("SELECT foo(ALL x, y)", new ParseInfo());
		assertEquals("SELECT foo(ALL x, y)", actualCommand.toString());
    }
    
    @Test public void testUserDefinedAggregateParsing1() throws QueryParserException {
		Query actualCommand = (Query)QueryParser.getQueryParser().parseCommand("SELECT foo(x, y order by e1)", new ParseInfo());
		assertEquals("SELECT foo(ALL x, y ORDER BY e1)", actualCommand.toString());
    }

    @Test public void testWindowedExpression() throws QueryParserException {
		QueryParser.getQueryParser().parseCommand("SELECT foo(x, y) over ()", new ParseInfo());
    }
    
    @Test public void testWindowedExpression1() throws QueryParserException {
		Query actualCommand = (Query)QueryParser.getQueryParser().parseCommand("SELECT foo(distinct x, y) over ()", new ParseInfo());
		assertEquals("SELECT foo(DISTINCT x, y) OVER ()", actualCommand.toString());
    }
    
    @Test public void testInvalidLimit() {
        helpException("SELECT * FROM pm1.g1 LIMIT -5");
    }

    @Test public void testInvalidLimit_Offset() {
    	helpException("SELECT * FROM pm1.g1 LIMIT -1, 100");
    }
    
    @Test public void testTextTableNegativeWidth() {        
        helpException("SELECT * from texttable(null columns x string width -1) as x"); 
	}
    
    @Test public void testBlockExceptionHandling() throws ParseException {
    	CommandStatement cmdStmt =	new CommandStatement(new Query(new Select(Arrays.asList(new MultipleElementSymbol())), new From(Arrays.asList(new UnaryFromClause(new GroupSymbol("x")))), null, null, null));
    	AssignmentStatement assigStmt =	new AssignmentStatement(new ElementSymbol("a"), new Constant(new Integer(1))); //$NON-NLS-1$
    	RaiseStatement errStmt =	new RaiseStatement(new Constant("My Error")); //$NON-NLS-1$
    	Block b = new Block();
    	b.setExceptionGroup("e");
    	b.addStatement(cmdStmt);
    	b.addStatement(assigStmt);
    	b.addStatement(errStmt, true);
    	helpStmtTest("BEGIN\nselect * from x;\na = 1;\nexception e\nERROR 'My Error';\nEND", "BEGIN\nSELECT * FROM x;\na = 1;\nEXCEPTION e\nRAISE SQLEXCEPTION 'My Error';\nEND", b); //$NON-NLS-1$
    }
    
    @Test public void testJSONObject() throws Exception {
    	JSONObject f = new JSONObject(Arrays.asList(new DerivedColumn("table", new ElementSymbol("a"))));
    	helpTestExpression("jsonObject(a as \"table\")", "JSONOBJECT(a AS \"table\")", f);
    }
    
    @Test public void testLineComment() {
    	String sql = "select 1 -- some comment";
    	Query query = new Query();
    	query.setSelect(new Select(Arrays.asList(new Constant(1))));
        helpTest(sql, "SELECT 1", query);
    }
    
    @Test public void testTrimExpression() throws QueryParserException {
    	String sql = "select trim(substring(Description, pos1+1))";
    	Query actualCommand = (Query)QueryParser.getQueryParser().parseCommand(sql, new ParseInfo());
		assertEquals("SELECT trim(' ' FROM substring(Description, (pos1 + 1)))", actualCommand.toString());
    }
    
    @Test public void testDateTimeKeywordLiterals() throws QueryParserException {
    	String sql = "select DATE '1970-01-02', TIME '00:01:02', TIMESTAMP '2001-01-01 02:03:04.1'";
    	Query actualCommand = (Query)QueryParser.getQueryParser().parseCommand(sql, new ParseInfo());
		assertEquals("SELECT {d'1970-01-02'}, {t'00:01:02'}, {ts'2001-01-01 02:03:04.1'}", actualCommand.toString()); 
    }
    
    @Test public void testDoubleAmp() {
    	String sql = "select 1 && 2";
    	Query query = new Query();
    	query.setSelect(new Select(Arrays.asList(new Function(SQLConstants.Tokens.DOUBLE_AMP, new Expression[] {new Constant(1), new Constant(2)}))));
        helpTest(sql, "SELECT (1 && 2)", query);
    }
    
    @Test public void testGeometryAlias() {
        String sql = "SELECT y AS geometry";
        AliasSymbol as = new AliasSymbol("geometry", new ElementSymbol("y")); //$NON-NLS-1$ //$NON-NLS-2$
        Query query = new Query();
        query.setSelect(new Select(Arrays.asList(as)));
        helpTest(sql, sql, query); //$NON-NLS-1$
    }
    
    @Test public void testUnderscoreAlias() {
        String sql = "SELECT y AS _name";
        AliasSymbol as = new AliasSymbol("_name", new ElementSymbol("y")); //$NON-NLS-1$ //$NON-NLS-2$
        Query query = new Query();
        query.setSelect(new Select(Arrays.asList(as)));
        helpTest(sql, "SELECT y AS \"_name\"", query); //$NON-NLS-1$
    }

}
