Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Getting Invalid SQL type: date error queries with declare statements #32

Closed
TunahanOcal opened this issue Dec 13, 2023 · 4 comments · Fixed by #37
Closed

Getting Invalid SQL type: date error queries with declare statements #32

TunahanOcal opened this issue Dec 13, 2023 · 4 comments · Fixed by #37

Comments

@TunahanOcal
Copy link

Hello,

I am getting type errors, if the query has declare statements. You can see the example declare statements and the exception below.

declare _delta_date_min date;
declare _delta_date_max date;


Exception in thread "main" com.google.zetasql.toolkit.catalog.typeparser.ZetaSQLTypeParseError: Invalid SQL type: date
	at com.google.zetasql.toolkit.catalog.typeparser.ZetaSQLTypeParser.parse(ZetaSQLTypeParser.java:77)
	at com.google.zetasql.toolkit.ZetaSQLToolkitAnalyzer$StatementAnalyzer.parseASTType(ZetaSQLToolkitAnalyzer.java:253)
	at java.base/java.util.Optional.map(Optional.java:265)
	at com.google.zetasql.toolkit.ZetaSQLToolkitAnalyzer$StatementAnalyzer.applyVariableDeclaration(ZetaSQLToolkitAnalyzer.java:285)
	at com.google.zetasql.toolkit.ZetaSQLToolkitAnalyzer$StatementAnalyzer.next(ZetaSQLToolkitAnalyzer.java:195)
	at com.google.zetasql.toolkit.ZetaSQLToolkitAnalyzer$StatementAnalyzer.next(ZetaSQLToolkitAnalyzer.java:148)

Thanks in advance.
@TunahanOcal
Copy link
Author

I just noticed that if the variable types are written in lowercase, errors will occur; whereas, if the types are written in uppercase, there will be no exception.

@marcinp55
Copy link

I'm getting the same exception and changing the types to uppercase really does solve the problem. Given that SQL is case-insensitive that's quite unexpected.

ppaglilla added a commit that referenced this issue May 5, 2024
@ppaglilla
Make the type parser case insensitive
465bced 
Fixes #32
ppaglilla added a commit that referenced this issue May 5, 2024
@ppaglilla
Make the type parser case insensitive (#35)
6e51423 
The type parser was previously case sensitive, while SQL types are case insensitive. This went unnoticed for a while since upper-cased types are usually always used, but is fundamentally incorrect.

Fixes #32
@ppaglilla
Copy link
Collaborator

ppaglilla commented May 5, 2024 •
edited
Loading

Fixed in 6e51423, will be included in the next release. This release has been a long time coming, but should release shortly.

@ppaglilla ppaglilla mentioned this issue May 6, 2024
Merged
ppaglilla added a commit that referenced this issue May 6, 2024
@ppaglilla @dion-ricky
@JLLeitschuh @TeamModerne @hamnis
Release v0.5.0 (#37)
c8c24d9 
* Update ZetaSQL to version 2023.10.1

* Rewrite queries to fully quote all name paths before analysis

* Avoid no longer necessary nesting of tables in catalogs

* Update query rewritting to only re-quote name paths that refer to resources (i.e. tables, functions, etc)

* Avoid no longer necessary nesting of functions in catalogs

* Avoid no longer necessary nesting of TVFs in catalogs

* Remove unnecessary slf4j dependency

Closes #24

* Reduce code duplication in BigQueryCatalog

* Reduce the amount of nesting for procedures in catalogs

* Avoid duplicate code when creating different types of resources in catalogs

* Use the DOUBLE type kind for BigQuery FLOAT64 columns

Closes #25

* Added extractColumnLevelLineage for ResolvedQueryStmt
Closes #28

* Changed the access modifier of ParentColumnFinder to public
Closes #28

* Make ColumnLineageExtractor accept only concrete statement types

Previously, the API for ColumnLineageExtractor used the method
::extractColumnLevelLineage(ResolvedStatement). Since introducing
support for ResolvedQueryStmts, which needs specifying an output table
separately; maintaining the generic ResolvedStatement API required
making it confusing, since it would optionally need to accept an output
table.

This makes it so that teams building lineage applications need to
explicitly determine the statements they support and call the
corresponding ::extractColumnLevelLineage() method. Such as
::extractColumnLevelLineage(ResolvedInsertStmt) or
::extractColumnLevelLineage(ResolvedQueryStmt, String).

* Bump development version to 0.5.0-SNAPSHOT

* vuln-fix: Use HTTPS instead of HTTP to resolve deps CVE-2021-26291 (#30)

This fixes a security vulnerability in this project where the `pom.xml`
files were configuring Maven to resolve dependencies over HTTP instead of
HTTPS.

Weakness: CWE-829: Inclusion of Functionality from Untrusted Control Sphere
Severity: High
CVSS: 8.1
Detection: CodeQL & OpenRewrite (https://app.moderne.io/recipes/org.openrewrite.maven.security.UseHttpsForRepositories)

Reported-by: Jonathan Leitschuh <[email protected]>


Bug-tracker: JLLeitschuh/security-research#8
Detection: CodeQL (https://codeql.github.com/codeql-query-help/java/java-maven-non-https-url/) & OpenRewrite (https://app.moderne.io/recipes/org.openrewrite.maven.security.UseHttpsForRepositories)

Reported-by: Jonathan Leitschuh <[email protected]>


Bug-tracker: JLLeitschuh/security-research#8


Use this link to re-run the recipe: https://app.moderne.io/recipes/org.openrewrite.maven.security.UseHttpsForRepositories?organizationId=R29vZ2xl

Co-authored-by: Moderne <[email protected]>

* Make the type parser case insensitive (#35)

The type parser was previously case sensitive, while SQL types are case insensitive. This went unnoticed for a while since upper-cased types are usually always used, but is fundamentally incorrect.

Fixes #32

* Add reflection-based patching of GRPC's default max nesting depth (#36)

ZetaSQL's Java API uses a GRPC service to call into the actual C++ implementation of ZetaSQL. By default, the serialization logic of that communication allows for a nesting depth in protobuf messages of up to 100. However, long queries can exceed that level of nesting and as a result cannot be analyzed by default.

This implements a reflection-based patch that allows users to override
that limit to a greater number. This is brittle by design and should
be used with caution.

Fixes #31

* Upgrade to zetasql-2024-03-01 and bump deps (#33)

* Upgrade to zetasql-2024-03-01 and bump deps

* Enable all features

* Rollback Mockito to version 4.11.0

* Remove some v1.4 language options not supported by BigQuery

---------

Co-authored-by: Pablo Paglilla <[email protected]>

* Add missing @return in catalog Javadocs

* Update version to v0.5.0

---------

Co-authored-by: Dion Ricky Saputra <[email protected]>
Co-authored-by: Jonathan Leitschuh <[email protected]>
Co-authored-by: Moderne <[email protected]>
Co-authored-by: Erlend Hamnaberg <[email protected]>
@ppaglilla
Copy link
Collaborator

Version 0.5.0 has been released with the fix for this issue and is already available through maven

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Release v0.5.0 GoogleCloudPlatform/zetasql-toolkit
3 participants
@ppaglilla @marcinp55 @TunahanOcal