/*
 * 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.
 * Modified by
 * @author  Data Virtuality GmbH
 * @date    $Date$
 *  
 */
// https://issues.jboss.org/browse/TEIID-2422
// https://issues.jboss.org/browse/TEIID-3282
// https://issues.jboss.org/browse/TEIID-4567
// https://issues.jboss.org/browse/TEIID-4532
// https://issues.jboss.org/browse/TEIID-2188
// https://issues.jboss.org/browse/TEIID-4378
// https://issues.jboss.org/browse/TEIID-5406
// https://issues.jboss.org/browse/TEIID-5410
// https://issues.jboss.org/browse/TEIID-2441
// https://issues.jboss.org/browse/TEIID-2681
// https://issues.jboss.org/browse/TEIID-3491
// https://issues.redhat.com/browse/TEIID-5709
// https://issues.redhat.com/browse/TEIID-3502
// https://issues.redhat.com/browse/TEIID-2334
// https://issues.redhat.com/browse/TEIID-1757
// https://issues.redhat.com/browse/TEIID-3760
package org.teiid.query.function;

import com.datavirtuality.dv.core.services.DVServices;
import org.apache.commons.text.similarity.CosineDistance;
import org.apache.commons.text.similarity.JaroWinklerSimilarity;
import org.apache.commons.text.similarity.LevenshteinDistance;
import org.teiid.api.exception.query.ExpressionEvaluationException;
import org.teiid.api.exception.query.FunctionExecutionException;
import org.teiid.client.util.ExceptionUtil;
import org.teiid.common.buffer.BufferManager;
import org.teiid.common.buffer.FileStore;
import org.teiid.common.buffer.FileStoreInputStreamFactory;
import org.teiid.core.plugin.CorePlugin;
import org.teiid.core.types.*;
import org.teiid.core.types.InputStreamFactory.BlobInputStreamFactory;
import org.teiid.core.types.InputStreamFactory.ClobInputStreamFactory;
import org.teiid.core.util.ObjectConverterUtil;
import org.teiid.core.util.PropertiesUtils;
import org.teiid.core.util.ReaderInputStream;
import org.teiid.core.util.TimestampWithTimezone;
import org.teiid.language.Like.MatchMode;
import org.teiid.language.SQLConstants;
import org.teiid.language.SQLConstants.NonReserved;
import org.teiid.metadata.FunctionMethod;
import org.teiid.query.QueryPlugin;
import org.teiid.query.eval.Evaluator;
import org.teiid.query.function.metadata.FunctionCategoryConstants;
import org.teiid.query.function.source.EncryptionsIntf;
import org.teiid.query.sql.lang.MatchCriteria;
import org.teiid.query.util.CommandContext;
import org.teiid.query.util.DVUsersHandlerIntf;
import org.teiid.translator.SourceSystemFunctions;

import java.io.*;
import java.lang.reflect.Array;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.math.RoundingMode;
import java.nio.charset.CharacterCodingException;
import java.nio.charset.Charset;
import java.nio.charset.CodingErrorAction;
import java.nio.charset.StandardCharsets;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.DayOfWeek;
import java.time.LocalDate;
import java.time.temporal.TemporalAdjusters;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.regex.PatternSyntaxException;
import org.teiid.util.CharsetUtils;

import static org.teiid.util.CharsetUtils.UTF_8_BOM;
import static org.teiid.util.CharsetUtils.getCharset;


import static java.lang.Character.getType;
/**
 * Static method hooks for most of the function library.
 */
public final class FunctionMethods {

	private static final int ONE_BILLION = 1000000000;
	private static final String DEFAULT_TIMESTAMP_FORMAT = "yyyy-MM-dd HH:mm:ss.SSS";
	private static Pattern DVCORE7850_PATTERN = Pattern.compile("\\AH+'\\w+'\\z");

    private static final class UpperLowerReader extends FilterReader {
   int c1 = -1;
   private boolean upper;

   private UpperLowerReader(Reader in, boolean upper) {
            super(in);
            this.upper = upper;
        }
        
        @Override
        public int read(char[] cbuf, int off, int len) throws IOException {
            if (len <= 0) {
                if (len < 0) {
                    throw new IndexOutOfBoundsException();
                } else if ((off < 0) || (off > cbuf.length)) {
                    throw new IndexOutOfBoundsException();
                }
                return 0;
            }
            int chars = 0;
            while (chars <= len) {
                int c = read();
                if (c == -1) {
                    if (chars == 0) {
                        return -1;
                    }
                    break;
                }
                cbuf[off++] = (char)c;
                chars++;
            }
            return chars;
        }

        @Override
        public int read() throws IOException {
       if (c1 != -1) {
           int c = c1;
           c1 = -1;
           return c;
       }
       int c = super.read();
       if ((char)c >= Character.MIN_HIGH_SURROGATE
               && (char)c <= Character.MAX_HIGH_SURROGATE) {
           c1 = super.read();
           if (Character.isLowSurrogate((char)c1)) {
               int codePoint = Character.toCodePoint((char)c, (char)c1);
               codePoint = modifyChar(codePoint);
               int count = Character.charCount(codePoint);
               if (count == 1) {
                   c1 = -1;
                   return codePoint;
               }
               char[] chars = Character.toChars(codePoint);
               c1 = chars[1];
               return chars[0];
           }
           c1 = modifyChar(c1);
           
       }
       return modifyChar(c);
   }

   private int modifyChar(int c) {
       if (upper) {
           return Character.toUpperCase(c);
       }
       return Character.toLowerCase(c);
   }
}
	// EXTENSION
	// ISSUE: DVCORE-778
	// FIXED BY: Roman Pedchenko
	// DATE: 2014.05.29
	// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2422
	private static final boolean CALENDAR_TIMESTAMPDIFF = PropertiesUtils.getBooleanProperty(System.getProperties(), "org.teiid.calendarTimestampDiff", true); //$NON-NLS-1$
	// END EXTENSION

	// ================== Function = plus ======================

	public static int plus(int x, int y) throws FunctionExecutionException {
		long result = (long)x + y;
		return integerRangeCheck(result);
	}

	public static int integerRangeCheck(long result)
			throws FunctionExecutionException {
		if (result > Integer.MAX_VALUE || result < Integer.MIN_VALUE) {
			throw new FunctionExecutionException(QueryPlugin.Event.TEIID31169, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31169, result));
		}
		return (int)result;
	}

	public static long plus(long x, long y) throws FunctionExecutionException {
		if (y > 0) {
			if (x > Long.MAX_VALUE - y) {
				throw new FunctionExecutionException(QueryPlugin.Event.TEIID31169, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31169, BigInteger.valueOf(x).add(BigInteger.valueOf(y))));
			}
		} else if (x < Long.MIN_VALUE - y) {
			throw new FunctionExecutionException(QueryPlugin.Event.TEIID31169, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31169, BigInteger.valueOf(x).add(BigInteger.valueOf(y))));
		}
		return x + y;
	}
	
	public static float plus(float x, float y) {
		return x + y;
	}
	
	public static double plus(double x, double y) {
		return x + y;
	}
	
	public static Object plus(BigInteger x, BigInteger y) {
		return x.add(y);
	}
	
	public static Object plus(BigDecimal x, BigDecimal y) {
		return x.add(y);
	}

	// ================== Function = minus =====================

	public static int minus(int x, int y) throws FunctionExecutionException {
		long result = (long)x - y;
		return integerRangeCheck(result);
	}

	public static long minus(long x, long y) throws FunctionExecutionException {
		if (y > 0) {
			if (x < Long.MIN_VALUE + y) {
				throw new FunctionExecutionException(QueryPlugin.Event.TEIID31169, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31169, BigInteger.valueOf(x).subtract(BigInteger.valueOf(y))));
			}
		} else if (x > Long.MAX_VALUE + y) {
			throw new FunctionExecutionException(QueryPlugin.Event.TEIID31169, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31169, BigInteger.valueOf(x).subtract(BigInteger.valueOf(y))));
		}
		return x - y;
	}
	
	public static float minus(float x, float y) {
		return x - y;
	}
	
	public static double minus(double x, double y) {
		return x - y;
	}
	
	public static Object minus(BigInteger x, BigInteger y) {
		return x.subtract(y);
	}
	
	public static Object minus(BigDecimal x, BigDecimal y) {
		return x.subtract(y);
	}

	// ================== Function = multiply =====================

	public static int multiply(int x, int y) throws FunctionExecutionException {
		long result = (long)x * y;
		return integerRangeCheck(result);
	}

	public static long multiply(long x, long y) throws FunctionExecutionException {
		if ((y > 0 && (x > Long.MAX_VALUE/y || x < Long.MIN_VALUE/y))
				|| ((y < -1) && (x > Long.MIN_VALUE/y || x < Long.MAX_VALUE/y))
				|| (y == -1 && x == Long.MIN_VALUE)) {
			throw new FunctionExecutionException(QueryPlugin.Event.TEIID31169, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31169, BigInteger.valueOf(x).multiply(BigInteger.valueOf(y))));
		}
		return x * y;
	}
	
	public static float multiply(float x, float y) {
		return x * y;
	}
	
	public static double multiply(double x, double y) {
		return x * y;
	}
	
	public static Object multiply(BigInteger x, BigInteger y) {
		return x.multiply(y);
	}
	
	public static Object multiply(BigDecimal x, BigDecimal y) {
		return x.multiply(y);
	}

	// ================== Function = divide =====================

	public static int divide(int x, int y) throws FunctionExecutionException {
		if (x == Integer.MIN_VALUE && y == -1) {
			throw new FunctionExecutionException(QueryPlugin.Event.TEIID31169, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31169, (long)Integer.MAX_VALUE + 1));
		}
		return x / y;
	}

	public static long divide(long x, long y) throws FunctionExecutionException {
		if (x == Long.MIN_VALUE && y == -1) {
			throw new FunctionExecutionException(QueryPlugin.Event.TEIID31169, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31169, BigInteger.valueOf(Long.MAX_VALUE).add(BigInteger.valueOf(1))));
		}
		return x / y;
	}
	
	public static float divide(float x, float y) {
		return x / y;
	}
	
	public static double divide(double x, double y) {
		return x / y;
	}
	
	public static Object divide(BigInteger x, BigInteger y) {
		return x.divide(y);
	}
	
	private static final BigDecimal ZERO = new BigDecimal(0);
	
	public static Object divide(BigDecimal x, BigDecimal y) {
		BigDecimal bd = x.divide(y, Math.max(16, x.scale() + y.precision() + 1), RoundingMode.HALF_UP).stripTrailingZeros();
		bd = bd.setScale(Math.max(x.scale(), bd.scale()));
		if (bd.compareTo(ZERO) == 0) {
			return ZERO;
		}
		return bd;
	}

	// ================== Function = abs =====================

	public static int abs(int x) throws FunctionExecutionException {
		if (x == Integer.MIN_VALUE) {
			throw new FunctionExecutionException(QueryPlugin.Event.TEIID31169, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31169, (long)Integer.MAX_VALUE + 1));
		}
		return Math.abs(x);
	}

	public static long abs(long x) throws FunctionExecutionException {
		if (x == Long.MIN_VALUE) {
			throw new FunctionExecutionException(QueryPlugin.Event.TEIID31169, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31169, BigInteger.valueOf(Long.MAX_VALUE).add(BigInteger.valueOf(1))));
		}
		return Math.abs(x);
	}
	
	public static float abs(float x) {
		return Math.abs(x);
	}
	
	public static double abs(double x) {
		return Math.abs(x);
	}
	
	public static Object abs(BigInteger x) {
		return x.abs();
	}
	
	public static Object abs(BigDecimal x) {
		return x.abs();
	}

	// ================== Function = ceiling =====================

	public static Object ceiling(Number x) {
		return new Double(Math.ceil(x.doubleValue()));
	}

	// ================== Function = exp =====================

	public static Object exp(Number x) {
		return new Double(Math.exp(x.doubleValue()));
	}

	// ================== Function = floor =====================

	public static  Object floor(Number x) {
		return new Double(Math.floor(x.doubleValue()));
	}

	// ================== Function = log =====================

	public static  Object log(Number x) {
		return new Double(Math.log(x.doubleValue()));
	}

	// ================== Function = log10 =====================

	private static final double log10baseE = Math.log(10);

	public static Object log10(Number x) {
		return new Double( Math.log(x.doubleValue()) / log10baseE);
	}
    
    // ================== Function = rand=====================
    
    public static Object rand(CommandContext context, Integer seed) {        
        if(seed == null) {
            return new Double(context.getNextRand());
        } 
        return new Double(context.getNextRand(seed.longValue()));
    }
        
    public static Object rand(CommandContext context) {
        return new Double(context.getNextRand());
    }
    
	// ================== Function = mod =====================

	public static int mod(int x, int y) {
		return x % y;
	}
	
	public static long mod(long x, long y) {
		return x % y;
	}
	
	public static float mod(float x, float y) {
		return x % y;
	}
	
	public static double mod(double x, double y) {
		return x % y;
	}
	
	public static Object mod(BigInteger x, BigInteger y) {
		return x.remainder(y);
	}
	
	public static Object mod(BigDecimal x, BigDecimal y) {
		return x.remainder(y);
	}
    
	// ================== Function = power =====================
	
	public static double power(double x, double y) {
		return Math.pow(x, y);
	}
	
	public static BigDecimal power(BigInteger x, int y) {
		return power(new BigDecimal(x), y);
	}
	
	public static BigDecimal power(BigDecimal x, int y) {
		if (y < 0)
			return BigDecimal.ONE.divide(x.pow(-y));
		else
			return x.pow(y);
	}

    public static int round(int number, int places) {
        if(places < 0){
        	return round(new BigDecimal(number), places).intValue();
        }
        return number;
    }
    
    public static float round(float number, int places) {
    	return round(new BigDecimal(number), places).floatValue();
    }
    
    public static double round(double number, int places) {
    	return round(new BigDecimal(number), places).doubleValue();
    }
    
    public static BigDecimal round(BigDecimal bigDecimalNumber, int places) {
        int scale = bigDecimalNumber.scale();
        if (scale <= places) {
        	return bigDecimalNumber;
        }
        bigDecimalNumber = bigDecimalNumber.setScale(places,BigDecimal.ROUND_HALF_UP);
        return bigDecimalNumber.setScale(scale,BigDecimal.ROUND_HALF_UP);
    }

	// ================== Function = sign =====================

	public static Object sign(int x) {
		return Integer.signum(x);
	}
	
	public static Object sign(long x) {
		return Long.signum(x);
	}

	public static Object sign(float x) {
		return Math.signum(x);
	}
	
	public static Object sign(double x) {
		return Math.signum(x);
	}
	
	public static Object sign(BigInteger x) {
		return new Integer(x.signum());
	}
	
	public static Object sign(BigDecimal x) {
		return new Integer(x.signum());
	}

	// ================== Function = sqrt =====================


	public static  Object sqrt(Number x) {
		return new Double( Math.sqrt(x.doubleValue()));
	}

	// ================== Function = currentDate =====================

	public static  Object currentDate() {
		return TimestampWithTimezone.createDate(new Date());
	}

	// ================== Function = currentTime =====================

	public static  Object currentTime() {
		return TimestampWithTimezone.createTime(new Date());
	}

	// ================== Function = currentTimestamp =====================

	public static  Object currentTimestamp() {
		return new Timestamp(System.currentTimeMillis());
	}

	// ================== Function = serverTimezone ====================

	public static String serverTimezone()  { return TimeZone.getDefault().getID(); }


	// ================== Helper for a bunch of date functions =====================

	private static int getField(java.util.Date date, int field) {
		Calendar cal = TimestampWithTimezone.getCalendar();
		cal.setTime(date);

		return cal.get(field);
	}

	// ================== Function = dayname =====================

	static final String[] dayNames = new String[] {
		"Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" }; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$

	public static Object dayName(Date x) {
		return dayNames[getField(x, Calendar.DAY_OF_WEEK) - 1];
	}

	// ================== Function = dayofmonth =====================

	public static  Object dayOfMonth(Date x) {
		return Integer.valueOf(getField(x, Calendar.DATE));
	}

	// ================== Function = dayofweek =====================

	public static int dayOfWeek(Date x) {
		int result = getField(x, Calendar.DAY_OF_WEEK);
		return result;
	}

	// ================== Function = dayofyear =====================

	public static Object dayOfYear(Date x) {
		return Integer.valueOf(getField(x, Calendar.DAY_OF_YEAR));
	}

	// ================== Function = hour =====================

	public static Object hour(Date x) {
		return Integer.valueOf(getField(x, Calendar.HOUR_OF_DAY));
	}

	// ================== Function = minute =====================

	public static Object minute(Date x) {
		return Integer.valueOf(getField(x, Calendar.MINUTE));
	}

	// ================== Function = month =====================

	public static Object month(Date x) {
		return Integer.valueOf(getField(x, Calendar.MONTH)+1);
	}

	// ================== Function = monthname =====================

	static final String[] monthNames = new String[] {
		"January", "February", "March", "April", "May", "June", //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$
		"July", "August", "September", "October", "November", "December" }; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$

	public static Object monthName(Date x) {
		return monthNames[getField(x, Calendar.MONTH)];
	}

	// ================== Function = second =====================

	public static Object second(Date x) {
		return Integer.valueOf(getField(x, Calendar.SECOND));
	}

	// ================== Function = week =====================

	public static int week(Date x) {
		return getField(x, Calendar.WEEK_OF_YEAR);
	}

	// ================== Function = year =====================

	public static Object year(Date x) {
		return Integer.valueOf(getField(x, Calendar.YEAR));
	}

	// ================== Function = quarter =====================

	public static Object quarter(Date date) {
		int month = getField(date, Calendar.MONTH);
		
		if (month > 11) {
			 throw new AssertionError(QueryPlugin.Util.gs(QueryPlugin.Event.DVCORE301194, date));
		}
		return Integer.valueOf(month/3 + 1);
	}

	//	================== Function = timestampadd =====================

	public static Object timestampAdd(String intervalType, int count, Timestamp timestamp) throws FunctionExecutionException {
		return timestampAdd(intervalType, (long)count, timestamp);
	}

	public static Object timestampAdd(String intervalType, long count, Timestamp timestamp) throws FunctionExecutionException {
		integerRangeCheck(count); //for compatibility with pushdown only allow int values

		Calendar cal = TimestampWithTimezone.getCalendar();

		long nanos = timestamp.getNanos();
		cal.setTime(timestamp);

		// case of interval = 1, fractional seconds (nanos), don't go to branches of addField()
		if (intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_FRAC_SECOND)) {
			nanos = plus(nanos, count);

			// Handle the case of nanos > 999,999,999 and increase the second.
			// Since the count number is an integer, so the maximum is definite,
			// and nanos/999,999,999 can at most be added to second
			if (Math.abs(nanos) >= ONE_BILLION) {
				long addSecond = nanos / ONE_BILLION;
				nanos = (int)(nanos % ONE_BILLION);

				cal.add(Calendar.SECOND, integerRangeCheck(addSecond));
			}

			if (nanos < 0) {
				cal.add(Calendar.SECOND, -1);
				nanos = ONE_BILLION + nanos;
			}

			Timestamp ts = new Timestamp(cal.getTime().getTime());
			ts.setNanos((int)nanos);
			return ts;
		}
		// for interval from 2 to 9
		addField(intervalType, integerRangeCheck(count), cal);
		Timestamp ts = new Timestamp(cal.getTime().getTime());

		//rectify returned timestamp with original nanos
		ts.setNanos((int)nanos);
		return ts;
	}
	
	/** Helper method for timestampAdd method
	 * @param interval Integer
	 * @param count Integer
	 * @param cal Calendar instance
	 */
	private static void addField(String interval, Integer count, Calendar cal) {
		int countValue = count.intValue();

        if(interval.equalsIgnoreCase(NonReserved.SQL_TSI_FRAC_SECOND)) {
            //nano seconds - should never get into this branch
        } else if(interval.equalsIgnoreCase(NonReserved.SQL_TSI_SECOND)) {
            cal.add(Calendar.SECOND, countValue);
        } else if(interval.equalsIgnoreCase(NonReserved.SQL_TSI_MINUTE)) {
            cal.add(Calendar.MINUTE, countValue);
        } else if(interval.equalsIgnoreCase(NonReserved.SQL_TSI_HOUR)) {
            cal.add(Calendar.HOUR_OF_DAY, countValue);
        } else if(interval.equalsIgnoreCase(NonReserved.SQL_TSI_DAY)) {
            cal.add(Calendar.DAY_OF_YEAR, countValue);
        } else if(interval.equalsIgnoreCase(NonReserved.SQL_TSI_WEEK)) {
            cal.add(Calendar.WEEK_OF_YEAR, countValue);
        } else if(interval.equalsIgnoreCase(NonReserved.SQL_TSI_MONTH)) {
            cal.add(Calendar.MONTH, countValue);
        } else if(interval.equalsIgnoreCase(NonReserved.SQL_TSI_QUARTER)) {
            cal.add(Calendar.MONTH, countValue*3);
        } else if(interval.equalsIgnoreCase(NonReserved.SQL_TSI_YEAR)) {
            cal.add(Calendar.YEAR, countValue);
        }
	}

	//	================== Function = timestampdiff =====================

    // EXTENSION
    // ISSUE: DVCORE-778
    // FIXED BY: Roman Pedchenko
    // DATE: 2014.05.29
    // ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2422
	// /**
     // * This method truncates (ignores) figures
     // * @param interval
     // * @param timestamp1
     // * @param timestamp2
     // * @return
     // * @throws FunctionExecutionException
     // */
    // public static Object timestampDiff(String intervalType, Timestamp ts1Obj, Timestamp ts2Obj)  {
    public static Long timestampDiff(String intervalType, Timestamp ts1Obj, Timestamp ts2Obj) throws FunctionExecutionException  {
    	return timestampDiff(intervalType, ts1Obj, ts2Obj, CALENDAR_TIMESTAMPDIFF);
    }
	
    public static Long timestampDiff(String intervalType, Timestamp ts1Obj, Timestamp ts2Obj, boolean calendarBased) throws FunctionExecutionException  {
    // END EXTENSION
        long ts1 = ts1Obj.getTime() / 1000;
        long ts2 = ts2Obj.getTime() / 1000;
		// Fix failing unit test due to timezone differences.
		TimeZone currentTZ = TimestampWithTimezone.getCalendar().getTimeZone();
		if ((currentTZ.getDSTSavings() <= 0) && (currentTZ.getRawOffset() % 3600000 == 0)) {
			ts1 += currentTZ.getOffset(ts1Obj.getTime()) / 1000;
			ts2 += currentTZ.getOffset(ts2Obj.getTime()) / 1000;
		}

        long tsDiff = ts2 - ts1;

        long count = 0;
        if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_FRAC_SECOND)) {
        	// EXTENSION
        	// ISSUE: DVCORE-778
        	// FIXED BY: Roman Pedchenko
        	// DATE: 2014.05.29
        	// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2422
        	if (Math.abs(tsDiff) > Integer.MAX_VALUE) {
        		throw new FunctionExecutionException(QueryPlugin.Event.TEIID31136, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31136));
        	}
        	// END EXTENSION
            count = tsDiff * ONE_BILLION + ts2Obj.getNanos() - ts1Obj.getNanos();
        } else { 
            if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_SECOND)) {
                count = tsDiff;
            // EXTENSION
            // ISSUE: DVCORE-778
            // FIXED BY: Roman Pedchenko
            // DATE: 2014.05.29
            // ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2422
            } else if (calendarBased) {
            	//alternative logic is needed to compute calendar differences 
            	//which looks at elapsed date parts, not total time between
            	if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_MINUTE)) {
            		count = ts2 / 60 - ts1 / 60;
            	} else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_HOUR)) {
            		TimeZone tz = TimestampWithTimezone.getCalendar().getTimeZone();
		            if (tz.getDSTSavings() > 0 || tz.getRawOffset() % 3600000 != 0) {
            			ts1 += tz.getOffset(ts1Obj.getTime())/1000;
            			ts2 += tz.getOffset(ts2Obj.getTime())/1000;
            		}
            		count = ts2 / (60*60) - ts1 / (60*60);	
            	} else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_DAY) || intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_WEEK)) {
            		TimeZone tz = TimestampWithTimezone.getCalendar().getTimeZone();
		            if (tz.getDSTSavings() > 0 || tz.getRawOffset() % 3600000 != 0) {
            			ts1 += tz.getOffset(ts1Obj.getTime())/1000;
            			ts2 += tz.getOffset(ts2Obj.getTime())/1000;
            		}
		            //since we are now effectively using GMT we can simply divide since the unix epoch starts at midnight.
		            count = ts2 / (60*60*24) - ts1 / (60*60*24);
                	if (intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_WEEK)) {
                    	//TODO:  this behavior matches SQL Server - but not Derby which expects only whole week
                		
                    	long days = count;
                    	//whole weeks between the two dates
                		count = count/7;
                    	//check for calendar difference assuming sunday as the first week day
                		if (days%7!=0) {
	                    	int day1 = dayOfWeek(ts1Obj);
	                    	int day2 = dayOfWeek(ts2Obj);
	                    	int diff = Integer.signum(day2 - day1);
	                    	if (diff > 0) {
	                    		if (tsDiff < 0) {
	                    			count--;
	                    		}
	                    	} else if (diff < 0) {
	                    		if (tsDiff > 0) {
	                    			count++; 
	                    		}
	                    	}
                		}
                	}
                } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_MONTH)) {
                	Calendar cal = TimestampWithTimezone.getCalendar();
                	cal.setTimeInMillis(ts1Obj.getTime());
                	int months1 = cal.get(Calendar.YEAR) * 12 + cal.get(Calendar.MONTH);
                	cal.setTimeInMillis(ts2Obj.getTime());
                	int months2 = cal.get(Calendar.YEAR) * 12 + cal.get(Calendar.MONTH);
                    count = months2 - months1;
                } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_QUARTER)) {
                	Calendar cal = TimestampWithTimezone.getCalendar();
                	cal.setTimeInMillis(ts1Obj.getTime());
                	int quarters1 = cal.get(Calendar.YEAR) * 4 + cal.get(Calendar.MONTH)/3;
                	cal.setTimeInMillis(ts2Obj.getTime());
                	int quarters2 = cal.get(Calendar.YEAR) * 4 + cal.get(Calendar.MONTH)/3;
                    count = quarters2 - quarters1;
                } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_YEAR)) {
                	Calendar cal = TimestampWithTimezone.getCalendar();
                	cal.setTimeInMillis(ts1Obj.getTime());
                	int years1 = cal.get(Calendar.YEAR);
                	cal.setTimeInMillis(ts2Obj.getTime());
                	int years2 = cal.get(Calendar.YEAR);
                    count = years2 - years1;
                }
            // END EXTENSION
            } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_MINUTE)) {
                count = tsDiff / 60;
            } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_HOUR)) {
                count = tsDiff / (60*60);
            } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_DAY)) {
                count = tsDiff / (60*60*24);
            } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_WEEK)) {
                count = tsDiff / (60*60*24*7);
            } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_MONTH)) {
                count = tsDiff / (60*60*24*30);
            } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_QUARTER)) {
                count = tsDiff / (60*60*24*91);
            } else if(intervalType.equalsIgnoreCase(NonReserved.SQL_TSI_YEAR)) {
                count = tsDiff / (60*60*24*365);
            }    
        }
        // EXTENSION
        // ISSUE: DVCORE-778
        // FIXED BY: Roman Pedchenko
        // DATE: 2014.05.29
        // ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2422
        // return new Long(count);
        //TODO: long results are not consistent with other sources
    	/*if (calendarBased && ((count > 0 && count > Integer.MAX_VALUE) || (count < 0 && count < Integer.MIN_VALUE))) {
    		throw new FunctionExecutionException(QueryPlugin.Event.TEIID31136, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31136));
    	}*/
        return Long.valueOf(count);
        // END EXTENSION
	}

    //  ================== Function = timestampcreate =====================

    public static Object timestampCreate(java.sql.Date date, Time time) {
        Calendar tsCal = TimestampWithTimezone.getCalendar();
        tsCal.setTime(time);
        int hour = tsCal.get(Calendar.HOUR_OF_DAY);
        int minute = tsCal.get(Calendar.MINUTE);
        int second = tsCal.get(Calendar.SECOND);
        tsCal.setTime(date);
        
        tsCal.set(Calendar.HOUR_OF_DAY, hour);
        tsCal.set(Calendar.MINUTE, minute);
        tsCal.set(Calendar.SECOND, second);

        return new Timestamp(tsCal.getTime().getTime());
    }

	// ================== Function = length =====================

	public static Object length(String str) {
		return new Integer(str.length());
	}

// ================== Function = concat =====================

	public static String concat(String str1, String str2) {
		return str1 + str2;
	}

	public static String concat2(String str1, String str2) {
		if (str1 == null) {
			if (str2 == null) {
				return null;
			}
			return str2;
		}
		if (str2 == null) {
			return str1;
		}
		return str1 + str2;
	}

	public static ClobType concat(CommandContext context, ClobType str1, ClobType str2) throws IOException, SQLException {
		BufferManager bm = context.getBufferManager();
		FileStore fs = bm.createFileStore("clob"); //$NON-NLS-1$
		FileStoreInputStreamFactory fsisf = new FileStoreInputStreamFactory(fs, Streamable.ENCODING);
		boolean remove = true;

		try (Reader characterStream = str1.getCharacterStream(); Reader characterStream2 = str2.getCharacterStream();){
			Writer writer = fsisf.getWriter();
			int chars = ObjectConverterUtil.write(writer, characterStream, -1, false);
			chars += ObjectConverterUtil.write(writer, characterStream2, -1, false);
			writer.close();
			if (fsisf.getStorageMode() == InputStreamFactory.StorageMode.MEMORY) {
				//detach if just in memory
				byte[] bytes = fsisf.getMemoryBytes();
				return new ClobType(new ClobImpl(new String(bytes, Streamable.ENCODING)));
			}
			remove = false;
			context.addCreatedLob(fsisf);
			return new ClobType(new ClobImpl(fsisf, chars));
		} finally {
			if (remove) {
				fs.remove();
			}
		}
	}

	public static ClobType concat2(CommandContext context, ClobType str1, ClobType str2) throws IOException, SQLException {
		if (str1 == null) {
			if (str2 == null) {
				return null;
			}
			return str2;
		}
		if (str2 == null) {
			return str1;
		}
		return concat(context, str1, str2);
	}

	// ================== Function = substring =====================

	public static Object substring(String string, Integer startVal, Integer lengthVal) {
		if (startVal < 0) {
        	startVal = string.length() + startVal;
        } else if (startVal > 0){
            startVal--;     // Adjust to 1-based
        }
		
		if(startVal < 0 || startVal >= string.length()) {
		    return null;
		}

		if(lengthVal < 0) {
		    return null;
		}

		int endVal = Math.min(startVal+lengthVal, string.length());

		return new String(string.substring(startVal, endVal));
	}

    public static Object substring(String string, Integer start) {
        int startVal = start.intValue();
        return substring(string, startVal, string.length());
    }

	// ================== Function = split_part =====================

	public static Object splitPart(String string, String delimiter, int position) {
    	String regex = "[" + delimiter + "]{" + delimiter.length() + "}";

		if (position <= 0) {
			throw new IndexOutOfBoundsException(QueryPlugin.Util.gs(QueryPlugin.Event.DVCORE301189));
		}

		if (delimiter.isEmpty()) {
			if (position > 1)
				return "";
			return string;
		}

		Pattern pattern = Pattern.compile(regex);
		Matcher matcher = pattern.matcher(string);

		position -= 1;
		if (matcher.find()) {
			String[] parts = string.split(regex);

			if (position > parts.length-1)
				return "";

			return parts[position];
		}

		if (position > string.split(regex).length-1)
			return "";

		return string;
	}

	// ================== Function = left =====================

	public static Object left(String string, Integer count)
		throws FunctionExecutionException {
		int countValue = count.intValue();
        if(countValue < 0) {
             throw new FunctionExecutionException(QueryPlugin.Event.TEIID30396, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30396, countValue));
        } 
        if(string.length() < countValue) {
            return string;
        }
        return new String(string.substring(0, countValue));
	}

	// ================== Function = right =====================

	public static Object right(String string, Integer count) 
		throws FunctionExecutionException {
		int countValue = count.intValue();
        if(countValue < 0) {
             throw new FunctionExecutionException(QueryPlugin.Event.TEIID30396, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30396, countValue));
        } else if(string.length() < countValue) {
            return string;
		} else {
			return new String(string.substring(string.length() - countValue));
        }
	}

	// ================== Function = lowercase =====================

	public static Object lowerCase(String str) {
		return str.toLowerCase();
	}

	public static ClobType lowerCase(ClobType str) {
		return new ClobType(new ClobImpl(new ClobInputStreamFactory(str) {
			@Override
			public Reader getReader(Reader reader) {
				return new UpperLowerReader(reader, false);
			}
		}, str.getLength()));
	}
	// ================== Function = uppercase =====================

	public static Object upperCase(String str) {
		return str.toUpperCase();
	}

	public static ClobType upperCase(ClobType str) {
		return new ClobType(new ClobImpl(new ClobInputStreamFactory(str) {
			@Override
			public Reader getReader(Reader reader) {
				return new UpperLowerReader(reader, true);
			}
		}, str.getLength()));
	}

	// ================== Function = locate =====================

	public static Object locate(String sub, String str) {
		return locate(sub, str, 1);
	}

	// ================== Function = endsWith =====================

	public static Object endsWith(String sub, String str) {
		return str.endsWith(sub);
	}

	/**
	 * TODO: The treatment of negative start indexes is inconsistent here.
	 * We're treating the null value like Derby, but not throwing an
	 * exception if the value is less than 1 (less than 0 in DB2).
	 */
	public static Object locate(String sub, String str, Integer start) {
		if(str == null || sub == null) {
			return null;
		} 
		if (start == null) {
			start = 1;
		}
		return new Integer(str.indexOf(sub, start.intValue() - 1) + 1);
	}

	public static String trim(String trimSpec, String trimChar, String string) throws FunctionExecutionException {
		if (trimChar.length() != 1) {
			 throw new FunctionExecutionException(QueryPlugin.Event.TEIID30398, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30398, "TRIM CHAR", trimChar));//$NON-NLS-1$
		}
		if (!trimSpec.equalsIgnoreCase(SQLConstants.Reserved.LEADING)) {
			string = rightTrim(string, trimChar.charAt(0));
		}
		if (!trimSpec.equalsIgnoreCase(SQLConstants.Reserved.TRAILING)) {
			string = leftTrim(string, trimChar.charAt(0));
		}
		return string;
	}
	
	// ================== Function = lefttrim =====================

	private static final char SPACE = ' ';

	public static String leftTrim(String string, char trimChar) {
		for(int i=0; i<string.length(); i++) {
			if(string.charAt(i) != trimChar) {
				// end of trim, return what's left
				if (i==0) {
					return string;
				}
				return new String(string.substring(i));
			}
		}

		// All spaces, so trim it all
		return ""; //$NON-NLS-1$
	}
	
	
	public static String leftTrim(String string) {
		return leftTrim(string, SPACE);
	}

	// ================== Function = righttrim =====================

	public static String rightTrim(String string, char trimChar) {
		return rightTrim(string, trimChar, true);
	}
	
	public static String rightTrim(String string, char trimChar, boolean newString) {
		for(int i=string.length()-1; i>=0; i--) {
			if(string.charAt(i) != trimChar) {
				// end of trim, return what's left
				if (i==string.length()-1) {
					return string;
				}
				String result = string.substring(0, i+1);
				if (newString) {
					return new String(result);
				}
				return result;
			}
		}

		// All spaces, so trim it all
		return ""; //$NON-NLS-1$
	}
	
	public static Object rightTrim(String string) {
		return rightTrim(string, SPACE);
	}

	// ================== Function = replace =====================

	public static Object replace(String string, String subString, String replaceString) {
		// Check some simple cases that require no work
		if(subString.length() > string.length() || string.length() == 0 || subString.length() == 0) {
			return string;
		}

		StringBuffer result = new StringBuffer();
		int index = 0;

		while(true) {
			int newIndex = string.indexOf(subString, index);
			if(newIndex < 0) {
				// No more replacement sections, grab from old index to end of string
				result.append( string.substring(index));

				// Break out of loop
				break;

			}
			// Matched the substring at newIndex

			// First append section from old index to new
			result.append( string.substring( index, newIndex));

			// Then append replacement section for sub
			result.append( replaceString );

			// Then move the index counter forward
			index = newIndex + subString.length();
		}

		return result.toString();
	}

	// ================== Function = insert =====================

	public static Object insert(String string1, Integer start, Integer length, String str2)
		throws FunctionExecutionException {
		int startValue = start.intValue();
		int len = length.intValue();

		// Check some invalid cases
		if(startValue < 1 || (startValue-1) > string1.length()) {
			 throw new FunctionExecutionException(QueryPlugin.Event.TEIID30399, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30399, start, string1));
		} else if (len < 0) {
			 throw new FunctionExecutionException(QueryPlugin.Event.TEIID30400, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30400, len));
		} else if (string1.length() == 0 && (startValue > 1 || len >0) ) {
			 throw new FunctionExecutionException(QueryPlugin.Event.TEIID30401, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30401));
		}

		StringBuffer result = new StringBuffer();
		result.append(string1.substring(0, startValue-1));
		int endValue = startValue + len - 1;

		// str2.length() = 0 is a valid case
		if (endValue > string1.length()) {
			result.append(str2);
		} else {
			result.append(str2);
			result.append(string1.substring( endValue ));
		}

		return result.toString();
	}

	// ================== Function = repeat =====================
	public static Object repeat(String str, Integer count) {
		int repeatCount = count.intValue();
		StringBuffer result = new StringBuffer();

		// EXTENSION
		// ISSUE: DVCORE-2607
		// FIXED BY: Roman Pedchenko
		// DATE: 2015.02.17
		// for (int i = 0; i < repeatCount && result.length() <= DataTypeManager.MAX_STRING_LENGTH; i++) {
		for (int i = 0; i < repeatCount; i++) {
		// END EXTENSION
			result.append(str);
		}
		return result.toString();
	}

    // ================== Function = ascii =====================

    public static Integer ascii(String ch) {
        if(ch.length() == 0) {
        	return null;
        } 
        return (int)ch.charAt(0);
    }
    
    public static Integer ascii(Character ch) {
        return (int)ch.charValue();
    }

    // ================== Function = chr =====================

    public static Object chr(int intValue) {
        return new Character((char) intValue);
    }

    // ================== Function = initCap =====================

    public static Object initCap(String s) {
        StringBuffer cap = new StringBuffer();

	    // we should use this approach after switching to Java8
        /*s.codePoints().forEachOrdered(new IntConsumer() {
		    boolean checkCap = true;

		    @Override
		    public void accept(int c) {
			    // Decide whether to upper case
			    if(checkCap) {
				    cap.appendCodePoint(Character.toUpperCase(c));
			    } else {
				    cap.appendCodePoint(Character.toLowerCase(c));
			    }

			    // Reset flag for next character
			    checkCap = Character.isWhitespace(c);
		    }
	    });*/
        boolean checkCap = true;
        for(int i=0; i<s.length(); i++) {
            char c = s.charAt(i);

            // Decide whether to upper case
            if(checkCap) {
                cap.append(Character.toUpperCase(c));
            } else {
                cap.append(Character.toLowerCase(c));
            }

            // Reset flag for next character
			checkCap = isDelimiter(c);
        }
        return cap.toString();
    }

    // ================== Function = lpad =====================

    public static Object lpad(String inputString, Integer padLength, String padStr)
        throws FunctionExecutionException {

    	return pad(inputString, padLength, padStr, true);
    }

    public static Object pad(String str, Integer padLength, String padStr, boolean left)
    throws FunctionExecutionException {
	    int length = padLength.intValue();
	    if(length < 1) {
	         throw new FunctionExecutionException(QueryPlugin.Event.TEIID30402, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30402));
	    }
	    if(length < str.length()) {
	        return new String(str.substring(0, length));
	    }
	    // EXTENSION
	    // ISSUE: DVCORE-2607
	    // FIXED BY: Roman Pedchenko
	    // DATE: 2015.02.17
	    // if(length > DataTypeManager.MAX_STRING_LENGTH) {
	    // 	length = DataTypeManager.MAX_STRING_LENGTH;
	    if(length > DataTypeManager.getMaxStringLength()) {
	    	length = DataTypeManager.getMaxStringLength();
	    // END EXTENSION
	    }
	    // Get pad character
	    if(padStr.length() == 0) {
	         throw new FunctionExecutionException(QueryPlugin.Event.TEIID30403, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30403));
	    }
	    // Pad string
	    StringBuffer outStr = new StringBuffer(str);
	    while(outStr.length() < length) {
	    	if (left) {
	    		outStr.insert(0, padStr);
	    	} else {
	    		outStr.append(padStr);
	    	}
	    }
	    if (left) {
	    	return new String(outStr.substring(outStr.length() - length));
	    }
	    return new String(outStr.substring(0, length));
	}

    
    public static final String SPACE_CHAR = " "; //$NON-NLS-1$

    public static Object lpad(String inputString, Integer padLength)
        throws FunctionExecutionException {

        return lpad(inputString, padLength, SPACE_CHAR);
    }

    // ================== Function = rpad =====================

    public static Object rpad(String inputString, Integer padLength, String padStr)
        throws FunctionExecutionException {

    	return pad(inputString, padLength, padStr, false);
    }

    public static Object rpad(String inputString, Integer padLength)
        throws FunctionExecutionException {

        return rpad(inputString, padLength, SPACE_CHAR);
    }

    // ================== Function = translate =====================

    public static Object translate(String str, String in, String out)
        throws FunctionExecutionException {

	    // we should use this approach after switching to Java8
    	/*List<Integer> inCodePoints = in.codePoints().boxed().collect(Collectors.toList());
	    List<Integer> outCodePoints = out.codePoints().boxed().collect(Collectors.toList());

	    if(inCodePoints.size() != outCodePoints.size()) {*/
        if(in.length() != out.length()) {
             throw new FunctionExecutionException(QueryPlugin.Event.TEIID30404, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30404));
        }

        if(in.length() == 0 || str.length() == 0) {
            return str;
        }

        StringBuffer translated = new StringBuffer(str.length());
	    // we should use this approach after switching to Java8
        /*str.codePoints().forEachOrdered(c->{
		    int j = inCodePoints.indexOf(c);
		    if (j >= 0) {
			    translated.appendCodePoint(outCodePoints.get(j));
		    } else {
			    translated.appendCodePoint(c);
		    }});*/
        for(int i=0; i<str.length(); i++) {
            char c = str.charAt(i);
            int j = in.indexOf(c);
            if (j >= 0) {
                translated.append(out.charAt(j));
            } else {
                translated.append(c);
            }
        }
        return translated.toString();
    }

	// ================== Function = convert =====================

	public static Object convert(Object src, String type)
		throws FunctionExecutionException {
		try {
	    	// EXTENSION
	    	// ISSUE: DVCORE-177,368
	    	// FIXED BY: Roman Pedchenko
	    	// DATE: 2012.12.10,2013.04.30
	        String typeName = type;
	        int braceIndex = typeName.indexOf('(');
	        int length = -1;
			if (braceIndex >= 0) {
				try {
					length = Integer.parseInt(typeName.substring(braceIndex + 1, typeName.length() - 1));
				} catch (NumberFormatException e) { }
				typeName = typeName.substring(0, braceIndex);
			}
			Object transformedValue = DataTypeManager.transformValue(src, DataTypeManager.getDataTypeClass(typeName));
			if (length >= 0 && transformedValue instanceof String) {
				String string = (String) transformedValue;
				if (string.length() > length)
					transformedValue = length > 0 ? string.substring(0, length) : "";
			}
			return transformedValue;
	    	// END EXTENSION
		} catch(TransformationException e) {
			 throw new FunctionExecutionException(QueryPlugin.Event.TEIID30405, e, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30405, new Object[]{src, DataTypeManager.getDataTypeName(src.getClass()), type}));
		}
	}

    // ================== Function = context and rowlimit =====================

    /**
     * This function should never actually be called - it is here solely so the
     * xml context function can be resolved properly.  The actual function is
     * implemented in the XML planner.
     * @param context The context to apply the criteria in
     * @param expression The expression on the left side of the criteria
     * @return Same as expression
     */
    public static Object context(Object context, Object expression)
        throws FunctionExecutionException {

         throw new FunctionExecutionException(QueryPlugin.Event.TEIID30406, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30406));
    }

    /**
     * This pseudo-function should never actually be called - it is here solely so the
     * xml rowlimit function can be resolved properly.  The actual functionality is
     * implemented in the XML planner/processor.
     * @param expression The expression on the left side of the criteria, an xml node
     * @return doesn't really return anything; this pseudo-function is used to control
     * the number of rows returned from a mapping class.
     */
    public static Object rowlimit(Object expression)
        throws FunctionExecutionException {
    
         throw new FunctionExecutionException(QueryPlugin.Event.TEIID30407, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30407));
    }    

    /**
     * This pseudo-function should never actually be called - it is here solely so the
     * xml rowlimitexception function can be resolved properly.  The actual functionality is
     * implemented in the XML planner/processor.
     * @param expression The expression on the left side of the criteria, an xml node
     * @return doesn't really return anything; this pseudo-function is used to control
     * the number of rows returned from a mapping class.
     */
    public static Object rowlimitexception(Object expression)
        throws FunctionExecutionException {
    
         throw new FunctionExecutionException(QueryPlugin.Event.TEIID30407, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30407));
    }      
    
    // ================== Function = lookup =====================

    /**
     * This function should never actually be called - it is here solely so the
     * lookup function can be resolved properly.  The actual function is
     * implemented in the ExpresionEvaluator
     * @param codeTable 
     * @param returnElement 
     * @param keyElement 
     * @param keyValue 
     * @return Same as expression
     */
    public static Object lookup(Object codeTable, Object returnElement, Object keyElement, Object keyValue) {

        throw new UnsupportedOperationException(QueryPlugin.Util.gs(QueryPlugin.Event.DVCORE301190));
    }
	
    // ================== Function = nvl =====================
    
    public static Object ifnull(Object value, Object ifNullValue) {
    	return coalesce(value, ifNullValue);
    }
    
    public static Object coalesce(Object value, Object value1, Object... other) {
    	if (value != null) {
    		return value;
    	}
    	if (value1 != null) {
    		return value1;
    	}
        for (Object object : other) {
			if (object != null) {
				return object;
			}
		}
        return null;
    }

	// ================== Format date/time/timestamp TO String ==================
	public static String format(CommandContext context, Date date, String format)
		throws FunctionExecutionException {
		try {
            SimpleDateFormat sdf = CommandContext.getDateFormat(context, format);
            return sdf.format(date);
		} catch (IllegalArgumentException iae) {
			 throw new FunctionExecutionException(QueryPlugin.Event.TEIID30409, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30409,iae.getMessage()));
		}
	}

	//	================== Parse String TO date/time/timestamp  ==================
	private static Date parseDateHelper(CommandContext context, String date, String format, String localeTag)
			throws FunctionExecutionException {
		String originalDate = date;
		String originalFormat = format;
		try {
			// BUG: Parsing time fails if the part to be omitted is numeric
			// ISSUE: DVCORE-7850
			// SOLUTION: Check if pattern found then add a space before numbers
			// FIXED by Muhammad Imran Ansari
			// date 2022.09.28
			if (DVCORE7850_PATTERN.matcher(format).matches()) {
				// extract the digits pattern from the format, what is in between single quotes
				String digits = format.substring(format.indexOf('\'') + 1, format.lastIndexOf('\''));
				// find this digit pattern in the date value to be parsed
				// if the digit pattern is found and the first character is a gigit
				if ((Character.isDigit(digits.charAt(0))) && (date.contains(digits))) {
					// add a space in format after first single quote and before digits
					format = new StringBuilder(format).insert(format.indexOf('\'') + 1, ' ').toString();
					// add a space in date value before the digit pattern has been match
					date = new StringBuilder(date).insert(date.indexOf(digits), ' ').toString();
				}
			}
			// BUG END
			DateFormat df = null;
			if (localeTag instanceof String) {
				df = new SimpleDateFormat(format, Locale.forLanguageTag(localeTag));
			} else {
				df = CommandContext.getDateFormat(context, format);
			}
			Date parsed = null;
			try {
				parsed = df.parse(date);
			} catch (ParseException e) {
				if (format.contains("EEE")) {
					int eeePos = format.indexOf("EEE") + 2;
					date = date.substring(0, eeePos) + "." + date.substring(eeePos);
					parsed = df.parse(date);
				} else throw e;
			}
			// create the max possible valid date
			Calendar cal = Calendar.getInstance();
			cal.set(9999, 11, 31, 23, 59, 59);
			cal.set(Calendar.MILLISECOND, 999);
			// make sure that your parsed data is not after the max possible valid date otherwise throw error
			if (parsed.after(cal.getTime())) {
				throw new ParseException(QueryPlugin.Util.gs(QueryPlugin.Event.DVCORE301191), 0);
			}
			return parsed;
		} catch (ParseException e) {
			throw new FunctionExecutionException(QueryPlugin.Event.TEIID30410, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30410, originalDate, originalFormat));
		}
	}
	
	public static Timestamp parseTimestamp(CommandContext context, String timestamp, String format, String... specs)
			throws FunctionExecutionException {
		String localeTag = specs.length > 0 ? specs[0] : null;
		return new Timestamp(parseDateHelper(context, timestamp, format, localeTag).getTime());
	}

	//	================== Format number TO String ==================
	public static String format(CommandContext context, Number number, String format)
	throws FunctionExecutionException {
		try {
	        DecimalFormat df = CommandContext.getDecimalFormat(context, format);
	        return df.format(number);
		} catch (IllegalArgumentException iae) {
			 throw new FunctionExecutionException(QueryPlugin.Event.TEIID30411, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30411, iae.getMessage()));
		}
	}

	//	================== Parse String TO numbers ==================
	public static Object parseInteger(CommandContext context, String number, String format)
		throws FunctionExecutionException {
		Number intNum = parseBigDecimal(context, number, format);
		return new Integer(intNum.intValue());
	}

	public static Object parseLong(CommandContext context, String number, String format)
		throws FunctionExecutionException {
		Number longNum = parseBigDecimal(context, number, format);
		return new Long(longNum.longValue());
	}

	public static Object parseDouble(CommandContext context, String number, String format)
		throws FunctionExecutionException {
		Number doubleNum = parseBigDecimal(context, number, format);
		return new Double(doubleNum.doubleValue());
	}

	public static Object parseFloat(CommandContext context, String number, String format)
		throws FunctionExecutionException {
		Number longNum = parseBigDecimal(context, number, format);
		return new Float(longNum.floatValue());
	}

	public static Object parseBigInteger(CommandContext context, String number, String format)
		throws FunctionExecutionException {
		Number bigIntegerNum = parseBigDecimal(context, number, format);
		return new BigInteger(bigIntegerNum.toString());
	}

	public static BigDecimal parseBigDecimal(CommandContext context, String number, String format)
		throws FunctionExecutionException {
		DecimalFormat df= CommandContext.getDecimalFormat(context, format);
		try {
			return (BigDecimal) df.parse(number);
		} catch (ParseException e) {
			 throw new FunctionExecutionException(QueryPlugin.Event.TEIID30412, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30412,number,format));
		}
	}

	// ================== Function - ACOS =====================
	public static Object acos(Number number) {
		return new Double(Math.acos(number.doubleValue()));
	}

	// ================== Function - ASIN =====================
	public static Object asin(Number number) {
		return new Double(Math.asin(number.doubleValue()));
	}

	// ================== Function - ATAN =====================
	public static Object atan(Number number) {
		return new Double(Math.atan(number.doubleValue()));
	}

	// ================== Function - ATAN2 =====================
	public static Object atan2(Number number1, Number number2) {
		return new Double(Math.atan2(number1.doubleValue(), number2.doubleValue()));
	}

	// ================== Function - COS =====================
	public static Object cos(Number number) {
		return new Double(Math.cos(number.doubleValue()));
	}

	// ================== Function - COT =====================
	public static Object cot(Number number) {
		return new Double(1/Math.tan(number.doubleValue()));
	}

	// ================== Function - DEGREES =====================
	public static Object degrees(Number number) {
		return new Double(Math.toDegrees(number.doubleValue()));
	}

	// ================== Function - PI =====================
	public static Object pi() {
		return new Double(Math.PI);
	}

	// ================== Function - RADIANS =====================
	public static Object radians(Number number) {
		return new Double(Math.toRadians(number.doubleValue()));
	}

	// ================== Function - SIN =====================
	public static Object sin(Number number) {
		return new Double(Math.sin(number.doubleValue()));
	}

	// ================== Function - TAN =====================
	public static Object tan(Number number) {
		return new Double(Math.tan(number.doubleValue()));
	}

    // ================== Function - BITAND =====================
	public static Object bitand(int x, int y) {
        return x & y;
	}

    // ================== Function - BITOR =====================
    public static Object bitor(int x, int y) {
        return x | y;
    }

    // ================== Function - BITXOR =====================
    public static Object bitxor(int x, int y) {
        return x ^ y;
    }

    // ================== Function - BITNOT =====================
    public static int bitnot(int x) {
        return x ^ 0xFFFFFFFF;
    }

    // ================= Functions - USER/CALLER ========================
	public static String trimSecurityDomain(String username) {
		return trimSecurityDomain(username, false);
	}

	public static String trimSecurityDomain(String username, boolean returnNull) {
		return trimSecurityDomain(username, returnNull, false);
	}

	private static List<String> userTypeDomainsCache = null; 
	public static String trimSecurityDomain(String username, boolean returnNull, boolean trimUserTypeDomains) {
		if (username == null || username.isEmpty())
			return username;
		int idx = username.lastIndexOf("@");
		if (idx < 0) {
			if (returnNull)
				return null;
			idx = username.length();
	}
		if (userTypeDomainsCache == null)
			userTypeDomainsCache = DVServices.callne(DVUsersHandlerIntf.class, DVUsersHandlerIntf::getUserTypeDomains, null);
		if (!trimUserTypeDomains && userTypeDomainsCache != null)
			for (String s : userTypeDomainsCache) {
				if (username.endsWith("@" + s))
					return username;
			}
		return username.substring(0, idx);
	}

    public static Object user(CommandContext context) {
        return trimSecurityDomain(context.getUserName());
    }

    public static Object caller(CommandContext context) {
        return trimSecurityDomain(context.getCallerUser());
    }

    public static Object current_database(CommandContext context) {
    	return context.getVdbName();
    }

    // ================= Function - COMMANDPAYLOAD ========================
    public static Object commandPayload(CommandContext context) {
        Serializable payload = context.getCommandPayload();
        if(payload == null) {
            return null;
        }
        // 0-arg form - just return payload as a string if it exists
        return payload.toString();
    }

    public static Object commandPayload(CommandContext context, String param) 
        throws ExpressionEvaluationException, FunctionExecutionException{
        Serializable payload = context.getCommandPayload();
        if(payload == null) {
            return null;
        }
        
        // 1-arg form - assume payload is a Properties object
        if(payload instanceof Properties) {
            return ((Properties)payload).getProperty(param);
        }            
        // Payload was bad
         throw new ExpressionEvaluationException(QueryPlugin.Event.TEIID30413, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30413, "commandPayload", payload.getClass().getName())); //$NON-NLS-1$
    }

    // ================= Function - ENV ========================
    public static Object env(String propertyName) {
        // All context property keys must be lowercase - we lowercase the incoming key here to match regardless of case
        String propertyNameNocase = propertyName.toLowerCase();
        String value = System.getProperty(propertyName);
        if (value == null) {
            value = System.getProperty(propertyNameNocase);
        }
        return value;            
    }

	public static Object session_id(CommandContext context) {
		return context.getConnectionId();
	}

	public static Object request_id(CommandContext context) {
		return context.getRequestID().getExecutionID();
	}


	// ================= Function - MODIFYTIMEZONE ========================
    
    public static Object modifyTimeZone(Timestamp value, String originalTimezoneString, String targetTimezoneString) {
        TimeZone originalTimeZone = TimeZone.getTimeZone(originalTimezoneString);
        TimeZone dbmsTimeZone = TimeZone.getTimeZone(targetTimezoneString);

        // Check that the dbms time zone is really different than the local time zone
        if (originalTimeZone.equals(dbmsTimeZone)) {
            return value;
        }

        Calendar cal = Calendar.getInstance(dbmsTimeZone);
        
        return TimestampWithTimezone.createTimestamp(value, originalTimeZone, cal);
    }

    public static Object modifyTimeZone(CommandContext context, Timestamp value, String targetTimezoneString) {
        TimeZone dbmsTimeZone = TimeZone.getTimeZone(targetTimezoneString);

        Calendar cal = Calendar.getInstance(dbmsTimeZone);
        
        return TimestampWithTimezone.createTimestamp(value, context.getServerTimeZone(), cal);
    }

	@TeiidFunction(category=FunctionCategoryConstants.CONVERSION, name="to_chars", nullOnNull=true)
	public static ClobType toChars(BlobType value, String encoding) throws SQLException, IOException, FunctionExecutionException {
		//TODO: defaulting to true as that was the pre 8.4.1 behavior
		return toChars(value, encoding, true);
	}

	@TeiidFunction(category=FunctionCategoryConstants.CONVERSION, name="to_chars")
	public static ClobType toChars(BlobType value, String encoding, boolean wellFormed) throws SQLException, IOException, FunctionExecutionException {
    	Charset cs;
    	try {
			cs = CharsetUtils.getCharset(encoding);
    	} catch (IllegalArgumentException e) {
    		throw new FunctionExecutionException(QueryPlugin.Util.getString("ValidationVisitor.invalid_encoding", encoding));
    	}
		BlobInputStreamFactory bisf = new BlobInputStreamFactory(value.getReference());
    	ClobImpl clob = new ClobImpl(bisf, -1);
    	clob.setCharset(cs);
		if (clob.length() == 0 && UTF_8_BOM.equals(cs)) {
			clob = new ClobImpl(bisf, -1);
			clob.setCharset(StandardCharsets.UTF_8); //some symbols in charset UTF_8_BOM may produce empty clob while saving
		}
		if (!wellFormed && !CharsetUtils.BASE64_NAME.equalsIgnoreCase(encoding) && !CharsetUtils.HEX_NAME.equalsIgnoreCase(encoding)) {
			//validate that the charcter conversion is possible
			//TODO: cache the result in a filestore
			Reader r = clob.getCharacterStream();
			try {
				while (r.read() != -1) {

				}
			} catch (IOException e) {
				CharacterCodingException cce = ExceptionUtil.getExceptionOfType(e, CharacterCodingException.class);
				if (cce != null) {
					throw new IOException(CorePlugin.Util.gs(CorePlugin.Event.TEIID10082, cs.displayName()), cce);
				}
				throw e;
			} finally {
				r.close();
			}
		}
    	return new ClobType(clob);
    }

	@TeiidFunction(category=FunctionCategoryConstants.CONVERSION, name="to_bytes", nullOnNull=true)
	public static BlobType toBytes(ClobType value, String encoding) throws IOException, SQLException, FunctionExecutionException {
		return toBytes(value, encoding, true);
	}

	@TeiidFunction(category=FunctionCategoryConstants.CONVERSION, name="to_bytes")
	public static BlobType toBytes(ClobType value, String encoding, boolean wellFormed) throws IOException, SQLException, FunctionExecutionException {
    	Charset cs;
    	try {
			cs = CharsetUtils.getCharset(encoding);
    	} catch (IllegalArgumentException e) {
    		throw new FunctionExecutionException(QueryPlugin.Util.getString("ValidationVisitor.invalid_encoding", encoding));
    	}
    	ClobInputStreamFactory cisf = new ClobInputStreamFactory(value.getReference());
    	cisf.setCharset(cs);
		if (!wellFormed || CharsetUtils.BASE64_NAME.equalsIgnoreCase(encoding) || CharsetUtils.HEX_NAME.equalsIgnoreCase(encoding)) {
    		//validate that the binary conversion is possible
    		//TODO: cache the result in a filestore
			InputStream is = new ReaderInputStream(value.getCharacterStream(),
					cs.newEncoder().onMalformedInput(CodingErrorAction.REPORT)
							.onUnmappableCharacter(CodingErrorAction.REPORT));
    		try {
	    		while (is.read() != -1) {
	    			
	    		}
		    } catch (IOException e) {
			    CharacterCodingException cce = ExceptionUtil.getExceptionOfType(e, CharacterCodingException.class);
			    if (cce != null) {
				    throw new IOException(CorePlugin.Util.gs(CorePlugin.Event.TEIID10083, cs.displayName()), cce);
			    }
			    throw e;
    		} finally {
    			is.close();
    		}
    	}
    	return new BlobType(new BlobImpl(cisf));
	}
    
    public static String unescape(String string) {
    	StringBuilder sb = new StringBuilder();
    	boolean escaped = false;
    	for (int i = 0; i < string.length(); i++) {
    		char c = string.charAt(i);
    		if (escaped) {
	    		switch (c) {
	    		case 'b':
	    			sb.append('\b');
	    			break;
	    		case 't':
	    			sb.append('\t');
	    			break;
	    		case 'n':
	    			sb.append('\n');
	    			break;
	    		case 'f':
	    			sb.append('\f');
	    			break;
	    		case 'r':
	    			sb.append('\r');
	    			break;
	    		case 'u':
					i = parseNumericValue(string, sb, i, 0, 4, 4);
					//TODO: this should probably be strict about needing 4 digits
	    			break;
    			default:
    				int value = Character.digit(c, 8);
					if (value == -1) {
						sb.append(c);
					} else {
						int possibleDigits = value < 3 ? 2:1;
						int radixExp = 3;
    					i = parseNumericValue(string, sb, i, value, possibleDigits, radixExp);
    				}
	    		}
	    		escaped = false;
    		} else {
    			if (c == '\\') {
    				escaped = true;
    			} else {
					sb.append(c);
    			}
    		}
    	}
    	//TODO: should this be strict?
    	//if (escaped) {
    		//throw new FunctionExecutionException();
    	//}
    	return sb.toString();
    }

	private static int parseNumericValue(String string, StringBuilder sb,
			int i, int value, int possibleDigits, int radixExp) {
		for (int j = 0; j < possibleDigits; j++) {
			if (i + 1 == string.length()) {
				break;
			}
			char digit = string.charAt(i + 1);
			int val = Character.digit(digit, 1 << radixExp);
			if (val == -1) {
				break;
			}
			i++;
			value = (value << radixExp) + val;
		}
		sb.append((char)value);
		return i;
	}

	public static String uuid() {
		return UUID.randomUUID().toString();
	}
	
	public static Object array_get(Object array, int index) throws FunctionExecutionException, SQLException {
		try {
// EXTENSION
// ISSUE: DVCORE-2441
// FIXED BY: Roman Pedchenko
// DATE: 2014.10.23
// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-247, 2166, 2151, 2234, 2581, 2571, 2369, 1386, 2226, 2233, 2224, 2225, 2257
//     		if (array.getClass().isArray()) {
//     			return Array.get(array, index - 1);
//     		}
// END EXTENSION
			if (array instanceof java.sql.Array) {
				return Array.get(((java.sql.Array)array).getArray(index, 1), 0);
			}
			// EXTENSION
			// ISSUE: DVCORE-2441
			// FIXED BY: Roman Pedchenko
			// DATE: 2014.10.20
			// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2111
			// EXTENSION
			// ISSUE: DVCORE-2441
			// FIXED BY: Roman Pedchenko
			// DATE: 2014.10.23
			// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-247, 2166, 2151, 2234, 2581, 2571, 2369, 1386, 2226, 2233, 2224, 2225, 2257
			// if (array instanceof ArrayValue) {
			// 	return ((ArrayValue)array).getValues()[index - 1];
			if (array.getClass().isArray()) {
				return Array.get(array, index - 1);
			// END EXTENSION
			}
			// END EXTENSION
		} catch (ArrayIndexOutOfBoundsException e) {
			// EXTENSION
			// ISSUE: DVCORE-2441
			// FIXED BY: Roman Pedchenko
			// DATE: 2014.10.23
			// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-247, 2166, 2151, 2234, 2581, 2571, 2369, 1386, 2226, 2233, 2224, 2225, 2257
			//  throw new FunctionExecutionException(QueryPlugin.Event.TEIID30415, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30415, index));
			return null;
			// END EXTENSION
		}
		 throw new FunctionExecutionException(QueryPlugin.Event.TEIID30416, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30416, array.getClass()));
	}
	
	public static int array_length(Object array) throws FunctionExecutionException, SQLException {
// EXTENSION
// ISSUE: DVCORE-2441
// FIXED BY: Roman Pedchenko
// DATE: 2014.10.23
// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-247, 2166, 2151, 2234, 2581, 2571, 2369, 1386, 2226, 2233, 2224, 2225, 2257
//     	if (array.getClass().isArray()) {
//     		return Array.getLength(array);
//     	}
// END EXTENSION
		if (array instanceof java.sql.Array) {
			return Array.getLength(((java.sql.Array)array).getArray());
		}
		// EXTENSION
		// ISSUE: DVCORE-2441
		// FIXED BY: Roman Pedchenko
		// DATE: 2014.10.20
		// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-2111
		// EXTENSION
		// ISSUE: DVCORE-2441
		// FIXED BY: Roman Pedchenko
		// DATE: 2014.10.23
		// ADOPTED FROM: https://issues.jboss.org/browse/TEIID-247, 2166, 2151, 2234, 2581, 2571, 2369, 1386, 2226, 2233, 2224, 2225, 2257
		// if (array instanceof ArrayValue) {
		// 	return ((ArrayValue)array).getValues().length;
		if (array.getClass().isArray()) {
			return Array.getLength(array);
		// END EXTENSION
		}
		// END EXTENSION
		 throw new FunctionExecutionException(QueryPlugin.Event.TEIID30416, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30416, array.getClass()));
	}

	public static Object array_add(Object array, Object value) throws FunctionExecutionException, SQLException {
		// extracting the original array
		if (array == null) {
			return null;
		}
		Object tempArray = null;
		if (array instanceof java.sql.Array)
			tempArray = ((java.sql.Array) array).getArray();
		if (array.getClass().isArray())
			tempArray = array;
		if (tempArray == null)
			throw new FunctionExecutionException(QueryPlugin.Util.getString(QueryPlugin.Event.DVCORE6123.name(), array));
		List<Object> list = arrayAsList(tempArray);

		// extracting the value to add
		Object tempArray2 = null;
        if (value != null) {
            if (value instanceof java.sql.Array)
                tempArray2 = ((java.sql.Array) value).getArray();
            if (value.getClass().isArray())
                tempArray2 = value;
		}

		Class<?> componentType = tempArray.getClass().getComponentType();
		// adding the value
		if (tempArray2 == null) {
			if (value != null && componentType != DataTypeManager.DefaultDataClasses.NULL) {
			checkComponentType(componentType, value.getClass());
			}
			list.add(value);
		} else {
			checkComponentType(componentType, tempArray2.getClass().getComponentType());
			List<Object> list2 = arrayAsList(tempArray2);
			list.addAll(list2);
		}
		return new ArrayImpl(list.toArray(new Object[list.size()]));
	}

	private static void checkComponentType(Class<?> componentType, Class<?> valueType) throws FunctionExecutionException {
		if (!componentType.isAssignableFrom(valueType))
			throw new FunctionExecutionException(QueryPlugin.Util.gs(QueryPlugin.Event.DVCORE301192, componentType, valueType));
	}

	private static List<Object> arrayAsList(Object array) {
		if (array == null)
			return Collections.emptyList();
		int len = Array.getLength(array);
		List<Object> list = new ArrayList<Object>(len);
		for (int i = 0; i < len; i++)
			list.add(Array.get(array, i));
		return list;
	}

	public static Object array_sub(Object array, int start, int count) throws FunctionExecutionException, SQLException {
		try {
			if (array instanceof java.sql.Array)
				return new ArrayImpl((Object[]) ((java.sql.Array) array).getArray(start, count));
			if (array.getClass().isArray())
				return new ArrayImpl(Arrays.copyOfRange((Object[]) array, start - 1, start + count - 1));
		} catch (ArrayIndexOutOfBoundsException e) {
			return null;
		}
		throw new FunctionExecutionException(QueryPlugin.Event.TEIID30416, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30416, array.getClass()));
	}

	public static int array_in(Object array, Object needle) throws FunctionExecutionException, SQLException {
		if (array == null)
			return 0;
		// extracting the original array
		Object tempArray = null;
		if (array instanceof java.sql.Array)
			tempArray = ((java.sql.Array) array).getArray();
		if (array.getClass().isArray())
			tempArray = array;
		if (tempArray == null)
			throw new FunctionExecutionException(QueryPlugin.Event.TEIID30416, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30416, array.getClass()));
		Object[] objectArray = (Object[]) tempArray;
		for (int i = 0; i < objectArray.length; i++) {
			final Object val = objectArray[i];
			if ((needle != null && needle.equals(val)) || (needle == null && val == null)) {
				return i + 1;
			}
		}
		return 0;
	}

	public static boolean array_like(Object array, String needle) throws SQLException, ExpressionEvaluationException {
		if (array == null)
			return false;
		// extracting the original array
		Object tempArray = null;
		if (array instanceof java.sql.Array)
			tempArray = ((java.sql.Array) array).getArray();
		if (array.getClass().isArray())
			tempArray = array;
		if (tempArray == null)
			throw new FunctionExecutionException(QueryPlugin.Event.TEIID30416, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30416, array.getClass()));
		for (Object val : (Object[]) tempArray) {
			if ((needle != null && Evaluator.match(needle, MatchCriteria.NULL_ESCAPE_CHAR, val.toString(), MatchMode.LIKE)) ||
					(needle == null && val == null)) {
				return true;
			}
		}
		return false;
	}

	public static boolean array_like_regex(Object array, String needle) throws SQLException, ExpressionEvaluationException {
		if (array == null)
			return false;
		// extracting the original array
		Object tempArray = null;
		if (array instanceof java.sql.Array)
			tempArray = ((java.sql.Array) array).getArray();
		if (array.getClass().isArray())
			tempArray = array;
		if (tempArray == null)
			throw new FunctionExecutionException(QueryPlugin.Event.TEIID30416, QueryPlugin.Util.gs(QueryPlugin.Event.TEIID30416, array.getClass()));
		for (Object val : (Object[]) tempArray)
			if ((needle != null && Evaluator.match(needle, MatchCriteria.NULL_ESCAPE_CHAR, val.toString(), MatchMode.REGEX)) ||
					(needle == null && val == null)) {
				return true;
			}
		return false;
	}

	// EXTENSION
	// ISSUE: DVCORE-399
	// FIXED BY: Roman Pedchenko
	// DATE: 2013.05.06
	public static Timestamp lastUpdated(String target) throws FunctionExecutionException {
		return null;
	}
	// END EXTENSION
	
	// EXTENSION
	// ISSUE: DVCORE-2365
	// FIXED BY: Roman Pedchenko
	// DATE: 2014.09.23
	public static Object asList(Object... args) throws FunctionExecutionException {
		return Arrays.asList(args);
	}
	public static Object asArray(Object... args) throws FunctionExecutionException {
		return args;
	}
	// END EXTENSION

	public static Object length(ClobType clob) throws Exception {
		return length(ClobType.getString(clob));
	}

    public static Object substring(ClobType clob, Integer startVal, Integer lengthVal) throws Exception {
    	return substring(ClobType.getString(clob), startVal, lengthVal);
    }

    public static Object substring(ClobType clob, Integer start) throws Exception {
    	return substring(ClobType.getString(clob), start);
    }
    
	public static Object left(ClobType clob, Integer count) throws Exception {
		return left(ClobType.getString(clob), count);
	}

	public static Object right(ClobType clob, Integer count) throws Exception {
		return right(ClobType.getString(clob), count);
	}

	public static Object locate(ClobType sub, ClobType clob) throws Exception {
		return locate(sub!=null?ClobType.getString(sub):null, clob!=null?ClobType.getString(clob):null);
	}
	
	public static Object locate(ClobType sub, ClobType clob, Integer start) throws Exception {
		return locate(sub!=null?ClobType.getString(sub):null, clob!=null?ClobType.getString(clob):null, start);
	}
	
	public static Object trim(String trimSpec, String trimChar, ClobType clob) throws Exception {
		return trim(trimSpec, trimChar, ClobType.getString(clob));
	}
	
	public static Object leftTrim(ClobType clob, char trimChar) throws Exception {
		return leftTrim(ClobType.getString(clob), trimChar);
	}
	
	public static Object leftTrim(ClobType clob) throws Exception {
		return leftTrim(ClobType.getString(clob));
	}

	public static Object rightTrim(ClobType clob, char trimChar) throws Exception {
		return rightTrim(ClobType.getString(clob), trimChar);
	}
	
	public static Object rightTrim(ClobType clob) throws Exception {
		return rightTrim(ClobType.getString(clob));
	}

	public static Object replace(ClobType clob, ClobType subString, ClobType replaceString) throws Exception {
		return replace(ClobType.getString(clob), ClobType.getString(subString), ClobType.getString(replaceString));
	}

	public static Object lpad(ClobType inputClob, Integer padLength, ClobType padStr) throws Exception {
		return lpad(ClobType.getString(inputClob), padLength, ClobType.getString(padStr));
	}

	public static Object lpad(ClobType inputClob, Integer padLength) throws Exception {
		return lpad(ClobType.getString(inputClob), padLength);
	}

	public static Object rpad(ClobType inputClob, Integer padLength, ClobType padStr) throws Exception {
		return rpad(ClobType.getString(inputClob), padLength, ClobType.getString(padStr));
	}

	public static Object rpad(ClobType inputClob, Integer padLength) throws Exception {
		return rpad(ClobType.getString(inputClob), padLength);
	}

    public static Object unescape(ClobType clob) throws Exception {
    	return unescape(ClobType.getString(clob));
    }
	// END EXTENSION

	//SHA1 | SHA2_256 | SHA2_512

	@TeiidFunction(category= FunctionCategoryConstants.SECURITY, nullOnNull=true, pushdown= FunctionMethod.PushDown.CAN_PUSHDOWN)
 	public static BinaryType md5_binary(String plainText) throws NoSuchAlgorithmException, UnsupportedEncodingException {
		return digest(plainText.getBytes("UTF-8"), "md5"); //$NON-NLS-1$ //$NON-NLS-2$
    }

	@TeiidFunction(category=FunctionCategoryConstants.SECURITY, nullOnNull=true, pushdown= FunctionMethod.PushDown.CAN_PUSHDOWN)
 	public static BinaryType sha1(String plainText) throws NoSuchAlgorithmException, UnsupportedEncodingException {
		return digest(plainText.getBytes("UTF-8"), "sha-1"); //$NON-NLS-1$ //$NON-NLS-2$
	}

	@TeiidFunction(category=FunctionCategoryConstants.SECURITY, nullOnNull=true, pushdown= FunctionMethod.PushDown.CAN_PUSHDOWN)
 	public static BinaryType sha2_256(String plainText) throws NoSuchAlgorithmException, UnsupportedEncodingException {
		return digest(plainText.getBytes("UTF-8"), "sha-256"); //$NON-NLS-1$ //$NON-NLS-2$
    }

	@TeiidFunction(category=FunctionCategoryConstants.SECURITY, nullOnNull=true, pushdown= FunctionMethod.PushDown.CAN_PUSHDOWN)
 	public static BinaryType sha2_512(String plainText) throws NoSuchAlgorithmException, UnsupportedEncodingException {
		return digest(plainText.getBytes("UTF-8"), "sha-512"); //$NON-NLS-1$ //$NON-NLS-2$
    }

	@TeiidFunction(category=FunctionCategoryConstants.SECURITY, nullOnNull=true)
 	public static BinaryType md5_binary(BinaryType plainText) throws NoSuchAlgorithmException {
	    return digest(plainText.getBytesDirect(), "md5"); //$NON-NLS-1$
    }

	@TeiidFunction(category=FunctionCategoryConstants.SECURITY, nullOnNull=true, pushdown= FunctionMethod.PushDown.CAN_PUSHDOWN)
 	public static BinaryType sha1(BinaryType plainText) throws NoSuchAlgorithmException {
	    return digest(plainText.getBytesDirect(), "sha-1"); //$NON-NLS-1$
    }

	@TeiidFunction(category=FunctionCategoryConstants.SECURITY, nullOnNull=true, pushdown= FunctionMethod.PushDown.CAN_PUSHDOWN)
 	public static BinaryType sha2_256(BinaryType plainText) throws NoSuchAlgorithmException {
	    return digest(plainText.getBytesDirect(), "sha-256"); //$NON-NLS-1$
    }

	@TeiidFunction(category=FunctionCategoryConstants.SECURITY, nullOnNull=true, pushdown= FunctionMethod.PushDown.CAN_PUSHDOWN)
 	public static BinaryType sha2_512(BinaryType plainText) throws NoSuchAlgorithmException {
	    return digest(plainText.getBytesDirect(), "sha-512"); //$NON-NLS-1$
	}

	public static BinaryType digest(byte[] plainText, String algorithm) throws NoSuchAlgorithmException {
		MessageDigest messageDigest = MessageDigest.getInstance(algorithm);
		return new BinaryType(messageDigest.digest(plainText));
    }
	
	/**
     * Perform find-replace operation on a string using regular expressions.
     *
     * See {@link java.util.regex.Pattern} for more information.
     *
     * @param context
     * @param source Value to perform replacement on.
     * @param regex Regular expression pattern.
     * @param replacement Replacement string.
     * @return Modified source if the pattern was matched, otherwise source.
     * @throws FunctionExecutionException If regex pattern was invalid.
     */
   @TeiidFunction(name=SourceSystemFunctions.REGEXP_REPLACE,
                  category=FunctionCategoryConstants.STRING,
                  nullOnNull=true)
   public static String regexpReplace(CommandContext context,
                                      String source,
                                      String regex,
                                      String replacement)
           throws FunctionExecutionException {
       return regexpReplace(context, source, regex, replacement, ""); // $NON-NLS-1$
   }
 
   /**
     * Perform find-replace operation on a string using regular expressions.
     *
     * See {@link java.util.regex.Pattern} for more information.
     *
     * Flags can be used to modify the matching behavior of the regular expression.
     * <ul>
     *   <li>g - Replaces all matches instead of just the first.</li>
     *   <li>i - Performs case insensitive pattern match.</li>
     *   <li>m - Changes behavior of "^" and "$" to match the beginning and end
     *           of the line instead of the entire string.</li>
     * </ul>
     *
     * @param context
     * @param source Value to perform replacement on.
     * @param regex Regular expression pattern.
     * @param replacement Replacement string.
     * @param flags Flags to modify behavior of the pattern.
     * @return Modified source if the pattern was matched, otherwise source.
     * @throws FunctionExecutionException If an invalid flag was supplied or if the
     *                                    regex pattern was invalid.
     */
   @TeiidFunction(name=SourceSystemFunctions.REGEXP_REPLACE,
                  category=FunctionCategoryConstants.STRING,
                  nullOnNull=true)
   public static String regexpReplace(CommandContext context,
                                      String source,
                                      String regex,
                                      String replacement,
                                      String flags)
           throws FunctionExecutionException {
       
       // Parse regex flags into a bitmask for Pattern API.
       // Exception is the 'g' flag which makes us call replaceAll instead of
       // replaceFirst.
       boolean global = false;
       int bitFlags = 0;
 
       for (int i = 0; i < flags.length(); ++i) {
           char c = flags.charAt(i);
           if (c == 'g') {
               // Global match.
               global = true;
           } else if (c == 'i') {
               // Case insensitive match.
               bitFlags |= Pattern.CASE_INSENSITIVE;
           } else if (c == 'm') {
               // Change ^$ to match line endings instad of entire source.
               bitFlags |= Pattern.MULTILINE;
           } else {
               throw new FunctionExecutionException(QueryPlugin.Util.gs(QueryPlugin.Event.TEIID31168, c));
           }
       }
       
       // Compile regex into pattern and do replacement.
       Pattern pattern;
       try {
           pattern = CommandContext.getPattern(context, regex, bitFlags);
       } catch (PatternSyntaxException e) {
           throw new FunctionExecutionException(e);
       }
       Matcher matcher = pattern.matcher(source);
       String result;
       if (global) {
           result = matcher.replaceAll(replacement);
       } else {
           result = matcher.replaceFirst(replacement);
       }
 
       return result;
   }


    @TeiidFunction(name = SourceSystemFunctions.DATE_TRUNC,
            category = FunctionCategoryConstants.DATETIME,
            nullOnNull = true,
            pushdown = FunctionMethod.PushDown.CAN_PUSHDOWN)
    public static Timestamp truncDate(CommandContext ctx, Object... varArgs) throws FunctionExecutionException {
        String datePart = null, date = null;
        List<Object> parameters = Arrays.asList(varArgs);
        if ((parameters == null) || (parameters.size() < 2) || (parameters.size() > 2)) {
            throw new RuntimeException(QueryPlugin.Util.gs(QueryPlugin.Event.DVCORE7932_1, parameters, SourceSystemFunctions.DATE_TRUNC));
}
        // handle first parameter - datePart
        if (parameters.get(0) instanceof String) {
            datePart = parameters.get(0).toString();
        } else {
            throw new RuntimeException(QueryPlugin.Util.gs(QueryPlugin.Event.DVCORE7932_1, parameters + "datePart", SourceSystemFunctions.DATE_TRUNC));
        }
        // handle second parameter - date
        if (parameters.get(1) instanceof String || parameters.get(1) instanceof Timestamp) {
            date = parameters.get(1).toString();
        } else if (parameters.get(1) instanceof Date) {
            date = new Timestamp(((Date) parameters.get(1)).getTime()).toString();
        } else if (parameters.get(1) instanceof Time) {
            // Add epoch 0, meaning 1970-01-01
            date = new Timestamp(((Time) parameters.get(1)).getTime()).toString();
        } else {
            throw new RuntimeException(QueryPlugin.Util.gs(QueryPlugin.Event.DVCORE7932_1, "date", SourceSystemFunctions.DATE_TRUNC));
        }
        // no more parameters to handle
		return parseTimestamp(ctx,  truncDateImpl(datePart, date), DEFAULT_TIMESTAMP_FORMAT);
    }

    // For century and millennium
    private final static String referenceDate = "0001-01-01";
	private final static String epochTime = "1970-01-01";
    private final static String referenceTime = "00:00:00";
    private final static String referenceSubSec = ".0";
    private final static String referenceNonDate = " " + referenceTime + referenceSubSec;
    private final static String referenceDateTime = referenceDate + referenceNonDate;
    private final static int dateLength = referenceDate.length();
    private final static int referenceTimeFracLength = referenceSubSec.length();
    private final static int referenceLength = referenceDateTime.length();

    public static String truncDateImpl(String datePart, String date) {
		date = date.replace("T"," ");
        // Time wo date component
		if (!date.contains(" ") && !date.contains("-")) date = epochTime + " " + date;
        // Date wo time component
		if (!date.contains(" ") && !date.contains(":")) date = date + " " + referenceTime;
        int length = date.length();
        if (length == dateLength) date += " ";
        // Complete input to second (if only date / hour / minute data available)
        if (length < referenceLength - referenceTimeFracLength)
            date += referenceTime.substring(Math.min(0, referenceLength - referenceTimeFracLength - date.length()));
        // If input in seconds
        if (date.length() == referenceLength - referenceTimeFracLength) date += referenceSubSec;

        int truncIx = length;
        // Drop quotes if needed
        if (datePart.charAt(0) == '\'') datePart = datePart.substring(1, datePart.length() - 2);
        switch (datePart.toUpperCase()) {
            case "MILLENNIUM":
            case "MILLENNIUMS": // DB2
				// The year 2000 belongs to the 1001 millennium, thus CAN"T be truncated
				// The year 1000 belongs to the 0001 millennium, thus CAN"T be truncated
				date = String.valueOf(String.format("%04d", Integer.parseInt(date.substring(0, 4)) - 1));
                truncIx = 1;
                break;
            case "CC": // ORACLE
            case "SCC": // ORACLE
            case "CENTURY":
            case "CENTURIES": // DB2
				// The year 2000 belongs to the 1901 century, thus CAN"T be truncated
				// The year 1000 belongs to the 0901 century, needs careful handling
				date = String.valueOf(String.format("%04d", Integer.parseInt(date.substring(0, 4)) - 1));
                truncIx = 2;
                break;
            case "DECADE": // Year ends in 0, unlike century and millennium
			case "DECADES": // DB2
                date = date.substring(0, 3) + "0";
                // continue
            case "Y":
            case "YY":
            case "YYY":
            case "YYYY":
            case "YEAR":
			case "YEARS": // DB2
                truncIx = 4;
                break;
            case "Q":
            case "QQ":
            case "QUARTER":
                int month = Integer.parseInt(date.substring(5, 7));
                String quarter = String.format("%02d", 1 + 3 * ((month - 1) / 3));
                return date.substring(0, 5) + quarter + referenceDateTime.substring(7);
            case "M":
            case "RM":
            case "MM":
            case "MON":
            case "MONTH":
			case "MONTHS": // DB2
                truncIx = 7;
                break;
			// First MONDAY of a week
            case "IW": // Oracle
            case "ISO_WEEK": // MSSQL
            case "ISOWK": // MSSQL
            case "ISOWW": // MSSQL
            case "WEEK":
				LocalDate firstMon = LocalDate.parse(date.substring(0, 10)).with(TemporalAdjusters.previousOrSame(DayOfWeek.MONDAY));
                return firstMon + referenceNonDate;
			// First SUNDAY of a week
            case "D": // Oracle
            case "DY": // Oracle
            case "WK": // MSSQL
            case "WW": // Oracle
            case "SUNDAY":
				LocalDate firstSun = LocalDate.parse(date.substring(0, 10)).with(TemporalAdjusters.previousOrSame(DayOfWeek.SUNDAY));
                return firstSun + referenceNonDate;
			case "J": // Oracle
			case "DD": // Oracle
			case "DDD": // Oracle
			case "DAYOFYEAR":
			case "DAY":
			case "DAYS": // DB2
                truncIx = 10;
                break;
            case "HH":
            case "HH12": // Oracle
            case "HH24": // Oracle
            case "HOUR":
			case "HOURS": // DB2
                truncIx = 13;
                break;
            case "N": // MSSQL
            case "MI": // MSSQL
            case "MINUTE":
            case "MINUTES": // DB2
                truncIx = 16;
                break;
            case "S":
            case "SS":
            case "SECOND":
            case "SECONDS": // DB2
                truncIx = 19;
                break;
            case "MS":
            case "MILLISECOND":
            case "MILLISECONDS": // DB2
                truncIx = 23;
                break;
            case "MCS":
            case "MICROSECOND":
            case "MICROSECONDS": // DB2
                truncIx = 26;
                break;
        }

        truncIx = Math.min(date.length(), truncIx);

        return date.substring(0, truncIx) + referenceDateTime.substring(Math.min(referenceLength, truncIx));
    }

	@TeiidFunction(category = FunctionCategoryConstants.SECURITY, nullOnNull = true)
	public static String aes_encrypt(String data, String key) throws FunctionExecutionException {
        try {
            return DVServices.call(EncryptionsIntf.class, s -> s.aesEncrypt(data, key));
        } catch (Throwable e) {
            throw new FunctionExecutionException(e);
        }
    }

	@TeiidFunction(category = FunctionCategoryConstants.SECURITY, nullOnNull = true)
	public static String aes_decrypt(String data, String key) throws FunctionExecutionException {
		try {
			return DVServices.call(EncryptionsIntf.class, s -> s.aesDecrypt(data, key));
		} catch (Throwable e) {
			throw new FunctionExecutionException(e);
		}
	}

	@TeiidFunction(category = FunctionCategoryConstants.SECURITY)
	public static String aes_encrypt(String data, String key, BinaryType iv, String salt, Integer iterationCount) throws FunctionExecutionException {
        try {
            return DVServices.call(EncryptionsIntf.class, s -> s.aesEncryptWoSignature(data, key, iv, salt, iterationCount));
        } catch (Throwable e) {
            throw new FunctionExecutionException(e);
        }
    }

	@TeiidFunction(category = FunctionCategoryConstants.SECURITY)
	public static String aes_decrypt(String data, String key, BinaryType iv, String salt, Integer iterationCount) throws FunctionExecutionException {
		try {
			return DVServices.call(EncryptionsIntf.class, s -> s.aesDecryptWoSignature(data, key, iv, salt, iterationCount));
		} catch (Throwable e) {
			throw new FunctionExecutionException(e);
		}
	}

	private static boolean isDelimiter(int ch) {
		int type = Character.getType(ch);

		return type == Character.SPACE_SEPARATOR ||
				type == Character.LINE_SEPARATOR ||
				type == Character.PARAGRAPH_SEPARATOR ||
				type == Character.DASH_PUNCTUATION ||
				type == Character.START_PUNCTUATION ||
				type == Character.END_PUNCTUATION ||
				type == Character.CONNECTOR_PUNCTUATION ||
				type == Character.OTHER_PUNCTUATION ||
				type == Character.MATH_SYMBOL ||
				type == Character.CURRENCY_SYMBOL ||
				type == Character.MODIFIER_SYMBOL ||
				type == Character.OTHER_SYMBOL ||

				ch == '\n' || ch == '\r' || ch == '\t';
	}

	public static final String COSINE = "cosine";
	public static final String JAROWINKLER = "jarowinkler";
	public static final String LEVENSHTEIN = "levenshtein";

	@TeiidFunction(category=FunctionCategoryConstants.STRING)
	public static double similarity(String value1, String value2) throws FunctionExecutionException {
		return similarity(value1, value2, null);
	}

	@TeiidFunction(category=FunctionCategoryConstants.STRING)
	public static double similarity(String value1, String value2, String algorithm) throws FunctionExecutionException {
		if (value1 == null || value2 == null)
			throw new FunctionExecutionException(QueryPlugin.Event.DVCORE8780, QueryPlugin.Util.gs(QueryPlugin.Event.DVCORE8780));
		if (algorithm == null)
			algorithm = LEVENSHTEIN;
		algorithm = algorithm.trim().toLowerCase();
        return switch (algorithm) {
			case COSINE -> 1.0 - new CosineDistance().apply(value1, value2);
			case JAROWINKLER -> new JaroWinklerSimilarity().apply(value1, value2);
			default -> {
				double distance = new LevenshteinDistance(null).apply(value1, value2);
				final int max = Math.max(value1.length(), value2.length());
				if (max == 0)
					yield 1.0;
				yield 1.0 - (distance / max);
			}
        };
	}
}
