Tag: javascript

Your SQL IS NOT JavaScript (neither PHP)

IS NULL, IS NOT NULL, truthiness among other assertions on MySQL and PostgreSQL

People like to complain about JavaScript, how can one thing be equal to the other, i.e. null == undefined evaluates to true unless you use the triple equals ===. Other dynamic typed languages have its peculiarities, such as Ruby where 0 == true evaluates to true, the reason for Ruby is that considers 0 as a value and any value evaluates to true.

How about SQL? The answer is… it depends.

Which database are you using?

Some may be more forgiving, like MySQL doing casts for you all over the place, or more strict like PostgreSQL where you can only compare the truthiness of something of the same type.

Assertion MySQL PostgreSQL
('A' = TRUE) IS TRUE 0 invalid input syntax for type boolean: “A”
('A' IS TRUE) IS TRUE 0 invalid input syntax for type boolean: “A”
(1 = TRUE) IS TRUE 1 operator does not exist: integer = boolean
(1 IS TRUE) IS TRUE 1 argument of IS TRUE must be type boolean, not type integer
('1' IS TRUE) IS TRUE 1 1
('0' IS FALSE) IS TRUE 1 1
(1 = '1') IS TRUE 1 1
(0 = '0') IS TRUE 1 1

Beyond the implications of wrong type comparison, as you can see on lines 1 through 4 where MySQL evaluates values as true or false, you should also worry about what the fact of a column being NULL may imply when comparing two columns.

Suddenly knowing if it is true or false doesn’t matter because you cornered yourself with a third possible value: NULL. By definition NULL is not a value neither a state, it should be considered “garbage”, and no column where you know its value and type would be garbage.

In this front both databases operate the same way:

Assertion SQL
(NULL = NULL) IS TRUE 0
(NULL = NULL) IS FALSE 0
(NULL IS NULL) IS TRUE 1
(0 = NULL) IS NULL 1
(1 = NULL) IS NULL 1
('A' = NULL) IS NULL 1
(TRUE = NULL) IS NULL 1
(FALSE = NULL) IS NULL 1
(0 IS NULL) IS TRUE 0
(0 IS NOT NULL) IS TRUE 0
(0 = NULL) IS TRUE 0
(0 = NULL) IS FALSE 0
(0 = NULL) IS NOT TRUE 1
(0 = NULL) IS NOT FALSE 1
('NULL' = NULL) IS TRUE 0
('NULL' IS NULL) IS TRUE 0

The highlighted parts are usually assumptions that people expect to behave differently, for example that zero is not equal to NULL. That catches many people by surprise. It shouldn’t catch you by surprise though, because 0 is a value. The default behavior of MySQL of casting NULL to 0, on a INTEGER NOT NULL column without a DEFAULT value taught a whole generation of developers that this assumption is true. The same applies when casting a string-based column that has no default value on a NOT NULL column to empty string.

Is that a spaceship operator?

While writing this post and taking a peek into MySQL documentation, I never noticed that a NULL-safe equal operator, <=>, existed, unfortunately the MySQL website only shows the documentation from 5.5 to 8.0, so I can’t be certain if this existed in prior releases.

[code lang=sql]
SELECT
1 IS NOT NULL,
1 = NULL,
1 <=> NULL;
[/code]

Assertion MySQL
1 IS NOT NULL 1
1 = NULL NULL
1 <=> NULL 0

The case where MySQL thinks it is PHP

Another behavior I discovered when doing some JOINs were the string and integer comparison. Both databases get SELECT 1 = '1'; as true, however, MySQL takes a step further:

[code lang=sql]
SELECT 1 = '1a',
'1' * 3,
'1abc' + 4;
[/code]

Assertion MySQL PostgreSQL
1 = '1a' 1 invalid input syntax for integer: “1a”
'1' * 3 3 3
'1abc' + 4 5 invalid input syntax for integer: “1abc”

Thus behaving like PHP. PHP is expected to change this behavior soon with this RFC. PostgreSQL, in this case, is forgiving in casting a string as an integer, only if there is an integer inside of the quotes. If you mix the integer with other characters, it throws an error as you can see above.

I Know!

No, I don’t. I thought I knew enough SQL. Apparently, I was wrong.

Independent of the language you are using, you should be aware that assertions that are true in your language may not be true in SQL and the other way as well.

react-usa-map: A package for customizing the USA map without D3

react-usa-map: A package for customizing the USA map without D3

TL;DR;

  • I created a React package called react-usa-map
  • It displays the USA map with the states including DC / Hawaii / Alaska
  • It’s MIT license
  • Install instructions on The package section of this post.

Package origins

I am working on a project which may or may not go much further. I am building a Rails back-end and playing with React + Redux for the front-end.

This project requires me to have a map of US States and for it to be possible to customize the colors and add an “onClick” event for each state.

I decided to create a React component because said map will be used in more than one place in my project. I tried D3. It’s good, really good, but I needed something simpler. I have three weeks to do this project and between programming back-end and front-end alone I don’t have the time to do the perfect Chropleth map as much as I want to. It is a “nice to have” in my project, not a “must have”. Maybe after the end of the project I will open source it. I do need to check if I can legally open source the project since I am getting the data from a 3rd party with tight licensing requirements.

After finding this map on Wikimedia through the react-us-state-map package on npmjs.org I thought: this is a cool idea, to have the map as an SVG and then change the filling as I want it.

The first step for the package

I extracted the dimensions out of the <svg> and put in a JSON to be read by the component.

The second step

Facebook created this really cool thing for React: create-react-app. It allows you to have a boilerplate code for your react application without actually have to worry how to transpile your code for ECMAScript 2015 and configure webpack.

However they don’t have a create-react-package or create-react-component. Since I am just now going in this front-end adventure, I did what other developer would do in my place: I googled and found this repo.

The third step

I did some modifications on the package.json removing packages I wasn’t using and other necessary actions such as adding tags, scripts, etc.

The final step

Publishing to NPM is actually simple:

Once logged in the terminal run:

[code lang=”bash”]
$ npm version 1.0.0 # your version number here
$ npm publish
[/code]

After this I created a demo project to see if my map would work as intended.

The package

You can install using:

yarn add react-usa-map

or

npm install react-usa-map --save

Compatibility

It uses React 15.4.2 as minimum requirement and it is compatible with React 16.0.0.

More information

react-usa-map started with only me as a contributor, and since the release people contributed adding tests, continuous integration and new features.