TPC BENCHMARK (TM) H (779138), страница 7
Текст из файла (страница 7)
If asponsor- created tool is used instead of QGEN, the behavior of its seeds must satisfy this Clause and its code mustbe disclosed. After execution, the query returns one or more rows. The rows returned are either rows from thedatabase or rows built from data in the database and are called the output data.2.1.3.4 Output data for each query should be expressed in a format easily readable by a non-sophisticated computer user. Inparticular, in order to be comparable with known output data for the purpose of query validation (see Clause 2.3),the format of the output data for each query must adhere to the following guidelines:a)Columns appear in the order specified by the SELECT list of either the functional query definition or anapproved variant.
Column headings are optional.b)Non-integer expressions including prices are expressed in decimal notation with at least two digits behindthe decimal point.c)Integer quantities contain no leading zeros.d)Dates are expressed in a format that includes the year, month and day in integer form, in that order (e.g.,YYYY-MM-DD). The delimiter between the year, month and day is not specified.
Other daterepresentations, for example the number of days since 1970-01-01, are specifically not allowed.e)Strings are case-sensitive and must be displayed as such. Leading or trailing blanks are acceptable.f)The amount of white space between columns is not specified.2.1.3.5 The precision of all values contained in the query validation output data must adhere to the following rules:a)For singleton column values and results from COUNT aggregates, the values must exactly match the queryvalidation output data.b)For ratios, results r must be within 1% of the query validation output data v when rounded to the nearest1/100th. That is, 0.99*v<=round(r,2)<=1.01*v.c)For results from SUM aggregates, the resulting values must be within $100 of the query validation outputdata.d)For results from AVG aggregates, the resulting values r must be within 1% of the query validation outputdata when rounded to the nearest 1/100th. That is, 0.99*v<=round(r,2)<=1.01*v.Comment 1: In cases where validation output data is computed using a combination of SUM aggregate and ratios(e.g.
queries 8,14 and 17), the precision for this validation output data must adhere to bullets b) and c) above.Comment 2: In cases where validation output data resembles a row count operation by summing up 0 and 1 using aSUM aggregate (e.g. query 12), the precision for this validation output data must adhere to bullet a) above.Comment 3: In cases were validation output data is selected from views without any further computation (e.g.
totalrevenue in Query 15), the precision for this validation output data must adhere to bullet c) above.Comment 4: In cases where validation output data is from the aggregate SUM(l_quantity) (e.g. queries 1 and 18),the precision for this validation output data must exactly match the query validation data.2.1.4The QGEN Program2.1.4.1 Executable query text must be generated according to the requirements of Clause 2.1.2 and Clause 2.1.3.
. QGen is aTPC provided software package that must be used to generate the query text.2.1.4.2 The data generated by QGen are meant to be compliant with the specification as per Clause 2.1.2 and Clause 2.1.3.In case of differences between the content of these two clauses and the text generated by QGen, the specificationprevails.2.1.4.3 The TPC Policies Clause 5.3.1 requires that the version of the specification and QGen must match. It is the testsponsor’s responsibility to ensure the correct version of QGen is used.2.1.4.4 QGen has been tested on a variety of platforms. Nonetheless, it is impossible to guarantee that QGen is functionallyTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 24correct in all aspects or will run correctly on all platforms.
It is the Test Sponsor's responsibility to ensure the TPCprovided software runs in compliance with the specification in their environment(s).2.1.4.5 If a Test Sponsor must correct an error in QGen in order to publish a Result, the following steps must beperformed:a. The error must be reported to the TPC administrator no later than the time when the Result is submitted.b. The error and the modification (i.e. diff of source files) used to correct the error must be reported in theFDR as described in clause 8.3.5.5.c.
The modification used to correct the error must be reviewed by a TPC-Certified Auditor as part of the auditprocess.Furthermore any consequences of the modification may be used as the basis for a non-compliance challenge.2.2Query Compliance2.2.1The queries must be expressed in a commercially available implementation of the SQL language. Since the latestISO SQL standard (currently ISO/IEC 9075:1992) has not yet been fully implemented by most vendors, and sincethe ISO SQL language is continually evolving, the TPC-H benchmark specification includes a number ofpermissible deviations from the formal functional query definitions found in Clause 2: .
An on-going process is alsodefined to approve additional deviations that meet specific criteria.2.2.2There are two types of permissible deviations from the functional query definitions, as follows:2.2.3a)Minor query modifications;b)Approved query variants.Minor Query Modifications2.2.3.1 It is recognized that implementations require specific adjustments for their operating environment and the syntacticvariations of its dialect of the SQL language. Therefore, minor query modifications are allowed. Minor querymodifications are those that fall within the bounds of what is described in Clause 2.2.3.3. They do not requireapproval.
Modifications that do not fall within the bounds of what is described in Clause 2.2.3.3are not minor andare not compliant unless they are an integral part of an approved query variant (see Clause 2.2.4).Comment 1: The intent of this Clause is to allow the use of any number of minor query modifications. These querymodifications are labeled minor based on the assumption that they do not significantly impact the performance ofthe queries.Comment 2: The only exception is for the queries that require a given number of rows to be returned.
Therequirements governing this exception are given in Clause 2.1.2.9.2.2.3.2 Minor query modifications can be used to produce executable query text by modifying either a functional querydefinition or an approved variant of that definition.2.2.3.3 The following query modifications are minor:a)Table names - The table and view names found in the CREATE TABLE, CREATE VIEW, DROP VIEWand in the FROM clause of each query may be modified to reflect the customary naming conventions of thesystem under test.b)Select-list expression aliases - For queries that include the definition of an alias for a SELECT-list item(e.g., AS CLAUSE), vendor-specific syntax may be used instead of the specified SQL-92 syntax.Replacement syntax must have equivalent semantic behavior. Examples of acceptable implementationsinclude "TITLE <string>", or "WITH HEADING <string>".
Use of a select-list expression alias is optional.c)Date expressions - For queries that include an expression involving manipulation of dates (e.g.,adding/subtracting days/months/years, or extracting years from dates), vendor-specific syntax may be usedTPC BenchmarkTM H Standard Specification Revision 2.17.1Page 25instead of the specified SQL-92 syntax. Replacement syntax must have equivalent semantic behavior.Examples of acceptable implementations include "YEAR(<column>)" to extract the year from a datecolumn or "DATE(<date>) + 3 MONTHS" to add 3 months to a date.d)GROUP BY and ORDER BY - For queries that utilize a view, nested table-expression, or select-list aliassolely for the purposes of grouping or ordering on an expression, vendors may replace the view, nestedtableexpression or select-list alias with a vendor-specific SQL extension to the GROUP BY or ORDER BYclause. Examples of acceptable implementations include "GROUP BY <ordinal>", "GROUP BY<expression>", "ORDER BY <ordinal>", and "ORDER BY <expression>".e)Command delimiters - Additional syntax may be inserted at the end of the executable query text for thepurpose of signaling the end of the query and requesting its execution.
Examples of such commanddelimiters are a semicolon or the word "GO".f)Output formatting functions - Scalar functions whose sole purpose is to affect output formatting orintermediate arithmetic result precision (such as CASTs) may be applied to items in the outermost SELECTlist of the query.g)Transaction control statements - A CREATE/DROP TABLE or CREATE/DROP VIEW statement may befollowed by a COMMIT WORK statement or an equivalent vendor-specific transaction control statement.h)Correlation names – Table-name aliases may be added to the executable query text. The keyword "AS"before the table-name alias may be omitted.i)Explicit ASC - ASC may be explicitly appended to columns in the ORDER BY.j)CREATE TABLE statements may be augmented with a tablespace reference conforming to therequirements of Clause 2.1.2.6.k)In cases where identifier names conflict with SQL-92 reserved words in a given implementation, delimitedidentifiers may be used.l)Relational operators - Relational operators used in queries such as "<", ">", "<>", "<=", and "=", may bereplaced by equivalent vendor-specific operators, for example ".LT.", ".GT.", "!=" or "^=", ".LE.", and"==", respectively.m)Nested table-expression aliasing - For queries involving nested table-expressions, the nested keyword "AS"before the table alias may be omitted.n)If an implementation is using variants involving views and the implementation only supports “DROPRESTRICT” semantics (i.e., all dependent objects must be dropped first), then additional DROP statementsfor the dependent views may be added.o)At large scale factors, the aggregates may exceed the range of the values supported by an integer.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
