Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

SQL error: Invalid input syntax for integer

I have the following projection definition:
CREATE PROJECTION fact_stat_adid_man_plat(
 AdvAccountId ENCODING RLE, 
 AdId ENCODING RLE, 
 TimeId ENCODING RLE,
 DateId ENCODING RLE, 
 PlatformId ENCODING RLE, 
 ManufacturerId ENCODING RLE, 
 Requests ENCODING DELTARANGE_COMP, 
 Impressions ENCODING DELTARANGE_COMP, 
 Clicks ENCODING BLOCKDICT_COMP, 
 Conversions ENCODING RLE, 
 AppSiteRevenue ENCODING BLOCKDICT_COMP, 
 Spend ENCODING BLOCKDICT_COMP, 
 AdFalconRevenue ENCODING BLOCKDICT_COMP
)
AS
 SELECT AdvAccountId, 
        DateId, 
TimeId,
        ManufacturerId, 
PlatformId,
AdId,
        Requests, 
        Impressions, 
        Clicks, 
        Conversions, 
        AppSiteRevenue, 
        Spend, 
        AdFalconRevenue
 FROM public.fact_stat 
 ORDER BY AdvAccountId,
        DateId,
        ManufacturerId, 
PlatformId,
        AdId,
TimeId 
SEGMENTED BY HASH (ManufacturerId, PlatformId) ALL NODES OFFSET 0;
When i try to execute the following query, it gives an invalid input syntax for integer "2014-10-01"
select dateid, timeid, adid, manufacturerid, platformid, sum(requests) as requests, 
sum(impressions) as impressions, 
sum(appsiterevenue) as revenue, 
sum(clicks) as clicks,
sum(spend) as spend,
sum(conversions) as conversions 
from fact_stat_adid_man_plat where dateid >= '2014-10-01' and dateid <= '2014-10-03' and advaccountid = 62130
group by  dateid, timeid, adid, manufacturerid, platformid   
order by  dateid, timeid, adid, manufacturerid, platformid   
limit 10 offset 0;
If you change the query to fetch from the anchor table "fact_stat", it uses the super projection and ignores the custom projection "fact_stat_adid_man_plat".. Bear in mind that this only occurs if i have the following super projection with the noted encoding schemes, if i change encoding to RLE in the super projection, everything will work fine, and the above query is executed without errors...
CREATE PROJECTION fact_stat_mysuper_comp(
 AdvAccountId ENCODING COMMONDELTA_COMP, 
 PubAccountId ENCODING COMMONDELTA_COMP, 
 AppSiteId ENCODING RLE, 
 CampaignId ENCODING COMMONDELTA_COMP, 
 AdGroupId ENCODING COMMONDELTA_COMP, 
 AdId ENCODING COMMONDELTA_COMP, 
 CountryId ENCODING COMMONDELTA_COMP, 
 RegionId ENCODING COMMONDELTA_COMP, 
 OperatorId ENCODING RLE, 
 ManufacturerId ENCODING BLOCKDICT_COMP, 
 DeviceId ENCODING BLOCKDICT_COMP, 
 PlatformId ENCODING COMMONDELTA_COMP, 
 DateId ENCODING RLE, 
 TimeId ENCODING COMMONDELTA_COMP, 
 Requests ENCODING DELTARANGE_COMP, 
 Impressions ENCODING DELTARANGE_COMP, 
 Clicks ENCODING BLOCKDICT_COMP, 
 Conversions ENCODING RLE, 
 AppSiteRevenue ENCODING BLOCKDICT_COMP, 
 Spend ENCODING BLOCKDICT_COMP, 
 AdFalconRevenue ENCODING BLOCKDICT_COMP
)
AS
 SELECT AdvAccountId, 
        PubAccountId, 
        AppSiteId, 
        CampaignId, 
        AdGroupId, 
        AdId, 
        CountryId, 
        RegionId, 
        OperatorId, 
        ManufacturerId, 
        DeviceId, 
        PlatformId, 
        DateId, 
        TimeId, 
        Requests, 
        Impressions, 
        Clicks, 
        Conversions, 
        AppSiteRevenue, 
        Spend, 
        AdFalconRevenue
 FROM public.fact_stat 
 ORDER BY 
          AdvAccountId,
          PubAccountId,
 DateId,
 CampaignId,
 AdGroupId,
 AdId,
          AppSiteId,
          OperatorId,
          CountryId,
          Conversions,
          Clicks,
          PlatformId,
          ManufacturerId,
          RegionId,
     TimeId 
SEGMENTED BY HASH (DateId) ALL NODES;
Does any body has an explanation for what is happening?

Thank you!

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.