Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.7, 10.0.9
-
Fix Version/s: 10.0.11
-
Component/s: None
-
Labels:
-
Environment:$ 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
Gliffy Diagrams
Attachments
Issue Links
- duplicates
-
-
- Closed
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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 |
works for me in Debian 7
crashes for me in the described way on Windows 7 64Bit