hannes
commented
3 weeks ago Hey, could you please share the exact steps to reproduce this?
Open yoonghm opened 4 weeks ago
hannes
commented
3 weeks ago Hey, could you please share the exact steps to reproduce this?
yoonghm
commented
3 weeks ago I could not share the data and views. Are there ways for me to trace the code for you?
Mause
commented
3 weeks ago If you can provide us with a sequence of commands and SQL queries to reproduce the issue, that would be best
yoonghm
commented
3 weeks ago I use the following SQL command to create or replace a view comp:
CREATE OR REPLACE VIEW comp AS
-- Latest semester for every module
WITH _a AS (
SELECT
module_id,
MAX(semester) AS max_semester
FROM nsrq966
GROUP BY module_id
),
_others AS (
SELECT DISTINCT
n.semester,
n.module_id,
n.catalog,
n.class,
n.component,
n.pattern,
n.day,
n.start_time,
n.duration,
n.load,
(n.day = 'SAT') AS oal
FROM
nsrq966 n,
_a
WHERE
n.semester = _a.max_semester AND
n.module_id = _a.module_id AND
n.class != 'SSP' AND -- ignore summer school program (SSP)
-- Ingore INT and FYP which have many different durations
n.catalog NOT LIKE '%INT6' AND
n.catalog NOT LIKE '%INTO6' AND
n.catalog NOT LIKE '%INTY1' AND
n.catalog NOT LIKE '%INTY2' AND
n.catalog NOT IN ('41FYP', '43PDD', '44FYP', '93FYP')
),
-- Manually put INT module back
_int AS (
SELECT DISTINCT
n.semester,
n.module_id,
n.catalog,
'XX' AS class, -- Set fixed class
'PRA' AS component, -- Set fixed component
'' AS pattern, -- Set fixed pattern
'' AS day, -- Set fixed day
'08:00' AS start_time, -- Set fixed start_time
2.0 AS duration, -- Set fixed duration
2.0 AS load, -- Set fixed load
false AS oal -- Set oal to false
FROM
nsrq966 n,
_a
WHERE
n.semester = _a.max_semester AND
n.module_id = _a.module_id AND
(n.catalog LIKE '%INT6' OR
n.catalog LIKE '%INTO6' OR
n.catalog LIKE '%INTY1' OR
n.catalog LIKE '%INTY2')
),
-- Manually put FYP module back
_fyp AS (
SELECT DISTINCT
n.semester,
n.module_id,
n.catalog,
'XX' AS class, -- Set fixed class
'PRA' AS component, -- Set fixed component
'' AS pattern, -- Set fixed pattern
'' AS day, -- Set fixed day
'08:00' AS start_time, -- Set fixed start_time
1.0 AS duration, -- Set fixed duration
1.0 AS load, -- Set fixed load
false AS oal -- Set oal to false
FROM
nsrq966 n,
_a
WHERE
n.semester = _a.max_semester AND
n.module_id = _a.module_id AND
n.catalog in ('41FYP', '43PDD', '44FYP', '93FYP')
),
-- Combine _others, _int and _fyp
_combined AS (
SELECT * FROM _others UNION ALL
SELECT * FROM _int UNION ALL
SELECT * FROM _fyp
),
-- Append sequence to repeated component in the
-- same module_id, class
_numbered AS (
SELECT
*,
CASE
WHEN ROW_NUMBER() OVER (
PARTITION BY
module_id,
class,
component
ORDER BY duration DESC) = 1 THEN component
ELSE component || CAST(ROW_NUMBER() OVER (
PARTITION BY
module_id,
class,
component
ORDER BY duration DESC) AS VARCHAR)
END AS component_1
FROM _combined
)
-- Finally
SELECT DISTINCT
module_id,
catalog,
component_1 AS component,
pattern,
duration,
load,
oal
FROM
_numbered
ORDER BY
catalog,
class,
component,
load,
oal;If the SQL is executed via duckdb CLI, it would give me the beautified SQL code:
CREATE VIEW comp
AS
WITH _a
AS (SELECT module_id,
Max(semester) AS max_semester
FROM nsrq966
GROUP BY module_id),
_others
AS (SELECT DISTINCT n.semester,
n.module_id,
n."catalog",
n."class",
n.component,
n.pattern,
n."day",
n.start_time,
n.duration,
n."load",
( n."day" = 'SAT' ) AS oal
FROM nsrq966 AS n,
_a
WHERE ( ( n.semester = _a.max_semester )
AND ( n.module_id = _a.module_id )
AND ( n."class" != 'SSP' )
AND ( n."catalog" !~~ '%INT6' )
AND ( n."catalog" !~~ '%INTO6' )
AND ( n."catalog" !~~ '%INTY1' )
AND ( n."catalog" !~~ '%INTY2' )
AND ( n."catalog" NOT IN ( '41FYP', '43PDD', '44FYP',
'93FYP'
) ) )
),
_int
AS (SELECT DISTINCT n.semester,
n.module_id,
n."catalog",
'XX' AS "class",
'PRA' AS component,
'' AS pattern,
'' AS "day",
'08:00' AS start_time,
2.0 AS duration,
2.0 AS "load",
Cast('f' AS BOOLEAN) AS oal
FROM nsrq966 AS n,
_a
WHERE ( ( n.semester = _a.max_semester )
AND ( n.module_id = _a.module_id )
AND ( ( n."catalog" ~~ '%INT6' )
OR ( n."catalog" ~~ '%INTO6' )
OR ( n."catalog" ~~ '%INTY1' )
OR ( n."catalog" ~~ '%INTY2' ) ) )),
_fyp
AS (SELECT DISTINCT n.semester,
n.module_id,
n."catalog",
'XX' AS "class",
'PRA' AS component,
'' AS pattern,
'' AS "day",
'08:00' AS start_time,
1.0 AS duration,
1.0 AS "load",
Cast('f' AS BOOLEAN) AS oal
FROM nsrq966 AS n,
_a
WHERE ( ( n.semester = _a.max_semester )
AND ( n.module_id = _a.module_id )
AND ( n."catalog" IN ( '41FYP', '43PDD', '44FYP', '93FYP' )
)
)),
_combined
AS (((SELECT *
FROM _others)
UNION ALL
(SELECT *
FROM _int))
UNION ALL
(SELECT *
FROM _fyp)),
_numbered
AS (SELECT *,
CASE
WHEN (( Row_number()
over (
PARTITION BY module_id, "class", component
ORDER BY duration DESC) = 1 )) THEN
( component )
ELSE ( component
|| Cast(Row_number()
over (
PARTITION BY module_id, "class",
component
ORDER BY duration DESC) AS VARCHAR) )
END AS component_1
FROM _combined)
SELECT DISTINCT module_id,
"catalog",
component_1 AS component,
pattern,
duration,
"load",
oal
FROM _numbered
ORDER BY "catalog",
"class",
component,
"load",
oal; I could not attach the database via DBeaver.
However, if I execute the first SQL code via DBeaver, it gives me the following beautified schema:
CREATE VIEW comp
(module_id, "catalog", component, pattern, duration, "load", oal)
AS
WITH _a
AS (SELECT module_id,
Max(semester) AS max_semester
FROM nsrq966
GROUP BY module_id),
_others
AS (SELECT DISTINCT n.semester,
n.module_id,
n."catalog",
n."class",
n.component,
n.pattern,
n."day",
n.start_time,
n.duration,
n."load",
( n."day" = 'SAT' ) AS oal
FROM nsrq966 AS n,
_a
WHERE ( ( n.semester = _a.max_semester )
AND ( n.module_id = _a.module_id )
AND ( n."class" != 'SSP' )
AND ( n."catalog" !~~ '%INT6' )
AND ( n."catalog" !~~ '%INTO6' )
AND ( n."catalog" !~~ '%INTY1' )
AND ( n."catalog" !~~ '%INTY2' )
AND ( n."catalog" NOT IN ( '41FYP', '43PDD', '44FYP',
'93FYP'
) ) )
),
_int
AS (SELECT DISTINCT n.semester,
n.module_id,
n."catalog",
'XX' AS "class",
'PRA' AS component,
'' AS pattern,
'' AS "day",
'08:00' AS start_time,
2.0 AS duration,
2.0 AS "load",
Cast('f' AS BOOLEAN) AS oal
FROM nsrq966 AS n,
_a
WHERE ( ( n.semester = _a.max_semester )
AND ( n.module_id = _a.module_id )
AND ( ( n."catalog" ~~ '%INT6' )
OR ( n."catalog" ~~ '%INTO6' )
OR ( n."catalog" ~~ '%INTY1' )
OR ( n."catalog" ~~ '%INTY2' ) ) )),
_fyp
AS (SELECT DISTINCT n.semester,
n.module_id,
n."catalog",
'XX' AS "class",
'PRA' AS component,
'' AS pattern,
'' AS "day",
'08:00' AS start_time,
1.0 AS duration,
1.0 AS "load",
Cast('f' AS BOOLEAN) AS oal
FROM nsrq966 AS n,
_a
WHERE ( ( n.semester = _a.max_semester )
AND ( n.module_id = _a.module_id )
AND ( n."catalog" IN ( '41FYP', '43PDD', '44FYP', '93FYP' )
)
)),
_combined
AS (((SELECT *
FROM _others)
UNION ALL
(SELECT *
FROM _int))
UNION ALL
(SELECT *
FROM _fyp)),
_numbered
AS (SELECT *,
CASE
WHEN (( Row_number()
over (
PARTITION BY module_id, "class", component
ORDER BY duration DESC) = 1 )) THEN
( component )
ELSE ( component
|| Cast(Row_number()
over (
PARTITION BY module_id, "class",
component
ORDER BY duration DESC) AS VARCHAR) )
END AS component_1
FROM _combined)
SELECT DISTINCT module_id,
"catalog",
component_1 AS component,
pattern,
duration,
"load",
oal
FROM _numbered
ORDER BY "catalog",
"class",
component,
"load",
oal;
CREATE TABLE nsrq966
(
career VARCHAR,
module_sch VARCHAR,
semester INTEGER,
acadprog VARCHAR,
stud_id VARCHAR,
stud VARCHAR,
module_id VARCHAR,
subject VARCHAR,
"catalog" VARCHAR,
"class" VARCHAR,
component VARCHAR,
instr_id VARCHAR,
instr VARCHAR,
venue VARCHAR,
"day" VARCHAR,
pattern VARCHAR,
start_time VARCHAR,
end_time VARCHAR,
duration DOUBLE,
"load" DOUBLE
); I could attach the database via DBeaver even I close and open DBeaver after creation of the view comp.
Hope the information is good for you.
I am using duckdb version 0.10.2, and using DBeaver 24.1.0 with org.jkiss.dbeaver.ext.duckdb_1.0.24.202406021658.jar.
DBeaver could connected to
:memory:and a file-backed duckdb database with an empty table, which are created using duckdb 0.10.2.The connection test from within DBeaver shows:
I did the same connection testing to larger duckdb (30 MB). It gave me this error:
I have raised a similar issue in https://github.com/dbeaver/dbeaver/issues/21353
Update 1:
I have updated duckdb python client to 1.0.0 and duckdb.exe to 1.0.0 too. The issue persists even if I have re-created a new database file.