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

mysql "booleans" translation issues

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Critical Critical
    • 9.2, 9.1.3
    • 9.x
    • JDBC Connector
    • None
    • 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 */
    • 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)

      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)

              rhn-engineering-shawkins Steven Hawkins
              m.ardito Marco Ardito (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: