Delen via

Foutklasse DIVIDE_BY_ZERO

  • Artikel

SQLSTATE: 22012

Delen door nul. Gebruik try_divide om te tolereren dat deler 0 is en in plaats daarvan NULL retourneert. Stel indien nodig <config> in op 'false' om deze fout te omzeilen.

Parameters

  • ansiConfig: de naam van de configuratie om het gedrag te wijzigen.

Uitleg

Azure Databricks genereert deze fout wanneer er wordt geprobeerd een INTERVAL of numeriek te delen door 0. De contextinformatie van deze fout isoleert het object en de expressie waarin de fout is opgetreden. Functies en operators zoals mod, die deze fout kunnen veroorzaken, omvatten degenen die delingen uitvoeren als onderdeel van complexere formules.

Mitigatie

De beperking van de fout is afhankelijk van de oorzaak:

  • Is de expressie die de fout veroorzaakt correct?

    Als de expressie onjuist is, corrigeer het zodat de 0 waarde niet kan optreden, en probeer de query opnieuw uit te voeren.

  • Zijn de gegevens juist?

    Als de invoergegevens kunnen resulteren in de 0 waarden die worden doorgegeven, moet u mogelijk de gegevens in de bron herstellen of opschonen voordat de gegevens als argument aan de functie worden doorgegeven.

    Het opschonen van gegevens kan betekenen dat de offending rijen worden uitgesloten, om de 0 waarden te converteren naar NULL met behulp van nullif(expr, 0)of om de gegevens te converteren naar een andere acceptabele waarde met behulp van if(expr = 0, alt, expr).

Als de expressie en de gegevens juist zijn en u de deling wilt tolereren door nul, kunt u try_dividegebruiken. Als alternatief wijzigt u het argument in nullif(expr, 0). Hierdoor wordt de expressie NULL geretourneerd in plaats van een fout. Als u wilt, kunt u nvl(try_divide(expr1, expr2), alt) gebruiken om de resulterende NULL om te zetten in alternatieve waarden zoals neutrale elementen voor optelling 0 of vermenigvuldiging 1.

Als oplossing van laatste redmiddel, wanneer de expressie of gegevensstroom niet kan worden gewijzigd, kunt u dit ANSI-gedrag uitschakelen door de opgegeven ansiconfig in te stellen op false. Houd er rekening mee dat deze instelling gevolgen heeft voorbij de onmiddellijke foutsituatie.

Voorbeelden

-- A DIVIDE_BY_ZERO in a embedded in view. The context information isolates the faiing function.
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT 1/val FROM VALUES(1), (0) AS T(val);
> SELECT c1 FROM v;
  [DIVIDE_BY_ZERO] Division by zero. To return NULL instead, use `try_divide`. If necessary set "spark.sql.ansi.enabled" to false (except for ANSI interval type) to bypass this error.
  == SQL of VIEW v(line 1, position 7) ==
  SELECT 1/val FROM VALUES(1), (0) AS T(val)
         ^^^^^

-- Tolerating division by zero by turning the result to NULL using try_divide.
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT try_divide(1, val) FROM VALUES(1), (0) AS T(val);
> SELECT c1 FROM v;
  1
  NULL

-- Tolerating division by zero by turning the result to NULL using nullif
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT 1 / nullif(val, 0) FROM VALUES(1), (0) AS T(val);
> SELECT c1 FROM v;
  1
  NULL

-- Filtering out offensive rows
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT 1/val FROM VALUES(1), (0) AS T(val) WHERE val != 0;
> SELECT c1 FROM v;
  1

-- Turning division by zero into division by a small number.
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT 1 / if(val = 0, 1e-10, val) FROM VALUES(1), (0) AS T(val);
> SELECT c1 FROM v;
  1
  10000000000

-- Turning division by zero into a neutral element for addition.
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT nvl(try_divide(1, val), 0) FROM VALUES(1), (0) AS T(val);
> SELECT c1 FROM v;
  1
  0

-- Disabling ANSI mode in Databricks SQL for the view definition only.
> SET ANSI_MODE = false;
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT 1/val FROM VALUES(1), (0) AS T(val);
> SET ANSI_MODE = true;

> SELECT c1 FROM v;
  1
  NULL

-- Disabling ANSI mode in Databricks Runtime for the view definition only.
> SET spark.sql.ansi.enabled = false;
> CREATE OR REPLACE TEMPORARY VIEW v(c1) AS SELECT 1/val FROM VALUES(1), (0) AS T(val);
> SET spark.sql.ansi.enabled = true;

> SELECT c1 FROM v;
  1
  NULL