Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-4693

mysql "booleans" translation issues

    Details

    • Steps to Reproduce:
      Hide

      eg, on a sample (real) table:
      select ag.closed from ag/* => 29917 total records */

      /* looking for true */
      select ag.closed from ag as ag where ag.closed ; /* => 0 results */
      select ag.closed from ag as ag where ag.closed = true/* => 0 results */
      select ag.closed from ag as ag where ag.closed != false/* => 293 results */
      select ag.closed from ag as ag where NOT (ag.closed = false)/* => 293 results */
      select ag.closed from ag as ag where ag.closed != 0 /* 293 rows */
      select ag.closed from ag as ag where ag.closed = -1 /* 0 rows */

      /* looking for false */
      select ag.closed from ag as ag where NOT ag.closed /* => 29917 results */
      select ag.closed from ag as ag where ag.closed = false/* => 29624 results */
      select ag.closed from ag as ag where ag.closed != true/* => 29917 results */
      select ag.closed from ag as ag where NOT (ag.closed = true)/* => 29917 results */
      select ag.closed from ag as ag where ag.closed = 0 /* 29624 rows */
      select ag.closed from ag as ag where ag.closed != -1 /* 29917 rows */

      Show
      eg, on a sample (real) table: select ag.closed from ag/* => 29917 total records */ /* looking for true */ select ag.closed from ag as ag where ag.closed ; /* => 0 results */ select ag.closed from ag as ag where ag.closed = true/* => 0 results */ select ag.closed from ag as ag where ag.closed != false/* => 293 results */ select ag.closed from ag as ag where NOT (ag.closed = false)/* => 293 results */ select ag.closed from ag as ag where ag.closed != 0 /* 293 rows */ select ag.closed from ag as ag where ag.closed = -1 /* 0 rows */ /* looking for false */ select ag.closed from ag as ag where NOT ag.closed /* => 29917 results */ select ag.closed from ag as ag where ag.closed = false/* => 29624 results */ select ag.closed from ag as ag where ag.closed != true/* => 29917 results */ select ag.closed from ag as ag where NOT (ag.closed = true)/* => 29917 results */ select ag.closed from ag as ag where ag.closed = 0 /* 29624 rows */ select ag.closed from ag as ag where ag.closed != -1 /* 29917 rows */
    • Workaround Description:
      Hide

      some forms of looking for "false" work, and also their negative when looking for true.
      also, it could be intercepted at vdb/xml level, as DDL and/or with a view (but impractical for many tables/fields)

      Show
      some forms of looking for "false" work, and also their negative when looking for true. also, it could be intercepted at vdb/xml level, as DDL and/or with a view (but impractical for many tables/fields)

      Description

      Fields defined as tinyint(1) in MySQL are reported as a bit type in JDBC, which is then mappeed in Teiid to boolean. However tinyint(1) does not behave as you would expect with bit. (see forum reference below for more details) at least, for instance, when it contains 0 as false and -1 as true (typical by vb/msaccess usage)

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                m.ardito Marco Ardito
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: