| ==== |
| ---- QUERY |
| # Based on Aggregation Queries |
| select int_col, sum(float_col) |
| from alltypessmall |
| where id < 5 |
| group by 1 |
| order by 2 |
| limit 3 |
| ---- RESULTS |
| 0,0 |
| 1,1.100000023841858 |
| 2,2.200000047683716 |
| ---- TYPES |
| INT, DOUBLE |
| ==== |
| ---- QUERY |
| # Run query without order by |
| select tinyint_col, count(*) |
| from alltypesagg |
| group by 1 |
| limit 10 |
| ---- RESULTS |
| 3,1000 |
| NULL,2000 |
| 2,1000 |
| 4,1000 |
| 8,1000 |
| 1,1000 |
| 6,1000 |
| 5,1000 |
| 7,1000 |
| 9,1000 |
| ---- TYPES |
| TINYINT, BIGINT |
| ==== |
| ---- QUERY |
| # Same query order by asc first col |
| select tinyint_col, count(*) |
| from alltypesagg |
| group by 1 |
| order by 1 |
| limit 10 |
| ---- RESULTS |
| 1,1000 |
| 2,1000 |
| 3,1000 |
| 4,1000 |
| 5,1000 |
| 6,1000 |
| 7,1000 |
| 8,1000 |
| 9,1000 |
| NULL,2000 |
| ---- TYPES |
| TINYINT, BIGINT |
| ==== |
| ---- QUERY |
| # Same query order by asc first col, NULL should be last |
| # because it compares greater. |
| select tinyint_col, count(*) |
| from alltypesagg |
| group by 1 |
| order by 1 |
| limit 10 |
| ---- RESULTS |
| 1,1000 |
| 2,1000 |
| 3,1000 |
| 4,1000 |
| 5,1000 |
| 6,1000 |
| 7,1000 |
| 8,1000 |
| 9,1000 |
| NULL,2000 |
| ---- TYPES |
| TINYINT, BIGINT |
| ==== |
| ---- QUERY |
| # Same query order by asc first col, NULL should be first |
| select tinyint_col, count(*) |
| from alltypesagg |
| group by 1 |
| order by 1 nulls first |
| limit 10 |
| ---- RESULTS |
| NULL,2000 |
| 1,1000 |
| 2,1000 |
| 3,1000 |
| 4,1000 |
| 5,1000 |
| 6,1000 |
| 7,1000 |
| 8,1000 |
| 9,1000 |
| ---- TYPES |
| TINYINT, BIGINT |
| ==== |
| ---- QUERY |
| # Same query but first col desc. |
| # NULL should be first because it compares greater |
| select tinyint_col, count(*) |
| from alltypesagg |
| group by 1 |
| order by 1 desc |
| limit 20 |
| ---- RESULTS |
| NULL,2000 |
| 9,1000 |
| 8,1000 |
| 7,1000 |
| 6,1000 |
| 5,1000 |
| 4,1000 |
| 3,1000 |
| 2,1000 |
| 1,1000 |
| ---- TYPES |
| TINYINT, BIGINT |
| ==== |
| ---- QUERY |
| # Same query as above but NULL should be last |
| select tinyint_col, count(*) |
| from alltypesagg |
| group by 1 |
| order by 1 desc nulls last |
| limit 20 |
| ---- RESULTS |
| 9,1000 |
| 8,1000 |
| 7,1000 |
| 6,1000 |
| 5,1000 |
| 4,1000 |
| 3,1000 |
| 2,1000 |
| 1,1000 |
| NULL,2000 |
| ---- TYPES |
| TINYINT, BIGINT |
| ==== |
| ---- QUERY |
| select date_string_col,int_col |
| from alltypesagg |
| order by date_string_col, int_col desc |
| limit 10 |
| ---- RESULTS |
| '01/01/10',NULL |
| '01/01/10',NULL |
| '01/01/10',999 |
| '01/01/10',998 |
| '01/01/10',997 |
| '01/01/10',996 |
| '01/01/10',995 |
| '01/01/10',994 |
| '01/01/10',993 |
| '01/01/10',992 |
| ---- TYPES |
| STRING, INT |
| ==== |
| ---- QUERY |
| # order by with null tuples in tuple row |
| select j.*, d.* from JoinTbl j full outer join DimTbl d |
| on (j.test_id = d.id) |
| order by j.test_id, j.test_name, j.test_zip, j.alltypes_id, d.name |
| limit 100 |
| ---- RESULTS |
| 1001,'Name1',94611,5000,1001,'Name1',94611 |
| 1002,'Name2',94611,5000,1002,'Name2',94611 |
| 1003,'Name3',94611,5000,1003,'Name3',94612 |
| 1004,'Name4',94611,5000,1004,'Name4',94612 |
| 1005,'Name5',94611,5000,1005,'Name5',94613 |
| 1006,'Name16',94612,5000,1006,'Name6',94613 |
| 1006,'Name16',94612,15000,1006,'Name6',94613 |
| 1006,'Name16',94616,5000,1006,'Name6',94613 |
| 1006,'Name16',94616,15000,1006,'Name6',94613 |
| 1006,'Name6',94616,5000,1006,'Name6',94613 |
| 1006,'Name6',94616,15000,1006,'Name6',94613 |
| 1106,'Name16',94612,5000,NULL,'NULL',NULL |
| 1106,'Name16',94612,15000,NULL,'NULL',NULL |
| 1106,'Name16',94616,5000,NULL,'NULL',NULL |
| 1106,'Name16',94616,15000,NULL,'NULL',NULL |
| 1106,'Name6',94612,5000,NULL,'NULL',NULL |
| 1106,'Name6',94612,15000,NULL,'NULL',NULL |
| 1106,'Name6',94616,5000,NULL,'NULL',NULL |
| 1106,'Name6',94616,15000,NULL,'NULL',NULL |
| NULL,'NULL',NULL,NULL,1010,'Name10',94615 |
| NULL,'NULL',NULL,NULL,1007,'Name7',94614 |
| NULL,'NULL',NULL,NULL,1008,'Name8',94614 |
| NULL,'NULL',NULL,NULL,1009,'Name9',94615 |
| ---- TYPES |
| BIGINT, STRING, INT, INT, BIGINT, STRING, INT |
| ==== |
| ---- QUERY |
| # order by multiple cols with nulls |
| select tinyint_col % 3, smallint_col % 3, count(*) |
| from alltypesagg |
| where day = 1 |
| group by 1, 2 |
| order by 1, 2 |
| limit 20 |
| ---- RESULTS |
| 0,0,120 |
| 0,1,90 |
| 0,2,90 |
| 1,0,90 |
| 1,1,120 |
| 1,2,90 |
| 2,0,90 |
| 2,1,90 |
| 2,2,120 |
| NULL,0,30 |
| NULL,1,30 |
| NULL,2,30 |
| NULL,NULL,10 |
| ---- TYPES |
| TINYINT, SMALLINT, BIGINT |
| ==== |
| ---- QUERY |
| select tinyint_col % 3, smallint_col % 3, count(*) |
| from alltypesagg |
| where day = 1 |
| group by 1, 2 |
| order by 1, 2 desc |
| limit 20 |
| ---- RESULTS |
| 0,2,90 |
| 0,1,90 |
| 0,0,120 |
| 1,2,90 |
| 1,1,120 |
| 1,0,90 |
| 2,2,120 |
| 2,1,90 |
| 2,0,90 |
| NULL,NULL,10 |
| NULL,2,30 |
| NULL,1,30 |
| NULL,0,30 |
| ---- TYPES |
| TINYINT, SMALLINT, BIGINT |
| ==== |
| ---- QUERY |
| select tinyint_col % 3, smallint_col % 3, count(*) |
| from alltypesagg |
| where day = 1 |
| group by 1, 2 |
| order by 1 desc, 2 |
| limit 20 |
| ---- RESULTS |
| NULL,0,30 |
| NULL,1,30 |
| NULL,2,30 |
| NULL,NULL,10 |
| 2,0,90 |
| 2,1,90 |
| 2,2,120 |
| 1,0,90 |
| 1,1,120 |
| 1,2,90 |
| 0,0,120 |
| 0,1,90 |
| 0,2,90 |
| ---- TYPES |
| TINYINT, SMALLINT, BIGINT |
| ==== |
| ---- QUERY |
| select tinyint_col % 3, smallint_col % 3, count(*) |
| from alltypesagg |
| where day = 1 |
| group by 1, 2 |
| order by 1 desc, 2 desc |
| limit 20 |
| ---- RESULTS |
| NULL,NULL,10 |
| NULL,2,30 |
| NULL,1,30 |
| NULL,0,30 |
| 2,2,120 |
| 2,1,90 |
| 2,0,90 |
| 1,2,90 |
| 1,1,120 |
| 1,0,90 |
| 0,2,90 |
| 0,1,90 |
| 0,0,120 |
| ---- TYPES |
| TINYINT, SMALLINT, BIGINT |
| ==== |
| ---- QUERY |
| # Multiple ordering columns with asc/desc and nulls first/last |
| select tinyint_col % 3, smallint_col % 3, count(*) |
| from alltypesagg |
| where day = 1 |
| group by 1, 2 |
| order by 2 desc nulls last, 1 asc nulls first |
| limit 20 |
| ---- RESULTS |
| NULL,2,30 |
| 0,2,90 |
| 1,2,90 |
| 2,2,120 |
| NULL,1,30 |
| 0,1,90 |
| 1,1,120 |
| 2,1,90 |
| NULL,0,30 |
| 0,0,120 |
| 1,0,90 |
| 2,0,90 |
| NULL,NULL,10 |
| ---- TYPES |
| TINYINT, SMALLINT, BIGINT |
| ==== |
| ---- QUERY |
| select date_string_col |
| from alltypessmall |
| order by date_string_col desc |
| limit 50 |
| ---- RESULTS |
| '04/03/09' |
| '04/03/09' |
| '04/03/09' |
| '04/03/09' |
| '04/03/09' |
| '04/02/09' |
| '04/02/09' |
| '04/02/09' |
| '04/02/09' |
| '04/02/09' |
| '04/02/09' |
| '04/02/09' |
| '04/02/09' |
| '04/02/09' |
| '04/02/09' |
| '04/01/09' |
| '04/01/09' |
| '04/01/09' |
| '04/01/09' |
| '04/01/09' |
| '04/01/09' |
| '04/01/09' |
| '04/01/09' |
| '04/01/09' |
| '04/01/09' |
| '03/03/09' |
| '03/03/09' |
| '03/03/09' |
| '03/03/09' |
| '03/03/09' |
| '03/02/09' |
| '03/02/09' |
| '03/02/09' |
| '03/02/09' |
| '03/02/09' |
| '03/02/09' |
| '03/02/09' |
| '03/02/09' |
| '03/02/09' |
| '03/02/09' |
| '03/01/09' |
| '03/01/09' |
| '03/01/09' |
| '03/01/09' |
| '03/01/09' |
| '03/01/09' |
| '03/01/09' |
| '03/01/09' |
| '03/01/09' |
| '03/01/09' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| # Based on join queries |
| select a.tinyint_col, b.id, a.string_col |
| from alltypesagg a join alltypessmall b on (a.tinyint_col = b.id) |
| where a.month=1 |
| and a.day=1 |
| and a.tinyint_col + b.tinyint_col < 5 |
| and a.string_col > '88' |
| and b.bool_col = false |
| order by a.string_col |
| limit 5 |
| ---- RESULTS |
| 1,1,'881' |
| 1,1,'891' |
| 1,1,'901' |
| 1,1,'91' |
| 1,1,'911' |
| ---- TYPES |
| TINYINT, INT, STRING |
| ==== |
| ---- QUERY |
| select a.tinyint_col, b.id, a.string_col |
| from alltypesagg a join alltypessmall b on (a.tinyint_col = b.id) |
| where a.month=1 |
| and a.day=1 |
| and a.tinyint_col + b.tinyint_col < 5 |
| and a.string_col > '88' |
| and b.bool_col = false |
| order by a.string_col desc |
| limit 5 |
| ---- RESULTS |
| 1,1,'991' |
| 1,1,'981' |
| 1,1,'971' |
| 1,1,'961' |
| 1,1,'951' |
| ---- TYPES |
| TINYINT, INT, STRING |
| ==== |
| ---- QUERY |
| select a.smallint_col, b.id, a.tinyint_col, c.id, a.int_col, b.float_col, c.string_col |
| from alltypesagg a |
| join alltypessmall b on (a.smallint_col = b.id) |
| join alltypessmall c on (a.tinyint_col = c.id) |
| where a.month=1 |
| and a.day=1 |
| and a.int_col > 899 |
| and b.float_col > 4.5 |
| and c.string_col < '7' |
| and a.int_col + b.float_col + cast(c.string_col as float) < 1000 |
| order by c.string_col desc, a.smallint_col |
| limit 10 |
| ---- RESULTS |
| 6,6,6,6,906,6.599999904632568,'6' |
| 16,16,6,6,916,6.599999904632568,'6' |
| 56,56,6,6,956,6.599999904632568,'6' |
| 66,66,6,6,966,6.599999904632568,'6' |
| 5,5,5,5,905,5.5,'5' |
| 15,15,5,5,915,5.5,'5' |
| 55,55,5,5,955,5.5,'5' |
| 65,65,5,5,965,5.5,'5' |
| 34,34,4,4,934,9.899999618530273,'4' |
| 44,44,4,4,944,9.899999618530273,'4' |
| ---- TYPES |
| SMALLINT, INT, TINYINT, INT, INT, FLOAT, STRING |
| ==== |
| ---- QUERY |
| # Order by a column that is not in the select list |
| # Query with ordering column in select list |
| # Don't include date_string_col, it comes back in random order. |
| select int_col, tinyint_col |
| from alltypessmall |
| order by int_col desc |
| limit 20 |
| ---- RESULTS |
| 9,9 |
| 9,9 |
| 9,9 |
| 9,9 |
| 9,9 |
| 9,9 |
| 9,9 |
| 9,9 |
| 8,8 |
| 8,8 |
| 8,8 |
| 8,8 |
| 8,8 |
| 8,8 |
| 8,8 |
| 8,8 |
| 7,7 |
| 7,7 |
| 7,7 |
| 7,7 |
| ---- TYPES |
| INT, TINYINT |
| ==== |
| ---- QUERY |
| # Same query with ordering col not in select list |
| select tinyint_col |
| from alltypessmall |
| order by int_col desc |
| limit 20 |
| ---- RESULTS |
| 9 |
| 9 |
| 9 |
| 9 |
| 9 |
| 9 |
| 9 |
| 9 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 7 |
| 7 |
| 7 |
| 7 |
| ---- TYPES |
| TINYINT |
| ==== |
| ---- QUERY |
| # Order by many exprs |
| select year, month, count(*) |
| from alltypes |
| group by 1, 2 |
| order by 1, 2 |
| limit 100 |
| ---- RESULTS |
| 2009,1,310 |
| 2009,2,280 |
| 2009,3,310 |
| 2009,4,300 |
| 2009,5,310 |
| 2009,6,300 |
| 2009,7,310 |
| 2009,8,310 |
| 2009,9,300 |
| 2009,10,310 |
| 2009,11,300 |
| 2009,12,310 |
| 2010,1,310 |
| 2010,2,280 |
| 2010,3,310 |
| 2010,4,300 |
| 2010,5,310 |
| 2010,6,300 |
| 2010,7,310 |
| 2010,8,310 |
| 2010,9,300 |
| 2010,10,310 |
| 2010,11,300 |
| 2010,12,310 |
| ---- TYPES |
| INT, INT, BIGINT |
| ==== |
| ---- QUERY |
| # More Complex Ordering Exprs |
| select int_col % 7, count(*), avg(tinyint_col) |
| from alltypesagg |
| group by 1 |
| order by avg(tinyint_col) |
| limit 10 |
| ---- RESULTS |
| 4,1570,4.984496124031008 |
| 6,1560,4.9921875 |
| 1,1570,4.992248062015504 |
| 3,1580,5 |
| 5,1570,5.007751937984496 |
| 0,1560,5.0078125 |
| 2,1570,5.015503875968992 |
| NULL,20,NULL |
| ---- TYPES |
| INT, BIGINT, DOUBLE |
| ==== |
| ---- QUERY |
| select int_col % 7, count(*), max(int_col) |
| from alltypesagg |
| group by 1 |
| order by max(int_col) |
| limit 10 |
| ---- RESULTS |
| 6,1560,993 |
| 0,1560,994 |
| 1,1570,995 |
| 2,1570,996 |
| 3,1580,997 |
| 4,1570,998 |
| 5,1570,999 |
| NULL,20,NULL |
| ---- TYPES |
| INT, BIGINT, INT |
| ==== |
| ---- QUERY |
| select int_col % 5, count(*), avg(tinyint_col) - avg(float_col) |
| from alltypesagg |
| group by 1 |
| order by avg(tinyint_col) - avg(float_col) desc |
| limit 10 |
| ---- RESULTS |
| NULL,20,NULL |
| 1,2000,-544.8499889141322 |
| 2,2000,-544.9500045645237 |
| 0,2980,-545 |
| 3,2000,-545.0499953591824 |
| 4,2000,-545.1500110459327 |
| ---- TYPES |
| INT, BIGINT, DOUBLE |
| ==== |
| ---- QUERY |
| select int_col |
| from alltypessmall |
| order by int_col % 5, int_col |
| limit 100 |
| ---- RESULTS |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 5 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 1 |
| 6 |
| 6 |
| 6 |
| 6 |
| 6 |
| 6 |
| 6 |
| 6 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 2 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 7 |
| 3 |
| 3 |
| 3 |
| 3 |
| 3 |
| 3 |
| 3 |
| 3 |
| 3 |
| 3 |
| 3 |
| 3 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 8 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 9 |
| 9 |
| 9 |
| 9 |
| 9 |
| 9 |
| 9 |
| 9 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| # All select list items have an implicit alias. Test that the order by column ref |
| # "int_col" is correctly aliased to t1.int_col, and therefore it is not an |
| # ambiguous reference. |
| select t1.int_col from alltypessmall t1, alltypessmall t2 where t1.id = t2.id |
| order by int_col |
| limit 2 |
| ---- RESULTS |
| 0 |
| 0 |
| ---- TYPES |
| INT |
| ==== |
| ---- QUERY |
| select date_sub(timestamp_col, id), timestamp_col, id |
| from alltypessmall order by 1 limit 20 |
| ---- RESULTS |
| 2008-12-10 00:24:00.960000000,2009-01-03 00:24:00.960000000,24 |
| 2008-12-11 00:23:00.930000000,2009-01-03 00:23:00.930000000,23 |
| 2008-12-12 00:22:00.910000000,2009-01-03 00:22:00.910000000,22 |
| 2008-12-13 00:21:00.900000000,2009-01-03 00:21:00.900000000,21 |
| 2008-12-14 00:19:00.810000000,2009-01-02 00:19:00.810000000,19 |
| 2008-12-14 00:20:00.900000000,2009-01-03 00:20:00.900000000,20 |
| 2008-12-15 00:18:00.730000000,2009-01-02 00:18:00.730000000,18 |
| 2008-12-16 00:17:00.660000000,2009-01-02 00:17:00.660000000,17 |
| 2008-12-16 00:24:00.960000000,2009-02-03 00:24:00.960000000,49 |
| 2008-12-17 00:16:00.600000000,2009-01-02 00:16:00.600000000,16 |
| 2008-12-17 00:23:00.930000000,2009-02-03 00:23:00.930000000,48 |
| 2008-12-18 00:15:00.550000000,2009-01-02 00:15:00.550000000,15 |
| 2008-12-18 00:22:00.910000000,2009-02-03 00:22:00.910000000,47 |
| 2008-12-19 00:14:00.510000000,2009-01-02 00:14:00.510000000,14 |
| 2008-12-19 00:21:00.900000000,2009-02-03 00:21:00.900000000,46 |
| 2008-12-19 00:24:00.960000000,2009-03-03 00:24:00.960000000,74 |
| 2008-12-20 00:13:00.480000000,2009-01-02 00:13:00.480000000,13 |
| 2008-12-20 00:19:00.810000000,2009-02-02 00:19:00.810000000,44 |
| 2008-12-20 00:20:00.900000000,2009-02-03 00:20:00.900000000,45 |
| 2008-12-20 00:23:00.930000000,2009-03-03 00:23:00.930000000,73 |
| ---- DBAPI_RESULTS |
| 2008-12-10 00:24:00.960000,2009-01-03 00:24:00.960000,24 |
| 2008-12-11 00:23:00.930000,2009-01-03 00:23:00.930000,23 |
| 2008-12-12 00:22:00.910000,2009-01-03 00:22:00.910000,22 |
| 2008-12-13 00:21:00.900000,2009-01-03 00:21:00.900000,21 |
| 2008-12-14 00:19:00.810000,2009-01-02 00:19:00.810000,19 |
| 2008-12-14 00:20:00.900000,2009-01-03 00:20:00.900000,20 |
| 2008-12-15 00:18:00.730000,2009-01-02 00:18:00.730000,18 |
| 2008-12-16 00:17:00.660000,2009-01-02 00:17:00.660000,17 |
| 2008-12-16 00:24:00.960000,2009-02-03 00:24:00.960000,49 |
| 2008-12-17 00:16:00.600000,2009-01-02 00:16:00.600000,16 |
| 2008-12-17 00:23:00.930000,2009-02-03 00:23:00.930000,48 |
| 2008-12-18 00:15:00.550000,2009-01-02 00:15:00.550000,15 |
| 2008-12-18 00:22:00.910000,2009-02-03 00:22:00.910000,47 |
| 2008-12-19 00:14:00.510000,2009-01-02 00:14:00.510000,14 |
| 2008-12-19 00:21:00.900000,2009-02-03 00:21:00.900000,46 |
| 2008-12-19 00:24:00.960000,2009-03-03 00:24:00.960000,74 |
| 2008-12-20 00:13:00.480000,2009-01-02 00:13:00.480000,13 |
| 2008-12-20 00:19:00.810000,2009-02-02 00:19:00.810000,44 |
| 2008-12-20 00:20:00.900000,2009-02-03 00:20:00.900000,45 |
| 2008-12-20 00:23:00.930000,2009-03-03 00:23:00.930000,73 |
| ---- TYPES |
| TIMESTAMP, TIMESTAMP, INT |
| ==== |
| ---- QUERY |
| # Test of order by with NULL tuple rows (from an outer join) |
| select t1.id, t1.int_col, t2.id, t2.int_col |
| from alltypesagg t1 |
| left outer join alltypessmall t2 |
| on (t1.int_col = t2.int_col) |
| order by t1.id,t2.id limit 10 |
| ---- RESULTS |
| 0,NULL,NULL,NULL |
| 0,NULL,NULL,NULL |
| 1,1,1,1 |
| 1,1,11,1 |
| 1,1,21,1 |
| 1,1,26,1 |
| 1,1,36,1 |
| 1,1,46,1 |
| 1,1,51,1 |
| 1,1,61,1 |
| ---- TYPES |
| int,int,int,int |
| ==== |
| ---- QUERY |
| # Test limit 0 from sub query |
| select sum(a.int_col) from |
| (select int_col from functional.alltypes order by int_col limit 0) a |
| ---- RESULTS |
| NULL |
| ---- TYPES |
| bigint |
| ==== |
| ---- QUERY |
| # Test queries with divide by 0 (cast to string to avoid nan != nan issues) |
| select cast(if(id % 2 = 0, cast(id/3 as int), -id) / if(id > 4 or id = 0, 0, 1) as string) |
| from alltypestiny order by |
| if(id % 2 = 0, cast(id/3 as int), -id) / if(id > 4 or id = 0, 0, 1) desc limit 100; |
| ---- RESULTS |
| 'inf' |
| '1' |
| '0' |
| '-1' |
| '-3' |
| '-inf' |
| '-inf' |
| 'nan' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| # Test queries with divide by 0 (cast to string to avoid nan != nan issues) |
| select CAST(if(id % 2 = 0, cast(id/3 as int), -id) / if(id > 4 or id = 0, 0, 1) as STRING) |
| from alltypestiny order by |
| if(id % 2 = 0, cast(id/3 as int), -id) / if(id > 4 or id = 0, 0, 1) asc limit 100; |
| ---- RESULTS |
| 'nan' |
| '-inf' |
| '-inf' |
| '-3' |
| '-1' |
| '0' |
| '1' |
| 'inf' |
| ==== |
| ---- QUERY |
| # Taken from limit.test |
| # With an offset of 0 |
| select * from alltypesagg where day is not null order by id limit 10 offset 0 |
| ---- RESULTS |
| 0,true,NULL,NULL,NULL,NULL,NULL,NULL,'01/01/10','0',2010-01-01 00:00:00,2010,1,1 |
| 1,false,1,1,1,10,1.100000023841858,10.1,'01/01/10','1',2010-01-01 00:01:00,2010,1,1 |
| 2,true,2,2,2,20,2.200000047683716,20.2,'01/01/10','2',2010-01-01 00:02:00.100000000,2010,1,1 |
| 3,false,3,3,3,30,3.299999952316284,30.3,'01/01/10','3',2010-01-01 00:03:00.300000000,2010,1,1 |
| 4,true,4,4,4,40,4.400000095367432,40.4,'01/01/10','4',2010-01-01 00:04:00.600000000,2010,1,1 |
| 5,false,5,5,5,50,5.5,50.5,'01/01/10','5',2010-01-01 00:05:00.100000000,2010,1,1 |
| 6,true,6,6,6,60,6.599999904632568,60.59999999999999,'01/01/10','6',2010-01-01 00:06:00.150000000,2010,1,1 |
| 7,false,7,7,7,70,7.699999809265137,70.7,'01/01/10','7',2010-01-01 00:07:00.210000000,2010,1,1 |
| 8,true,8,8,8,80,8.800000190734863,80.8,'01/01/10','8',2010-01-01 00:08:00.280000000,2010,1,1 |
| 9,false,9,9,9,90,9.899999618530273,90.89999999999999,'01/01/10','9',2010-01-01 00:09:00.360000000,2010,1,1 |
| ---- DBAPI_RESULTS |
| 0,true,NULL,NULL,NULL,NULL,NULL,NULL,'01/01/10','0',2010-01-01 00:00:00,2010,1,1 |
| 1,false,1,1,1,10,1.100000023841,10.1,'01/01/10','1',2010-01-01 00:01:00,2010,1,1 |
| 2,true,2,2,2,20,2.200000047683,20.2,'01/01/10','2',2010-01-01 00:02:00.100000,2010,1,1 |
| 3,false,3,3,3,30,3.299999952316,30.3,'01/01/10','3',2010-01-01 00:03:00.300000,2010,1,1 |
| 4,true,4,4,4,40,4.400000095367,40.4,'01/01/10','4',2010-01-01 00:04:00.600000,2010,1,1 |
| 5,false,5,5,5,50,5.5,50.5,'01/01/10','5',2010-01-01 00:05:00.100000,2010,1,1 |
| 6,true,6,6,6,60,6.599999904632,60.59999999999999,'01/01/10','6',2010-01-01 00:06:00.150000,2010,1,1 |
| 7,false,7,7,7,70,7.699999809265,70.7,'01/01/10','7',2010-01-01 00:07:00.210000,2010,1,1 |
| 8,true,8,8,8,80,8.800000190734,80.8,'01/01/10','8',2010-01-01 00:08:00.280000,2010,1,1 |
| 9,false,9,9,9,90,9.899999618530,90.89999999999999,'01/01/10','9',2010-01-01 00:09:00.360000,2010,1,1 |
| ---- TYPES |
| int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int, int |
| ==== |
| ---- QUERY |
| # Taken from limit.test |
| # With a non-zero offset |
| select * from alltypesagg where day is not null order by id limit 10 offset 5 |
| ---- RESULTS |
| 5,false,5,5,5,50,5.5,50.5,'01/01/10','5',2010-01-01 00:05:00.100000000,2010,1,1 |
| 6,true,6,6,6,60,6.599999904632568,60.59999999999999,'01/01/10','6',2010-01-01 00:06:00.150000000,2010,1,1 |
| 7,false,7,7,7,70,7.699999809265137,70.7,'01/01/10','7',2010-01-01 00:07:00.210000000,2010,1,1 |
| 8,true,8,8,8,80,8.800000190734863,80.8,'01/01/10','8',2010-01-01 00:08:00.280000000,2010,1,1 |
| 9,false,9,9,9,90,9.899999618530273,90.89999999999999,'01/01/10','9',2010-01-01 00:09:00.360000000,2010,1,1 |
| 10,true,NULL,10,10,100,11,101,'01/01/10','10',2010-01-01 00:10:00.450000000,2010,1,1 |
| 11,false,1,11,11,110,12.10000038146973,111.1,'01/01/10','11',2010-01-01 00:11:00.550000000,2010,1,1 |
| 12,true,2,12,12,120,13.19999980926514,121.2,'01/01/10','12',2010-01-01 00:12:00.660000000,2010,1,1 |
| 13,false,3,13,13,130,14.30000019073486,131.3,'01/01/10','13',2010-01-01 00:13:00.780000000,2010,1,1 |
| 14,true,4,14,14,140,15.39999961853027,141.4,'01/01/10','14',2010-01-01 00:14:00.910000000,2010,1,1 |
| ---- DBAPI_RESULTS |
| 5,false,5,5,5,50,5.5,50.5,'01/01/10','5',2010-01-01 00:05:00.100000,2010,1,1 |
| 6,true,6,6,6,60,6.599999904632,60.59999999999999,'01/01/10','6',2010-01-01 00:06:00.150000,2010,1,1 |
| 7,false,7,7,7,70,7.699999809265,70.7,'01/01/10','7',2010-01-01 00:07:00.210000,2010,1,1 |
| 8,true,8,8,8,80,8.800000190734,80.8,'01/01/10','8',2010-01-01 00:08:00.280000,2010,1,1 |
| 9,false,9,9,9,90,9.899999618530,90.89999999999999,'01/01/10','9',2010-01-01 00:09:00.360000,2010,1,1 |
| 10,true,NULL,10,10,100,11,101,'01/01/10','10',2010-01-01 00:10:00.450000,2010,1,1 |
| 11,false,1,11,11,110,12.10000038146,111.1,'01/01/10','11',2010-01-01 00:11:00.550000,2010,1,1 |
| 12,true,2,12,12,120,13.19999980926,121.2,'01/01/10','12',2010-01-01 00:12:00.660000,2010,1,1 |
| 13,false,3,13,13,130,14.30000019073,131.3,'01/01/10','13',2010-01-01 00:13:00.780000,2010,1,1 |
| 14,true,4,14,14,140,15.39999961853,141.4,'01/01/10','14',2010-01-01 00:14:00.910000,2010,1,1 |
| ---- TYPES |
| int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int, int |
| ==== |
| ---- QUERY |
| # Taken from limit.test |
| # With an offset that reduces the result size (limit is much bigger than number of rows) |
| select id from functional.alltypessmall order by 1 limit 10000 offset 99; |
| ---- RESULTS |
| 99 |
| ---- TYPES |
| int |
| ==== |
| ---- QUERY |
| # Taken from limit.test |
| # With an offset that is bigger than the number of rows |
| select id from functional.alltypessmall order by 1 limit 1 offset 1000; |
| ---- RESULTS |
| ---- TYPES |
| int |
| ==== |
| ---- QUERY |
| # Taken from limit.test |
| # Offset within a subquery |
| select x.id from ( |
| select id from alltypesagg order by id limit 5 offset 5) x |
| order by x.id |
| limit 100 offset 4; |
| ---- RESULTS |
| 8 |
| ---- TYPES |
| int |
| ==== |
| ---- QUERY |
| # Taken from joins.test |
| # cross join |
| select t1.id, t2.id from alltypestiny t1 cross join alltypestiny t2 |
| where (t1.id < 3 and t2.id < 3) |
| order by t1.id, t2.id limit 100 |
| ---- RESULTS |
| 0,0 |
| 0,1 |
| 0,2 |
| 1,0 |
| 1,1 |
| 1,2 |
| 2,0 |
| 2,1 |
| 2,2 |
| ---- TYPES |
| int, int |
| ==== |
| ---- QUERY |
| # Taken from joins.test |
| # cross join with nulls and constant table |
| select id, tinyint_col, t1.c from functional.alltypesagg |
| cross join (values(NULL c, 1, 2)) as t1 |
| order by tinyint_col nulls first, id, t1.c |
| limit 6 |
| ---- RESULTS |
| 0,NULL,NULL |
| 0,NULL,NULL |
| 10,NULL,NULL |
| 10,NULL,NULL |
| 20,NULL,NULL |
| 20,NULL,NULL |
| ---- TYPES |
| int, tinyint, null |
| ---- HS2_TYPES |
| # HS2 maps NULL to BOOLEAN |
| int, tinyint, boolean |
| ==== |
| ---- QUERY |
| # check cross joins within a subquery |
| # Taken from joins.test |
| select t1.id as t1_id, t2_id, t3_id from alltypestiny t1 |
| cross join (select t2.id as t2_id, t3.id as t3_id from alltypestiny t2 |
| cross join alltypestiny t3) t4 |
| where t1.id < 2 and t2_id < 2 and t3_id < 2 |
| order by t1.id, t2_id, t3_id |
| limit 10 |
| ---- RESULTS |
| 0,0,0 |
| 0,0,1 |
| 0,1,0 |
| 0,1,1 |
| 1,0,0 |
| 1,0,1 |
| 1,1,0 |
| 1,1,1 |
| ---- TYPES |
| int, int, int |
| ==== |
| ---- QUERY |
| # join with three tables and then a cross join, extra where predicates, extra scan |
| # predicates, nulls in joins cols (alltypesagg.tinyint_col contains nulls instead of |
| # 0s) |
| # Taken from joins.test |
| select a.smallint_col, b.id, a.tinyint_col, c.id, a.int_col, b.float_col, c.string_col, d.id |
| from alltypesagg a |
| join alltypessmall b on (a.smallint_col = b.id) |
| join alltypessmall c on (a.tinyint_col = c.id) |
| cross join alltypestiny d |
| where a.month=1 |
| and a.day=1 |
| and a.int_col > 899 |
| and b.float_col > 4.5 |
| and c.string_col < '4' |
| and a.int_col + b.float_col + cast(c.string_col as float) < 1000 |
| and d.id < 2 |
| order by a.id, b.id, c.id, d.id |
| limit 100 |
| ---- RESULTS |
| 31,31,1,1,931,6.599999904632568,'1',0 |
| 31,31,1,1,931,6.599999904632568,'1',1 |
| 32,32,2,2,932,7.699999809265137,'2',0 |
| 32,32,2,2,932,7.699999809265137,'2',1 |
| 33,33,3,3,933,8.800000190734863,'3',0 |
| 33,33,3,3,933,8.800000190734863,'3',1 |
| 41,41,1,1,941,6.599999904632568,'1',0 |
| 41,41,1,1,941,6.599999904632568,'1',1 |
| 42,42,2,2,942,7.699999809265137,'2',0 |
| 42,42,2,2,942,7.699999809265137,'2',1 |
| 43,43,3,3,943,8.800000190734863,'3',0 |
| 43,43,3,3,943,8.800000190734863,'3',1 |
| 81,81,1,1,981,6.599999904632568,'1',0 |
| 81,81,1,1,981,6.599999904632568,'1',1 |
| 82,82,2,2,982,7.699999809265137,'2',0 |
| 82,82,2,2,982,7.699999809265137,'2',1 |
| 83,83,3,3,983,8.800000190734863,'3',0 |
| 83,83,3,3,983,8.800000190734863,'3',1 |
| 91,91,1,1,991,6.599999904632568,'1',0 |
| 91,91,1,1,991,6.599999904632568,'1',1 |
| ---- DBAPI_RESULTS |
| 31,31,1,1,931,6.599999904632,'1',0 |
| 31,31,1,1,931,6.599999904632,'1',1 |
| 32,32,2,2,932,7.699999809265,'2',0 |
| 32,32,2,2,932,7.699999809265,'2',1 |
| 33,33,3,3,933,8.800000190734,'3',0 |
| 33,33,3,3,933,8.800000190734,'3',1 |
| 41,41,1,1,941,6.599999904632,'1',0 |
| 41,41,1,1,941,6.599999904632,'1',1 |
| 42,42,2,2,942,7.699999809265,'2',0 |
| 42,42,2,2,942,7.699999809265,'2',1 |
| 43,43,3,3,943,8.800000190734,'3',0 |
| 43,43,3,3,943,8.800000190734,'3',1 |
| 81,81,1,1,981,6.599999904632,'1',0 |
| 81,81,1,1,981,6.599999904632,'1',1 |
| 82,82,2,2,982,7.699999809265,'2',0 |
| 82,82,2,2,982,7.699999809265,'2',1 |
| 83,83,3,3,983,8.800000190734,'3',0 |
| 83,83,3,3,983,8.800000190734,'3',1 |
| 91,91,1,1,991,6.599999904632,'1',0 |
| 91,91,1,1,991,6.599999904632,'1',1 |
| ---- TYPES |
| SMALLINT, INT, TINYINT, INT, INT, FLOAT, STRING, INT |
| ==== |
| ---- QUERY |
| # join against subquery with limit; |
| # predicate pushdown is prevented in presence of order by/limit clause; variant w/ join |
| # Taken from subquery-limit.test |
| select alltypes.id, a.id |
| from alltypes |
| join ( |
| select a.id, a.bool_col |
| from alltypessmall a join alltypessmall using (id) |
| order by a.id limit 10 |
| ) a using (id) |
| where a.bool_col = true |
| order by |
| a.id |
| limit 5 |
| ---- RESULTS |
| 0,0 |
| 2,2 |
| 4,4 |
| 6,6 |
| 8,8 |
| ---- TYPES |
| INT, INT |
| ==== |
| ---- QUERY |
| # Mixed UNION ALL/DISTINCT but effectively only UNION DISTINCT, no nested unions, |
| # with order by and limit |
| # Taken from union.test |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union all |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union all |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2 |
| union distinct |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2) |
| order by 1 limit 3 |
| ---- RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| ---- DBAPI_RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| ---- TYPES |
| int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int |
| ==== |
| ---- QUERY |
| # Mixed UNION ALL/DISTINCT, no nested unions, with order by and limit |
| # Taken from union.test |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union distinct |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union all |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2 |
| union all |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2) |
| order by 1,2 limit 3 |
| ---- RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| ---- DBAPI_RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| ---- TYPES |
| int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int |
| ==== |
| ---- QUERY |
| # Mixed UNION ALL/DISTINCT, no nested unions, with order by and limit |
| # Taken from union.test |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union all |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union distinct |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2 |
| union all |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2) |
| order by 1,2 limit 4 |
| ---- RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| ---- DBAPI_RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| ---- TYPES |
| int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int |
| ==== |
| ---- QUERY |
| # Complex union unnesting: Partially unnestable up to 2nd level |
| # Taken from union.test |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union all |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union distinct |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2 |
| union all |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2 |
| union distinct |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=3) |
| order by 1 limit 3))) |
| order by 12, 13, 1 |
| limit 20 |
| ---- RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| 3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2 |
| 4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3 |
| ---- DBAPI_RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| 3,false,1,1,1,10,1.10000002384158,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2 |
| 4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3 |
| ---- TYPES |
| int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int |
| ==== |
| ---- QUERY |
| # Complex union unnesting: Partially unnestable up to 1st level |
| # Taken from union.test |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union distinct |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union distinct |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2 |
| union all |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2 |
| union distinct |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=3) |
| order by 1 limit 3))) |
| order by 12, 13, 1 |
| limit 20 |
| ---- RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| 3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2 |
| 4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3 |
| ---- DBAPI_RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| 3,false,1,1,1,10,1.100000023841,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2 |
| 4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3 |
| ---- TYPES |
| int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int |
| ==== |
| ---- QUERY |
| # Complex union unnesting: Multiple nested unions to test all rules in a single query |
| # Taken from union.test |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union distinct |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union all |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2) |
| union distinct |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2 |
| union all |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=3) |
| order by 1 limit 3) |
| union all |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=3 |
| union all |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=4) |
| union all |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=4 |
| union all |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=5) |
| order by 1 limit 3) |
| order by 12, 13, 1 |
| limit 20 |
| ---- RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| 3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2 |
| 4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3 |
| 4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3 |
| 5,false,1,1,1,10,1.100000023841858,10.1,'03/01/09','1',2009-03-01 00:01:00,2009,3 |
| 6,true,0,0,0,0,0,0,'04/01/09','0',2009-04-01 00:00:00,2009,4 |
| 6,true,0,0,0,0,0,0,'04/01/09','0',2009-04-01 00:00:00,2009,4 |
| 7,false,1,1,1,10,1.100000023841858,10.1,'04/01/09','1',2009-04-01 00:01:00,2009,4 |
| 7,false,1,1,1,10,1.100000023841858,10.1,'04/01/09','1',2009-04-01 00:01:00,2009,4 |
| ---- DBAPI_RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| 3,false,1,1,1,10,1.100000023841,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2 |
| 4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3 |
| 4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3 |
| 5,false,1,1,1,10,1.100000023841,10.1,'03/01/09','1',2009-03-01 00:01:00,2009,3 |
| 6,true,0,0,0,0,0,0,'04/01/09','0',2009-04-01 00:00:00,2009,4 |
| 6,true,0,0,0,0,0,0,'04/01/09','0',2009-04-01 00:00:00,2009,4 |
| 7,false,1,1,1,10,1.100000023841,10.1,'04/01/09','1',2009-04-01 00:01:00,2009,4 |
| 7,false,1,1,1,10,1.100000023841,10.1,'04/01/09','1',2009-04-01 00:01:00,2009,4 |
| ---- TYPES |
| int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int |
| ==== |
| ---- QUERY |
| # UNION ALL in subquery |
| # Taken from union.test |
| select x.id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union all |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1) x |
| union all |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2) |
| order by 1 limit 5 |
| ---- RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| ---- DBAPI_RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| ---- TYPES |
| int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int |
| ==== |
| ---- QUERY |
| # UNION DISTINCT in subquery |
| # Taken from union.test |
| select x.id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1 |
| union distinct |
| select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=1) x |
| union distinct |
| (select id, bool_col, tinyint_col, smallint_col, int_col, bigint_col, float_col, double_col, date_string_col, string_col, timestamp_col, year, month from alltypestiny where year=2009 and month=2) |
| order by 1 limit 3 |
| ---- RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| ---- DBAPI_RESULTS |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 1,false,1,1,1,10,1.100000023841,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| ---- TYPES |
| int, boolean, tinyint, smallint, int, bigint, float, double, string, string, timestamp, int, int |
| ==== |
| ---- QUERY |
| values(1+1, 2, 5.0, 'a') order by 1 limit 10 |
| ---- RESULTS |
| 2,2,5.0,'a' |
| ---- TYPES |
| SMALLINT, TINYINT, DECIMAL, STRING |
| ==== |
| ---- QUERY |
| values((1+8, 2, 5.0, 'a'), (2, 3, 6.0, 'b'), (3, 4, 7.0, 'c')) order by 1 desc limit 2 |
| ---- RESULTS |
| 9,2,5.0,'a' |
| 3,4,7.0,'c' |
| ---- TYPES |
| SMALLINT, TINYINT, DECIMAL, STRING |
| ==== |
| ---- QUERY |
| # Basic test with a single with-clause view that references a virtual view. |
| # Taken from with-clause.test |
| with t as (select abc x, xyz y from functional.complex_view) |
| select x, y from t order by y limit 10 |
| ---- RESULTS |
| 2,'0' |
| 2,'1' |
| ---- TYPES |
| BIGINT, STRING |
| ==== |
| ---- QUERY |
| # Multiple views in with-clause. All views are used in a union. |
| # Taken from with-clause.test |
| with t1 as (select int_col x, bigint_col y from functional.alltypestiny), |
| t2 as (select 1 x , 10 y), t3 as (values(2 x , 20 y), (3, 30)) |
| select * from t1 union all select * from t2 union all (select * from t3) order by x limit 20 |
| ---- RESULTS |
| 0,0 |
| 0,0 |
| 0,0 |
| 0,0 |
| 1,10 |
| 1,10 |
| 1,10 |
| 1,10 |
| 1,10 |
| 2,20 |
| 3,30 |
| ---- TYPES |
| INT, BIGINT |
| ==== |
| ---- QUERY |
| # Self-join of with-clause table to make sure the join op is properly set |
| # in the cloned inline-view instances. |
| # Taken from with-clause.test |
| with t as (select int_col x, bigint_col y from functional.alltypestiny order by id limit 2) |
| select * from t t1 left outer join t t2 on t1.y = t2.x full outer join t t3 on t2.y = t3.x |
| order by t1.x limit 10 |
| ---- RESULTS |
| 0,0,0,0,0,0 |
| 1,10,NULL,NULL,NULL,NULL |
| NULL,NULL,NULL,NULL,1,10 |
| ---- TYPES |
| INT, BIGINT, INT, BIGINT, INT, BIGINT |
| ==== |
| ---- QUERY |
| select * from functional.alltypestiny order by 2,1 limit 10 |
| ---- RESULTS |
| 1,false,1,1,1,10,1.100000023841858,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 3,false,1,1,1,10,1.100000023841858,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2 |
| 5,false,1,1,1,10,1.100000023841858,10.1,'03/01/09','1',2009-03-01 00:01:00,2009,3 |
| 7,false,1,1,1,10,1.100000023841858,10.1,'04/01/09','1',2009-04-01 00:01:00,2009,4 |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| 4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3 |
| 6,true,0,0,0,0,0,0,'04/01/09','0',2009-04-01 00:00:00,2009,4 |
| ---- DBAPI_RESULTS |
| 1,false,1,1,1,10,1.100000023841,10.1,'01/01/09','1',2009-01-01 00:01:00,2009,1 |
| 3,false,1,1,1,10,1.100000023841,10.1,'02/01/09','1',2009-02-01 00:01:00,2009,2 |
| 5,false,1,1,1,10,1.100000023841,10.1,'03/01/09','1',2009-03-01 00:01:00,2009,3 |
| 7,false,1,1,1,10,1.100000023841,10.1,'04/01/09','1',2009-04-01 00:01:00,2009,4 |
| 0,true,0,0,0,0,0,0,'01/01/09','0',2009-01-01 00:00:00,2009,1 |
| 2,true,0,0,0,0,0,0,'02/01/09','0',2009-02-01 00:00:00,2009,2 |
| 4,true,0,0,0,0,0,0,'03/01/09','0',2009-03-01 00:00:00,2009,3 |
| 6,true,0,0,0,0,0,0,'04/01/09','0',2009-04-01 00:00:00,2009,4 |
| ---- TYPES |
| INT, BOOLEAN, TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, STRING, STRING, TIMESTAMP, INT, INT |
| ==== |
| ---- QUERY |
| # Test that codegen of ordering expression with intermediate char value doesn't crash. |
| # The cast expression is evaluated as part of tuple comparison. |
| select cast(string_col as char(20)) from alltypes order by 1 limit 5 |
| ---- RESULTS |
| '0 ' |
| '0 ' |
| '0 ' |
| '0 ' |
| '0 ' |
| ---- TYPES |
| CHAR |
| ==== |
| ---- QUERY |
| # Test queries with the maximum value for the limit and offset |
| select string_col from alltypes order by 1 limit 9223372036854775807 |
| offset 9223372036854775807 |
| ---- RESULTS |
| ---- TYPES |
| STRING |
| ==== |
