4 Replies Latest reply on Apr 5, 2018 10:06 AM by Dan Cory

    [bug report] SQL query problem in Tableau 10.5.2

    Joe Smith

      The sql query issue I encountered is described below step-by-step, which I hope can be reproduced on other computers.

       

      1. To read SQLite database file in Tableau, the following SQL driver was installed on my computer:

       

      SQLite 32-bit ODBC driver for Windows:

       

      2. The SQLite database file (20MB) used in the example Tableau workbook can be downloaded from this link1 or link2. It is created from a building energy modeling software EnergyPlus which contains several data tables.

       

      3. To read this SQLite database file, choose Other Databases (ODBC) after Tableau is open:

      Image 6.png

       

      4. select Connect after SQLite 3 ODBC Driver is selected (assuming the SQLite driver is installed) :

      Image 7.png

       

      5. select the SQLite database file downloaded from the link above:

      Image 8.png

       

      6. click Sign In to connect to the SQLite database file:

      Image 9.png

       

      7. go to the Data Source section:

      Image 10.png

       

      8. search and import the ReportDataDictionary table as shown below:

      Image 11.png

       

      9. search and import the ReportData table, and link it to the previous table:

      Image 12.png

       

      10. search and import the Zones table:

      Image 13.png

       

      11. change the table join method to Left with the following fields linked:

      Image 14.png

       

      12. the data of the linked tables shall look like this:

      Image 15.png

       

      13. I created a new calculated field to sum the floor area for all the building zones:

      Image 16.png

       

      14. However, when I drag this calculated measure to the sheet, I got the following error message:

      Image 18.png

       

      15. and the problematic query as shown above is:

      SELECT "t2"."X_measure__E" AS "usr_Calculation_BECIHGHADDJHDBCFBCA_ok"

      FROM (

        SELECT MIN(1) AS "empty"

        FROM "ReportDataDictionary"

          INNER JOIN "ReportData" ON ("ReportDataDictionary"."ReportDataDictionaryIndex" = "ReportData"."ReportDataDictionaryIndex")

        HAVING (COUNT(1) > 0)

      ) "t0"

        CROSS JOIN (

        SELECT SUM("t1"."X_measure__C") AS "X_measure__E"

        FROM (

          SELECT MIN("Zones"."FloorArea") AS "X_measure__C",

            "Zones"."ZoneIndex" AS "ZoneIndex"

          FROM "ReportDataDictionary"

            INNER JOIN "ReportData" ON ("ReportDataDictionary"."ReportDataDictionaryIndex" = "ReportData"."ReportDataDictionaryIndex")

            LEFT JOIN "Zones" ON ("ReportDataDictionary"."KeyValue" = "Zones"."ZoneName")

          GROUP BY 2

        ) "t1"

        HAVING (COUNT(1) > 0)

      ) "t2"

       

      17. The same workflow is all good in Tableau 10.4. So, I'd appreciate if anybody or the Tableau support team can take a look and advise if this is a bug in Tableau 10.5.2 to deal with SQL database file.

       

      Thank you.

        • 1. Re: [bug report] SQL query problem in Tableau 10.5.2
          Dan Cory

          Joe -

          My advice from your last post still applies.

          My guess is that Tableau 10.5 added some query optimization which generates SQL that SQLite doesn't like. If Tableau 10.5 has a "Show Details" button, it should show the actual error message from SQLite, which would be quite helpful. You should also be able to find that in the logs.

          If you could post a performance recording of the workbook succeeding in Tableau 10.4 which would include the query that succeeds, that would be useful.

           

          I'm afraid that the elaborate set of steps needed to reproduce your problem are more than I have time for right now.

           

          Dan

          • 2. Re: [bug report] SQL query problem in Tableau 10.5.2
            Dan Cory

            I had some time so I came back to this.

             

            We don't actually show an error message with a "Show Details" button, which is quite odd. But it looks like the SQLite ODBC driver may not be returning an error message string.

             

            The queries are somewhat different, due to some optimizations we made in 10.5.

             

            I suspect SQLite is balking at the GROUP BY that refers to an aggregate:

              SELECT MIN(1) AS "empty"

              FROM "ReportDataDictionary"

                INNER JOIN "ReportData" ON ("ReportDataDictionary"."ReportDataDictionaryIndex" = "ReportData"."ReportDataDictionaryIndex")

              GROUP BY 1

             

            I got it to work after adding the following TDC customizations:

            CAP_QUERY_INCLUDE_GROUP_BY_COLUMNS_IN_SELECT = yes

            CAP_QUERY_HAVING_REQUIRES_GROUP_BY = yes

             

            See Customize and Tune ODBC Connections  for instructions on using a TDC.

             

            Dan

            1 of 1 people found this helpful
            • 3. Re: [bug report] SQL query problem in Tableau 10.5.2
              Joe Smith

              Dear Dan Cory, thank you very much for taking your time to solve this problem.

               

              Yes, your suggestion works like a charm.

               

              Following your advice, I created a odbc-sqlite.tdc file with the customization lines you suggested (as shown beolow) and put it in the C:\Users\YOUR_USER_NAME\Documents\My Tableau Repository\Datasources directory, and Tableau 10.5.2 can read the SQLite database correctly without the query issue as reported.

               

              I wonder if Tableau shall consider a more permanent solution to this issue if this may affect all aggregation related query for SQL database, and it'll be great to avoid the manual work of creating the .tbc customization file whenever the workbook is open on a new computer.

               

              Nevertheless, a very huge thanks to you because I can open my old workbook with all those templates reading SQLite database in the latest Tableau 10.5 now.

               

              <?xml version='1.0' encoding='utf-8' ?>

               

              <connection-customization class='genericodbc' enabled='true' version='10.5'>

               

              <vendor name='SQLite' />

               

              <driver name='SQLite3 ODBC Driver' />

               

                  <customizations>

               

                      <customization name='CAP_QUERY_INCLUDE_GROUP_BY_COLUMNS_IN_SELECT' value='yes' />

               

                      <customization name='CAP_QUERY_HAVING_REQUIRES_GROUP_BY' value='yes' />

               

                  </customizations>

               

              </connection-customization>

              • 4. Re: [bug report] SQL query problem in Tableau 10.5.2
                Dan Cory

                I'm glad it worked.

                 

                We've never had a built-in connection for SQLite. There's not a ton of demand.

                 

                We do have a problem that many configurations of capabilities lead us to generate SQL in some situations that is unlikely to be legal. And some of those capabilities can be easily chosen by either our capability detection code or users changing TDCs. I don't have a great story for how to make this easier, but it is an issue we are aware of.

                 

                Dan