Compter les lignes renvoyées par une requête qui plante

La mesure de l’échec

      Il est possible dans une requête SQL d’utiliser une expression dans la clause de projection, cette expression pouvant être une sous-requête. Cela signifie que la syntaxe SELECT col1, col2, SELECT ..., col3 FROM nom_table est correcte.
      La limitation est que le SELECT utilisé comme expression doit ici renvoyer au plus UNE ligne pour chaque ligne de la table NOM_TABLE. A défaut, vous obtiendrez une erreur explicite ERREUR: plus d'une ligne renvoyée par une sous-requête utilisée comme une expression.
      Il est par ailleurs possible de compter les résultats renvoyés par une requête en écrivant SELECT cout(*) FROM (SELECT ...) ALIAS.
      Vous pouvez combiner ces deux possibilités avec une requête du style SELECT count(*) FROM (SELECT col1, col2, SELECT ..., col3 FROM nom_table) ALIAS.
      Intuitivement, nous pourrions penser que la limitation citée précédemment s’applique toujours et que si la requête (SELECT col1, col2, SELECT ..., col3 FROM nom_table) plante alors la requête SELECT count(*) FROM (SELECT col1, col2, SELECT ..., col3 FROM nom_table) ALIAS plantera également.
      Qu’en est-il en pratique ? D√©monstration avec PostgreSQL 10 :

select version(); version -------------------------------------------------------------------------------------------------- PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.2.0-12) 7.2.1 20171025, 64-bit (1 ligne) CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; CREATE EXTENSION create table geants( idg integer primary key, idgu uuid, nmge varchar(128), devise varchar(128)); CREATE TABLE with recursive serie(i) as (select 1 UNION ALL select i+1 from serie where i < 500000) insert into geants(idg, idgu, nmge, devise) select i, uuid_generate_v4(), upper(md5(random()::text)), upper(md5(random()::text)) from serie; INSERT 0 500000 create table lancers( idg integer, perf integer); CREATE TABLE with recursive serie(i) as (select 1 UNION ALL select i+1 from serie where i < 500000) insert into lancers(idg, perf) select trunc(random() * 500000 + 1), case when random() <= 0.0001 then 50000 + trunc(random() * 50000 + 1) else trunc(random() * 50000 + 1) end from serie; INSERT 0 500000 select g.idg, (select perf from lancers l where l.idg = g.idg) from geants g; ERREUR: plus d'une ligne renvoyée par une sous-requête utilisée comme une expression select count(*) from (select g.idg, (select perf from lancers l where l.idg = g.idg) from geants g) r; count -------- 500000 (1 ligne) explain select count(*) from (select g.idg, (select perf from lancers l where l.idg = g.idg) from geants g) r; QUERY PLAN ------------------------------------------------------------------------------------------- Finalize Aggregate (cost=10747.38..10747.39 rows=1 width=8) -> Gather (cost=10747.17..10747.38 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=9747.17..9747.18 rows=1 width=8) -> Parallel Seq Scan on geants g (cost=0.00..9226.33 rows=208333 width=0) (5 lignes) Temps : 1,056 ms with r as (select g.idg, (select perf from lancers l where l.idg = g.idg) from geants g) select count(*) from r; ERREUR: plus d'une ligne renvoyée par une sous-requête utilisée comme une expression explain with r as (select g.idg, (select perf from lancers l where l.idg = g.idg) from geants g) select count(*) from r; QUERY PLAN ------------------------------------------------------------------------------ Aggregate (cost=4231523393.00..4231523393.01 rows=1 width=8) CTE r -> Seq Scan on geants g (cost=0.00..4231512143.00 rows=500000 width=8) SubPlan 1 -> Seq Scan on lancers l (cost=0.00..8463.00 rows=2 width=4) Filter: (idg = g.idg) -> CTE Scan on r (cost=0.00..10000.00 rows=500000 width=0) (7 lignes)

      Cornegidouille ! Le premier select count(*) ne plante pas en raison d'une optimisation. Le planner PostgreSQL ne considère pas la clause de projection qui n’est pas censée avoir une influence sur le nombre de lignes renvoyées. Le plan d’exécution ne fait donc aucune référence à la table lancers. Il n’y a ici aucune clause de restriction WHERE, le planner en conclut que le nombre de lignes à compter correspond au nombre de lignes de la table geants.
      La situation est différente avec une CTE (with query) en raison du comportement du planner en ce qui les concerne. A part si elle n’est pas citée dans la requête principale, une with query est systématiquement exécutée avec PostgreSQL, ce qui explique le plantage du select count(*) dans ce cas.
      A présent, réalisons le même test avec Oracle Database 12.2.0.1 :

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production create table geants( idg integer primary key, idgu raw(16), nmge varchar(128), devise varchar(128)) tablespace users; Table created. insert into geants(idg, idgu, nmge, devise) with serie(i) as (select 1 from dual UNION ALL select i+1 from serie where i < 500000) select i, sys_guid(), dbms_random.string('P', 32) , dbms_random.string('P', 32) from serie; 500000 rows created. create table lancers( idg integer, perf integer); Table created. insert into lancers(idg, perf) with serie(i) as (select 1 from dual UNION ALL select i+1 from serie where i < 500000) select trunc(dbms_random.value*500000+1) , trunc(dbms_random.value*50000+1) from serie; 500000 rows created. select g.idg, (select perf from lancers l where l.idg = g.idg) from geants g; ERROR: ORA-01427: single-row subquery returns more than one row select count(*) from (select g.idg, (select perf from lancers l where l.idg = g.idg) from geants g) r; COUNT(*) ---------- 500000 explain plan for select count(*) from (select g.idg, (select perf from lancers l where l.idg = g.idg) from geants g) r; Explained. select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 3332795107 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 272 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| SYS_C005652 | 524K| 272 (0)| 00:00:01 | ----------------------------------------------------------------------------- Note PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- ----- - dynamic statistics used: dynamic sampling (level=2) 13 rows selected. with r as (select g.idg, (select perf from lancers l where l.idg = g.idg) from geants g) select count(*) from r; COUNT(*) ---------- 500000 explain plan for with r as (select g.idg, (select perf from lancers l where l.idg = g.idg) from geants g) select count(*) from r; Explained. select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 3332795107 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 272 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| SYS_C005652 | 524K| 272 (0)| 00:00:01 | ----------------------------------------------------------------------------- Note PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----- - dynamic statistics used: dynamic sampling (level=2) 13 rows selected. with r as (select /*+ materialize */ g.idg, (select perf from lancers l where l.idg = g.idg) from geants g) select count(*) from r; with r as (select /*+ materialize */ g.idg, (select perf from lancers l where l.idg = g.idg) * ERROR at line 1: ORA-01427: single-row subquery returns more than one row explain plan for with r as (select /*+ materialize */ g.idg, (select perf from lancers l where l.idg = g.idg) from geants g) select count(*) from r; Explained. select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 4125088535 ---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 895K (1)| 00:00:35 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D660D_1185C1 | | | | | |* 3 | TABLE ACCESS FULL | LANCERS | 4459 | 113K| 2 (0)| 00:00:01 | | 4 | INDEX FAST FULL SCAN | SYS_C005652 | 524K| 6657K| 272 (0)| 00:00:01 | | 5 | SORT AGGREGATE | | 1 | | | | | 6 | VIEW | | 524K| | 256 (2)| 00:00:01 | | 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660D_1185C1 | 524K| 6657K| 256 (2)| 00:00:01 | ---------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter("L"."IDG"=:B1) Note ----- - dynamic statistics used: dynamic sampling (level=2) 23 rows selected.

      Oracle Database réalise le même type d’optimisation et le comportement observé est donc ici largement similaire à celui observé avec PostgreSQL.
      Seule différence, les with queries ne sont pas systématiquement exécutées avec Oracle Database, à part si le hint /*+ materialize */ est utilisé. Cela explique qu’aucun plantage ne survienne sur le deuxième SELECT count(*) alors que le dernier plante.

Mise à jour : 01/12/2017