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

Crash MariaDB when querying PIVOT table

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.7, 10.0.9
    • 10.0.11
    • None
    • $ lsb_release -a
      No LSB modules are available.
      Distributor ID: Ubuntu
      Description: Ubuntu 12.04.4 LTS
      Release: 12.04
      Codename: precise

    Description

      A simple PIVOT table test crashes the server while trying to connect back to execute the pivot query.

      Started with a clean MariaDB installation from the MariaDB repositories for Ubuntu Precise release.

      1. CREATE DATABASE test_pivot
      2. Create the empty table using the file 'test_pivot_2.sql' in the attached tarball.
      3. Load the data using LOAD DATA INFILE from the 'test_pivot_2.txt' file in the attached tarball.
      4. Create the pivot table using the 'my_pivot_2.sql' file in the tarball.
      5. Execute SELECT * FROM my_pivot_2;

      The client will show the following error message:
      ERROR 2013 (HY000): Lost connection to MySQL server during query

      See attached error log: bart-05.err

      Attachments

        Issue Links

          Activity

            nobswolf Nobs Wolf added a comment -

            works for me in Debian 7

            crashes for me in the described way on Windows 7 64Bit

            nobswolf Nobs Wolf added a comment - works for me in Debian 7 crashes for me in the described way on Windows 7 64Bit

            The crash was caused by a bug concerning DATE columns that was easily fixed. Meanwhile I found some other problems (no crash) concerning the proper recognition of date/time values. I shall push the fix after I solve them, soon I hope.

            bertrandop Olivier Bertrand added a comment - The crash was caused by a bug concerning DATE columns that was easily fixed. Meanwhile I found some other problems (no crash) concerning the proper recognition of date/time values. I shall push the fix after I solve them, soon I hope.

            While fixing the crash (a trivial error) I also fixed some issues of bad recognition of some column types and length by discovery.

            Concerning your example, some remarks:
            After fixing, your sample table is displayed as:

            pk ts merchant amount Meals & Tips Other Phone Transportation (Taxi,bus,train etc.) Uncategorized
            1 2014-02-27 Hotel la Airport 73los Angeles 492.22         Thursday
            2 2014-02-26 Hotel lax Andiamo 7los Angeles 209.31         Wednesday
            3 2014-02-25 Hotel lax Andiamo 7los Angeles 58.14         Tuesday
            4 2014-02-25 Hotel lax Bistro 73los Angeles 3.73         Tuesday
            5 2014-02-24 Airplane Ticket 756.00 Monday        
            6 2014-02-21 Restaurant 33.01         Friday
            7 2014-02-21 Cafe & Bakery 5.75         Friday
            8 2014-02-18 Airplane Ticket 380.00 Tuesday        
            9 2014-02-18 Hotels 542.18         Tuesday
            10 2014-02-17 Purchase Finance Charge 129.00   Monday      
            11 2014-02-15 Bistro 36.46         Saturday
            12 2014-02-14 Cafe & Bakery 11.72         Friday
            13 2014-02-13 Airplane Ticket 283.00 Thursday        
            14 2014-02-13 Coffee & Tea 2.15         Thursday
            15 2014-02-12 Pfd Parking 00-seattle 7.00       Wednesday  
            16 2014-02-10 Airplane Ticket 272.00 Monday        
            17 2014-02-10 Airplane Ticket 299.00 Monday        
            18 2014-02-10 Phone Company 303.00     Monday    

            This is meaningless due to the source tables and the way it is pivoted.
            Firstly, "GroupBy=6" does not make sense. The GroupBy option is a boolean option to be set to true only when the source table has the format of a group by query result. One the other hand, the FncCol is taken by default as he last column (wd), which I doubt is what you intend.
            The FncCol is generally a numeric column (unless you use the MIN or MAX function) in particular with the default SUM function.
            I presume it should be amount, the only numeric column of the source table.

            However, even changing "GroupBY=6" by "FncCol=amount" will not give an interesting result:

            CREATE TABLE my_pivot_2
            ENGINE=CONNECT TABLE_TYPE=pivot TABNAME=test_pivot_2
            OPTION_LIST='PivotCol=category,FncCol=amount';

            This is because the source table has columns that make the internal grouping still have the same number of rows than the source table (in particular the pk column)

            More interesting result can be obtain by removing from the pivot table these extra columns. For instance, to have a pivot display of the
            sum of amount by merchant and category, the pk, ts, and wd columns must be dropped from the pivot table.
            There are several ways to do that. For instance:

            ALTER TABLE my_pivot_2 drop pk, drop ts, drop wd;

            Another way is to make the pivot table on a restricted source table:

            CREATE VIEW test_pivot_1 as select merchant, category, amount from test_pivot_2;
            CREATE TABLE my_pivot_2
            ENGINE=CONNECT TABLE_TYPE=pivot TABNAME=test_pivot_1
            OPTION_LIST='PivotCol=category,fnccol=amount';

            Once done, SELECT * FROM my_pivot_2 will display:

            merchant Meals & Tips Other Phone Transportation (Taxi,bus,train etc.) Uncategorized
            Airplane Ticket 1990.00 0.00 0.00 0.00 0.00
            Bistro 0.00 0.00 0.00 0.00 36.46
            Cafe & Bakery 0.00 0.00 0.00 0.00 17.47
            Coffee & Tea 0.00 0.00 0.00 0.00 2.15
            Hotel la Airport 73los Angeles 0.00 0.00 0.00 0.00 492.22
            Hotel lax Andiamo 7los Angeles 0.00 0.00 0.00 0.00 267.45
            Hotel lax Bistro 73los Angeles 0.00 0.00 0.00 0.00 3.73
            Hotels 0.00 0.00 0.00 0.00 542.18
            Pfd Parking 00-seattle 0.00 0.00 0.00 7.00 0.00
            Phone Company 0.00 0.00 303.00 0.00 0.00
            Purchase Finance Charge 0.00 129.00 0.00 0.00 0.00
            Restaurant 0.00 0.00 0.00 0.00 33.01
            bertrandop Olivier Bertrand added a comment - While fixing the crash (a trivial error) I also fixed some issues of bad recognition of some column types and length by discovery. Concerning your example, some remarks: After fixing, your sample table is displayed as: pk ts merchant amount Meals & Tips Other Phone Transportation (Taxi,bus,train etc.) Uncategorized 1 2014-02-27 Hotel la Airport 73los Angeles 492.22         Thursday 2 2014-02-26 Hotel lax Andiamo 7los Angeles 209.31         Wednesday 3 2014-02-25 Hotel lax Andiamo 7los Angeles 58.14         Tuesday 4 2014-02-25 Hotel lax Bistro 73los Angeles 3.73         Tuesday 5 2014-02-24 Airplane Ticket 756.00 Monday         6 2014-02-21 Restaurant 33.01         Friday 7 2014-02-21 Cafe & Bakery 5.75         Friday 8 2014-02-18 Airplane Ticket 380.00 Tuesday         9 2014-02-18 Hotels 542.18         Tuesday 10 2014-02-17 Purchase Finance Charge 129.00   Monday       11 2014-02-15 Bistro 36.46         Saturday 12 2014-02-14 Cafe & Bakery 11.72         Friday 13 2014-02-13 Airplane Ticket 283.00 Thursday         14 2014-02-13 Coffee & Tea 2.15         Thursday 15 2014-02-12 Pfd Parking 00-seattle 7.00       Wednesday   16 2014-02-10 Airplane Ticket 272.00 Monday         17 2014-02-10 Airplane Ticket 299.00 Monday         18 2014-02-10 Phone Company 303.00     Monday     This is meaningless due to the source tables and the way it is pivoted. Firstly, "GroupBy=6" does not make sense. The GroupBy option is a boolean option to be set to true only when the source table has the format of a group by query result. One the other hand, the FncCol is taken by default as he last column (wd), which I doubt is what you intend. The FncCol is generally a numeric column (unless you use the MIN or MAX function) in particular with the default SUM function. I presume it should be amount, the only numeric column of the source table. However, even changing "GroupBY=6" by "FncCol=amount" will not give an interesting result: CREATE TABLE my_pivot_2 ENGINE=CONNECT TABLE_TYPE=pivot TABNAME=test_pivot_2 OPTION_LIST='PivotCol=category,FncCol=amount'; This is because the source table has columns that make the internal grouping still have the same number of rows than the source table (in particular the pk column) More interesting result can be obtain by removing from the pivot table these extra columns. For instance, to have a pivot display of the sum of amount by merchant and category, the pk, ts, and wd columns must be dropped from the pivot table. There are several ways to do that. For instance: ALTER TABLE my_pivot_2 drop pk, drop ts, drop wd; Another way is to make the pivot table on a restricted source table: CREATE VIEW test_pivot_1 as select merchant, category, amount from test_pivot_2; CREATE TABLE my_pivot_2 ENGINE=CONNECT TABLE_TYPE=pivot TABNAME=test_pivot_1 OPTION_LIST='PivotCol=category,fnccol=amount'; Once done, SELECT * FROM my_pivot_2 will display: merchant Meals & Tips Other Phone Transportation (Taxi,bus,train etc.) Uncategorized Airplane Ticket 1990.00 0.00 0.00 0.00 0.00 Bistro 0.00 0.00 0.00 0.00 36.46 Cafe & Bakery 0.00 0.00 0.00 0.00 17.47 Coffee & Tea 0.00 0.00 0.00 0.00 2.15 Hotel la Airport 73los Angeles 0.00 0.00 0.00 0.00 492.22 Hotel lax Andiamo 7los Angeles 0.00 0.00 0.00 0.00 267.45 Hotel lax Bistro 73los Angeles 0.00 0.00 0.00 0.00 3.73 Hotels 0.00 0.00 0.00 0.00 542.18 Pfd Parking 00-seattle 0.00 0.00 0.00 7.00 0.00 Phone Company 0.00 0.00 303.00 0.00 0.00 Purchase Finance Charge 0.00 129.00 0.00 0.00 0.00 Restaurant 0.00 0.00 0.00 0.00 33.01

            People

              bertrandop Olivier Bertrand
              gerry Gerardo Narvaja (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              5 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.