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

View has to fail with error ER_SUBQUERY_NO_1_ROW (1242) but it doesn't

    XMLWordPrintable

Details

    Description

      When the definition of view contains subselect with aggregation function COUNT() that returns more 1 row it has to fail with error ER_SUBQUERY_NO_1_ROW (1242) (or smth. like that ) but it doesn't

      Test:

      CREATE TABLE t1 (a INTEGER, b INTEGER);
      CREATE TABLE t2 (x INTEGER);
      INSERT INTO t1 VALUES (1,11), (2,22), (2,22);
      INSERT INTO t2 VALUES (1), (2);
       
      CREATE VIEW v1 AS SELECT (SELECT COUNT(b) FROM t2) FROM t1;
      SELECT * from v1;
       
      DROP VIEW v1;
      DROP TABLES t1,t2;
      

      Actual result:

      (SELECT COUNT(b) FROM t2)
      2
      2
      2
      

      Using EXPLAIN EXTENDED it is seen the difference between:
      original select:

      EXPLAIN EXTENDED SELECT (SELECT COUNT(b) FROM t2) FROM t1;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
      2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1276	Field or reference 'test.t1.b' of SELECT #2 was resolved in SELECT #1
      Note	1981	Aggregate function 'count()' of SELECT #2 belongs to SELECT #1
      Note	1003	/* select#1 */ select <expr_cache><`test`.`t1`.`b`,count(`test`.`t1`.`b`),count(`test`.`t1`.`b`)>((/* select#2 */ select count(`test`.`t1`.`b`) from `test`.`t2`)) AS `(SELECT COUNT(b) FROM t2)` from `test`.`t1`
      

      and select from view:

      CREATE VIEW v1 AS SELECT (SELECT COUNT(b) FROM t2) FROM t1;
      EXPLAIN EXTENDED  SELECT * from v1;
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
      3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1276	Field or reference 'test.t1.b' of SELECT #3 was resolved in SELECT #2
      Note	1003	/* select#1 */ select <expr_cache><`test`.`t1`.`b`>((/* select#3 */ select count(`test`.`t1`.`b`) from `test`.`t2`)) AS `(SELECT COUNT(b) FROM t2)` from `test`.`t1`
      

      Attachments

        Issue Links

          Activity

            People

              igor Igor Babaev
              lstartseva Lena Startseva
              Votes:
              0 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.