Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-674

Update a column with a subquery resulted NULLs in the target column

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 1.0.8, 1.1.0
    • 1.0.10
    • DMLProc
    • None
    • 2017-14

    Description

      Build tested: 1.0.8-1 and develop branch
      [root@localhost ~]# mcsmysql mytest
      Welcome to the MariaDB monitor. Commands end with ; or \g.
      Your MariaDB connection id is 6
      Server version: 10.1.22-MariaDB Columnstore 1.0.8-1

      Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

      MariaDB [mytest]> create table t1 (c1 int, c2 char(5)) engine=columnstore;
      Query OK, 0 rows affected (0.21 sec)

      MariaDB [mytest]> create table t2 (c1 int, c2 char(5)) engine=columnstore;
      Query OK, 0 rows affected (0.15 sec)

      MariaDB [mytest]> insert into t1 values (5, 'aa');
      Query OK, 1 row affected (0.13 sec)

      MariaDB [mytest]> insert into t1 values (5, 'aa');
      Query OK, 1 row affected (0.06 sec)

      MariaDB [mytest]> insert into t1 values (5, 'aa');
      Query OK, 1 row affected (0.06 sec)

      MariaDB [mytest]> insert into t1 values (5, 'aa');
      Query OK, 1 row affected (0.05 sec)

      MariaDB [mytest]> insert into t1 values (5, 'aa');
      Query OK, 1 row affected (0.06 sec)

      MariaDB [mytest]> insert into t2 values (1, 'aa');
      Query OK, 1 row affected (0.09 sec)

      MariaDB [mytest]> insert into t2 values (1, 'aa');
      Query OK, 1 row affected (0.06 sec)

      MariaDB [mytest]> insert into t2 values (1, 'aa');
      Query OK, 1 row affected (0.06 sec)

      MariaDB [mytest]> insert into t2 values (1, 'aa');
      Query OK, 1 row affected (0.07 sec)

      MariaDB [mytest]> insert into t2 values (1, 'aa');
      Query OK, 1 row affected (0.05 sec)

      MariaDB [mytest]> select * from t1;
      ----------+

      c1 c2

      ----------+

      5 aa
      5 aa
      5 aa
      5 aa
      5 aa

      ----------+
      5 rows in set (0.04 sec)

      MariaDB [mytest]> select * from t2;
      ----------+

      c1 c2

      ----------+

      1 aa
      1 aa
      1 aa
      1 aa
      1 aa

      ----------+
      5 rows in set (0.02 sec)

      MariaDB [mytest]> update t1 set t1.c1=(select distinct c1 from t2);
      Query OK, 5 rows affected (0.07 sec)
      Rows matched: 0 Changed: 0 Warnings: 0

      MariaDB [mytest]> select * from t1;
      ----------+

      c1 c2

      ----------+

      NULL aa
      NULL aa
      NULL aa
      NULL aa
      NULL aa

      ----------+
      5 rows in set (0.02 sec)

      MariaDB [mytest]> update t1 set c1=5;
      Query OK, 5 rows affected (0.11 sec)
      Rows matched: 0 Changed: 0 Warnings: 0

      MariaDB [mytest]> select * from t1;
      ----------+

      c1 c2

      ----------+

      5 aa
      5 aa
      5 aa
      5 aa
      5 aa

      ----------+
      5 rows in set (0.02 sec)

      MariaDB [mytest]> update t1 set t1.c1=(select c1 from t2 limit 1);
      Query OK, 5 rows affected, 1 warning (0.07 sec)
      Rows matched: 0 Changed: 0 Warnings: 1

      MariaDB [mytest]> show warnings;
      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Level Code Message

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.

      ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      1 row in set (0.00 sec)

      MariaDB [mytest]> select * from t1;
      ----------+

      c1 c2

      ----------+

      NULL aa
      NULL aa
      NULL aa
      NULL aa
      NULL aa

      ----------+
      5 rows in set (0.02 sec)

      MariaDB [mytest]>

      Attachments

        Activity

          People

            dleeyh Daniel Lee (Inactive)
            dleeyh Daniel Lee (Inactive)
            Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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