# Copyright (c) 2020-2021, NVIDIA CORPORATION.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

from conftest import is_databricks_runtime
from spark_session import with_spark_session
import pytest

SELECT_SQL = [
# (" FUNCTIONAL CHECKING", "FUNCTIONAL CHECKING"),
# (" MATH functions", "MATH functions"),
("SELECT abs(intF*(-1)) FROM test_table", "abs(intF*(-1))"),
("SELECT abs(floatF*(-1)) FROM test_table", "abs(floatF*(-1))"),
("SELECT abs(doubleF*(-1)) FROM test_table", "abs(doubleF*(-1))"),
("SELECT acosh(intF) FROM test_table", "acosh(intF)"),
("SELECT acosh(floatF) FROM test_table", "acosh(floatF)"),
("SELECT acosh(doubleF) FROM test_table", "acosh(doubleF)"),
("SELECT asin(intF) FROM test_table", "asin(intF)"),
("SELECT asin(floatF) FROM test_table", "asin(floatF)"),
("SELECT asin(doubleF) FROM test_table", "asin(doubleF)"),
("SELECT asinh(intF*(-1)) FROM test_table", "asinh(intF*(-1))"),
("SELECT asinh(doubleF) FROM test_table", "asinh(doubleF)"),
("SELECT asinh(floatF) FROM test_table", "asinh(floatF)"),
("SELECT atan(byteF) FROM test_table", "atan(byteF)"),
("SELECT atan(shortF) FROM test_table", "atan(shortF)"),
("SELECT atan(intF) FROM test_table", "atan(intF)"),
("SELECT atan(longF) FROM test_table", "atan(longF)"),
("SELECT atan(floatF) FROM test_table", "atan(floatF)"),
("SELECT atan(doubleF) FROM test_table", "atan(doubleF)"),
("SELECT atanh(intF) FROM test_table", "atanh(intF)"),
("SELECT atanh(byteF) FROM test_table", "atanh(byteF)"),
("SELECT atanh(shortF) FROM test_table", "atanh(shortF)"),
("SELECT atanh(longF) FROM test_table", "atanh(longF)"),
("SELECT atanh(floatF) FROM test_table", "atanh(floatF)"),
("SELECT atanh(doubleF) FROM test_table", "atanh(doubleF)"),
("SELECT cbrt(intF) FROM test_table", "cbrt(intF)"),
("SELECT cbrt(byteF) FROM test_table", "cbrt(byteF)"),
("SELECT cbrt(shortF) FROM test_table", "cbrt(shortF)"),
("SELECT cbrt(longF) FROM test_table", "cbrt(longF)"),
("SELECT cbrt(floatF) FROM test_table", "cbrt(floatF)"),
("SELECT cbrt(doubleF) FROM test_table", "cbrt(doubleF)"),
("SELECT ceil(intF) FROM test_table ", "ceil(intF)"),
("SELECT ceil(byteF) FROM test_table ", "ceil(byteF)"),
("SELECT ceil(shortF) FROM test_table  ", "ceil(shortF)"),
("SELECT ceil(longF) FROM test_table ", "ceil(longF)"),
("SELECT ceil(floatF) FROM test_table ", "ceil(floatF)"),
("SELECT ceil(doubleF) FROM test_table  ", "ceil(doubleF)"),
("SELECT cos(intF) FROM test_table", "cos(intF)"),
("SELECT cos(byteF) FROM test_table", "cos(byteF)"),
("SELECT cos(shortF) FROM test_table", "cos(shortF)"),
("SELECT cos(longF) FROM test_table", "cos(longF)"),
("SELECT cos(floatF) FROM test_table", "cos(floatF)"),
("SELECT cos(doubleF) FROM test_table", "cos(doubleF)"),
("SELECT cot(byteF) FROM test_table", "cot(byteF)"),
("SELECT cot(shortF) FROM test_table", "cot(shortF)"),
("SELECT cot(intF) FROM test_table", "cot(intF)"),
("SELECT cot(longF) FROM test_table", "cot(longF)"),
("SELECT cot(floatF) FROM test_table", "cot(floatF)"),
("SELECT cot(doubleF) FROM test_table", "cot(doubleF)"),
("SELECT cot(byteF) FROM test_table", "cot(byteF)"),
("SELECT cot(shortF) FROM test_table", "cot(shortF)"),
("SELECT cot(intF) FROM test_table", "cot(intF)"),
("SELECT cot(longF) FROM test_table", "cot(longF)"),
("SELECT cot(floatF) FROM test_table", "cot(floatF)"),
("SELECT cot(doubleF) FROM test_table", "cot(doubleF)"),
("SELECT e()*doubleF FROM test_table", "e()*doubleF"),
("SELECT exp(intF) FROM test_table", "exp(intF)"),
("SELECT exp(byteF) FROM test_table", "exp(byteF)"),
("SELECT exp(shortF) FROM test_table", "exp(shortF)"),
("SELECT exp(longF) FROM test_table", "exp(longF)"),
("SELECT exp(floatF) FROM test_table", "exp(floatF)"),
("SELECT exp(doubleF) FROM test_table", "exp(doubleF)"),
("SELECT expm1(intF) FROM test_table", "expm1(intF)"),
("SELECT expm1(byteF) FROM test_table", "expm1(byteF)"),
("SELECT expm1(shortF) FROM test_table", "expm1(shortF)"),
("SELECT expm1(longF) FROM test_table", "expm1(longF)"),
("SELECT expm1(floatF) FROM test_table", "expm1(floatF)"),
("SELECT expm1(doubleF) FROM test_table", "expm1(doubleF)"),
("SELECT floor(intF) FROM test_table", "floor(intF)"),
("SELECT floor(byteF) FROM test_table", "floor(byteF)"),
("SELECT floor(shortF) FROM test_table", "floor(shortF)"),
("SELECT floor(longF) FROM test_table", "floor(longF)"),
("SELECT floor(floatF) FROM test_table", "floor(floatF)"),
("SELECT floor(doubleF) FROM test_table", "floor(doubleF)"),
("SELECT log2(intF) FROM test_table", "log2(intF)"),
("SELECT log2(shortF) FROM test_table", "log2(shortF)"),
("SELECT log2(byteF) FROM test_table", "log2(byteF)"),
("SELECT log2(longF) FROM test_table", "log2(longF)"),
("SELECT log2(floatF) FROM test_table", "log2(floatF)"),
("SELECT log2(doubleF) FROM test_table", "log2(doubleF)"),
("SELECT log10(intF) FROM test_table", "log10(intF)"),
("SELECT log10(byteF) FROM test_table", "log10(byteF)"),
("SELECT log10(shortF) FROM test_table", "log10(shortF)"),
("SELECT log10(longF) FROM test_table", "log10(longF)"),
("SELECT log10(floatF) FROM test_table", "log10(floatF)"),
("SELECT log10(doubleF) FROM test_table", "log10(doubleF)"),
("SELECT log1p(intF) FROM test_table", "log1p(intF)"),
("SELECT log1p(byteF) FROM test_table", "log1p(byteF)"),
("SELECT log1p(shortF) FROM test_table", "log1p(shortF)"),
("SELECT log1p(longF) FROM test_table", "log1p(longF)"),
("SELECT log1p(floatF) FROM test_table", "log1p(floatF)"),
("SELECT log1p(doubleF) FROM test_table", "log1p(doubleF)"),
("SELECT log(intF, intF) FROM test_table", "log(intF, intF)"),
("SELECT log(byteF, byteF) FROM test_table", "log(byteF, byteF)"),
("SELECT log(shortF, shortF) FROM test_table", "log(shortF, shortF)"),
("SELECT log(longF, longF) FROM test_table", "log(longF, longF)"),
("SELECT log(floatF, floatF) FROM test_table", "log(floatF, floatF)"),
("SELECT log(doubleF, doubleF) FROM test_table", "log(doubleF, doubleF)"),
("SELECT MOD(intF,10) FROM test_table", "MOD(intF,10)"),
("SELECT MOD(byteF,10) FROM test_table", "MOD(byteF,10)"),
("SELECT MOD(shortF,10) FROM test_table", "MOD(shortF,10)"),
("SELECT MOD(longF,10) FROM test_table", "MOD(longF,10)"),
("SELECT MOD(floatF,10) FROM test_table", "MOD(floatF,10)"),
("SELECT MOD(doubleF,10) FROM test_table", "MOD(doubleF,10)"),
("SELECT pi()*intF FROM test_table", "pi()*intF"),
("SELECT pi()*floatF*10 FROM test_table", "pi()*floatF*10"),
("SELECT pow(intF,2) FROM test_table", "pow(intF,2)"),
("SELECT pow(byteF,2) FROM test_table", "pow(byteF,2)"),
("SELECT pow(shortF,2) FROM test_table", "pow(shortF,2)"),
("SELECT pow(longF,2) FROM test_table", "pow(longF,2)"),
("SELECT pow(floatF,2) FROM test_table", "pow(floatF,2)"),
("SELECT pow(doubleF,2) FROM test_table", "pow(doubleF,2)"),
("SELECT rint(intF) FROM test_table", "rint(intF)"),
("SELECT rint(byteF) FROM test_table", "rint(byteF)"),
("SELECT rint(shortF) FROM test_table", "rint(shortF)"),
("SELECT rint(longF) FROM test_table", "rint(longF)"),
("SELECT rint(floatF) FROM test_table", "rint(floatF)"),
("SELECT rint(doubleF) FROM test_table", "rint(doubleF)"),
("SELECT signum(intF) FROM test_table", "signum(intF)"),
("SELECT signum(byteF) FROM test_table", "signum(byteF)"),
("SELECT signum(shortF) FROM test_table", "signum(shortF)"),
("SELECT signum(longF) FROM test_table", "signum(longF)"),
("SELECT signum(floatF) FROM test_table", "signum(floatF)"),
("SELECT signum(doubleF) FROM test_table", "signum(doubleF)"),
("SELECT sin(intF) FROM test_table", "sin(intF)"),
("SELECT sin(byteF) FROM test_table", "sin(byteF)"),
("SELECT sin(shortF) FROM test_table", "sin(shortF)"),
("SELECT sin(longF) FROM test_table", "sin(longF)"),
("SELECT sin(floatF) FROM test_table", "sin(floatF)"),
("SELECT sin(doubleF) FROM test_table", "sin(doubleF)"),
("SELECT tan(intF) FROM test_table", "tan(intF)"),
("SELECT tan(byteF) FROM test_table", "tan(byteF)"),
("SELECT tan(shortF) FROM test_table", "tan(shortF)"),
("SELECT tan(longF) FROM test_table", "tan(longF)"),
("SELECT tan(floatF) FROM test_table", "tan(floatF)"),
("SELECT tan(doubleF) FROM test_table", "tan(doubleF)"),
("SELECT intF+intF FROM test_table", "intF+intF"),
("SELECT byteF+byteF FROM test_table", "byteF+byteF"),
("SELECT shortF+shortF FROM test_table", "shortF+shortF"),
("SELECT longF+longF FROM test_table", "longF+longF"),
("SELECT floatF+floatF FROM test_table", "floatF+floatF"),
("SELECT doubleF+doubleF FROM test_table", "doubleF+doubleF"),
("SELECT byteF-doubleF FROM test_table", "byteF-doubleF"),
("SELECT shortF-byteF FROM test_table", "shortF-byteF"),
("SELECT intF-byteF FROM test_table", "intF-byteF"),
("SELECT longF-byteF FROM test_table", "longF-byteF"),
("SELECT floatF-intF FROM test_table", "floatF-intF"),
("SELECT doubleF-floatF FROM test_table", "doubleF-floatF"),
("SELECT intF*intF FROM test_table", "intF*intF"),
("SELECT byteF*byteF FROM test_table", "byteF*byteF"),
("SELECT shortF*shortF FROM test_table", "shortF*shortF"),
("SELECT longF*longF FROM test_table", "longF*longF"),
("SELECT floatF*floatF FROM test_table", "floatF*floatF"),
("SELECT doubleF*doubleF FROM test_table", "doubleF*doubleF"),
("SELECT intF/intF FROM test_table", "intF/intF"),
("SELECT byteF/floatF FROM test_table", "byteF/floatF"),
("SELECT shortF/doubleF FROM test_table", "shortF/doubleF"),
("SELECT longF/intF FROM test_table", "longF/intF"),
("SELECT floatF/floatF FROM test_table", "floatF/floatF"),
("SELECT doubleF/doubleF FROM test_table", "doubleF/doubleF"),
("SELECT intF%floatF FROM test_table", "intF%floatF"),
("SELECT byteF%10 FROM test_table", "byteF%10"),
("SELECT shortF%10 FROM test_table", "shortF%10"),
("SELECT longF%floatF FROM test_table", "longF%floatF"),
("SELECT floatF%10 FROM test_table", "floatF%10"),
("SELECT doubleF%10 FROM test_table", "doubleF%10"),
# ("STRING", "STRING"),
("SELECT Lower(strF) FROM test_table", "Lower(strF)"),
("SELECT Upper(strF) FROM test_table", "Upper(strF)"),
("SELECT Substring(strF, 1, 3) FROM test_table", "Substring(strF, 1, 3)"),
("SELECT Substring(strF, -1, 5) FROM test_table", "Substring(strF, -1, 5)"),
("SELECT * FROM test_table WHERE strF LIKE 'Yuan' ", "* WHERE strF LIKE 'Yuan'"),
("SELECT * FROM test_table WHERE strF LIKE '%Yuan%' ", "* WHERE strF LIKE '%Yuan%'"),
("SELECT * FROM test_table WHERE strF LIKE 'Y%'", "* WHERE strF LIKE 'Y%'"),
("SELECT * FROM test_table WHERE strF LIKE '%an' ", "* WHERE strF LIKE '%an'"),
("SELECT REPLACE(strF, 'Yuan', 'Eric') FROM test_table", "REPLACE(strF, 'Yuan', 'Eric')"),
#("SELECT REGEXP_REPLACE(strF, 'Y*', 'Eric') FROM test_table", "REGEXP_REPLACE(strF, 'Y*', 'Eric')"),
("SELECT CONCAT(strF, strF) FROM test_table", "CONCAT(strF, strF)"),
# (" DATETIME", "DATETIME"),
("SELECT dayofmonth(timestampF) from test_table", "dayofmonth(timestampF)"),
("SELECT hour(timestampF) from test_table", "hour(timestampF)"),
("SELECT minute(timestampF) from test_table", "minute(timestampF)"),
("SELECT second(timestampF) from test_table", "second(timestampF)"),
("SELECT year(timestampF) from test_table", "year(timestampF)"),
("SELECT month(timestampF) from test_table", "month(timestampF)"),
# (" CAST", "CAST"),
("SELECT cast(intF as tinyint) from test_table", "cast(intF as tinyint)"),
("SELECT cast(intF as smallint) from test_table", "cast(intF as smallint)"),
("SELECT cast(intF as bigint) from test_table", "cast(intF as bigint)"),
("SELECT cast(intF as long) from test_table", "cast(intF as long)"),
("SELECT cast(intF as float) from test_table", "cast(intF as float)"),
("SELECT cast(intF as double) from test_table", "cast(intF as double)"),
("SELECT cast(byteF as tinyint) from test_table", "cast(byteF as tinyint)"),
("SELECT cast(byteF as smallint) from test_table", "cast(byteF as smallint)"),
("SELECT cast(byteF as Bigint) from test_table", "cast(byteF as Bigint)"),
("SELECT cast(byteF as long) from test_table", "cast(byteF as long)"),
("SELECT cast(byteF as float) from test_table", "cast(byteF as float)"),
("SELECT cast(byteF as double) from test_table", "cast(byteF as double)"),
("SELECT cast(shortF as tinyint) from test_table", "cast(shortF as tinyint)"),
("SELECT cast(shortF as smallint) from test_table", "cast(shortF as smallint)"),
("SELECT cast(shortF as Bigint) from test_table", "cast(shortF as Bigint)"),
("SELECT cast(shortF as long) from test_table", "cast(shortF as long)"),
("SELECT cast(shortF as float) from test_table", "cast(shortF as float)"),
("SELECT cast(shortF as double) from test_table", "cast(shortF as double)"),
("SELECT cast(longF as tinyint) from test_table", "cast(longF as tinyint)"),
("SELECT cast(longF as smallint) from test_table", "cast(longF as smallint)"),
("SELECT cast(longF as Bigint) from test_table", "cast(longF as Bigint)"),
("SELECT cast(longF as long) from test_table", "cast(longF as long)"),
("SELECT cast(longF as float) from test_table", "cast(longF as float)"),
("SELECT cast(longF as double) from test_table", "cast(longF as double)"),
("SELECT cast(floatF as tinyint) from test_table", "cast(floatF as tinyint)"),
("SELECT cast(floatF as smallint) from test_table", "cast(floatF as smallint)"),
("SELECT cast(floatF as Bigint) from test_table", "cast(floatF as Bigint)"),
("SELECT cast(floatF as long) from test_table", "cast(floatF as long)"),
("SELECT cast(floatF as float) from test_table", "cast(floatF as float)"),
("SELECT cast(floatF as double) from test_table", "cast(floatF as double)"),
("SELECT cast(doubleF as tinyint) from test_table", "cast(doubleF as tinyint)"),
("SELECT cast(doubleF as smallint) from test_table", "cast(doubleF as smallint)"),
("SELECT cast(doubleF as Bigint) from test_table", "cast(doubleF as Bigint)"),
("SELECT cast(doubleF as long) from test_table", "cast(doubleF as long)"),
("SELECT cast(doubleF as float) from test_table", "cast(doubleF as float)"),
("SELECT cast(doubleF as double) from test_table", "cast(doubleF as double)"),
# (" COMPARISON", "COMPARISON"),
("SELECT * FROM test_table WHERE intF = 0", "* WHERE intF = 0"),
("SELECT * FROM test_table WHERE byteF = 0", "* WHERE byteF = 0"),
("SELECT * FROM test_table WHERE shortF = 0", "* WHERE shortF = 0"),
("SELECT * FROM test_table WHERE longF = 0", "* WHERE longF = 0"),
("SELECT * FROM test_table WHERE floatF = 0", "* WHERE floatF = 0"),
("SELECT * FROM test_table WHERE doubleF = 0", "* WHERE doubleF = 0"),
("SELECT * FROM test_table WHERE booleanF = true", "* WHERE booleanF = true"),
("SELECT * FROM test_table WHERE strF = 'Alex'", "* WHERE strF = 'Alex'"),
("SELECT * FROM test_table WHERE dateF =  '1990-1-1'", "* WHERE dateF = '1990-1-1'"),
("SELECT * FROM test_table WHERE dateF =  '2020-05-01 12:01:01' ", "* WHERE dateF = '2020-05-01 12:01:01'"),
("SELECT * FROM test_table WHERE intF == 0", "* WHERE intF == 0"),
("SELECT * FROM test_table WHERE byteF == 0", "* WHERE byteF == 0"),
("SELECT * FROM test_table WHERE shortF == 0", "* WHERE shortF == 0"),
("SELECT * FROM test_table WHERE longF == 0", "* WHERE longF == 0"),
("SELECT * FROM test_table WHERE floatF == 0", "* WHERE floatF == 0"),
("SELECT * FROM test_table WHERE doubleF == 0", "* WHERE doubleF == 0"),
("SELECT * FROM test_table WHERE booleanF == true", "* WHERE booleanF == true"),
("SELECT * FROM test_table WHERE strF == 'Alex'", "* WHERE strF == 'Alex'"),
("SELECT * FROM test_table WHERE dateF ==  '1990-1-1'", "* WHERE dateF == '1990-1-1'"),
("SELECT * FROM test_table WHERE dateF ==  '2020-05-01 12:01:01' ", "* WHERE dateF == '2020-05-01 12:01:01'"),
("SELECT * FROM test_table WHERE intF > 0", "* WHERE intF > 0"),
("SELECT * FROM test_table WHERE byteF > 0", "* WHERE byteF > 0"),
("SELECT * FROM test_table WHERE shortF > 0", "* WHERE shortF > 0"),
("SELECT * FROM test_table WHERE longF > 0", "* WHERE longF > 0"),
("SELECT * FROM test_table WHERE floatF > 0", "* WHERE floatF > 0"),
("SELECT * FROM test_table WHERE doubleF > 0", "* WHERE doubleF > 0"),
("SELECT * FROM test_table WHERE booleanF > false ", "* WHERE booleanF > false"),
("SELECT * FROM test_table WHERE strF > 'Yuan'", "* WHERE strF > 'Yuan'"),
("SELECT * FROM test_table WHERE dateF > '1990-1-1' ", "* WHERE dateF > '1990-1-1'"),
("SELECT * FROM test_table WHERE dateF > '2020-05-01 12:01:01'", "* WHERE dateF > '2020-05-01 12:01:01'"),
("SELECT * FROM test_table WHERE intF < 0", "* WHERE intF < 0"),
("SELECT * FROM test_table WHERE byteF < 0", "* WHERE byteF < 0"),
("SELECT * FROM test_table WHERE shortF < 0", "* WHERE shortF < 0"),
("SELECT * FROM test_table WHERE longF < 0", "* WHERE longF < 0"),
("SELECT * FROM test_table WHERE floatF < 0", "* WHERE floatF < 0"),
("SELECT * FROM test_table WHERE doubleF < 0", "* WHERE doubleF < 0"),
("SELECT * FROM test_table WHERE booleanF < true", "* WHERE booleanF < true"),
("SELECT * FROM test_table WHERE strF < 'Yuan'", "* WHERE strF < 'Yuan'"),
("SELECT * FROM test_table WHERE dateF <  '1994-01-01' ", "* WHERE dateF < '1994-01-01'"),
("SELECT * FROM test_table WHERE dateF < '2020-05-01 12:01:01' ", "* WHERE dateF < '2020-05-01 12:01:01'"),
("SELECT * FROM test_table WHERE intF >=  0", "* WHERE intF >= 0"),
("SELECT * FROM test_table WHERE byteF >=  0", "* WHERE byteF >= 0"),
("SELECT * FROM test_table WHERE shortF >= 0", "* WHERE shortF >= 0"),
("SELECT * FROM test_table WHERE longF >=  0", "* WHERE longF >= 0"),
("SELECT * FROM test_table WHERE floatF >=  0", "* WHERE floatF >= 0"),
("SELECT * FROM test_table WHERE doubleF >= 0", "* WHERE doubleF >= 0"),
("SELECT * FROM test_table WHERE booleanF >= false ", "* WHERE booleanF >= false"),
("SELECT * FROM test_table WHERE strF >= 'Yuan'", "* WHERE strF >= 'Yuan'"),
("SELECT * FROM test_table WHERE dateF >=  '1994-01-01' ", "* WHERE dateF >= '1994-01-01'"),
("SELECT * FROM test_table WHERE dateF >= '2020-05-01 12:01:01' ", "* WHERE dateF >= '2020-05-01 12:01:01'"),
("SELECT * FROM test_table WHERE intF <= 0", "* WHERE intF <= 0"),
("SELECT * FROM test_table WHERE byteF <= 0", "* WHERE byteF <= 0"),
("SELECT * FROM test_table WHERE shortF <= 0", "* WHERE shortF <= 0"),
("SELECT * FROM test_table WHERE longF <= 0", "* WHERE longF <= 0"),
("SELECT * FROM test_table WHERE floatF <= 0", "* WHERE floatF <= 0"),
("SELECT * FROM test_table WHERE doubleF <= 0", "* WHERE doubleF <= 0"),
("SELECT * FROM test_table WHERE booleanF <= true ", "* WHERE booleanF <= true"),
("SELECT * FROM test_table WHERE strF <= 'Yuan'", "* WHERE strF <= 'Yuan'"),
("SELECT * FROM test_table WHERE dateF <=  '1994-01-01' ", "* WHERE dateF <= '1994-01-01'"),
("SELECT * FROM test_table WHERE dateF <= '2020-05-01 12:01:01' ", "* WHERE dateF <= '2020-05-01 12:01:01'"),
("SELECT * FROM test_table WHERE intF <> 0", "* WHERE intF <> 0"),
("SELECT * FROM test_table WHERE byteF <> 0", "* WHERE byteF <> 0"),
("SELECT * FROM test_table WHERE shortF <> 0", "* WHERE shortF <> 0"),
("SELECT * FROM test_table WHERE longF <> 0", "* WHERE longF <> 0"),
("SELECT * FROM test_table WHERE floatF <> 0", "* WHERE floatF <> 0"),
("SELECT * FROM test_table WHERE doubleF <> 0", "* WHERE doubleF <> 0"),
("SELECT * FROM test_table WHERE booleanF <> true  ", "* WHERE booleanF <> true"),
("SELECT * FROM test_table WHERE strF <> 'Yuan'", "* WHERE strF <> 'Yuan'"),
("SELECT * FROM test_table WHERE dateF <>  '1994-01-01' ", "* WHERE dateF <> '1994-01-01'"),
("SELECT * FROM test_table WHERE dateF <> '2020-05-01 12:01:01' ", "* WHERE dateF <> '2020-05-01 12:01:01'"),
("SELECT * FROM test_table WHERE intF != 0", "* WHERE intF != 0"),
("SELECT * FROM test_table WHERE byteF != 0", "* WHERE byteF != 0"),
("SELECT * FROM test_table WHERE shortF != 0", "* WHERE shortF != 0"),
("SELECT * FROM test_table WHERE longF != 0", "* WHERE longF != 0"),
("SELECT * FROM test_table WHERE floatF != 0", "* WHERE floatF != 0"),
("SELECT * FROM test_table WHERE doubleF != 0", "* WHERE doubleF != 0"),
("SELECT * FROM test_table WHERE booleanF != true  ", "* WHERE booleanF != true"),
("SELECT * FROM test_table WHERE strF != 'Yuan'", "* WHERE strF != 'Yuan'"),
("SELECT * FROM test_table WHERE dateF !=  '1994-01-01' ", "* WHERE dateF != '1994-01-01'"),
("SELECT * FROM test_table WHERE dateF != '2020-05-01 12:01:01' ", "* WHERE dateF != '2020-05-01 12:01:01'"),
("SELECT * FROM test_table WHERE strF= 'Yuan' AND intF=10", "* WHERE strF= 'Yuan' AND intF=10"),
("SELECT * FROM test_table WHERE strF= 'Yuan' OR intF=10", "* WHERE strF= 'Yuan' OR intF=10"),
("SELECT * FROM test_table WHERE NOT strF= 'Alex' ", "* WHERE NOT strF= 'Alex'"),
("SELECT * FROM test_table WHERE byteF IN (10, 100)", "* WHERE byteF IN (10, 100)"),
("SELECT * FROM test_table WHERE shortF IN (10, 100)", "* WHERE shortF IN (10, 100)"),
("SELECT * FROM test_table WHERE longF IN (10, 100)", "* WHERE longF IN (10.0, 100.0)"),
("SELECT * FROM test_table WHERE intF IN (10, 100)", "* WHERE intF IN (10, 100)"),
("SELECT * FROM test_table WHERE floatF IN (10.0, 100.0)", "* WHERE floatF IN (10.0, 100.0)"),
("SELECT * FROM test_table WHERE doubleF IN (10.0, 100.0)", "* WHERE doubleF IN (10.0, 100.0)"),
# (" BITWISE", "BITWISE"),
("SELECT intF & 3  FROM test_table", "intF & 3"),
("SELECT intF & intF  FROM test_table", "intF & intF"),
("SELECT intF | 5  FROM test_table", "intF | 5"),
("SELECT intF | intF  FROM test_table", "intF | intF"),
("SELECT ~intF FROM test_table", "~intF"),
("SELECT intF^intF FROM test_table", "intF^intF"),
("SELECT shiftleft(intF, 1) FROM test_table", "shiftleft(intF, 1)"),
("SELECT shiftright(intF, 1) FROM test_table", "shiftright(intF, 1)"),
("SELECT shiftrightunsigned(intF, 1) FROM test_table", "shiftrightunsigned(intF, 1)"),
("SELECT shortF & 3  FROM test_table", "shortF & 3"),
("SELECT shortF & shortF  FROM test_table", "shortF & shortF"),
("SELECT shortF | shortF  FROM test_table", "shortF | shortF"),
("SELECT ~shortF FROM test_table", "~shortF"),
("SELECT shortF^shortF FROM test_table", "shortF^shortF"),
("SELECT shiftleft(shortF, 1) FROM test_table", "shiftleft(shortF, 1)"),
("SELECT shiftright(shortF, 1) FROM test_table", "shiftright(shortF, 1)"),
("SELECT shiftrightunsigned(shortF, 1) FROM test_table", "shiftrightunsigned(shortF, 1)"),
("SELECT byteF & 3  FROM test_table", "byteF & 3"),
("SELECT byteF & byteF  FROM test_table", "byteF & byteF"),
("SELECT byteF | 5  FROM test_table", "byteF | 5"),
("SELECT byteF | byteF  FROM test_table", "byteF | byteF"),
("SELECT ~byteF FROM test_table", "~byteF"),
("SELECT byteF^byteF FROM test_table", "byteF^byteF"),
("SELECT shiftleft(byteF, 1) FROM test_table", "shiftleft(byteF, 1)"),
("SELECT shiftright(byteF, 1) FROM test_table", "shiftright(byteF, 1)"),
("SELECT  shiftrightunsigned(byteF, 1) FROM test_table", "shiftrightunsigned(byteF, 1)"),
("SELECT longF & 3  FROM test_table", "longF & 3"),
("SELECT longF & longF  FROM test_table", "longF & longF"),
("SELECT longF | 5  FROM test_table", "longF | 5"),
("SELECT ~longF FROM test_table", "~longF"),
("SELECT longF^longF FROM test_table", "longF^longF"),
("SELECT shiftleft(longF, 1) FROM test_table", "shiftleft(longF, 1)"),
("SELECT shiftright(longF, 1) FROM test_table", "shiftright(longF, 1)"),
("SELECT shiftrightunsigned(longF, 1) FROM test_table", "shiftrightunsigned(longF, 1)"),
# (" OTHER", "OTHER"),
# ("MULTIPLE CASES ", "MULTIPLE CASES"),
("SELECT CASE WHEN byteF > 5 THEN 5*byteF WHEN intF > 5 THEN 5*shortF ELSE doubleF/2 END FROM test_table", "CASE WHEN byteF > 5 THEN 5*byteF WHEN intF > 5 THEN 5*shortF ELSE doubleF/2 END"),
# ("IF", "IF"),
("SELECT intF, IF (intF > 100, 10, 5) FROM test_table", "intF, IF (intF > 100, 10, 5)"),
("SELECT byteF, IF (byteF > 100, 10, 5) FROM test_table", "byteF, IF (byteF > 100, 10, 5)"),
("SELECT shortF, IF (shortF > 100, 10, 5) FROM test_table", "shortF, IF (shortF > 100, 10, 5)"),
("SELECT longF, IF (longF > 100, 10, 5) FROM test_table", "longF, IF (longF > 100, 10, 5)"),
("SELECT floatF, IF (floatF > 100.0, 10, 5) as if_float FROM test_table", "floatF, IF (floatF > 100.0, 10, 5)"),
("SELECT doubleF, IF (longF > 100.0, 10, 5) FROM test_table", "doubleF, IF (longF > 100.0, 10, 5)"),
("SELECT booleanF, IF (booleanF = True, False, True) FROM test_table", "booleanF, IF (booleanF = True, False, True)"),
("SELECT strF, IF (strF = 'Alex', 10, 5) FROM test_table", "strF, IF (strF = 'Alex', 10, 5)"),
("SELECT dateF, IF (dateF > '1994-01-01', 1990, 1980) FROM test_table", "dateF, IF (dateF > '1994-01-01', 1990, 1980)"),
("SELECT dateF, IF (dateF > '2020-05-01 12:01:015', 1990, 1980) FROM test_table", "dateF, IF (dateF > '2020-05-01 12:01:015', 1990, 1980)"),
# ("ISNAN", "ISNAN"),
("SELECT ISNAN(intF/byteF) FROM test_table", "ISNAN(intF/byteF)"),
("SELECT ISNAN(byteF/byteF) FROM test_table", "ISNAN(byteF/byteF)"),
("SELECT ISNAN(shortF/byteF) FROM test_table", "ISNAN(shortF/byteF)"),
("SELECT ISNAN(longF/byteF) FROM test_table", "ISNAN(longF/byteF)"),
("SELECT ISNAN(floatF/byteF) FROM test_table", "ISNAN(floatF/byteF)"),
("SELECT ISNAN(doubleF/byteF) FROM test_table", "ISNAN(doubleF/byteF)"),
("SELECT ISNAN(strF/strF) FROM test_table", "ISNAN(strF/strF)"),
("SELECT NANVL(byteF/byteF, 0) FROM test_table", "NANVL(byteF/byteF, 0)"),
("SELECT NANVL(shortF/byteF, 0) FROM test_table", "NANVL(shortF/byteF, 0)"),
("SELECT NANVL(intF/byteF, 0) FROM test_table", "NANVL(intF/byteF, 0)"),
("SELECT NANVL(longF/byteF, 0) FROM test_table", "NANVL(longF/byteF, 0)"),
("SELECT NANVL(floatF/byteF, 0) FROM test_table", "NANVL(floatF/byteF, 0)"),
("SELECT NANVL(doubleF/byteF, 0) FROM test_table", "NANVL(doubleF/byteF, 0)"),
("SELECT NANVL(strF/byteF, 0) FROM test_table", "NANVL(strF/byteF, 0)"),
("SELECT IFNULL(byteF, 'nobody') as if_null FROM test_table", "IFNULL(byteF, 'nobody')"),
("SELECT IFNULL(shortF, 'nobody') as if_null FROM test_table", "IFNULL(shortF, 'nobody')"),
("SELECT IFNULL(intF, 'nobody') as if_null FROM test_table", "IFNULL(intF, 'nobody')"),
("SELECT IFNULL(longF, 'nobody') as if_null FROM test_table", "IFNULL(longF, 'nobody')"),
("SELECT IFNULL(doubleF, 'nobody') as if_null FROM test_table", "IFNULL(doubleF, 'nobody')"),
("SELECT IFNULL(booleanF, True) as if_null FROM test_table", "IFNULL(booleanF, True)"),
("SELECT IFNULL(strF, 'nobody') as if_null FROM test_table", "IFNULL(strF, 'nobody')"),
("SELECT IFNULL(dateF, 'nobody') as if_null FROM test_table", "IFNULL(dateF, 'nobody')"),
("SELECT IFNULL(timestampF, 'nobody') as if_null FROM test_table", "IFNULL(timestampF, 'nobody')"),
("SELECT ISNULL(byteF) FROM test_table", "ISNULL(byteF)"),
("SELECT ISNULL(shortF) FROM test_table", "ISNULL(shortF)"),
("SELECT ISNULL(intF) FROM test_table", "ISNULL(intF)"),
("SELECT ISNULL(longF) FROM test_table", "ISNULL(longF)"),
("SELECT ISNULL(floatF ) FROM test_table", "ISNULL(floatF )"),
("SELECT ISNULL(doubleF) FROM test_table", "ISNULL(doubleF)"),
("SELECT ISNULL(booleanF) FROM test_table", "ISNULL(booleanF)"),
("SELECT ISNULL(strF) FROM test_table", "ISNULL(strF)"),
("SELECT ISNULL(dateF) FROM test_table", "ISNULL(dateF)"),
("SELECT ISNULL(timestampF) FROM test_table", "ISNULL(timestampF)"),
("SELECT ISNOTNULL(byteF) FROM test_table", "ISNOTNULL(byteF)"),
("SELECT ISNOTNULL(shortF) FROM test_table", "ISNOTNULL(shortF)"),
("SELECT ISNOTNULL(intF) FROM test_table", "ISNOTNULL(intF)"),
("SELECT ISNOTNULL(longF) FROM test_table", "ISNOTNULL(longF)"),
("SELECT ISNOTNULL(floatF) FROM test_table", "ISNOTNULL(floatF)"),
("SELECT ISNOTNULL(doubleF) FROM test_table", "ISNOTNULL(doubleF)"),
("SELECT ISNOTNULL(booleanF) FROM test_table", "ISNOTNULL(booleanF)"),
("SELECT ISNOTNULL(strF) FROM test_table", "ISNOTNULL(strF)"),
("SELECT ISNOTNULL(dateF) FROM test_table", "ISNOTNULL(dateF)"),
("SELECT ISNOTNULL(timestampF) FROM test_table", "ISNOTNULL(timestampF)"),
("SELECT NULLIF(intF, 0) as null_if FROM test_table", "NULLIF(intF, 0)"),
("SELECT NULLIF(byteF, 0) as null_if FROM test_table", "NULLIF(byteF, 0)"),
("SELECT NULLIF(shortF,0) as null_if FROM test_table", "NULLIF(shortF,0)"),
("SELECT NULLIF(intF, 0) as null_if FROM test_table", "NULLIF(intF, 0)"),
("SELECT NULLIF(longF, 0) as null_if FROM test_table", "NULLIF(longF, 0)"),
("SELECT NULLIF(floatF,0) as null_if FROM test_table", "NULLIF(floatF,0)"),
("SELECT NULLIF(doubleF, 0) as null_if FROM test_table", "NULLIF(doubleF, 0)"),
("SELECT NULLIF(booleanF, True) as null_if FROM test_table", "NULLIF(booleanF, True)"),
("SELECT NULLIF(dateF, '1990-1-1') as null_if FROM test_table", "NULLIF(dateF, '1990-1-1')"),
("SELECT NULLIF(dateF, '2022-12-01 12:01:01') as null_if FROM test_table", "NULLIF(dateF, '2022-12-01 12:01:01')"),
("SELECT NVL(byteF, 0) as nvl_value FROM test_table", "NVL(byteF, 0)"),
("SELECT NVL(shortF, 0) as nvl_value FROM test_table", "NVL(shortF, 0)"),
("SELECT NVL(intF, 0) as nvl_value FROM test_table", "NVL(intF, 0)"),
("SELECT NVL(longF, 0) as nvl_value FROM test_table", "NVL(longF, 0)"),
("SELECT NVL(floatF, 0) as nvl_value FROM test_table", "NVL(floatF, 0)"),
("SELECT NVL(doubleF, 0) as nvl_value FROM test_table", "NVL(doubleF, 0)"),
("SELECT NVL(booleanF, TRUE) as nvl_value FROM test_table", "NVL(booleanF, TRUE)"),
("SELECT NVL(strF, 0) as nvl_value FROM test_table", "NVL(strF, 0)"),
("SELECT NVL(dateF, '1990-1-1') as nvl_value FROM test_table", "NVL(dateF, '1990-1-1')"),
("SELECT NVL(timestampF, '2022-12-01 12:01:01') as nvl_value FROM test_table", "NVL(timestampF, '2022-12-01 12:01:01')"),
("SELECT NVL2(byteF, 'not null value','null value') as nvl2_value FROM test_table", "NVL2(byteF, 'not null value','null value')"),
("SELECT NVL2(shortF, 'not null value','null value' ) as nvl2_value FROM test_table", "NVL2(shortF, 'not null value','null value' )"),
("SELECT NVL2(intF, 'not null value','null value' ) as nvl2_value FROM test_table", "NVL2(intF, 'not null value','null value' )"),
("SELECT NVL2(longF, 'not null value','null value' ) as nvl2_value FROM test_table", "NVL2(longF, 'not null value','null value' )"),
("SELECT NVL2(floatF, 'not null value','null value' ) as nvl2_value FROM test_table", "NVL2(floatF, 'not null value','null value' )"),
("SELECT NVL2(doubleF, 'not null value','null value' ) as nvl2_value FROM test_table", "NVL2(doubleF, 'not null value','null value' )"),
("SELECT NVL2(booleanF, 'not null value','null value') as nvl2_value FROM test_table", "NVL2(booleanF, 'not null value','null value')"),
("SELECT NVL2(strF, 'not null value','null value' ) as nvl2_value FROM test_table", "NVL2(strF, 'not null value','null value' )"),
("SELECT NVL2(dateF, 'not null value','null value' ) as nvl2_value FROM test_table", "NVL2(dateF, 'not null value','null value' )"),
("SELECT NVL2(timestampF, 'not null value','null value') as nvl2_value FROM test_table", "NVL2(timestampF, 'not null value','null value')"),
# (" SQL FILTER", "SQL FILTER"),
("SELECT byteF FROM test_table WHERE byteF = 20", "byteF WHERE byteF = 20"),
("SELECT shortF FROM test_table WHERE shortF = 20", "shortF WHERE shortF = 20"),
("SELECT intF FROM test_table WHERE intF = 20", "intF WHERE intF = 20"),
("SELECT longF FROM test_table WHERE longF = 20", "longF WHERE longF = 20"),
("SELECT floatF FROM test_table WHERE floatF >20", "floatF WHERE floatF >20"),
("SELECT doubleF FROM test_table WHERE doubleF > 20", "doubleF WHERE doubleF > 20"),
("SELECT booleanF FROM test_table WHERE booleanF = True", "booleanF WHERE booleanF = True"),
("SELECT dateF FROM test_table WHERE dateF = '1990-1-1' ", "dateF WHERE dateF = '1990-1-1'"),
("SELECT timestampF FROM test_table WHERE timestampF > '2020-02-01 12:01:01' ", "timestampF WHERE timestampF > '2020-02-01 12:01:01'"),
("SELECT byteF FROM test_table WHERE byteF BETWEEN 10 AND 30", "byteF WHERE byteF BETWEEN 10 AND 30"),
("SELECT shortF FROM test_table WHERE shortF BETWEEN 10 AND 30", "shortF WHERE shortF BETWEEN 10 AND 30"),
("SELECT intF FROM test_table WHERE intF BETWEEN 10 AND 30", "intF WHERE intF BETWEEN 10 AND 30"),
("SELECT longF FROM test_table WHERE longF BETWEEN 10 AND 30", "longF WHERE longF BETWEEN 10 AND 30"),
("SELECT floatF FROM test_table WHERE floatF BETWEEN 10 AND 30", "floatF WHERE floatF BETWEEN 10 AND 30"),
("SELECT doubleF FROM test_table WHERE doubleF BETWEEN 10 AND 30", "doubleF WHERE doubleF BETWEEN 10 AND 30"),
("SELECT dateF FROM test_table WHERE dateF BETWEEN '1995-01-01' AND '1990-02-01' ", "dateF WHERE dateF BETWEEN '1995-01-01' AND '1990-02-01'"),
("SELECT timestampF FROM test_table WHERE timestampF  BETWEEN '2020-02-01 12:01:01' AND '2022-12-01 12:01:01' ", "timestampF WHERE timestampF BETWEEN '2020-02-01 12:01:01' AND '2022-12-01 12:01:01'"),
# ("coalesce", "coalesce"),
("SELECT byteF, COALESCE(byteF,'N/A') FROM test_table", "byteF, COALESCE(byteF,'N/A')"),
("SELECT shortF, COALESCE(shortF,'N/A') FROM test_table", "shortF, COALESCE(shortF,'N/A')"),
("SELECT intF, COALESCE(intF,'N/A') FROM test_table", "intF, COALESCE(intF,'N/A')"),
("SELECT longF, COALESCE(longF,'N/A') FROM test_table", "longF, COALESCE(longF,'N/A')"),
("SELECT doubleF, COALESCE(doubleF,'N/A') FROM test_table", "doubleF, COALESCE(doubleF,'N/A')"),
("SELECT booleanF, COALESCE(booleanF, TRUE) FROM test_table", "booleanF, COALESCE(booleanF, TRUE)"),
("SELECT strF, COALESCE(strF,'N/A') FROM test_table", "strF, COALESCE(strF,'N/A')"),
# ("orderby/sort", "orderby/sort"),
("SELECT byteF FROM test_table ORDER BY byteF, doubleF", "byteF ORDER BY byteF, doubleF"),
("SELECT shortF FROM test_table ORDER BY shortF", "shortF ORDER BY shortF"),
("SELECT intF FROM test_table ORDER BY intF", "intF ORDER BY intF"),
("SELECT longF FROM test_table ORDER BY longF", "longF ORDER BY longF"),
("SELECT floatF FROM test_table ORDER BY floatF", "floatF ORDER BY floatF"),
("SELECT doubleF FROM test_table ORDER BY doubleF", "doubleF ORDER BY doubleF"),
("SELECT booleanF FROM test_table ORDER BY booleanF", "booleanF ORDER BY booleanF"),
("SELECT strF FROM test_table ORDER BY strF", "strF ORDER BY strF"),
("SELECT dateF FROM test_table ORDER BY dateF", "dateF ORDER BY dateF"),
("SELECT timestampF FROM test_table ORDER BY timestampF", "timestampF ORDER BY timestampF"),
# (" FUNCTIONAL TEST END", "FUNCTIONAL TEST END"),
# (" RANDOM TEST CHECKING", "RANDOM TEST CHECKING"),
("SELECT shortF FROM test_table", "shortF"),
("SELECT * FROM test_table", "*"),
("SELECT strF, intF, byteF, shortF, longF, booleanF, floatF  FROM test_table", "strF, intF, byteF, shortF, longF, booleanF, floatF"),
("SELECT 100 FROM test_table", "100"),
("SELECT intF, 2000 FROM test_table", "intF, 2000"),
("SELECT strF IS NULL FROM test_table", "strF IS NULL"),
("SELECT intF FROM test_table WHERE strF IS NULL", "intF WHERE strF IS NULL"),
("SELECT CASE WHEN strF IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM test_table", "CASE WHEN strF IS NULL THEN 'TRUE' ELSE 'FALSE' END"),
("SELECT Case WHEN intF=1000 THEN 'TRUE' ELSE 'FALSE' END FROM test_table", "Case WHEN intF=1000 THEN 'TRUE' ELSE 'FALSE' END"),
("SELECT intF IS NULL FROM test_table", "intF IS NULL"),
("SELECT intF IS NOT NULL FROM test_table", "intF IS NOT NULL"),
("SELECT * FROM test_table WHERE strF is NULL", "* WHERE strF is NULL"),
("SELECT * FROM test_table WHERE strF IS NOT NULL", "* WHERE strF IS NOT NULL"),
("SELECT * FROM test_table WHERE strF = 'Phuoc' OR strF = 'Yuan'", "* WHERE strF = 'Phuoc' OR strF = 'Yuan'"),
("SELECT * FROM test_table WHERE intF > 1000", "* WHERE intF > 1000"),
("SELECT strF, intF, shortF FROM test_table WHERE intF>1000 AND shortF>200", "strF, intF, shortF WHERE intF>1000 AND shortF>200"),
("SELECT * FROM test_table WHERE intF > 1000 AND intF = 2000", "* WHERE intF > 1000 AND intF = 2000"),
("SELECT strF, intF, longF, intF+longF FROM test_table", "strF, intF, longF, intF+longF"),
("SELECT intF+2000 FROM test_table", "intF+2000"),
("SELECT CAST(intF as long) FROM test_table", "CAST(intF as long)"),
("SELECT strF AS user_name FROM test_table", "strF AS user_name"),
("SELECT COALESCE(strF,'N/A') strF FROM test_table", "COALESCE(strF,'N/A') strF"),
("SELECT SUM(shortF) FROM test_table", "SUM(shortF)"),
("SELECT MIN(intF) FROM test_table", "MIN(intF)"),
("SELECT MAX(intF) FROM test_table", "MAX(intF)"),
("SELECT AVG(intF) FROM test_table", "AVG(intF)"),
("SELECT intF, shortF FROM test_table ORDER by intF, shortF", "intF, shortF ORDER by intF, shortF"),

("SELECT dateF, COALESCE(dateF,'N/A') FROM test_table", "dateF, COALESCE(dateF,'N/A')"),
("SELECT timestampF, COALESCE(timestampF,'N/A') FROM test_table", "timestampF, COALESCE(timestampF,'N/A')"),

("SELECT COUNT(1) FROM test_table", "COUNT(1)"),
("SELECT COUNT('*') FROM test_table", "COUNT('*')"),
("SELECT COUNT(strF) FROM test_table", "COUNT(strF)"),
("SELECT COUNT(strF) FROM test_table WHERE intF=3000", "COUNT(strF) WHERE intF=3000"),
("SELECT CASE WHEN byteF > 5  THEN 5*byteF  END FROM test_table", "CASE WHEN byteF > 5 THEN 5*byteF END"),
("SELECT CASE WHEN shortF > 5 THEN 5+shortF END FROM test_table", "CASE WHEN shortF > 5 THEN 5+shortF END"),
("SELECT CASE WHEN intF > 5 THEN 5  END FROM test_table", "CASE WHEN intF > 5 THEN 5 END"),
("SELECT CASE WHEN longF > 5 THEN 5 END FROM test_table", "CASE WHEN longF > 5 THEN 5 END"),
("SELECT CASE WHEN booleanF = True THEN 'TRUE'  END FROM test_table", "CASE WHEN booleanF = True THEN 'TRUE' END"),
("SELECT (CASE WHEN floatF > float(5.0) THEN float(5.0) END) as castwhen FROM test_table", "CASE WHEN floatF > 5.0 THEN 5.0 END"),
("SELECT (CASE WHEN doubleF > double(5.0) THEN double(5.0)  END) as castwhen FROM test_table", "CASE WHEN doubleF > 5.0 THEN 5.0 END"),
("SELECT CASE WHEN dateF >= '1994-01-01'  THEN 'good day'  END FROM test_table", "CASE WHEN dateF >= '1994-01-01' THEN 'good day' END"),
("SELECT CASE WHEN timestampF > '2020-05-01 12:01:015' THEN 'good time' END FROM test_table", "CASE WHEN timestampF > '2020-05-01 12:01:015' THEN 'good time' END"),
("SELECT CASE WHEN byteF > 5  THEN 5*byteF  ELSE byteF END FROM test_table", "CASE WHEN byteF > 5 THEN 5*byteF ELSE byteF END"),
("SELECT CASE WHEN shortF > 5 THEN 5+shortF ELSE shortF END FROM test_table", "CASE WHEN shortF > 5 THEN 5+shortF ELSE shortF END"),
("SELECT CASE WHEN intF > 5 THEN 5  ELSE 0 END FROM test_table", "CASE WHEN intF > 5 THEN 5 ELSE 0 END"),
("SELECT CASE WHEN longF > 5 THEN 5 ELSE 5*longF END FROM test_table", "CASE WHEN longF > 5 THEN 5 ELSE 5*longF END"),
("SELECT CASE WHEN booleanF = True THEN 'TRUE'  ELSE 'false'  END FROM test_table", "CASE WHEN booleanF = True THEN 'TRUE' ELSE 'false' END"),
("SELECT (CASE WHEN floatF > float(5.0) THEN float(5.0) ELSE float(6.0) END) as casewhen FROM test_table", "CASE WHEN floatF > 5.0 THEN 5.0 ELSE 6.0 END"),
("SELECT CASE WHEN doubleF > 5 THEN 5  ELSE doubleF/2 END FROM test_table", "CASE WHEN doubleF > 5.0 THEN 5.0 ELSE doubleF/2 END"),
("SELECT CASE WHEN dateF >= '1994-01-01'   THEN 'good day' ELSE 'bad day'  END FROM test_table", "CASE WHEN dateF >= '1994-01-01' THEN 'good day' ELSE 'bad day' END"),
("SELECT CASE WHEN timestampF > '2020-05-01 12:01:015' THEN 'good time' ELSE 'bad time' END FROM test_table", "CASE WHEN timestampF > '2020-05-01 12:01:015' THEN 'good time' ELSE 'bad time' END"),
]

SELECT_NEEDS_SORT_SQL = [
# (" AGG functions", "AGG functions"),
("SELECT AVG(intF) FROM test_table", "AVG(intF)"),
("SELECT AVG(byteF) FROM test_table", "AVG(byteF)"),
("SELECT AVG(shortF) FROM test_table", "AVG(shortF)"),
("SELECT AVG(longF) FROM test_table", "AVG(longF)"),
("SELECT AVG(floatF) FROM test_table", "AVG(floatF)"),
("SELECT AVG(doubleF) FROM test_table", "AVG(doubleF)"),
("SELECT AVG(byteF) FROM test_table GROUP BY intF", "AVG(byteF) GROUP BY intF"),
("SELECT AVG(shortF) FROM test_table GROUP BY intF", "AVG(shortF) GROUP BY intF"),
("SELECT AVG(intF) FROM test_table GROUP BY byteF", "AVG(intF) GROUP BY byteF"),
("SELECT AVG(longF) FROM test_table GROUP BY byteF", "AVG(longF) GROUP BY byteF"),
("SELECT AVG(floatF) FROM test_table GROUP BY byteF", "AVG(floatF) GROUP BY byteF"),
("SELECT AVG(doubleF) FROM test_table GROUP BY byteF", "AVG(doubleF) GROUP BY byteF"),
("SELECT AVG(intF) FROM test_table GROUP BY byteF, doubleF", "AVG(intF) GROUP BY byteF, doubleF"),
("SELECT AVG(floatF) FROM test_table GROUP BY byteF, shortF, intF ", "AVG(floatF) GROUP BY byteF, shortF, intF"),
("SELECT SUM(byteF) FROM test_table", "SUM(byteF)"),
("SELECT SUM(shortF) FROM test_table", "SUM(shortF)"),
("SELECT SUM(intF) FROM test_table", "SUM(intF)"),
("SELECT SUM(longF) FROM test_table", "SUM(longF)"),
("SELECT SUM(floatF) FROM test_table", "SUM(floatF)"),
("SELECT SUM(doubleF) FROM test_table", "SUM(doubleF)"),
("SELECT SUM(byteF)  FROM test_table GROUP BY intF", "SUM(byteF) GROUP BY intF"),
("SELECT SUM(shortF)  FROM test_table GROUP BY intF", "SUM(shortF) GROUP BY intF"),
("SELECT SUM(intF)  FROM test_table GROUP BY shortF", "SUM(intF) GROUP BY shortF"),
("SELECT SUM(longF)  FROM test_table GROUP BY shortF", "SUM(longF) GROUP BY shortF"),
("SELECT SUM(floatF)  FROM test_table GROUP BY byteF", "SUM(floatF) GROUP BY byteF"),
("SELECT SUM(doubleF)  FROM test_table GROUP BY intF", "SUM(doubleF) GROUP BY intF"),
("SELECT SUM(byteF)  FROM test_table GROUP BY intF, shortF", "SUM(byteF) GROUP BY intF, shortF"),
("SELECT SUM(shortF)  FROM test_table GROUP BY intF, floatF", "SUM(shortF) GROUP BY intF, floatF"),
("SELECT COUNT(byteF) FROM test_table", "COUNT(byteF)"),
("SELECT COUNT(shortF) FROM test_table", "COUNT(shortF)"),
("SELECT COUNT(intF) FROM test_table", "COUNT(intF)"),
("SELECT COUNT(longF) FROM test_table", "COUNT(longF)"),
("SELECT COUNT(floatF) FROM test_table", "COUNT(floatF)"),
("SELECT COUNT(doubleF) FROM test_table", "COUNT(doubleF)"),
("SELECT COUNT(booleanF) FROM test_table", "COUNT(booleanF)"),
("SELECT COUNT(strF) FROM test_table", "COUNT(strF)"),
("SELECT COUNT(dateF) FROM test_table", "COUNT(dateF)"),
("SELECT COUNT(timestampF) FROM test_table", "COUNT(timestampF)"),
("SELECT COUNT(byteF)  FROM test_table GROUP BY intF", "COUNT(byteF) GROUP BY intF"),
("SELECT COUNT(shortF)  FROM test_table GROUP BY intF", "COUNT(shortF) GROUP BY intF"),
("SELECT COUNT(intF)  FROM test_table GROUP BY byteF", "COUNT(intF) GROUP BY byteF"),
("SELECT COUNT(longF)  FROM test_table GROUP BY byteF", "COUNT(longF) GROUP BY byteF"),
("SELECT COUNT(floatF)  FROM test_table GROUP BY intF", "COUNT(floatF) GROUP BY intF"),
("SELECT COUNT(doubleF)  FROM test_table GROUP BY intF", "COUNT(doubleF) GROUP BY intF"),
("SELECT COUNT(byteF)  FROM test_table GROUP BY intF, shortF", "COUNT(byteF) GROUP BY intF, shortF"),
("SELECT COUNT(shortF)  FROM test_table GROUP BY intF, byteF", "COUNT(shortF) GROUP BY intF, byteF"),
("SELECT COUNT(intF)  FROM test_table GROUP BY byteF, shortF", "COUNT(intF) GROUP BY byteF, shortF"),
("SELECT MIN(byteF) FROM test_table", "MIN(byteF)"),
("SELECT MIN(shortF) FROM test_table", "MIN(shortF)"),
("SELECT MIN(intF) FROM test_table", "MIN(intF)"),
("SELECT MIN(longF) FROM test_table", "MIN(longF)"),
("SELECT MIN(floatF) FROM test_table", "MIN(floatF)"),
("SELECT MIN(doubleF) FROM test_table", "MIN(doubleF)"),
("SELECT MIN(booleanF) FROM test_table", "MIN(booleanF)"),
("SELECT MIN(dateF) FROM test_table", "MIN(dateF)"),
("SELECT MIN(timestampF) FROM test_table", "MIN(timestampF)"),
("SELECT MIN(byteF)  FROM test_table GROUP BY intF", "MIN(byteF) GROUP BY intF"),
("SELECT MIN(shortF)  FROM test_table GROUP BY intF", "MIN(shortF) GROUP BY intF"),
("SELECT MIN(intF)  FROM test_table GROUP BY intF", "MIN(intF) GROUP BY intF"),
("SELECT MIN(longF)  FROM test_table GROUP BY intF", "MIN(longF) GROUP BY intF"),
("SELECT MIN(floatF)  FROM test_table GROUP BY intF", "MIN(floatF) GROUP BY intF"),
("SELECT MIN(doubleF)  FROM test_table GROUP BY intF", "MIN(doubleF) GROUP BY intF"),
("SELECT MIN(booleanF)  FROM test_table GROUP BY intF", "MIN(booleanF) GROUP BY intF"),
("SELECT MIN(dateF)  FROM test_table GROUP BY intF", "MIN(dateF) GROUP BY intF"),
("SELECT MIN(timestampF)  FROM test_table GROUP BY intF", "MIN(timestampF) GROUP BY intF"),
("SELECT MIN(byteF)  FROM test_table GROUP BY intF, shortF", "MIN(byteF) GROUP BY intF, shortF"),
("SELECT MIN(shortF)  FROM test_table GROUP BY intF, byteF", "MIN(shortF) GROUP BY intF, byteF"),
("SELECT MAX(intF) FROM test_table", "MAX(intF)"),
("SELECT MAX(byteF) FROM test_table", "MAX(byteF)"),
("SELECT MAX(shortF) FROM test_table", "MAX(shortF)"),
("SELECT MAX(longF) FROM test_table", "MAX(longF)"),
("SELECT MAX(floatF) FROM test_table", "MAX(floatF)"),
("SELECT MAX(doubleF) FROM test_table", "MAX(doubleF)"),
("SELECT MAX(booleanF) FROM test_table", "MAX(booleanF)"),
("SELECT MAX(dateF) FROM test_table", "MAX(dateF)"),
("SELECT MAX(timestampF) FROM test_table", "MAX(timestampF)"),
("SELECT MAX(byteF)  FROM test_table GROUP BY intF", "MAX(byteF) GROUP BY intF"),
("SELECT MAX(shortF)  FROM test_table GROUP BY intF", "MAX(shortF) GROUP BY intF"),
("SELECT MAX(intF)  FROM test_table GROUP BY byteF", "MAX(intF) GROUP BY byteF"),
("SELECT MAX(longF)  FROM test_table GROUP BY intF", "MAX(longF) GROUP BY intF"),
("SELECT MAX(floatF)  FROM test_table GROUP BY intF", "MAX(floatF) GROUP BY intF"),
("SELECT MAX(doubleF)  FROM test_table GROUP BY intF", "MAX(doubleF) GROUP BY intF"),
("SELECT MAX(booleanF)  FROM test_table GROUP BY intF", "MAX(booleanF) GROUP BY intF"),
("SELECT MAX(dateF)  FROM test_table GROUP BY intF", "MAX(dateF) GROUP BY intF"),
("SELECT MAX(timestampF)  FROM test_table GROUP BY intF", "MAX(timestampF) GROUP BY intF"),
("SELECT MAX(byteF)  FROM test_table GROUP BY intF, shortF", "MAX(byteF) GROUP BY intF, shortF"),
("SELECT MAX(shortF)  FROM test_table GROUP BY intF, byteF", "MAX(shortF) GROUP BY intF, byteF"),
("SELECT DISTINCT(byteF) FROM test_table", "DISTINCT(byteF)"),
("SELECT DISTINCT(shortF) FROM test_table", "DISTINCT(shortF)"),
("SELECT DISTINCT(intF) FROM test_table", "DISTINCT(intF)"),
("SELECT DISTINCT(longF) FROM test_table", "DISTINCT(longF)"),
("SELECT DISTINCT(floatF) FROM test_table", "DISTINCT(floatF)"),
("SELECT DISTINCT(doubleF) FROM test_table", "DISTINCT(doubleF)"),
("SELECT DISTINCT(booleanF) FROM test_table", "DISTINCT(booleanF)"),
("SELECT DISTINCT(strF) FROM test_table", "DISTINCT(strF)"),
("SELECT DISTINCT(dateF) FROM test_table", "DISTINCT(dateF)"),
("SELECT DISTINCT(timestampF) FROM test_table", "DISTINCT(timestampF)"),
("SELECT COUNT(DISTINCT(byteF)) FROM test_table GROUP BY intF", "COUNT(DISTINCT(byteF)) GROUP BY intF"),
("SELECT COUNT(DISTINCT(shortF)) FROM test_table GROUP BY intF", "COUNT(DISTINCT(shortF)) GROUP BY intF"),
("SELECT COUNT(DISTINCT(intF)) FROM test_table GROUP BY byteF", "COUNT(DISTINCT(intF)) GROUP BY byteF"),
("SELECT COUNT(DISTINCT(longF)) FROM test_table GROUP BY intF", "COUNT(DISTINCT(longF)) GROUP BY intF"),
("SELECT COUNT(DISTINCT(floatF)) FROM test_table GROUP BY intF", "COUNT(DISTINCT(floatF)) GROUP BY intF"),
("SELECT COUNT(DISTINCT(doubleF)) FROM test_table GROUP BY intF", "COUNT(DISTINCT(doubleF)) GROUP BY intF"),
("SELECT COUNT(DISTINCT(booleanF)) FROM test_table GROUP BY intF", "COUNT(DISTINCT(booleanF)) GROUP BY intF"),
("SELECT COUNT(DISTINCT(strF)) FROM test_table GROUP BY intF", "COUNT(DISTINCT(strF)) GROUP BY intF"),
("SELECT COUNT(DISTINCT(dateF)) FROM test_table GROUP BY intF", "COUNT(DISTINCT(dateF)) GROUP BY intF"),
("SELECT COUNT(DISTINCT(timestampF)) FROM test_table GROUP BY intF", "COUNT(DISTINCT(timestampF)) GROUP BY intF"),
("SELECT COUNT(DISTINCT(intF)), COUNT(DISTINCT(byteF)), COUNT(DISTINCT(shortF)), COUNT(DISTINCT(longF)) FROM test_table", "COUNT(DISTINCT(intF)), COUNT(DISTINCT(byteF)), COUNT(DISTINCT(shortF)), COUNT(DISTINCT(longF))"),
("SELECT COUNT(DISTINCT(intF)), COUNT(DISTINCT(floatF)), COUNT(DISTINCT(doubleF)), COUNT(DISTINCT(booleanF)) FROM test_table", "COUNT(DISTINCT(intF)), COUNT(DISTINCT(floatF)), COUNT(DISTINCT(doubleF)), COUNT(DISTINCT(booleanF))"),
("SELECT COUNT(DISTINCT(dateF)), COUNT(DISTINCT(strF)), COUNt(DISTINCT(timestampF)) FROM test_table", "COUNT(DISTINCT(dateF)), COUNT(DISTINCT(strF)), COUNt(DISTINCT(timestampF))"),
("SELECT COUNT(DISTINCT(intF)), COUNT(DISTINCT(byteF)), COUNT(DISTINCT(shortF)) FROM test_table GROUP BY longF ", "COUNT(DISTINCT(intF)), COUNT(DISTINCT(byteF)), COUNT(DISTINCT(shortF)) GROUP BY longF"),
("SELECT COUNT(DISTINCT(intF)), COUNt(DISTINCT(byteF)) FROM test_table GROUP BY longF, floatF", "COUNT(DISTINCT(intF)), COUNt(DISTINCT(byteF)) GROUP BY longF, floatF"),
("SELECT strF, intF, SUM(shortF) FROM test_table GROUP BY strF, intF", "strF, intF, SUM(shortF) GROUP BY strF, intF"),
("SELECT COUNT(byteF) as count, AVG(intF) as avg, (SUM(intF) + MAX(shortF * 3)) as summax FROM test_table GROUP BY intF", "COUNT(byteF), AVG(intF), SUM(intF) + MAX(shortF * 3) GROUP BY intF"),
("SELECT COUNT(byteF) as count, (AVG(intF) * 5.0) as avg, (SUM(intF) + MAX(shortF * 3)) as summax FROM test_table GROUP BY intF*3", "COUNT(byteF), AVG(intF) * 5.0, SUM(intF) + MAX(shortF * 3) GROUP BY intF*3"),
("SELECT COUNT(*) as count, (AVG(intF) * 5.0) as avg, (SUM(intF) + MAX(shortF * 3)) as summax FROM test_table GROUP BY intF*3", "COUNT(*), AVG(intF) * 5.0, SUM(intF) + MAX(shortF * 3) GROUP BY intF*3"),
# ("SELECT  SUM(intF) OVER (PARTITION BY byteF ORDER BY shortF) as sum_total FROM test_table", "SUM(intF) OVER (PARTITION BY byteF ORDER BY shortF) as sum_total"),
pytest.param(("SELECT ROW_NUMBER() OVER (PARTITION BY byteF ORDER BY byteF) row_num,  byteF FROM test_table", "ROW_NUMBER() OVER (PARTITION BY byteF ORDER BY byteF) row_num, byteF"), marks=pytest.mark.xfail(is_databricks_runtime(), reason='https://github.com/NVIDIA/spark-rapids/issues/203')),
pytest.param(("SELECT ROW_NUMBER() OVER (PARTITION BY shortF ORDER BY shortF) row_num,  shortF FROM test_table", "ROW_NUMBER() OVER (PARTITION BY shortF ORDER BY shortF) row_num, shortF"), marks=pytest.mark.xfail(is_databricks_runtime(), reason='https://github.com/NVIDIA/spark-rapids/issues/203')),
pytest.param(("SELECT ROW_NUMBER() OVER (PARTITION BY intF ORDER BY intF) row_num,  intF FROM test_table", "ROW_NUMBER() OVER (PARTITION BY intF ORDER BY intF) row_num, intF"), marks=pytest.mark.xfail(is_databricks_runtime(), reason='https://github.com/NVIDIA/spark-rapids/issues/203')),
pytest.param(("SELECT ROW_NUMBER() OVER (PARTITION BY longF ORDER BY longF) row_num,  longF FROM test_table", "ROW_NUMBER() OVER (PARTITION BY longF ORDER BY longF) row_num, longF"), marks=pytest.mark.xfail(is_databricks_runtime(), reason='https://github.com/NVIDIA/spark-rapids/issues/203')),
pytest.param(("SELECT ROW_NUMBER() OVER (PARTITION BY floatF ORDER BY floatF) row_num,  floatF FROM test_table", "ROW_NUMBER() OVER (PARTITION BY floatF ORDER BY floatF) row_num, floatF"), marks=pytest.mark.xfail(is_databricks_runtime(), reason='https://github.com/NVIDIA/spark-rapids/issues/203')),
pytest.param(("SELECT ROW_NUMBER() OVER (PARTITION BY booleanF ORDER BY booleanF) row_num,  booleanF FROM test_table", "ROW_NUMBER() OVER (PARTITION BY booleanF ORDER BY booleanF) row_num, booleanF"), marks=pytest.mark.xfail(is_databricks_runtime(), reason='https://github.com/NVIDIA/spark-rapids/issues/203')),
pytest.param(("SELECT ROW_NUMBER() OVER (PARTITION BY strF ORDER BY strF) row_num,  strF FROM test_table", "ROW_NUMBER() OVER (PARTITION BY strF ORDER BY strF) row_num, strF"), marks=pytest.mark.xfail(is_databricks_runtime(), reason='https://github.com/NVIDIA/spark-rapids/issues/203')),
pytest.param(("SELECT ROW_NUMBER() OVER (PARTITION BY dateF ORDER BY dateF) row_num,  dateF FROM test_table", "ROW_NUMBER() OVER (PARTITION BY dateF ORDER BY dateF) row_num, dateF"), marks=pytest.mark.xfail(is_databricks_runtime(), reason='https://github.com/NVIDIA/spark-rapids/issues/203')),
pytest.param(("SELECT ROW_NUMBER() OVER (PARTITION BY timestampF ORDER BY timestampF) row_num,  timestampF FROM test_table", "ROW_NUMBER() OVER (PARTITION BY timestampF ORDER BY timestampF) row_num, timestampF"), marks=pytest.mark.xfail(is_databricks_runtime(), reason='https://github.com/NVIDIA/spark-rapids/issues/203')),
# ("window/row/range  (need change)", "window/row/range (need change)"),
#("SELECT  byteF, SUM(byteF) OVER (PARTITION BY byteF ORDER BY byteF RANGE BETWEEN 20 PRECEDING AND 10 FOLLOWING ) as sum_total FROM test_table", "byteF, SUM(byteF) OVER (PARTITION BY byteF ORDER BY byteF RANGE BETWEEN 20 PRECEDING AND 10 FOLLOWING ) as sum_total"),
#("SELECT  SUM(intF) OVER (PARTITION BY byteF ORDER BY byteF RANGE BETWEEN 20 PRECEDING AND 10 FOLLOWING ) as sum_total FROM test_table", "SUM(intF) OVER (PARTITION BY byteF ORDER BY byteF RANGE BETWEEN 20 PRECEDING AND 10 FOLLOWING ) as sum_total"),
("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY shortF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY shortF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total"),
#("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY shortF RANGE BETWEEN 20 PRECEDING AND 5 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY shortF RANGE BETWEEN 20 PRECEDING AND 5 FOLLOWING ) as sum_total"),
("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY longF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY longF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total"),
#("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY longF RANGE BETWEEN 20 PRECEDING AND 5 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY longF RANGE BETWEEN 20 PRECEDING AND 5 FOLLOWING ) as sum_total"),
("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY floatF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY floatF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total"),
#("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY floatF RANGE BETWEEN 20 PRECEDING AND 5 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY floatF RANGE BETWEEN 20 PRECEDING AND 5 FOLLOWING ) as sum_total"),
("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY doubleF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY doubleF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total"),
#("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY doubleF RANGE BETWEEN 20 PRECEDING AND 50 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY doubleF RANGE BETWEEN 20 PRECEDING AND 50 FOLLOWING ) as sum_total"),
("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY booleanF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY booleanF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total"),
("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY booleanF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY booleanF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total"),
("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY dateF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY dateF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total"),
("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY timestampF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY timestampF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total"),
("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY CAST(dateF AS TIMESTAMP) RANGE BETWEEN INTERVAL 1 DAYS PRECEDING AND INTERVAL 1 DAYS FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY CAST(dateF AS TIMESTAMP) RANGE BETWEEN INTERVAL 1 DAYS PRECEDING AND INTERVAL 1 DAYS FOLLOWING ) as sum_total"),


# ("not supported, filed bug", "not supported, filed bug"),
# ("SELECT  SUM(byteF) OVER (PARTITION BY byteF ORDER BY timestampF RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total FROM test_table", "SUM(byteF) OVER (PARTITION BY byteF ORDER BY timestampF RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as sum_total"),

("SELECT A.shortF, B.intF from test_table A, test_table B where A.intF=B.intF ORDER BY A.intF", "A.shortF, B.intF A, test_table B where A.intF=B.intF ORDER BY A.intF"),
("SELECT A.shortF, B.intF from test_table A, test_table B where A.intF=B.intF", "A.shortF, B.intF A, test_table B where A.intF=B.intF"),
]



SELECT_JOIN_SQL = [
# (" JOIN", "JOIN"),
("SELECT A.byteF as AbyteF, B.byteF as BbyteF from test_table A, test_table B where A.byteF=B.byteF ", "A.byteF, B.byteF A, test_table B where A.byteF=B.byteF"),
("SELECT A.shortF as AshortF, B.shortF as BbyteF from test_table A, test_table B where A.shortF=B.shortF", "A.shortF, B.shortF A, test_table B where A.shortF=B.shortF"),
("SELECT A.intF as AintF, B.intF as BintF from test_table A, test_table B where A.intF=B.intF ", "A.intF, B.intF A, test_table B where A.intF=B.intF"),
("SELECT A.longF as AlongF, B.longF as BlongF from test_table A, test_table B where A.longF=B.longF", "A.longF, B.longF A, test_table B where A.longF=B.longF"),
("SELECT A.floatF as AfloatF, B.floatF as BfloatF from test_table A, test_table B where A.floatF=B.floatF ", "A.floatF, B.floatF A, test_table B where A.floatF=B.floatF"),
("SELECT A.doubleF as AdoubleF, B.doubleF as BdoubleF from test_table A, test_table B where A.doubleF=B.doubleF", "A.doubleF, B.doubleF A, test_table B where A.doubleF=B.doubleF"),
("SELECT A.booleanF as AbooleanF, B.booleanF as BbooleanF from test_table A, test_table B where A.booleanF=B.booleanF ", "A.booleanF, B.booleanF A, test_table B where A.booleanF=B.booleanF"),
("SELECT A.strF as AstrF, B.strF as BstrF from test_table A, test_table B where A.strF=B.strF", "A.strF, B.strF A, test_table B where A.strF=B.strF"),
("SELECT A.dateF as AdateF, B.dateF as BdateF from test_table A, test_table B where A.dateF=B.dateF ", "A.dateF, B.dateF A, test_table B where A.dateF=B.dateF"),
("SELECT A.timestampF as AtimestampF, B.timestampF as BtimestampF from test_table A, test_table B where A.timestampF=B.timestampF", "A.timestampF, B.timestampF A, test_table B where A.timestampF=B.timestampF"),
("SELECT test_table.byteF as byteF, test_table1.byteF as byteF1 from test_table INNER JOIN test_table1 ON test_table.byteF=test_table1.byteF", "test_table.byteF, test_table1.byteF INNER JOIN test_table1 ON test_table.byteF=test_table1.byteF"),
("SELECT test_table.shortF as shortF, test_table1.shortF as shortF1 from test_table INNER JOIN test_table1 ON test_table.shortF=test_table1.shortF", "test_table.shortF, test_table1.shortF INNER JOIN test_table1 ON test_table.shortF=test_table1.shortF"),
("SELECT test_table.intF as intF, test_table1.intF as intF1 from test_table INNER JOIN test_table1 ON test_table.intF=test_table1.intF", "test_table.intF, test_table1.intF INNER JOIN test_table1 ON test_table.intF=test_table1.intF"),
("SELECT test_table.longF as longF, test_table1.longF as longF1 from test_table INNER JOIN test_table1 ON test_table.longF=test_table1.longF", "test_table.longF, test_table1.longF INNER JOIN test_table1 ON test_table.longF=test_table1.longF"),
("SELECT test_table.floatF as floatF, test_table1.floatF as floatF1 from test_table INNER JOIN test_table1 ON test_table.floatF=test_table1.floatF", "test_table.floatF, test_table1.floatF INNER JOIN test_table1 ON test_table.floatF=test_table1.floatF"),
("SELECT test_table.doubleF as doubleF, test_table1.doubleF as doubleF1 from test_table INNER JOIN test_table1 ON test_table.doubleF=test_table1.doubleF", "test_table.doubleF, test_table1.doubleF INNER JOIN test_table1 ON test_table.doubleF=test_table1.doubleF"),
("SELECT test_table.booleanF as booleanF, test_table1.booleanF as booleanF1 from test_table INNER JOIN test_table1 ON test_table.booleanF=test_table1.booleanF", "test_table.booleanF, test_table1.booleanF INNER JOIN test_table1 ON test_table.booleanF=test_table1.booleanF"),
("SELECT test_table.strF as strF, test_table1.strF as strF1 from test_table INNER JOIN test_table1 ON test_table.strF=test_table1.strF", "test_table.strF, test_table1.strF INNER JOIN test_table1 ON test_table.strF=test_table1.strF"),
("SELECT test_table.dateF as dateF, test_table1.dateF as dateF1 from test_table INNER JOIN test_table1 ON test_table.dateF=test_table1.dateF", "test_table.dateF, test_table1.dateF INNER JOIN test_table1 ON test_table.dateF=test_table1.dateF"),
("SELECT test_table.timestampF as timestampF, test_table1.timestampF as timestampF1 from test_table INNER JOIN test_table1 ON test_table.timestampF=test_table1.timestampF", "test_table.timestampF, test_table1.timestampF INNER JOIN test_table1 ON test_table.timestampF=test_table1.timestampF"),
("SELECT test_table.byteF as byteF, test_table1.byteF as byteF1 from test_table LEFT JOIN test_table1 ON test_table.byteF=test_table1.byteF", "test_table.byteF, test_table1.byteF LEFT JOIN test_table1 ON test_table.byteF=test_table1.byteF"),
("SELECT test_table.shortF as shortF, test_table1.shortF as shortF1 from test_table LEFT JOIN test_table1 ON test_table.shortF=test_table1.shortF", "test_table.shortF, test_table1.shortF LEFT JOIN test_table1 ON test_table.shortF=test_table1.shortF"),
("SELECT test_table.intF as intF, test_table1.intF as intF1 from test_table LEFT JOIN test_table1 ON test_table.intF=test_table1.intF", "test_table.intF, test_table1.intF LEFT JOIN test_table1 ON test_table.intF=test_table1.intF"),
("SELECT test_table.longF as longF, test_table1.longF as longF1 from test_table LEFT JOIN test_table1 ON test_table.longF=test_table1.longF", "test_table.longF, test_table1.longF LEFT JOIN test_table1 ON test_table.longF=test_table1.longF"),
("SELECT test_table.floatF as floatF, test_table1.floatF as floatF1 from test_table LEFT JOIN test_table1 ON test_table.floatF=test_table1.floatF", "test_table.floatF, test_table1.floatF LEFT JOIN test_table1 ON test_table.floatF=test_table1.floatF"),
("SELECT test_table.doubleF as doubleF, test_table1.doubleF as doubleF1 from test_table LEFT JOIN test_table1 ON test_table.doubleF=test_table1.doubleF", "test_table.doubleF, test_table1.doubleF LEFT JOIN test_table1 ON test_table.doubleF=test_table1.doubleF"),
("SELECT test_table.booleanF as booleanF, test_table1.booleanF as booleanF1 from test_table LEFT JOIN test_table1 ON test_table.booleanF=test_table1.booleanF", "test_table.booleanF, test_table1.booleanF LEFT JOIN test_table1 ON test_table.booleanF=test_table1.booleanF"),
("SELECT test_table.strF as strF, test_table1.strF as strF1 from test_table LEFT JOIN test_table1 ON test_table.strF=test_table1.strF", "test_table.strF, test_table1.strF LEFT JOIN test_table1 ON test_table.strF=test_table1.strF"),
("SELECT test_table.dateF as dateF, test_table1.dateF as dateF1 from test_table LEFT JOIN test_table1 ON test_table.dateF=test_table1.dateF", "test_table.dateF, test_table1.dateF LEFT JOIN test_table1 ON test_table.dateF=test_table1.dateF"),
("SELECT test_table.timestampF as timestampF, test_table1.timestampF as timestampF1 from test_table LEFT JOIN test_table1 ON test_table.timestampF=test_table1.timestampF", "test_table.timestampF, test_table1.timestampF LEFT JOIN test_table1 ON test_table.timestampF=test_table1.timestampF"),
("SELECT test_table.byteF as byteF, test_table1.byteF as byteF1 from test_table RIGHT JOIN test_table1 ON test_table.byteF=test_table1.byteF", "test_table.byteF, test_table1.byteF RIGHT JOIN test_table1 ON test_table.byteF=test_table1.byteF"),
("SELECT test_table.shortF as shortF, test_table1.shortF as shortF1 from test_table RIGHT JOIN test_table1 ON test_table.shortF=test_table1.shortF", "test_table.shortF, test_table1.shortF RIGHT JOIN test_table1 ON test_table.shortF=test_table1.shortF"),
("SELECT test_table.intF as intF, test_table1.intF as intF1 from test_table RIGHT JOIN test_table1 ON test_table.intF=test_table1.intF", "test_table.intF, test_table1.intF RIGHT JOIN test_table1 ON test_table.intF=test_table1.intF"),
("SELECT test_table.longF as longF, test_table1.longF as longF1 from test_table RIGHT JOIN test_table1 ON test_table.longF=test_table1.longF", "test_table.longF, test_table1.longF RIGHT JOIN test_table1 ON test_table.longF=test_table1.longF"),
("SELECT test_table.floatF as floatF, test_table1.floatF as floatF1 from test_table RIGHT JOIN test_table1 ON test_table.floatF=test_table1.floatF", "test_table.floatF, test_table1.floatF RIGHT JOIN test_table1 ON test_table.floatF=test_table1.floatF"),
("SELECT test_table.doubleF as doubleF, test_table1.doubleF as doubleF1 from test_table RIGHT JOIN test_table1 ON test_table.doubleF=test_table1.doubleF", "test_table.doubleF, test_table1.doubleF RIGHT JOIN test_table1 ON test_table.doubleF=test_table1.doubleF"),
("SELECT test_table.booleanF as booleanF, test_table1.booleanF as booleanF1 from test_table RIGHT JOIN test_table1 ON test_table.booleanF=test_table1.booleanF", "test_table.booleanF, test_table1.booleanF RIGHT JOIN test_table1 ON test_table.booleanF=test_table1.booleanF"),
("SELECT test_table.strF as strF, test_table1.strF as strF1 from test_table RIGHT JOIN test_table1 ON test_table.strF=test_table1.strF", "test_table.strF, test_table1.strF RIGHT JOIN test_table1 ON test_table.strF=test_table1.strF"),
("SELECT test_table.dateF as dateF, test_table1.dateF as dateF1 from test_table RIGHT JOIN test_table1 ON test_table.dateF=test_table1.dateF", "test_table.dateF, test_table1.dateF RIGHT JOIN test_table1 ON test_table.dateF=test_table1.dateF"),
("SELECT test_table.timestampF as timestampF, test_table1.timestampF as timestampF1 from test_table RIGHT JOIN test_table1 ON test_table.timestampF=test_table1.timestampF", "test_table.timestampF, test_table1.timestampF RIGHT JOIN test_table1 ON test_table.timestampF=test_table1.timestampF"),
("SELECT test_table.byteF as byteF, test_table1.byteF as byteF1 from test_table FULL JOIN test_table1 ON test_table.byteF=test_table1.byteF", "test_table.byteF, test_table1.byteF FULL JOIN test_table1 ON test_table.byteF=test_table1.byteF"),
("SELECT test_table.shortF as shortF, test_table1.shortF as shortF1 from test_table FULL JOIN test_table1 ON test_table.shortF=test_table1.shortF", "test_table.shortF, test_table1.shortF FULL JOIN test_table1 ON test_table.shortF=test_table1.shortF"),
("SELECT test_table.intF as intF, test_table1.intF as intF1 from test_table FULL JOIN test_table1 ON test_table.intF=test_table1.intF", "test_table.intF, test_table1.intF FULL JOIN test_table1 ON test_table.intF=test_table1.intF"),
("SELECT test_table.longF as longF, test_table1.longF as longF1 from test_table FULL JOIN test_table1 ON test_table.longF=test_table1.longF", "test_table.longF, test_table1.longF FULL JOIN test_table1 ON test_table.longF=test_table1.longF"),
("SELECT test_table.floatF as floatF, test_table1.floatF as floatF1 from test_table FULL JOIN test_table1 ON test_table.floatF=test_table1.floatF", "test_table.floatF, test_table1.floatF FULL JOIN test_table1 ON test_table.floatF=test_table1.floatF"),
("SELECT test_table.doubleF as doubleF, test_table1.doubleF as doubleF1 from test_table FULL JOIN test_table1 ON test_table.doubleF=test_table1.doubleF", "test_table.doubleF, test_table1.doubleF FULL JOIN test_table1 ON test_table.doubleF=test_table1.doubleF"),
("SELECT test_table.booleanF as booleanF, test_table1.booleanF as booleanF1 from test_table FULL JOIN test_table1 ON test_table.booleanF=test_table1.booleanF", "test_table.booleanF, test_table1.booleanF FULL JOIN test_table1 ON test_table.booleanF=test_table1.booleanF"),
("SELECT test_table.strF as strF, test_table1.strF as strF1 from test_table FULL JOIN test_table1 ON test_table.strF=test_table1.strF", "test_table.strF, test_table1.strF FULL JOIN test_table1 ON test_table.strF=test_table1.strF"),
("SELECT test_table.dateF as dateF, test_table1.dateF as dateF1 from test_table FULL JOIN test_table1 ON test_table.dateF=test_table1.dateF", "test_table.dateF, test_table1.dateF FULL JOIN test_table1 ON test_table.dateF=test_table1.dateF"),
("SELECT test_table.timestampF as timestampF, test_table1.timestampF as timestampF1 from test_table FULL JOIN test_table1 ON test_table.timestampF=test_table1.timestampF", "test_table.timestampF, test_table1.timestampF FULL JOIN test_table1 ON test_table.timestampF=test_table1.timestampF")
]

SELECT_PRE_ORDER_SQL=[
("SELECT FIRST(byteF) as res FROM test_table GROUP BY intF", "FIRST(byteF) GROUP BY intF", "byteF"),
("SELECT FIRST(shortF) as res  FROM test_table GROUP BY intF", "FIRST(shortF) GROUP BY intF", "shortF"),
("SELECT FIRST(intF) as res FROM test_table GROUP BY byteF", "FIRST(intF) GROUP BY byteF", "intF"),
("SELECT FIRST(longF) as res FROM test_table GROUP BY intF", "FIRST(longF) GROUP BY intF", "longF"),
("SELECT FIRST(floatF) as res FROM test_table GROUP BY intF", "FIRST(floatF) GROUP BY intF", "floatF"),
("SELECT FIRST(doubleF) as res FROM test_table GROUP BY intF", "FIRST(doubleF) GROUP BY intF", "doubleF"),
("SELECT FIRST(booleanF) as res FROM test_table GROUP BY intF", "FIRST(booleanF) GROUP BY intF", "booleanF"),
("SELECT FIRST(dateF) as res FROM test_table GROUP BY intF", "FIRST(dateF) GROUP BY intF", "dateF"),
("SELECT FIRST(timestampF) as res FROM test_table GROUP BY intF", "FIRST(timestampF) GROUP BY intF", "timestampF"),
("SELECT FIRST(byteF) as res FROM test_table GROUP BY intF, shortF", "FIRST(byteF) GROUP BY intF, shortF", "byteF"),
("SELECT FIRST(shortF) as res FROM test_table GROUP BY intF, byteF", "FIRST(shortF) GROUP BY intF, byteF", "shortF"),

("SELECT LAST(byteF) as res FROM test_table GROUP BY intF", "LAST(byteF) GROUP BY intF", "byteF"),
("SELECT LAST(shortF) as res FROM test_table GROUP BY intF", "LAST(shortF) GROUP BY intF", "shortF"),
("SELECT LAST(intF) as res FROM test_table GROUP BY byteF", "LAST(intF) GROUP BY byteF", "intF"),
("SELECT LAST(longF) as res FROM test_table GROUP BY intF", "LAST(longF) GROUP BY intF", "longF"),
("SELECT LAST(floatF) as res FROM test_table GROUP BY intF", "LAST(floatF) GROUP BY intF", "floatF"),
("SELECT LAST(doubleF) as res FROM test_table GROUP BY intF", "LAST(doubleF) GROUP BY intF", "doubleF"),
("SELECT LAST(booleanF) as res FROM test_table GROUP BY intF", "LAST(booleanF) GROUP BY intF", "booleanF"),
("SELECT LAST(dateF) as res FROM test_table GROUP BY intF", "LAST(dateF) GROUP BY intF", "dateF"),
("SELECT LAST(timestampF) as res FROM test_table GROUP BY intF", "LAST(timestampF) GROUP BY intF", "timestampF"),

("SELECT byteF, SUM(byteF) OVER (PARTITION BY shortF ORDER BY intF ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING ) as res FROM test_table", "byteF, SUM(byteF) OVER (PARTITION BY shortF ORDER BY intF ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING ) as res", "byteF"),
("SELECT SUM(intF) OVER (PARTITION BY byteF ORDER BY byteF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as res FROM test_table", "SUM(intF) OVER (PARTITION BY byteF ORDER BY byteF ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) as res", "intF"),
# Aggregations with variable width outputs, like strings, are done using a sort aggregation on the CPU
# There are a number of issues related to this and getting the GPU to match. If either of these
# queries fail it is likely related to sorting in spark, and there may not be a lot that we can
# do to fix this.
("SELECT LAST(strF) as res FROM test_table GROUP BY intF", "LAST(strF) GROUP BY intF", "strF"),
("SELECT FIRST(strF) as res FROM test_table GROUP BY intF", "FIRST(strF) GROUP BY intF", "strF"),
]
'''
("SELECT LAST(byteF) FROM test_table", "LAST(byteF)"),
("SELECT LAST(shortF) FROM test_table", "LAST(shortF)"),
("SELECT LAST(intF) FROM test_table", "LAST(intF)"),
("SELECT LAST(longF) FROM test_table", "LAST(longF)"),
("SELECT LAST(floatF) FROM test_table", "LAST(floatF)"),
("SELECT LAST(doubleF) FROM test_table", "LAST(doubleF)"),
("SELECT LAST(booleanF) FROM test_table", "LAST(booleanF)"),
("SELECT LAST(strF) FROM test_table", "LAST(strF)"),
("SELECT LAST(dateF) FROM test_table", "LAST(dateF)"),
("SELECT LAST(timestampF) FROM test_table", "LAST(timestampF)"),

("SELECT FIRST(byteF) FROM test_table", "FIRST(byteF)"),
("SELECT FIRST(shortF) FROM test_table", "FIRST(shortF)"),
("SELECT FIRST(intF) FROM test_table", "FIRST(intF)"),
("SELECT FIRST(longF) FROM test_table", "FIRST(longF)"),
("SELECT FIRST(floatF) FROM test_table", "FIRST(floatF)"),
("SELECT FIRST(doubleF) FROM test_table", "FIRST(doubleF)"),
("SELECT FIRST(booleanF) FROM test_table", "FIRST(booleanF)"),
("SELECT FIRST(strF) FROM test_table", "FIRST(strF)"),
("SELECT FIRST(dateF) FROM test_table", "FIRST(dateF)"),
("SELECT FIRST(timestampF) FROM test_table", "FIRST(timestampF)"),
'''
SELECT_FLOAT_SQL=[
("SELECT IFNULL(floatF, 0) as if_null FROM test_table", "IFNULL(floatF, 0)"),
("SELECT floatF, COALESCE(floatF, 0) FROM test_table", "floatF, COALESCE(floatF,0)"),
]

SELECT_REGEXP_SQL=[
("SELECT REGEXP_REPLACE(strF, 'Yu', 'Eric') FROM test_table", "REGEXP_REPLACE(strF, 'Yu', 'Eric')"),
]