Comments (8)
@ged-yuko please create a separate issue and provide samples and other useful details there.
Snowflake documentation demonstrates expressions of the expr:something::typename
form, the lack of support for which is the reason behind the behavior found by @ufuk-ergin-carbon
Here some offtopic follows:
Looks like there are also inline XPath-expressions and a FLATTEN function with a strange lambda-like arguments in Snowflake, and then there is more. These pieces doesn't really needed until you are targeting to cover all the SQL extensions provided by all the databases (or at least complete syntax of some DBs). I also don't think you would really like to implement a complete XPath handling for your semantic analysis, so another issue doesn't worth it. Otherwise it would require appropriate investigation of the SQL extensions and corresponding planning. Maybe even modular parsing, or at least modular grammars, which you apparently decided not to use at all, while different DBs have pretty different syntax extensions.
from dbeaver.
Sure. Here are the log files. Thank you for your analysis.
dbeaver-debug-1715096569377.log
dbeaver-debug-1715324262819.log
dbeaver-debug-1715345341257.log
dbeaver-debug.log
from dbeaver.
Hello @ufuk-ergin-carbon,
Please provide the mentioned script and the table's DDL so we can reproduce the issue. Thanks!
from dbeaver.
Hi @ShadelessFox , here is the script file. It has multiple tables. Do you need the DDL of all the joined tables?
WITH parent_child_relation AS (
SELECT
t2.objectid,
t1.parentobjectid,
t1.parentobjecttype
FROM ANALYTICS_PROD_DB.ANALYTICS_AUDITTRAIL.AUDIT_TRAILS_HIERARCHY AS t1
INNER JOIN ANALYTICS_PROD_DB.ANALYTICS_AUDITTRAIL.AUDIT_TRAILS_HIERARCHY AS t2
ON t1.objectid = t2.parentobjectid
)
SELECT
CASE
WHEN a.objecttype = 1 THEN a.objectid
WHEN a.objecttype = 2 THEN level_1.parentobjectid
WHEN a.objecttype = 3 THEN level_1.parentobjectid
WHEN a.objecttype = 4 THEN level_2.parentobjectid
WHEN a.objecttype = 5 THEN level_2.parentobjectid
WHEN a.objecttype = 6 THEN level_1.parentobjectid
WHEN a.objecttype = 7 THEN NULL
WHEN a.objecttype = 15 THEN NULL
WHEN a.objecttype = 24 THEN level_2.parentobjectid
WHEN a.objecttype = 25 THEN level_1.parentobjectid
WHEN a.objecttype = 26 THEN level_1.parentobjectid
WHEN a.objecttype = 27 THEN level_1.parentobjectid
WHEN a.objecttype = 28 THEN NULL
END AS source_bill_id,
a.id,
a.actiontime AS action_time_utc,
to_date(convert_timezone(coalesce(dc.timezone, 'America/Los_Angeles'), a.actiontime)) AS adjusted_user_date,
a.actiontype AS action_type,
a.actortype AS actor_type,
a.actorid AS actor_id,
dc.doctor_id AS doctor_id,
dc.practice_name,
a.objectid AS object_id,
a.objecttype AS object_type_id,
CASE
WHEN a.objecttype = 1 THEN 'Bill'
WHEN a.objecttype = 2 THEN 'Billable'
WHEN a.objecttype = 3 THEN 'Payment Report'
WHEN a.objecttype = 4 THEN 'Payment Report Item'
WHEN a.objecttype = 5 THEN 'Billable Split'
WHEN a.objecttype = 6 THEN 'Billable Notes'
WHEN a.objecttype = 7 THEN 'Payment Report Item Adjustment'
WHEN a.objecttype = 15 THEN 'Insurance Coverage'
WHEN a.objecttype = 24 THEN 'Refunds'
WHEN a.objecttype = 25 THEN 'Payments'
WHEN a.objecttype = 26 THEN 'WriteOffs'
WHEN a.objecttype = 27 THEN 'Claim Submission'
WHEN a.objecttype = 28 THEN 'Queued Bills'
END AS object_type,
CASE
WHEN is_null_value(try_parse_json(a.changes):billId) = true THEN null
ELSE try_parse_json(a.changes):billId::STRING
END AS bill_id,
CASE
WHEN is_null_value(try_parse_json(a.changes):reviewed) = true THEN null
ELSE try_parse_json(a.changes):reviewed::STRING
END AS reviewed_at,
CASE
WHEN is_null_value(try_parse_json(a.changes):reviewedBy) = true THEN null
ELSE try_parse_json(a.changes):reviewedBy::STRING
END AS reviewed_by,
CASE
WHEN is_null_value(try_parse_json(a.changes):insuranceCoverageId) = true THEN null
ELSE try_parse_json(a.changes):insuranceCoverageId::STRING
END AS insurance_coverage_id,
CASE
WHEN is_null_value(try_parse_json(a.changes):secondaryInsuranceCoverageId) = true THEN null
ELSE try_parse_json(a.changes):secondaryInsuranceCoverageId::STRING
END AS secondary_insurance_coverage_id,
CASE
WHEN is_null_value(try_parse_json(a.changes):tertiaryInsuranceCoverageId) = true THEN null
ELSE try_parse_json(a.changes):tertiaryInsuranceCoverageId::STRING
END AS tertiary_insurance_coverage_id,
CASE
WHEN is_null_value(try_parse_json(a.changes):organizationId) = true THEN null
ELSE try_parse_json(a.changes):organizationId::STRING
END AS organization_id,
coalesce(try_parse_json(a.changes):organizationId::STRING IS NOT NULL OR is_null_value(try_parse_json(a.changes):organizationId) = false, false) AS is_organization_responsibility,
coalesce(try_parse_json(a.changes):insuranceCoverageId::STRING IS NOT NULL OR is_null_value(try_parse_json(a.changes):insuranceCoverageId) = false, false) AS is_insurance_responsibility,
coalesce((is_null_value(try_parse_json(a.changes):insuranceCoverageId) = true OR try_parse_json(a.changes):insuranceCoverageId IS NULL) AND (is_null_value(try_parse_json(a.changes):organizationId) = true OR try_parse_json(a.changes):organizationId IS NULL), false) AS is_patient_responsibility,
CASE
WHEN is_null_value(try_parse_json(a.changes):invoiceSentTime) = true THEN null
ELSE try_parse_json(a.changes):invoiceSentTime::STRING
END AS invoice_sent_time,
CASE
WHEN is_null_value(try_parse_json(a.changes):status) = true THEN null
ELSE try_parse_json(a.changes):status::STRING
END AS status,
CASE
WHEN is_null_value(try_parse_json(a.changes):createdAt) = true THEN null
ELSE try_parse_json(a.changes):createdAt::STRING
END AS created_at,
CASE
WHEN is_null_value(try_parse_json(a.changes):deleted) = true THEN null
ELSE try_parse_json(a.changes):deleted::STRING
END AS deleted_at,
CASE
WHEN is_null_value(try_parse_json(a.changes):frequencyCode) = true THEN null
ELSE try_parse_json(a.changes):frequencyCode::STRING
END AS frequency_code,
CASE
WHEN is_null_value(try_parse_json(a.changes):text) = true THEN null
ELSE try_parse_json(a.changes):text::STRING
END AS changes_text,
CASE
WHEN is_null_value(try_parse_json(a.changes):queue_id) = true THEN null
ELSE try_parse_json(a.changes):queue_id::STRING
END AS queue_id,
a.changes AS audit_trails_change,
q."TYPE" AS queues_type
FROM ANALYTICS_PROD_DB.ANALYTICS_AUDITTRAIL.AUDIT_TRAILS AS a
LEFT JOIN analytics_prod_db.analytics.stg_doctors AS dc
ON a.actorid = dc.user_id
LEFT JOIN ANALYTICS_PROD_DB.ANALYTICS_BILLING_HUB.QUEUES AS q
ON
q.id = try_parse_json(a.changes):queue_id::STRING
AND q.type IN ('DEFERRAL', 'ESCALATION')
LEFT JOIN ANALYTICS_PROD_DB.ANALYTICS_AUDITTRAIL.AUDIT_TRAILS_HIERARCHY AS level_1
ON
level_1.objectid = a.objectid
AND level_1.parentobjecttype = 1
LEFT JOIN parent_child_relation AS level_2
ON
level_2.objectid = a.objectid
AND level_2.parentobjecttype = 1
WHERE
a.objecttype IN (1, 2, 3, 4, 5, 6, 7, 15, 24, 25, 26, 27, 28)
AND a.actortype = 'U'
;
from dbeaver.
Thank you for your quick answer. We will try reproducing the issue without the DDL; the query analysis probably failed due to the script's complexity, so it shouldn't depend on the schema.
Can you also provide log files? We might find something helpful there: https://github.com/dbeaver/dbeaver/wiki/Log-files#log-files
from dbeaver.
Taking a look at the parser's grammar used in dbeaver, while it parses typecast of the form expr::typename, it doesn't handle expr:something::typename. Which is also easily discoverable by observing where the identifiers stop being highlighted in this select statement. So its about the support of the json-related syntax constructs apparently.
from dbeaver.
@ged-yuko please create a separate issue and provide samples and other useful details there.
from dbeaver.
Thank you for report
from dbeaver.
Related Issues (20)
- OpenStreetMap view not displaying map when showing point with EPSG 32188 projection in DBeaver HOT 2
- Opening a procedure via the command line HOT 1
- MySQL database dump with --force option still stops on errors HOT 1
- Dump Database not functioning HOT 2
- Cannot save changes to stored procedures through data editor
- Add a connection to Microsoft Dynamics 365 HOT 3
- (Postgres and Oracle) Advanced Copy as JSON, wrong value when copying date-like types
- Prevent auto upper casing of keywords in comments HOT 1
- Store secrets using Secret Service API on Linux
- Add toggle to switch between names and labels of fields during export to JSON HOT 1
- semicolon removed at the end of the line HOT 1
- Corrupt UI components after fresh install of v24.0.4 HOT 2
- Can't edit data in Derby HOT 1
- Order by clause is invalid when using filtering
- [DuckDB] Can't update data in data grid
- MySQL Connector 8.2.0 issue HOT 6
- divide the parameters of functions and procedures into two tabs into input and return parameters HOT 1
- [Eclipse] Errors when creating new column HOT 1
- Minor translation issue with German translation "Zeigen alle Datenbanken an" HOT 1
- Data transform can't invert coordinates
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from dbeaver.