Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-35784

Inconsistent Behavior Between Materialized Table and View When Using BIN() Function on LINESTRING Data

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.5.2
    • 11.4
    • None
    • None

    Description

      The query behavior between a materialized table and a view created from the same source table, which contains LINESTRING and POINT types, is inconsistent when using the BIN() function.

      Steps to Reproduce:

      1. Create Table t0 and Insert Data

      CREATE TABLE t0 (c0 LINESTRING, c1 POINT);
      		 
      INSERT INTO t0 (c0, c1) VALUES 
          (LineFromText('LINESTRING(110.625891 23.647987, 131.761062 -22.036987)'), PointFromText('POINT(-31.973521 -32.253348)')),
      		 
          (LineFromText('LINESTRING(-64.099221 85.347816, 83.476771 49.964253, 67.395816 -68.661706, 89.535845 -60.182826)'), PointFromText('POINT(72.132833 50.949942)')),
      		 
          (LineFromText('LINESTRING(30.138314 30.867908, 36.898225 63.201669, 167.383497 51.478527, 114.171311 55.291141, -88.146277 -78.797587)'), PointFromText('POINT(-90.396609 66.746729)'));

      2. Create Materialized Table t1 and Query

      MariaDB [test]> CREATE TABLE t1 AS (SELECT (BIN(c0)) AS c0 , c1 AS c1 FROM t0 );
      		 
      Query OK, 3 rows affected (0.018 sec)
      		 
      Records: 3  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [test]> SELECT c1 FROM t1 WHERE (CASE WHEN ((c0) > (PointFromText('POINT(159.026499 76.330577)'))) THEN (('WRHVP0tOYN')) ELSE (('8kUtsNTg5m')) END);
      		 
      +---------------------------+
      		 
      | c1                        |
      		 
      +---------------------------+
      		 
      |        8?@m @        |
      		 
      |        R@eyI@       |
      		 
      |        '
      		 
      bVc~nhʯP@     |
      		 
      +---------------------------+
      		 
      3 rows in set, 3 warnings (0.001 sec)

      3. Create t1 as VIEW instead:

      MariaDB [test]> CREATE VIEW t1 AS (SELECT (BIN(c0)) AS c0 , c1 AS c1 FROM t0 );
      		 
      Query OK, 0 rows affected (0.007 sec)
      		 
       
      		 
      MariaDB [test]> SELECT c1 FROM t1 WHERE (CASE WHEN ((c0) > (PointFromText('POINT(159.026499 76.330577)'))) THEN (('WRHVP0tOYN')) ELSE (('8kUtsNTg5m')) END);
      		 
      Empty set, 3 warnings (0.005 sec)

      Expected Behavior:

      Both the materialized table (t1) and the view (t1) should return the same result for the SELECT query.

      Observed Behavior:

      Materialized Table (t1) Query: Returns 3 rows.
      View (t1) Query: Returns an empty set, even though the data in the underlying table is identical.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35787 Inconsistent Query Results Between Materialized Table and View with ST_AsGeoJSON and Spatial Data

          • Major - Major loss of function.
          • Open

          Activity

            There are no comments yet on this issue.

            People

              bar Alexander Barkov
              dwenking chaos
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.