On Database Constraints in Oracle

So there was something confusing about a check constraint in one of my tables.

I've proven that the check constraint works as it should, but I failed to understand why.

Actually there were two constraints that sort of combine.

Let's say I have a table called "customers". And that there are two constraints at play:

• constraint check01 check (bankaccountnr is not null or creditcardnr is not null or paypalemail is not null)
• constraint check02 check (bankaccountnr > 0 and creditcardnr > 0 and length(paypalemail) > 0)

The first constraint is obvious, at least one of the fields must be filled in, in order to process orders.

It's the second constraint that is confusing.

I thought that, because of the and, the check02 constraint should fire if I try to enter only a bankaccountnr (as the other two conditions evaluate to false).

And I thought I verified that by using the query:

select * from customers where bankaccountnr > 0 and creditcardnr > 0 and length(paypalemail) > 0;

As I suspected it provided me ONLY the records that had ALL fields entered properly.

But apparently, in (check) constraints, if the condition is "unknown", for example in the case of NULL-values, it is accepted.

According to the following quote, found in [1] which I found via [2]:

A check constraint on a column or set of columns requires that a specified condition be true or unknown for every row. If DML results in the condition of the constraint evaluating to false, then the SQL statement is rolled back.

References

[1] Oracle - Data Integrity

https://docs.oracle.com/database/121/CNCPT/datainte.htm#CNCPT1660

[2] StackOverflow - Why is this check constraint not working when it checks length?

https://stackoverflow.com/questions/66031098/why-is-this-check-constraint-not-working-when-it-checks-length

SQL: Concat operator and Spaces

So I had a pressing need to query my database and concatenate several values from a row together (using either concat or ||), but with spaces in between.

But I don't want two spaces if the value in between is empty.

It is surprisingly difficult to do in SQL, but there are some options available.

First the data:

TITLE	FIRSTNAME	MIDDLENAME	FAMILYNAME
	Ichabod		Crane
Dr.	Thomas		Lancaster
	Philip	Martin	Brown

0. No solution

select TITLE || ' ' || FIRSTNAME || ' ' || MIDDLENAME || ' ' || FAMILYNAME from PERSON;

So, if you use the SQL statement above, you get the problem, extraneous spaces. For example "[ ]Ichabod[ ][ ]Crane". We wish to eliminate those.

1. COALESCE

select coalesce(TITLE || ' ','') || coalesce(FIRSTNAME || ' ','') || coalesce(MIDDLENAME || ' ','') || FAMILYNAME from PERSON;

This is a good try, but doesn't work, as the first expression of the coalesce is never NULL, because of the concat used with the space (|| ' ') used.

1. DECODE

select decode(TITLE, null, '', TITLE, TITLE || ' ') ||
       decode(FIRSTNAME, null, '', FIRSTNAME, FIRSTNAME || ' ') ||
       decode(MIDDLENAME, null, '', MIDDLENAME, MIDDLENAME || ' ') ||
       FAMILYNAME
       from PERSON;

This works but is a lot of code to do something very simple.

2. CASE

select (case
           when TITLE is null then ''
           else TITLE || ' ' end) ||
       (case
           when FIRSTNAME is null then ''
           else FIRSTNAME || ' ' end) ||
       (case
           when MIDDLENAME is null then ''
           else MIDDLENAME || ' ' end) ||
       FAMILYNAME
from PERSON;

This works but is a lot of code. Luckily the case statement, when indented properly, is quite readable and not at all bad.

3. NVL2

select nvl2(TITLE, TITLE || ' ', '') ||
       nvl2(FIRSTNAME, FIRSTNAME || ' ', '') ||
       nvl2(MIDDLENAME, MIDDLENAME || ' ', '') ||
       FAMILYNAME from PERSON;

This works but is hard to read. Requires knowledge of nvl2.

4. LTRIM

select ltrim(TITLE || ' ') ||
       ltrim(FIRSTNAME || ' ') ||
       ltrim(MIDDLENAME || ' ') ||
       FAMILYNAME from PERSON;

As far as I can tell, this is quite appropriate. The ltrim removes spaces at the beginning of the string. If the string contains only ' ', all ' ' are removed and we're home free.

It is short, but requires some knowledge of what ltrim does exactly.

Falls in the category of 'doing something clever'.

5. REGEXP_REPLACE

select regexp_replace(TITLE
        || ' ' || FIRSTNAME
        || ' ' || MIDDLENAME
        || ' ' || FAMILYNAME
        , ' +', ' ')
from person;

A colleague of mine came up with this little chestnut.

It's nice if you enjoy regular expressions. I do not.

Still, it's nice to be able to just concat everything together, and have the whole thing sorted out with one simple regular expression.

Addendum

The function wm_concat has been removed from Oracle PL/SQL and should not be used.

Which is a shame, as it does exactly what we want.

References

American Culture - Naming

https://culturalatlas.sbs.com.au/american-culture/american-culture-naming

Lalit Kumar B - Why not use WM_CONCAT function in Oracle?

https://lalitkumarb.wordpress.com/2015/04/29/why-not-use-wm_concat-function-in-oracle/

Updates: added regexp_replace solution

The DUAL table

I'm working with Oracle databases (version 19 for now), and I always wondered about the weird DUAL table¹ that I require in my work.

Things like this:

select sysdate from dual;
select seq_s_ordertable.nextval from dual;

Well, apparently we have Charles "Chuck" Weiss² to thank for it.

One of the reasons is that the FROM clause in Oracle SQL syntax is mandatory.

Interestingly, Charles originally created the DUAL table with two rows, hence the name DUAL. Nowadays it's one row, but the name's stuck.

It's part of the Data Dictionary of the SYS user. Changing the DUAL table will cause problems!³

References

[1] Wikipedia - DUAL table

https://en.wikipedia.org/wiki/DUAL_table

[2] Wikipedia - Charles Weiss

https://en.wikipedia.org/wiki/Charles_Weiss

[3] Ask tom - All about the DUAL table

https://asktom.oracle.com/ords/f?p=100:11:0::::P11_QUESTION_ID:1562813956388

Entity-Relationship Diagrams

I tend to forget what the arrows mean in an entity-relationship diagram.

Example

In the diagram above, there's strong limitations on the values possible in the GuildMember table. Unfortunately, these cannot be expressed in Foreign Key Relationships.

For example:

• a Person can hold only one rank in a Guild.
• a Person can only be a GuildMember in a Guild once.

The way to fascilitate this, is to put a unique index on GuildMember regarding the columns GUILDNR and PERSONNR.

References

PlantUML - Entity Relationship Diagram

https://plantuml.com/ie-diagram

LucidChart - Wat is een Entity Relationship Diagram?

https://www.lucidchart.com/pages/nl/wat-is-een-entity-relationship-diagram

How to UPSERT (INSERT or UPDATE) rows with MERGE in Oracle Database

It's awesome! A colleague of mine uses merge for batch jobs, as it is a great deal quicker.

Unfortunately, performance seems to drop when using it to insert/update individual entries.

Join in SQL

There are lots of nice diagrams explaining the difference between the different joins in SQL.

References

Code Project for those who Code - Visual Representation of SQL Joins

https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

Using SQL Developer to visualise Geometry data

I was looking to see if shapes were incorporated inside other shapes.

I could have let the database check this, but sometimes it is very insightful to have a visual representation.

It turns out, SQL Developer facilitates this.

If you select a geometry field in your table, and right click and select [Display geometry shape], you'll get a nice little window indicating the shape.

Sometimes this is greyed out. The solution for this is to click on [Edit] in the menu above, and select [Map View]. This will "initialize" stuff, and then it's no longer greyed out.

Map View

The map view is very convenient, if you wish to show multiple shapes and how they interact.

To get started, select under "View", "Map view" and you are presented with a new subwindow.

Pressting the "New" button in the Map View window, you can just enter a query that returns a geometry, give it a name, proper colouring, and you're good to go.

In the example above, there's two queries, one for a point and one for a shape. The Map View displays them both, and you can even deactive one or the other in the table on the right.

Very convenient in my work, I say.

References

[1] ThatJeffSmith - Visualize Spatial Data with SQL Developer

https://www.thatjeffsmith.com/archive/2011/12/visualize-spatial-data-with-sql-developer/

Spatial Information in an Oracle Database

SDO_GEOMETRY

This is here for me to remember how much magic is used in inserting spatial (geometry) information into an Oracle database¹.

CREATE TYPE sdo_geometry AS OBJECT (
SDO_GTYPE NUMBER,
SDO_SRID NUMBER,
SDO_POINT SDO_POINT_TYPE,
SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY,
SDO_ORDINATES SDO_ORDINATE_ARRAY);

A point geometry:

MDSYS.SDO_GEOMETRY(
    2001, -- 2-dimensional (2), non-linear referencing geometry or default (0), point (01)
    28992, -- Amersfoort / RD New
    NULL, 
    MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1),
    MDSYS.SDO_ORDINATE_ARRAY(81431.756, 455218.921))

A polygon geometry:

MDSYS.SDO_GEOMETRY(
    2003, -- 2-dimensional (2), non-linear referencing geometry or default (0), polygon (03)
    28992, -- Amersfoort / RD New
    NULL,
    MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1), -- first coordinate (1), exterior (1003), polygon (1)
    MDSYS.SDO_ORDINATE_ARRAY(
        -1041172.16,1407540.16,
        -958596.8,-591471.68,
        1363835.2,-625878.08,
        1319106.88,1428184,
        -1041172.16,1407540.16) -- coordinates
)

A multipolygon geometry:

MDSYS.SDO_GEOMETRY(
    2007, -- 2-dimensional (2), non-linear referencing geometry or default (0), MULTIPOLYGON or MULTISURFACE (07)
    28992, -- Amersfoort / RD New
    NULL, 
    MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1, 11, 2003, 1), 
    MDSYS.SDO_ORDINATE_ARRAY(42, 78, 41, 85, 46, 82.7, 45, 79, 42, 78, 43.5, 72.5, 52.5, 72.5, 54.5, 79.5, 79, 60, 93, 46, 53, 32, 54, 25, 49, 22, 45, 17, 41, 16, 37, 23, 33, 24, 25, 20, 33, 46, 39, 53, 43.5, 72.5))

The only thing that makes sense among all the magic numbers are the coordinates. The last coordinate is the same as the first coordinate to close the polygon.

I since have found some more information after some digging regarding the SDO_ELEM_INFO.

It's an array of triplets, where a triplet consists of:

SDO_STARTING_OFFSET

offset within the coordinates array, the first triplet usually has "1" as being the first coordinate in the coordinate array

SDO_ETYPE

type of element

SDO_INTERPRETATION

depends on if SDO_ETYPE is a compound element

I found more information in [2].

WKT - Well-known text representation of geometry

"POLYGON ((194232.738 467652.498, 194232.774322728 467652.19885542, 194232.881179968 467651.917096035, 194232.738 467652.498))"

"POINT (31256.383 393077.6)"

"MULTIPOLYGON(((42 78, 41 85, 46 82.7, 45 79, 42 78),(43.5 72.5, 52.5 72.5, 54.5 79.5, 79 60, 93 46, 53 32, 54 25, 49 22, 45 17, 41 16, 37 23, 33 24, 25 20, 33 46, 39 53, 43.5 72.5)))"

It's possible to create SDO_GEOMETRY in PL/SQL based on WKT strings.

Like so:

SELECT SDO_GEOMETRY('MULTIPOLYGON(((42 78, 41 85, 46 82.7, 45 79, 42 78),(43.5 72.5, 52.5 72.5, 54.5 79.5, 79 60, 93 46, 53 32, 54 25, 49 22, 45 17, 41 16, 37 23, 33 24, 25 20, 33 46, 39 53, 43.5 72.5)))') FROM DUAL;

Convenient if you want to do something quick, but you cannot provide additional information regarding the dimensions, and coordinate system, etc.

Let me rephrase that sentence. It means your resulting geometrie has NO SRID, and you'll get in trouble using it in geometry queries! This is not a joke!

The reverse (if you want to find out the geometrie in WKT format, basically because it reads easier) is of course also possible, like so:

SELECT SDO_UTIL.TO_WKTGEOMETRY(geometry) FROM buildinggeo;

Geometry Functions

So I was playing around with ConvexHull, ConcaveHull and ConcaveHull-Boundary functions.

I took the MultiPolygon in the paragraph above as an example.

SELECT SDO_GEOM.SDO_CONVEXHULL(MDSYS.SDO_GEOMETRY(
2007, -- 2-dimensional (2), non-linear referencing geometry or default (0), multipolygon (07)
28992, -- Amersfoort / RD New
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,11,2003,1), -- first coordinate (1), Simple polygon whose vertices are connected by straight line segments (1003,1), 11th coordinate (11), Simple polygon whose vertices are connected by straight line segments (1003,1)
MDSYS.SDO_ORDINATE_ARRAY(42, 78, 41, 85, 46, 82.7, 45, 79, 42, 78,
43.5, 72.5, 52.5, 72.5, 54.5, 79.5, 79, 60, 93, 46, 53, 32, 54, 25, 49, 22, 45, 17, 41, 16, 37, 23, 33, 24, 25, 20, 33, 46, 39, 53, 43.5, 72.5) -- coordinates
), 1)
FROM dual;
-- creates MDSYS.SDO_GEOMETRY(2003, 28992, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(41, 16, 45, 17, 93, 46, 79, 60, 54.5, 79.5, 41, 85, 25, 20, 41, 16))

SELECT SDO_GEOM.SDO_CONCAVEHULL(MDSYS.SDO_GEOMETRY(
2007, -- 2-dimensional (2), non-linear referencing geometry or default (0), multipolygon (07)
28992, -- Amersfoort / RD New
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,11,2003,1), -- first coordinate (1), Simple polygon whose vertices are connected by straight line segments (1003,1), 11th coordinate (11), Simple polygon whose vertices are connected by straight line segments (1003,1)
MDSYS.SDO_ORDINATE_ARRAY(42, 78, 41, 85, 46, 82.7, 45, 79, 42, 78,
43.5, 72.5, 52.5, 72.5, 54.5, 79.5, 79, 60, 93, 46, 53, 32, 54, 25, 49, 22, 45, 17, 41, 16, 37, 23, 33, 24, 25, 20, 33, 46, 39, 53, 43.5, 72.5) -- coordinates
), 0.1)
FROM dual;
-- creates MDSYS.SDO_GEOMETRY(2003, 28992, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(41, 85, 42, 78, 43.5, 72.5, 39, 53, 33, 46, 25, 20, 41, 16, 45, 17, 54, 25, 53, 32, 93, 46, 79, 60, 54.5, 79.5, 46, 82.7, 41, 85))

SELECT SDO_GEOM.SDO_CONCAVEHULL_BOUNDARY(MDSYS.SDO_GEOMETRY(
2007, -- 2-dimensional (2), non-linear referencing geometry or default (0), multipolygon (07)
28992, -- Amersfoort / RD New
NULL,
MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1,11,2003,1), -- first coordinate (1), Simple polygon whose vertices are connected by straight line segments (1003,1), 11th coordinate (11), Simple polygon whose vertices are connected by straight line segments (1003,1)
MDSYS.SDO_ORDINATE_ARRAY(42, 78, 41, 85, 46, 82.7, 45, 79, 42, 78,
43.5, 72.5, 52.5, 72.5, 54.5, 79.5, 79, 60, 93, 46, 53, 32, 54, 25, 49, 22, 45, 17, 41, 16, 37, 23, 33, 24, 25, 20, 33, 46, 39, 53, 43.5, 72.5) -- coordinates
), 0.1, 0.01)
FROM dual;
-- creates MDSYS.SDO_GEOMETRY(2003, 28992, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 1003, 1), MDSYS.SDO_ORDINATE_ARRAY(41, 85, 33, 46, 25, 20, 41, 16, 45, 17, 54, 25, 53, 32, 93, 46, 79, 60, 54.5, 79.5, 41, 85))

I will expand on this blog post, as my knowledge in this area is expanded.

References

[1] Spatial and Graph Developer's Guide - 2.2 SDO_GEOMETRY Object Type

https://docs.oracle.com/database/121/SPATL/sdo_geometry-object-type.htm#SPATL489

[2] Spatial and Graph Developer's Guide - 2.2.4 SDO_ELEM_INFO

https://docs.oracle.com/database/121/SPATL/sdo_geometry-object-type.htm#SPATL494

Spatial and Graph Developer's Guide - 2.1 Simple Example: Inserting, Indexing, and Querying Spatial Data

https://docs.oracle.com/database/121/SPATL/simple-example-inserting-indexing-and-querying-spatial-data.htm#SPATL486

MariaDB issues

I ran into some issues, and I thought I'd document them here.

Not able to detect platform for vendor name [MariaDB1010.3.17-MariaDB]. Defaulting to [org.eclipse.persistence.platform.database.DatabasePlatform]. The database dialect used may not match with the database you are using. Please explicitly provide a platform using property "eclipselink.target-database".]]

So changed my persistence.xml and added:

<property name="eclipselink.target-database" value="MySQL4"/>

Also:

Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.4.payara-p2): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: (conn=16) Table 'mmud.SEQUENCE' doesn't exist
Error Code: 1146
Call: UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?
       bind => [2 parameters bound]
Query: DataModifyQuery(name="SEQ_GEN_IDENTITY" sql="UPDATE SEQUENCE SET SEQ_COUNT = SEQ_COUNT + ? WHERE SEQ_NAME = ?")
       at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:333)

As the vendor name was not detected, eclipselink switched to a default implementation. The default implementation requires SEQUENCES for the IDENTITY definition.

It turns out the MariaDB implementation of Sequences is not compatible with the standard SQL way of creating sequences.

So I had to add the following property to the JDBC Connection pool:

useMysqlMetadata = true

References

JIRA MariaDB : since 2.4.0 j-connector throws sequence errors via JPA/ eclipselink on @GeneratedValue(strategy = GenerationType.IDENTITY) columns

https://jira.mariadb.org/browse/CONJ-702

Java Persistence API (JPA) Extensions Reference for EclipseLink, Release 2.4 : target-database

https://www.eclipse.org/eclipselink/documentation/2.4/jpa/extensions/p_target_database.htm

Eclipse JIRA : Bug 462196 - Add support for MariaDB

https://bugs.eclipse.org/bugs/show_bug.cgi?id=462196

MERGE SQL Statement

I recently had to do a insert on the database, if the records weren't there, and an update if it was.

Performance was a problem.

Colleague of mine had the same problem when using an insert and update statement with an (exists (select 1)) on 2.5 million records.

After 5 minutes, 5000 rows were done. It would have taken 41 hours to fix them all.

After a merge¹ statement, the entire thing was done in 2.5 minutes.

So, I had to get to grips with the merge statement as well. I hadn't used it before.

There's plenty of tutorials on how it works.

Oracle allows a DELETE statement in the MERGE statement, which seems not to be defined in the SQL Standard.

References

[1] Wikipedia - Merge(SQL)

https://en.wikipedia.org/wiki/Merge_(SQL)

[2] Oracle PL/SQL Documentation - MERGE

https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

Comparing NULLs in SQL

Consider the following table "mm_mailtable", containing the following data:

id	name	toname	whensent	subject	haveread	newmail
27999	William	Joe	2018-03-13 12:41:25	Golf next week?	1	0
28000	William	Linda	2018-03-13 12:41:25	I'm out!	1	0
28001	Linda	William	2018-03-13 12:41:25	Okay!	1	0
28002	Joe	William	2018-03-13 12:41:25	Sure thing!	1	1
28003	Jim	William	2018-03-13 12:41:25	The house	1	NULL

Also consider the following query I found in our source code somewhere.

SELECT *
FROM mm_mailtable
WHERE toname = :NAME
AND newmail = COALESCE(:NEWMAIL, newmail);

Let's say we try the query out with "William" as NAME and 0 as NEWMAIL.

We get an old mail to William with "Okay!" as subject.

Let's say we try the query out with "William" as NAME and 1 as NEWMAIL.

We get a new mail to William with "Sure thing!" as subject.

Let's say we try the query out with "William" as NAME and NULL as NEWMAIL.

Now I would expect to get all three entries returned, but the one containing the NULL value for "newmail" is not returned.

It takes some getting used to, but comparing null values is not possible in most databases, as it is undefined (a.k.a.. NULL).

See for more and better explanations the references below.

P.S. in this case, in our database, the column "newmail" should have been defined as "NOT NULL" and given a "DEFAULT" value, to prevent this sort of thing. Apparently it was forgotten.

Rewriting the query

This should work:

SELECT * 
FROM mm_mailtable 
WHERE toname = :NAME
AND (:NEWMAIL is null OR :NEWMAIL = newmail);

References

StackOverflow - why is null not equal to null false

https://stackoverflow.com/questions/1833949/why-is-null-not-equal-to-null-false

Baron Schwartz's Blog - Why NULL never compares false to anything in SQL

https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/

Automated executing of MySQL/MariaDB scripts

I am running MariaDB and I wish to execute sql scripts without all this hassle of entering my password. Of course this carries severe security risks with it, that we need to be aware of and, if possible, mitigate.

Via the commandline

It is possible to execute sql scripts via the commandline¹, but the problem here is that the password you use is visible in the process list. So this is a security risk.

Let's not do this.

mysql_config_editor

I firstly checked out mysql_config_editor², which enables you to put the password and other options into an encrypted configuration file. But it turns out that MariaDB does not come with that specific tool. The encryption used seems quite weak, and there's an article about the security issues at [3]. There is also the blogpost at [4] giving some details.

So now what?

Well, there is always the plan to use the configuration file .my.cnf⁵, and you can store your mysql or mariadb password in there and everything would be hunky-dory.

The .my.cnf looks like this:

[client]
password=topsecretpassword

You are no doubt aware that the password is stored in cleartext.

The following security measures should be in place:

• always make sure the permissions on the file are set to -rw-------
• create a user in your database with only those permissions that are required by your scripts. In most cases, this is select/update/delete/insert statements.
• when you are finished with your scripts, it might be a good idea to remove the password from the conf file. I understand that with cron jobs and batch scripts this might not be possible.

There is an example of a my.cnf containing every possible configuration option at /usr/share/mysql/my-large.cnf when you install MariaDB.

References

[1] StackOverflow - How to execute a MySQL command from a shell script?

https://stackoverflow.com/questions/8055694/how-to-execute-a-mysql-command-from-a-shell-script

[2] MysqlManual 5.7 - mysql_config_editor

https://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html

[3] MariaDb Blog -

https://mariadb.com/resources/blog/mysql-56-security-through-complacency

[4] Todd's MySQL Blog - Understanding mysql_config_editor’s security aspects

http://mysqlblog.fivefarmers.com/2012/08/16/understanding-mysql_config_editors-security-aspects/

[5] MariaDB - Configuring MariaDB with my.cnf

https://mariadb.com/kb/en/library/configuring-mariadb-with-mycnf/

EclipseLink Logging

In the persistence.xml I have added the following to enable logging for EclipseLink.

Just for my information (so I don't lose the info).

References

EclipseLink 2.5.x. Understanding EclipseLink - Specify Logging

http://www.eclipse.org/eclipselink/documentation/2.5/solutions/tlandgs002.htm#CIHHJIGF

Eclipse - EclipseLink/Examples/JPA/Logging

https://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging

Making Mistakes In JPQL

Well, crap. My boss mentioned that I introduced a Bug in my JPQL, whilst fixing a Bug.

I think it's a Bug that is easy to create, and is therefore worth a little blogpost.

In Java the || and && operators are short-circuiting operators. This means the expression on the right of the operator will not be evaluated if the expression on the left already makes the entire expression true¹.

When using Hibernate (or any other ORM) I do tend to program JPQL in the same way as I program in Java. As this is translated into JPQL, the translation is sometimes not what you would expect.

The class diagram looked something like the following. Bear in mind that each Class represents a table in the database, as is usual in an ORM.

The problem

The following JPQL had an issue:

//@formatter:off
String queryStr = "SELECT person " +
                  "FROM Person person " +
                  "JOIN FETCH person.function " +
                  "WHERE person.lastname = :lastname " +
                  "AND person.fired = false " +
                  "AND (person.address is null OR person.address.deleted = false) ";
//@formatter:on

The idea here is to provide us with a list of all Persons with a specific last name, who are both not fired and may or may not have an existing address.

It will be translated by Hibernate (in our case) into the following SQL statement:

select * -- a lot of fields here
from Person person0_ inner join Function function1_ on person0_.FUNCTIONID=function1_.FUNCTIONID, Address address2_ 
where person0_.ADDRESSID=address2_.ADDRESSID 
and person0_.LASTNAME=? 
and person0_.FIRED='N' 
and (person0_.ADDRESSID is null or address2_.DELETED='N')

Can you spot the problem?

The Solution

The problem above, apparently, is that besides the explicit inner join (inner join Function) there is an implicit inner join upon the Address table (from Address address2_ where person0_.ADDRESSID=address2_.ADDRESSID). This means that only persons will be shown that have an existing address. The condition in the where at the bottom regarding "person0_.ADDRESSID is null" is ignored.

The following, slightly more complicated, JPQL solves this problem:

//@formatter:off
String queryStr = "SELECT person " +
                  "FROM Person person " +
                  "JOIN FETCH person.function " +
                  "LEFT JOIN person.address address " +
                  "WHERE person.lastname = :lastname " +
                  "AND person.fired = false " +
                  "AND (address is null OR address.deleted = false) ";
//@formatter:on

This will be translated appropriately by Hibernate into the following SQL Query:

SELECT * -- a lot of fields here
FROM Person person0_
   INNER JOIN FUNCTION function1_
      ON person0_.FUNCTIONID=function1_.FUNCTIONID
   LEFT OUTER JOIN Address address2_
      ON person0_.ADDRESSID     =address2_.ADDRESSID
WHERE person0_.LASTNAME   =?
AND person0_.FIRED        ='N'
AND (address2_.ADDRESSID IS NULL OR address2_.DELETED      ='N')

Testing

Of course, I would not have made such a crappy mistake, if I did my tests properly. Apparently, testing is hard, and it is easy to miss to test all eventualities.

In this case it is obvious I forgot to test for the Address==null case.

Well, we live and learn.

References

The Java^TM Tutorials - Equality, Relational, and Conditional Operators

http://docs.oracle.com/javase/tutorial/java/nutsandbolts/op2.html

StackOverflow - Explicit vs Implicit SQL Joins

http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins

SQL Operator Precedence

Just a small note for me to remember how this works.

select * 
from ((select 'FINANCE' as dept,  42 as employees from dual) union 
      (select 'SALES' as dept, 32 as employees from dual)) departments
where
   departments.dept = 'FINANCE'
or
   departments.dept = 'SALES'
and
   departments.employees < 30;
returns: FINANCE 42

Clearly, AND takes precedence over OR, as usual.

A AND B OR C => (A AND B) OR C

A OR B AND C => A OR (B AND C)

References

Stackoverflow - sql logic operator precedence

http://stackoverflow.com/questions/1241142/sql-logic-operator-precedence-and-and-or

SQL Logical Operators

http://www.praetoriate.com/t_garmany_easysql_sql_logical_operators.htm

From Hibernate to EclipseLink

I've decided to try porting my little application from Hibernate to EclipseLink.

I am not currently using a lot of Hibernate specific functionality. The ones that occur to me at the moment is:

• Hibernate Filters
• Hibernate DELETE_ORPHAN

Filters

Filters are, as far as I know, currently not a part of the JPA Specification. Every ORM has its own implementation of dealing with it.

Below are the two different (very different!) implementations for Hibernate and EclipseLink. I find the Hibernate one to be more powerful.

Hibernate

Definition of the filter:

@FilterDef(name = "activePersons", defaultCondition="active = 1")
package mmud.database.entities;

Using the Filter on Entities:

import org.hibernate.annotations.Filter;
import org.hibernate.annotations.Filters;

@Entity
@Filters({ @Filter(name = "activePersons") })
public class Person implements Serializable { ...

@Entity
public class Room implements Serializable {

   @OneToMany(cascade = CascadeType.ALL, mappedBy = "room")
   @Filter(name = "activePersons")
   private Set<Person> persons = new HashSet<>();

Enabling the Filter, upon starting a session:

// Hibernate specific
Session session = 
    ((org.hibernate.ejb.EntityManagerImpl)em.getDelegate()).getSession();// JPA1.0
// Session session = getEntityManager().unwrap(Session.class); // JPA2.0
session.enableFilter("activePersons");

EclipseLink

EclipseLink uses the AdditionalCriteria^{2 3} annotation on Entity level.

@Entity
@AdditionalCriteria("(:activePersonFilter <> 1 or this.active = 1)")
public class Person implements Serializable { ...
...
...
getEntityManager().setProperty("activePersonFilter", 1); // turns filter on

Unfortunately, I cannot not set it, as that will trigger the following:

org.eclipse.persistence.exceptions.QueryException.missingContextPropertyForPropertyParameterExpression(QueryException.java:260)

So I am obligated to turn the filter on or off at the boundary where the entityManager (or EntityManagerFactory) is first called.

And there is not a convenient way of turning a filter on or off. Right now, the turning on/off is part of the subquery.⁴

However, I do like the fact that my AdditionalCriteria suddenly works everywhere once I turn it on. I do not have to set specifics on the fields of an Entity. Of course, this does limit the flexibility, but in my case it is not an issue.

Deleting Orphans

Well, Hibernate was one of the first to implement the Delete-orphan functionality and it took a while for it to become meanstream in the JPA specification. But it's there now, and should be supported by all ORMs.

Hibernate

@OneToMany(cascade = CascadeType.ALL, mappedBy = "belongsto")
@Cascade({ org.hibernate.annotations.CascadeType.DELETE_ORPHAN })
private Set<item> items;

JPA 2.0

@OneToMany(cascade = CascadeType.ALL, mappedBy = "belongsto", orphanRemoval = true)
private Set<item> items;

Sha1

Hibernate

Apparently JPA doesn't have an sha1 function, yet mysql does. Hibernate had no problems with it, so I looked for a solution from EclipseLink⁵.

@NamedQuery(name = "User.authorise", query = "select p from Person p WHERE p.name = :name and p.password = sha1(:password)")

JPA 2.1

It turns out JPA⁵ has a specific reserved word called "FUNCTION" to support this, that I quite like. It prevents me from having to write a specific MySQL Dialect.

Of course, it does tie me to the MySQL implementation, but you cannot have everything.

@NamedQuery(name = "User.authorise", query = "select p from Person p WHERE p.name = :name and p.password = FUNCTION('sha1', :password)")

Notes

I found EclipseLink to be very strict with the interpretation of JPQL.

Issue 1 - References

For example, it complains about:

SELECT max(id) FROM ItemDefinition i

Wanting something more along the lines of:

SELECT max(i.id) FROM ItemDefinition i

Issue 2 - Like statements

Then there's an issue with the following query statement:

SELECT s FROM SillyName s WHERE :name like s.name

That gave me the error message "You have attempted to set a value of type class java.lang.String for parameter name with expected type of class java.lang.Boolean". I had to do some voodoo magic to make it work:

SELECT s FROM SillyName s WHERE concat('',:name) like s.name

Yikes!

Issue 3 - Update statements

Furthermore, I got a warning when creating the following update statement:

UPDATE Item i SET i.container = :container WHERE i = :item and i.belongsto = :person

The warning was:

“Input parameters can only be used in the WHERE clause or HAVING clause of a query.”

Apparently JPQL doesn't allow named parameters (or parameters of any kind) outside a WHERE or HAVING clause.

This was only a warning and the thing does work, but I should heed it.

It is described in Chapter 10.2.5.4. JPQL Input Parameters of the JPQL Language Spec⁷

Netbeans

I especially liked the fact that in Netbeans, I can mouse over the NamedQueries in the Entities and it tells me in a popup what is wrong.

References

[1] The Java EE 7 Tutorial - Introduction to the Java Persistence API

http://docs.oracle.com/javaee/7/tutorial/doc/persistence-intro001.htm

[2] AdditionalCriteria

http://wiki.eclipse.org/EclipseLink/Development/AdditionalCriteria

[3] AdditionCriteria

https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Mapping/Additional_Criteria

[4] StackOverflow - Is there a way to disable additionalcriteria in EclipseLink?

http://stackoverflow.com/questions/15847792/is-there-a-way-to-disable-additionalcriteria-in-eclipselink

[5] StackOverflow - JPA How to persist column with sha1 encryption

http://stackoverflow.com/questions/7868939/jpa-how-to-persist-column-with-sha1-encryption

[6] EclipseLink UserGuide - Support for Native Database Functions

http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Support_for_Native_Database_Functions#FUNC

[7] Oracle - JPQL Language References

http://docs.oracle.com/cd/E17904_01/apirefs.1111/e13946/ejb3_langref.html

Oracle Timestamp vs. Date

Recently have been hitting a brick wall due to a bug at work.

Query doesn't return any results if the date is "2009-06-01 00:00:00:01". But for the date "2009-06-01 00:00:00:00" it works fine.

The table is as follows:

And contains:

ID	CODE	STARTDATE	ENDDATE
1	0358	1900/01/01 00:00:00:000000	(null)

/* Formatted on 28/11/2013 17:02:58 (QP5 v5.227.12220.39754) */
SELECT *
FROM MUNICIPAL municipal1_  
WHERE municipal1_.code='0358'
AND (TO_TIMESTAMP('2009/06/01:00:00:01:01', 'yyyy/mm/dd:hh24:mi:ss:FF2') BETWEEN municipal1_.startdate
AND NVL (municipal1_.enddate - 1, TO_TIMESTAMP('2009/06/01:00:00:01:01', 'yyyy/mm/dd:hh24:mi:ss:FF2')))

The query above returns no rows. Why?

Some notes:

1. the (null) for enddate means the timeperiod reaches into the future
2. the sql function BETWEEN^[5] checks to see if a value is within a certain range. The boundaries are inclusive.
3. The enddate is a timestamp(6)
4. enddate - 1 is no longer a timestamp, but a common date. An implicit datatype conversion takes place.^[4]
5. date doesn't store anything smaller than seconds ^[2]
6. we lose the milliseconds
7. NVL tries, if the first argument is null, to cast the second argument to the same type as the first argument (a date) ^[3]
8. even though enddate is null, the data conversion still takes place
9. ergo, the timestamp used now falls outside the range, and no results are returned.

Solution

There is one good solution to this. Don't use BETWEEN with timestamps if the range is exclusive the enddate. Use greater than/equals (>=) and lesser than (<) instead.

If you do wish to change a TIMESTAMP, use INTERVAL instead of arithmetic. ^[4]

/* Formatted on 28/11/2013 17:02:58 (QP5 v5.227.12220.39754) */
SELECT *
FROM MUNICIPAL municipal1_  
WHERE municipal1_.code='0358'
AND (TO_TIMESTAMP('2009/06/01:00:00:01:01', 'yyyy/mm/dd:hh24:mi:ss:FF2') >= municipal1_.startdate
AND TO_TIMESTAMP('2009/06/01:00:00:01:01', 'yyyy/mm/dd:hh24:mi:ss:FF2') < nvl(municipal1_.enddate, 
                            TO_TIMESTAMP('2009/06/01:00:00:01:01', 'yyyy/mm/dd:hh24:mi:ss:FF2')))

Conclusion

Do NOT use Arithmetic with Timestamps! You will get an automatic conversion to datatype Date and lose millisecond precision!

It is quite ugly to substract a day from a DATE, just so that you could use BETWEEN and not worry about the last boundary being included.

I guess it is my own fault for appropriating a bit of SQL code that was originally designed for DATES instead.

References

[1] Timestamp

http://docs.oracle.com/javase/7/docs/api/java/sql/Timestamp.html

[2] PL/SQL Data Type

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/datatypes.htm#CIHBCJEG

[3] NVL

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm

[4] Math with Timestamp

https://blogs.oracle.com/knutvatsendvik/entry/math_with_timestamp

[5] BETWEEN

http://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions011.htm#SQLRF52147

(+) to LEFT/RIGHT OUTER JOIN

Recently had to transform an Oracle SQL Query into a Standard SQL Query. One of the three steps to take was changing the Oracle notation for outer joins into a "LEFT OUTER JOIN" and "RIGHT OUTER JOIN".

(+) is an Oracle specific function^[1].

Let's go with the old and trivial example of the employees and the departments in a company, before we dive any deeper. It seems to work for everyone else, so it should work here.

As we see if the (+) is on the right of the expression, it is a "LEFT OUTER JOIN" and vice versa.

The example comes straight from [3]. It will show NULL for the department values for all employees that are not assigned to a department. Wikipedia has a nice article on the behaviour of outer joins.^[4]

Multiple Outer Join

This is an example of how you could use left join multiple times.
In my work, what I usually find, is a query that selects from a master table that needs to provide all records, left joined with the rest of the world, just in case the records in other tables do not exist.

This won't work

You can write conditions in the "ON" clause, however, these conditions must be related to the SLAVE table. The following, for example, will not work.^[2]

It will show all employees, even the ones that are no longer working for the company.

The solution here is to move the offending condition into the WHERE clause.

Odd one out

The example below, I encountered, is a bit odd, as it is not really an outer join between tables. It is used here to prevent having to write the 'or is null' part.

References

[1] Oracle (+)

http://docs.oracle.com/cd/B28359_01/server.111/b28286/queries006.htm

[2] The ON condition

http://www.oreillynet.com/pub/a/network/2002/10/01/whatsinacondition.html>

[3] Example Oracle Left Outer Join

http://www.dba-oracle.com/tips_oracle_left_outer_join.htm

[4] Wikipedia JOIN SQL

http://en.wikipedia.org/wiki/Join_%28SQL%29

Outer joins in Oracle

https://blogs.oracle.com/optimizer/entry/outerjoins_in_oracle

A Visual Explanation of SQL Joins

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

DECODE to CASE

Recently had to transform an Oracle SQL Query into a Standard SQL Query. One of the three steps to take was changing the DECODE into a CASE.

DECODE is an Oracle specific function^[1].

The case statements, though more standard, are a bit more wordy than the decode statements. As shown in the following, slightly more complex example.

References

[1] Oracle DECODE

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm

[2] Case and decode two powerfull constructs of sql t181

http://www.club-oracle.com/forums/case-and-decode-two-powerfull-constructs-of-sql-t181/

NVL to COALESCE

Recently had to transform an Oracle SQL Query into a Standard SQL Query. One of the three steps to take was changing the NVL into a COALESCE.

NVL is a Oracle specific function^[1].

A simple "search & replace" did the trick.


COALESCE requires the same data types, where NVL does not.

References

Oracle NVL

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm

Stackoverflow - Oracle differences between nvl and coalesce

http://stackoverflow.com/questions/950084/oracle-differences-between-nvl-and-coalesce