SQL Structural Parity — Legend SQL (LegendSql)

Summary

Percentages exclude UNSUPPORTED and SKIP from the denominator.

Error Categories

Category Summary

joins

set_operations

subqueries

ctes

order_limit_offset

group_by

distinct

null_semantics

type_casting

case_expressions

where_predicates

aliases

having

lateral_joins

boolean_logic

select_star

multiple_schemas

json_operators

interval_arithmetic

column_resolution

column_resolution_corpus

grouping_sets

filter_clause

within_group

tablesample

fetch_with_ties

recursive_ctes

values_clause

window_frames

window_partitioning

compositions

Error Details

ALIAS_NOT_FOUND (7 tests)

union_multiple_mixed

📘 TDS Path

Input SQL:

SELECT 'dept1' AS src, name FROM persons WHERE dept_id = 1 UNION ALL SELECT 'dept2', name FROM persons WHERE dept_id = 2 ORDER BY 1, 2

Legend SQL:

SELECT 'dept1' AS src, name FROM func('e2e::tds_persons') WHERE dept_id = 1 UNION ALL SELECT 'dept2', name FROM func('e2e::tds_persons') WHERE dept_id = 2 ORDER BY 1, 2

Error:

Column 'dept2' (case sensitive? true) not found among: [src, name]

union_with_agg

📘 TDS Path

Input SQL:

SELECT dept_id, COUNT(*) AS cnt FROM persons WHERE dept_id IS NOT NULL GROUP BY dept_id UNION ALL SELECT NULL, COUNT(*) FROM persons ORDER BY 1

Legend SQL:

SELECT dept_id, COUNT(*) AS cnt FROM func('e2e::tds_persons') WHERE dept_id IS NOT NULL GROUP BY dept_id UNION ALL SELECT NULL, COUNT(*) FROM func('e2e::tds_persons') ORDER BY 1

Error:

Column 'NULL' (case sensitive? true) not found among: [dept_id, cnt]

union_with_agg_sum

📘 TDS Path

Input SQL:

SELECT 'by_dept' AS label, dept_id, SUM(salary) AS total FROM persons WHERE dept_id IS NOT NULL AND salary IS NOT NULL GROUP BY dept_id UNION ALL SELECT 'overall', NULL, SUM(salary) FROM persons WHERE salary IS NOT NULL ORDER BY 1, 2

Legend SQL:

SELECT 'by_dept' AS label, dept_id, SUM(salary) AS total FROM func('e2e::tds_persons') WHERE dept_id IS NOT NULL AND salary IS NOT NULL GROUP BY dept_id UNION ALL SELECT 'overall', NULL, SUM(salary) FROM func('e2e::tds_persons') WHERE salary IS NOT NULL ORDER BY 1, 2

Error:

Column 'overall' (case sensitive? true) not found among: [label, dept_id, total]

union_all_expression

📘 TDS Path

Input SQL:

SELECT name, salary * 1.1 AS adjusted FROM persons WHERE dept_id = 1 AND salary IS NOT NULL UNION ALL SELECT name, salary * 0.9 FROM persons WHERE dept_id = 2 AND salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary * 1.1 AS adjusted FROM func('e2e::tds_persons') WHERE dept_id = 1 AND salary IS NOT NULL UNION ALL SELECT name, salary * 0.9 FROM func('e2e::tds_persons') WHERE dept_id = 2 AND salary IS NOT NULL ORDER BY 1

Error:

Column 'salary * 0.9' (case sensitive? true) not found among: [name, adjusted]

union_with_case

📘 TDS Path

Input SQL:

SELECT 'active' AS status, name FROM persons WHERE active = true UNION ALL SELECT 'inactive', name FROM persons WHERE active = false ORDER BY 1, 2

Legend SQL:

SELECT 'active' AS status, name FROM func('e2e::tds_persons') WHERE active = true UNION ALL SELECT 'inactive', name FROM func('e2e::tds_persons') WHERE active = false ORDER BY 1, 2

Error:

Column 'inactive' (case sensitive? true) not found among: [status, name]

colres_gap_order_by_expression_in_window_query

📘 TDS Path

Input SQL:

SELECT name, dept_id, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY name) AS rn FROM persons WHERE dept_id IS NOT NULL ORDER BY UPPER(name)

Legend SQL:

SELECT name, dept_id, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY name) AS rn FROM func('e2e::tds_persons') WHERE dept_id IS NOT NULL ORDER BY UPPER(name)

Error:

The column 'UPPER(name)' can't be found ("id","name","age","salary","hire_date","active","dept_id","rn")

📗 Relation Path

Input SQL:

SELECT name, dept_id, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY name) AS rn FROM persons WHERE dept_id IS NOT NULL ORDER BY UPPER(name)

Legend SQL:

SELECT name, dept_id, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY name) AS rn FROM func('e2e::rel_persons') WHERE dept_id IS NOT NULL ORDER BY UPPER(name)

Error:

The column 'UPPER(name)' can't be found in the relation (id:Integer[1], name:String, age:Integer, salary:Float, hire_date:StrictDate, active:Boolean, dept_id:Integer, rn:Integer[1])

crcs_g4_order_by_column_projected_only_through_a_cast

📘 TDS Path

Input SQL:

SELECT CAST(p.id AS VARCHAR(10)) AS id_text, p.name AS name FROM persons p INNER JOIN departments d ON p.dept_id = d.id ORDER BY p.id

Legend SQL:

SELECT CAST(p.id AS VARCHAR(10)) AS id_text, p.name AS name FROM func('e2e::tds_persons') p INNER JOIN func('e2e::tds_departments') d ON p.dept_id = d.id ORDER BY p.id

Error:

The column 'id_p' can't be found ("id_text","name")

📗 Relation Path

Input SQL:

SELECT CAST(p.id AS VARCHAR(10)) AS id_text, p.name AS name FROM persons p INNER JOIN departments d ON p.dept_id = d.id ORDER BY p.id

Legend SQL:

SELECT CAST(p.id AS VARCHAR(10)) AS id_text, p.name AS name FROM func('e2e::rel_persons') p INNER JOIN func('e2e::rel_departments') d ON p.dept_id = d.id ORDER BY p.id

Error:

The column 'id_p' can't be found in the relation (id_text:String[1], name:String)

MISC (82 tests)

union_multiple_mixed

📗 Relation Path

Input SQL:

SELECT 'dept1' AS src, name FROM persons WHERE dept_id = 1 UNION ALL SELECT 'dept2', name FROM persons WHERE dept_id = 2 ORDER BY 1, 2

Legend SQL:

SELECT 'dept1' AS src, name FROM func('e2e::rel_persons') WHERE dept_id = 1 UNION ALL SELECT 'dept2', name FROM func('e2e::rel_persons') WHERE dept_id = 2 ORDER BY 1, 2

Error:

The two relations are incompatible and can't be concatenated (src:String[1], name:String) and (dept2:String[1], name:String)

union_with_agg

📗 Relation Path

Input SQL:

SELECT dept_id, COUNT(*) AS cnt FROM persons WHERE dept_id IS NOT NULL GROUP BY dept_id UNION ALL SELECT NULL, COUNT(*) FROM persons ORDER BY 1

Legend SQL:

SELECT dept_id, COUNT(*) AS cnt FROM func('e2e::rel_persons') WHERE dept_id IS NOT NULL GROUP BY dept_id UNION ALL SELECT NULL, COUNT(*) FROM func('e2e::rel_persons') ORDER BY 1

Error:

The two relations are incompatible and can't be concatenated (dept_id:Integer, cnt:Integer[1]) and (NULL:String[0], COUNT():Integer[1])

union_with_agg_sum

📗 Relation Path

Input SQL:

SELECT 'by_dept' AS label, dept_id, SUM(salary) AS total FROM persons WHERE dept_id IS NOT NULL AND salary IS NOT NULL GROUP BY dept_id UNION ALL SELECT 'overall', NULL, SUM(salary) FROM persons WHERE salary IS NOT NULL ORDER BY 1, 2

Legend SQL:

SELECT 'by_dept' AS label, dept_id, SUM(salary) AS total FROM func('e2e::rel_persons') WHERE dept_id IS NOT NULL AND salary IS NOT NULL GROUP BY dept_id UNION ALL SELECT 'overall', NULL, SUM(salary) FROM func('e2e::rel_persons') WHERE salary IS NOT NULL ORDER BY 1, 2

Error:

The two relations are incompatible and can't be concatenated (label:String[1], dept_id:Integer, total:Float[1]) and (overall:String[1], NULL:String[0], SUM(salary):Float[1])

union_all_expression

📗 Relation Path

Input SQL:

SELECT name, salary * 1.1 AS adjusted FROM persons WHERE dept_id = 1 AND salary IS NOT NULL UNION ALL SELECT name, salary * 0.9 FROM persons WHERE dept_id = 2 AND salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary * 1.1 AS adjusted FROM func('e2e::rel_persons') WHERE dept_id = 1 AND salary IS NOT NULL UNION ALL SELECT name, salary * 0.9 FROM func('e2e::rel_persons') WHERE dept_id = 2 AND salary IS NOT NULL ORDER BY 1

Error:

The two relations are incompatible and can't be concatenated (name:String, adjusted:Float) and (name:String, salary * 0.9:Float)

union_with_case

📗 Relation Path

Input SQL:

SELECT 'active' AS status, name FROM persons WHERE active = true UNION ALL SELECT 'inactive', name FROM persons WHERE active = false ORDER BY 1, 2

Legend SQL:

SELECT 'active' AS status, name FROM func('e2e::rel_persons') WHERE active = true UNION ALL SELECT 'inactive', name FROM func('e2e::rel_persons') WHERE active = false ORDER BY 1, 2

Error:

The two relations are incompatible and can't be concatenated (status:String[1], name:String) and (inactive:String[1], name:String)

subquery_correlated

📗 Relation Path

Input SQL:

SELECT p.name, p.salary FROM persons p WHERE p.salary > (SELECT AVG(salary) FROM persons WHERE dept_id = p.dept_id AND salary IS NOT NULL) ORDER BY 1

Legend SQL:

SELECT p.name, p.salary FROM func('e2e::rel_persons') p WHERE p.salary > (SELECT AVG(salary) FROM func('e2e::rel_persons') WHERE dept_id = p.dept_id AND salary IS NOT NULL) ORDER BY 1

Error:

Column 'dept_id' of the enclosing query cannot be referenced from this subquery: both use the table alias 'root'

subquery_in_having

📗 Relation Path

Input SQL:

SELECT dept_id, COUNT(*) AS cnt FROM persons WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING COUNT(*) > (SELECT AVG(cnt) FROM (SELECT COUNT(*) AS cnt FROM persons WHERE dept_id IS NOT NULL GROUP BY dept_id) sub) ORDER BY 1

Legend SQL:

SELECT dept_id, COUNT(*) AS cnt FROM func('e2e::rel_persons') WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING COUNT(*) > (SELECT AVG(cnt) FROM (SELECT COUNT(*) AS cnt FROM func('e2e::rel_persons') WHERE dept_id IS NOT NULL GROUP BY dept_id) sub) ORDER BY 1

Error:

restrict__d#4_e1_d#2 not found

subquery_exists_grouped

📗 Relation Path

Input SQL:

SELECT p.name FROM persons p WHERE EXISTS (SELECT o.person_id, COUNT(*) AS cnt FROM orders o WHERE o.person_id = p.id GROUP BY o.person_id) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::rel_persons') p WHERE EXISTS (SELECT o.person_id, COUNT(*) AS cnt FROM func('e2e::rel_orders') o WHERE o.person_id = p.id GROUP BY o.person_id) ORDER BY 1

Error:

Column 'id' of the enclosing query cannot be referenced from this subquery: both use the table alias 'root'

subquery_in_correlated

📗 Relation Path

Input SQL:

SELECT p.name FROM persons p WHERE p.id IN (SELECT o.person_id FROM orders o WHERE o.amount > p.salary / 1000) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::rel_persons') p WHERE p.id IN (SELECT o.person_id FROM func('e2e::rel_orders') o WHERE o.amount > p.salary / 1000) ORDER BY 1

Error:

Column 'salary' of the enclosing query cannot be referenced from this subquery: both use the table alias 'root'

subquery_quant_all_correlated_empty

📗 Relation Path

Input SQL:

SELECT p.name FROM persons p WHERE p.id < ALL (SELECT o.id FROM orders o WHERE o.person_id = p.id) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::rel_persons') p WHERE p.id < ALL(SELECT o.id FROM func('e2e::rel_orders') o WHERE o.person_id = p.id) ORDER BY 1

Error:

Column 'id' of the enclosing query cannot be referenced from this subquery: both use the table alias 'root'

cte_basic

📘 TDS Path

Input SQL:

WITH active_persons AS (SELECT name, salary FROM persons WHERE active = true) SELECT name, salary FROM active_persons ORDER BY 1

Legend SQL:

WITH active_persons AS (SELECT name, salary FROM func('e2e::tds_persons') WHERE active = true) SELECT name, salary FROM active_persons ORDER BY 1

Error:

Unhandled value type: meta::pure::tds::SortInformation

cte_basic_filter

📘 TDS Path

Input SQL:

WITH high_earners AS (SELECT name, salary FROM persons WHERE salary > 70000) SELECT name, salary FROM high_earners ORDER BY 1

Legend SQL:

WITH high_earners AS (SELECT name, salary FROM func('e2e::tds_persons') WHERE salary > 70000) SELECT name, salary FROM high_earners ORDER BY 1

Error:

Unhandled value type: meta::pure::tds::SortInformation

cte_multiple

📘 TDS Path

Input SQL:

WITH dept_stats AS (SELECT dept_id, AVG(salary) AS avg_sal FROM persons WHERE salary IS NOT NULL GROUP BY dept_id), high_earners AS (SELECT name, salary, dept_id FROM persons WHERE salary > 70000) SELECT h.name, h.salary, d.avg_sal FROM high_earners h INNER JOIN dept_stats d ON h.dept_id = d.dept_id ORDER BY 1

Legend SQL:

WITH dept_stats AS (SELECT dept_id, AVG(salary) AS avg_sal FROM func('e2e::tds_persons') WHERE salary IS NOT NULL GROUP BY dept_id), high_earners AS (SELECT name, salary, dept_id FROM func('e2e::tds_persons') WHERE salary > 70000) SELECT h.name, h.salary, d.avg_sal FROM high_earners h INNER JOIN dept_stats d ON h.dept_id = d.dept_id ORDER BY 1

Error:

Function with parameters not yet handled

cte_with_agg

📘 TDS Path

Input SQL:

WITH dept_summary AS (SELECT dept_id, COUNT(*) AS cnt, SUM(salary) AS total FROM persons WHERE dept_id IS NOT NULL AND salary IS NOT NULL GROUP BY dept_id) SELECT dept_id, cnt, total FROM dept_summary ORDER BY 1

Legend SQL:

WITH dept_summary AS (SELECT dept_id, COUNT(*) AS cnt, SUM(salary) AS total FROM func('e2e::tds_persons') WHERE dept_id IS NOT NULL AND salary IS NOT NULL GROUP BY dept_id) SELECT dept_id, cnt, total FROM dept_summary ORDER BY 1

Error:

Unhandled value type: meta::pure::tds::SortInformation

cte_with_agg_having

📘 TDS Path

Input SQL:

WITH dept_counts AS (SELECT dept_id, COUNT(*) AS cnt FROM persons WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING COUNT(*) > 2) SELECT dept_id, cnt FROM dept_counts ORDER BY 1

Legend SQL:

WITH dept_counts AS (SELECT dept_id, COUNT(*) AS cnt FROM func('e2e::tds_persons') WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING COUNT(*) > 2) SELECT dept_id, cnt FROM dept_counts ORDER BY 1

Error:

Unhandled value type: meta::pure::tds::SortInformation

cte_with_window

📘 TDS Path

Input SQL:

WITH ranked AS (SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk FROM persons WHERE salary IS NOT NULL) SELECT name, salary, rnk FROM ranked WHERE rnk <= 3 ORDER BY 1

Legend SQL:

WITH ranked AS (SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk FROM func('e2e::tds_persons') WHERE salary IS NOT NULL) SELECT name, salary, rnk FROM ranked WHERE rnk <= 3 ORDER BY 1

Error:

Function with parameters not yet handled

cte_with_union

📘 TDS Path

Input SQL:

WITH high AS (SELECT name, salary FROM persons WHERE salary > 70000), low AS (SELECT name, salary FROM persons WHERE salary <= 70000 AND salary IS NOT NULL) SELECT name, salary, 'high' AS band FROM high UNION ALL SELECT name, salary, 'low' FROM low ORDER BY 1

Legend SQL:

WITH high AS (SELECT name, salary FROM func('e2e::tds_persons') WHERE salary > 70000), low AS (SELECT name, salary FROM func('e2e::tds_persons') WHERE salary <= 70000 AND salary IS NOT NULL) SELECT name, salary, 'high' AS band FROM high UNION ALL SELECT name, salary, 'low' FROM low ORDER BY 1

Error:

Unhandled value type: meta::pure::tds::BasicColumnSpecification

📗 Relation Path

Input SQL:

WITH high AS (SELECT name, salary FROM persons WHERE salary > 70000), low AS (SELECT name, salary FROM persons WHERE salary <= 70000 AND salary IS NOT NULL) SELECT name, salary, 'high' AS band FROM high UNION ALL SELECT name, salary, 'low' FROM low ORDER BY 1

Legend SQL:

WITH high AS (SELECT name, salary FROM func('e2e::rel_persons') WHERE salary > 70000), low AS (SELECT name, salary FROM func('e2e::rel_persons') WHERE salary <= 70000 AND salary IS NOT NULL) SELECT name, salary, 'high' AS band FROM high UNION ALL SELECT name, salary, 'low' FROM low ORDER BY 1

Error:

The two relations are incompatible and can't be concatenated (name:String, salary:Float, band:String[1]) and (name:String, salary:Float, low:String[1])

cte_filter_agg

📘 TDS Path

Input SQL:

WITH dept_stats AS (SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal FROM persons WHERE dept_id IS NOT NULL AND salary IS NOT NULL GROUP BY dept_id) SELECT dept_id, cnt, avg_sal FROM dept_stats WHERE cnt > 2 ORDER BY 1

Legend SQL:

WITH dept_stats AS (SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal FROM func('e2e::tds_persons') WHERE dept_id IS NOT NULL AND salary IS NOT NULL GROUP BY dept_id) SELECT dept_id, cnt, avg_sal FROM dept_stats WHERE cnt > 2 ORDER BY 1

Error:

Function with parameters not yet handled

alias_subquery_column_list

📘 TDS Path

Input SQL:

SELECT s.n, s.s FROM (SELECT name, salary FROM persons) s(n, s) WHERE s.s IS NOT NULL ORDER BY 1

Legend SQL:

SELECT s.n, s.s FROM (SELECT name, salary FROM func('e2e::tds_persons')) s(n, s) WHERE s.s IS NOT NULL ORDER BY 1

Error:

Execution error at (resource:/core_external_query_sql/binding/fromPure/fromPure.pure line:3961 column:28), "Cannot cast a collection of size 0 to multiplicity [1]"

📗 Relation Path

Input SQL:

SELECT s.n, s.s FROM (SELECT name, salary FROM persons) s(n, s) WHERE s.s IS NOT NULL ORDER BY 1

Legend SQL:

SELECT s.n, s.s FROM (SELECT name, salary FROM func('e2e::rel_persons')) s(n, s) WHERE s.s IS NOT NULL ORDER BY 1

Error:

Column type is empty for col: n

lateral_cross_subquery

🔴 Failed in both TDS and Relation

Input SQL:

SELECT p.name, sub.colleague FROM persons p CROSS JOIN LATERAL (SELECT p2.name AS colleague FROM persons p2 WHERE p2.dept_id = p.dept_id AND p2.id != p.id) sub WHERE p.id <= 3 ORDER BY 1, 2

Legend SQL (TDS):

SELECT p.name, sub.colleague FROM func('e2e::tds_persons') p CROSS JOIN LATERAL (SELECT p2.name AS colleague FROM func('e2e::tds_persons') p2 WHERE p2.dept_id = p.dept_id AND p2.id != p.id) sub WHERE p.id <= 3 ORDER BY 1, 2

Legend SQL (Relation):

SELECT p.name, sub.colleague FROM func('e2e::rel_persons') p CROSS JOIN LATERAL (SELECT p2.name AS colleague FROM func('e2e::rel_persons') p2 WHERE p2.dept_id = p.dept_id AND p2.id != p.id) sub WHERE p.id <= 3 ORDER BY 1, 2

Error:

no named relation found for 'p', ensure you have aliased the correct table/subquery

lateral_left_join

🔴 Failed in both TDS and Relation

Input SQL:

SELECT p.name, sub.top_sal FROM persons p LEFT JOIN LATERAL (SELECT MAX(salary) AS top_sal FROM persons p2 WHERE p2.dept_id = p.dept_id) sub ON TRUE WHERE p.id <= 5 ORDER BY 1

Legend SQL (TDS):

SELECT p.name, sub.top_sal FROM func('e2e::tds_persons') p LEFT JOIN LATERAL (SELECT MAX(salary) AS top_sal FROM func('e2e::tds_persons') p2 WHERE p2.dept_id = p.dept_id) sub ON TRUE WHERE p.id <= 5 ORDER BY 1

Legend SQL (Relation):

SELECT p.name, sub.top_sal FROM func('e2e::rel_persons') p LEFT JOIN LATERAL (SELECT MAX(salary) AS top_sal FROM func('e2e::rel_persons') p2 WHERE p2.dept_id = p.dept_id) sub ON TRUE WHERE p.id <= 5 ORDER BY 1

Error:

no named relation found for 'p', ensure you have aliased the correct table/subquery

lateral_left_join_no_match

🔴 Failed in both TDS and Relation

Input SQL:

SELECT p.name, sub.colleague FROM persons p LEFT JOIN LATERAL (SELECT p2.name AS colleague FROM persons p2 WHERE p2.dept_id = p.dept_id AND p2.id != p.id AND p2.salary > 999999) sub ON TRUE WHERE p.id <= 3 ORDER BY 1, 2

Legend SQL (TDS):

SELECT p.name, sub.colleague FROM func('e2e::tds_persons') p LEFT JOIN LATERAL (SELECT p2.name AS colleague FROM func('e2e::tds_persons') p2 WHERE p2.dept_id = p.dept_id AND p2.id != p.id AND p2.salary > 999999) sub ON TRUE WHERE p.id <= 3 ORDER BY 1, 2

Legend SQL (Relation):

SELECT p.name, sub.colleague FROM func('e2e::rel_persons') p LEFT JOIN LATERAL (SELECT p2.name AS colleague FROM func('e2e::rel_persons') p2 WHERE p2.dept_id = p.dept_id AND p2.id != p.id AND p2.salary > 999999) sub ON TRUE WHERE p.id <= 3 ORDER BY 1, 2

Error:

no named relation found for 'p', ensure you have aliased the correct table/subquery

lateral_top_n

🔴 Failed in both TDS and Relation

Input SQL:

SELECT d.name AS dept, sub.person_name FROM departments d CROSS JOIN LATERAL (SELECT p.name AS person_name FROM persons p WHERE p.dept_id = d.id ORDER BY p.salary DESC LIMIT 2) sub ORDER BY 1, 2

Legend SQL (TDS):

SELECT d.name AS dept, sub.person_name FROM func('e2e::tds_departments') d CROSS JOIN LATERAL (SELECT p.name AS person_name FROM func('e2e::tds_persons') p WHERE p.dept_id = d.id ORDER BY p.salary DESC LIMIT 2) sub ORDER BY 1, 2

Legend SQL (Relation):

SELECT d.name AS dept, sub.person_name FROM func('e2e::rel_departments') d CROSS JOIN LATERAL (SELECT p.name AS person_name FROM func('e2e::rel_persons') p WHERE p.dept_id = d.id ORDER BY p.salary DESC LIMIT 2) sub ORDER BY 1, 2

Error:

no named relation found for 'd', ensure you have aliased the correct table/subquery

lateral_aggregate

🔴 Failed in both TDS and Relation

Input SQL:

SELECT p.name, sub.dept_count FROM persons p CROSS JOIN LATERAL (SELECT COUNT(*) AS dept_count FROM persons p2 WHERE p2.dept_id = p.dept_id) sub WHERE p.id <= 5 ORDER BY 1

Legend SQL (TDS):

SELECT p.name, sub.dept_count FROM func('e2e::tds_persons') p CROSS JOIN LATERAL (SELECT COUNT(*) AS dept_count FROM func('e2e::tds_persons') p2 WHERE p2.dept_id = p.dept_id) sub WHERE p.id <= 5 ORDER BY 1

Legend SQL (Relation):

SELECT p.name, sub.dept_count FROM func('e2e::rel_persons') p CROSS JOIN LATERAL (SELECT COUNT(*) AS dept_count FROM func('e2e::rel_persons') p2 WHERE p2.dept_id = p.dept_id) sub WHERE p.id <= 5 ORDER BY 1

Error:

no named relation found for 'p', ensure you have aliased the correct table/subquery

lateral_unnest_with_ordinality

🔴 Failed in both TDS and Relation

Input SQL:

SELECT p.name, u.val, u.ord FROM persons p CROSS JOIN LATERAL UNNEST(ARRAY[10, 20, 30]) WITH ORDINALITY AS u(val, ord) WHERE p.id = 1 ORDER BY 2

Legend SQL (TDS):

SELECT p.name, u.val, u.ord FROM func('e2e::tds_persons') p CROSS JOIN LATERAL UNNEST(ARRAY[10, 20, 30]) WITH ORDINALITY u(val, ord) WHERE p.id = 1 ORDER BY 2

Legend SQL (Relation):

SELECT p.name, u.val, u.ord FROM func('e2e::rel_persons') p CROSS JOIN LATERAL UNNEST(ARRAY[10, 20, 30]) WITH ORDINALITY u(val, ord) WHERE p.id = 1 ORDER BY 2

Error:

no named relation found for 'u', ensure you have aliased the correct table/subquery

lateral_unnest_aggregate_sum

📗 Relation Path

Input SQL:

SELECT p.name, SUM(u.val) AS total FROM persons p CROSS JOIN LATERAL UNNEST(ARRAY[p.dept_id, p.dept_id * 2]) AS u(val) WHERE p.id <= 3 AND p.dept_id IS NOT NULL GROUP BY p.name ORDER BY 1

Legend SQL:

SELECT p.name, SUM(u.val) AS total FROM func('e2e::rel_persons') p CROSS JOIN LATERAL UNNEST(ARRAY[p.dept_id, p.dept_id * 2]) AS u(val) WHERE p.id <= 3 AND p.dept_id IS NOT NULL GROUP BY p.name ORDER BY 1

Error:

Collection element must have a multiplicity [1] - Context:[build Lambda, new lambda, Applying meta::pure::functions::relation::sort, Applying meta::pure::functions::relation::groupBy, Applying meta::pure::functions::relation::filter, Applying meta::pure::functions::relation::lateral, new lambda, Applying meta::pure::functions::relation::variant::flatten], multiplicity:[0..1]

lateral_unnest_multiple_arrays

📗 Relation Path

Input SQL:

SELECT p.name, u1.val AS val1, u2.val AS val2 FROM persons p CROSS JOIN LATERAL UNNEST(ARRAY[1, 2]) AS u1(val) CROSS JOIN LATERAL UNNEST(ARRAY['A', 'B']) AS u2(val) WHERE p.id = 1 ORDER BY 2, 3

Legend SQL:

SELECT p.name, u1.val AS val1, u2.val AS val2 FROM func('e2e::rel_persons') p CROSS JOIN LATERAL UNNEST(ARRAY[1, 2]) AS u1(val) CROSS JOIN LATERAL UNNEST(ARRAY['A', 'B']) AS u2(val) WHERE p.id = 1 ORDER BY 2, 3

Error:

no alias found with name: u1.val alias: val1

lateral_unnest_top_n_per_array_element

🔴 Failed in both TDS and Relation

Input SQL:

SELECT u.val, p.name FROM UNNEST(ARRAY[1, 2, 3]) AS u(val) CROSS JOIN LATERAL (SELECT name FROM persons WHERE dept_id = u.val ORDER BY salary DESC LIMIT 1) p ORDER BY 1

Legend SQL (TDS):

SELECT u.val, p.name FROM UNNEST(ARRAY[1, 2, 3]) AS u(val) CROSS JOIN LATERAL (SELECT name FROM func('e2e::tds_persons') WHERE dept_id = u.val ORDER BY salary DESC LIMIT 1) p ORDER BY 1

Legend SQL (Relation):

SELECT u.val, p.name FROM UNNEST(ARRAY[1, 2, 3]) AS u(val) CROSS JOIN LATERAL (SELECT name FROM func('e2e::rel_persons') WHERE dept_id = u.val ORDER BY salary DESC LIMIT 1) p ORDER BY 1

Error:

no named relation found for 'u', ensure you have aliased the correct table/subquery

bool_column_implicit

📗 Relation Path

Input SQL:

SELECT name FROM persons WHERE active ORDER BY 1

Legend SQL:

SELECT name FROM func('e2e::rel_persons') WHERE active ORDER BY 1

Error:

Can't find a match for function 'meta::pure::functions::relation::filter(Relation<(id:Integer[1], name:String, age:Integer, salary:Float, hire_date:StrictDate, active:Boolean, dept_id:Integer)>[1],LambdaFunction<{(id:Integer[1], name:String, age:Integer, salary:Float, hire_date:StrictDate, active:Boolean, dept_id:Integer)[1]->Boolean[0..1]}>[1])'.\nFunctions that can match if parameter types or multiplicities are changed:\n\t\tfilter(Relation[1],Function<{T[1]->Boolean[1]}>[1]):Relation[1]\n\t\tfilter(TabularDataSet[1],Function<{TDSRow[1]->Boolean[1]}>[1]):TabularDataSet[1]\n\t\tfilter(T[],Function<{T[1]->Boolean[1]}>[1]):T[]\n

star_from_cte

📘 TDS Path

Input SQL:

WITH cte AS (SELECT name, dept_id FROM persons WHERE dept_id IS NOT NULL) SELECT * FROM cte ORDER BY name

Legend SQL:

WITH cte AS (SELECT name, dept_id FROM func('e2e::tds_persons') WHERE dept_id IS NOT NULL) SELECT * FROM cte ORDER BY name

Error:

Unhandled value type: meta::pure::tds::SortInformation

json_extract_field

📘 TDS Path

Input SQL:

SELECT '{"a":1,"b":2}'::jsonb -> 'a' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":1,"b":2}'::jsonb -> 'a' AS result FROM func('e2e::tds_persons') WHERE id = 1

Error:

Execution error at (resource:/core_relational/relational/pureToSQLQuery/pureToSQLQuery_deprecated.pure line:282 column:90), "Match failure: 1 instanceOf class org.finos.legend.pure.runtime.java.extension.external.variant.VariantInstanceImpl"

📗 Relation Path

Input SQL:

SELECT '{"a":1,"b":2}'::jsonb -> 'a' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":1,"b":2}'::jsonb -> 'a' AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

Error mapping not found for class Map cache:''

json_extract_field_nested

📘 TDS Path

Input SQL:

SELECT '{"a":{"b":3}}'::jsonb -> 'a' -> 'b' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":{"b":3}}'::jsonb -> 'a' -> 'b' AS result FROM func('e2e::tds_persons') WHERE id = 1

Error:

Execution error at (resource:/core_relational/relational/pureToSQLQuery/pureToSQLQuery_deprecated.pure line:282 column:90), "Match failure: 3 instanceOf class org.finos.legend.pure.runtime.java.extension.external.variant.VariantInstanceImpl"

📗 Relation Path

Input SQL:

SELECT '{"a":{"b":3}}'::jsonb -> 'a' -> 'b' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":{"b":3}}'::jsonb -> 'a' -> 'b' AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

Error mapping not found for class Map cache:''

json_extract_array_index

📘 TDS Path

Input SQL:

SELECT '[10,20,30]'::jsonb -> 1 AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '[10,20,30]'::jsonb -> 1 AS result FROM func('e2e::tds_persons') WHERE id = 1

Error:

Execution error at (resource:/core_relational/relational/pureToSQLQuery/pureToSQLQuery_deprecated.pure line:282 column:90), "Match failure: 20 instanceOf class org.finos.legend.pure.runtime.java.extension.external.variant.VariantInstanceImpl"

json_extract_array_negative_index

📘 TDS Path

Input SQL:

SELECT '[10,20,30]'::jsonb -> -1 AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '[10,20,30]'::jsonb -> -1 AS result FROM func('e2e::tds_persons') WHERE id = 1

Error:

Execution error at ??, "The system is trying to get an element at offset -1 where the collection is of size 3"

json_extract_text_field

📗 Relation Path

Input SQL:

SELECT '{"a":"hello","b":2}'::jsonb ->> 'a' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":"hello","b":2}'::jsonb ->> 'a' AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

Error mapping not found for class Map cache:''

json_extract_text_number

📗 Relation Path

Input SQL:

SELECT '{"a":1,"b":2}'::jsonb ->> 'a' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":1,"b":2}'::jsonb ->> 'a' AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

Error mapping not found for class Map cache:''

json_extract_text_null_key

📗 Relation Path

Input SQL:

SELECT '{"a":1}'::jsonb ->> 'missing' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":1}'::jsonb ->> 'missing' AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

Error mapping not found for class Map cache:''

json_path_extract

📘 TDS Path

Input SQL:

SELECT '{"a":{"b":{"c":42}}}'::jsonb #> '{a,b}' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":{"b":{"c":42}}}'::jsonb #> '{a,b}' AS result FROM func('e2e::tds_persons') WHERE id = 1

Error:

Execution error at (resource:/core_relational/relational/pureToSQLQuery/pureToSQLQuery_deprecated.pure line:282 column:90), "Match failure: {"c":42} instanceOf class org.finos.legend.pure.runtime.java.extension.external.variant.VariantInstanceImpl"

📗 Relation Path

Input SQL:

SELECT '{"a":{"b":{"c":42}}}'::jsonb #> '{a,b}' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":{"b":{"c":42}}}'::jsonb #> '{a,b}' AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

Error mapping not found for class Map cache:''

json_path_extract_array

📘 TDS Path

Input SQL:

SELECT '{"a":[1,2,3]}'::jsonb #> '{a,1}' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":[1,2,3]}'::jsonb #> '{a,1}' AS result FROM func('e2e::tds_persons') WHERE id = 1

Error:

Execution error at (resource:/core_relational/relational/pureToSQLQuery/pureToSQLQuery_deprecated.pure line:282 column:90), "Match failure: 2 instanceOf class org.finos.legend.pure.runtime.java.extension.external.variant.VariantInstanceImpl"

📗 Relation Path

Input SQL:

SELECT '{"a":[1,2,3]}'::jsonb #> '{a,1}' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":[1,2,3]}'::jsonb #> '{a,1}' AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

Error mapping not found for class Map cache:''

json_path_extract_deep

📘 TDS Path

Input SQL:

SELECT '{"a":{"b":{"c":"deep"}}}'::jsonb #> '{a,b,c}' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":{"b":{"c":"deep"}}}'::jsonb #> '{a,b,c}' AS result FROM func('e2e::tds_persons') WHERE id = 1

Error:

Execution error at (resource:/core_relational/relational/pureToSQLQuery/pureToSQLQuery_deprecated.pure line:282 column:90), "Match failure: "deep" instanceOf class org.finos.legend.pure.runtime.java.extension.external.variant.VariantInstanceImpl"

📗 Relation Path

Input SQL:

SELECT '{"a":{"b":{"c":"deep"}}}'::jsonb #> '{a,b,c}' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":{"b":{"c":"deep"}}}'::jsonb #> '{a,b,c}' AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

Error mapping not found for class Map cache:''

json_path_extract_text

📗 Relation Path

Input SQL:

SELECT '{"a":{"b":"hello"}}'::jsonb #>> '{a,b}' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":{"b":"hello"}}'::jsonb #>> '{a,b}' AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

Error mapping not found for class Map cache:''

json_path_extract_text_number

📗 Relation Path

Input SQL:

SELECT '{"a":{"b":42}}'::jsonb #>> '{a,b}' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":{"b":42}}'::jsonb #>> '{a,b}' AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

Error mapping not found for class Map cache:''

jsonb_contains_right

🔴 Failed in both TDS and Relation

Input SQL:

SELECT '{"a":1,"b":2}'::jsonb @> '{"a":1}'::jsonb AS result FROM persons WHERE id = 1

Legend SQL (TDS):

SELECT '{"a":1,"b":2}'::jsonb @> '{"a":1}'::jsonb AS result FROM func('e2e::tds_persons') WHERE id = 1

Legend SQL (Relation):

SELECT '{"a":1,"b":2}'::jsonb @> '{"a":1}'::jsonb AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

json operation right side must be integer or string

jsonb_contains_right_array

🔴 Failed in both TDS and Relation

Input SQL:

SELECT '[1,2,3]'::jsonb @> '[1,3]'::jsonb AS result FROM persons WHERE id = 1

Legend SQL (TDS):

SELECT '[1,2,3]'::jsonb @> '[1,3]'::jsonb AS result FROM func('e2e::tds_persons') WHERE id = 1

Legend SQL (Relation):

SELECT '[1,2,3]'::jsonb @> '[1,3]'::jsonb AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

json operation right side must be integer or string

jsonb_contains_right_false

🔴 Failed in both TDS and Relation

Input SQL:

SELECT '{"a":1}'::jsonb @> '{"a":2}'::jsonb AS result FROM persons WHERE id = 1

Legend SQL (TDS):

SELECT '{"a":1}'::jsonb @> '{"a":2}'::jsonb AS result FROM func('e2e::tds_persons') WHERE id = 1

Legend SQL (Relation):

SELECT '{"a":1}'::jsonb @> '{"a":2}'::jsonb AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

json operation right side must be integer or string

jsonb_contains_right_nested

🔴 Failed in both TDS and Relation

Input SQL:

SELECT '{"a":{"b":1}}'::jsonb @> '{"a":{"b":1}}'::jsonb AS result FROM persons WHERE id = 1

Legend SQL (TDS):

SELECT '{"a":{"b":1}}'::jsonb @> '{"a":{"b":1}}'::jsonb AS result FROM func('e2e::tds_persons') WHERE id = 1

Legend SQL (Relation):

SELECT '{"a":{"b":1}}'::jsonb @> '{"a":{"b":1}}'::jsonb AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

json operation right side must be integer or string

jsonb_contained_by

🔴 Failed in both TDS and Relation

Input SQL:

SELECT '{"a":1}'::jsonb <@ '{"a":1,"b":2}'::jsonb AS result FROM persons WHERE id = 1

Legend SQL (TDS):

SELECT '{"a":1}'::jsonb <@ '{"a":1,"b":2}'::jsonb AS result FROM func('e2e::tds_persons') WHERE id = 1

Legend SQL (Relation):

SELECT '{"a":1}'::jsonb <@ '{"a":1,"b":2}'::jsonb AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

json operation right side must be integer or string

jsonb_contained_by_false

🔴 Failed in both TDS and Relation

Input SQL:

SELECT '{"a":1,"b":2}'::jsonb <@ '{"a":1}'::jsonb AS result FROM persons WHERE id = 1

Legend SQL (TDS):

SELECT '{"a":1,"b":2}'::jsonb <@ '{"a":1}'::jsonb AS result FROM func('e2e::tds_persons') WHERE id = 1

Legend SQL (Relation):

SELECT '{"a":1,"b":2}'::jsonb <@ '{"a":1}'::jsonb AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

json operation right side must be integer or string

jsonb_has_all_keys

🔴 Failed in both TDS and Relation

Input SQL:

SELECT '{"a":1,"b":2,"c":3}'::jsonb ?& array['a','b'] AS result FROM persons WHERE id = 1

Legend SQL (TDS):

SELECT '{"a":1,"b":2,"c":3}'::jsonb ?& ARRAY['a', 'b'] AS result FROM func('e2e::tds_persons') WHERE id = 1

Legend SQL (Relation):

SELECT '{"a":1,"b":2,"c":3}'::jsonb ?& ARRAY['a', 'b'] AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

json operation right side must be integer or string

jsonb_has_all_keys_false

🔴 Failed in both TDS and Relation

Input SQL:

SELECT '{"a":1,"b":2}'::jsonb ?& array['a','c'] AS result FROM persons WHERE id = 1

Legend SQL (TDS):

SELECT '{"a":1,"b":2}'::jsonb ?& ARRAY['a', 'c'] AS result FROM func('e2e::tds_persons') WHERE id = 1

Legend SQL (Relation):

SELECT '{"a":1,"b":2}'::jsonb ?& ARRAY['a', 'c'] AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

json operation right side must be integer or string

json_extract_then_text

📗 Relation Path

Input SQL:

SELECT '{"a":{"b":"value"}}'::jsonb -> 'a' ->> 'b' AS result FROM persons WHERE id = 1

Legend SQL:

SELECT '{"a":{"b":"value"}}'::jsonb -> 'a' ->> 'b' AS result FROM func('e2e::rel_persons') WHERE id = 1

Error:

Error mapping not found for class Map cache:''

jsonb_contains_in_where

🔴 Failed in both TDS and Relation

Input SQL:

SELECT 1 AS result FROM persons WHERE '{"active":true}'::jsonb @> '{"active":true}'::jsonb AND id = 1

Legend SQL (TDS):

SELECT 1 AS result FROM func('e2e::tds_persons') WHERE '{"active":true}'::jsonb @> '{"active":true}'::jsonb AND id = 1

Legend SQL (Relation):

SELECT 1 AS result FROM func('e2e::rel_persons') WHERE '{"active":true}'::jsonb @> '{"active":true}'::jsonb AND id = 1

Error:

json operation right side must be integer or string

interval_ts_diff_literal

🔴 Failed in both TDS and Relation

Input SQL:

SELECT ts - TIMESTAMP '2020-01-01 00:00:00' AS result FROM dates WHERE ts IS NOT NULL ORDER BY 1

Legend SQL (TDS):

SELECT ts - TIMESTAMP '2020-01-01 00:00:00' AS result FROM func('e2e::tds_dates') WHERE ts IS NOT NULL ORDER BY 1

Legend SQL (Relation):

SELECT ts - TIMESTAMP '2020-01-01 00:00:00' AS result FROM func('e2e::rel_dates') WHERE ts IS NOT NULL ORDER BY 1

Error:

ERROR: Error while retrieving a row

interval_extract_epoch

📘 TDS Path

Input SQL:

SELECT EXTRACT(EPOCH FROM ts - TIMESTAMP '2020-01-01') AS result FROM dates WHERE ts IS NOT NULL ORDER BY 1

Legend SQL:

SELECT EXTRACT(EPOCH FROM ts - TIMESTAMP '2020-01-01') AS result FROM func('e2e::tds_dates') WHERE ts IS NOT NULL ORDER BY 1

Error:

ERROR: operator does not exist: interval - timestamp without time zone\n Hint: No operator matches the given name and argument types. You might need to add explicit type casts.\n Position: 129

📗 Relation Path

Input SQL:

SELECT EXTRACT(EPOCH FROM ts - TIMESTAMP '2020-01-01') AS result FROM dates WHERE ts IS NOT NULL ORDER BY 1

Legend SQL:

SELECT EXTRACT(EPOCH FROM ts - TIMESTAMP '2020-01-01') AS result FROM func('e2e::rel_dates') WHERE ts IS NOT NULL ORDER BY 1

Error:

Can't find a match for function 'meta::pure::functions::date::toEpochValue(Integer[1])'.\nFunctions that can match if parameter types or multiplicities are changed:\n\t\ttoEpochValue(Date[1]):Integer[1]\nFunctions that can match if number of parameters are changed:\n\t\ttoEpochValue(Date[1],DurationUnit[1]):Integer[1]\n

interval_extract_days

📗 Relation Path

Input SQL:

SELECT EXTRACT(DAY FROM ts - TIMESTAMP '2020-01-01') AS result FROM dates WHERE ts IS NOT NULL ORDER BY 1

Legend SQL:

SELECT EXTRACT(DAY FROM ts - TIMESTAMP '2020-01-01') AS result FROM func('e2e::rel_dates') WHERE ts IS NOT NULL ORDER BY 1

Error:

Can't find a match for function 'meta::pure::functions::date::dayOfMonth(Integer[1])'.\nFunctions that can match if parameter types or multiplicities are changed:\n\t\tdayOfMonth(Date[1]):Integer[1]\n

interval_column_plus_date

🔴 Failed in both TDS and Relation

Input SQL:

SELECT d + i AS result FROM dates WHERE d IS NOT NULL AND i IS NOT NULL ORDER BY 1

Legend SQL (TDS):

SELECT d + i AS result FROM func('e2e::tds_dates') WHERE d IS NOT NULL AND i IS NOT NULL ORDER BY 1

Legend SQL (Relation):

SELECT d + i AS result FROM func('e2e::rel_dates') WHERE d IS NOT NULL AND i IS NOT NULL ORDER BY 1

Error:

no column found named: 'i'. Available columns: [id, d, ts, tsz]

interval_column_plus_ts

🔴 Failed in both TDS and Relation

Input SQL:

SELECT ts + i AS result FROM dates WHERE ts IS NOT NULL AND i IS NOT NULL ORDER BY 1

Legend SQL (TDS):

SELECT ts + i AS result FROM func('e2e::tds_dates') WHERE ts IS NOT NULL AND i IS NOT NULL ORDER BY 1

Legend SQL (Relation):

SELECT ts + i AS result FROM func('e2e::rel_dates') WHERE ts IS NOT NULL AND i IS NOT NULL ORDER BY 1

Error:

no column found named: 'i'. Available columns: [id, d, ts, tsz]

colres_e2_window_aggregate_over_join

📗 Relation Path

Input SQL:

SELECT p.name AS name, p.dept_id AS dept_id, SUM(p.salary) OVER (PARTITION BY p.dept_id) AS dept_total FROM persons p INNER JOIN departments d ON p.dept_id = d.id WHERE p.salary IS NOT NULL ORDER BY 2, 1

Legend SQL:

SELECT p.name AS name, p.dept_id AS dept_id, SUM(p.salary) OVER (PARTITION BY p.dept_id) AS dept_total FROM func('e2e::rel_persons') p INNER JOIN func('e2e::rel_departments') d ON p.dept_id = d.id WHERE p.salary IS NOT NULL ORDER BY 2, 1

Error:

Can't find property 'salary_p' in class 'meta::pure::metamodel::relation::Relation'

colres_e2_window_two_sides_renamed_differently

📗 Relation Path

Input SQL:

SELECT p.name AS name, d.name AS dept_name, MAX(p.id) OVER (PARTITION BY d.name) AS max_id_in_dept FROM persons p INNER JOIN departments d ON p.dept_id = d.id ORDER BY 2, 1

Legend SQL:

SELECT p.name AS name, d.name AS dept_name, MAX(p.id) OVER (PARTITION BY d.name) AS max_id_in_dept FROM func('e2e::rel_persons') p INNER JOIN func('e2e::rel_departments') d ON p.dept_id = d.id ORDER BY 2, 1

Error:

Can't find property 'id_p' in class 'meta::pure::metamodel::relation::Relation'

colres_gap_order_by_expression_over_unprojected_column

🔴 Failed in both TDS and Relation

Input SQL:

SELECT name FROM persons WHERE age IS NOT NULL ORDER BY age * 2, name

Legend SQL (TDS):

SELECT name FROM func('e2e::tds_persons') WHERE age IS NOT NULL ORDER BY age * 2, name

Legend SQL (Relation):

SELECT name FROM func('e2e::rel_persons') WHERE age IS NOT NULL ORDER BY age * 2, name

Error:

no column found named: 'age'. Available columns: [name]

within_group_rank_hypothetical

📗 Relation Path

Input SQL:

SELECT RANK(50000) WITHIN GROUP (ORDER BY salary) AS rank_at_50k FROM persons

Legend SQL:

SELECT RANK(50000) WITHIN GROUP (ORDER BY salary) AS rank_at_50k FROM func('e2e::rel_persons')

Error:

Can't find variable class for variable 'p' in the graph

recursive_cte_union_distinct_unsupported

📗 Relation Path

Input SQL:

WITH RECURSIVE t(n) AS (SELECT id FROM persons WHERE id = 1 UNION SELECT n + 1 FROM t WHERE n < 5) SELECT n FROM t ORDER BY 1

Legend SQL:

WITH RECURSIVE t (n) AS (SELECT id FROM func('e2e::rel_persons') WHERE id = 1 UNION SELECT n + 1 FROM t WHERE n < 5) SELECT n FROM t ORDER BY 1

Error:

WITH RECURSIVE requires UNION ALL; UNION would have to de-duplicate across rounds, which recurse() does not do

recursive_keyword_without_recursion

📘 TDS Path

Input SQL:

WITH RECURSIVE t AS (SELECT name, salary FROM persons WHERE salary > 70000) SELECT name, salary FROM t ORDER BY 1

Legend SQL:

WITH RECURSIVE t AS (SELECT name, salary FROM func('e2e::tds_persons') WHERE salary > 70000) SELECT name, salary FROM t ORDER BY 1

Error:

Unhandled value type: meta::pure::tds::SortInformation

frame_rows_unbounded_current

📗 Relation Path

Input SQL:

SELECT name, salary, SUM(salary) OVER (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM persons WHERE salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary, SUM(salary) OVER (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM func('e2e::rel_persons') WHERE salary IS NOT NULL ORDER BY 1

Error:

Cannot invoke "Object.getClass()" because "resO" is null

frame_rows_unbounded_current_avg

📗 Relation Path

Input SQL:

SELECT name, salary, AVG(salary) OVER (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg FROM persons WHERE salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary, AVG(salary) OVER (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg FROM func('e2e::rel_persons') WHERE salary IS NOT NULL ORDER BY 1

Error:

Cannot invoke "Object.getClass()" because "resO" is null

frame_rows_whole_partition

📗 Relation Path

Input SQL:

SELECT name, salary, SUM(salary) OVER (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total FROM persons WHERE salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary, SUM(salary) OVER (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total FROM func('e2e::rel_persons') WHERE salary IS NOT NULL ORDER BY 1

Error:

Cannot invoke "Object.getClass()" because "resO" is null

frame_rows_sliding_3

📗 Relation Path

Input SQL:

SELECT name, salary, AVG(salary) OVER (ORDER BY name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg FROM persons WHERE salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary, AVG(salary) OVER (ORDER BY name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_avg FROM func('e2e::rel_persons') WHERE salary IS NOT NULL ORDER BY 1

Error:

Cannot invoke "Object.getClass()" because "resO" is null

frame_rows_lookback_2

📗 Relation Path

Input SQL:

SELECT name, salary, SUM(salary) OVER (ORDER BY name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_3 FROM persons WHERE salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary, SUM(salary) OVER (ORDER BY name ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS sum_3 FROM func('e2e::rel_persons') WHERE salary IS NOT NULL ORDER BY 1

Error:

Cannot invoke "Object.getClass()" because "resO" is null

frame_rows_current_to_end

📗 Relation Path

Input SQL:

SELECT name, salary, COUNT(*) OVER (ORDER BY name ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining FROM persons WHERE salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary, COUNT(*) OVER (ORDER BY name ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining FROM func('e2e::rel_persons') WHERE salary IS NOT NULL ORDER BY 1

Error:

Execution error at (resource:/core_external_query_sql/binding/fromPure/fromPure.pure line:1383 column:48), "Cannot cast a collection of size 0 to multiplicity [1]"

frame_multi_agg

📗 Relation Path

Input SQL:

SELECT name, salary, SUM(salary) OVER w AS run_sum, AVG(salary) OVER w AS run_avg, COUNT(*) OVER w AS run_cnt, MIN(salary) OVER w AS run_min, MAX(salary) OVER w AS run_max FROM persons WHERE salary IS NOT NULL WINDOW w AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ORDER BY 1

Legend SQL:

SELECT name, salary, SUM(salary) OVER w AS run_sum, AVG(salary) OVER w AS run_avg, COUNT(*) OVER w AS run_cnt, MIN(salary) OVER w AS run_min, MAX(salary) OVER w AS run_max FROM func('e2e::rel_persons') WHERE salary IS NOT NULL WINDOW w AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ORDER BY 1

Error:

Execution error at (resource:/core_external_query_sql/binding/fromPure/fromPure.pure line:1383 column:48), "Cannot cast a collection of size 0 to multiplicity [1]"

frame_rows_lookback_only

📗 Relation Path

Input SQL:

SELECT name, salary, SUM(salary) OVER (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS prev_2 FROM persons WHERE salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary, SUM(salary) OVER (ORDER BY name ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS prev_2 FROM func('e2e::rel_persons') WHERE salary IS NOT NULL ORDER BY 1

Error:

frame_rows_lookahead

📗 Relation Path

Input SQL:

SELECT name, salary, SUM(salary) OVER (ORDER BY name ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS next_2 FROM persons WHERE salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary, SUM(salary) OVER (ORDER BY name ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS next_2 FROM func('e2e::rel_persons') WHERE salary IS NOT NULL ORDER BY 1

Error:

frame_rows_current_only

📗 Relation Path

Input SQL:

SELECT name, salary, SUM(salary) OVER (ORDER BY name ROWS BETWEEN CURRENT ROW AND CURRENT ROW) AS self_only FROM persons WHERE salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary, SUM(salary) OVER (ORDER BY name ROWS BETWEEN CURRENT ROW AND CURRENT ROW) AS self_only FROM func('e2e::rel_persons') WHERE salary IS NOT NULL ORDER BY 1

Error:

frame_rows_wide_sliding

📗 Relation Path

Input SQL:

SELECT name, salary, AVG(salary) OVER (ORDER BY name ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS wide_avg FROM persons WHERE salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary, AVG(salary) OVER (ORDER BY name ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS wide_avg FROM func('e2e::rel_persons') WHERE salary IS NOT NULL ORDER BY 1

Error:

named_window_with_frame

📗 Relation Path

Input SQL:

SELECT name, salary, AVG(salary) OVER w AS moving_avg FROM persons WHERE salary IS NOT NULL WINDOW w AS (ORDER BY name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ORDER BY 1

Legend SQL:

SELECT name, salary, AVG(salary) OVER w AS moving_avg FROM func('e2e::rel_persons') WHERE salary IS NOT NULL WINDOW w AS (ORDER BY name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ORDER BY 1

Error:

named_window_multi_agg

📗 Relation Path

Input SQL:

SELECT name, salary, MIN(salary) OVER w AS run_min, MAX(salary) OVER w AS run_max, COUNT(*) OVER w AS run_cnt FROM persons WHERE salary IS NOT NULL WINDOW w AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ORDER BY 1

Legend SQL:

SELECT name, salary, MIN(salary) OVER w AS run_min, MAX(salary) OVER w AS run_max, COUNT(*) OVER w AS run_cnt FROM func('e2e::rel_persons') WHERE salary IS NOT NULL WINDOW w AS (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) ORDER BY 1

Error:

Execution error at (resource:/core_external_query_sql/binding/fromPure/fromPure.pure line:1383 column:48), "Cannot cast a collection of size 0 to multiplicity [1]"

comp_three_table_agg

📘 TDS Path

Input SQL:

SELECT d.name AS dept_name, COUNT(DISTINCT p.id) AS emp_count, COALESCE(SUM(o.amount), 0) AS total_orders FROM departments d LEFT JOIN persons p ON p.dept_id = d.id LEFT JOIN orders o ON o.person_id = p.id GROUP BY d.name ORDER BY 1

Legend SQL:

SELECT d.name AS dept_name, COUNT(DISTINCT p.id) AS emp_count, COALESCE(SUM(o.amount), 0) AS total_orders FROM func('e2e::tds_departments') d LEFT JOIN func('e2e::tds_persons') p ON p.dept_id = d.id LEFT JOIN func('e2e::tds_orders') o ON o.person_id = p.id GROUP BY d.name ORDER BY 1

Error:

dyna function [firstNotNull] is not registered in meta::relational::functions::sqlQueryToString::DynaFunctionRegistry

comp_left_join_coalesce_multi

📘 TDS Path

Input SQL:

SELECT d.name, COUNT(p.id) AS emps, COALESCE(MIN(p.salary), 0) AS min_sal, COALESCE(MAX(p.salary), 0) AS max_sal FROM departments d LEFT JOIN persons p ON p.dept_id = d.id GROUP BY d.name ORDER BY 1

Legend SQL:

SELECT d.name, COUNT(p.id) AS emps, COALESCE(MIN(p.salary), 0) AS min_sal, COALESCE(MAX(p.salary), 0) AS max_sal FROM func('e2e::tds_departments') d LEFT JOIN func('e2e::tds_persons') p ON p.dept_id = d.id GROUP BY d.name ORDER BY 1

Error:

dyna function [firstNotNull] is not registered in meta::relational::functions::sqlQueryToString::DynaFunctionRegistry

stress_running_case

📗 Relation Path

Input SQL:

SELECT name, salary, SUM(CASE WHEN salary > 60000 THEN salary ELSE 0 END) OVER (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS high_earner_running FROM persons WHERE salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, salary, SUM(CASE WHEN salary > 60000 THEN salary ELSE 0 END) OVER (ORDER BY name ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS high_earner_running FROM func('e2e::rel_persons') WHERE salary IS NOT NULL ORDER BY 1

Error:

stress_multi_agg_join

📘 TDS Path

Input SQL:

SELECT d.name AS dept, COUNT(DISTINCT p.id) AS employees, COALESCE(AVG(p.salary), 0) AS avg_salary, COALESCE(SUM(o.amount), 0) AS total_orders, COALESCE(COUNT(DISTINCT o.id), 0) AS order_count FROM departments d LEFT JOIN persons p ON p.dept_id = d.id LEFT JOIN orders o ON o.person_id = p.id GROUP BY d.name ORDER BY 1

Legend SQL:

SELECT d.name AS dept, COUNT(DISTINCT p.id) AS employees, COALESCE(AVG(p.salary), 0) AS avg_salary, COALESCE(SUM(o.amount), 0) AS total_orders, COALESCE(COUNT(DISTINCT o.id), 0) AS order_count FROM func('e2e::tds_departments') d LEFT JOIN func('e2e::tds_persons') p ON p.dept_id = d.id LEFT JOIN func('e2e::tds_orders') o ON o.person_id = p.id GROUP BY d.name ORDER BY 1

Error:

dyna function [firstNotNull] is not registered in meta::relational::functions::sqlQueryToString::DynaFunctionRegistry

stress_percentile_sim

📘 TDS Path

Input SQL:

SELECT name, salary, ROUND(CAST(rn AS NUMERIC) / cnt * 100, 1) AS percentile FROM (SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS cnt FROM persons WHERE salary IS NOT NULL) sub ORDER BY 1

Legend SQL:

SELECT name, salary, ROUND(CAST(rn AS NUMERIC) / cnt * 100, 1) AS percentile FROM (SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS cnt FROM func('e2e::tds_persons') WHERE salary IS NOT NULL) sub ORDER BY 1

Error:

Execution error at (resource:/core_external_query_sql/binding/fromPure/fromPure.pure line:1402 column:36), "Match failure: null"

📗 Relation Path

Input SQL:

SELECT name, salary, ROUND(CAST(rn AS NUMERIC) / cnt * 100, 1) AS percentile FROM (SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS cnt FROM persons WHERE salary IS NOT NULL) sub ORDER BY 1

Legend SQL:

SELECT name, salary, ROUND(CAST(rn AS NUMERIC) / cnt * 100, 1) AS percentile FROM (SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS cnt FROM func('e2e::rel_persons') WHERE salary IS NOT NULL) sub ORDER BY 1

Error:

Execution error at (resource:/core_external_query_sql/binding/fromPure/fromPure.pure line:1383 column:48), "Cannot cast a collection of size 0 to multiplicity [1]"

nested_exists_agg

📗 Relation Path

Input SQL:

SELECT d.name, d.budget FROM departments d WHERE EXISTS (SELECT 1 FROM persons p WHERE p.dept_id = d.id GROUP BY p.dept_id HAVING COUNT(*) > 2) ORDER BY 1

Legend SQL:

SELECT d.name, d.budget FROM func('e2e::rel_departments') d WHERE EXISTS (SELECT 1 FROM func('e2e::rel_persons') p WHERE p.dept_id = d.id GROUP BY p.dept_id HAVING COUNT(*) > 2) ORDER BY 1

Error:

Column 'id' of the enclosing query cannot be referenced from this subquery: both use the table alias 'root'

UNSUPPORTED_SYNTAX (191 tests)

except_basic

🔴 Failed in both TDS and Relation

Input SQL:

SELECT dept_id FROM persons WHERE dept_id IS NOT NULL EXCEPT SELECT id FROM departments WHERE budget < 600000 ORDER BY 1

Legend SQL (TDS):

SELECT dept_id FROM func('e2e::tds_persons') WHERE dept_id IS NOT NULL EXCEPT SELECT id FROM func('e2e::tds_departments') WHERE budget < 600000 ORDER BY 1

Legend SQL (Relation):

SELECT dept_id FROM func('e2e::rel_persons') WHERE dept_id IS NOT NULL EXCEPT SELECT id FROM func('e2e::rel_departments') WHERE budget < 600000 ORDER BY 1

Error:

Unsupported: Except

intersect_basic

🔴 Failed in both TDS and Relation

Input SQL:

SELECT dept_id FROM persons WHERE dept_id IS NOT NULL AND salary > 50000 INTERSECT SELECT dept_id FROM persons WHERE dept_id IS NOT NULL AND age > 30 ORDER BY 1

Legend SQL (TDS):

SELECT dept_id FROM func('e2e::tds_persons') WHERE dept_id IS NOT NULL AND salary > 50000 INTERSECT SELECT dept_id FROM func('e2e::tds_persons') WHERE dept_id IS NOT NULL AND age > 30 ORDER BY 1

Legend SQL (Relation):

SELECT dept_id FROM func('e2e::rel_persons') WHERE dept_id IS NOT NULL AND salary > 50000 INTERSECT SELECT dept_id FROM func('e2e::rel_persons') WHERE dept_id IS NOT NULL AND age > 30 ORDER BY 1

Error:

Unsupported: Intersect

subquery_in_where

📘 TDS Path

Input SQL:

SELECT name FROM persons WHERE id IN (SELECT person_id FROM orders WHERE amount > 100) ORDER BY 1

Legend SQL:

SELECT name FROM func('e2e::tds_persons') WHERE id IN (SELECT person_id FROM func('e2e::tds_orders') WHERE amount > 100) ORDER BY 1

Error:

Unsupported: in (subquery) only supported on relation inputs

subquery_in_dept

📘 TDS Path

Input SQL:

SELECT name FROM persons WHERE dept_id IN (SELECT id FROM departments WHERE budget > 500000) ORDER BY 1

Legend SQL:

SELECT name FROM func('e2e::tds_persons') WHERE dept_id IN (SELECT id FROM func('e2e::tds_departments') WHERE budget > 500000) ORDER BY 1

Error:

Unsupported: in (subquery) only supported on relation inputs

subquery_not_in

📘 TDS Path

Input SQL:

SELECT name FROM persons WHERE id NOT IN (SELECT person_id FROM orders WHERE person_id IS NOT NULL) ORDER BY 1

Legend SQL:

SELECT name FROM func('e2e::tds_persons') WHERE id NOT IN (SELECT person_id FROM func('e2e::tds_orders') WHERE person_id IS NOT NULL) ORDER BY 1

Error:

Unsupported: in (subquery) only supported on relation inputs

📗 Relation Path

Input SQL:

SELECT name FROM persons WHERE id NOT IN (SELECT person_id FROM orders WHERE person_id IS NOT NULL) ORDER BY 1

Legend SQL:

SELECT name FROM func('e2e::rel_persons') WHERE id NOT IN (SELECT person_id FROM func('e2e::rel_orders') WHERE person_id IS NOT NULL) ORDER BY 1

Error:

Negating in(value, Relation) is not supported on relational stores. Use not exists instead, for example replace !$d.departmentId->in($employees->select(~department)) with $d.departmentId->isEmpty() || !$employees->exists(e | $e.department == $d.departmentId)

subquery_not_in_dept

📘 TDS Path

Input SQL:

SELECT name FROM persons WHERE dept_id NOT IN (SELECT id FROM departments WHERE budget < 600000) AND dept_id IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name FROM func('e2e::tds_persons') WHERE dept_id NOT IN (SELECT id FROM func('e2e::tds_departments') WHERE budget < 600000) AND dept_id IS NOT NULL ORDER BY 1

Error:

Unsupported: in (subquery) only supported on relation inputs

📗 Relation Path

Input SQL:

SELECT name FROM persons WHERE dept_id NOT IN (SELECT id FROM departments WHERE budget < 600000) AND dept_id IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name FROM func('e2e::rel_persons') WHERE dept_id NOT IN (SELECT id FROM func('e2e::rel_departments') WHERE budget < 600000) AND dept_id IS NOT NULL ORDER BY 1

Error:

Negating in(value, Relation) is not supported on relational stores. Use not exists instead, for example replace !$d.departmentId->in($employees->select(~department)) with $d.departmentId->isEmpty() || !$employees->exists(e | $e.department == $d.departmentId)

subquery_exists

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE EXISTS (SELECT 1 FROM orders o WHERE o.person_id = p.id) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE EXISTS (SELECT 1 FROM func('e2e::tds_orders') o WHERE o.person_id = p.id) ORDER BY 1

Error:

Unsupported: exists only supported on relation inputs

subquery_exists_dept

📘 TDS Path

Input SQL:

SELECT d.name FROM departments d WHERE EXISTS (SELECT 1 FROM persons p WHERE p.dept_id = d.id AND p.salary > 70000) ORDER BY 1

Legend SQL:

SELECT d.name FROM func('e2e::tds_departments') d WHERE EXISTS (SELECT 1 FROM func('e2e::tds_persons') p WHERE p.dept_id = d.id AND p.salary > 70000) ORDER BY 1

Error:

Unsupported: exists only supported on relation inputs

subquery_not_exists

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.person_id = p.id) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE NOT EXISTS (SELECT 1 FROM func('e2e::tds_orders') o WHERE o.person_id = p.id) ORDER BY 1

Error:

Unsupported: exists only supported on relation inputs

subquery_correlated

📘 TDS Path

Input SQL:

SELECT p.name, p.salary FROM persons p WHERE p.salary > (SELECT AVG(salary) FROM persons WHERE dept_id = p.dept_id AND salary IS NOT NULL) ORDER BY 1

Legend SQL:

SELECT p.name, p.salary FROM func('e2e::tds_persons') p WHERE p.salary > (SELECT AVG(salary) FROM func('e2e::tds_persons') WHERE dept_id = p.dept_id AND salary IS NOT NULL) ORDER BY 1

Error:

Unsupported: scalar subquery comparison only supported on relation inputs

subquery_agg_comparison

📘 TDS Path

Input SQL:

SELECT name, salary FROM persons WHERE salary > (SELECT AVG(salary) FROM persons WHERE salary IS NOT NULL) ORDER BY 1

Legend SQL:

SELECT name, salary FROM func('e2e::tds_persons') WHERE salary > (SELECT AVG(salary) FROM func('e2e::tds_persons') WHERE salary IS NOT NULL) ORDER BY 1

Error:

Unsupported: scalar subquery comparison only supported on relation inputs

subquery_agg_min

📘 TDS Path

Input SQL:

SELECT name, salary FROM persons WHERE salary = (SELECT MIN(salary) FROM persons WHERE salary IS NOT NULL) ORDER BY 1

Legend SQL:

SELECT name, salary FROM func('e2e::tds_persons') WHERE salary = (SELECT MIN(salary) FROM func('e2e::tds_persons') WHERE salary IS NOT NULL) ORDER BY 1

Error:

Unsupported: scalar subquery comparison only supported on relation inputs

subquery_in_having

📘 TDS Path

Input SQL:

SELECT dept_id, COUNT(*) AS cnt FROM persons WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING COUNT(*) > (SELECT AVG(cnt) FROM (SELECT COUNT(*) AS cnt FROM persons WHERE dept_id IS NOT NULL GROUP BY dept_id) sub) ORDER BY 1

Legend SQL:

SELECT dept_id, COUNT(*) AS cnt FROM func('e2e::tds_persons') WHERE dept_id IS NOT NULL GROUP BY dept_id HAVING COUNT(*) > (SELECT AVG(cnt) FROM (SELECT COUNT(*) AS cnt FROM func('e2e::tds_persons') WHERE dept_id IS NOT NULL GROUP BY dept_id) sub) ORDER BY 1

Error:

Unsupported: scalar subquery comparison only supported on relation inputs

subquery_union

📘 TDS Path

Input SQL:

SELECT name FROM persons WHERE id IN (SELECT person_id FROM orders WHERE amount > 100 UNION SELECT person_id FROM orders WHERE status = 'completed') ORDER BY 1

Legend SQL:

SELECT name FROM func('e2e::tds_persons') WHERE id IN (SELECT person_id FROM func('e2e::tds_orders') WHERE amount > 100 UNION SELECT person_id FROM func('e2e::tds_orders') WHERE status = 'completed') ORDER BY 1

Error:

Unsupported: in (subquery) only supported on relation inputs

subquery_in_over_union

📘 TDS Path

Input SQL:

SELECT sub.id, sub.name FROM (SELECT id, name FROM persons WHERE dept_id = 1 UNION ALL SELECT id, name FROM persons WHERE dept_id = 2) sub WHERE sub.id IN (SELECT person_id FROM orders WHERE amount > 100) ORDER BY 1

Legend SQL:

SELECT sub.id, sub.name FROM (SELECT id, name FROM func('e2e::tds_persons') WHERE dept_id = 1 UNION ALL SELECT id, name FROM func('e2e::tds_persons') WHERE dept_id = 2) sub WHERE sub.id IN (SELECT person_id FROM func('e2e::tds_orders') WHERE amount > 100) ORDER BY 1

Error:

Unsupported: in (subquery) only supported on relation inputs

subquery_exists_multi_cond

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE EXISTS (SELECT 1 FROM orders o WHERE o.person_id = p.id AND o.amount > 100 AND o.status = 'completed') ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE EXISTS (SELECT 1 FROM func('e2e::tds_orders') o WHERE o.person_id = p.id AND o.amount > 100 AND o.status = 'completed') ORDER BY 1

Error:

Unsupported: exists only supported on relation inputs

subquery_in_case

📘 TDS Path

Input SQL:

SELECT name, CASE WHEN salary > (SELECT AVG(salary) FROM persons WHERE salary IS NOT NULL) THEN 'Above' ELSE 'Below' END AS vs_avg FROM persons WHERE salary IS NOT NULL ORDER BY 1

Legend SQL:

SELECT name, CASE WHEN salary > (SELECT AVG(salary) FROM func('e2e::tds_persons') WHERE salary IS NOT NULL) THEN 'Above' ELSE 'Below' END AS vs_avg FROM func('e2e::tds_persons') WHERE salary IS NOT NULL ORDER BY 1

Error:

Unsupported: scalar subquery comparison only supported on relation inputs

subquery_exists_uncorrelated

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE EXISTS (SELECT 1 FROM departments d WHERE d.budget > 900000) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE EXISTS (SELECT 1 FROM func('e2e::tds_departments') d WHERE d.budget > 900000) ORDER BY 1

Error:

Unsupported: exists only supported on relation inputs

subquery_exists_uncorrelated_empty

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE EXISTS (SELECT 1 FROM departments d WHERE d.budget > 99999999) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE EXISTS (SELECT 1 FROM func('e2e::tds_departments') d WHERE d.budget > 99999999) ORDER BY 1

Error:

Unsupported: exists only supported on relation inputs

subquery_exists_select_column

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE EXISTS (SELECT o.id FROM orders o WHERE o.person_id = p.id AND o.amount > 400) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE EXISTS (SELECT o.id FROM func('e2e::tds_orders') o WHERE o.person_id = p.id AND o.amount > 400) ORDER BY 1

Error:

Unsupported: exists only supported on relation inputs

subquery_exists_grouped

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE EXISTS (SELECT o.person_id, COUNT(*) AS cnt FROM orders o WHERE o.person_id = p.id GROUP BY o.person_id) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE EXISTS (SELECT o.person_id, COUNT(*) AS cnt FROM func('e2e::tds_orders') o WHERE o.person_id = p.id GROUP BY o.person_id) ORDER BY 1

Error:

Unsupported: exists only supported on relation inputs

subquery_in_uncorrelated_filtered

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.dept_id IN (SELECT d.id FROM departments d WHERE d.budget > 500000) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.dept_id IN (SELECT d.id FROM func('e2e::tds_departments') d WHERE d.budget > 500000) ORDER BY 1

Error:

Unsupported: in (subquery) only supported on relation inputs

subquery_in_correlated

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.id IN (SELECT o.person_id FROM orders o WHERE o.amount > p.salary / 1000) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.id IN (SELECT o.person_id FROM func('e2e::tds_orders') o WHERE o.amount > p.salary / 1000) ORDER BY 1

Error:

Unsupported: in (subquery) only supported on relation inputs

subquery_in_empty

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.dept_id IN (SELECT d.id FROM departments d WHERE d.budget > 99999999) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.dept_id IN (SELECT d.id FROM func('e2e::tds_departments') d WHERE d.budget > 99999999) ORDER BY 1

Error:

Unsupported: in (subquery) only supported on relation inputs

subquery_quant_gt_any

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.age > ANY (SELECT p2.age FROM persons p2 WHERE p2.dept_id = 2) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.age > ANY(SELECT p2.age FROM func('e2e::tds_persons') p2 WHERE p2.dept_id = 2) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_quant_eq_any

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.dept_id = ANY (SELECT d.id FROM departments d WHERE d.budget > 700000) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.dept_id = ANY(SELECT d.id FROM func('e2e::tds_departments') d WHERE d.budget > 700000) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_quant_neq_any

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.dept_id <> ANY (SELECT d.id FROM departments d WHERE d.budget > 700000) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.dept_id <> ANY(SELECT d.id FROM func('e2e::tds_departments') d WHERE d.budget > 700000) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_quant_gt_all

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.age > ALL (SELECT p2.age FROM persons p2 WHERE p2.dept_id = 2) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.age > ALL(SELECT p2.age FROM func('e2e::tds_persons') p2 WHERE p2.dept_id = 2) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_quant_neq_all

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.dept_id <> ALL (SELECT d.id FROM departments d WHERE d.budget > 700000) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.dept_id <> ALL(SELECT d.id FROM func('e2e::tds_departments') d WHERE d.budget > 700000) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_quant_ge_some

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.age >= SOME (SELECT p2.age FROM persons p2 WHERE p2.dept_id = 2) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.age >= SOME(SELECT p2.age FROM func('e2e::tds_persons') p2 WHERE p2.dept_id = 2) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_quant_all_null_in_subquery

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.salary > ALL (SELECT p2.salary FROM persons p2 WHERE p2.dept_id = 1) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.salary > ALL(SELECT p2.salary FROM func('e2e::tds_persons') p2 WHERE p2.dept_id = 1) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_quant_all_correlated_empty

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.id < ALL (SELECT o.id FROM orders o WHERE o.person_id = p.id) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.id < ALL(SELECT o.id FROM func('e2e::tds_orders') o WHERE o.person_id = p.id) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_quant_all_empty

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.id > ALL (SELECT p2.id FROM persons p2 WHERE p2.id > 100) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.id > ALL(SELECT p2.id FROM func('e2e::tds_persons') p2 WHERE p2.id > 100) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_quant_all_empty_null_value

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.age > ALL (SELECT p2.age FROM persons p2 WHERE p2.id > 100) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.age > ALL(SELECT p2.age FROM func('e2e::tds_persons') p2 WHERE p2.id > 100) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_quant_any_empty

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE p.id > ANY (SELECT p2.id FROM persons p2 WHERE p2.id > 100) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE p.id > ANY(SELECT p2.id FROM func('e2e::tds_persons') p2 WHERE p2.id > 100) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_quant_not_gt_any

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE NOT (p.age > ANY (SELECT p2.age FROM persons p2 WHERE p2.dept_id = 2)) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE NOT (p.age > ANY(SELECT p2.age FROM func('e2e::tds_persons') p2 WHERE p2.dept_id = 2)) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_quant_not_gt_all_null_in_subquery

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE NOT (p.salary > ALL (SELECT p2.salary FROM persons p2 WHERE p2.dept_id = 1)) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE NOT (p.salary > ALL(SELECT p2.salary FROM func('e2e::tds_persons') p2 WHERE p2.dept_id = 1)) ORDER BY 1

Error:

Unsupported: quantified comparison only supported on relation inputs

subquery_exists_correlated_unqualified_outer_column

📘 TDS Path

Input SQL:

SELECT p.name FROM persons p WHERE EXISTS (SELECT 1 FROM orders o WHERE o.person_id = p.id AND o.amount > salary / 1000) ORDER BY 1

Legend SQL:

SELECT p.name FROM func('e2e::tds_persons') p WHERE EXISTS (SELECT 1 FROM func('e2e::tds_orders') o WHERE o.person_id = p.id AND o.amount > salary / 1000) ORDER BY 1

Error:

Unsupported: exists only supported on relation inputs

subquery_exists_correlated_unqualified_outer_dept_id