CDATA Changelog Build 8069 (2022-02-09) ----------------------- Changes since build 8007: - Fix metadata for statements involving COMPUTE or MACRO. Previously these would not return metadata when prepared (only when executed), but should now report the correct columns and types. Prepared query: SELECT COMPUTE('DATE', 'logindate(-5)') Columns: - Name: COMPUTE Type: DATE Prepared query: SELECT * FROM MACRO($$ $$) Columns: - Name: name Type: VARCHAR - Name: type Type: VARCHAR - Name: orbits Type: VARCHAR - etc. Internally prepared queries are exeucted on the server with WHERE 0 = 1, so their metadata is accurate to what 1010 reports but no actual data is read. The only exception is MACRO, because the driver does not understand the MACRO syntax completely enough to know how to apply WHERE 0 = 1 in all cases. Build 8007 (2021-12-03) ----------------------- Changes since build 7954: - Support for the COMPUTE function for executing 1010 macro code in SQL queries. The syntax supported depends upon whether QueryPassthrough is being used or not. Non-passthrough mode (default in Tableau / PBI) uses this syntax: SELECT COMPUTE('DATE', 'logindate(-5)'), ... FROM tdvt.calcs Passthrough mode uses the native syntax: SELECT COMPUTE(DATE, 'logindate(-5)'), ... FROM tdvt.calcs Build 7954 (2021-10-11) ----------------------- Changes since build 7894: - Tableau dialect fixes - Push down NULLS FIRST and NULLS LAST when performing an ORDER BY, so the results are in line with what Tableau expects. - General SQL translation fixes - Preserve the literals of ODBC literals given to the driver. Instead of converting {d 'xxx'}, {t 'xxx'} and {ts 'xxx'} all to TIMESTAMP 'xxx', they are converted to DATE 'xxx', TIME 'xxx' and TIMESTAMP 'xxx' - Avoid generating unnecessary casts when comparing against MIN and MAX in HAVING clauses. Previously this would result in clauses like this: HAVING CAST(MAX(...)) AS VARCHAR > '123.456' For input queries like this: HAVING MAX(...) > 123.456 - Other issues - Use 64-bit page and row counters to avoid overflows with queries that return over 4 billion rows. - Extract error messages from the !login API more reliably. The HTML tags shouldn't be reported in errors. - Update behavior of CacheSession to be more consistent across threads. Before CacheSession=false (a rare setting sometimes used to avoid persisting session info to disk) would share credentials across connections in an in-memory store that could not be reset. Now setting CacheSession=false will not cache credentials at all. This helps automated tests check handling of authentication errors more reliably. Build 7894 (2021-08-12) ----------------------- Changes since build 7836: - Re-fix Unicode parsing issues. The changes for this were applied in the 2020 builds but did not get applied in 2021. - Update to use the more compact !head API. This combines the request for the metadata (previously !cols) and the first 500 rows (previously !data) into a single request. - Provides a noticable improvement for queries that produce under 500 rows, especially when several are run in series. - Introduced a few regressions due to data format differences. Fixed issues around NULL detection for floats, and migrated functions based on date_part to use floats as !head:json does not support bigint. - Updated 1010-specific Tableau dialect settings to use our new custom dialect configuration. This is an internal change and should not have user-visible effects. Testing has not revealed any regressions caused by this migration. Build 7836 (2021-06-15) ----------------------- Changes since build 7803: - Add support for SAM pools using GroupID and ResetPool/RetryPool properties. If the GroupID property is set the driver will connect to a SAM pool user and use its session for the duration of the connection. The other options control different behaviors of the pool connection process: - ResetPool can be enabled to clean out stale pool sessions if the pool is exhausted. This should only be enabled temporarily since using it consistenly will prevent other connections from operating. - RetryPool can be disabled to fail immediately if the pool is exhausted instead of waiting for a pool user to become active. By default the driver will retry 4 times over 30 seconds before failing. - Various fixes to Tableau SQL generation, queries take advantage of 1010's conformant NULL behavior to simplify filters. - Add support for the ISDATE function Build 7803 (2021-05-13) ----------------------- Changes since build 7769: - Try to detect cases where timestamp calculations lead to values that are off by +/- 1 millisecond. The driver detects this by checking whether a timestamp ends in either .999s or .001s and rounds to the nearest whole second value. If needed this can be disabled by disabling the FixMilliseconds hidden property via Other="FixMilliseconds=false" - Make sure DATEDIFF upcasts to 64-bit integers, which avoids NULL outputs when doing date arithmetic or casting dates to numeric types. - Fix behavior of CAST(x AS NUMERIC) to apply standard rounding behavior. This comes in three variations: - CAST(x AS NUMERIC) rounds to the nearest integer - CAST(x AS NUMERIC(n, 0)) also rounds to the nearest integer - CAST(x AS NUMERIC(n, d)) rounds out to d digits - Update LOCATE to validate its string and position inputs. Build 7769 (2021-04-09) ----------------------- Changes since build 7719: - SSO - Re-enable SSO properties after completion of SSO QA. - Add support for the HTTPForm SSO type, which can be used to submit authorization over HTTP and retrieve tokens using the standard SAML POST binding form. - Enable hex logging for all requests that go over bd endpoints. This makes it easier to read logs and avoids log corruption from email or text editors. - Handle Unicode content within text fields. This would previously manifest as field data being truncated or improprerly decoded. - Preserve timestamp precision through internal driver conversions. This avoids adding or subtracting an extra millisecond cases where this used to occur. - The Tableau dialect was rewritten, which fixes some issues present in the original base SQL dialect (broken SQL for datetime comparisons and exponentiation, among other things). This also updates the float->int and string->int conversions to round in the way that Tableau expects and fixes other minor issues. Build 7719 (2021-02-18) ----------------------- Changes since builds 7591: - Disable SSO properties temporarily pending the completion of SSO QA. - Fix DATEDIFF function mappings to handle units of non-even length. This affects date differences using 'millennium', 'century', 'year', 'quarter', 'month' and 'week' as the difference unit. - Fix DATEDIFF/DATENAME/DATEPART when applied to 'week' so that Sunday is treated as the start of the week, in addition to fixes related to the other non-uniform units fixed within DATEDIFF. - Fix date functions to recognize the 'dayofweek' and 'dayofyear' units correctly. - Other minor query building fixes - Recognize the types of arithmetic operators - Recognize the type of the double-pipe operator and apply casts when necessary - Cast numeric literals using the literal synatx (integer '42') when required, instead of using CAST - NULL literals should not affect the types assigned to CAST expressions. Should avoid unnecessary casts to VARCHAR when a CASE returns a NULL. - Fix cases where columns resulting from a JOIN would always have NULL values. - Fix test connection so that it always validates the user's credentials, instead of potentially using a cached session and allowing login with invalid credentials. - Fix Tableau dialect so that date computations in alternate locales (using their own start-of-week day) return correct results. Build 7591 (2020-10-13) ----------------------- Changes since build 7513: - Move the Tableau UI back to the old user/password/login URL form supported by 2019.4+ - Mark tenten as the default catalog (this can be overridden via the Catalog connection property if necessary). This allows the driver's metadata cache to work for queries that use unqualified table names (for example, `"public"."date_dim"`). This was most pronounced in TDVT but may help with other configurations as well. - Add better retry handling for "tag not found" errors, which can happen after a previous retry created a new session. Instead of giving up after restart the session, the driver will forget the previous query ID and re-execute with a new one. - Support translations for additional functions not available in native 1010data: SQUARE(x) SUBSTRING(str FROM start FOR length) -- 3-arg form translated properly LOCATE(substr, str, start) -- Same TRUNC(x) -- Output type now integer CEILING(x) SPLIT(str, delim, idx) STARTSWITH(str, substr) ENDSWITH(str, substr) CONTAINS(str, substr) LEN(str) - Fix how GROUP BY clauses of conditionals are built. These are normalized into CASE expressions but this normalization wasn't applied consistently, leading to grouping errors: SELECT COUNT(*), x IS NULL =>| SELECT COUNT(*), CASE WHEN x IS NULL THEN TRUE FROM t | WHEN NOT (x IS NULL) THEN FALSE GROUP BY (x IS NULL) | ELSE NULL END, | FROM t | GROUP BY CASE ... END -- Previous translation inconsistent, used GROUP BY (x IS NULL) - Fix type annotations for aggregation functions so they are not cast unnecessarily - Check inputs for DATEDIFF to avoid sending date_part('...', NULL) to server. Internally date_part is written to a different expression which cannot accept NULL literals (this would manifest as an "to_char(NULL, ..." error) Build 7513 (2020-07-27) ----------------------- Changes since build 7458: - Add explicit logging for query rebuilding without having to see all transport logs. At verbosity level 2, these entries appear in the logs: [1010data] Finished translating query into 1010data syntax [1010data] Normalized input query: [SELECT MONTH(d_date) as m FROM dim_date] [1010data] Output query: [SELECT date_part('month', CAST("d_date" AS timestamp)) AS "m" FROM "tenten"."tpcds100"."dim_date"] - Authentication is now controlled via the AuthScheme property. If set (options are ADFS, OKTA, BASIC or AUTO) the driver will use that authentication scheme, overriding the UseSSO and SSOProvider option. If not set, the driver determines the authentication type as it would normally. - Fix date parsing issue which resulted in times being 5 or 6 hours off. Two issues here: one is that the driver was assuming times were UTC instead of local, and performing a timezone adjustment to bring them into local which was unnecessary. The other is that the epoch used for parsing dates was off by an hour. - Query building updates around date functions. Several date functions using dayofweek will return the correct value in the range 1-7, instead of the server value in 0-6. Add a mapping for the DATENAME function which uses date_part or to_char as necessary. - Import more function definitions to avoid unnecessary casts in some comparisons. This avoids a cast to varchar the driver emitted previously because it was unaware of the types of the values involved in the comparison. WHERE HOUR(d_date) = 5 WHERE CAST(date_part('hour', d_date) AS varchar) = '5' -- Old WHERE date_part(d_date) = 5 -- New Build 7458 (2020-06-02) ----------------------- Changes since build 7440: - Support for OKTA as an SSO IdP. This required adding an extra property called SSOProperties, which is used for properties specific to each IdP: // Driver needs to know the login URL (the page to the OKTA login portal) // as well as the organization's OKTA domain UseSSO=true; SSOProvider=OKTA; SSOLoginURL=https://ecom-discoverdev.oktapreview.com/home/ecomdiscoverdev_cdatasaml_1/0oars9leyiywxNyBj0h7/alnrs9rzcqXEiYOYL0h7; SSOProperties="Domain=ecom-discoverdev.oktapreview.com"; This is only required for OKTA if the user's domain and login portal don't match. In most cases the org's domain can be inferred from the login portal: // OKTA domain is automatically set to 'ecom-discoverdev.oktapreview.com' // based on SSOLoginURL UseSSO=true; SSOProvider=OKTA; SSOLoginURL=https://ecom-discoverdev.oktapreview.com/home/ecomdiscoverdev_cdatasaml_1/0oars9leyiywxNyBj0h7/alnrs9rzcqXEiYOYL0h7; SSOProperties is currently ignored when using ADFS, since it can connect with only a LoginURL. - Update the documentation to cover SSO connection properties in more detail. Both SSOLoginURL and the new SSOProperties option give examples for how they should be used with ADFS and OKTA. - Remove intro/connection setup section from the documentation. Build 7440 (2020-05-15) ------------------------ Changes since build 7404 (2020-04-09) - Strip CASTs from around date comparisons when they are unnecessary. Power BI has a tendency to generate queries like this where it performs a cast which is actually unnecessary: SELECT ... WHERE datecol > timestamp '2000-01-01 00:00:00' -- Old form: WHERE CAST(datecol AS timestamp) > timestamp '2000-01-01 00:00:00' - Disabled implicit casting around IS NULL / IS NOT NULL: SELECT ... WHERE x IS NOT NULL -- Old form: WHERE CAST(x AS varchar) IS NOT NULL - Add support for resolving types across subqueries to avoid CASTs where they are unnecessary. SELECT x, y FROM (SELECT anint AS x, anint AS y FROM t) WHERE x > y -- Old form: WHERE CAST(x AS varchar) > CAST(y AS varchar) - Add support for type resolution with CASE expressions so that unnecessary CASTs are not added impliciltly. This would usually happen during comparisons: SELECT ... WHERE 42 = (CASE ... THEN 42 END) -- Old form: WHERE 42 = CAST(CASE ... THEN 42 END AS integer) - Avoid reundant casts when a truncation is desired and the input is already numeric. For example, if x is a double column: SELECT CAST(x AS integer) FROM t -- Old form: SELECT CAST(CAST(x AS double precision) as integer) FROM t