search

CategoricalData / CQL

Categorical Query Language IDE
http://categoricaldata.net
299 stars 23 forks source link

catdata.LineException: org.postgresql.util.PSQLException: ERROR: column "varchar" does not exist #53

Closed o1lo01ol1o closed 3 years ago

o1lo01ol1o commented 4 years ago

Calling command export_jdbc_query with default options causes complaints on varchar types.

Changing Varchar to Text in cql has no effect.

Stack below:

java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: column "varchar" does not exist
  Position: 106
    at catdata.aql.fdm.ToJdbcPragmaQuery.execute(ToJdbcPragmaQuery.java:48)
    at catdata.aql.exp.AqlMultiDriver.call(AqlMultiDriver.java:407)
    at java.base/java.lang.Thread.run(Thread.java:835)
Caused by: org.postgresql.util.PSQLException: ERROR: column "varchar" does not exist
  Position: 106
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
    at catdata.aql.fdm.ToJdbcPragmaQuery.execute(ToJdbcPragmaQuery.java:43)
    ... 2 more
catdata.LineException: org.postgresql.util.PSQLException: ERROR: column "varchar" does not exist
  Position: 106
    at catdata.aql.exp.AqlMultiDriver.call(AqlMultiDriver.java:437)
    at java.base/java.lang.Thread.run(Thread.java:835)
o1lo01ol1o commented 4 years ago

Nvarchar and Longvarchar throw a slightly different error:

java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: type "nvarchar" does not exist
  Position: 112
    at catdata.aql.fdm.ToJdbcPragmaInstance.execute(ToJdbcPragmaInstance.java:109)
    at catdata.aql.exp.AqlMultiDriver.call(AqlMultiDriver.java:407)
    at java.base/java.lang.Thread.run(Thread.java:835)
Caused by: org.postgresql.util.PSQLException: ERROR: type "nvarchar" does not exist
  Position: 112
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2183)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:307)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:293)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:270)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:266)
    at catdata.aql.fdm.ToJdbcPragmaInstance.deleteThenCreate(ToJdbcPragmaInstance.java:61)
    at catdata.aql.fdm.ToJdbcPragmaInstance.execute(ToJdbcPragmaInstance.java:75)
    ... 2 more
catdata.LineException: org.postgresql.util.PSQLException: ERROR: type "nvarchar" does not exist
  Position: 112
    at catdata.aql.exp.AqlMultiDriver.call(AqlMultiDriver.java:437)
    at java.base/java.lang.Thread.run(Thread.java:835)
o1lo01ol1o commented 4 years ago

adding { options jdbc_query_export_convert_type = "Foo" } will cause the error to complain that column "foo" does not exist

o1lo01ol1o commented 4 years ago

@wisnesky If you think by any chance that this is a mechanical fix, I can take a look at it if you can point me to the relevant source.

wisnesky commented 4 years ago

The relevant code, I think, is the toSqlViews method of Query:

https://github.com/CategoricalData/CQL/blob/bb61af640dbd500d223d37cab614c114dd61cbf8/src/main/java/catdata/aql/Query.java#L966 https://github.com/CategoricalData/CQL/blob/bb61af640dbd500d223d37cab614c114dd61cbf8/src/main/java/catdata/aql/Query.java#L966

Along with the toSQL method of Schema:

https://github.com/CategoricalData/CQL/blob/bb61af640dbd500d223d37cab614c114dd61cbf8/src/main/java/catdata/aql/Schema.java#L439 https://github.com/CategoricalData/CQL/blob/bb61af640dbd500d223d37cab614c114dd61cbf8/src/main/java/catdata/aql/Schema.java#L439

For whatever reason, these methods are returning ill-formed SQL, such that ‘varchar' is being thought of as a table rather than a type. The place to start would be to print the generated SQL at both locations, look at the way the generated code is mangled, and see if there’s a hack that would fix the generated SQL in your case specifically. With that in hand hopefully a general fix will be easy.

On Sep 16, 2020, at 7:28 AM, Tim Pierson notifications@github.com wrote:

@wisnesky https://github.com/wisnesky If you think by any chance that this is a mechanical fix, I can take a look at it if you can point me to the relevant source.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/CategoricalData/CQL/issues/53#issuecomment-693443569, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA2QKNZK3Q7746EGB76PSMDSGDDSJANCNFSM4PEQRKRQ.

wisnesky commented 3 years ago

The 2021 version of CQL should be more robust on stuff like this - in particular, falling back to "other" when no available CQL type in the "sql" typeside is available. However, that's been tested mostly on import so far, not export.

wisnesky commented 3 years ago

query export on Postgres works now - this was actually being caused by the use of 'convert' instead of 'cast', as well as not converting cql to sql types