| /* |
| * Licensed to the Apache Software Foundation (ASF) under one or more |
| * contributor license agreements. See the NOTICE file distributed with |
| * this work for additional information regarding copyright ownership. |
| * The ASF licenses this file to You under the Apache License, Version 2.0 |
| * (the "License"); you may not use this file except in compliance with |
| * the License. You may obtain a copy of the License at |
| * |
| * 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. |
| */ |
| |
| package org.apache.carbondata.presto.integrationtest |
| |
| import java.io.File |
| import java.sql.Timestamp |
| import java.util |
| |
| import org.apache.hadoop.fs.permission.{FsAction, FsPermission} |
| import org.scalatest.{BeforeAndAfterAll, FunSuiteLike} |
| |
| import org.apache.carbondata.common.logging.LogServiceFactory |
| import org.apache.carbondata.core.constants.CarbonCommonConstants |
| import org.apache.carbondata.core.datastore.impl.FileFactory |
| import org.apache.carbondata.core.util.{CarbonProperties, CarbonUtil} |
| import org.apache.carbondata.presto.server.PrestoServer |
| |
| |
| class PrestoAllDataTypeTest extends FunSuiteLike with BeforeAndAfterAll { |
| |
| private val logger = LogServiceFactory |
| .getLogService(classOf[PrestoAllDataTypeTest].getCanonicalName) |
| |
| private val rootPath = new File(this.getClass.getResource("/").getPath |
| + "../../../..").getCanonicalPath |
| private val storePath = s"$rootPath/integration/presto/target/store" |
| private val prestoServer = new PrestoServer |
| |
| // Table schema: |
| // +-------------+----------------+-------------+------------+ |
| // | Column name | Data type | Column type | Dictionary | |
| // +-------------+----------------+--------------+-----------+ |
| // | id | string | dimension | yes | |
| // +-------------+----------------+-------------+------------+ |
| // | date | date | dimension | yes | |
| // +-------------+----------------+-------------+------------+ |
| // | country | string | dimension | yes | |
| // +-------------+----------------+-------------+------------- |
| // | name | string | dimension | yes | |
| // +-------------+----------------+-------------+------------- |
| // | phonetype | string | dimension | yes | |
| // +-------------+----------------+-------------+------------- |
| // | serialname | string | dimension | true | |
| // +-------------+----------------+-------------+------------- |
| // | bonus |short decimal | measure | false | |
| // +-------------+----------------+-------------+------------- |
| // | monthlyBonus| longdecimal | measure | false | |
| // +-------------+----------------+-------------+------------- |
| // | dob | timestamp | dimension | true | |
| // +-------------+----------------+-------------+------------+ |
| // | shortField | shortfield | measure | true | |
| // +-------------+----------------+-------------+------------- |
| // |isCurrentEmp | boolean | measure | true | |
| // +-------------+----------------+-------------+------------+ |
| |
| override def beforeAll: Unit = { |
| import org.apache.carbondata.presto.util.CarbonDataStoreCreator |
| CarbonProperties.getInstance().addProperty(CarbonCommonConstants.CARBON_WRITTEN_BY_APPNAME, |
| "Presto") |
| val map = new util.HashMap[String, String]() |
| map.put("hive.metastore", "file") |
| map.put("hive.metastore.catalog.dir", s"file://$storePath") |
| |
| prestoServer.startServer("testdb", map) |
| prestoServer.execute("drop table if exists testdb.testtable") |
| prestoServer.execute("drop schema if exists testdb") |
| prestoServer.execute("create schema testdb") |
| prestoServer.execute("create table testdb.testtable(ID int, date date, country varchar, name varchar, phonetype varchar, serialname varchar,salary double, bonus decimal(10,4), monthlyBonus decimal(18,4), dob timestamp, shortField smallint, iscurrentemployee boolean) with(format='CARBON') ") |
| CarbonDataStoreCreator |
| .createCarbonStore(storePath, |
| s"$rootPath/integration/presto/src/test/resources/alldatatype.csv") |
| logger.info(s"\nCarbon store is created at location: $storePath") |
| cleanUp |
| } |
| |
| override def afterAll(): Unit = { |
| prestoServer.stopServer() |
| CarbonUtil.deleteFoldersAndFiles(FileFactory.getCarbonFile(storePath)) |
| } |
| |
| test("test the result for count(*) in presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT COUNT(*) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> 11)) |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("test the result for count() clause with distinct operator in presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT COUNT(DISTINCT ID) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> 9)) |
| assert(actualResult.equals(expectedResult)) |
| |
| } |
| test("test the result for sum()in presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT SUM(ID) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> 54)) |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("test the result for sum() wiTh distinct operator in presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT SUM(DISTINCT ID) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> 45)) |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("test the result for avg() with distinct operator in presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT AVG(DISTINCT ID) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> 5)) |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("test the result for min() with distinct operator in presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT MIN(DISTINCT ID) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> 1)) |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("test the result for max() with distinct operator in presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT MAX(DISTINCT ID) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> 9)) |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("test the result for count()clause with distinct operator on decimal column in presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT COUNT(DISTINCT BONUS) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> 10)) |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("test the result for count()clause with out distinct operator on decimal column in presto") |
| { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT COUNT(BONUS) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> 10)) |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("test the result for sum()with out distinct operator for decimal column in presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT SUM(DISTINCT BONUS) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> 20774.6475)) |
| assert(actualResult.toString().equals(expectedResult.toString())) |
| } |
| test("test the result for sum() with distinct operator for decimal column in presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT SUM(DISTINCT BONUS) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> 20774.6475)) |
| assert( |
| actualResult.head("RESULT").toString.toDouble == |
| expectedResult.head("RESULT").toString.toDouble) |
| } |
| test("test the result for avg() with distinct operator on decimal on presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT AVG(DISTINCT BONUS) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> 2077.4648)) |
| assert(actualResult.toString.equals(expectedResult.toString)) |
| } |
| |
| test("test the result for min() with distinct operator in decimalType of presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT MIN(BONUS) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map( |
| "RESULT" -> java.math.BigDecimal.valueOf(500.414).setScale(4))) |
| assert(actualResult.equals(expectedResult)) |
| } |
| |
| test("test the result for max() with distinct operator in decimalType of presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT MAX(BONUS) AS RESULT FROM TESTDB.TESTTABLE ") |
| val expectedResult: List[Map[String, Any]] = List(Map( |
| "RESULT" -> java.math.BigDecimal.valueOf(9999.999).setScale(4))) |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("select decimal data type with ORDER BY clause") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT DISTINCT BONUS FROM TESTDB.TESTTABLE ORDER BY BONUS limit 3 ") |
| val expectedResult: List[Map[String, Any]] = List(Map( |
| "BONUS" -> java.math.BigDecimal.valueOf(500.414).setScale(4)), |
| Map("BONUS" -> java.math.BigDecimal.valueOf(500.59).setScale(4)), |
| Map("BONUS" -> java.math.BigDecimal.valueOf(500.88).setScale(4))) |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("select string type with order by clause") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT NAME FROM TESTDB.TESTTABLE ORDER BY NAME") |
| val expectedResult: List[Map[String, Any]] = List(Map("NAME" -> "akash"), |
| Map("NAME" -> "anubhav"), |
| Map("NAME" -> "bhavya"), |
| Map("NAME" -> "geetika"), |
| Map("NAME" -> "jatin"), |
| Map("NAME" -> "jitesh"), |
| Map("NAME" -> "liang"), |
| Map("NAME" -> "prince"), |
| Map("NAME" -> "ravindra"), |
| Map("NAME" -> "sahil"), |
| Map("NAME" -> null) |
| ) |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("select DATE type with order by clause") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT DATE FROM TESTDB.TESTTABLE ORDER BY DATE") |
| val expectedResult: List[Map[String, Any]] = List(Map("DATE" -> "2015-07-18"), |
| Map("DATE" -> "2015-07-23"), |
| Map("DATE" -> "2015-07-24"), |
| Map("DATE" -> "2015-07-25"), |
| Map("DATE" -> "2015-07-26"), |
| Map("DATE" -> "2015-07-27"), |
| Map("DATE" -> "2015-07-28"), |
| Map("DATE" -> "2015-07-29"), |
| Map("DATE" -> "2015-07-30"), |
| Map("DATE" -> null), |
| Map("DATE" -> null) |
| ) |
| |
| assert(actualResult.filterNot(_.get("DATE") == null).zipWithIndex.forall { |
| case (map, index) => map.get("DATE").toString |
| .equals(expectedResult(index).get("DATE").toString) |
| }) |
| assert(actualResult.reverse.head("DATE") == null) |
| } |
| test("select int type with order by clause") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT DISTINCT ID FROM TESTDB.TESTTABLE ORDER BY ID") |
| val expectedResult: List[Map[String, Any]] = List(Map("ID" -> 1), |
| Map("ID" -> 2), |
| Map("ID" -> 3), |
| Map("ID" -> 4), |
| Map("ID" -> 5), |
| Map("ID" -> 6), |
| Map("ID" -> 7), |
| Map("ID" -> 8), |
| Map("ID" -> 9), |
| Map("ID" -> null) |
| ) |
| |
| assert(actualResult.equals(expectedResult)) |
| |
| } |
| |
| test("test and filter clause with greater than expression") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery( |
| "SELECT ID,DATE,COUNTRY,NAME,PHONETYPE,SERIALNAME,SALARY,BONUS FROM TESTDB.TESTTABLE " + |
| "WHERE BONUS>1234 AND ID>2 GROUP BY ID,DATE,COUNTRY,NAME,PHONETYPE,SERIALNAME,SALARY," + |
| "BONUS ORDER BY ID") |
| val expectedResult: List[Map[String, Any]] = List(Map("ID" -> 4, |
| "NAME" -> "prince", |
| "BONUS" -> java.math.BigDecimal.valueOf(9999.9990).setScale(4), |
| "DATE" -> "2015-07-26", |
| "SALARY" -> 15003.0, |
| "SERIALNAME" -> "ASD66902", |
| "COUNTRY" -> "china", |
| "PHONETYPE" -> "phone2435"), |
| Map("ID" -> 5, |
| "NAME" -> "bhavya", |
| "BONUS" -> java.math.BigDecimal.valueOf(5000.999).setScale(4), |
| "DATE" -> "2015-07-27", |
| "SALARY" -> 15004.0, |
| "SERIALNAME" -> "ASD90633", |
| "COUNTRY" -> "china", |
| "PHONETYPE" -> "phone2441")) |
| assert(actualResult.toString() equals expectedResult.toString()) |
| |
| |
| } |
| |
| test("test and filter clause with greater than equal to expression") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery( |
| "SELECT ID,DATE,COUNTRY,NAME,PHONETYPE,SERIALNAME,SALARY,BONUS FROM TESTDB.TESTTABLE " + |
| "WHERE BONUS>=1234.444 GROUP BY ID,DATE,COUNTRY,NAME,PHONETYPE,SERIALNAME,SALARY," + |
| "BONUS ORDER BY ID") |
| val expectedResult: List[Map[String, Any]] = List(Map("ID" -> 1, |
| "NAME" -> "anubhav", |
| "BONUS" -> java.math.BigDecimal.valueOf(1234.4440).setScale(4), |
| "DATE" -> "2015-07-23", |
| "SALARY" -> "5000000.0", |
| "SERIALNAME" -> "ASD69643", |
| "COUNTRY" -> "china", |
| "PHONETYPE" -> "phone197"), |
| Map("ID" -> 2, |
| "NAME" -> "jatin", |
| "BONUS" -> java.math.BigDecimal.valueOf(1234.5555).setScale(4) |
| , |
| "DATE" -> "2015-07-24", |
| "SALARY" -> java.math.BigDecimal.valueOf(150010.9990).setScale(3), |
| "SERIALNAME" -> "ASD42892", |
| "COUNTRY" -> "china", |
| "PHONETYPE" -> "phone756"), |
| Map("ID" -> 4, |
| "NAME" -> "prince", |
| "BONUS" -> java.math.BigDecimal.valueOf(9999.9990).setScale(4), |
| "DATE" -> "2015-07-26", |
| "SALARY" -> java.math.BigDecimal.valueOf(15003.0).setScale(1), |
| "SERIALNAME" -> "ASD66902", |
| "COUNTRY" -> "china", |
| "PHONETYPE" -> "phone2435"), |
| Map("ID" -> 5, |
| "NAME" -> "bhavya", |
| "BONUS" -> java.math.BigDecimal.valueOf(5000.9990).setScale(4), |
| "DATE" -> "2015-07-27", |
| "SALARY" -> java.math.BigDecimal.valueOf(15004.0).setScale(1), |
| "SERIALNAME" -> "ASD90633", |
| "COUNTRY" -> "china", |
| "PHONETYPE" -> "phone2441")) |
| assert(actualResult.toString() equals expectedResult.toString()) |
| } |
| test("test and filter clause with less than equal to expression") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery( |
| "SELECT ID,DATE,COUNTRY,NAME,PHONETYPE,SERIALNAME,SALARY,BONUS FROM TESTDB.TESTTABLE " + |
| "WHERE BONUS<=1234.444 GROUP BY ID,DATE,COUNTRY,NAME,PHONETYPE,SERIALNAME,SALARY," + |
| "BONUS ORDER BY ID LIMIT 2") |
| |
| val expectedResult: List[Map[String, Any]] = List(Map("ID" -> 1, |
| "NAME" -> "anubhav", |
| "BONUS" -> java.math.BigDecimal.valueOf(1234.4440).setScale(4), |
| "DATE" -> "2015-07-23", |
| "SALARY" -> "5000000.0", |
| "SERIALNAME" -> "ASD69643", |
| "COUNTRY" -> "china", |
| "PHONETYPE" -> "phone197"), |
| Map("ID" -> 3, |
| "NAME" -> "liang", |
| "BONUS" -> java.math.BigDecimal.valueOf(600.7770).setScale(4), |
| "DATE" -> "2015-07-25", |
| "SALARY" -> java.math.BigDecimal.valueOf(15002.11).setScale(2), |
| "SERIALNAME" -> "ASD37014", |
| "COUNTRY" -> "china", |
| "PHONETYPE" -> "phone1904")) |
| assert(actualResult.toString() equals expectedResult.toString()) |
| } |
| test("test equal to expression on decimal value") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery( |
| "SELECT ID FROM TESTDB.TESTTABLE WHERE BONUS=1234.444") |
| |
| val expectedResult: List[Map[String, Any]] = List(Map("ID" -> 1)) |
| |
| assert(actualResult equals expectedResult) |
| } |
| test("test less than expression with and operator") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery( |
| "SELECT ID,DATE,COUNTRY,NAME,PHONETYPE,SERIALNAME,SALARY,BONUS FROM TESTDB.TESTTABLE " + |
| "WHERE BONUS>1234 AND ID<2 GROUP BY ID,DATE,COUNTRY,NAME,PHONETYPE,SERIALNAME,SALARY," + |
| "BONUS ORDER BY ID") |
| val expectedResult: List[Map[String, Any]] = List(Map("ID" -> 1, |
| "NAME" -> "anubhav", |
| "BONUS" -> java.math.BigDecimal.valueOf(1234.4440).setScale(4), |
| "DATE" -> "2015-07-23", |
| "SALARY" -> 5000000.0, |
| "SERIALNAME" -> "ASD69643", |
| "COUNTRY" -> "china", |
| "PHONETYPE" -> "phone197")) |
| assert(actualResult.toString().equals(expectedResult.toString())) |
| } |
| test("test the result for in clause") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT NAME from testdb.testtable WHERE PHONETYPE IN('phone1848','phone706')") |
| val expectedResult: List[Map[String, Any]] = List( |
| Map("NAME" -> "geetika"), |
| Map("NAME" -> "ravindra"), |
| Map("NAME" -> "jitesh")) |
| |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("test the result for not in clause") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery( |
| "SELECT NAME from testdb.testtable WHERE PHONETYPE NOT IN('phone1848','phone706')") |
| val expectedResult: List[Map[String, Any]] = List(Map("NAME" -> "anubhav"), |
| Map("NAME" -> "jatin"), |
| Map("NAME" -> "liang"), |
| Map("NAME" -> "prince"), |
| Map("NAME" -> "bhavya"), |
| Map("NAME" -> "akash"), |
| Map("NAME" -> "sahil")) |
| |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("test for null operator on date data type") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT ID FROM TESTDB.TESTTABLE WHERE DATE IS NULL") |
| val expectedResult: List[Map[String, Any]] = List(Map("ID" -> 9),Map("ID" -> null)) |
| assert(actualResult.equals(expectedResult)) |
| |
| } |
| test("test for not null operator on date data type") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT NAME FROM TESTDB.TESTTABLE WHERE DATE IS NOT NULL AND ID=9") |
| val expectedResult: List[Map[String, Any]] = List(Map("NAME" -> "ravindra")) |
| assert(actualResult.equals(expectedResult)) |
| |
| } |
| test("test for not null operator on timestamp type") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT NAME FROM TESTDB.TESTTABLE WHERE DOB IS NOT NULL AND ID=9") |
| val expectedResult: List[Map[String, Any]] = List(Map("NAME" -> "ravindra"), |
| Map("NAME" -> "jitesh")) |
| assert(actualResult.equals(expectedResult)) |
| |
| } |
| test("test for null operator on timestamp type") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT NAME FROM TESTDB.TESTTABLE WHERE DOB IS NULL AND ID=1") |
| val expectedResult: List[Map[String, Any]] = List(Map("NAME" -> "anubhav")) |
| assert(actualResult.equals(expectedResult)) |
| |
| } |
| test("test the result for short datatype with order by clause") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery( |
| "SELECT DISTINCT SHORTFIELD from testdb.testtable ORDER BY SHORTFIELD ") |
| val expectedResult: List[Map[String, Any]] = List(Map("SHORTFIELD" -> 1), |
| Map("SHORTFIELD" -> 4), |
| Map("SHORTFIELD" -> 8), |
| Map("SHORTFIELD" -> 10), |
| Map("SHORTFIELD" -> 11), |
| Map("SHORTFIELD" -> 12), |
| Map("SHORTFIELD" -> 18), |
| Map("SHORTFIELD" -> null)) |
| |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("test the result for short datatype in clause where field is null") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery( |
| "SELECT ID from testdb.testtable WHERE SHORTFIELD IS NULL ORDER BY SHORTFIELD ") |
| val expectedResult: List[Map[String, Any]] = List(Map("ID" -> 7),Map("ID" -> null)) |
| |
| assert(actualResult.equals(expectedResult)) |
| } |
| test("test the result for short datatype with greater than operator") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery( |
| "SELECT ID from testdb.testtable WHERE SHORTFIELD>11 ") |
| val expectedResult: List[Map[String, Any]] = List(Map("ID" -> 6), Map("ID" -> 9)) |
| |
| assert(actualResult.equals(expectedResult)) |
| } |
| |
| test("test longDecimal type of presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery( |
| "SELECT ID from testdb.testtable WHERE bonus = DECIMAL '1234.5555'") |
| val expectedResult: List[Map[String, Any]] = List(Map("ID" -> 2)) |
| |
| assert(actualResult.equals(expectedResult)) |
| } |
| |
| test("test shortDecimal type of presto") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery( |
| "SELECT ID from testdb.testtable WHERE monthlyBonus = 15.13") |
| val expectedResult: List[Map[String, Any]] = List(Map("ID" -> 2)) |
| |
| assert(actualResult.equals(expectedResult)) |
| } |
| |
| test("test timestamp datatype using cast operator") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT NAME AS RESULT FROM TESTDB.TESTTABLE WHERE DOB = CAST('2016-04-14 15:00:09' AS TIMESTAMP)") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> "jatin")) |
| assert(actualResult.equals(expectedResult)) |
| } |
| |
| test("test timestamp datatype using cast and in operator") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT ID AS RESULT FROM TESTDB.TESTTABLE WHERE DOB in (cast('2016-04-14 " + |
| "15:00:09' as timestamp),cast('2015-10-07' as timestamp),cast('2015-10-07 01:00:03' as timestamp))") |
| val expectedResult: List[Map[String, Any]] = List(Map("RESULT" -> "2")) |
| assert(actualResult.toString() equals expectedResult.toString()) |
| } |
| test("test the boolean data type") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT isCurrentEmployee AS RESULT FROM TESTDB.TESTTABLE WHERE ID=1") |
| assert(actualResult.head("RESULT").toString.toBoolean) |
| } |
| test("test the boolean data type for null value") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT id AS RESULT FROM TESTDB.TESTTABLE WHERE isCurrentEmployee is null") |
| assert(actualResult.head("RESULT").toString.toInt==2) |
| } |
| test("test the boolean data type for not null value with filter ") { |
| val actualResult: List[Map[String, Any]] = prestoServer |
| .executeQuery("SELECT id AS RESULT FROM TESTDB.TESTTABLE WHERE isCurrentEmployee is NOT null AND ID>8") |
| assert(actualResult.head("RESULT").toString.toInt==9) |
| } |
| |
| test("test the show schemas result"){ |
| val actualResult = prestoServer.executeQuery("SHOW SCHEMAS") |
| assert(actualResult.equals(List(Map("Schema" -> "information_schema"), Map("Schema" -> "testdb")))) |
| } |
| test("test the show tables"){ |
| val actualResult = prestoServer.executeQuery("SHOW TABLES") |
| assert(actualResult.equals(List(Map("Table" -> "testtable")))) |
| } |
| |
| private def cleanUp(): Unit = { |
| FileFactory.deleteFile(s"$storePath/Fact") |
| FileFactory |
| .createDirectoryAndSetPermission(s"$storePath/_system", |
| new FsPermission(FsAction.ALL, FsAction.ALL, FsAction.ALL)) |
| FileFactory |
| .createDirectoryAndSetPermission(s"$storePath/.DS_Store", |
| new FsPermission(FsAction.ALL, FsAction.ALL, FsAction.ALL)) |
| FileFactory.createNewFile(s"$storePath/testdb/.DS_STORE") |
| } |
| |
| test("test the OR operator on same column"){ |
| val actualResult: List[Map[String, Any]] = prestoServer.executeQuery("SELECT BONUS FROM TESTDB.TESTTABLE WHERE" + |
| " BONUS < 600 OR BONUS > 5000 ORDER BY BONUS") |
| val expectedResult: List[Map[String, Any]] = List( |
| Map("BONUS" -> java.math.BigDecimal.valueOf(500.4140).setScale(4)), |
| Map("BONUS" -> java.math.BigDecimal.valueOf(500.5900).setScale(4)), |
| Map("BONUS" -> java.math.BigDecimal.valueOf(500.8800).setScale(4)), |
| Map("BONUS" -> java.math.BigDecimal.valueOf(500.9900).setScale(4)), |
| Map("BONUS" -> java.math.BigDecimal.valueOf(5000.9990).setScale(4)), |
| Map("BONUS" -> java.math.BigDecimal.valueOf(9999.9990).setScale(4))) |
| assert(actualResult.equals(expectedResult)) |
| } |
| |
| test("test the AND, OR operator on same column"){ |
| val actualResult: List[Map[String, Any]] = prestoServer.executeQuery("SELECT SHORTFIELD FROM TESTDB.TESTTABLE WHERE" + |
| " SHORTFIELD > 4 AND (SHORTFIELD < 10 or SHORTFIELD > 15) ORDER BY SHORTFIELD") |
| val expectedResult: List[Map[String, Any]] = List( |
| Map("SHORTFIELD" -> 8), |
| Map("SHORTFIELD" -> 18)) |
| assert(actualResult.equals(expectedResult)) |
| } |
| |
| test("test the OR operator with multiple AND on same column"){ |
| val actualResult: List[Map[String, Any]] = prestoServer.executeQuery("SELECT SHORTFIELD FROM TESTDB.TESTTABLE WHERE" + |
| " (SHORTFIELD > 1 AND SHORTFIELD < 5) OR (SHORTFIELD > 10 AND SHORTFIELD < 15) ORDER BY SHORTFIELD") |
| val expectedResult: List[Map[String, Any]] = List( |
| Map("SHORTFIELD" -> 4), |
| Map("SHORTFIELD" -> 11), |
| Map("SHORTFIELD" -> 12)) |
| assert(actualResult.equals(expectedResult)) |
| } |
| |
| test("test the OR, AND operator with on Different column"){ |
| val actualResult: List[Map[String, Any]] = prestoServer.executeQuery("SELECT SHORTFIELD FROM TESTDB.TESTTABLE WHERE" + |
| " ID < 7 AND (SHORTFIELD < 5 OR SHORTFIELD > 15) ORDER BY SHORTFIELD") |
| val expectedResult: List[Map[String, Any]] = List( |
| Map("SHORTFIELD" -> 4), |
| Map("SHORTFIELD" -> 18)) |
| assert(actualResult.equals(expectedResult)) |
| } |
| |
| test("test the Timestamp greaterthan expression"){ |
| val actualResult: List[Map[String, Any]] = prestoServer.executeQuery("SELECT DOB FROM TESTDB.TESTTABLE" + |
| " WHERE DOB > timestamp '2016-01-01 00:00:00.0' order by DOB") |
| val expectedResult: List[Map[String, Any]] = List( |
| Map("DOB" -> new Timestamp(new java.util.Date(2016-1900,1-1,14,15,7,9).getTime)), |
| Map("DOB" -> new Timestamp(new java.util.Date(2016-1900,4-1,14,15,0,9).getTime))) |
| assert(actualResult.equals(expectedResult)) |
| } |
| |
| |
| } |
