Still at proposal stage, it's not yet known when work on the colourful addition to the city could start, but with the way Toronto is booming and the small fact that this is such a prime plot, it is likely to be sooner than later. The 50-storey building is comprised of four box sections stacked seemingly randomly on top of one another The proposal, which comes from the pens of Toronto based architects Raw Design, will if built inject some much needed colour and life into the area. The plot, which is currently a rather drab car park, is the last of the undeveloped prime downtown plots and is currently surrounded by a mix of old hotel, office and residential buildings. It is important to know that PostgreSQL provides with 2 special wildcard characters for the purpose of patterns matching as below: Percent ( ) for matching any sequence of. Its result include strings that are case-insensitive and follow the mentioned pattern. ORDER BY a unique criterion - for example, entity.tenant_id and entity.creationdate, and the problem will go away.Unlike many other cities around the Western world, the Canadian city of Toronto appears to be going through something of a building boom with another high rise proposal, this time located at the southeast corner of Bay and Gerrard. The PostgreSQL ILIKE operator is used query data using pattern matching techniques. Materialising the WITH clause would only hide the ambiguity of the result of Cumulative Sum. SUM() OVER() does exactly what it's supposed to do, only the ORDER criterion turns out to return a partially random order. So the coder, here, is rather the victim than the master of what the query does. And as only the entity.creationdate is in the ORDER BY subclause of the OVER() windowing clause, the two rows of tenant_id 3 and 5 can indeed appear in any order they want. Note that creationDate is the same: for both tenant_id 3 and 5. It returns one of these two versions of the result table randomly: tenant_id PARTITION BY 1 ORDER BY date(entity.creationdate) ASC ROWS UNBOUNDED PRECEDING , sum(COUNT (DISTINCT entity.entityreference)) OVER(
So, with the data filled as above, I run this query several times: SELECT I had initially thought that PARTITION BY NULL could be the culprit, as NULL always risks to yield unpredictable results, and is not needed at all in this case, but then I realised that two tenants have the same creationDate, and that's where your issue comes from. Then, I run the same query (one branch of your MINUS query, but rewritten in my stile for efficiency - and better readability for me, twice. INSERT INTO dwh_ins_hub.entity_last_version SELECT * FROM entity I now reverse-engineered the query with ' s help in providing test data:ĭata preparation: CREATE TABLE dwh_ins_hub.entity (ĬREATE TABLE dwh_ins_hub.entity_last_version(ĬOPY dwh_ins_hub.entity FROM STDIN DELIMITER ',' ABORT ON ERROR If I run the query with the hint, the result is null (expected). Two diagnostic steps could help to shed some light on the matter: a) add the pseudo column epoch from all original source tables, so and to the view, and get the max (epoch) from both. Then, run it once with the /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */ hint, and once without. I even do CTAS to another names to fix the data and had the same problem. ON elv.entityreference = entity.entityreferenceĪND elv.last_entityversion = entity.entityversion , sum(COUNT (DISTINCT entity.entityreference)) over(
Two diagnostic steps could help to shed some light on the matter:Ī) add the pseudo column epoch from all original source tables, so dwh_ins_ and dwh_ins_hub.entity_last_version.epoch to the view, and get the max(epoch) from bothī) rewrite your query to: WITH /*+ENABLE_WITH_CLAUSE_MATERIALIZATION */
What I suspect (but cannot prove) is that the same full select (the one coming from the view) is fired two times, but at two different points in time, and some new rows are in one select that are not in the other. The result is not null nor always the same (the number of rows changes each time). Select * from dwh_ins_hub.dim_entity_cumulative_sum ORDER BY DATE(entity.creationDate ) ASC rows UNBOUNDED PRECEDING ) "Cumulative Sum"įROM (dwh_ins_hub.entity JOIN dwh_ins_hub.entity_last_version elv ON (((elv.entityReference = entity.entityReference) AND (elv.last_entityVersion = entity.entityVersion)))) Sum(count (DISTINCT entity.entityReference)) "$user", public, v_catalog, v_monitor, v_internal, v_funcĬREATE OR REPLACE VIEW dwh_ins_hub.dim_entity_cumulative_sum AS