Skip to content

Instantly share code, notes, and snippets.

@45deg

45deg/duckdb_raytrace.sql

Created July 31, 2026 17:03
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save 45deg/43c39109ae2e87ff3666f952ab27dcd3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save 45deg/43c39109ae2e87ff3666f952ab27dcd3 to your computer and use it in GitHub Desktop.
Download ZIP
Pure DuckDB SQL ray tracer
Raw
duckdb_raytrace.sql
-- Pure DuckDB SQL ray tracer
--
-- Run from this directory:
-- duckdb < render.sql
--
-- The output is an ASCII PPM image: render.ppm.
-- On the test machine, the recursive workload stopped scaling after 4 threads.
SET threads = 4;
-- Rendering parameters. 400 x 400, 50 samples and 8 bounces match the article.
CREATE OR REPLACE TEMP TABLE render_settings AS
SELECT
400::BIGINT AS image_width,
400::BIGINT AS image_height,
50::BIGINT AS samples_per_pixel,
8::INTEGER AS max_bounces,
1e-6::DOUBLE AS hit_epsilon,
1e-4::FLOAT AS origin_bias;
-- Deterministic pseudo-random values. A single ray_id is enough to identify a
-- pixel/sample pair, reducing the USING KEY hash from three columns to one.
CREATE OR REPLACE TEMP MACRO rand01(ray_id, bounce, axis) AS
(hash(ray_id, bounce, axis)::DOUBLE / 18446744073709551616.0);
CREATE OR REPLACE TEMP MACRO rand_signed(ray_id, bounce, axis) AS
(2.0 * rand01(ray_id, bounce, axis) - 1.0)::FLOAT;
CREATE OR REPLACE TEMP MACRO channel255(value) AS
round(255.0 * sqrt(greatest(0.0, least(1.0, value))))::INTEGER;
-- Keep hot scene data in scalar FLOAT columns. Values used by every hit are
-- precomputed once. material_id: 0 = diffuse, 1 = metal, 2 = glass.
CREATE OR REPLACE TEMP TABLE scene AS
SELECT
object_id,
center_x::FLOAT AS center_x,
center_y::FLOAT AS center_y,
center_z::FLOAT AS center_z,
radius::FLOAT AS radius,
(radius::DOUBLE * radius::DOUBLE) AS radius_squared,
(1.0 / radius)::FLOAT AS inverse_radius,
albedo_r::FLOAT AS albedo_r,
albedo_g::FLOAT AS albedo_g,
albedo_b::FLOAT AS albedo_b,
(
CASE material
WHEN 'metal' THEN 1
WHEN 'glass' THEN 2
ELSE 0
END
)::UTINYINT AS material_id,
refraction_index::FLOAT AS refraction_index
FROM (
VALUES
(0, 0.0, 0.0, -1.0, 0.5, 0.8, 0.5, 0.2, 'diffuse', 1.0),
(1, 1.0, 0.0, -1.0, 0.5, 0.8, 0.7, 0.9, 'metal', 1.0),
(2, -1.0, 0.0, -1.0, 0.5, 1.0, 1.0, 1.0, 'glass', 1.5),
(3, 0.4, -0.4, -0.5, 0.1, 0.0, 1.0, 0.5, 'diffuse', 1.0),
(4, -0.4, -0.4, -0.5, 0.1, 1.0, 1.0, 1.0, 'metal', 1.0),
(5, 0.0, -100.5, -1.0, 100.0, 0.5, 0.5, 0.2, 'diffuse', 1.0)
) AS objects(
object_id,
center_x,
center_y,
center_z,
radius,
albedo_r,
albedo_g,
albedo_b,
material,
refraction_index
);
CREATE OR REPLACE TEMP TABLE rendered_pixels AS
WITH RECURSIVE
initial_rays AS (
SELECT
ray_id,
0::INTEGER AS bounce,
0.0::FLOAT AS origin_x,
0.0::FLOAT AS origin_y,
0.0::FLOAT AS origin_z,
(
2.0 * (
coordinates.pixel_x
+ rand_signed(ray_id, 0, 3)
) / settings.image_width - 1.0
)::FLOAT AS direction_x,
(
1.0 - 2.0 * (
coordinates.pixel_y
+ rand_signed(ray_id, 0, 4)
) / settings.image_height
)::FLOAT AS direction_y,
(-1.0)::FLOAT AS direction_z,
1.0::FLOAT AS attenuation_r,
1.0::FLOAT AS attenuation_g,
1.0::FLOAT AS attenuation_b,
false AS done,
NULL::FLOAT AS final_r,
NULL::FLOAT AS final_g,
NULL::FLOAT AS final_b
FROM render_settings AS settings
CROSS JOIN range(
settings.image_width
* settings.image_height
* settings.samples_per_pixel
) AS ids(ray_id)
CROSS JOIN LATERAL (
SELECT
(
(ray_id // settings.samples_per_pixel)
% settings.image_width
)::BIGINT AS pixel_x,
(
(ray_id // settings.samples_per_pixel)
// settings.image_width
)::BIGINT AS pixel_y
) AS coordinates
),
-- USING KEY keeps only the latest state for each ray. All hot vector values are
-- scalar FLOAT columns to reduce state width and STRUCT packing/extraction.
trace(
ray_id,
bounce,
origin_x,
origin_y,
origin_z,
direction_x,
direction_y,
direction_z,
attenuation_r,
attenuation_g,
attenuation_b,
done,
final_r,
final_g,
final_b
)
USING KEY (ray_id) AS (
SELECT * FROM initial_rays
UNION
SELECT
step.ray_id,
step.bounce + 1,
(
CASE WHEN step.hit_t IS NULL
THEN step.origin_x
ELSE step.hit_x
+ (
CASE
WHEN step.material_id = 2
AND NOT step.glass_reflects
THEN -step.face_normal_x
ELSE step.face_normal_x
END
) * step.origin_bias
END
)::FLOAT AS origin_x,
(
CASE WHEN step.hit_t IS NULL
THEN step.origin_y
ELSE step.hit_y
+ (
CASE
WHEN step.material_id = 2
AND NOT step.glass_reflects
THEN -step.face_normal_y
ELSE step.face_normal_y
END
) * step.origin_bias
END
)::FLOAT AS origin_y,
(
CASE WHEN step.hit_t IS NULL
THEN step.origin_z
ELSE step.hit_z
+ (
CASE
WHEN step.material_id = 2
AND NOT step.glass_reflects
THEN -step.face_normal_z
ELSE step.face_normal_z
END
) * step.origin_bias
END
)::FLOAT AS origin_z,
(
CASE
WHEN step.hit_t IS NULL THEN step.direction_x
WHEN step.material_id = 0 THEN
step.face_normal_x
+ step.random_x * step.random_inverse_length
WHEN step.material_id = 1 OR step.glass_reflects THEN
step.unit_direction_x
- 2.0
* step.direction_dot_face_normal
* step.face_normal_x
ELSE
step.refract_perpendicular_x
+ step.refract_parallel_scale * step.face_normal_x
END
)::FLOAT AS direction_x,
(
CASE
WHEN step.hit_t IS NULL THEN step.direction_y
WHEN step.material_id = 0 THEN
step.face_normal_y
+ step.random_y * step.random_inverse_length
WHEN step.material_id = 1 OR step.glass_reflects THEN
step.unit_direction_y
- 2.0
* step.direction_dot_face_normal
* step.face_normal_y
ELSE
step.refract_perpendicular_y
+ step.refract_parallel_scale * step.face_normal_y
END
)::FLOAT AS direction_y,
(
CASE
WHEN step.hit_t IS NULL THEN step.direction_z
WHEN step.material_id = 0 THEN
step.face_normal_z
+ step.random_z * step.random_inverse_length
WHEN step.material_id = 1 OR step.glass_reflects THEN
step.unit_direction_z
- 2.0
* step.direction_dot_face_normal
* step.face_normal_z
ELSE
step.refract_perpendicular_z
+ step.refract_parallel_scale * step.face_normal_z
END
)::FLOAT AS direction_z,
(
CASE WHEN step.hit_t IS NULL
THEN step.attenuation_r
ELSE step.attenuation_r * step.albedo_r
END
)::FLOAT AS attenuation_r,
(
CASE WHEN step.hit_t IS NULL
THEN step.attenuation_g
ELSE step.attenuation_g * step.albedo_g
END
)::FLOAT AS attenuation_g,
(
CASE WHEN step.hit_t IS NULL
THEN step.attenuation_b
ELSE step.attenuation_b * step.albedo_b
END
)::FLOAT AS attenuation_b,
step.hit_t IS NULL AS done,
(
CASE WHEN step.hit_t IS NULL
THEN (1.0 - step.sky_t / 2.0) * step.attenuation_r
ELSE NULL
END
)::FLOAT AS final_r,
(
CASE WHEN step.hit_t IS NULL
THEN (1.0 - step.sky_t * 0.3) * step.attenuation_g
ELSE NULL
END
)::FLOAT AS final_g,
(
CASE WHEN step.hit_t IS NULL
THEN step.attenuation_b
ELSE NULL
END
)::FLOAT AS final_b
FROM (
SELECT
glass_vectors.*,
(
glass_vectors.material_id = 2
AND (
glass_vectors.refraction_ratio
* glass_vectors.sin_theta > 1.0
OR glass_vectors.reflectance
> glass_vectors.glass_random
)
) AS glass_reflects,
(
-sqrt(abs(
1.0
- glass_vectors.refract_perpendicular_x
* glass_vectors.refract_perpendicular_x
- glass_vectors.refract_perpendicular_y
* glass_vectors.refract_perpendicular_y
- glass_vectors.refract_perpendicular_z
* glass_vectors.refract_perpendicular_z
))
)::FLOAT AS refract_parallel_scale
FROM (
SELECT
optics.*,
(
optics.refraction_ratio
* (
optics.unit_direction_x
+ optics.cos_theta * optics.face_normal_x
)
)::FLOAT AS refract_perpendicular_x,
(
optics.refraction_ratio
* (
optics.unit_direction_y
+ optics.cos_theta * optics.face_normal_y
)
)::FLOAT AS refract_perpendicular_y,
(
optics.refraction_ratio
* (
optics.unit_direction_z
+ optics.cos_theta * optics.face_normal_z
)
)::FLOAT AS refract_perpendicular_z
FROM (
SELECT
surface.*,
sqrt(greatest(
0.0,
1.0 - surface.cos_theta * surface.cos_theta
))::FLOAT AS sin_theta,
(
(
(1.0 - surface.refraction_index)
/ (1.0 + surface.refraction_index)
) * (
(1.0 - surface.refraction_index)
/ (1.0 + surface.refraction_index)
)
+ (
1.0
- (
(1.0 - surface.refraction_index)
/ (1.0 + surface.refraction_index)
) * (
(1.0 - surface.refraction_index)
/ (1.0 + surface.refraction_index)
)
) * pow(1.0 - surface.cos_theta, 5)
)::FLOAT AS reflectance
FROM (
SELECT
base.*,
(
CASE
WHEN base.direction_dot_outward_normal <= 0.0
THEN base.normal_x
ELSE -base.normal_x
END
)::FLOAT AS face_normal_x,
(
CASE
WHEN base.direction_dot_outward_normal <= 0.0
THEN base.normal_y
ELSE -base.normal_y
END
)::FLOAT AS face_normal_y,
(
CASE
WHEN base.direction_dot_outward_normal <= 0.0
THEN base.normal_z
ELSE -base.normal_z
END
)::FLOAT AS face_normal_z,
(
-abs(base.direction_dot_outward_normal)
)::FLOAT AS direction_dot_face_normal,
least(
abs(base.direction_dot_outward_normal),
1.0
)::FLOAT AS cos_theta,
(
CASE
WHEN base.direction_dot_outward_normal <= 0.0
THEN 1.0 / base.refraction_index
ELSE base.refraction_index
END
)::FLOAT AS refraction_ratio
FROM (
SELECT
normalized.*,
(
normalized.unit_direction_x
* normalized.normal_x
+ normalized.unit_direction_y
* normalized.normal_y
+ normalized.unit_direction_z
* normalized.normal_z
)::FLOAT AS direction_dot_outward_normal,
(
CASE
WHEN normalized.hit_t IS NOT NULL
AND normalized.material_id = 0
THEN 1.0 / sqrt(
normalized.random_x
* normalized.random_x
+ normalized.random_y
* normalized.random_y
+ normalized.random_z
* normalized.random_z
)
ELSE 0.0
END
)::FLOAT AS random_inverse_length,
(
(
1.0
+ normalized.direction_y
* normalized.direction_inverse_length
) / 2.0
)::FLOAT AS sky_t,
(
CASE
WHEN normalized.material_id = 2
THEN rand01(
normalized.ray_id,
normalized.bounce + 1,
5
)
ELSE 0.0
END
)::FLOAT AS glass_random
FROM (
SELECT
randomized.*,
(
randomized.direction_x
* randomized.direction_inverse_length
)::FLOAT AS unit_direction_x,
(
randomized.direction_y
* randomized.direction_inverse_length
)::FLOAT AS unit_direction_y,
(
randomized.direction_z
* randomized.direction_inverse_length
)::FLOAT AS unit_direction_z
FROM (
SELECT
geometry.*,
(
1.0 / sqrt(
geometry.direction_x
* geometry.direction_x
+ geometry.direction_y
* geometry.direction_y
+ geometry.direction_z
* geometry.direction_z
)
)::FLOAT AS direction_inverse_length,
(
CASE
WHEN geometry.hit_t IS NOT NULL
AND geometry.material_id = 0
THEN rand_signed(
geometry.ray_id,
geometry.bounce + 1,
0
)
ELSE 0.0
END
)::FLOAT AS random_x,
(
CASE
WHEN geometry.hit_t IS NOT NULL
AND geometry.material_id = 0
THEN rand_signed(
geometry.ray_id,
geometry.bounce + 1,
1
)
ELSE 0.0
END
)::FLOAT AS random_y,
(
CASE
WHEN geometry.hit_t IS NOT NULL
AND geometry.material_id = 0
THEN rand_signed(
geometry.ray_id,
geometry.bounce + 1,
2
)
ELSE 0.0
END
)::FLOAT AS random_z
FROM (
SELECT
hits.*,
(
hits.origin_x
+ hits.hit_t * hits.direction_x
)::FLOAT AS hit_x,
(
hits.origin_y
+ hits.hit_t * hits.direction_y
)::FLOAT AS hit_y,
(
hits.origin_z
+ hits.hit_t * hits.direction_z
)::FLOAT AS hit_z,
(
(
hits.origin_x
+ hits.hit_t * hits.direction_x
- hits.center_x
) * hits.inverse_radius
)::FLOAT AS normal_x,
(
(
hits.origin_y
+ hits.hit_t * hits.direction_y
- hits.center_y
) * hits.inverse_radius
)::FLOAT AS normal_y,
(
(
hits.origin_z
+ hits.hit_t * hits.direction_z
- hits.center_z
) * hits.inverse_radius
)::FLOAT AS normal_z
FROM (
SELECT
ray.*,
settings.origin_bias,
nearest.center_x,
nearest.center_y,
nearest.center_z,
nearest.inverse_radius,
nearest.albedo_r,
nearest.albedo_g,
nearest.albedo_b,
nearest.material_id,
nearest.refraction_index,
nearest.hit_t
FROM trace AS ray
CROSS JOIN render_settings AS settings
-- One correlated aggregate replaces ORDER BY t LIMIT 1.
-- arg_min selects every attribute from the nearest hit.
CROSS JOIN LATERAL (
SELECT
arg_min(center_x, t)
FILTER (WHERE t IS NOT NULL) AS center_x,
arg_min(center_y, t)
FILTER (WHERE t IS NOT NULL) AS center_y,
arg_min(center_z, t)
FILTER (WHERE t IS NOT NULL) AS center_z,
arg_min(inverse_radius, t)
FILTER (WHERE t IS NOT NULL)
AS inverse_radius,
arg_min(albedo_r, t)
FILTER (WHERE t IS NOT NULL) AS albedo_r,
arg_min(albedo_g, t)
FILTER (WHERE t IS NOT NULL) AS albedo_g,
arg_min(albedo_b, t)
FILTER (WHERE t IS NOT NULL) AS albedo_b,
arg_min(material_id, t)
FILTER (WHERE t IS NOT NULL) AS material_id,
arg_min(refraction_index, t)
FILTER (WHERE t IS NOT NULL)
AS refraction_index,
min(t) AS hit_t
FROM (
SELECT
roots.*,
CASE
WHEN roots.near_t
> settings.hit_epsilon
THEN roots.near_t
WHEN roots.far_t
> settings.hit_epsilon
THEN roots.far_t
ELSE NULL
END AS t
FROM (
SELECT
determinants.*,
(
(
-determinants.half_b
- sqrt(greatest(
determinants.discriminant,
0.0
))
) / determinants.a
)::DOUBLE AS near_t,
(
(
-determinants.half_b
+ sqrt(greatest(
determinants.discriminant,
0.0
))
) / determinants.a
)::DOUBLE AS far_t
FROM (
SELECT
coefficients.*,
(
coefficients.half_b
* coefficients.half_b
- coefficients.a
* coefficients.quadratic_c
)::DOUBLE AS discriminant
FROM (
SELECT
intersection_input.*,
(
intersection_input.direction_x_d
* intersection_input.direction_x_d
+ intersection_input.direction_y_d
* intersection_input.direction_y_d
+ intersection_input.direction_z_d
* intersection_input.direction_z_d
)::DOUBLE AS a,
(
(
intersection_input.origin_x_d
- intersection_input.center_x_d
) * intersection_input.direction_x_d
+ (
intersection_input.origin_y_d
- intersection_input.center_y_d
) * intersection_input.direction_y_d
+ (
intersection_input.origin_z_d
- intersection_input.center_z_d
) * intersection_input.direction_z_d
)::DOUBLE AS half_b,
(
(
intersection_input.origin_x_d
- intersection_input.center_x_d
) * (
intersection_input.origin_x_d
- intersection_input.center_x_d
)
+ (
intersection_input.origin_y_d
- intersection_input.center_y_d
) * (
intersection_input.origin_y_d
- intersection_input.center_y_d
)
+ (
intersection_input.origin_z_d
- intersection_input.center_z_d
) * (
intersection_input.origin_z_d
- intersection_input.center_z_d
)
- intersection_input.radius_squared
)::DOUBLE AS quadratic_c
FROM (
SELECT
sphere.*,
ray.origin_x::DOUBLE
AS origin_x_d,
ray.origin_y::DOUBLE
AS origin_y_d,
ray.origin_z::DOUBLE
AS origin_z_d,
ray.direction_x::DOUBLE
AS direction_x_d,
ray.direction_y::DOUBLE
AS direction_y_d,
ray.direction_z::DOUBLE
AS direction_z_d,
sphere.center_x::DOUBLE
AS center_x_d,
sphere.center_y::DOUBLE
AS center_y_d,
sphere.center_z::DOUBLE
AS center_z_d
FROM scene AS sphere
) AS intersection_input
) AS coefficients
) AS determinants
WHERE determinants.discriminant > 0.0
) AS roots
) AS candidates
) AS nearest
WHERE NOT ray.done
AND ray.bounce < settings.max_bounces
) AS hits
) AS geometry
) AS randomized
) AS normalized
) AS base
) AS surface
) AS optics
) AS glass_vectors
) AS step
)
SELECT
(
(trace.ray_id // settings.samples_per_pixel)
% settings.image_width
)::BIGINT AS px,
(
(trace.ray_id // settings.samples_per_pixel)
// settings.image_width
)::BIGINT AS py,
channel255(avg(coalesce(trace.final_r, 0.0))) AS red,
channel255(avg(coalesce(trace.final_g, 0.0))) AS green,
channel255(avg(coalesce(trace.final_b, 0.0))) AS blue
FROM trace
CROSS JOIN render_settings AS settings
GROUP BY px, py;
COPY (
SELECT line
FROM (
SELECT
-1::BIGINT AS line_number,
format(
'P3 {} {} 255',
settings.image_width,
settings.image_height
) AS line
FROM render_settings AS settings
UNION ALL
SELECT
pixels.py * settings.image_width + pixels.px AS line_number,
format('{} {} {}', pixels.red, pixels.green, pixels.blue) AS line
FROM rendered_pixels AS pixels
CROSS JOIN render_settings AS settings
) AS ppm
ORDER BY line_number
) TO 'render.ppm' (FORMAT CSV, HEADER false, DELIMITER '|');
SELECT
format(
'wrote render.ppm ({} x {}, {} samples/pixel, {} bounces)',
settings.image_width,
settings.image_height,
settings.samples_per_pixel,
settings.max_bounces
) AS result
FROM render_settings AS settings;
@45deg

45deg commented Jul 31, 2026

Copy link
Copy Markdown
Author
render

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment