Boolean term conversion for null-tolerant disjunctive predicates

Patent metadata

Field	Value
Publication number	US-9519662-B2
Application number	US-201314022322-A
Country	US
Kind code	B2
Filing date	Sep 10, 2013
Priority date	Sep 10, 2013
Publication date	Dec 13, 2016
Grant date	Dec 13, 2016

Abstract

System, method, and computer program product to modify a received query, the received query including a first predicate specifying a comparison operator and a second predicate specifying an IS NULL operator, by replacing the comparison operator of the first predicate with an extended comparison operator, and removing the IS NULL operator from the received query, the extended comparison operator configured to support searching of NULL values in a database management system (DBMS), wherein the extended comparison operator is configured to return rows having column data matching a value predefined to represent NULL values.

First claim

What is claimed is:

1. A system, comprising:
one or more computer processors; and
a memory containing a program, which when executed by the one or more computer processors, performs an operation comprising:
determining that a received query comprises a first predicate specifying a comparison operator and a second predicate specifying an IS NULL operator;
modifying the received query, wherein modifying the received query comprises:
replacing the comparison operator of the first predicate with an extended comparison operator, wherein the extended comparison operator is not a NULL operator and returns column values having a value predefined to represent NULL values; and
removing the second predicate and the IS NULL operator from the received query; and

processing the modified query, wherein processing the modified query comprises:
searching a set of column values in a database management system (DBMS) satisfying the extended comparison operator of the first predicate; and
returning at least a first column value, of the set of column values, as responsive to the modified query, wherein the first column value satisfies the extended comparison operator of the first predicate by matching the value predefined to represent NULL values.

2. The system of claim 1 , wherein the modified query comprises a single comparison predicate, wherein the single comparison predicate comprises the extended comparison operator, wherein the value predefined to represent NULL values comprises one of: (i) a largest value capable of being stored in the column data, and (ii) a smallest value capable of being stored in the column data, the operation further comprising:
providing the extended comparison operator in the DBMS.

3. The system of claim 2 , the operation further comprising:
prior to modifying the received query, determining whether a NULL indicator of at least one row of column data specified in the received query indicates whether the column data is NULL, wherein the NULL indicator precedes the column data.

4. The system of claim 3 , wherein the received query is modified upon determining that the value predefined to represent NULL values would be returned by applying the extended comparison operator to the column data, the operation further comprising:
executing the modified query.

5. The system of claim 4 , wherein the value predefined to represent NULL values is the largest value capable of being stored in the column data, wherein searching the set of column values comprises identifying column values matching at least one of: (i) the value predefined to represent NULL values, (ii) a value between a specified value in the first predicate and the value predefined to represent NULL values, and (iii) the specified value in the first predicate, wherein the extended comparison operator comprises each individual extended operator selected from the group comprising: (i) an extended greater than operator, (ii) an extended equals operator, and (iii) an extended greater than or equals operator.

6. The system of claim 4 , wherein the value predefined to represent NULL values is the smallest value capable of being stored in the column data, wherein searching the set of column values comprises identifying column values matching at least one of: (i) the value predefined to represent NULL values, (ii) a value between the value predefined to represent NULL values and a specified value in the first predicate, and (iii) the specified value in the first predicate, wherein the extended comparison operator comprises each individual extended operator selected from the group comprising: (i) an extended less than operator, (ii) an extended equals operator, and (iii) an extended less than or equals operator.

7. The system of claim 6 , wherein the extended comparison operator comprises an IN list predicate that is augmented to be NULL-tolerant.

8. The system of claim 1 , wherein searching the set of column values comprises identifying column values matching the value predefined to represent NULL values.

9. A computer program product, comprising:
a non-transitory computer-readable storage medium having computer-readable program code embodied therewith, the computer-readable program code executable by a processor to perform an operation comprising:
determining that a received query comprises a first predicate specifying a comparison operator and a second predicate specifying an IS NULL operator;
modifying the received query, wherein modifying the received query comprises:
replacing the comparison operator of the first predicate with an extended comparison operator, wherein the extended comparison operator is not a NULL operator and returns column values having a value predefined to represent NULL values; and
removing the second predicate and the IS NULL operator from the received query; and

processing the modified query, wherein processing the modified query comprises:
searching a set of column values in a database management system (DBMS) satisfying the extended comparison operator of the first predicate; and
returning at least a first column value, of the set of column values, as responsive to the modified query, wherein the first column value satisfies the extended comparison operator of the first predicate by matching the value predefined to represent NULL values.

10. The computer program product of claim 9 , wherein the modified query comprises a single comparison predicate, wherein the single comparison predicate comprises the extended comparison operator, wherein the value predefined to represent NULL values comprises one of: (i) a largest value capable of being stored in the column data, and (ii) a smallest value capable of being stored in the column data, the operation further comprising:
providing the extended comparison operator in the DBMS.

11. The computer program product of claim 10 , the operation further comprising:
prior to modifying the received query, determining whether a NULL indicator of at least one row of column data specified in the received query indicates whether the column data is NULL, wherein the NULL indicator precedes the column data.

12. The computer program product of claim 11 , wherein the received query is modified upon determining that the value predefined to represent NULL values would be returned by applying the extended comparison operator to the column data, the operation further comprising:
executing the modified query.

13. The computer program product of claim 12 , wherein the value predefined to represent NULL values is the largest value capable of being stored in the column data, wherein searching the set of column values comprises identifying column values matching at least one of: (i) the value predefined to represent NULL values, (ii) a value between a specified value in the first predicate and the value predefined to represent NULL values, and (iii) the specified value in the first predicate, wherein the extended comparison operator comprises each individual extended operator selected from the group comprising: (i) an extended greater than operator, (ii) an extended equals operator, and (iii) an extended greater than or equals operator.

14. The computer program product of claim 12 , wherein the value predefined to represent NULL values is the smallest value capable of being stored in the column data, wherein searching the set of column values comprises identifying column values matching at least one of: (i) the value predefined to represent NULL values, (ii) a value between the value predefined to represent NULL values and a specified value in the first predicate, and (iii) the specified value in the first

Assignees

• IBM

Inventors

• Beavin Thomas A
• Cai Mengchu
• Gao Dengfeng
• Lee Jae H
• Lee Li-Mey P
• Purcell Terence P
• Suo Shengxi

Classifications

• G06F17/30448

  Physics · mapped topic

• G06F17/30289 Primary

  Physics · mapped topic

• G06F16/21 Primary

  Design, administration or maintenance of databases · CPC title

• G06F16/24534 Primary

  Query rewriting; Transformation · CPC title