/*
 * 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.translator.jdbc;

import static org.junit.Assert.*;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Types;

import org.junit.BeforeClass;
import org.junit.Test;
import org.mockito.Mockito;
import org.teiid.adminapi.impl.SessionMetadata;
import org.teiid.dqp.internal.datamgr.ExecutionContextImpl;
import org.teiid.dqp.internal.datamgr.TestDeleteImpl;
import org.teiid.dqp.internal.datamgr.TestInsertImpl;
import org.teiid.dqp.internal.datamgr.TestProcedureImpl;
import org.teiid.dqp.internal.datamgr.TestQueryImpl;
import org.teiid.dqp.internal.datamgr.TestUpdateImpl;
import org.teiid.language.LanguageObject;
import org.teiid.translator.ExecutionContext;
import org.teiid.translator.TranslatorException;

/**
 */
@SuppressWarnings("nls")
public class TestSQLConversionVisitor {

    public static final ExecutionContextImpl context = new ExecutionContextImpl("VDB",  //$NON-NLS-1$
                                                                            1, 
                                                                            "Payload",  //$NON-NLS-1$
                                                                            "ConnectionID",   //$NON-NLS-1$
                                                                            "Connector", //$NON-NLS-1$
                                                                            1, 
                                                                            "PartID",  //$NON-NLS-1$ 
                                                                            "ExecCount");     //$NON-NLS-1$ 
    
    static {
    	context.setSession(new SessionMetadata());
    }
    
    private static JDBCExecutionFactory TRANSLATOR; 
    
    @BeforeClass public static void oneTimeSetup() throws TranslatorException {
    	TRANSLATOR = new JDBCExecutionFactory();
    	TRANSLATOR.setTrimStrings(true);
    	TRANSLATOR.setUseBindVariables(false);
    	TRANSLATOR.start();
    }
    
    public String getTestVDB() {
        return TranslationHelper.PARTS_VDB;
    }
    
    public void helpTestVisitor(String vdb, String input, String expectedOutput) {
        helpTestVisitor(vdb, input, expectedOutput, false);
    }
    
    public TranslatedCommand helpTestVisitor(String vdb, String input, String expectedOutput, boolean usePreparedStatement) {
    	JDBCExecutionFactory trans = new JDBCExecutionFactory();
    	trans.setUseBindVariables(usePreparedStatement);
        try {
			trans.start();
	        return TranslationHelper.helpTestVisitor(vdb, input, expectedOutput, trans);
		} catch (TranslatorException e) {
			throw new RuntimeException(e);
		}
    }

    private String getStringWithContext(LanguageObject obj) throws TranslatorException {
    	return getStringWithContext(obj, null);
    }
    
    private String getStringWithContext(LanguageObject obj, String comment) throws TranslatorException {
    	JDBCExecutionFactory env = new JDBCExecutionFactory();
    	env.setUseCommentsInSourceQuery(true);
    	env.setUseBindVariables(false);
    	if (comment != null) {
    		env.setCommentFormat(comment);
    	}
        env.start();
        
        SQLConversionVisitor visitor = env.getSQLConversionVisitor();
        visitor.setExecutionContext(context);
        visitor.append(obj);
        return visitor.toString();
    }  
    
    @Test public void testSimple() {
        helpTestVisitor(getTestVDB(),
            "select part_name from parts", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME FROM PARTS"); //$NON-NLS-1$
    }

    @Test public void testAliasInSelect() {
        helpTestVisitor(getTestVDB(),
            "select part_name as x from parts", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME AS x FROM PARTS"); //$NON-NLS-1$
    }

    @Test public void testAliasedGroup() {
        helpTestVisitor(getTestVDB(),
            "select y.part_name from parts y", //$NON-NLS-1$
            "SELECT y.PART_NAME FROM PARTS AS y"); //$NON-NLS-1$
    }

    @Test public void testAliasedGroupAndElement() {
        helpTestVisitor(getTestVDB(),
            "select y.part_name AS z from parts y", //$NON-NLS-1$
            "SELECT y.PART_NAME AS z FROM PARTS AS y"); //$NON-NLS-1$
    }

    @Test public void testLiteralString() {
        helpTestVisitor(getTestVDB(),
            "select 'x' from parts", //$NON-NLS-1$
            "SELECT 'x' FROM PARTS"); //$NON-NLS-1$
    }

    @Test public void testLiteralInteger() {
        helpTestVisitor(getTestVDB(),
            "select 5 from parts", //$NON-NLS-1$
            "SELECT 5 FROM PARTS"); //$NON-NLS-1$
    }

    @Test public void testLiteralFloat() {
        helpTestVisitor(getTestVDB(),
            "select 5.2 from parts", //$NON-NLS-1$
            "SELECT 5.2 FROM PARTS"); //$NON-NLS-1$
    }

    @Test public void testLiteralLowFloat() {
        helpTestVisitor(getTestVDB(),
            "select 0.012 from parts", //$NON-NLS-1$
            "SELECT 0.012 FROM PARTS"); //$NON-NLS-1$
    }
    
    @Test public void testLiteralLowFloat2() {
        helpTestVisitor(getTestVDB(),
            "select 0.00012 from parts", //$NON-NLS-1$
            "SELECT 0.00012 FROM PARTS"); //$NON-NLS-1$
    }    
    
    @Test public void testLiteralHighFloat() {
        helpTestVisitor(getTestVDB(),
            "select 12345.123 from parts", //$NON-NLS-1$
            "SELECT 12345.123 FROM PARTS"); //$NON-NLS-1$
    }

    @Test public void testLiteralHighFloat2() {
        helpTestVisitor(getTestVDB(),
            "select 1234567890.1234567 from parts", //$NON-NLS-1$
            "SELECT 1234567890.1234567 FROM PARTS"); //$NON-NLS-1$
    }
    
    @Test public void testLiteralBoolean() {
        helpTestVisitor(getTestVDB(),
            "select {b'true'}, {b'false'} from parts", //$NON-NLS-1$
            "SELECT 1, 0 FROM PARTS"); //$NON-NLS-1$
    }

    @Test public void testLiteralDate() {
        helpTestVisitor(getTestVDB(),
            "select {d '2003-12-31'} from parts", //$NON-NLS-1$
            "SELECT {d '2003-12-31'} FROM PARTS"); //$NON-NLS-1$
    }

    @Test public void testLiteralTime() {
        helpTestVisitor(getTestVDB(),
            "select {t '23:59:59'} from parts", //$NON-NLS-1$
            "SELECT {t '23:59:59'} FROM PARTS"); //$NON-NLS-1$
    }

    @Test public void testLiteralNull() {
        helpTestVisitor(getTestVDB(),
            "select null from parts", //$NON-NLS-1$
            "SELECT NULL FROM PARTS"); //$NON-NLS-1$
    }

    @Test public void testLiteralTimestamp() {
        helpTestVisitor(getTestVDB(),
            "select {ts '2003-12-31 23:59:59.123'} from parts", //$NON-NLS-1$
            "SELECT {ts '2003-12-31 23:59:59.123'} FROM PARTS"); //$NON-NLS-1$
    }

    @Test public void testSQL89Join() {
        helpTestVisitor(getTestVDB(),
            "select p.part_name from parts p, supplier_parts s where p.part_id = s.part_id", //$NON-NLS-1$
            "SELECT p.PART_NAME FROM PARTS AS p, SUPPLIER_PARTS AS s WHERE p.PART_ID = s.PART_ID"); //$NON-NLS-1$
    }

    @Test public void testSQL92Join() {
        helpTestVisitor(getTestVDB(),
            "select p.part_name from parts p join supplier_parts s on p.part_id = s.part_id", //$NON-NLS-1$
            "SELECT p.PART_NAME FROM PARTS AS p INNER JOIN SUPPLIER_PARTS AS s ON p.PART_ID = s.PART_ID"); //$NON-NLS-1$
    }

    @Test public void testSelfJoin() {
        helpTestVisitor(getTestVDB(),
            "select p.part_name from parts p join parts p2 on p.part_id = p2.part_id", //$NON-NLS-1$
            "SELECT p.PART_NAME FROM PARTS AS p INNER JOIN PARTS AS p2 ON p.PART_ID = p2.PART_ID"); //$NON-NLS-1$
    }

    @Test public void testRightOuterJoin() {
        helpTestVisitor(getTestVDB(),
            "select p.part_name from parts p right join supplier_parts s on p.part_id = s.part_id", //$NON-NLS-1$
            "SELECT p.PART_NAME FROM SUPPLIER_PARTS AS s LEFT OUTER JOIN PARTS AS p ON p.PART_ID = s.PART_ID"); //$NON-NLS-1$
    }

    @Test public void testLeftOuterJoin() {
        helpTestVisitor(getTestVDB(),
            "select p.part_name from parts p left join supplier_parts s on p.part_id = s.part_id", //$NON-NLS-1$
            "SELECT p.PART_NAME FROM PARTS AS p LEFT OUTER JOIN SUPPLIER_PARTS AS s ON p.PART_ID = s.PART_ID"); //$NON-NLS-1$
    }

    @Test public void testFullOuterJoin() {
        helpTestVisitor(getTestVDB(),
            "select p.part_name from parts p full join supplier_parts s on p.part_id = s.part_id", //$NON-NLS-1$
            "SELECT p.PART_NAME FROM PARTS AS p FULL OUTER JOIN SUPPLIER_PARTS AS s ON p.PART_ID = s.PART_ID"); //$NON-NLS-1$
    }

    @Test public void testCompare1() {
        helpTestVisitor(getTestVDB(),
            "select part_name from parts where part_id = 'x'", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID = 'x'"); //$NON-NLS-1$
    }

    @Test public void testCompare2() {
        helpTestVisitor(getTestVDB(),
            "select part_name from parts where part_id <> 'x'", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID <> 'x'"); //$NON-NLS-1$
    }

    @Test public void testCompare3() {
        helpTestVisitor(getTestVDB(),
            "select part_name from parts where part_id < 'x'", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID < 'x'"); //$NON-NLS-1$
    }

    @Test public void testCompare4() {
        helpTestVisitor(getTestVDB(),
            "select part_name from parts where part_id <= 'x'", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID <= 'x'"); //$NON-NLS-1$
    }

    @Test public void testCompare5() {
        helpTestVisitor(getTestVDB(),
            "select part_name from parts where part_id > 'x'", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID > 'x'"); //$NON-NLS-1$
    }

    @Test public void testCompare6() {
        helpTestVisitor(getTestVDB(),
            "select part_name from parts where part_id >= 'x'", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID >= 'x'"); //$NON-NLS-1$
    }

    @Test public void testIn1() {
        helpTestVisitor(getTestVDB(),
            "select part_name from parts where part_id in ('x')", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID = 'x'"); //$NON-NLS-1$
    }

    @Test public void testIn2() {
        helpTestVisitor(getTestVDB(),
            "select part_name from parts where part_id in ('x', 'y')", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID IN ('x', 'y')"); //$NON-NLS-1$
    }

    @Test public void testIn3() {
        helpTestVisitor(getTestVDB(),
            "select part_name from parts where part_id not in ('x', 'y')", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID NOT IN ('x', 'y')"); //$NON-NLS-1$
    }

    @Test public void testIsNull1() {
        helpTestVisitor(getTestVDB(),
            "select part_name from parts where part_id is null", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID IS NULL"); //$NON-NLS-1$
    }

    @Test public void testIsNull2() {
        helpTestVisitor(getTestVDB(),
            "select part_name from parts where part_id is not null", //$NON-NLS-1$
            "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID IS NOT NULL"); //$NON-NLS-1$
    }

    @Test public void testInsertNull() {
        helpTestVisitor(getTestVDB(),
            "insert into parts (part_id, part_name, part_color, part_weight) values ('a', null, 'c', 'd')", //$NON-NLS-1$
            "INSERT INTO PARTS (PART_ID, PART_NAME, PART_COLOR, PART_WEIGHT) VALUES ('a', NULL, 'c', 'd')"); //$NON-NLS-1$
    }

    @Test public void testUpdateNull() {
        helpTestVisitor(getTestVDB(),
            "update parts set part_weight = null where part_color = 'b'", //$NON-NLS-1$
            "UPDATE PARTS SET PART_WEIGHT = NULL WHERE PARTS.PART_COLOR = 'b'"); //$NON-NLS-1$
    }

    @Test public void testUpdateWhereNull() {
        helpTestVisitor(getTestVDB(),
            "update parts set part_weight = 'a' where part_weight = null", //$NON-NLS-1$
            "UPDATE PARTS SET PART_WEIGHT = 'a' WHERE NULL <> NULL"); //$NON-NLS-1$
    }

    @Test public void testPreparedStatementCreationWithUpdate() {
        helpTestVisitor(getTestVDB(),
                        "update parts set part_weight = 'a' || 'b' where part_weight < 50/10", //$NON-NLS-1$
                        "UPDATE PARTS SET PART_WEIGHT = ? WHERE PARTS.PART_WEIGHT < ?", //$NON-NLS-1$
                        true); 
    }
    
    @Test public void testPreparedStatementCreationWithInsert() {
        helpTestVisitor(getTestVDB(),
                        "insert into parts (part_weight) values (50/10)", //$NON-NLS-1$
                        "INSERT INTO PARTS (PART_WEIGHT) VALUES (?)", //$NON-NLS-1$
                        true); 
    }
    
    @Test public void testPreparedStatementCreationWithSelect() {
        helpTestVisitor(getTestVDB(),
                        "select part_name from parts where part_id not in ('x' || 'a', 'y' || 'b') and part_weight < '6'", //$NON-NLS-1$
                        "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_ID NOT IN (?, ?) AND PARTS.PART_WEIGHT < '6'", //$NON-NLS-1$
                        true); 
    }
    
    @Test public void testPreparedStatementCreationWithLike() {
        helpTestVisitor(getTestVDB(),
                        "select part_name from parts where part_name like '%foo' || '_'", //$NON-NLS-1$
                        "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_NAME LIKE ?", //$NON-NLS-1$
                        true); 
    }
    
    /**
     * ideally this should not happen, but to be on the safe side 
     * only the right side should get replaced
     */
    public void defer_testPreparedStatementCreationWithLeftConstant() {
        helpTestVisitor(getTestVDB(),
                        "select part_name from parts where 'x' = 'y'", //$NON-NLS-1$
                        "SELECT PARTS.PART_NAME FROM PARTS WHERE 1 = ?", //$NON-NLS-1$
                        true); 
    }
    
    /**
     * In the future, functions can be made smarter about which of their literal arguments
     * either are (or are not) eligible to be bind variables 
     */
    @Test public void testPreparedStatementCreationWithFunction() {
        helpTestVisitor(getTestVDB(),
                        "select part_name from parts where concat(part_name, 'x') = concat('y', part_weight)", //$NON-NLS-1$
                        "SELECT PARTS.PART_NAME FROM PARTS WHERE concat(PARTS.PART_NAME, 'x') = concat('y', PARTS.PART_WEIGHT)", //$NON-NLS-1$
                        true); 
    }
    
    @Test public void testPreparedStatementCreationWithCase() {
        helpTestVisitor(getTestVDB(),
                        "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_WEIGHT = CASE WHEN PARTS.PART_NAME='a' || 'b' THEN 'b' ELSE 'c' END", //$NON-NLS-1$
                        "SELECT PARTS.PART_NAME FROM PARTS WHERE PARTS.PART_WEIGHT = CASE WHEN PARTS.PART_NAME = ? THEN 'b' ELSE 'c' END", //$NON-NLS-1$
                        true); 
    }

    @Test public void testVisitIDeleteWithComment() throws Exception {
        String expected = "DELETE /*teiid sessionid:ConnectionID, requestid:ConnectionID.1.PartID*/ FROM g1 WHERE 100 >= 200 AND 500 < 600"; //$NON-NLS-1$
        assertEquals(expected, getStringWithContext(TestDeleteImpl.example()));
    }

    @Test public void testVisitIInsertWithComment() throws Exception {
        String expected = "INSERT /*teiid sessionid:ConnectionID, requestid:ConnectionID.1.PartID*/ INTO g1 (e1, e2, e3, e4) VALUES (1, 2, 3, 4)"; //$NON-NLS-1$
        assertEquals(expected, getStringWithContext(TestInsertImpl.example("g1"))); //$NON-NLS-1$
    }  
    
    @Test public void testVisitISelectWithComment() throws Exception {
        String expected = "SELECT /*teiid sessionid:ConnectionID, requestid:ConnectionID.1.PartID*/ g1.e1, g1.e2, g1.e3, g1.e4 FROM g1, g2 AS myAlias, g3, g4 WHERE 100 >= 200 AND 500 < 600 GROUP BY g1.e1, g1.e2, g1.e3, g1.e4 HAVING 100 >= 200 AND 500 < 600 ORDER BY g1.e1, g1.e2 DESC, g1.e3, g1.e4 DESC"; //$NON-NLS-1$
        assertEquals(expected, getStringWithContext(TestQueryImpl.example(false)));
        expected = "SELECT /*teiid sessionid:ConnectionID, requestid:ConnectionID.1.PartID*/ DISTINCT g1.e1, g1.e2, g1.e3, g1.e4 FROM g1, g2 AS myAlias, g3, g4 WHERE 100 >= 200 AND 500 < 600 GROUP BY g1.e1, g1.e2, g1.e3, g1.e4 HAVING 100 >= 200 AND 500 < 600 ORDER BY g1.e1, g1.e2 DESC, g1.e3, g1.e4 DESC"; //$NON-NLS-1$
        assertEquals(expected, getStringWithContext(TestQueryImpl.example(true)));
    }
    
    @Test public void testVisitSelectWithCustomComment() throws Exception {
        String expected = "SELECT /* foo ConnectionID ConnectionID.1 PartID ExecCount null VDB 1 false */g1.e1, g1.e2, g1.e3, g1.e4 FROM g1, g2 AS myAlias, g3, g4 WHERE 100 >= 200 AND 500 < 600 GROUP BY g1.e1, g1.e2, g1.e3, g1.e4 HAVING 100 >= 200 AND 500 < 600 ORDER BY g1.e1, g1.e2 DESC, g1.e3, g1.e4 DESC"; //$NON-NLS-1$
        assertEquals(expected, getStringWithContext(TestQueryImpl.example(false), "/* foo {0} {1} {2} {3} {4} {5} {6} {7} */"));
    }
    
    @Test public void testVisitIUpdateWithComment() throws Exception {
        String expected = "UPDATE /*teiid sessionid:ConnectionID, requestid:ConnectionID.1.PartID*/ g1 SET e1 = 1, e2 = 1, e3 = 1, e4 = 1 WHERE 1 = 1"; //$NON-NLS-1$
        assertEquals(expected, getStringWithContext(TestUpdateImpl.example()));
    }    
    
    @Test public void testVisitIProcedureWithComment() throws Exception {
        String expected = "/*teiid sessionid:ConnectionID, requestid:ConnectionID.1.PartID*/ {call sq3(?,?)}"; //$NON-NLS-1$
        assertEquals(expected, getStringWithContext(TestProcedureImpl.example()));
    }  
    
    @Test public void testTrimStrings() throws Exception {
        TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, "select stringkey from bqt1.smalla", "SELECT rtrim(SmallA.StringKey) FROM SmallA", TRANSLATOR); //$NON-NLS-1$ //$NON-NLS-2$
    }
    
    @Test public void testNestedSetQuery() throws Exception {
    	String input = "select part_id id FROM parts UNION ALL (select part_name FROM parts UNION select part_id FROM parts)"; //$NON-NLS-1$
        String output = "SELECT rtrim(PARTS.PART_ID) AS id FROM PARTS UNION ALL (SELECT PARTS.PART_NAME FROM PARTS UNION SELECT rtrim(PARTS.PART_ID) FROM PARTS)"; //$NON-NLS-1$
          
        TranslationHelper.helpTestVisitor(TranslationHelper.PARTS_VDB,
            input, 
            output, TRANSLATOR);
    }
    
    @Test public void testNestedSetQuery1() throws Exception {
    	String input = "select part_id id FROM parts UNION (select part_name FROM parts EXCEPT select part_id FROM parts)"; //$NON-NLS-1$
        String output = "SELECT rtrim(PARTS.PART_ID) AS id FROM PARTS UNION (SELECT PARTS.PART_NAME FROM PARTS EXCEPT SELECT rtrim(PARTS.PART_ID) FROM PARTS)"; //$NON-NLS-1$
          
        TranslationHelper.helpTestVisitor(TranslationHelper.PARTS_VDB,
            input, 
            output, TRANSLATOR);
    }
    
    @Test public void testNestedSetQuery2() throws Exception {
    	String input = "select part_id id FROM parts UNION select part_name FROM parts EXCEPT select part_id FROM parts"; //$NON-NLS-1$
        String output = "SELECT rtrim(PARTS.PART_ID) AS id FROM PARTS UNION SELECT PARTS.PART_NAME FROM PARTS EXCEPT SELECT rtrim(PARTS.PART_ID) FROM PARTS"; //$NON-NLS-1$
          
        TranslationHelper.helpTestVisitor(TranslationHelper.PARTS_VDB,
            input, 
            output, TRANSLATOR);
    }
    
    @Test public void testNestedSetQuery3() throws Exception {
    	String input = "select part_id id FROM parts UNION (select part_name FROM parts Union ALL select part_id FROM parts)"; //$NON-NLS-1$
        String output = "SELECT rtrim(PARTS.PART_ID) AS id FROM PARTS UNION SELECT PARTS.PART_NAME FROM PARTS UNION SELECT rtrim(PARTS.PART_ID) FROM PARTS"; //$NON-NLS-1$
          
        TranslationHelper.helpTestVisitor(TranslationHelper.PARTS_VDB,
            input, 
            output, TRANSLATOR);
    }
    
    @Test public void testUpdateTrimStrings() throws Exception {
    	String input = "UPDATE PARTS SET PART_ID = NULL WHERE PARTS.PART_COLOR = 'b'"; //$NON-NLS-1$
        String output = "UPDATE PARTS SET PART_ID = NULL WHERE PARTS.PART_COLOR = 'b'"; //$NON-NLS-1$
          
        TranslationHelper.helpTestVisitor(TranslationHelper.PARTS_VDB,
            input, 
            output, TRANSLATOR);
        
        input = "insert into parts (part_id) values ('a')"; //$NON-NLS-1$
        output = "INSERT INTO PARTS (PART_ID) VALUES ('a')"; //$NON-NLS-1$
          
        TranslationHelper.helpTestVisitor(TranslationHelper.PARTS_VDB,
            input, 
            output, TRANSLATOR);
    }
    
    @Test public void testOrderByUnrelated() throws Exception {
    	String input = "select part_id id FROM parts order by part_name"; //$NON-NLS-1$
        String output = "SELECT rtrim(PARTS.PART_ID) AS id FROM PARTS ORDER BY PARTS.PART_NAME"; //$NON-NLS-1$
          
        TranslationHelper.helpTestVisitor(TranslationHelper.PARTS_VDB,
            input, 
            output, TRANSLATOR);
    }
    
    @Test public void testVarbinary() throws Exception {
    	String input = "select X'AB' FROM parts"; //$NON-NLS-1$
        String output = "SELECT X'AB' FROM PARTS"; //$NON-NLS-1$
          
        TranslationHelper.helpTestVisitor(TranslationHelper.PARTS_VDB,
            input, 
            output, TRANSLATOR);
    }
    
    @Test public void testConcat2() {
        helpTestVisitor(getTestVDB(),
                        "select part_name from parts where concat2(part_name, 'x') = concat2(part_weight, part_id)", //$NON-NLS-1$
                        "SELECT PARTS.PART_NAME FROM PARTS WHERE concat(ifnull(PARTS.PART_NAME, ''), 'x') = CASE WHEN PARTS.PART_WEIGHT IS NULL AND PARTS.PART_ID IS NULL THEN NULL ELSE concat(ifnull(PARTS.PART_WEIGHT, ''), ifnull(PARTS.PART_ID, '')) END", //$NON-NLS-1$
                        true); 
    }
    
    @Test public void testSelectWithoutFrom() {
        helpTestVisitor(getTestVDB(),
                        "select 1", //$NON-NLS-1$
                        "SELECT 1", //$NON-NLS-1$
                        true); 
    }
    
    @Test public void testFunctionNativeQuery() throws SQLException {
    	String ddl = "create foreign table t (x integer, y integer); create foreign function bsl (arg1 integer, arg2 integer) returns integer OPTIONS (\"teiid_rel:native-query\" '$1 << $2');";
    	
        helpTestVisitor(ddl,
                        "select bsl(x, y) from t", //$NON-NLS-1$
                        "SELECT t.x << t.y FROM t", //$NON-NLS-1$
                        true); 
        //make sure we don't treat arguments as bind values
        helpTestVisitor(ddl,
                "select bsl(x, y) from t where x = 1 + 1", //$NON-NLS-1$
                "SELECT t.x << t.y FROM t WHERE t.x = ?", //$NON-NLS-1$
                true);
        
        TranslatedCommand tc = helpTestVisitor(ddl,
                "select bsl(1, y) from t where x = y", //$NON-NLS-1$
                "SELECT ? << t.y FROM t WHERE t.x = t.y", //$NON-NLS-1$
                true);
        JDBCQueryExecution qe = new JDBCQueryExecution(null, null, Mockito.mock(ExecutionContext.class), TRANSLATOR);
        PreparedStatement ps = Mockito.mock(PreparedStatement.class);
        qe.bind(ps, tc.getPreparedValues(), null);
        Mockito.verify(ps, Mockito.times(1)).setObject(1, 1, Types.INTEGER);
    }
    
    @Test public void testGroupByRollup() {
        helpTestVisitor(getTestVDB(),
                        "select part_name, max(part_weight) from parts group by rollup(part_name)", //$NON-NLS-1$
                        "SELECT PARTS.PART_NAME, MAX(PARTS.PART_WEIGHT) FROM PARTS GROUP BY ROLLUP(PARTS.PART_NAME)", //$NON-NLS-1$
                        true); 
    }
    
    //previously would have failed in other locales
    @Test public void testDoubleFormat() {
        helpTestVisitor(getTestVDB(),
                        "select 1.0e10, -1.0e2", //$NON-NLS-1$
                        "SELECT 10000000000.0, -100.0", //$NON-NLS-1$
                        true);
    }
    
    @Test public void testDecimalFormat() {
        helpTestVisitor(getTestVDB(),
                        "select 100000000000.0, -.0000001", //$NON-NLS-1$
                        "SELECT 100000000000.0, -0.0000001", //$NON-NLS-1$
                        true);
    }
    
    @Test public void testProcedureTable() {
        helpTestVisitor("create foreign table smallb (intkey integer, stringkey string); "
        		+ "create foreign procedure spTest5 (param integer) returns table(stringkey string options (nameinsource 'other'), intkey integer)",
        		"select smallb.intkey, x.stringkey, x.intkey "
                		+ "from smallb left outer join lateral (exec spTest5(smallb.intkey)) as x on (true)", 
                        "SELECT smallb.intkey, x.other, x.intkey FROM smallb LEFT OUTER JOIN LATERAL (EXEC spTest5(smallb.intkey)) AS x ON 1 = 1", //$NON-NLS-1$
                        true);
    }
    
}
