Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.9, 10.2(EOL)
    • 10.2.11
    • Optimizer - CTE
    • None
    • Windows 10

    Description

      I meet an issue with a recursive CTE.
      The error message is :
      [Exception, Error code 1 146, SQLState 42S02] Table 'mysql.gen_lignes' doesn't exist
      Line 3, column 1

      Here is the query (which works fine on PostgreSQL). Thank's a lot for you help :

      -- tableau d'amortissement d'immobilisation
      WITH
      tmp_valinitiales (capital, taux, annee_depart, nb_mois_an1, nb_annuites) AS (
       SELECT CAST(100000 AS DECIMAL(11, 0)), -- capital initial
         CAST((10+0.0) / 100 AS DECIMAL(5, 2)), -- taux d'amortissment
         CAST(2001 AS INTEGER), -- année de départ
         CAST(6 AS INTEGER), -- nbre mois pour calcul 1ère annuitée
         CAST(10 AS INTEGER) -- nombre d'années d'amortissement
      ),
      -- Conversion de certaines valeurs initiales en format décimal et précalcul de certaines données
      tmp_valdepart (capital, taux, annee_depart, nb_mois_an1, prorata, nb_annuites) AS (
       SELECT capital, taux, annee_depart, nb_mois_an1,
         CAST( CAST(nb_mois_an1 AS DEC(2, 0)) / 12.0 AS DEC(5, 4)) as prorata, -- prorata de la première annuité au format décimal
         CASE WHEN nb_mois_an1 = 12 THEN nb_annuites ELSE nb_annuites + 1 END AS nb_annuites -- nombre d'années d'amortissement de type entier
       FROM tmp_valinitiales
      ),
      -- Génération des lignes du tableau
      gen_lignes AS (
          SELECT cast(nx as integer) as val_inc FROM (
              WITH RECURSIVE gen_lignes(nx) AS (
                  SELECT 1 as n1 
                  UNION ALL
                  SELECT nx+1 as n2 FROM gen_lignes WHERE nx < (SELECT nb_annuites FROM  tmp_valdepart)
              ) SELECT nx FROM gen_lignes
          ) AS X
      ),
      -- Calcul d'un premier tableau d'annuités théoriques
      tmp_tableau1 as (
       SELECT val_inc, (SELECT taux FROM tmp_valdepart) AS taux,
         (SELECT capital FROM tmp_valdepart) AS capital_initial,
         CASE WHEN val_inc = 1 THEN
           -- la première année n'est pas forcément une année pleine, d'où application d'un prorata temporis sur la mensualité
           (SELECT capital FROM tmp_valdepart) * (SELECT taux FROM tmp_valdepart) * (SELECT prorata FROM tmp_valdepart)
         ELSE
           -- mensualité théorique pour une année pleine
           (SELECT capital FROM tmp_valdepart) * (SELECT taux FROM tmp_valdepart)
         END AS annuites  
       FROM gen_lignes
      ),
      -- Second tableau théorique incluant le calcul du CRD
      tmp_tableau2 AS (
       SELECT a.val_inc, a.capital_initial, a.annuites,
          a.capital_initial - (SELECT SUM(annuites) FROM tmp_tableau1 x WHERE x.val_inc <= a.val_inc) AS crd
       FROM tmp_tableau1 a
      )
      SELECT *
      FROM tmp_tableau1
      ;
      

      Attachments

        Issue Links

          Activity

            elenst Elena Stepanova added a comment - - edited

            Thanks for the report and test case.

            I'm not sure whether it's the same problem as MDEV-13780, I'll leave it to igor to decide.
            Here is a simplified version of the same query:

            WITH
            cte1 AS (
              SELECT n FROM (
                WITH RECURSIVE rec_cte(n) AS (
                  SELECT 1 as n1 
                  UNION ALL
                  SELECT n+1 as n2 FROM rec_cte WHERE n < 3
                ) SELECT n FROM rec_cte
              ) AS X
            ),
            cte2 as (
             SELECT 2 FROM cte1
            )
            SELECT *
            FROM cte1;
            

            MariaDB 10.2 result

            ERROR 1146 (42S02): Table 'test.rec_cte' doesn't exist
            

            PostgreSQL 9.6 result

             n 
            ---
             1
             2
             3
            (3 rows)
            

            elenst Elena Stepanova added a comment - - edited Thanks for the report and test case. I'm not sure whether it's the same problem as MDEV-13780 , I'll leave it to igor to decide. Here is a simplified version of the same query: WITH cte1 AS ( SELECT n FROM ( WITH RECURSIVE rec_cte(n) AS ( SELECT 1 as n1 UNION ALL SELECT n+1 as n2 FROM rec_cte WHERE n < 3 ) SELECT n FROM rec_cte ) AS X ), cte2 as ( SELECT 2 FROM cte1 ) SELECT * FROM cte1; MariaDB 10.2 result ERROR 1146 (42S02): Table 'test.rec_cte' doesn't exist PostgreSQL 9.6 result n --- 1 2 3 (3 rows )
            igor Igor Babaev added a comment -

            A fix for this bug was pushed into 10.2

            igor Igor Babaev added a comment - A fix for this bug was pushed into 10.2

            People

              igor Igor Babaev
              gregja gregory jarrige
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.