Looks like a bug - it should not be necessary to have an IS NOT NULL filter. Please file a JIRA.

Thanks,
James


On Fri, Jul 4, 2014 at 2:50 PM, puneet <puneet.kumar@pubmatic.com> wrote:
Seems , it is only happening for Phoenix 4.0.0 and not for Phoenix 3.0.0


On Friday 04 July 2014 05:54 PM, puneet wrote:
Hi Team,

Please see the query below :-

I have specified specific filter value as 232 for geo column but it returns NULL values also for geo column. In order to restrict it I need to specify "is not NULL" for GEO column to restrict the NULL values.

select Pub,Site,channel,Advertiser,GeoId,sum(TI),sum(Rev) from PUB_PARTIAL_CUBE where date>=19700116 and date<=19700116 and GeoId='232' and Pub=6846 and Site is not Null and Advertiser is not Null and ATD is Null and Section is Null and Ad_Tag is Null and Deal is Null and DSP is Null group by Pub,Site,channel,Advertiser,GeoId;

|    PUB     |    SITE    |  CHANNEL   | ADVERTISER |   GEOID    | SUM(A.TI)  | SUM(A.REV) |
+------------+------------+------------+------------+------------+------------+------------+
| 6846       | 6847       | 1          | 1          | null       | 48         | 5.494624800000002 |
| 6846       | 6847       | 1          | 11104      | null       | 8          | 2.523784   |
| 6846       | 6847       | 1          | 1170       | null       | 4          | 0.068464   |
| 6846       | 6847       | 1          | 11742      | null       | 44         | 17.987671999999968 |
| 6846       | 6847       | 1          | 13131      | null       | 24         | 0.36       |
| 6846       | 6847       | 1          | 14302      | null       | 4          | 0.06197559999999999 |
| 6846       | 6847       | 1          | 15423      | null       | 8          | 0.8070079999999998 |
| 6846       | 6847       | 1          | 1835       | null       | 40         | 5.304000000000001 |
| 6846       | 6847       | 1          | 19840      | null       | 0          | 0.13607999999999998 |
| 6846       | 6847       | 1          | 1995       | null       | 4          | 0.06       |
| 6846       | 6847       | 1          | 20865      | null       | 0          | 0.044000000000000004 |
| 6846       | 6847       | 1          | 22621      | null       | 8          | 0.6575271999999999 |
| 6846       | 6847       | 1          | 25267      | null       | 48         | 1.671988   |
| 6846       | 6847       | 1          | 3307       | null       | 456        | 42.85293920000001 |
| 6846       | 6847       | 1          | 34158      | null       | 8          | 0.12       |
| 6846       | 6847       | 1          | 37090      | null       | 188        | 5.264767200000002 |
| 6846       | 6847       | 1          | 7262       | null       | 40         | 1.0799055999999998 |
| 6846       | 6847       | 1          | 8544       | null       | 640        | 346.3737784000001 |
| 6846       | 6847       | 1          | 1          | 232        | 12         | 1.5472752000000002 |
| 6846       | 6847       | 1          | 13682      | 232        | 4          | 0.108      |
| 6846       | 6847       | 1          | 15033      | 232        | 16         | 0.24       |
| 6846       | 6847       | 1          | 21033      | 232        | 0          | 0.13223520000000002 |
| 6846       | 6847       | 1          | 2431       | 232        | 8          | 0.8227711999999998 |
| 6846       | 6847       | 1          | 2670       | 232        | 24         | 0.5745912000000001 |
| 6846       | 6847       | 1          | 2856       | 232        | 0          | 0.7346728  |
| 6846       | 6847       | 1          | 3349       | 232        | 4          | 0.63191    |
| 6846       | 6847       | 1          | 3837       | 232        | 0          | 0.47881680000000004 |
| 6846       | 6847       | 1          | 390        | 232        | 8          | 1.0987328  |
| 6846       | 6847       | 1          | 5805       | 232        | 0          | 0.32800000000000003 |
| 6846       | 6847       | 1          | 69         | 232        | 0          | 0.008      |
| 6846       | 6847       | 1          | 6953       | 232        | 0          | 0.11691760000000001 |
| 6846       | 6847       | 1          | 7921       | 232        | 1488       | 114.79178239999995 |
| 6846       | 6847       | 1          | 8710       | 232        | 8          | 0.2285896  |

Let me know if it needs to be explicitly specified (It's not the case with SQL) OR there is something I am missing OR it is a bug.



Hi Team,

Please see the query below :-

I have specified specific filter value as 232 for geo column but it returns NULL values also for geo column. In order to restrict it I need to specify "is not NULL" for GEO column to restrict the NULL values.

select Pub,Site,channel,Advertiser,GeoId,sum(TI),sum(Rev) from PUB_PARTIAL_CUBE where date>=19700116 and date<=19700116 and GeoId='232' and Pub=6846 and Site is not Null and Advertiser is not Null and ATD is Null and Section is Null and Ad_Tag is Null and Deal is Null and DSP is Null group by Pub,Site,channel,Advertiser,GeoId;

|    PUB     |    SITE    |  CHANNEL   | ADVERTISER |   GEOID    | SUM(A.TI)  | SUM(A.REV) |
+------------+------------+------------+------------+------------+------------+------------+
| 6846       | 6847       | 1          | 1          | null       | 48         | 5.494624800000002 |
| 6846       | 6847       | 1          | 11104      | null       | 8          | 2.523784   |
| 6846       | 6847       | 1          | 1170       | null       | 4          | 0.068464   |
| 6846       | 6847       | 1          | 11742      | null       | 44         | 17.987671999999968 |
| 6846       | 6847       | 1          | 13131      | null       | 24         | 0.36       |
| 6846       | 6847       | 1          | 14302      | null       | 4          | 0.06197559999999999 |
| 6846       | 6847       | 1          | 15423      | null       | 8          | 0.8070079999999998 |
| 6846       | 6847       | 1          | 1835       | null       | 40         | 5.304000000000001 |
| 6846       | 6847       | 1          | 19840      | null       | 0          | 0.13607999999999998 |
| 6846       | 6847       | 1          | 1995       | null       | 4          | 0.06       |
| 6846       | 6847       | 1          | 20865      | null       | 0          | 0.044000000000000004 |
| 6846       | 6847       | 1          | 22621      | null       | 8          | 0.6575271999999999 |
| 6846       | 6847       | 1          | 25267      | null       | 48         | 1.671988   |
| 6846       | 6847       | 1          | 3307       | null       | 456        | 42.85293920000001 |
| 6846       | 6847       | 1          | 34158      | null       | 8          | 0.12       |
| 6846       | 6847       | 1          | 37090      | null       | 188        | 5.264767200000002 |
| 6846       | 6847       | 1          | 7262       | null       | 40         | 1.0799055999999998 |
| 6846       | 6847       | 1          | 8544       | null       | 640        | 346.3737784000001 |
| 6846       | 6847       | 1          | 1          | 232        | 12         | 1.5472752000000002 |
| 6846       | 6847       | 1          | 13682      | 232        | 4          | 0.108      |
| 6846       | 6847       | 1          | 15033      | 232        | 16         | 0.24       |
| 6846       | 6847       | 1          | 21033      | 232        | 0          | 0.13223520000000002 |
| 6846       | 6847       | 1          | 2431       | 232        | 8          | 0.8227711999999998 |
| 6846       | 6847       | 1          | 2670       | 232        | 24         | 0.5745912000000001 |
| 6846       | 6847       | 1          | 2856       | 232        | 0          | 0.7346728  |
| 6846       | 6847       | 1          | 3349       | 232        | 4          | 0.63191    |
| 6846       | 6847       | 1          | 3837       | 232        | 0          | 0.47881680000000004 |
| 6846       | 6847       | 1          | 390        | 232        | 8          | 1.0987328  |
| 6846       | 6847       | 1          | 5805       | 232        | 0          | 0.32800000000000003 |
| 6846       | 6847       | 1          | 69         | 232        | 0          | 0.008      |
| 6846       | 6847       | 1          | 6953       | 232        | 0          | 0.11691760000000001 |
| 6846       | 6847       | 1          | 7921       | 232        | 1488       | 114.79178239999995 |
| 6846       | 6847       | 1          | 8710       | 232        | 8          | 0.2285896  |

Let me know if it needs to be explicitly specified (It's not the case with SQL) OR there is something I am missing OR it is a bug. 

--

Puneet Ojha | Tech Lead: Data Analytics



--

Puneet Ojha | Tech Lead: Data Analytics
6th Floor, Amar Paradigm | Baner Road, Pune 411045
o: +91-20-67285700 | m: +91-8605359898 | Skype: puneetkr.ojha

PubMatic
Every ad. Every sales channel. Every screen. One platform

On the Web: Facebook Twitter LinkedIn

Find out more about our services: PubConnect | PubDirect | Solutions for Media Buyers
What's New: PubMatic Launches Mobile SDK for app developers.
Latest Research: Real-Time Bidding in the United States and Worldwide, 2010-2017 (Q4 2013)
Recent Videos: Ad Revenue 6 | Ad Revenue Europe