testdata/workloads/functional-query/queries/QueryTest/hbase-inserts.test

====
---- QUERY
insert into table insertalltypesagg
select id, bigint_col, cast(string_col as binary), bool_col, date_string_col, day, double_col, float_col,
int_col, month, smallint_col, string_col, timestamp_col, tinyint_col, year from functional.alltypesagg
---- RUNTIME_PROFILE
NumModifiedRows: 11000
====
---- QUERY
select id, bool_col from insertalltypesagg
WHERE id > 300
ORDER BY id
LIMIT 2
---- RESULTS
301,false
302,true
---- TYPES
INT, BOOLEAN
====
---- QUERY
insert into table insertalltypesagg
select 9999999, bigint_col, cast(string_col as binary), false, date_string_col, day, double_col, float_col,
int_col, month, smallint_col, string_col, timestamp_col, tinyint_col, year from functional.alltypesagg
---- RUNTIME_PROFILE
NumModifiedRows: 11000
====
---- QUERY
select id, bool_col from insertalltypesagg
WHERE id = 9999999
ORDER BY id
LIMIT 2
---- RESULTS
9999999,false
---- TYPES
INT, BOOLEAN
====
---- QUERY
# test insert into ... select *
# using limit 1 to reduce execution time
insert into table insertalltypesagg
select * from insertalltypesagg limit 1
---- RUNTIME_PROFILE
NumModifiedRows: 1
====
---- QUERY
# test inserting Hive's default text representation of NULL '\N'
# and make sure a scan returns the string and not NULL
insert into table insertalltypesagg
select 9999999, bigint_col, cast("\\N" as binary), false, "\\N", day, double_col, float_col,
int_col, month, smallint_col, "\\N", timestamp_col, tinyint_col, year from functional.alltypesagg limit 1
---- RUNTIME_PROFILE
NumModifiedRows: 1
====
---- QUERY
select id, date_string_col, string_col, binary_col from insertalltypesagg
where id = 9999999
---- RESULTS
9999999,'\\N','\\N','\\N'
---- TYPES
INT, STRING, STRING, BINARY
====
---- QUERY
insert into table insertalltypesaggbinary
select id, bigint_col, cast(string_col as binary), bool_col, date_string_col, day, double_col, float_col,
int_col, month, smallint_col, string_col, timestamp_col, tinyint_col, year from functional.alltypesagg
---- RUNTIME_PROFILE
NumModifiedRows: 11000
====
---- QUERY
select count(*) from (
  select hb.* from insertalltypesaggbinary hb, functional.alltypesagg a
  where hb.id = a.id
    and (hb.bigint_col = a.bigint_col or
         (hb.bigint_col is null and a.bigint_col is null))
    and (hb.bool_col = a.bool_col or
         (hb.bool_col is null and a.bool_col is null))
    and (hb.date_string_col = a.date_string_col or
         (hb.date_string_col is null and a.date_string_col is null))
    and (hb.double_col = a.double_col or
         (hb.double_col is null and a.double_col is null))
    and (hb.float_col = a.float_col or
         (hb.float_col is null and a.float_col is null))
    and (hb.int_col = a.int_col or
         (hb.int_col is null and a.int_col is null))
    and (hb.smallint_col = a.smallint_col or
         (hb.smallint_col is null and a.smallint_col is null))
    and (hb.tinyint_col = a.tinyint_col or
         (hb.tinyint_col is null and a.tinyint_col is null))
    and (hb.string_col = a.string_col or
         (hb.string_col is null and a.string_col is null))
    and (hb.timestamp_col = a.timestamp_col or
         (hb.timestamp_col is null and a.timestamp_col is null))
  ) x
---- RESULTS
11000
---- TYPES
BIGINT
====
---- QUERY
select id, bool_col from insertalltypesaggbinary
WHERE id > 300
ORDER BY id
LIMIT 2
---- RESULTS
301,false
302,true
---- TYPES
INT, BOOLEAN
====
---- QUERY
insert into table insertalltypesaggbinary
select 9999999, bigint_col,  cast(string_col as binary), false, date_string_col, day, double_col, float_col,
int_col, month, smallint_col, string_col, timestamp_col, tinyint_col, year from functional.alltypesagg
---- RUNTIME_PROFILE
NumModifiedRows: 11000
====
---- QUERY
select id, bool_col from insertalltypesaggbinary
WHERE id = 9999999
ORDER BY id
LIMIT 2
---- RESULTS
9999999,false
---- TYPES
INT, BOOLEAN
====
---- QUERY
# test insert into ... select *
# using limit 1 to reduce execution time
insert into table insertalltypesaggbinary
select * from insertalltypesaggbinary limit 1
---- RUNTIME_PROFILE
NumModifiedRows: 1
====
---- QUERY
# test inserting Hive's default text representation of NULL '\N'
# and make sure a scan returns the string and not NULL
insert into table insertalltypesaggbinary
select 9999999, bigint_col,  cast("\\N" as binary), false, "\\N", day, double_col, float_col,
int_col, month, smallint_col, "\\N", timestamp_col, tinyint_col, year from functional.alltypesagg limit 1
---- RUNTIME_PROFILE
NumModifiedRows: 1
====
---- QUERY
select id, date_string_col, string_col, binary_col from insertalltypesaggbinary
where id = 9999999
---- RESULTS
9999999,'\\N','\\N','\\N'
---- TYPES
INT, STRING, STRING, BINARY
====
---- QUERY
#IMPALA-715 handle large string value
insert into table insertalltypesagg(id, string_col) values(9999999, rpad('a', 50000, 'b'))
---- RUNTIME_PROFILE
NumModifiedRows: 1
====
---- QUERY
select id, length(string_col) from insertalltypesagg
WHERE id = 9999999
---- RESULTS
9999999,50000
---- TYPES
INT, INT
====
---- QUERY
# IMPALA-2133
insert into table insertalltypesagg (id, string_col) values (99999999, 'William\'s'), (999999999, "Other\"s")
---- RUNTIME_PROFILE
NumModifiedRows: 2
====
---- QUERY
select id, string_col from insertalltypesagg where id = 99999999
---- RESULTS
99999999,'William''s'
---- TYPES
INT, STRING
====
---- QUERY
select id, string_col from insertalltypesagg where string_col = 'William\'s'
---- RESULTS
99999999,'William''s'
---- TYPES
INT, STRING
====
---- QUERY
select id, string_col from insertalltypesagg where string_col = "Other\"s"
---- RESULTS
999999999,'Other"s'
---- TYPES
INT, STRING
====
---- QUERY
insert into table insert_date_tbl
select id_col, date_col, date_part
from functional.date_tbl
---- RUNTIME_PROFILE
NumModifiedRows: 22
====
---- QUERY
select id_col, date_col from insert_date_tbl
WHERE id_col > 20
ORDER BY id_col
LIMIT 2
---- RESULTS
21,0001-06-22
22,0001-06-23
---- TYPES
INT, DATE
====
---- QUERY
insert into table insert_date_tbl
select 9999999, date_col, '1521-12-13'
from functional.date_tbl
---- RUNTIME_PROFILE
NumModifiedRows: 22
====
---- QUERY
select id_col, date_part from insert_date_tbl
WHERE id_col = 9999999
ORDER BY id_col
LIMIT 2
---- RESULTS
9999999,1521-12-13
---- TYPES
INT, DATE
====
---- QUERY
# test insert into ... select *
# using limit 1 to reduce execution time
insert into table insert_date_tbl
select * from insert_date_tbl limit 1
---- RUNTIME_PROFILE
NumModifiedRows: 1
====
---- QUERY
# Insert special characters to binary_col.
insert into table insertalltypesagg (id, binary_col) values (99999999, cast(unhex('00112233445566778899AABBCCDDEEFF') as binary))
---- RUNTIME_PROFILE
NumModifiedRows: 1
====
---- QUERY
select id, hex(cast(binary_col as string)) from insertalltypesagg where id = 99999999
---- RESULTS
99999999,'00112233445566778899AABBCCDDEEFF'
---- TYPES
INT, STRING
====