testdata/workloads/functional-query/queries/QueryTest/chars.test

====
---- QUERY
select (cast("xyzzzzz12" as char(-1)))
---- CATCH
Syntax error
====
---- QUERY
select count(*), count(cs), count(cl), count(vc) from chars_tiny
---- TYPES
bigint,bigint,bigint,bigint
---- RESULTS
9,8,8,8
====
---- QUERY
select * from chars_tiny where cs = cast('6a' as CHAR(2))
---- TYPES
char,char,string
---- HS2_TYPES
char,char,varchar
---- RESULTS
'6a   ','6b                                                                                                                                          ','6c'
'6a   ','6b                                                                                                                                          ','6c'
====
---- QUERY
select count(*) from chars_tiny where vc != cast('5c' as varchar(3))
---- TYPES
bigint
---- RESULTS
7
====
---- QUERY
select count(*) from chars_tiny where cs != cast('a' as char(3))
---- TYPES
bigint
---- RESULTS
7
====
---- QUERY
select count(DISTINCT cs) from chars_tiny where vc = cast('5c' as varchar(10))
---- TYPES
bigint
---- RESULTS
1
====
---- QUERY
select count(DISTINCT cs) from chars_tiny where cs = cast('5a' as char(10))
---- TYPES
bigint
---- RESULTS
1
====
---- QUERY
select cs, count(cl) from chars_tiny group by cs having count(vc) > 1
---- TYPES
char, bigint
---- RESULTS
'6a   ',2
====
---- QUERY
select A.cs from chars_tiny as A, chars_tiny as B where
cast(A.cs as char(1)) = cast(B.cl as char(1)) order by A.cs
---- TYPES
char
---- RESULTS
'1aaaa'
'2aaaa'
'3aaa '
'4aa  '
'5a   '
'6a   '
'6a   '
'6a   '
'6a   '
====
---- QUERY
# Regression test for IMPALA-1316
select A.vc from chars_tiny as A join chars_tiny using (vc) order by A.vc
---- TYPES
string
---- HS2_TYPES
varchar
---- RESULTS
'1cccc'
'2cccccc'
'3ccc'
'4cc'
'5c'
'6c'
'6c'
'6c'
'6c'
'c'
====
---- QUERY
# Regression test for IMPALA-1322
select count(*) from chars_tiny as A, chars_tiny as B
where cast(A.cs as CHAR(1)) = cast(B.vc as CHAR(1));
---- TYPES
bigint
---- RESULTS
9
====
---- QUERY
select min(cs), max(vc), ndv(cl), ndv(vc), appx_median(cs), appx_median(vc)
from chars_tiny
---- TYPES
string, string, bigint, bigint, string, string
---- RESULTS
'1aaaa','c',7,7,'5a   ','5c'
====
---- QUERY
# Regression test for IMPALA-1316
select t1.vc, COUNT(1) FROM chars_tiny t1 GROUP BY 1 ORDER BY t1.vc
---- TYPES
string, bigint
---- HS2_TYPES
varchar, bigint
---- RESULTS
'1cccc',1
'2cccccc',1
'3ccc',1
'4cc',1
'5c',1
'6c',2
'c',1
'NULL',1
====
---- QUERY
# Regression test for IMPALA-1316
select t1.cl, COUNT(1) FROM chars_tiny t1 GROUP BY 1 ORDER BY t1.cl
---- TYPES
char, bigint
---- RESULTS
'1bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1
'2bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1
'3bbbbb                                                                                                                                      ',1
'4bbbb                                                                                                                                       ',1
'5bbb                                                                                                                                        ',1
'6b                                                                                                                                          ',2
'b                                                                                                                                           ',1
'NULL',1
====
---- QUERY
# Regression test for IMPALA-1344
select cs, LAST_VALUE(cs) OVER (ORDER BY cs rows between unbounded preceding and
current row) FROM chars_tiny;
---- TYPES
char, string
---- RESULTS
'1aaaa','1aaaa'
'2aaaa','2aaaa'
'3aaa ','3aaa '
'4aa  ','4aa  '
'5a   ','5a   '
'6a   ','6a   '
'6a   ','6a   '
'a    ','a    '
'NULL','NULL'
====
---- QUERY
# Test returning mix of nulls and non-nulls.
WITH numbered AS (
  SELECT *, row_number() over (order by cs) as rn
  FROM chars_tiny)
SELECT *
FROM (
  SELECT CASE WHEN rn % 2 = 0 THEN cs END cs,
    CASE WHEN rn % 2 = 1 THEN cl END cl,
    CASE WHEN rn % 3 = 0 THEN vc END vc
  FROM numbered
  UNION ALL
  SELECT CASE WHEN rn % 2 = 1 THEN cs END cs,
    CASE WHEN rn % 2 = 0 THEN cl END cl,
    CASE WHEN rn % 3 = 1 THEN vc END vc
  FROM numbered) v
---- TYPES
char, char, string
---- HS2_TYPES
char, char, varchar
---- RESULTS
'NULL','1bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb','NULL'
'2aaaa','NULL','NULL'
'NULL','3bbbbb                                                                                                                                      ','3ccc'
'4aa  ','NULL','NULL'
'NULL','5bbb                                                                                                                                        ','NULL'
'6a   ','NULL','6c'
'NULL','6b                                                                                                                                          ','NULL'
'a    ','NULL','NULL'
'NULL','NULL','NULL'
'1aaaa','NULL','1cccc'
'NULL','2bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb','NULL'
'3aaa ','NULL','NULL'
'NULL','4bbbb                                                                                                                                       ','4cc'
'5a   ','NULL','NULL'
'NULL','6b                                                                                                                                          ','NULL'
'6a   ','NULL','6c'
'NULL','b                                                                                                                                           ','NULL'
'NULL','NULL','NULL'
====
---- QUERY
# Scan non-NULL CHAR and VARCHAR from larger table.
select date_char_col, date_varchar_col, count(*)
from chars_medium
group by 1, 2
---- TYPES
char, string, bigint
---- HS2_TYPES
char, varchar, bigint
---- RESULTS
'01/01/10','01/01/10',1100
'01/02/10','01/02/10',1100
'01/03/10','01/03/10',1100
'01/10/10','01/10/10',1100
'01/08/10','01/08/10',1100
'01/07/10','01/07/10',1100
'01/04/10','01/04/10',1100
'01/09/10','01/09/10',1100
'01/06/10','01/06/10',1100
'01/05/10','01/05/10',1100
====
---- QUERY
# Scan CHAR and VARCHAR with NULLs from larger table.
select id, char_col, varchar_col
from chars_medium
where id in (3, 1000, 1234, 5678, 1111, 2222, 3333)
---- TYPES
int, char, string
---- HS2_TYPES
int, char, varchar
---- RESULTS
3,'3  ','3'
1000,'0  ','NULL'
1111,'111','NULL'
1234,'234','NULL'
2222,'NULL','NULL'
3333,'333','333'
5678,'NULL','NULL'
1000,'0  ','NULL'
====