You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by "apatrida (via GitHub)" <gi...@apache.org> on 2026/04/22 17:38:23 UTC
[I] [Bug] Complex queries when placed into views fail to return correct results, sometimes no results [doris]
apatrida opened a new issue, #62726:
URL: https://github.com/apache/doris/issues/62726
### Search before asking
- [x] I had searched in the [issues](https://github.com/apache/doris/issues?q=is%3Aissue) and found no similar issues.
### Version
4.0.5 and 4.1
### What's Wrong?
Similar to closed issue [#47404](https://github.com/apache/doris/issues/47405)
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], m
ax(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_st
art[#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(lef
t(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_correl
ator['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(coa
lesce(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_correl
ator['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(coa
lesce(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_correl
ator['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(coa
lesce(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_correl
ator['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], coales
ce(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_correl
ator['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_correl
ator['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(6
5533)), 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
- [x] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Complex queries when placed into views fail to return correct results, sometimes no results [doris]
Posted by "morrySnow (via GitHub)" <gi...@apache.org>.
morrySnow commented on issue #62726:
URL: https://github.com/apache/doris/issues/62726#issuecomment-4302755251
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?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Complex queries when placed into views fail to return correct results, sometimes no results [doris]
Posted by "apatrida (via GitHub)" <gi...@apache.org>.
apatrida commented on issue #62726:
URL: https://github.com/apache/doris/issues/62726#issuecomment-4310893801
@morrySnow yes, the error remains. THe small reproducible case sent to Darren shows it fails after this change as well.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Complex queries when placed into views fail to return correct results, sometimes no results [doris]
Posted by "apatrida (via GitHub)" <gi...@apache.org>.
apatrida commented on issue #62726:
URL: https://github.com/apache/doris/issues/62726#issuecomment-4298642482
And the plan for the same wrapped into a `foo`
```
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)), ''), coale
sce(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_i
nfo 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 VARC
HAR(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
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Complex queries when placed into views fail to return correct results, sometimes no results [doris]
Posted by "apatrida (via GitHub)" <gi...@apache.org>.
apatrida commented on issue #62726:
URL: https://github.com/apache/doris/issues/62726#issuecomment-4299343308
Full reproduction case given to "Darren" @ velodb
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Complex queries when placed into views fail to return correct results, sometimes no results [doris]
Posted by "ixzc (via GitHub)" <gi...@apache.org>.
ixzc commented on issue #62726:
URL: https://github.com/apache/doris/issues/62726#issuecomment-4299931330
we are looking into this
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
Re: [I] [Bug] Complex queries when placed into views fail to return correct results, sometimes no results [doris]
Posted by "ixzc (via GitHub)" <gi...@apache.org>.
ixzc commented on issue #62726:
URL: https://github.com/apache/doris/issues/62726#issuecomment-4482024246
fixed this
https://github.com/apache/doris/pull/62907
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org