16555326021

Committed 27 Jul 2025 08:45PM UTC coverage: 89.626% (-0.08%) from 89.702%

Build # 16555326021

Build Type

Pull #147

github

Committed by

web-flow

Commit Message

Merge 72b44c129 into 56fc28d96

Pull Request Pull Request #147: fix join select issue

Run Details

15 of 17 new or added lines in 2 files covered. (88.24%)

38 existing lines in 4 files now uncovered.

1270 of 1417 relevant lines covered (89.63%)

213.73 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

92.59

/src/structs.jl

mutable struct CTE
    name::String
    select::String
    from::String
    where::String
    groupBy::String
    having::String
    function CTE(;name::String="", select::String="", from::String="", where::String="", groupBy::String="", having::String="")
        new(name, select, from, where, groupBy, having)
    end
end

mutable struct SQLQuery
    select::String
    from::String
    where::String
    groupBy::String
    orderBy::String
    having::String
    window_order::String
    windowFrame::String 
    is_aggregated::Bool
    post_aggregation::Bool
    post_join::Bool
    metadata::DataFrame
    distinct::Bool
    db::Any 
    ctes::Vector{CTE}
    cte_count::Int
    athena_params::Any    
    limit::String
    ch_settings::String
    join_count::Int
    post_unnest::Bool
    post_mutate::Bool
    post_count::Bool
    groupBy_exprs::Bool
    function SQLQuery(; select::String="", from::String="", where::String="", groupBy::String="", orderBy::String="", having::String="", 
        window_order::String="", windowFrame::String="", is_aggregated::Bool=false, post_aggregation::Bool=false, post_join::Bool=false, metadata::DataFrame=DataFrame(), 
        distinct::Bool=false, db::Any=nothing, ctes::Vector{CTE}=Vector{CTE}(), cte_count::Int=0, athena_params::Any=nothing, limit::String="", 
        ch_settings::String="", join_count::Int = 0, post_unnest::Bool = false, post_mutate::Bool = false,  post_count::Bool = false, groupBy_exprs::Bool = false)
        new(select, from, where, groupBy, orderBy, having, window_order, windowFrame, is_aggregated, 
        post_aggregation, post_join, metadata, distinct, db, ctes, cte_count, athena_params, limit, ch_settings, join_count, post_unnest, post_mutate, post_count, groupBy_exprs)
    end
end

function from_query(query::TidierDB.SQLQuery)
    function copy(cte::TidierDB.CTE)
        return TidierDB.CTE(name=cte.name, select=cte.select, from=cte.from, where=cte.where, groupBy=cte.groupBy, having=cte.having)
    end
    
    new_query = TidierDB.SQLQuery(
        select=query.select,
        from=query.from,
        where=query.where,
        groupBy=query.groupBy,
        orderBy=query.orderBy,
        having=query.having,
        window_order=query.window_order,
        windowFrame=query.windowFrame,
        is_aggregated=query.is_aggregated,
        post_aggregation=query.post_aggregation,
        post_join=query.post_join,

        metadata=deepcopy(query.metadata), 
        distinct=query.distinct,
        db=query.db,
        ctes=[copy(cte) for cte in query.ctes],  
        cte_count=query.cte_count,
        athena_params = query.athena_params,
        limit = query.limit,
        ch_settings = query.ch_settings,
        join_count = query.join_count,
        post_unnest = query.post_unnest,
        post_mutate = query.post_mutate,
        post_count = query.post_count,
        groupBy_exprs = query.groupBy_exprs
    )
    return new_query
end

t(table) = from_query(table)

function up_cte_name(sq, cte_name)
    if cte_name != "cte_1"
        if all(x -> x >= 1, sq.metadata.current_selxn)
            sq.metadata.table_name .= cte_name
        end
    end
end

function build_cte!(sq)
    # or if you want to also check sq.post_join, add it here
    cte_name = "cte_" * string(sq.cte_count + 1)

        sq.post_aggregation = false

        # Use provided select expression, or "*" if none was specified.
        select_expressions = !isempty(sq.select) ? [sq.select] : ["*"]

        # Build the SQL for the new CTE.
        cte_sql = " " * join(select_expressions, ", ") * " FROM " * sq.from
        if sq.is_aggregated && !isempty(sq.groupBy)
            cte_sql *= " " * sq.groupBy
            sq.groupBy = ""
        end
        if !isempty(sq.where)
            cte_sql *= " WHERE " * sq.where
            sq.where = ""
        end
        if !isempty(sq.having)
            cte_sql *= "  " * sq.having
            sq.having = ""
        end

        # Define the new CTE name.
        cte_name = "cte_" * string(sq.cte_count + 1)

        # Create the new CTE and update sq.
        new_cte = CTE(name=cte_name, select=cte_sql)
        up_cte_name(sq, cte_name)
        push!(sq.ctes, new_cte)
        sq.cte_count += 1
        sq.from = cte_name
        sq.post_count = false

    return sq
end

function finalize_ctes(ctes::Vector{CTE})
    if isempty(ctes)
        return ""
    end
  
    cte_strings = String[]
    for cte in ctes
     
        if startswith(cte.name, "jcte_")
            cte.select = replace(cte.select, r"FROM cte_" => "FROM jcte_")
            cte.from = replace(cte.from, r"^cte_" => "jcte_")
        elseif startswith(cte.name, r"j\d+cte")
            match_result = match(r"(j\d+cte_)", cte.name)
            if match_result !== nothing
                replacement = match_result.match
                cte.select = replace(cte.select, r"FROM cte_" => "FROM $replacement")
                cte.from = replace(cte.from, r"^cte_" => replacement)
            end
        end
    
        cte_str = string(
            cte.name, " AS (SELECT ", cte.select, 
            occursin(" FROM ", cte.select) ? "" : " FROM " * cte.from, 
            (!isempty(cte.where) ? " WHERE " * cte.where : ""), 
            (!isempty(cte.groupBy) ? " GROUP BY " * cte.groupBy : ""), 
            (!isempty(cte.having) ? " HAVING " * cte.having : ""), 
            ")"
        )
        push!(cte_strings, cte_str)
    end

    return "WITH " * join(cte_strings, ", ") * " "
end

function finalize_query(sqlquery::SQLQuery)
    cte_part = finalize_ctes(sqlquery.ctes)

    select_already_present = occursin(r"^SELECT\s+", uppercase(sqlquery.select))
    select_part = if sqlquery.distinct && !select_already_present
        "SELECT DISTINCT " * (isempty(sqlquery.select) ? "*" : sqlquery.select)
    elseif !select_already_present
        "SELECT " * (isempty(sqlquery.select) ? "*" : sqlquery.select)
    else
        sqlquery.select
    end

    # Initialize query_parts with the CTE part
    query_parts = [cte_part]

    # Since sq.from has been updated to reference a CTE, adjust the FROM clause accordingly
    if !isempty(sqlquery.ctes)
        # If CTEs are defined, FROM clause should reference the latest CTE (already updated in sq.from)
        push!(query_parts, select_part, "FROM " * sqlquery.from)
    else
        # If no CTEs are defined, use the original table name in sq.from
        push!(query_parts, select_part, "FROM " * sqlquery.from)
    end

    # Append other clauses if present
    if !isempty(sqlquery.where) push!(query_parts, " " * sqlquery.where) end
    if !isempty(sqlquery.groupBy) push!(query_parts, "" * sqlquery.groupBy) end
    if !isempty(sqlquery.having) push!(query_parts, " " * sqlquery.having) end
    if !isempty(sqlquery.orderBy) push!(query_parts, " " * sqlquery.orderBy) end
    if !isempty(sqlquery.limit) push!(query_parts, " LIMIT " * sqlquery.limit) end
    
    complete_query = join(filter(!isempty, query_parts), " ")

    if !isempty(sqlquery.ch_settings) && current_sql_mode[] == clickhouse()
        complete_query = complete_query * " \n " * string(sqlquery.ch_settings)
    end
    complete_query = replace(complete_query, "&&" => " AND ", "||" => " OR ",
     "FROM )" => ")" ,  "SELECT SELECT " => "SELECT ", "SELECT  SELECT " => "SELECT ", "DISTINCT SELECT " => "DISTINCT ", 
     "SELECT SELECT SELECT " => "SELECT ", "PARTITION BY GROUP BY" => "PARTITION BY", "GROUP BY GROUP BY" => "GROUP BY", "HAVING HAVING" => "HAVING", 
     r"var\"(.*?)\"" => s"\1", r"\"\\\$" => "\"\$",  "WHERE \"" => "WHERE ", "WHERE \"NOT" => "WHERE NOT", "%')\"" =>"%\")", "NULL)\"" => "NULL)",
    "NULL))\"" => "NULL))", r"(?i)INTERVAL(\d+)([a-zA-Z]+)" => s"INTERVAL \1 \2", "SELECT SUMMARIZE " =>  "SUMMARIZE ", "\"(__(" => "(", ")__(\"" => ")"
     , "***\"" => " ", "\"***" => " ", "***" => " ", "WHERE WHERE " => "WHERE ", "WHERE  WHERE " => "WHERE ", "(__(" => "", ")__(" => "", "SELECT , CONCAT_WS" => "SELECT CONCAT_WS")
     complete_query = replace(complete_query, ", AS " => " AS ", "OR  \"" => "OR ")
    if current_sql_mode[] == postgres() || current_sql_mode[] == duckdb() || current_sql_mode[] == mysql() || current_sql_mode[] == mssql() || current_sql_mode[] == clickhouse() || current_sql_mode[] == athena() || current_sql_mode[] == gbq() || current_sql_mode[] == oracle()  || current_sql_mode[] == snowflake() || current_sql_mode[] == databricks()
        complete_query = replace(complete_query, "\"" => "'", "==" => "=")
    end
        complete_query = current_sql_mode[] == postgres() ?  replace(complete_query, r"INTERVAL (\d+) ([a-zA-Z]+)" => s"INTERVAL '\1 \2'") : complete_query
        complete_query = replace(complete_query, r"(?s)(\(SELECT\s+UNNEST.*?FROM\s+.*?\))" => s -> string(s) * ")",  "IS NULL) )'  AND" => "IS NULL) )  AND")
        
    return complete_query
end



1	mutable struct CTE
2	name::String
3	select::String
4	from::String
5	where::String
6	groupBy::String
7	having::String
8	function CTE(;name::String="", select::String="", from::String="", where::String="", groupBy::String="", having::String="")	1,752✔
9	new(name, select, from, where, groupBy, having)	876✔
10	end
11	end
12
13	mutable struct SQLQuery
14	select::String
15	from::String
16	where::String
17	groupBy::String
18	orderBy::String
19	having::String
20	window_order::String
21	windowFrame::String
22	is_aggregated::Bool
23	post_aggregation::Bool
24	post_join::Bool
25	metadata::DataFrame
26	distinct::Bool
27	db::Any
28	ctes::Vector{CTE}
29	cte_count::Int
30	athena_params::Any
31	limit::String
32	ch_settings::String
33	join_count::Int
34	post_unnest::Bool
35	post_mutate::Bool
36	post_count::Bool
37	groupBy_exprs::Bool
38	function SQLQuery(; select::String="", from::String="", where::String="", groupBy::String="", orderBy::String="", having::String="",	2,260✔
39	window_order::String="", windowFrame::String="", is_aggregated::Bool=false, post_aggregation::Bool=false, post_join::Bool=false, metadata::DataFrame=DataFrame(),
40	distinct::Bool=false, db::Any=nothing, ctes::Vector{CTE}=Vector{CTE}(), cte_count::Int=0, athena_params::Any=nothing, limit::String="",
41	ch_settings::String="", join_count::Int = 0, post_unnest::Bool = false, post_mutate::Bool = false, post_count::Bool = false, groupBy_exprs::Bool = false)
42	new(select, from, where, groupBy, orderBy, having, window_order, windowFrame, is_aggregated,	1,130✔
43	post_aggregation, post_join, metadata, distinct, db, ctes, cte_count, athena_params, limit, ch_settings, join_count, post_unnest, post_mutate, post_count, groupBy_exprs)
44	end
45	end
46
47	function from_query(query::TidierDB.SQLQuery)	918✔
48	function copy(cte::TidierDB.CTE)	1,320✔
49	return TidierDB.CTE(name=cte.name, select=cte.select, from=cte.from, where=cte.where, groupBy=cte.groupBy, having=cte.having)	402✔
50	end
51
52	new_query = TidierDB.SQLQuery(	918✔
53	select=query.select,
54	from=query.from,
55	where=query.where,
56	groupBy=query.groupBy,
57	orderBy=query.orderBy,
58	having=query.having,
59	window_order=query.window_order,
60	windowFrame=query.windowFrame,
61	is_aggregated=query.is_aggregated,
62	post_aggregation=query.post_aggregation,
63	post_join=query.post_join,
64
65	metadata=deepcopy(query.metadata),
66	distinct=query.distinct,
67	db=query.db,
68	ctes=[copy(cte) for cte in query.ctes],
69	cte_count=query.cte_count,
70	athena_params = query.athena_params,
71	limit = query.limit,
72	ch_settings = query.ch_settings,
73	join_count = query.join_count,
74	post_unnest = query.post_unnest,
75	post_mutate = query.post_mutate,
76	post_count = query.post_count,
77	groupBy_exprs = query.groupBy_exprs
78	)
79	return new_query	918✔
80	end
81
82	t(table) = from_query(table)	918✔
83
84	function up_cte_name(sq, cte_name)	412✔
85	if cte_name != "cte_1"	412✔
86	if all(x -> x >= 1, sq.metadata.current_selxn)	648✔
87	sq.metadata.table_name .= cte_name	84✔
88	end
89	end
90	end
91
92	function build_cte!(sq)	100✔
93	# or if you want to also check sq.post_join, add it here
94	cte_name = "cte_" * string(sq.cte_count + 1)	100✔
95
96	sq.post_aggregation = false	100✔
97
98	# Use provided select expression, or "*" if none was specified.
99	select_expressions = !isempty(sq.select) ? [sq.select] : ["*"]	182✔
100
101	# Build the SQL for the new CTE.
102	cte_sql = " " * join(select_expressions, ", ") * " FROM " * sq.from	150✔
103	if sq.is_aggregated && !isempty(sq.groupBy)	100✔
104	cte_sql = " " sq.groupBy	2✔
105	sq.groupBy = ""	2✔
106	end
107	if !isempty(sq.where)	100✔
108	cte_sql = " WHERE " sq.where	72✔
109	sq.where = ""	72✔
110	end
111	if !isempty(sq.having)	100✔
UNCOV 112	cte_sql = " " sq.having	×
UNCOV 113	sq.having = ""	×
114	end
115
116	# Define the new CTE name.
117	cte_name = "cte_" * string(sq.cte_count + 1)	100✔
118
119	# Create the new CTE and update sq.
120	new_cte = CTE(name=cte_name, select=cte_sql)	100✔
121	up_cte_name(sq, cte_name)	100✔
122	push!(sq.ctes, new_cte)	100✔
123	sq.cte_count += 1	100✔
124	sq.from = cte_name	100✔
125	sq.post_count = false	100✔
126
127	return sq	100✔
128	end
129
130	function finalize_ctes(ctes::Vector{CTE})	384✔
131	if isempty(ctes)	384✔
132	return ""	102✔
133	end
134
135	cte_strings = String[]	282✔
136	for cte in ctes	282✔
137
138	if startswith(cte.name, "jcte_")	711✔
139	cte.select = replace(cte.select, r"FROM cte_" => "FROM jcte_")	×
140	cte.from = replace(cte.from, r"^cte_" => "jcte_")	×
141	elseif startswith(cte.name, r"j\d+cte")	474✔
142	match_result = match(r"(j\d+cte_)", cte.name)	76✔
143	if match_result !== nothing	76✔
144	replacement = match_result.match	76✔
145	cte.select = replace(cte.select, r"FROM cte_" => "FROM $replacement")	76✔
146	cte.from = replace(cte.from, r"^cte_" => replacement)	76✔
147	end
148	end
149
150	cte_str = string(	520✔
151	cte.name, " AS (SELECT ", cte.select,
152	occursin(" FROM ", cte.select) ? "" : " FROM " * cte.from,
153	(!isempty(cte.where) ? " WHERE " * cte.where : ""),
154	(!isempty(cte.groupBy) ? " GROUP BY " * cte.groupBy : ""),
155	(!isempty(cte.having) ? " HAVING " * cte.having : ""),
156	")"
157	)
158	push!(cte_strings, cte_str)	474✔
159	end	474✔
160
161	return "WITH " * join(cte_strings, ", ") * " "	282✔
162	end
163
164	function finalize_query(sqlquery::SQLQuery)	384✔
165	cte_part = finalize_ctes(sqlquery.ctes)	384✔
166
167	select_already_present = occursin(r"^SELECT\s+", uppercase(sqlquery.select))	384✔
168	select_part = if sqlquery.distinct && !select_already_present	384✔
169	"SELECT DISTINCT " * (isempty(sqlquery.select) ? "*" : sqlquery.select)	×
170	elseif !select_already_present	384✔
171	"SELECT " * (isempty(sqlquery.select) ? "*" : sqlquery.select)	522✔
172	else
173	sqlquery.select	850✔
174	end
175
176	# Initialize query_parts with the CTE part
177	query_parts = [cte_part]	384✔
178
179	# Since sq.from has been updated to reference a CTE, adjust the FROM clause accordingly
180	if !isempty(sqlquery.ctes)	384✔
181	# If CTEs are defined, FROM clause should reference the latest CTE (already updated in sq.from)
182	push!(query_parts, select_part, "FROM " * sqlquery.from)	282✔
183	else
184	# If no CTEs are defined, use the original table name in sq.from
185	push!(query_parts, select_part, "FROM " * sqlquery.from)	102✔
186	end
187
188	# Append other clauses if present
189	if !isempty(sqlquery.where) push!(query_parts, " " * sqlquery.where) end	420✔
190	if !isempty(sqlquery.groupBy) push!(query_parts, "" * sqlquery.groupBy) end	416✔
191	if !isempty(sqlquery.having) push!(query_parts, " " * sqlquery.having) end	384✔
192	if !isempty(sqlquery.orderBy) push!(query_parts, " " * sqlquery.orderBy) end	498✔
193	if !isempty(sqlquery.limit) push!(query_parts, " LIMIT " * sqlquery.limit) end	388✔
194
195	complete_query = join(filter(!isempty, query_parts), " ")	384✔
196
197	if !isempty(sqlquery.ch_settings) && current_sql_mode[] == clickhouse()	384✔
198	complete_query = complete_query * " \n " * string(sqlquery.ch_settings)	×
199	end
200	complete_query = replace(complete_query, "&&" => " AND ", "\|\|" => " OR ",	384✔
201	"FROM )" => ")" , "SELECT SELECT " => "SELECT ", "SELECT SELECT " => "SELECT ", "DISTINCT SELECT " => "DISTINCT ",
202	"SELECT SELECT SELECT " => "SELECT ", "PARTITION BY GROUP BY" => "PARTITION BY", "GROUP BY GROUP BY" => "GROUP BY", "HAVING HAVING" => "HAVING",
203	r"var\"(.*?)\"" => s"\1", r"\"\\\$" => "\"\$", "WHERE \"" => "WHERE ", "WHERE \"NOT" => "WHERE NOT", "%')\"" =>"%\")", "NULL)\"" => "NULL)",
204	"NULL))\"" => "NULL))", r"(?i)INTERVAL(\d+)([a-zA-Z]+)" => s"INTERVAL \1 \2", "SELECT SUMMARIZE " => "SUMMARIZE ", "\"(__(" => "(", ")__(\"" => ")"
205	, "*\"" => " ", "\"" => " ", "**" => " ", "WHERE WHERE " => "WHERE ", "WHERE WHERE " => "WHERE ", "(__(" => "", ")__(" => "", "SELECT , CONCAT_WS" => "SELECT CONCAT_WS")
206	complete_query = replace(complete_query, ", AS " => " AS ", "OR \"" => "OR ")	384✔
207	if current_sql_mode[] == postgres() \|\| current_sql_mode[] == duckdb() \|\| current_sql_mode[] == mysql() \|\| current_sql_mode[] == mssql() \|\| current_sql_mode[] == clickhouse() \|\| current_sql_mode[] == athena() \|\| current_sql_mode[] == gbq() \|\| current_sql_mode[] == oracle() \|\| current_sql_mode[] == snowflake() \|\| current_sql_mode[] == databricks()	768✔
208	complete_query = replace(complete_query, "\"" => "'", "==" => "=")	384✔
209	end
210	complete_query = current_sql_mode[] == postgres() ? replace(complete_query, r"INTERVAL (\d+) ([a-zA-Z]+)" => s"INTERVAL '\1 \2'") : complete_query	768✔
211	complete_query = replace(complete_query, r"(?s)(\(SELECT\s+UNNEST.?FROM\s+.?\))" => s -> string(s) * ")", "IS NULL) )' AND" => "IS NULL) ) AND")	384✔
212
213	return complete_query	384✔
214	end
215
216

TidierOrg / TidierDB.jl / 16555326021

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous