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

          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.