[MCOL-74] Nth_value() windowing function does not exist error Created: 2016-05-23  Updated: 2016-10-11  Resolved: 2016-10-06

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: None
Fix Version/s: 1.0.4

Type: Bug Priority: Minor
Reporter: Daniel Lee (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: relnote

Sprint: 2016-19

 Description   

Build tested:

InfiniDB> getcalpontsoft
getcalpontsoftwareinfo Mon May 23 09:32:32 2016

Name : infinidb-platform Relocations: (not relocatable)
Version : 5.0 Vendor: MariaDB, Inc.
Release : 0 Build Date: Sun 15 May 2016 07:24:29 PM CDT
Install Date: Mon 16 May 2016 05:21:51 PM CDT Build Host: srvbuilder

the Nth_value() function was working in earlier build of ColumnStore, although the returned result was incorrect(There is an bug opened for this incorrect result issue). Now this function no longer exists in the build.

 Comments   
Comment by Daniel Lee (Inactive) [ 2016-05-23 ]

MariaDB [tpch1m]> select o_custkey, Nth_value(o_custkey,2) OVER (PARTITION BY abs(o_custkey)+10 ORDER BY o_custkey DESC NULLS LAST ,o_orderkey DESC NULLS LAST ,o_orderdate DESC NULLS LAST ROWS BETWEEN 15 FOLLOWING AND 15 FOLLOWING) from (select * from orders where o_custkey <= 20000) s;
ERROR 1815 (HY000): Internal error: IDB-9003: Window function 'Nth_value' does not exist.

Comment by David Hall (Inactive) [ 2016-06-02 ]

Here's a list of the test queries that failed. This list also includes last_value:

Compare failed - working_tpch1_compareLogOnly/windowFunctions/bug5777.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0024.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0026.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0041.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0042.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0043.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0044.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0045.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0054b.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0073.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0269.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0270.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0271.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0272.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0273.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0274.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0275.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0276.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0277.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0278.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0289.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0290.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0291.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0292.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0293.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0294.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0295.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0296.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0297.sql
Compare failed - working_tpch1_compareLogOnly/windowFunctions/q0298.sql

Comment by David Hall (Inactive) [ 2016-09-28 ]

NTH_VALUE was listed in func_array_nulls of item_create_window_function.cc, but not in func_array. If you add ignore nulls in the proper place, it works.

Added NTH_VALUE to func_array.

So, why does this work in InfininDB? I checked and the code is the same. Anyway, adding it fixed the issue.

Comment by David Hall (Inactive) [ 2016-09-28 ]

This bug fix only fixes the syntax error. It does not address any incorrect results, if any, as defined in MCOL-37.

Comment by David Hall (Inactive) [ 2016-09-29 ]

First, the above fix is incorrect. There is a line missing in lex.h that got left behind in the InfiniDB port to MariaDB.

While working this, I noticed that the phrase FROM FIRST | LAST causes syntax error. This worked in InfiniDB. The cause is a conflict with the way MariaDB parses the FROM clause of SELECT and others.

The new sql_yacc.yy contains this:
table_expression:
opt_from_clause
opt_where_clause
opt_group_clause
opt_having_clause
opt_order_clause
opt_limit_clause
opt_procedure_clause
opt_select_lock_type
;

from_clause:
FROM table_reference_list
;

opt_from_clause:
/* empty */

from_clause
;

This did not exist in mysql 5.1.73 – another mechanism was used that did not conflict.

A change in the columnstore NTH_VALUE rules to explicitly demand the FROM FIRST, FROM LAST and no FROM clause fixes the issue.

Comment by Andrew Hutchings (Inactive) [ 2016-09-29 ]

Good fix! Moving to testing

Comment by Daniel Lee (Inactive) [ 2016-10-06 ]

Build verified: 1.0.4-1
mcsadmin> getsoft
getsoftwareinfo Tue Oct 4 22:29:41 2016

Name : mariadb-columnstore-platform
Version : 1.0.4
Release : 1
Architecture: x86_64
Install Date: Tue 04 Oct 2016 01:38:36 PM CDT
Group : Applications/Databases
Size : 11506458
License : Copyright (c) 2016 MariaDB Corporation Ab., all rights reserved; redistributable under the terms of the GPL, see the file COPYING for details.
Signature : (none)
Source RPM : mariadb-columnstore-platform-1.0.4-1.src.rpm
Build Date : Fri 30 Sep 2016 01:02:40 PM CDT

Generated at Thu Feb 08 02:18:19 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.