| ==== |
| ---- QUERY |
| insert into table insertalltypesagg |
| select id, bigint_col, 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 |
| ---- RESULTS |
| : 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, false, date_string_col, day, double_col, float_col, |
| int_col, month, smallint_col, string_col, timestamp_col, tinyint_col, year from functional.alltypesagg |
| ---- RESULTS |
| : 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 |
| ---- RESULTS |
| : 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, false, "\\N", day, double_col, float_col, |
| int_col, month, smallint_col, "\\N", timestamp_col, tinyint_col, year from functional.alltypesagg limit 1 |
| ---- RESULTS |
| : 1 |
| ==== |
| ---- QUERY |
| select id, date_string_col, string_col from insertalltypesagg |
| where id = 9999999 |
| ---- RESULTS |
| 9999999,'\\N','\\N' |
| ---- TYPES |
| INT, STRING, STRING |
| ==== |
| ---- QUERY |
| insert into table insertalltypesaggbinary |
| select id, bigint_col, 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 |
| ---- RESULTS |
| : 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, false, date_string_col, day, double_col, float_col, |
| int_col, month, smallint_col, string_col, timestamp_col, tinyint_col, year from functional.alltypesagg |
| ---- RESULTS |
| : 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 |
| ---- RESULTS |
| : 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, false, "\\N", day, double_col, float_col, |
| int_col, month, smallint_col, "\\N", timestamp_col, tinyint_col, year from functional.alltypesagg limit 1 |
| ---- RESULTS |
| : 1 |
| ==== |
| ---- QUERY |
| select id, date_string_col, string_col from insertalltypesaggbinary |
| where id = 9999999 |
| ---- RESULTS |
| 9999999,'\\N','\\N' |
| ---- TYPES |
| INT, STRING, STRING |
| ==== |
| ---- QUERY |
| #IMPALA-715 handle large string value |
| insert into table insertalltypesagg(id, string_col) values(9999999, rpad('a', 50000, 'b')) |
| ---- RESULTS |
| : 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") |
| ---- RESULTS |
| : 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 |
| ==== |