@Test public void testHiveSelectQueryWithOrderByDescAndHighNullsWithVersion20() { final HiveSqlDialect hive2_1_0_Dialect = new HiveSqlDialect(SqlDialect.EMPTY_CONTEXT .withDatabaseMajorVersion(2) .withDatabaseMinorVersion(0) .withNullCollation(NullCollation.LOW)); final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls first"; final String expected = "SELECT product_id\n" + "FROM foodmart.product\n" + "ORDER BY product_id IS NULL DESC, product_id DESC"; sql(query).dialect(hive2_1_0_Dialect).ok(expected); }
@Test public void testMySqlWithFirstNullsSelectWithOrderByDescAndNullsLastShouldBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls last"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` IS NULL, `product_id` DESC"; sql(query).dialect(mySqlDialect(NullCollation.FIRST)).ok(expected); }
@Test public void testMySqlSelectQueryWithOrderByDescNullsLastShouldNotAddNullEmulation() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls last"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` DESC"; sql(query).dialect(MysqlSqlDialect.DEFAULT).ok(expected); }
@Test public void testMySqlSelectQueryWithOrderByAscNullsFirstShouldNotAddNullEmulation() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls first"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id`"; sql(query).dialect(MysqlSqlDialect.DEFAULT).ok(expected); }
@Test public void testHiveSelectQueryWithOrderByDescAndNullsFirstShouldBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls first"; final String expected = "SELECT product_id\n" + "FROM foodmart.product\n" + "ORDER BY product_id IS NULL DESC, product_id DESC"; sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected); }
@Test public void testMySqlWithLastNullsSelectWithOrderByAscAndNullsLastShouldNotBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls last"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id`"; sql(query).dialect(mySqlDialect(NullCollation.LAST)).ok(expected); }
@Test public void testMySqlWithFirstNullsSelectWithOrderByDescAndNullsLastShouldBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls last"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` IS NULL, `product_id` DESC"; sql(query).dialect(mySqlDialect(NullCollation.FIRST)).ok(expected); }
@Test public void testMySqlWithHighNullsSelectWithOrderByDescNullsLastAndNullEmulation() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls last"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` IS NULL, `product_id` DESC"; sql(query).dialect(mySqlDialect(NullCollation.HIGH)).ok(expected); }
@Test public void testMySqlWithHighNullsSelectWithOrderByAscNullsFirstAndNullEmulation() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls first"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` IS NULL DESC, `product_id`"; sql(query).dialect(mySqlDialect(NullCollation.HIGH)).ok(expected); }
@Test public void testJethroDataSelectQueryWithOrderByDescAndNullsFirstShouldBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls first"; final String expected = "SELECT \"product_id\"\n" + "FROM \"foodmart\".\"product\"\n" + "ORDER BY \"product_id\", \"product_id\" DESC"; sql(query).dialect(jethroDataSqlDialect()).ok(expected); }
@Test public void testHiveSelectQueryWithOrderByAscAndNullsLastShouldBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls last"; final String expected = "SELECT product_id\n" + "FROM foodmart.product\n" + "ORDER BY product_id IS NULL, product_id"; sql(query).dialect(HiveSqlDialect.DEFAULT).ok(expected); }
@Test public void testMySqlWithHighNullsSelectWithOrderByAscNullsFirstAndNullEmulation() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls first"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` IS NULL DESC, `product_id`"; sql(query).dialect(mySqlDialect(NullCollation.HIGH)).ok(expected); }
@Test public void testMySqlWithHighNullsSelectWithOrderByAscNullsLastAndNoEmulation() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls last"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id`"; sql(query).dialect(mySqlDialect(NullCollation.HIGH)).ok(expected); }
@Test public void testMySqlWithLastNullsSelectWithOrderByAscAndNullsFirstShouldBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls first"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` IS NULL DESC, `product_id`"; sql(query).dialect(mySqlDialect(NullCollation.LAST)).ok(expected); }
@Test public void testMySqlSelectQueryWithOrderByDescNullsLastShouldNotAddNullEmulation() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls last"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` DESC"; sql(query).dialect(MysqlSqlDialect.DEFAULT).ok(expected); }
@Test public void testMySqlSelectQueryWithOrderByAscNullsFirstShouldNotAddNullEmulation() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls first"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id`"; sql(query).dialect(MysqlSqlDialect.DEFAULT).ok(expected); }
@Test public void testMySqlSelectQueryWithOrderByDescAndNullsFirstShouldBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls first"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` IS NULL DESC, `product_id` DESC"; sql(query).dialect(MysqlSqlDialect.DEFAULT).ok(expected); }
@Test public void testMySqlSelectQueryWithOrderByAscAndNullsLastShouldBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" nulls last"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` IS NULL, `product_id`"; sql(query).dialect(MysqlSqlDialect.DEFAULT).ok(expected); }
@Test public void testMySqlSelectQueryWithOrderByDescAndNullsFirstShouldBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls first"; final String expected = "SELECT `product_id`\n" + "FROM `foodmart`.`product`\n" + "ORDER BY `product_id` IS NULL DESC, `product_id` DESC"; sql(query).dialect(MysqlSqlDialect.DEFAULT).ok(expected); }
@Test public void testJethroDataSelectQueryWithOrderByDescAndNullsFirstShouldBeEmulated() { final String query = "select \"product_id\" from \"product\"\n" + "order by \"product_id\" desc nulls first"; final String expected = "SELECT \"product_id\"\n" + "FROM \"foodmart\".\"product\"\n" + "ORDER BY \"product_id\", \"product_id\" DESC"; sql(query).dialect(jethroDataSqlDialect()).ok(expected); }