| ==== |
| ---- QUERY |
| # Reference the string row key but do not create a predicate on the key. |
| select distinct id from stringids where int_col = 1 |
| ---- RESULTS |
| '2001' |
| '9001' |
| '4001' |
| '8001' |
| '5001' |
| '3001' |
| '1' |
| '1001' |
| '7001' |
| '6001' |
| ---- TYPES |
| STRING |
| ==== |
| ---- QUERY |
| select * from alltypessmall where string_col = '4' |
| ---- RESULTS |
| 14,40,true,'01/02/09',40.4,4.400000095367432,4,1,4,'4',2009-01-02 00:14:00.510000000,4,2009 |
| 24,40,true,'01/03/09',40.4,4.400000095367432,4,1,4,'4',2009-01-03 00:24:00.960000000,4,2009 |
| 29,40,false,'02/01/09',40.4,4.400000095367432,4,2,4,'4',2009-02-01 00:04:00.600000000,4,2009 |
| 39,40,false,'02/02/09',40.4,4.400000095367432,4,2,4,'4',2009-02-02 00:14:00.510000000,4,2009 |
| 4,40,true,'01/01/09',40.4,4.400000095367432,4,1,4,'4',2009-01-01 00:04:00.600000000,4,2009 |
| 49,40,false,'02/03/09',40.4,4.400000095367432,4,2,4,'4',2009-02-03 00:24:00.960000000,4,2009 |
| 54,40,true,'03/01/09',40.4,4.400000095367432,4,3,4,'4',2009-03-01 00:04:00.600000000,4,2009 |
| 64,40,true,'03/02/09',40.4,4.400000095367432,4,3,4,'4',2009-03-02 00:14:00.510000000,4,2009 |
| 74,40,true,'03/03/09',40.4,4.400000095367432,4,3,4,'4',2009-03-03 00:24:00.960000000,4,2009 |
| 79,40,false,'04/01/09',40.4,4.400000095367432,4,4,4,'4',2009-04-01 00:04:00.600000000,4,2009 |
| 89,40,false,'04/02/09',40.4,4.400000095367432,4,4,4,'4',2009-04-02 00:14:00.510000000,4,2009 |
| 99,40,false,'04/03/09',40.4,4.400000095367432,4,4,4,'4',2009-04-03 00:24:00.960000000,4,2009 |
| ---- TYPES |
| INT, BIGINT, BOOLEAN, STRING, DOUBLE, FLOAT, INT, INT, SMALLINT, STRING, TIMESTAMP, TINYINT, INT |
| ==== |
| ---- QUERY |
| # make sure this works even when not requesting the col family w/ the predicate |
| select tinyint_col from alltypessmall where string_col = '4' |
| ---- RESULTS |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| 4 |
| ---- TYPES |
| TINYINT |
| ==== |
| ---- QUERY |
| select count(*) from alltypessmall where string_col = '4' |
| ---- RESULTS |
| 12 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| select count(*) from alltypessmall where string_col != '4' |
| ---- RESULTS |
| 88 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| select count(*) from alltypessmall where string_col < '4' |
| ---- RESULTS |
| 48 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| select count(*) from alltypessmall where string_col > '4' |
| ---- RESULTS |
| 40 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| select count(*) from alltypessmall where string_col <= '4' |
| ---- RESULTS |
| 60 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| select count(*) from alltypessmall where string_col >= '4' |
| ---- RESULTS |
| 52 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # empty result |
| select count(*) from alltypessmall where string_col = '100' |
| ---- RESULTS |
| 0 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # multiple filters |
| select * from alltypessmall where string_col = '4' and date_string_col = '01/01/09' |
| ---- RESULTS |
| 4,40,true,'01/01/09',40.4,4.400000095367432,4,1,4,'4',2009-01-01 00:04:00.600000000,4,2009 |
| ---- TYPES |
| INT, BIGINT, BOOLEAN, STRING, DOUBLE, FLOAT, INT, INT, SMALLINT, STRING, TIMESTAMP, TINYINT, INT |
| ==== |
| ---- QUERY |
| # hbase filter with regular predicate |
| select * from alltypessmall where string_col <= '4' and int_col >= 4 |
| ---- RESULTS |
| 14,40,true,'01/02/09',40.4,4.400000095367432,4,1,4,'4',2009-01-02 00:14:00.510000000,4,2009 |
| 24,40,true,'01/03/09',40.4,4.400000095367432,4,1,4,'4',2009-01-03 00:24:00.960000000,4,2009 |
| 29,40,false,'02/01/09',40.4,4.400000095367432,4,2,4,'4',2009-02-01 00:04:00.600000000,4,2009 |
| 39,40,false,'02/02/09',40.4,4.400000095367432,4,2,4,'4',2009-02-02 00:14:00.510000000,4,2009 |
| 4,40,true,'01/01/09',40.4,4.400000095367432,4,1,4,'4',2009-01-01 00:04:00.600000000,4,2009 |
| 49,40,false,'02/03/09',40.4,4.400000095367432,4,2,4,'4',2009-02-03 00:24:00.960000000,4,2009 |
| 54,40,true,'03/01/09',40.4,4.400000095367432,4,3,4,'4',2009-03-01 00:04:00.600000000,4,2009 |
| 64,40,true,'03/02/09',40.4,4.400000095367432,4,3,4,'4',2009-03-02 00:14:00.510000000,4,2009 |
| 74,40,true,'03/03/09',40.4,4.400000095367432,4,3,4,'4',2009-03-03 00:24:00.960000000,4,2009 |
| 79,40,false,'04/01/09',40.4,4.400000095367432,4,4,4,'4',2009-04-01 00:04:00.600000000,4,2009 |
| 89,40,false,'04/02/09',40.4,4.400000095367432,4,4,4,'4',2009-04-02 00:14:00.510000000,4,2009 |
| 99,40,false,'04/03/09',40.4,4.400000095367432,4,4,4,'4',2009-04-03 00:24:00.960000000,4,2009 |
| ---- TYPES |
| INT, BIGINT, BOOLEAN, STRING, DOUBLE, FLOAT, INT, INT, SMALLINT, STRING, TIMESTAMP, TINYINT, INT |
| ==== |
| ---- QUERY |
| # hbase filter with regular predicate and start/stop keys |
| select * from stringids where string_col <= '4' and int_col >= 4 and id > concat('39', '0') |
| and id < concat('4', '00') |
| ---- RESULTS |
| '391',3910,false,'01/01/10',1,3949.1,430.1000061035156,391,1,91,'391',2010-01-01 06:43:42.450000000,1,2010 |
| '392',3920,true,'01/01/10',1,3959.2,431.2000122070312,392,1,92,'392',2010-01-01 06:44:46.360000000,2,2010 |
| '393',3930,false,'01/01/10',1,3969.3,432.2999877929688,393,1,93,'393',2010-01-01 06:45:50.280000000,3,2010 |
| '394',3940,true,'01/01/10',1,3979.4,433.3999938964844,394,1,94,'394',2010-01-01 06:46:54.210000000,4,2010 |
| '395',3950,false,'01/01/10',1,3989.5,434.5,395,1,95,'395',2010-01-01 06:47:58.150000000,5,2010 |
| '396',3960,true,'01/01/10',1,3999.6,435.6000061035156,396,1,96,'396',2010-01-01 06:49:02.100000000,6,2010 |
| '397',3970,false,'01/01/10',1,4009.7,436.7000122070312,397,1,97,'397',2010-01-01 06:50:06.600000000,7,2010 |
| '398',3980,true,'01/01/10',1,4019.8,437.7999877929688,398,1,98,'398',2010-01-01 06:51:10.300000000,8,2010 |
| '399',3990,false,'01/01/10',1,4029.9,438.8999938964844,399,1,99,'399',2010-01-01 06:52:14.100000000,9,2010 |
| '4',40,true,'01/01/10',1,40.4,4.400000095367432,4,1,4,'4',2010-01-01 00:04:00.600000000,4,2010 |
| ---- TYPES |
| STRING, BIGINT, BOOLEAN, STRING, INT, DOUBLE, FLOAT, INT, INT, SMALLINT, STRING, TIMESTAMP, TINYINT, INT |
| ==== |
| ---- QUERY |
| # TODO: fix this, this shouldn't match every row |
| # comparison w/ cast doesn't turn into a predicate |
| #select count(*) from alltypessmall where string_col >= 4 |
| #----- |
| #bigint |
| #----- |
| #12 |
| #===== |
| # non-const comparison doesn't turn into a predicate |
| select count(*) from alltypessmall where string_col != date_string_col |
| ---- RESULTS |
| 100 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # HBase scan query with an 'IS NULL' predicate |
| select * from alltypesagg where bigint_col is null |
| ---- RESULTS |
| 0,NULL,true,'01/01/10',1,NULL,NULL,NULL,1,NULL,'0',2010-01-01 00:00:00,NULL,2010 |
| 1000,NULL,true,'01/02/10',2,NULL,NULL,NULL,1,NULL,'0',2010-01-02 00:00:00,NULL,2010 |
| 2000,NULL,true,'01/03/10',3,NULL,NULL,NULL,1,NULL,'0',2010-01-03 00:00:00,NULL,2010 |
| 3000,NULL,true,'01/04/10',4,NULL,NULL,NULL,1,NULL,'0',2010-01-04 00:00:00,NULL,2010 |
| 4000,NULL,true,'01/05/10',5,NULL,NULL,NULL,1,NULL,'0',2010-01-05 00:00:00,NULL,2010 |
| 5000,NULL,true,'01/06/10',6,NULL,NULL,NULL,1,NULL,'0',2010-01-06 00:00:00,NULL,2010 |
| 6000,NULL,true,'01/07/10',7,NULL,NULL,NULL,1,NULL,'0',2010-01-07 00:00:00,NULL,2010 |
| 7000,NULL,true,'01/08/10',8,NULL,NULL,NULL,1,NULL,'0',2010-01-08 00:00:00,NULL,2010 |
| 8000,NULL,true,'01/09/10',9,NULL,NULL,NULL,1,NULL,'0',2010-01-09 00:00:00,NULL,2010 |
| 9000,NULL,true,'01/10/10',10,NULL,NULL,NULL,1,NULL,'0',2010-01-10 00:00:00,NULL,2010 |
| ---- TYPES |
| INT, BIGINT, BOOLEAN, STRING, INT, DOUBLE, FLOAT, INT, INT, SMALLINT, STRING, TIMESTAMP, TINYINT, INT |
| ==== |
| ---- QUERY |
| # HBase scan query with projection and an 'IS NULL' predicate on one of the projected columns |
| select bigint_col, day from alltypesagg where bigint_col is null |
| ---- RESULTS |
| NULL,1 |
| NULL,2 |
| NULL,3 |
| NULL,4 |
| NULL,5 |
| NULL,6 |
| NULL,7 |
| NULL,8 |
| NULL,9 |
| NULL,10 |
| ---- TYPES |
| BIGINT, INT |
| ==== |
| ---- QUERY |
| # HBase scan query with an 'IS NOT NULL' predicate; order by and limit are |
| # used for consistent test results |
| select * from alltypesagg where bigint_col is not null order by id limit 10 |
| ---- RESULTS |
| 1,10,false,'01/01/10',1,10.1,1.100000023841858,1,1,1,'1',2010-01-01 00:01:00,1,2010 |
| 2,20,true,'01/01/10',1,20.2,2.200000047683716,2,1,2,'2',2010-01-01 00:02:00.100000000,2,2010 |
| 3,30,false,'01/01/10',1,30.3,3.299999952316284,3,1,3,'3',2010-01-01 00:03:00.300000000,3,2010 |
| 4,40,true,'01/01/10',1,40.4,4.400000095367432,4,1,4,'4',2010-01-01 00:04:00.600000000,4,2010 |
| 5,50,false,'01/01/10',1,50.5,5.5,5,1,5,'5',2010-01-01 00:05:00.100000000,5,2010 |
| 6,60,true,'01/01/10',1,60.59999999999999,6.599999904632568,6,1,6,'6',2010-01-01 00:06:00.150000000,6,2010 |
| 7,70,false,'01/01/10',1,70.7,7.699999809265137,7,1,7,'7',2010-01-01 00:07:00.210000000,7,2010 |
| 8,80,true,'01/01/10',1,80.8,8.800000190734863,8,1,8,'8',2010-01-01 00:08:00.280000000,8,2010 |
| 9,90,false,'01/01/10',1,90.89999999999999,9.899999618530273,9,1,9,'9',2010-01-01 00:09:00.360000000,9,2010 |
| 10,100,true,'01/01/10',1,101,11,10,1,10,'10',2010-01-01 00:10:00.450000000,NULL,2010 |
| ---- TYPES |
| INT, BIGINT, BOOLEAN, STRING, INT, DOUBLE, FLOAT, INT, INT, SMALLINT, STRING, TIMESTAMP, TINYINT, INT |
| ==== |
| ---- QUERY |
| # HBase scan query with conjunctive predicates one of which is an 'IS NULL' |
| select * from alltypesagg where bigint_col is null and day = 1 |
| ---- RESULTS |
| 0,NULL,true,'01/01/10',1,NULL,NULL,NULL,1,NULL,'0',2010-01-01 00:00:00,NULL,2010 |
| ---- TYPES |
| INT, BIGINT, BOOLEAN, STRING, INT, DOUBLE, FLOAT, INT, INT, SMALLINT, STRING, TIMESTAMP, TINYINT, INT |
| ==== |
| ---- QUERY |
| # HBase scan query with conjunctive predicates one of which is an 'IS NOT NULL' |
| select * from alltypesagg where bigint_col is not null and bool_col = true order by id limit 10 |
| ---- RESULTS |
| 2,20,true,'01/01/10',1,20.2,2.200000047683716,2,1,2,'2',2010-01-01 00:02:00.100000000,2,2010 |
| 4,40,true,'01/01/10',1,40.4,4.400000095367432,4,1,4,'4',2010-01-01 00:04:00.600000000,4,2010 |
| 6,60,true,'01/01/10',1,60.59999999999999,6.599999904632568,6,1,6,'6',2010-01-01 00:06:00.150000000,6,2010 |
| 8,80,true,'01/01/10',1,80.8,8.800000190734863,8,1,8,'8',2010-01-01 00:08:00.280000000,8,2010 |
| 10,100,true,'01/01/10',1,101,11,10,1,10,'10',2010-01-01 00:10:00.450000000,NULL,2010 |
| 12,120,true,'01/01/10',1,121.2,13.19999980926514,12,1,12,'12',2010-01-01 00:12:00.660000000,2,2010 |
| 14,140,true,'01/01/10',1,141.4,15.39999961853027,14,1,14,'14',2010-01-01 00:14:00.910000000,4,2010 |
| 16,160,true,'01/01/10',1,161.6,17.60000038146973,16,1,16,'16',2010-01-01 00:16:01.200000000,6,2010 |
| 18,180,true,'01/01/10',1,181.8,19.79999923706055,18,1,18,'18',2010-01-01 00:18:01.530000000,8,2010 |
| 20,200,true,'01/01/10',1,202,22,20,1,20,'20',2010-01-01 00:20:01.900000000,NULL,2010 |
| ---- TYPES |
| INT, BIGINT, BOOLEAN, STRING, INT, DOUBLE, FLOAT, INT, INT, SMALLINT, STRING, TIMESTAMP, TINYINT, INT |
| ==== |
| ---- QUERY |
| # HBase scan query with aggregation and a single predicate |
| select count(*) from alltypesagg where bigint_col = 10 |
| ---- RESULTS |
| 10 |
| ---- TYPES |
| BIGINT |
| ==== |
| ---- QUERY |
| # HBase scan query with aggregration and conjunctive predicates |
| select count(*) from alltypesagg where bigint_col = 10 and day = 1 |
| ---- RESULTS |
| 1 |
| ---- TYPES |
| BIGINT |
