SQLite Forum

Bug report: CTE can report "circular reference" for an actual column count issue
Login

Bug report: CTE can report "circular reference" for an actual column count issue

(1) By mzm2021 on 2021-03-18 10:35:52 [source]

The CTE query below should report error "table ds0 has 10 values for 11 columns". It complains about "circular reference: ds0" instead on 3.35.2 (and possibly all 3.35 releases.)

Check the comments in ds0 and in the body for variations on the query to make SQLite behave correctly.

The problem was found on the latest 3.35.2. It does not affect version 3.34.1.

I used the stock SQLite shell (compiled from the amalgamation ZIP) using:

gcc shell.c sqlite3.c -lpthread -ldl -lm -g

My apologies for the not-so-minimal query. It is already a simplified version of the original CTE that went bad after upgrading to 3.35.x.

It seems that the complexity of the CTE is part of what triggers this bug.

WITH
	cst(rsx, rsy) AS  (
		SELECT 100, 100
	),

	cst2(minx, maxx, stepx, miny, maxy, stepy, qualitativex, qualitativey) AS (		
		SELECT NULL, NULL, NULL, NULL, NULL, NULL, 0, 0
	),

	ds0(m, n, x, y, x2, y2, title, size, mark, label, markmode) AS (
		-- Reports error "circular reference: ds0" instead of complaining about the column number "table ds0 has 10 values for 11 columns"
		SELECT 1, 2, 3, 4, 5, 6, 7 , 8, 9, 10

		-- Works fine with the 11th value
		-- SELECT 1, 2, 3, 4, 5, 6, 7 , 8, 9, 10, 11
	)
	,
	
	ds(m, n, x, y, x2, y2, title, size, mark, label, markmode) AS (
		SELECT m, n, x,
			y, x2,
			y2,
			title, size, mark, label, markmode
		FROM ds0
		WINDOW w AS (PARTITION BY m, x ORDER BY n)
	),

	d(m, n, x, y, x2, y2, labelx, labely, title, size, mark, label, markmode) AS (
		SELECT m, n, x, y,	x2, y2, x, y, title, size, mark, label, markmode
		FROM ds, cst2
	),

	ylabels(y, label) AS (
		SELECT y, MIN(labely) FROM d GROUP BY y
	),

	yaxis(maxy, miny, stepy , minstepy) AS (
		WITH
			xt0(minx, maxx) AS (
				SELECT  coalesce(miny, min(min(y2), min(y))),  coalesce(maxy, max(max(y2), max(y))) + qualitativey  FROM d, cst2
			),
			xt1(mx, mn) AS (SELECT maxx, minx FROM xt0),
			xt2(mx, mn, step) AS (SELECT mx, mn, floor(mx-mn)  FROM xt1),
			
			xt3(mx, mn, ms) AS (
				SELECT mx, mn, first_value(rs) OVER (order by x desc) AS ms FROM (SELECT mx, mn, step, f,(mx-mn) as rng, 1.0*step/f as rs, 1.0*(mx-mn)/(step/f) AS x FROM xt2, (SELECT 1 AS f UNION ALL SELECT 2 UNION ALL SELECT 4 UNION ALL SELECT 5)) AS src WHERE x < 10 limit 1),
			xt4(minstepy) AS (
				SELECT MIN(abs(y2-y)) FROM d WHERE y2 != y
			)
		SELECT ceil(mx/ms)*ms, floor(mn/ms)*ms, coalesce(stepy, ms ), coalesce(minstepy, ms, stepy)  FROM xt3, cst2,xt4
	),

	distinct_mark_n_m(mark, ze, zem, title) AS (
		SELECT DISTINCT mark, n AS ze, m AS zem, title FROM ds0
	),

	facet0(m, mi, title, radial) AS (
		SELECT md, row_number() OVER () - 1, title, 'radial' IN (SELECT mark FROM distinct_mark_n_m WHERE zem = md)
		FROM (SELECT DISTINCT zem AS md, title AS title FROM distinct_mark_n_m ORDER BY 2, 1)
	),

	facet(m, mi, xorigin, yorigin, title, radial) AS (
		SELECT m, mi,
			rsx * 1.2 * IFNULL(CASE WHEN (
				0
			) > 0 THEN mi / (
				0
			) ELSE mi % (
				2
			)  END, mi),
			rsy  * 1.2 * IFNULL(CASE WHEN (
				2
			) > 0 THEN mi / (
				2
			) ELSE mi / (
				0
			)  END, 0),
			title, radial FROM facet0, cst
	),

	radygrid(m, mi, tty, wty, ttx, ttx2, xorigin, yorigin) AS (
		SELECT m, mi,  rsy / 2 / ((maxy-miny)/stepy) * (value-1) AS tty, coalesce(NULL, miny + stepy * (value-1)) AS wty, xorigin, xorigin+rsx, xorigin + rsx / 2, yorigin + rsy / 2  FROM generate_series(1), yaxis, cst, facet LEFT JOIN ylabels ON ylabels.y = (miny + (value-1) * stepy) WHERE radial AND stop = 1+1.0*(maxy-miny)/stepy
	),
	
	ypos(m, mi, pcx, pcy, radial) AS (
		SELECT m, mi, xorigin, yorigin + CASE
			WHEN 0 BETWEEN miny AND maxy THEN
				rsy - (0 - miny) * rsy / (maxy-miny)
			WHEN 0 >= maxy THEN 0
			ELSE  rsy
		END, radial FROM yaxis, cst, facet WHERE NOT radial
		UNION ALL
		SELECT m, mi, xorigin + rsx / 2, yorigin + (CASE
			WHEN 0 BETWEEN miny AND maxy THEN
				rsy - (0 - miny) * rsy / 2 / (maxy-miny)
			WHEN 0 >= maxy THEN 0
			ELSE  rsy
		END ) / 2, radial FROM yaxis, cst, facet WHERE radial
	)

-- With any reference to radygrid or ypos, error "circular reference: ds0" is reported instead of "table ds0 has 10 values for 11 columns"
SELECT * FROM ypos, radygrid

-- Correct error "table ds0 has 10 values for 11 columns" is reported when any table other than ypos or radygrid is used
-- SELECT * FROM facet0

Thanks you.

(2) By mzm2021 on 2021-07-06 15:45:28 in reply to 1 [link] [source]

I confirm this bug is no more present in 3.36.

After inspecting the timeline, it seems that it has been corrected in check-in 99812236 without referencing this report.

Anyway, thanks dan for the fix.