Ambiguous Error Message for TDVT Failures

[ravjotbrar]

About You:
Name: Ravjot Brar
Company: Dremio

I have two tests failing (lod.10_As in-out Set and BUGS.B14080 ) that have the following error message:

Attempting to run query...
TableauException: Tableau cannot evaluate the query that results from this calculation.

Error running query

There's no SQL generated and I couldn't find anything useful in the logs. Based on my research I'm assuming this query tries to use a function that isn't defined in the dialect file. How do I find out which function exactly?

The rest of the ~60 failures have the following error message, which tells me they're a consequence of the two test failures above:

Error. Previous error message is: Attempting to run query...
TableauException: Tableau cannot evaluate the query that results from this calculation.

Error running query

Happy to provide any other info if needed.

[rosswbrown]

Can you attach your test_results_combined.csv file to start.

[ravjotbrar]

test_results_combined.csv

[rosswbrown]

I'd recommend looking at the datetime functions generally. For example from looking at the definition of BUGS.B14080, the function below is used here:

    <function group='operator' name='-' return-type='real'>
      <formula>...</formula>
      <argument type='datetime' />
      <argument type='datetime' />
    </function>
    

You can also attach your full artifacts here, or link to your repo. Please also include the version of TDVT you are running to see if the repeated error message is a known issue or not.

[ravjotbrar]

TDVT Version: 2.7.5

tableau-sdk-connector.zip

TDVTLogs.zip

[yurifal]

TDVT Version: 2.7.5

tableau-sdk-connector.zip

TDVTLogs.zip

Works for me after modifying the class tag and renaming the dialect file (to the dialect.tdd).
dremio_jdbc.zip

[ravjotbrar]

@yurifal Were you able to repro before the file name changes? I'm still getting an error in the tests.

[ravjotbrar]

@rosswbrown We did find the following in the tab_query logs for the two tests:

"FederatedQueryOptimizerImpl::DetermineConnForExecution: Could not find connection for evaluating all LogicalExps"

Not sure if this helps.

[yurifal]

@yurifal Were you able to repro before the file name changes? I'm still getting an error in the tests.

My bad, I've replied with the total nonsense.
Please ignore my reply and move on.
Best of luck!

[rosswbrown]

Just to follow up on the other comment, based on the logs you've provided I don't believe there is any need to rename the connector. My guess is this is your in development connector loading as expected:

{...k":"connector-plugin","v":"Successfully loaded connector for class dremio, plugin-version:4.0.0 from connector plugin C:\\Program Files\\Tableau\\Connectors\\dremio.taco"}

Looking at your test_results_combined.csv file I believe there is an issue in the results file after BUGS.B641638 test fails. Viewing the file manually you can see this query:

SELECT DATE_TRUNC('DAY', CAST(""Calcs"".""date0"" AS DATE)) AS ""tdy:Calculation_2683863928708153344:ok""
FROM ""@dremio"".""Calcs"" ""Calcs""
WHERE (DATE_TRUNC('DAY', CAST(""Calcs"".""date0"" AS DATE)) IN ((TIMESTAMP '1972-07-04 00:00:00.000'), (TIMESTAMP '1975-11-12 00:00:00.000')))
GROUP BY DATE_TRUNC('DAY', CAST(""Calcs"".""date0"" AS DATE))

There are additional logs in the tabquery_logs.zip\logical_calcs_dremio\ folder.

[rosswbrown]

It is possible the lack of time field handling into a DateTime type might be impacting the ability to run some tests like BUGS.B641638. Consider skipping with this and see if that unblocks better error messages in the subsequent tests:

[StandardTests]
LogicalExclusions_Calcs = BUGS.B641638

The No such function $IN_SET$ that takes arguments of type (str, datetime, datetime, datetime, datetime) failure is trying to test these are in the [time0] field:

21:07:32
22:42:43
1900-01-01 04:57:51
1900-01-01 18:51:48

This is an example of the query generated by that test:

SELECT "Calcs"."str1" AS "str1",
  SUM("Calcs"."num1") AS "sum:num1:ok",
  "Calcs"."time0" AS "time0"
FROM "Calcs"
WHERE ("Calcs"."time0" IN ((TIMESTAMP '1899-12-30 21:07:32.000'), (TIMESTAMP '1899-12-30 22:42:43.000'), (TIMESTAMP '1900-01-01 04:57:51.000'), (TIMESTAMP '1900-01-01 18:51:48.000')))
GROUP BY 1, 3

[ravjotbrar]

Tried excluding that test and got the same ambiguous error messages unfortunately.

[rosswbrown]

I think the best starting point may be to get the calcs_data.time to pass and match the expected file. This column is the basis for at least a few of the failing tests.

A connector's Date Literal Support is described in that link and some additional details within this previous answer. Since the connector attached above is using PostgreSQL90Dialect as a base it is likely this is the current implementation:

  <function-map>
    <function group='system' name='TIME1899' return-type='datetime'>
      <formula>(CAST('1899-12-30' AS DATE) + %1)</formula>
      <argument type='datetime' />
    </function>
...
  <sql-format>
    <format-date-literal formula="(DATE '%1')"  format='yyyy-MM-dd' />
    <format-datetime-literal formula="(TIMESTAMP '%1')" format='yyyy-MM-dd HH:mm:ss.SSS' />

If you're still seeing a large number of failures try using more general skips and reducing the number of test suites used as a starting point. For example:

[StandardTests]
LogicalExclusions_Staples = BUGS.*,Filter.*
LogicalExclusions_Calcs = BUGS.*,Filter.*

You can also remove suites entirely like [LODTests] and [UnionTest] from the ini file to help narrow the issue.

[ravjotbrar]

I'll give that a shot. Thanks @rosswbrown

I also noticed that all of the failures with the error messages are related to the Staples tests. Does that change our strategy for debugging at all?

[rosswbrown]

As you identified originally I think this a situation where one of the logical Staples tests is causing a cascading failure, similar to what is reported in #1123. My best guess is still to start with the suggestions above.

Previously we'd established if you can skip the failing test then the others will succeed. You are also able to run tests individually in this style, which may help:

Run one expression test:
tdvt.tdvt run-pattern <your_connector> --exp exprtests/standard/setup.date.datepart.second*.txt --tdp cast_calcs.<your_connector>.tds

Run one logical query test:
tdvt.tdvt run-pattern <your_connector> --logp logicaltests/setup/staples/setup.BUGS.B14080.<your_logical_config_name>.xml --tdp Staples.<your_connector>.tds

[ravjotbrar]

I've tried the suggestion of excluding tests failing with the following error message:

Attempting to run query...
TableauException: Tableau cannot evaluate the query that results from this calculation.

Error running query

I noticed if I skipped the tests with that error, a new test in the same suite would then fail with that error. I repeated the cycle until no tests were left in the logical staples test. Based on that assumption, there doesn't seem to be one single culprit, but the entire logical staples test suite.

I also excluded the calcs_data.time and operator.datetime.minus_time tests, but they didn't affect the tests mentioned above.

Here is the full list of the tests that I'm excluding:

[StandardTests]
LogicalExclusions_Calcs = BUGS.B641638
ExpressionExclusions_Standard = calcs_data.time, operator.datetime.minus_time 
LogicalExclusions_Staples = BUGS.*, Filter.*, Query.*

[UnionTest]

[ConnectionTests]
StaplesTestEnabled = True
CastCalcsTestEnabled = True

[rosswbrown]

One thing I've determined is that certain exceptions during query formation, like the one you're seeing above, will cause the rest of the tests in the suite to be skipped. Currently this is by design, though we're exploring changing that in a future release to make this easier to develop with.

I see the connector attached is an update to an existing one, which I believe passed these same tests previously. I'm not sure what else to suggest in isolation. Perhaps you can verify the previous version and/or driver to help eliminate some variables.

In general four independent factors could cause a test to fail:

1. the test endpoint does not contain the correct column type and data values
2. the connector implementation
3. the driver used by the connector
4. the Tableau product version of tabquerytool.exe

[rosswbrown]

Let us know if you need this issue reopened or feel free to open another as well. Thanks.

[ravjotbrar]

Thanks for your help Ross. It seems that the test data was being loaded incorrectly into our data source. I've loaded it into a postgres source within Dremio and noticed all those errors went away.