Changelog

Source: NEWS.md

SqlRender 1.19.0

Changes:

  1. When creating emulated temp tables (Oracle, Spark, BigQuery), will first attempt to drop (if exist). This is to clean up any orphan tables from a previous (unsuccesful) run.

  2. Adding tentative support for InterSystems IRIS. Could still be removed.

Bugfixes:

  1. On Snowflake, fixed ‘This session does not have a current schema’ error when translating legacy IF OBJECT_ID('tempdb..#table', 'U') IS NOT NULL DROP TABLE #table;

SqlRender 1.18.12024-08-21

Changes:

  1. Changing Spark translation of DATEADD() from DATE_ADD() to DATEADD() as required by some but not all Databricks instances.

Bugfixes:

  1. Fixed DuckDb translation of CAST(CONCATENATE(...) AS DATE).

  2. Fix Snowflake and DataBricks translation of CAST(... AS DATE) when ... is a literal.

SqlRender 1.18.02024-05-30

Changes:

  1. Adding translation for bitwise AND operator (&).

  2. Changing temp table field name maximum length to 63-8 for throwing warnings (was 30-8). Oracle changed it’s limit from 30 to 128 in version 12.2, which was released in 2021. The new limit comes from PostgreSQL, which by default allows for 63 characters. All other supported DBMSs allow for longer names.

Bugfixes:

  1. For SQLite, now translating ALTER TABLE ALTER COLUMN BIGINTto dummy statement (SELECT 0;), since all integer types are the same on SQLite.

  2. Fixed translation of ALTER TABLE ALTER COLUMN on PostgreSQL.

  3. More robust detection of string concatenation for BigQuery.

SqlRender 1.17.02024-03-20

Changes:

  1. Adding datetime calculations to Spark translation.

Bugfixes:

  1. Fix translation of NEWID() for DuckDB.

  2. Fix LEFT() and RIGHT() translation on Spark.

  3. Fix some date functions on SQLite.

  4. Fix DROP TABLE IF EXISTS and CREATE TABLE IF EXISTS translation for Synapse.

SqlRender 1.16.12023-10-07

Changes:

  1. Adding support for ALTER TABLE ADD COLUMN for PostgreSQL, even though it is not correct OhdsiSql (because it is not valid SQL Server SQL).

  2. Removing translation of DELETE and INSERT rules for DataBricks as no longer needed.

Bugfixes:

  1. Fix translation of ALTER TABLE ADD CONTRAINT on Postgres, which was broken by v1.16.0.

SqlRender 1.16.02023-09-20

Changes:

  1. Adding support for ALTER TABLE ADD for SQLite and PostgreSQL.

  2. The render(), translate(), and translateSingleStatement() functions now preserve attributes of the SQL object.

  3. Adding support for IIF for Synapse.

  4. Translating double quotes to backticks for BigQuery.

Bugfixes:

  1. Fix translation of drvd() for Snowflake.

  2. Fix translation of ‘a.b.c…d’ pattern for Snowflake.

SqlRender 1.15.22023-08-10

Bugfixes:

  1. Fixing translation of DATEADD() for DuckDB when number to add is not an integer.

SqlRender 1.15.12023-06-29

Bugfixes:

  1. Fixed translation of DATEADD() for DuckDB when number to add is an expression instead of a verbatim number.

  2. Fixed Synapse option in the SqlDeveloper Shiny app.

SqlRender 1.15.02023-05-08

Changes:

  1. Adding translation of FROM (VALUES ...) AS drvd(...) for PostgreSQL, SQL Server, Oracle, RedShift, SQLite, DuckDb, BigQuery, and Spark.

Bugfixes:

  1. Correct translation when referring to temp table field for DBMSs that don’t support temp tables (e.g. SELECT #tmp.name FROM #tmp;).

  2. Fixing ‘…’ in table aliases generated by dbplyr.

SqlRender 1.14.02023-04-13

Changes:

  1. Adding translation of DATEPART() for Spark.

  2. Adding translation of CEILING() for Spark.

Bugfixes:

  1. Fixing translation of CAST(AS DATE) on Oracle and Netezza when thing to cast is not a literal string.

  2. Fixing translation of ALTER TABLE for PostgreSQL.

SqlRender 1.13.12023-03-21

Bugfixes:

  1. Fixing translation of DROP TABLE IF EXISTS on Netezza.

SqlRender 1.13.02023-03-14

Changes:

  1. Added translation of charindex for BigQuery.

  2. SQLite translation of RAND() now returns value between 0 and 1 for consistency with other platforms.

Bugfixes:

  1. Fixing DuckDB translation of DATEADD().

SqlRender 1.12.12023-02-21

Bugfixes:

  1. Fixed translation of WITH ... INSERT on Snowflake.

  2. Fixed translation of some functions on Snowflake casting to NUMERIC instead of FLOAT.

SqlRender 1.12.02023-01-26

Changes:

  1. Adding translation of TRY_CAST().

  2. The loadRenderTranslateSql() function now also looks in the sql folder of the package, so SQL files no longer have to be in the sql/sql_server subfolder.

  3. Ensuring result of YEAR(), MONTH(), DAY(), and DATEPART() equivalents return integers on SQLite.

  4. Ensuring interval is integer on BigQuery.

SqlRender 1.11.12023-01-11

Changes:

  1. Removing parentheses around subqueries for UNION and UNION ALL on SQLite, which otherwise would cause an error.

  2. Preventing translating SQL twice by adding attribute to output string.

Bugfixes:

  1. Fixing translation of INTERSECT on BigQuery.

SqlRender 1.11.02022-12-02

Changes:

  1. Added translation for SELECT *, pattern for Oracle.

  2. Switched Oracle translation of SELECT TOP from WHERE ROWNUM <= to FETCH FIRST ROWS ONLY.

  3. Added translation of DATEPART() to all dialects.

  4. Added translation patterns to avoid alias conflicts when using dbplyr on BigQuery.

  5. Adding translation of SQL Server’s IIF() shorthand for all dialects.

  6. As a temporary workaround for older SQL Server instances, translating DROP TABLE IF EXISTS and CREATE TABLE IF NOT EXISTS to old syntax.

Bugfixes:

  1. Fixed erroneously identifying parts of quoted text as start of SQL comments.

  2. Fixed translation of INSERT INTO with more than one CTE for Spark.

  3. Fixed translation of SELECT TOP on all platforms when using DISTINCT.

SqlRender 1.10.02022-09-20

Changes:

  1. Added translation to Snowflake.

  2. Added translation to Synapse.

  3. Added translation to DuckDb.

Bugfixes:

  1. Fixed translation for NEWID() on BigQuery.

SqlRender 1.9.22022-07-11

Bugfixes:

  1. Fixed error about missing checkmate package.

  2. Fixed error about targetDialect types not matching (character != factor) on older R versions (< 4).

  3. Fixed warning about deprecated icon in Shiny app.

SqlRender 1.9.12022-06-29

Changes:

  1. Adding input checks to all functions for more informative error messages.

Bugfixes:

  1. Fixed translation of CREATE TABLE statements specifying a field can be NULL on BigQuery.

  2. Fixes translation of CAST('20000101' AS DATE) on Oracle.

SqlRender 1.9.02022-02-18

Changes:

  1. Added additional logic for INSERT statements on Spark, including the new sparkHandleInsert() function.

  2. Supporting DROP TABLE IF EXISTS translation across all dialects.

  3. Adding translation rule for CAST(... AS DATE) for SQLite.

  4. Added snakeCaseToCamelCaseNames() and camelCaseToSnakeCaseNames() functions.

SqlRender 1.8.32022-01-07

Changes:

  1. Added rules for translating implicit string concatenation to BigQuery.

  2. Added getTempTablePrefix() function.

SqlRender 1.8.2

Changes:

  1. Adding listSupportedDialects() function.

Bugfixes:

  1. Fix DATEFROMPARTS and DATETIMEFROMPARTS translation for newer SQLite versions by first converting to INT before converting to TEXT.

SqlRender 1.8.12021-09-15

Changes:

  1. Provide informative error message when Java is outdated.

SqlRender 1.8.02021-09-02

Changes:

  1. Added Apache Spark dialect (“spark”).

  2. Adding automated check whether correct Java Jar file is loaded, throws warning if not.

  3. Adding translation of CEILING() for SQLite.

Bugfixes:

  1. Fixing setting of global tempEmulationSchema option.

  2. Workaround for SUBSTR() function bug in newer versions of SQLite (by explicitly casting string to type STRING).

SqlRender 1.7.02021-03-02

Changes:

  1. Deprecating oracleTempSchema argument in various functions in favor of tempEmulationSchema schema, which can also be set globally using the sqlRenderTempEmulationSchema option.

  2. Adding translation of DATEDIFF(YEAR, …) and DATEDIFF(MONTH, …) to all dialects.

  3. Updated createRWrapperForSql() to latest SqlRender standards.

Bugfixes:

  1. Fixed translation of CTE without FROM or UNION in BigQuery.

  2. Fixed translation of CONVERT(DATE…) in SQLite.

  3. Fixed translation of DATEDIFF with literals in SQLite.

SqlRender 1.6.82020-07-31

Bugfixes:

  1. Fixing error when SQL is not a native character vector (e.g. when it has been created using glue).

SqlRender 1.6.7

Changes:

  1. Throw a more informative error message when loadRenderTranslateSql() cannot find the SQL file in the specified package.

Bugfixes:

  1. On SQLite, DATEADD and CONVERT functions now cast to REAL (used to represent DATE / DATETIME).

  2. On SQLite, DATEADD function now works when amount to add is not a verbatim number.

SqlRender 1.6.62020-05-19

Changes:

  1. Adding rules for modulus operator for BigQuery.

  2. Deleting UPDATE STATISTICS statement for BigQuery.

SqlRender 1.6.52020-04-01

Bugfixes:

  1. Fixed CAST(@a as DATE) for ‘YYYYMMDD’ string dates on BigQuery.

SqlRender 1.6.42020-03-17

Changes:

  1. Adding support for Apache Hive LLAP.

  2. Adding functions to convert camelCase to Title Case. (camelCaseToTitleCase)

SqlRender 1.6.32020-01-13

Changes:

  1. Added rules for SQLite for LEFT and RIGHT functions.

  2. SQLite now dropping schema name when creating and dropping index (as this throws an error if left).

  3. No longer automatically casting literal to TEXT in RedShift CTE. Users are required to do explicit casts instead.

  4. BigQuery insertTable() now also uses CTAS hack.

  5. Added translation rules for HASHBYTES.

Bugfixes:

  1. Fixing GETDATE translation for SQLite.

  2. When calling render, the replacement value can now contain a $ sign. (Previously this caused an error).

  3. isNumeric can now also be applied to numeric fields in Postgres.

  4. Better handling of illegal characters in Impala.

SqlRender 1.6.22019-06-24

Changes:

  1. Added rules for Oracle for conditional indices.

Bugfixes:

  1. Fixing erroneous variable name translation for BigQuery.

SqlRender 1.6.1

Changes:

  1. Added rules for Impala for INTEGER NOT NULL and DOUBLE PRECISION.

Bugfixes:

  1. Fixed isNumeric check for Netezza

SqlRender 1.6.02019-02-15

Changes:

  1. Major overhaul of BigQuery translation.

  2. Added support for SQLite.

  3. ISNUMERIC translation implemented for Impala, Netezza, and BigQuery.

  4. Performance improvement for Impala temp tables (CREATE TABLE ... STORED AS PARQUET).

  5. Adding functions render and translate that output strings instead of lists. Deprecating renderSql and translateSql.

  6. Added function translateSingleStatement.

Bugfixes:

  1. Dropping WHERE clause when translating CREATE INDEX for PDW.

  2. Fixed PDW’s equivalent of CREATE TABLE IF NOT EXISTS.

SqlRender 1.5.32018-11-23

Changes:

  1. Added translation rules for DATETIME2.

Bugfixes:

  1. Fixed misspelling of DISTRIBUTE in Netezza translation rules.

SqlRender 1.5.22018-08-17

Changes:

  1. Translation for indexes for RedShift and Impala.
  2. Translation for UPDATE STATISTICS.

Bugfixes:

  1. Fixed translation of AS when used for table names in Oracle.

SqlRender 1.5.02018-05-28

Changes:

  1. Improved support for Netezza.

  2. Added random distribution hints for PDW, RedShift, Netezza.

  3. Improved MPP index translation for PDW and Netezza.

  4. Warnings about missing parameters when rendering SQL can now be turned off.

Bugfixes:

  1. Translation of camelCase to snake_case now adds underscore before numbers.

SqlRender 1.4.82018-02-26

Changes:

  1. Better handling of ‘FROM DUAL’ for Oracle.

  2. Improved support for Netezza and Impala.

SqlRender 1.4.62017-11-07

Changes:

  1. Bigquery support for mismatched string and int arguments in coalesce.

  2. Translate decimal to float for BigQuery.

  3. Created rules to add dummy ‘group by’ for Oracle statements combining ‘case’ and ‘count’ to prevent Oracle from crashing.

  4. Adding ‘UNBOUNDED PRECEDING’ to RedShift windowing functions.

SqlRender 1.4.32017-09-15

Changes:

  1. Added a Shiny app for developing parameterized SQL, and view how this would be rendered and translated into the various supported dialects.

  2. Added support for Google BigQuery.

  3. Added many more rules for Amazon RedShift, including support for optimization hints.

  4. Added rules for DELETE FROM translation for Impala.

Bugfixes:

  1. Fixed issue when splitting SQL containing hints.

SqlRender 1.3.72017-05-03

Changes:

  1. Added translation rules for ISNUMERIC and LOG(@expression, @base)

Bugfixes:

  1. Fixed bug when trying to split SQL where reserved word ‘end’ is used as a field name.

  2. Fixes for Impala translations.

  3. Fixed translation issues for Oracle involving ‘FROM DUAL’.

  4. Added workaround for Oracle bug for intervals greater than 99 days.

  5. Fixed bug when trying to split SQL where last line has comment but no EOL.

SqlRender 1.3.02017-03-24

Changes:

  1. Added ability to use regular expression in translation patterns. This allowed SELECT TOP n to be translated.

  2. Deprecated sourceDialect argument.

  3. Added translation for CONCAT function with >2 arguments to Oracle (which only allows 2 arguments)

  4. Added hints for translation optimization to massive parallel platforms like RedShift

  5. Throw warnings when translateSql() is called with variable names that are not in the SQL

  6. Throw warnings when table names are too long for Oracle

Bugfixes:

  1. Fixed translation for date functions so they will now work properly with datetime fields as well.
  2. Now throwing error when boolean logic cannot be parsed (instead of assuming result is TRUE)

SqlRender 1.2.02017-01-16

Changes:

  1. Added support for Impala

Bugfixes:

  1. Fixed translation for DATEFROMPARTS for RedShift

SqlRender 1.1.72016-08-16

Changes: initial submission to CRAN