[Bug] Complex queries when placed into views fail to return correct results, sometimes no results

[apatrida]

Search before asking

• I had searched in the issues and found no similar issues.

Version

4.0.5 and 4.1

What's Wrong?

Similar to closed issue #47404

I have a complex query that works fine inline. Take it, add a filter at the end, run it, correct results.

When you wrap this into a view (or an MV) it fails to return results. Something goes wrong in the optimization or folding of the view into the outer query and breaks an otherwise fine SQL SELECT.

There appears to be NO WORKAROUND when this happens. Turning off/on NEREIDS does not change it, runtime_filter_mode does not change it, CBO rule changes does not change the outcome.

I have a bunch of similar views that ALL break.

What You Expected?

Query direct SQL statement, get results.
Query same SQL as a view, get same results.

How to Reproduce?

this CTE:

WITH daily AS (
  SELECT DATE_TRUNC(event_at, 'DAY') AS event_day,
         MINUTE_FLOOR(event_at, 5) AS window_start_at,
         * FROM events
    WHERE event_type = 'watch_time'
          AND (event_sub_type IS NULL OR event_sub_type = '')
          AND event_version = '1'
          AND possible_bot_user_agent <> true
          AND possible_bot_cidr <> true
          AND event_time_classification NOT IN ('FUTURE_UNREASONABLE', 'PAST_UNREASONABLE', 'PAST_LAGGING', 'PAST_SUSPECT')
          AND array_size(experiments) <= 8
), extractFields AS (
    SELECT event_day,
           window_start_at,
           event_at,
           event_key,
           landed_at,
           COALESCE(user_id,0) AS viewing_user_id,
           CAST(user_connect_info.user_client AS VARCHAR) AS viewing_user_client,
           CAST(user_connect_info.user_client_version AS VARCHAR) AS viewing_user_client_version,
           CAST(event_value.video_id AS LARGEINT) AS video_id,
           COALESCE(TRY_CAST(event_value.start AS INTEGER),0) AS watch_start_offset_ms,
           TRY_CAST(event_value.duration AS INTEGER) AS watch_duration_ms,
           TRY_CAST((TRY_CAST(event_value.start AS LARGEINT) +
                     TRY_CAST(event_value.duration AS LARGEINT)
           ) AS INTEGER) AS watch_end_offset_ms,
           COALESCE( -- variants not protected in events need size checks
               LEFT(CAST(event_value.correlator AS STRING),36),
               LEFT(CAST(event_value.search_correlator AS STRING),36)
           ) AS correlation_search_correlator,
           COALESCE(CAST(event_value.autoplay AS BOOLEAN),false) AS watch_is_autoplay,
           COALESCE(CAST(event_value.is_placeholder AS BOOLEAN), false) AS video_is_placeholder,
           LEFT(CAST(event_value.paywall_type AS STRING),15) AS video_paywall_type,
           TRY_CAST(event_value.paywall_type_id AS INTEGER) AS video_paywall_type_id,
           user_session_correlation_id AS correlation_user_session_id,
           CAST(user_connect_info.user_ip_ipv6 AS VARCHAR) AS viewing_user_ip_ipv6,
           LEFT(CAST(user_connect_info.user_agent AS STRING),250) AS viewing_user_user_agent,
           LEFT(CAST(user_connect_info.user_location_iso2 AS STRING),2) AS viewing_user_location_iso2,
           COALESCE(user_is_premium, false) AS viewing_user_is_premium,
           COALESCE(CAST(content_attribution.creator_id AS LARGEINT),
                    CAST(content_attribution.creator_user_id AS LARGEINT)) AS video_creator_id,
           CAST(content_attribution.channel_id AS LARGEINT) AS video_channel_id,
           LEFT(CAST(event_value.session_segment_id AS STRING),36) AS viewing_session_segment_id,
           array_map(x -> LEFT(TRY_CAST(x AS VARCHAR(40)),40), experiments) AS experiments
    FROM daily
), filterDeadRows AS (
  SELECT *
    FROM extractFields
   WHERE watch_duration_ms > 0
          AND watch_duration_ms <> 86400000
          AND video_id IS NOT NULL
          AND video_id > 0
          AND watch_start_offset_ms IS NOT NULL
          AND watch_end_offset_ms IS NOT NULL
          AND watch_duration_ms IS NOT NULL
          AND viewing_user_client IS NOT NULL
), withMD5 AS (
  SELECT *,
         MD5(viewing_user_client) AS viewing_user_client_md5,
         MD5SUM(viewing_user_id, COALESCE(correlation_search_correlator,'-none-')) AS correlation_search_correlator_md5,
         MD5(CAST(ARRAY_SORT(COALESCE(experiments,[])) AS VARCHAR)) AS experiments_md5,
         MD5SUM(coalesce(viewing_user_id, 0),
                coalesce(viewing_user_ip_ipv6, ''),
                coalesce(viewing_user_client, ''),
                coalesce(viewing_user_client_version, ''),
                coalesce(viewing_user_user_agent, '')) AS viewing_anon_md5
    FROM filterDeadRows
), withPossibleKeys AS (
  SELECT *,
         (CAST(CONV(SUBSTRING(viewing_user_client_md5, 1, 16), 16, 10) AS LARGEINT) * 18446744073709551616)
          + CAST(CONV(SUBSTRING(viewing_user_client_md5, 17, 16), 16, 10) AS LARGEINT) AS viewing_user_client_key,
         (CAST(CONV(SUBSTRING(correlation_search_correlator_md5, 1, 16), 16, 10) AS LARGEINT) * 18446744073709551616)
          + CAST(CONV(SUBSTRING(correlation_search_correlator_md5, 17, 16), 16, 10) AS LARGEINT) AS correlation_search_correlator_key,
         (CAST(CONV(SUBSTRING(experiments_md5, 1, 16), 16, 10) AS LARGEINT) * 18446744073709551616)
          + CAST(CONV(SUBSTRING(experiments_md5, 17, 16), 16, 10) AS LARGEINT) AS experiments_key,
         (CAST(CONV(SUBSTRING(viewing_anon_md5, 1, 16), 16, 10) AS LARGEINT) * 18446744073709551616)
          + CAST(CONV(SUBSTRING(viewing_anon_md5, 17, 16), 16, 10) AS LARGEINT) AS viewing_anon_key
    FROM withMD5
), addRollupType AS (
  SELECT *,
         CAST(MILLISECONDS_ADD(CAST(event_at AS DATETIMEV2(3)), watch_duration_ms) AS DATETIMEV2(3)) AS watch_segment_ended_at,
         CASE
              WHEN viewing_user_id > 0 THEN 'U'
              ELSE 'A'
         END AS watch_rollup_type,
         CASE
              WHEN viewing_user_id > 0 THEN viewing_user_id
              ELSE viewing_anon_key
         END AS watch_rollup_key
    FROM withPossibleKeys
), joinVideo AS (
    SELECT e.*,
           v.effective_duration_s AS video_duration_s,
           (v.effective_duration_s+10)*1000 AS _max_watch_offset_ms,
           COALESCE(v.is_short, false) AS video_is_short,
           COALESCE(v.is_livestream, false) AS video_is_livestream,
           IF(v.is_livestream AND
              v.livestream_start IS NOT NULL AND
              v.livestream_end IS NOT NULL AND
              e.event_at < v.livestream_end AND
              e.watch_segment_ended_at > v.livestream_start,
              true,
              false
           ) AS watch_is_live
    FROM addRollupType e
    JOIN video_metadata v ON e.video_id = v.fid
), geometryTaints AS (
   SELECT *,
          IF(watch_start_offset_ms < 0 OR
              watch_end_offset_ms < watch_start_offset_ms OR
              watch_duration_ms <= 0 OR
              (
                 video_duration_s IS NOT NULL AND
                 (
                   watch_start_offset_ms > _max_watch_offset_ms OR
                   watch_end_offset_ms > _max_watch_offset_ms OR
                   watch_start_offset_ms + watch_duration_ms > _max_watch_offset_ms
                 )
              ), 1, 0) AS taint_bad_segment_geometry,
          IF(watch_duration_ms >= 4*60*60*1000 OR
              (video_is_short AND watch_duration_ms >= 30*60*1000),
               1, 0) AS taint_excessive_segment_duration
   FROM joinVideo
), sumTaints AS (
    SELECT *,
           CAST(GREATEST(taint_bad_segment_geometry, taint_excessive_segment_duration) AS BOOLEAN) AS taint_bad_segment
      FROM geometryTaints g
), finalProjection AS (
    SELECT -- rollup
           watch_rollup_type,
           watch_rollup_key,
           viewing_user_client_key,
           correlation_search_correlator_key,
           video_id,
           watch_is_autoplay,
           video_is_placeholder,
           video_paywall_type_id,
           experiments_key,
           window_start_at,
           -- join by
           event_day,
           MIN_BY(event_key, event_at) AS event_key,
           -- calc
           MIN(watch_start_offset_ms) AS watch_start_offset_ms,
           SUM(watch_duration_ms) AS watch_duration_ms,
           MAX(watch_end_offset_ms) AS watch_end_offset_ms,
           MAX(watch_segment_ended_at) AS window_end_at,
           COUNT(*) AS watch_segment_count,
           SUM(taint_bad_segment) AS taint_segment_count,
           -- carry forward non-hashed versions
           MIN_BY(viewing_user_id, event_at) AS viewing_user_id,
           MIN_BY(viewing_user_client, event_at) AS viewing_user_client,
           MIN_BY(correlation_search_correlator, event_at) AS correlation_search_correlator,
           ANY_VALUE(experiments) AS experiments,
           -- wide
           MIN_BY(video_creator_id, event_at) AS video_creator_id,
           MIN_BY(video_channel_id, event_at) AS video_channel_id,
           MAX(viewing_user_is_premium) AS viewing_user_is_premium,
           MIN_BY(viewing_user_location_iso2, event_at) AS viewing_user_location_iso2,
           MIN_BY(correlation_user_session_id, event_at) AS correlation_user_session_id,
           MIN_BY(viewing_session_segment_id, event_at) AS viewing_session_segment_id,
           MIN_BY(video_paywall_type, event_at) AS video_paywall_type,
           MIN_BY(viewing_user_client_version, event_at) AS viewing_user_client_version,
           MAX(video_is_short) AS video_is_short,
           MAX(video_is_livestream) AS video_is_livestream,
           MAX(watch_is_live) AS watch_is_live,
           MIN_BY(video_duration_s, event_at) AS video_duration_s
    FROM sumTaints
    GROUP BY 1,2,3,4,5,6,7,8,9,10,11
)
SELECT  /*+SET_VAR(enable_local_shuffle=true)*/ *
FROM finalProjection
where event_day >= '2026-04-19' LIMIT 1000;

works fine

make it a view:

CREATE VIEW foo AS 
...same CTE minus the WHERE clause

then

SELECT * FROM foo
WHERE event_day >= '2026-04-19' LIMIT 1000;

0 results.

underlaying table is:

CREATE TABLE events (
        event_key LARGEINT NOT NULL,
        event_at datetime NOT NULL,
        landed_at datetime NOT NULL,
        created_at datetime NOT NULL,
        event_type VARCHAR(20) NOT NULL,
        event_sub_type VARCHAR(20),
        event_version VARCHAR(4),
        user_id LARGEINT,
        user_is_premium boolean,
        user_session_correlation_id VARCHAR(36),
        event_value variant<
            'video_id':largeint,
            'start':bigint,
            'duration':bigint,
            'autoplay':boolean,
            'search_correlator':text,
            'correlator':text,
            'session_segment_id':text,
            'first_ordinal':bigint
        >,
        content_attribution variant<
             'creator_id':largeint,
             'creator_user_id':largeint,
             'channel_id':largeint,
             'video_id':largeint
        >,
        experiments array<text>,
        tags array<text>,
        referer_info variant,
        user_connect_info variant<
            'user_client':text,
            'user_location_iso2':text,
            'user_ip_ipv4':ipv4,
            'user_ip_ipv6':ipv6
        >,
        ingest_info variant<
            'source_server':text,
            'source_file':text,
            'ingested_at':datetimev2(0),
            'landed_at':datetimev2(0)
        >,
        closet variant,
        event_time_classification VARCHAR(20) NOT NULL,
        possible_bot_user_agent boolean NOT NULL,
        possible_bot_cidr boolean NOT NULL
)
UNIQUE KEY(event_key, event_at)
CLUSTER BY (event_type, event_at)
PARTITION BY RANGE(date_trunc(event_at, 'day')) ()
DISTRIBUTED BY HASH(event_key) BUCKETS 16
PROPERTIES (
               'replication_num' = '1',
               'storage_format' = 'V3',
               'function_column.sequence_col' = 'landed_at'
           );

but other variations of this with thinner columns still shows the same issue. CASE statements in the CTE seem to cause more issues, but not sure is the cause.

You can add a few rows to the table and have same problem.

Anything Else?

plan for querying the CTE directly:

PLAN FRAGMENT 0
  OUTPUT EXPRS:
    watch_rollup_type[#790]
    watch_rollup_key[#791]
    viewing_user_client_key[#792]
    correlation_search_correlator_key[#793]
    video_id[#794]
    watch_is_autoplay[#795]
    video_is_placeholder[#796]
    video_paywall_type_id[#797]
    experiments_key[#798]
    window_start_at[#799]
    event_day[#800]
    event_key[#801]
    watch_start_offset_ms[#802]
    watch_duration_ms[#803]
    watch_end_offset_ms[#804]
    window_end_at[#805]
    watch_segment_count[#806]
    taint_segment_count[#807]
    viewing_user_id[#808]
    viewing_user_client[#809]
    correlation_search_correlator[#810]
    experiments[#811]
    video_creator_id[#812]
    video_channel_id[#813]
    viewing_user_is_premium[#814]
    viewing_user_location_iso2[#815]
    correlation_user_session_id[#816]
    viewing_session_segment_id[#817]
    video_paywall_type[#818]
    viewing_user_client_version[#819]
    video_is_short[#820]
    video_is_livestream[#821]
    watch_is_live[#822]
    video_duration_s[#823]
  PARTITION: UNPARTITIONED
""
  HAS_COLO_PLAN_NODE: false
""
  VRESULT SINK
     MYSQL_PROTOCOL
""
  7:VMERGING-EXCHANGE
     offset: 0
     limit: 1000
     final projections: watch_rollup_type[#756], watch_rollup_key[#757], viewing_user_client_key[#758], correlation_search_correlator_key[#759], video_id[#760], watch_is_autoplay[#761], video_is_placeholder[#762], video_paywall_type_id[#763], experiments_key[#764], window_start_at[#765], event_day[#766], event_key[#767], watch_start_offset_ms[#768], watch_duration_ms[#769], watch_end_offset_ms[#770], window_end_at[#771], watch_segment_count[#772], taint_segment_count[#773], viewing_user_id[#774], viewing_user_client[#775], correlation_search_correlator[#776], experiments[#777], video_creator_id[#778], video_channel_id[#779], viewing_user_is_premium[#780], viewing_user_location_iso2[#781], correlation_user_session_id[#782], viewing_session_segment_id[#783], video_paywall_type[#784], viewing_user_client_version[#785], video_is_short[#786], video_is_livestream[#787], watch_is_live[#788], video_duration_s[#789]
     final project output tuple id: 19
     distribute expr lists: 
""
PLAN FRAGMENT 1
""
  PARTITION: HASH_PARTITIONED: fid[#572]
""
  HAS_COLO_PLAN_NODE: true
""
  STREAM DATA SINK
    EXCHANGE ID: 07
    UNPARTITIONED
IS_MERGE: true
""
  6:VTOP-N(1652)
  |  order by: watch_rollup_type[#756] ASC, watch_rollup_key[#757] ASC, viewing_user_client_key[#758] ASC, correlation_search_correlator_key[#759] ASC, video_id[#760] ASC, watch_is_autoplay[#761] ASC, video_is_placeholder[#762] ASC, video_paywall_type_id[#763] ASC, experiments_key[#764] ASC, window_start_at[#765] ASC, event_day[#766] ASC
  |  algorithm: heap sort
  |  local merge sort
  |  merge by exchange
  |  offset: 0
  |  limit: 1000
  |  distribute expr lists: video_id[#726]
  |  
  5:VAGGREGATE (merge finalize)(1648)
  |  output: min_by(event_key[#708], event_at[#706])[#733], min(watch_start_offset_ms[#715])[#734], sum(watch_duration_ms[#717])[#735], max(watch_end_offset_ms[#718])[#736], max(watch_segment_ended_at[#707])[#737], count(*)[#738], sum(CAST(taint_bad_segment[#719] AS tinyint))[#739], min_by(viewing_user_id[#709], event_at[#706])[#740], min_by(viewing_user_client[#711], event_at[#706])[#741], min_by(correlation_search_correlator[#720], event_at[#706])[#742], any_value(experiments[#705])[#743], min_by(video_creator_id[#702], event_at[#706])[#744], min_by(video_channel_id[#703], event_at[#706])[#745], max(viewing_user_is_premium[#701])[#746], min_by(viewing_user_location_iso2[#700], event_at[#706])[#747], min_by(correlation_user_session_id[#699], event_at[#706])[#748], min_by(viewing_session_segment_id[#704], event_at[#706])[#749], min_by(video_paywall_type[#721], event_at[#706])[#750], min_by(viewing_user_client_version[#712], event_at[#706])[#751], max(video_is_short[#713])[#752], max(video_is_livestream[#714])[#753], max(watch_is_live[#716])[#754], min_by(video_duration_s[#710], event_at[#706])[#755]
  |  group by: watch_rollup_type[#688], watch_rollup_key[#689], viewing_user_client_key[#690], correlation_search_correlator_key[#691], video_id[#692], watch_is_autoplay[#693], video_is_placeholder[#694], video_paywall_type_id[#695], experiments_key[#696], window_start_at[#697], event_day[#698]
  |  sortByGroupKey:true
  |  cardinality=22,779,281
  |  limit: 1000
  |  distribute expr lists: video_id[#692]
  |  
  4:VHASH JOIN(1640)
  |  join op: INNER JOIN(PARTITIONED)[]
  |  equal join conjunct: (fid[#572] = video_id[#499])
  |  cardinality=68,337,843
  |  vec output tuple id: 16
  |  output tuple id: 16
  |  vIntermediate tuple ids: 13 
  |  hash output slot ids: 576 512 577 513 514 515 516 517 518 519 520 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 573 510 574 575 511 
  |  runtime filters: RF000[min_max] <- video_id[#499](9656316/8388608/8388608), RF001[in_or_bloom] <- video_id[#499](9656316/8388608/8388608)
  |  final projections: watch_rollup_type[#650], watch_rollup_key[#651], viewing_user_client_key[#652], correlation_search_correlator_key[#653], video_id[#654], watch_is_autoplay[#655], video_is_placeholder[#656], video_paywall_type_id[#657], experiments_key[#658], window_start_at[#659], event_day[#660], correlation_user_session_id[#661], viewing_user_location_iso2[#662], viewing_user_is_premium[#663], video_creator_id[#664], video_channel_id[#665], viewing_session_segment_id[#666], experiments[#667], event_at[#668], watch_segment_ended_at[#669], event_key[#670], viewing_user_id[#671], effective_duration_s[#672], viewing_user_client[#673], viewing_user_client_version[#674], coalesce(is_short, FALSE)[#686], coalesce(is_livestream[#676], FALSE), watch_start_offset_ms[#677], ((((is_livestream[#676] <=> TRUE) AND livestream_start[#678] IS NOT NULL) AND livestream_end[#679] IS NOT NULL) AND ((event_at[#668] < livestream_end[#679]) AND ((watch_segment_ended_at[#669] > CAST(livestream_start[#678] AS datetimev2(3))) <=> TRUE))), watch_duration_ms[#680], watch_end_offset_ms[#681], CAST(greatest(if((((watch_start_offset_ms[#677] < 0) OR (watch_end_offset_ms[#681] < watch_start_offset_ms[#677])) OR ((watch_duration_ms[#680] <= 0) OR (effective_duration_s[#672] IS NOT NULL AND (((cast(watch_start_offset_ms as BIGINT)[#685] > ((effective_duration_s + 10) * 1000)[#687]) OR (CAST(watch_end_offset_ms[#681] AS bigint) > ((effective_duration_s + 10) * 1000)[#687])) OR ((cast(watch_start_offset_ms as BIGINT)[#685] + CAST(watch_duration_ms[#680] AS bigint)) > ((effective_duration_s + 10) * 1000)[#687]))))), 1, 0), if(((watch_duration_ms[#680] >= 14400000) OR (coalesce(is_short, FALSE)[#686] AND (watch_duration_ms[#680] >= 1800000))), 1, 0)) AS boolean), correlation_search_correlator[#682], video_paywall_type[#683]
  |  final project output tuple id: 16
  |  intermediate projections: watch_rollup_type[#613], watch_rollup_key[#614], viewing_user_client_key[#615], correlation_search_correlator_key[#616], video_id[#617], watch_is_autoplay[#618], video_is_placeholder[#619], video_paywall_type_id[#620], experiments_key[#621], window_start_at[#622], event_day[#623], correlation_user_session_id[#624], viewing_user_location_iso2[#625], viewing_user_is_premium[#626], video_creator_id[#627], video_channel_id[#628], viewing_session_segment_id[#629], experiments[#630], event_at[#631], watch_segment_ended_at[#632], event_key[#633], viewing_user_id[#634], effective_duration_s[#635], viewing_user_client[#636], viewing_user_client_version[#637], is_short[#638], is_livestream[#639], watch_start_offset_ms[#640], livestream_start[#641], livestream_end[#642], watch_duration_ms[#643], watch_end_offset_ms[#644], correlation_search_correlator[#645], video_paywall_type[#646], (effective_duration_s + 10)[#647], cast(watch_start_offset_ms as BIGINT)[#648], coalesce(is_short, FALSE)[#649], ((effective_duration_s + 10)[#647] * 1000)
  |  intermediate tuple id: 15
  |  intermediate projections: watch_rollup_type[#611], watch_rollup_key[#612], viewing_user_client_key[#607], correlation_search_correlator_key[#608], video_id[#591], watch_is_autoplay[#596], video_is_placeholder[#597], video_paywall_type_id[#599], experiments_key[#609], window_start_at[#585], event_day[#584], correlation_user_session_id[#600], viewing_user_location_iso2[#601], viewing_user_is_premium[#602], video_creator_id[#603], video_channel_id[#604], viewing_session_segment_id[#605], experiments[#606], event_at[#586], watch_segment_ended_at[#610], event_key[#587], viewing_user_id[#588], effective_duration_s[#583], viewing_user_client[#589], viewing_user_client_version[#590], is_short[#582], is_livestream[#579], watch_start_offset_ms[#592], livestream_start[#580], livestream_end[#581], watch_duration_ms[#593], watch_end_offset_ms[#594], correlation_search_correlator[#595], video_paywall_type[#598], (effective_duration_s[#583] + 10), CAST(watch_start_offset_ms[#592] AS bigint), coalesce(is_short[#582], FALSE)
  |  intermediate tuple id: 14
  |  distribute expr lists: fid[#572]
  |  distribute expr lists: video_id[#499]
  |  
  |----1:VEXCHANGE
  |       offset: 0
  |       distribute expr lists: 
  |    
  3:VEXCHANGE
     offset: 0
     distribute expr lists: fid[#572]
""
PLAN FRAGMENT 2
""
  PARTITION: HASH_PARTITIONED: fid[#521]
""
  HAS_COLO_PLAN_NODE: false
""
  STREAM DATA SINK
    EXCHANGE ID: 03
    HASH_PARTITIONED: fid[#572]
""
  2:VOlapScanNode(1611)
     TABLE: video_metadata(video_metadata), PREAGGREGATION: ON
     PREDICATES: ((fid[#521] > 0) AND (__DORIS_DELETE_SIGN__[#569] = 0))
     partitions=1/1 (video_metadata)
     tablets=32/32, tabletList=1776129913941,1776129913943,1776129913945 ...
     cardinality=68337843, avgRowSize=3587.096, numNodes=4
     pushAggOp=NONE
     runtime filters: RF000[min_max] -> fid[#521], RF001[in_or_bloom] -> fid[#521]
     final projections: fid[#521], is_livestream[#537], livestream_start[#541], livestream_end[#542], is_short[#565], effective_duration_s[#568]
     final project output tuple id: 12
""
PLAN FRAGMENT 3
""
  PARTITION: RANDOM
""
  HAS_COLO_PLAN_NODE: false
""
  STREAM DATA SINK
    EXCHANGE ID: 01
    HASH_PARTITIONED: video_id[#499]
""
  0:VOlapScanNode(1624)
     TABLE: events(events), PREAGGREGATION: ON
     PREDICATES: ((((((date_trunc(event_at[#1], 'DAY') >= '2026-04-19 00:00:00') AND (TRY_CAST(event_value[#17] AS int) > 0)) AND ((TRY_CAST(event_value[#17] AS int) != 86400000) AND TRY_CAST(event_value[#17] AS int) IS NOT NULL)) AND (((CAST(event_value[#19] AS largeint) > 0) AND CAST(event_value[#19] AS largeint) IS NOT NULL) AND (coalesce(TRY_CAST(event_value[#18] AS int), 0) IS NOT NULL AND TRY_CAST((TRY_CAST(event_value[#18] AS largeint) + TRY_CAST(event_value[#17] AS largeint)) AS int) IS NOT NULL))) AND (((CAST(user_connect_info[#29] AS varchar(65533)) IS NOT NULL AND (event_type[#4] = 'watch_time')) AND (((event_sub_type[#5] = '') OR event_sub_type[#5] IS NULL) AND (event_version[#6] = '1'))) AND (((possible_bot_user_agent[#37] != TRUE) AND (possible_bot_cidr[#38] != TRUE)) AND (event_time_classification[#36] NOT IN ('FUTURE_UNREASONABLE', 'PAST_UNREASONABLE', 'PAST_LAGGING', 'PAST_SUSPECT') AND (cardinality(experiments[#25]) <= 8))))) AND (__DORIS_DELETE_SIGN__[#39] = 0))
     partitions=3/118 (p20260419000000,p20260418000000,p20260420000000)
     tablets=48/48, tabletList=1776290375409,1776290375411,1776290375413 ...
     cardinality=410907142, avgRowSize=524.95105, numNodes=4
     pushAggOp=NONE
     nested columns:
       event_value:
         origin type: variant
         all access paths: [event_value.correlator]
       event_value:
         origin type: variant
         all access paths: [event_value.search_correlator]
       event_value:
         origin type: variant
         all access paths: [event_value.paywall_type_id]
       event_value:
         origin type: variant
         all access paths: [event_value.paywall_type]
       event_value:
         origin type: variant
         all access paths: [event_value.session_segment_id]
       event_value:
         origin type: variant
         all access paths: [event_value.is_placeholder]
       event_value:
         origin type: variant
         all access paths: [event_value.duration]
         predicate access paths: [event_value.duration]
       event_value:
         origin type: variant
         all access paths: [event_value.start]
         predicate access paths: [event_value.start]
       event_value:
         origin type: variant
         all access paths: [event_value.video_id]
         predicate access paths: [event_value.video_id]
       event_value:
         origin type: variant
         all access paths: [event_value.autoplay]
       content_attribution:
         origin type: variant
         all access paths: [content_attribution.creator_user_id]
       content_attribution:
         origin type: variant
         all access paths: [content_attribution.creator_id]
       content_attribution:
         origin type: variant
         all access paths: [content_attribution.channel_id]
       experiments:
         origin type: array<text>
         all access paths: [experiments]
         predicate access paths: [experiments]
       user_connect_info:
         origin type: variant
         all access paths: [user_connect_info.user_client]
         predicate access paths: [user_connect_info.user_client]
       user_connect_info:
         origin type: variant
         all access paths: [user_connect_info.user_client_version]
       user_connect_info:
         origin type: variant
         all access paths: [user_connect_info.user_ip_ipv6]
       user_connect_info:
         origin type: variant
         all access paths: [user_connect_info.user_location_iso2]
       user_connect_info:
         origin type: variant
         all access paths: [user_connect_info.user_agent]
     final projections: date_trunc(event_at[#434], 'DAY'), minute_floor(event_at[#434], 5), event_at[#434], event_key[#435], coalesce(user_id, 0)[#459], cast(user_connect_info.user_client['user_client'] as VARCHAR(65533))[#466], cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533))[#472], CAST(event_value[#439] AS largeint), coalesce(TRY_CAST(event_value[#440] AS int), 0), tryCast(event_value.duration['duration'] as INT)[#470], TRY_CAST((TRY_CAST(event_value[#440] AS largeint) + TRY_CAST(event_value[#441] AS largeint)) AS int), coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36))[#482], coalesce(CAST(event_value[#444] AS boolean), FALSE), coalesce(CAST(event_value[#445] AS boolean), FALSE), left(CAST(event_value[#446] AS text), 15), TRY_CAST(event_value[#447] AS int), user_session_correlation_id[#448], left(CAST(user_connect_info[#449] AS text), 2), coalesce(user_is_premium[#450], FALSE), coalesce(CAST(content_attribution[#451] AS largeint), CAST(content_attribution[#452] AS largeint)), CAST(content_attribution[#453] AS largeint), left(CAST(event_value[#454] AS text), 36), array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments)[#485], ((CAST(conv(substring(md5(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)))[#475], 1, 16), 16, 10) AS largeint) * 18446744073709551616) + CAST(conv(substring(md5(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)))[#475], 17, 16), 16, 10) AS largeint)), ((CAST(conv(substring(md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)), '-none-'))[#487], 1, 16), 16, 10) AS largeint) * 18446744073709551616) + CAST(conv(substring(md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)), '-none-'))[#487], 17, 16), 16, 10) AS largeint)), ((CAST(conv(substring(md5(cast(array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])) as VARCHAR(65533)))[#491], 1, 16), 16, 10) AS largeint) * 18446744073709551616) + CAST(conv(substring(md5(cast(array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])) as VARCHAR(65533)))[#491], 17, 16), 16, 10) AS largeint)), CAST(milliseconds_add(CAST(event_at[#434] AS datetimev2(3)), CAST(tryCast(event_value.duration['duration'] as INT)[#470] AS bigint)) AS datetimev2(3)), if((coalesce(user_id, 0) > 0)[#471], 'U', 'A'), if((coalesce(user_id, 0) > 0)[#471], coalesce(user_id, 0)[#459], ((CAST(conv(substring(md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), ''), coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), ''), coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250), ''))[#486], 1, 16), 16, 10) AS largeint) * 18446744073709551616) + CAST(conv(substring(md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), ''), coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), ''), coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250), ''))[#486], 17, 16), 16, 10) AS largeint)))
     final project output tuple id: 10
     intermediate projections: event_at[#377], event_key[#378], user_id[#379], user_connect_info[#380], user_connect_info[#381], event_value[#382], event_value[#383], event_value[#384], event_value[#385], event_value[#386], event_value[#387], event_value[#388], event_value[#389], event_value[#390], user_session_correlation_id[#391], user_connect_info[#392], user_is_premium[#393], content_attribution[#394], content_attribution[#395], content_attribution[#396], event_value[#397], experiments[#398], user_connect_info[#399], user_connect_info[#400], user_connect_info.user_client[#401], coalesce(user_id, 0)[#402], event_value.correlator[#403], event_value.search_correlator[#404], event_value.duration[#405], user_connect_info.user_client_version[#406], user_connect_info.user_ip_ipv6[#407], user_connect_info.user_agent[#408], cast(user_connect_info.user_client['user_client'] as VARCHAR(65533))[#409], cast(event_value.correlator['correlator'] as TEXT)[#410], cast(event_value.search_correlator['search_correlator'] as TEXT)[#411], cast(coalesce(user_id, 0) as VARCHAR(65533))[#412], tryCast(event_value.duration['duration'] as INT)[#413], (coalesce(user_id, 0) > 0)[#414], cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533))[#415], cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533))[#416], cast(user_connect_info.user_agent['user_agent'] as TEXT)[#417], md5(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)))[#418], left(cast(event_value.correlator['correlator'] as TEXT), 36)[#419], left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)[#420], coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), '')[#421], coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), '')[#422], coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), '')[#423], left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250)[#424], coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36))[#425], coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250), '')[#426], coalesce(coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)), '-none-')[#427], array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments)[#428], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), ''), coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), ''), coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250), ''))[#429], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)), '-none-'))[#430], coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])[#431], array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), []))[#432], cast(array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])) as VARCHAR(65533))[#433], md5(cast(array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])) as VARCHAR(65533))[#433])
     intermediate tuple id: 9
     intermediate projections: event_at[#321], event_key[#322], user_id[#323], user_connect_info[#324], user_connect_info[#325], event_value[#326], event_value[#327], event_value[#328], event_value[#329], event_value[#330], event_value[#331], event_value[#332], event_value[#333], event_value[#334], user_session_correlation_id[#335], user_connect_info[#336], user_is_premium[#337], content_attribution[#338], content_attribution[#339], content_attribution[#340], event_value[#341], experiments[#342], user_connect_info[#343], user_connect_info[#344], user_connect_info.user_client[#345], coalesce(user_id, 0)[#346], event_value.correlator[#347], event_value.search_correlator[#348], event_value.duration[#349], user_connect_info.user_client_version[#350], user_connect_info.user_ip_ipv6[#351], user_connect_info.user_agent[#352], cast(user_connect_info.user_client['user_client'] as VARCHAR(65533))[#353], cast(event_value.correlator['correlator'] as TEXT)[#354], cast(event_value.search_correlator['search_correlator'] as TEXT)[#355], cast(coalesce(user_id, 0) as VARCHAR(65533))[#356], tryCast(event_value.duration['duration'] as INT)[#357], (coalesce(user_id, 0) > 0)[#358], cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533))[#359], cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533))[#360], cast(user_connect_info.user_agent['user_agent'] as TEXT)[#361], md5(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)))[#362], left(cast(event_value.correlator['correlator'] as TEXT), 36)[#363], left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)[#364], coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), '')[#365], coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), '')[#366], coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), '')[#367], left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250)[#368], coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36))[#369], coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250), '')[#370], coalesce(coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)), '-none-')[#371], array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments)[#372], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), ''), coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), ''), coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250), ''))[#373], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)), '-none-'))[#374], coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])[#375], array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), []))[#376], CAST(array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), []))[#376] AS varchar(65533))
     intermediate tuple id: 8
     intermediate projections: event_at[#266], event_key[#267], user_id[#268], user_connect_info[#269], user_connect_info[#270], event_value[#271], event_value[#272], event_value[#273], event_value[#274], event_value[#275], event_value[#276], event_value[#277], event_value[#278], event_value[#279], user_session_correlation_id[#280], user_connect_info[#281], user_is_premium[#282], content_attribution[#283], content_attribution[#284], content_attribution[#285], event_value[#286], experiments[#287], user_connect_info[#288], user_connect_info[#289], user_connect_info.user_client[#290], coalesce(user_id, 0)[#291], event_value.correlator[#292], event_value.search_correlator[#293], event_value.duration[#294], user_connect_info.user_client_version[#295], user_connect_info.user_ip_ipv6[#296], user_connect_info.user_agent[#297], cast(user_connect_info.user_client['user_client'] as VARCHAR(65533))[#298], cast(event_value.correlator['correlator'] as TEXT)[#299], cast(event_value.search_correlator['search_correlator'] as TEXT)[#300], cast(coalesce(user_id, 0) as VARCHAR(65533))[#301], tryCast(event_value.duration['duration'] as INT)[#302], (coalesce(user_id, 0) > 0)[#303], cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533))[#304], cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533))[#305], cast(user_connect_info.user_agent['user_agent'] as TEXT)[#306], md5(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)))[#307], left(cast(event_value.correlator['correlator'] as TEXT), 36)[#308], left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)[#309], coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), '')[#310], coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), '')[#311], coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), '')[#312], left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250)[#313], coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36))[#314], coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250), '')[#315], coalesce(coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)), '-none-')[#316], array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments)[#317], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), ''), coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), ''), coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250), ''))[#318], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)), '-none-'))[#319], coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])[#320], array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])[#320])
     intermediate tuple id: 7
     intermediate projections: event_at[#213], event_key[#214], user_id[#215], user_connect_info[#216], user_connect_info[#217], event_value[#218], event_value[#219], event_value[#220], event_value[#221], event_value[#222], event_value[#223], event_value[#224], event_value[#225], event_value[#226], user_session_correlation_id[#227], user_connect_info[#228], user_is_premium[#229], content_attribution[#230], content_attribution[#231], content_attribution[#232], event_value[#233], experiments[#234], user_connect_info[#235], user_connect_info[#236], user_connect_info.user_client[#237], coalesce(user_id, 0)[#238], event_value.correlator[#239], event_value.search_correlator[#240], event_value.duration[#241], user_connect_info.user_client_version[#242], user_connect_info.user_ip_ipv6[#243], user_connect_info.user_agent[#244], cast(user_connect_info.user_client['user_client'] as VARCHAR(65533))[#245], cast(event_value.correlator['correlator'] as TEXT)[#246], cast(event_value.search_correlator['search_correlator'] as TEXT)[#247], cast(coalesce(user_id, 0) as VARCHAR(65533))[#248], tryCast(event_value.duration['duration'] as INT)[#249], (coalesce(user_id, 0) > 0)[#250], cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533))[#251], cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533))[#252], cast(user_connect_info.user_agent['user_agent'] as TEXT)[#253], md5(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)))[#254], left(cast(event_value.correlator['correlator'] as TEXT), 36)[#255], left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)[#256], coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), '')[#257], coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), '')[#258], coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), '')[#259], left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250)[#260], coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36))[#261], coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250), '')[#262], coalesce(coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)), '-none-')[#263], array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments)[#264], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), ''), coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), ''), coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250), ''))[#265], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533))[#248], coalesce(coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)), '-none-')[#263]), coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments)[#264], [])
     intermediate tuple id: 6
     intermediate projections: event_at[#163], event_key[#164], user_id[#165], user_connect_info[#166], user_connect_info[#167], event_value[#168], event_value[#169], event_value[#170], event_value[#171], event_value[#172], event_value[#173], event_value[#174], event_value[#175], event_value[#176], user_session_correlation_id[#177], user_connect_info[#178], user_is_premium[#179], content_attribution[#180], content_attribution[#181], content_attribution[#182], event_value[#183], experiments[#184], user_connect_info[#185], user_connect_info[#186], user_connect_info.user_client[#187], coalesce(user_id, 0)[#188], event_value.correlator[#189], event_value.search_correlator[#190], event_value.duration[#191], user_connect_info.user_client_version[#192], user_connect_info.user_ip_ipv6[#193], user_connect_info.user_agent[#194], cast(user_connect_info.user_client['user_client'] as VARCHAR(65533))[#195], cast(event_value.correlator['correlator'] as TEXT)[#196], cast(event_value.search_correlator['search_correlator'] as TEXT)[#197], cast(coalesce(user_id, 0) as VARCHAR(65533))[#198], tryCast(event_value.duration['duration'] as INT)[#199], (coalesce(user_id, 0) > 0)[#200], cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533))[#201], cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533))[#202], cast(user_connect_info.user_agent['user_agent'] as TEXT)[#203], md5(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)))[#204], left(cast(event_value.correlator['correlator'] as TEXT), 36)[#205], left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)[#206], coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), '')[#207], coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), '')[#208], coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), '')[#209], left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250)[#210], coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36))[#211], coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250), '')[#212], coalesce(coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36))[#211], '-none-'), array_map(x -> left(substring(CAST(x AS varchar(40)), 1, 40), 40), experiments[#184]), md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533))[#198], coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), '')[#207], coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), '')[#208], coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), '')[#209], coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250), '')[#212])
     intermediate tuple id: 5
     intermediate projections: event_at[#115], event_key[#116], user_id[#117], user_connect_info[#118], user_connect_info[#119], event_value[#120], event_value[#121], event_value[#122], event_value[#123], event_value[#124], event_value[#125], event_value[#126], event_value[#127], event_value[#128], user_session_correlation_id[#129], user_connect_info[#130], user_is_premium[#131], content_attribution[#132], content_attribution[#133], content_attribution[#134], event_value[#135], experiments[#136], user_connect_info[#137], user_connect_info[#138], user_connect_info.user_client[#139], coalesce(user_id, 0)[#140], event_value.correlator[#141], event_value.search_correlator[#142], event_value.duration[#143], user_connect_info.user_client_version[#144], user_connect_info.user_ip_ipv6[#145], user_connect_info.user_agent[#146], cast(user_connect_info.user_client['user_client'] as VARCHAR(65533))[#147], cast(event_value.correlator['correlator'] as TEXT)[#148], cast(event_value.search_correlator['search_correlator'] as TEXT)[#149], cast(coalesce(user_id, 0) as VARCHAR(65533))[#150], tryCast(event_value.duration['duration'] as INT)[#151], (coalesce(user_id, 0) > 0)[#152], cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533))[#153], cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533))[#154], cast(user_connect_info.user_agent['user_agent'] as TEXT)[#155], md5(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)))[#156], left(cast(event_value.correlator['correlator'] as TEXT), 36)[#157], left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)[#158], coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533)), '')[#159], coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533)), '')[#160], coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533)), '')[#161], left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250)[#162], coalesce(left(cast(event_value.correlator['correlator'] as TEXT), 36)[#157], left(cast(event_value.search_correlator['search_correlator'] as TEXT), 36)[#158]), coalesce(left(cast(user_connect_info.user_agent['user_agent'] as TEXT), 250)[#162], '')
     intermediate tuple id: 4
     intermediate projections: event_at[#74], event_key[#75], user_id[#76], user_connect_info[#77], user_connect_info[#78], event_value[#79], event_value[#80], event_value[#81], event_value[#82], event_value[#83], event_value[#84], event_value[#85], event_value[#86], event_value[#87], user_session_correlation_id[#88], user_connect_info[#89], user_is_premium[#90], content_attribution[#91], content_attribution[#92], content_attribution[#93], event_value[#94], experiments[#95], user_connect_info[#96], user_connect_info[#97], user_connect_info.user_client[#98], coalesce(user_id, 0)[#99], event_value.correlator[#100], event_value.search_correlator[#101], event_value.duration[#102], user_connect_info.user_client_version[#103], user_connect_info.user_ip_ipv6[#104], user_connect_info.user_agent[#105], cast(user_connect_info.user_client['user_client'] as VARCHAR(65533))[#106], cast(event_value.correlator['correlator'] as TEXT)[#107], cast(event_value.search_correlator['search_correlator'] as TEXT)[#108], cast(coalesce(user_id, 0) as VARCHAR(65533))[#109], tryCast(event_value.duration['duration'] as INT)[#110], (coalesce(user_id, 0) > 0)[#111], cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533))[#112], cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533))[#113], cast(user_connect_info.user_agent['user_agent'] as TEXT)[#114], md5(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533))[#106]), left(cast(event_value.correlator['correlator'] as TEXT)[#107], 36), left(cast(event_value.search_correlator['search_correlator'] as TEXT)[#108], 36), coalesce(cast(user_connect_info.user_ip_ipv6['user_ip_ipv6'] as VARCHAR(65533))[#113], ''), coalesce(cast(user_connect_info.user_client['user_client'] as VARCHAR(65533))[#106], ''), coalesce(cast(user_connect_info.user_client_version['user_client_version'] as VARCHAR(65533))[#112], ''), left(cast(user_connect_info.user_agent['user_agent'] as TEXT)[#114], 250)
     intermediate tuple id: 3
     intermediate projections: event_at[#42], event_key[#43], user_id[#44], user_connect_info[#45], user_connect_info[#46], event_value[#47], event_value[#48], event_value[#49], event_value[#50], event_value[#51], event_value[#52], event_value[#53], event_value[#54], event_value[#55], user_session_correlation_id[#56], user_connect_info[#57], user_is_premium[#58], content_attribution[#59], content_attribution[#60], content_attribution[#61], event_value[#62], experiments[#63], user_connect_info[#64], user_connect_info[#65], user_connect_info.user_client[#66], coalesce(user_id, 0)[#67], event_value.correlator[#68], event_value.search_correlator[#69], event_value.duration[#70], user_connect_info.user_client_version[#71], user_connect_info.user_ip_ipv6[#72], user_connect_info.user_agent[#73], CAST(user_connect_info.user_client[#66] AS varchar(65533)), CAST(event_value.correlator[#68] AS text), CAST(event_value.search_correlator[#69] AS text), CAST(coalesce(user_id, 0)[#67] AS varchar(65533)), TRY_CAST(event_value.duration[#70] AS int), (coalesce(user_id, 0)[#67] > 0), CAST(user_connect_info.user_client_version[#71] AS varchar(65533)), CAST(user_connect_info.user_ip_ipv6[#72] AS varchar(65533)), CAST(user_connect_info.user_agent[#73] AS text)
     intermediate tuple id: 2
     intermediate projections: event_at[#1], event_key[#0], user_id[#7], user_connect_info[#29], user_connect_info[#30], event_value[#19], event_value[#18], event_value[#17], event_value[#11], event_value[#12], event_value[#20], event_value[#16], event_value[#14], event_value[#13], user_session_correlation_id[#9], user_connect_info[#32], user_is_premium[#8], content_attribution[#23], content_attribution[#22], content_attribution[#24], event_value[#15], experiments[#25], user_connect_info[#31], user_connect_info[#33], user_connect_info[#29], coalesce(user_id[#7], 0), event_value[#11], event_value[#12], event_value[#17], user_connect_info[#30], user_connect_info[#31], user_connect_info[#33]
     intermediate tuple id: 1
""
""
========== MATERIALIZATIONS ==========
""
MaterializedView
MaterializedViewRewriteSuccessAndChose:
""
MaterializedViewRewriteSuccessButNotChose:
""
MaterializedViewRewriteFail:
 CBO.internal.events_watch_time_mv fail
  FailInfo: View struct info is invalid, Materialized view rule exec fail
""
""
========== STATISTICS ==========
planned with unknown column statistics

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[apatrida]

And the plan for the same wrapped into a foo, this returns 0 results

select watch_rollup_type,
           watch_rollup_key,
           viewing_user_client_key,
           correlation_search_correlator_key,
           video_id,
           watch_is_autoplay,
           video_is_placeholder,
           video_paywall_type_id,
           experiments_key,
           window_start_at,
           event_day
from foo
where event_day >= '2026-04-19'
LIMIT 100;

and the plan:

PLAN FRAGMENT 0
  OUTPUT EXPRS:
    watch_rollup_type[#313]
    watch_rollup_key[#314]
    viewing_user_client_key[#315]
    correlation_search_correlator_key[#316]
    video_id[#317]
    watch_is_autoplay[#318]
    video_is_placeholder[#319]
    video_paywall_type_id[#320]
    experiments_key[#321]
    window_start_at[#322]
    event_day[#323]
  PARTITION: UNPARTITIONED
""
  HAS_COLO_PLAN_NODE: false
""
  VRESULT SINK
     MYSQL_PROTOCOL
""
  6:VEXCHANGE
     offset: 0
     limit: 100
     distribute expr lists: video_id[#317]
""
PLAN FRAGMENT 1
""
  PARTITION: HASH_PARTITIONED: fid[#278]
""
  HAS_COLO_PLAN_NODE: true
""
  STREAM DATA SINK
    EXCHANGE ID: 06
    UNPARTITIONED
""
  5:VAGGREGATE (merge finalize)(1953)
  |  group by: watch_rollup_type[#291], watch_rollup_key[#292], viewing_user_client_key[#293], correlation_search_correlator_key[#294], video_id[#295], watch_is_autoplay[#296], video_is_placeholder[#297], video_paywall_type_id[#298], experiments_key[#299], window_start_at[#300], event_day[#301]
  |  sortByGroupKey:false
  |  cardinality=22,779,281
  |  limit: 100
  |  final projections: watch_rollup_type[#302], watch_rollup_key[#303], viewing_user_client_key[#304], correlation_search_correlator_key[#305], video_id[#306], watch_is_autoplay[#307], video_is_placeholder[#308], video_paywall_type_id[#309], experiments_key[#310], window_start_at[#311], event_day[#312]
  |  final project output tuple id: 16
  |  distribute expr lists: video_id[#295]
  |  
  4:VHASH JOIN(1945)
  |  join op: INNER JOIN(PARTITIONED)[]
  |  equal join conjunct: (fid[#278] = video_id[#218])
  |  cardinality=68,337,843
  |  vec output tuple id: 14
  |  output tuple id: 14
  |  vIntermediate tuple ids: 13 
  |  hash output slot ids: 224 225 226 216 217 218 219 220 221 222 223 
  |  runtime filters: RF000[min_max] <- video_id[#218](9656316/8388608/8388608), RF001[in_or_bloom] <- video_id[#218](9656316/8388608/8388608)
  |  final projections: watch_rollup_type[#289], watch_rollup_key[#290], viewing_user_client_key[#286], correlation_search_correlator_key[#287], video_id[#282], watch_is_autoplay[#283], video_is_placeholder[#284], video_paywall_type_id[#285], experiments_key[#288], window_start_at[#281], event_day[#280]
  |  final project output tuple id: 14
  |  distribute expr lists: fid[#278]
  |  distribute expr lists: video_id[#218]
  |  
  |----1:VEXCHANGE
  |       offset: 0
  |       distribute expr lists: 
  |    
  3:VEXCHANGE
     offset: 0
     distribute expr lists: fid[#278]
""
PLAN FRAGMENT 2
""
  PARTITION: HASH_PARTITIONED: fid[#227]
""
  HAS_COLO_PLAN_NODE: false
""
  STREAM DATA SINK
    EXCHANGE ID: 03
    HASH_PARTITIONED: fid[#278]
""
  2:VOlapScanNode(1916)
     TABLE: video_metadata(video_metadata), PREAGGREGATION: ON
     PREDICATES: ((fid[#227] > 0) AND (__DORIS_DELETE_SIGN__[#275] = 0))
     partitions=1/1 (video_metadata)
     tablets=32/32, tabletList=1776129913941,1776129913943,1776129913945 ...
     cardinality=68337843, avgRowSize=3587.096, numNodes=4
     pushAggOp=NONE
     runtime filters: RF000[min_max] -> fid[#227], RF001[in_or_bloom] -> fid[#227]
     final projections: fid[#227]
     final project output tuple id: 12
""
PLAN FRAGMENT 3
""
  PARTITION: RANDOM
""
  HAS_COLO_PLAN_NODE: false
""
  STREAM DATA SINK
    EXCHANGE ID: 01
    HASH_PARTITIONED: video_id[#218]
""
  0:VOlapScanNode(1929)
     TABLE: events(events), PREAGGREGATION: ON
     PREDICATES: ((((((date_trunc(event_at[#1], 'DAY') >= '2026-04-19 00:00:00') AND (TRY_CAST(event_value[#10] AS int) > 0)) AND ((TRY_CAST(event_value[#10] AS int) != 86400000) AND TRY_CAST(event_value[#10] AS int) IS NOT NULL)) AND (((CAST(event_value[#10] AS largeint) > 0) AND CAST(event_value[#10] AS largeint) IS NOT NULL) AND (coalesce(TRY_CAST(event_value[#10] AS int), 0) IS NOT NULL AND TRY_CAST((TRY_CAST(event_value[#10] AS largeint) + TRY_CAST(event_value[#10] AS largeint)) AS int) IS NOT NULL))) AND (((CAST(user_connect_info[#15] AS varchar(65533)) IS NOT NULL AND (event_type[#4] = 'watch_time')) AND (((event_sub_type[#5] = '') OR event_sub_type[#5] IS NULL) AND (event_version[#6] = '1'))) AND (((possible_bot_user_agent[#19] != TRUE) AND (possible_bot_cidr[#20] != TRUE)) AND (event_time_classification[#18] NOT IN ('FUTURE_UNREASONABLE', 'PAST_UNREASONABLE', 'PAST_LAGGING', 'PAST_SUSPECT') AND (cardinality(experiments[#12]) <= 8))))) AND (__DORIS_DELETE_SIGN__[#21] = 0))
     partitions=3/118 (p20260419000000,p20260418000000,p20260420000000)
     tablets=48/48, tabletList=1776290375409,1776290375411,1776290375413 ...
     cardinality=410907142, avgRowSize=524.95105, numNodes=4
     pushAggOp=NONE
     nested columns:
       experiments:
         origin type: array<text>
         all access paths: [experiments]
         predicate access paths: [experiments]
     final projections: date_trunc(event_at[#189], 'DAY'), minute_floor(event_at[#189], 5), CAST(event_value[#190] AS largeint), coalesce(cast(event_value as BOOLEAN), FALSE)[#199], coalesce(cast(event_value as BOOLEAN), FALSE)[#199], TRY_CAST(event_value[#190] AS int), ((CAST(conv(substring(md5(cast(user_connect_info as VARCHAR(65533)))[#200], 1, 16), 16, 10) AS largeint) * 18446744073709551616) + CAST(conv(substring(md5(cast(user_connect_info as VARCHAR(65533)))[#200], 17, 16), 16, 10) AS largeint)), ((CAST(conv(substring(md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36)), '-none-'))[#210], 1, 16), 16, 10) AS largeint) * 18446744073709551616) + CAST(conv(substring(md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36)), '-none-'))[#210], 17, 16), 16, 10) AS largeint)), ((CAST(conv(substring(md5(cast(array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])) as VARCHAR(65533)))[#215], 1, 16), 16, 10) AS largeint) * 18446744073709551616) + CAST(conv(substring(md5(cast(array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])) as VARCHAR(65533)))[#215], 17, 16), 16, 10) AS largeint)), if((coalesce(user_id, 0) > 0)[#203], 'U', 'A'), if((coalesce(user_id, 0) > 0)[#203], coalesce(user_id, 0)[#197], ((CAST(conv(substring(md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info as TEXT), 250), ''))[#209], 1, 16), 16, 10) AS largeint) * 18446744073709551616) + CAST(conv(substring(md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info as TEXT), 250), ''))[#209], 17, 16), 16, 10) AS largeint)))
     final project output tuple id: 10
     intermediate projections: event_at[#163], event_value[#164], user_connect_info[#165], user_id[#166], experiments[#167], cast(event_value as BOOLEAN)[#168], cast(user_connect_info as VARCHAR(65533))[#169], cast(event_value as TEXT)[#170], coalesce(user_id, 0)[#171], cast(user_connect_info as TEXT)[#172], coalesce(cast(event_value as BOOLEAN), FALSE)[#173], md5(cast(user_connect_info as VARCHAR(65533)))[#174], left(cast(event_value as TEXT), 36)[#175], cast(coalesce(user_id, 0) as VARCHAR(65533))[#176], (coalesce(user_id, 0) > 0)[#177], coalesce(cast(user_connect_info as VARCHAR(65533)), '')[#178], left(cast(user_connect_info as TEXT), 250)[#179], coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36))[#180], coalesce(left(cast(user_connect_info as TEXT), 250), '')[#181], coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36)), '-none-')[#182], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info as TEXT), 250), ''))[#183], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36)), '-none-'))[#184], array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments)[#185], coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])[#186], array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), []))[#187], cast(array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])) as VARCHAR(65533))[#188], md5(cast(array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])) as VARCHAR(65533))[#188])
     intermediate tuple id: 9
     intermediate projections: event_at[#138], event_value[#139], user_connect_info[#140], user_id[#141], experiments[#142], cast(event_value as BOOLEAN)[#143], cast(user_connect_info as VARCHAR(65533))[#144], cast(event_value as TEXT)[#145], coalesce(user_id, 0)[#146], cast(user_connect_info as TEXT)[#147], coalesce(cast(event_value as BOOLEAN), FALSE)[#148], md5(cast(user_connect_info as VARCHAR(65533)))[#149], left(cast(event_value as TEXT), 36)[#150], cast(coalesce(user_id, 0) as VARCHAR(65533))[#151], (coalesce(user_id, 0) > 0)[#152], coalesce(cast(user_connect_info as VARCHAR(65533)), '')[#153], left(cast(user_connect_info as TEXT), 250)[#154], coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36))[#155], coalesce(left(cast(user_connect_info as TEXT), 250), '')[#156], coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36)), '-none-')[#157], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info as TEXT), 250), ''))[#158], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36)), '-none-'))[#159], array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments)[#160], coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])[#161], array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), []))[#162], CAST(array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), []))[#162] AS varchar(65533))
     intermediate tuple id: 8
     intermediate projections: event_at[#114], event_value[#115], user_connect_info[#116], user_id[#117], experiments[#118], cast(event_value as BOOLEAN)[#119], cast(user_connect_info as VARCHAR(65533))[#120], cast(event_value as TEXT)[#121], coalesce(user_id, 0)[#122], cast(user_connect_info as TEXT)[#123], coalesce(cast(event_value as BOOLEAN), FALSE)[#124], md5(cast(user_connect_info as VARCHAR(65533)))[#125], left(cast(event_value as TEXT), 36)[#126], cast(coalesce(user_id, 0) as VARCHAR(65533))[#127], (coalesce(user_id, 0) > 0)[#128], coalesce(cast(user_connect_info as VARCHAR(65533)), '')[#129], left(cast(user_connect_info as TEXT), 250)[#130], coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36))[#131], coalesce(left(cast(user_connect_info as TEXT), 250), '')[#132], coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36)), '-none-')[#133], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info as TEXT), 250), ''))[#134], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36)), '-none-'))[#135], array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments)[#136], coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])[#137], array_sort(coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments), [])[#137])
     intermediate tuple id: 7
     intermediate projections: event_at[#91], event_value[#92], user_connect_info[#93], user_id[#94], experiments[#95], cast(event_value as BOOLEAN)[#96], cast(user_connect_info as VARCHAR(65533))[#97], cast(event_value as TEXT)[#98], coalesce(user_id, 0)[#99], cast(user_connect_info as TEXT)[#100], coalesce(cast(event_value as BOOLEAN), FALSE)[#101], md5(cast(user_connect_info as VARCHAR(65533)))[#102], left(cast(event_value as TEXT), 36)[#103], cast(coalesce(user_id, 0) as VARCHAR(65533))[#104], (coalesce(user_id, 0) > 0)[#105], coalesce(cast(user_connect_info as VARCHAR(65533)), '')[#106], left(cast(user_connect_info as TEXT), 250)[#107], coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36))[#108], coalesce(left(cast(user_connect_info as TEXT), 250), '')[#109], coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36)), '-none-')[#110], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info as TEXT), 250), ''))[#111], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36)), '-none-'))[#112], array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments)[#113], coalesce(array_map(x -> left(substring(cast(x as VARCHAR(40)), 1, 40), 40), experiments)[#113], [])
     intermediate tuple id: 6
     intermediate projections: event_at[#70], event_value[#71], user_connect_info[#72], user_id[#73], experiments[#74], cast(event_value as BOOLEAN)[#75], cast(user_connect_info as VARCHAR(65533))[#76], cast(event_value as TEXT)[#77], coalesce(user_id, 0)[#78], cast(user_connect_info as TEXT)[#79], coalesce(cast(event_value as BOOLEAN), FALSE)[#80], md5(cast(user_connect_info as VARCHAR(65533)))[#81], left(cast(event_value as TEXT), 36)[#82], cast(coalesce(user_id, 0) as VARCHAR(65533))[#83], (coalesce(user_id, 0) > 0)[#84], coalesce(cast(user_connect_info as VARCHAR(65533)), '')[#85], left(cast(user_connect_info as TEXT), 250)[#86], coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36))[#87], coalesce(left(cast(user_connect_info as TEXT), 250), '')[#88], coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36)), '-none-')[#89], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533)), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(cast(user_connect_info as VARCHAR(65533)), ''), coalesce(left(cast(user_connect_info as TEXT), 250), ''))[#90], md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533))[#83], coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36)), '-none-')[#89]), array_map(x -> left(substring(CAST(x AS varchar(40)), 1, 40), 40), experiments[#74])
     intermediate tuple id: 5
     intermediate projections: event_at[#51], event_value[#52], user_connect_info[#53], user_id[#54], experiments[#55], cast(event_value as BOOLEAN)[#56], cast(user_connect_info as VARCHAR(65533))[#57], cast(event_value as TEXT)[#58], coalesce(user_id, 0)[#59], cast(user_connect_info as TEXT)[#60], coalesce(cast(event_value as BOOLEAN), FALSE)[#61], md5(cast(user_connect_info as VARCHAR(65533)))[#62], left(cast(event_value as TEXT), 36)[#63], cast(coalesce(user_id, 0) as VARCHAR(65533))[#64], (coalesce(user_id, 0) > 0)[#65], coalesce(cast(user_connect_info as VARCHAR(65533)), '')[#66], left(cast(user_connect_info as TEXT), 250)[#67], coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36))[#68], coalesce(left(cast(user_connect_info as TEXT), 250), '')[#69], coalesce(coalesce(left(cast(event_value as TEXT), 36), left(cast(event_value as TEXT), 36))[#68], '-none-'), md5sum(cast(coalesce(user_id, 0) as VARCHAR(65533))[#64], coalesce(cast(user_connect_info as VARCHAR(65533)), '')[#66], coalesce(cast(user_connect_info as VARCHAR(65533)), '')[#66], coalesce(cast(user_connect_info as VARCHAR(65533)), '')[#66], coalesce(left(cast(user_connect_info as TEXT), 250), '')[#69])
     intermediate tuple id: 4
     intermediate projections: event_at[#34], event_value[#35], user_connect_info[#36], user_id[#37], experiments[#38], cast(event_value as BOOLEAN)[#39], cast(user_connect_info as VARCHAR(65533))[#40], cast(event_value as TEXT)[#41], coalesce(user_id, 0)[#42], cast(user_connect_info as TEXT)[#43], coalesce(cast(event_value as BOOLEAN), FALSE)[#44], md5(cast(user_connect_info as VARCHAR(65533)))[#45], left(cast(event_value as TEXT), 36)[#46], cast(coalesce(user_id, 0) as VARCHAR(65533))[#47], (coalesce(user_id, 0) > 0)[#48], coalesce(cast(user_connect_info as VARCHAR(65533)), '')[#49], left(cast(user_connect_info as TEXT), 250)[#50], coalesce(left(cast(event_value as TEXT), 36)[#46], left(cast(event_value as TEXT), 36)[#46]), coalesce(left(cast(user_connect_info as TEXT), 250)[#50], '')
     intermediate tuple id: 3
     intermediate projections: event_at[#24], event_value[#25], user_connect_info[#26], user_id[#27], experiments[#28], cast(event_value as BOOLEAN)[#29], cast(user_connect_info as VARCHAR(65533))[#30], cast(event_value as TEXT)[#31], coalesce(user_id, 0)[#32], cast(user_connect_info as TEXT)[#33], coalesce(cast(event_value as BOOLEAN)[#29], FALSE), md5(cast(user_connect_info as VARCHAR(65533))[#30]), left(cast(event_value as TEXT)[#31], 36), CAST(coalesce(user_id, 0)[#32] AS varchar(65533)), (coalesce(user_id, 0)[#32] > 0), coalesce(cast(user_connect_info as VARCHAR(65533))[#30], ''), left(cast(user_connect_info as TEXT)[#33], 250)
     intermediate tuple id: 2
     intermediate projections: event_at[#1], event_value[#10], user_connect_info[#15], user_id[#7], experiments[#12], CAST(event_value[#10] AS boolean), CAST(user_connect_info[#15] AS varchar(65533)), CAST(event_value[#10] AS text), coalesce(user_id[#7], 0), CAST(user_connect_info[#15] AS text)
     intermediate tuple id: 1
""
""
========== MATERIALIZATIONS ==========
""
MaterializedView
MaterializedViewRewriteSuccessAndChose:
""
MaterializedViewRewriteSuccessButNotChose:
""
MaterializedViewRewriteFail:
 CBO.internal.events_watch_time_mv fail
  FailInfo: View struct info is invalid, View dimensions doesn't not cover the query dimensions
""
""
========== STATISTICS ==========
planned with unknown column statistics

[apatrida]

Full reproduction case given to "Darren" @ velodb

[ixzc]

we are looking into this

[morrySnow]

could you test in the following way:

• remove /*+SET_VAR(enable_local_shuffle=true)*/ in view definition.
• set enable_local_shuffle=false and then run the query on view

is the result still wrong?

[apatrida]

@morrySnow yes, the error remains. THe small reproducible case sent to Darren shows it fails after this change as well.