papera 0.2.0

A SQL compatibility layer that transpiles Trino and Redshift SQL to DuckDB SQL
Documentation 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106

use sqlparser::ast::{
    Expr, Ident, Join, JoinConstraint, JoinOperator, Select, TableAlias, TableAliasColumnDef,
    TableFactor,
};

use crate::Result;
use crate::dialect::SourceDialect;

/// Transform Hive-style LATERAL VIEW clauses to CROSS JOIN UNNEST for DuckDB.
///
/// Hive: `SELECT ... FROM t LATERAL VIEW explode(arr) AS x`
/// DuckDB: `SELECT ... FROM t CROSS JOIN UNNEST(arr) AS __lv(x)`
///
/// This is mainly relevant for Trino queries that use Hive-compatible syntax.
pub fn rewrite_lateral_views(select: &mut Select, _dialect: SourceDialect) -> Result<()> {
    if select.lateral_views.is_empty() {
        return Ok(());
    }

    let lateral_views = std::mem::take(&mut select.lateral_views);

    for (i, lv) in lateral_views.into_iter().enumerate() {
        // Extract the array expression from the LATERAL VIEW.
        // The lateral_view field is the function call (e.g., explode(arr)).
        // For DuckDB, we need UNNEST(arr) instead.
        let array_expr = match &lv.lateral_view {
            Expr::Function(func) => {
                // Extract the first argument from explode/posexplode
                let func_name = func.name.to_string().to_lowercase();
                match func_name.as_str() {
                    "explode" | "posexplode" | "explode_outer" | "posexplode_outer" => {
                        // Extract first arg
                        extract_first_arg(func)
                    }
                    _ => {
                        // Unknown function — pass through the whole expression
                        Some(lv.lateral_view.clone())
                    }
                }
            }
            other => Some(other.clone()),
        };

        let array_expr = array_expr.unwrap_or(lv.lateral_view.clone());

        // Build alias from lateral_col_alias
        let alias_name = if lv.lateral_view_name.0.is_empty() {
            Ident::new(format!("__lv{i}"))
        } else {
            lv.lateral_view_name
                .0
                .last()
                .unwrap()
                .as_ident()
                .cloned()
                .unwrap_or_else(|| Ident::new(format!("__lv{i}")))
        };

        let col_aliases = lv.lateral_col_alias;

        let unnest = TableFactor::UNNEST {
            alias: Some(TableAlias {
                name: alias_name,
                columns: col_aliases
                    .into_iter()
                    .map(|c| TableAliasColumnDef {
                        name: c,
                        data_type: None,
                    })
                    .collect(),
                explicit: true,
            }),
            array_exprs: vec![array_expr],
            with_offset: false,
            with_offset_alias: None,
            with_ordinality: false,
        };

        let join = Join {
            relation: unnest,
            global: false,
            join_operator: JoinOperator::CrossJoin(JoinConstraint::None),
        };

        // Add the join to the first FROM item
        if let Some(first_from) = select.from.first_mut() {
            first_from.joins.push(join);
        }
    }

    Ok(())
}

fn extract_first_arg(func: &sqlparser::ast::Function) -> Option<Expr> {
    match &func.args {
        sqlparser::ast::FunctionArguments::List(list) => {
            list.args.first().and_then(|arg| match arg {
                sqlparser::ast::FunctionArg::Unnamed(sqlparser::ast::FunctionArgExpr::Expr(e)) => {
                    Some(e.clone())
                }
                _ => None,
            })
        }
        _ => None,
    }
}