From dev-return-54779-archive-asf-public=cust-asf.ponee.io@phoenix.apache.org Sat Dec 8 01:59:14 2018 Return-Path: X-Original-To: archive-asf-public@cust-asf.ponee.io Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by mx-eu-01.ponee.io (Postfix) with SMTP id A350C180647 for ; Sat, 8 Dec 2018 01:59:13 +0100 (CET) Received: (qmail 97214 invoked by uid 500); 8 Dec 2018 00:59:12 -0000 Mailing-List: contact dev-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@phoenix.apache.org Delivered-To: mailing list dev@phoenix.apache.org Received: (qmail 97203 invoked by uid 99); 8 Dec 2018 00:59:12 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd1-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 08 Dec 2018 00:59:12 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd1-us-west.apache.org (ASF Mail Server at spamd1-us-west.apache.org) with ESMTP id 435B3CC6EA for ; Sat, 8 Dec 2018 00:59:12 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd1-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: -110.301 X-Spam-Level: X-Spam-Status: No, score=-110.301 tagged_above=-999 required=6.31 tests=[ENV_AND_HDR_SPF_MATCH=-0.5, RCVD_IN_DNSWL_MED=-2.3, SPF_PASS=-0.001, USER_IN_DEF_SPF_WL=-7.5, USER_IN_WHITELIST=-100] autolearn=disabled Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd1-us-west.apache.org [10.40.0.7]) (amavisd-new, port 10024) with ESMTP id 3BvnNFZVP6S4 for ; Sat, 8 Dec 2018 00:59:10 +0000 (UTC) Received: from mailrelay1-us-west.apache.org (mailrelay1-us-west.apache.org [209.188.14.139]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTP id 7B6975FBBD for ; Sat, 8 Dec 2018 00:51:02 +0000 (UTC) Received: from jira-lw-us.apache.org (unknown [207.244.88.139]) by mailrelay1-us-west.apache.org (ASF Mail Server at mailrelay1-us-west.apache.org) with ESMTP id 32B5DE1328 for ; Sat, 8 Dec 2018 00:51:01 +0000 (UTC) Received: from jira-lw-us.apache.org (localhost [127.0.0.1]) by jira-lw-us.apache.org (ASF Mail Server at jira-lw-us.apache.org) with ESMTP id A29C8252D6 for ; Sat, 8 Dec 2018 00:51:00 +0000 (UTC) Date: Sat, 8 Dec 2018 00:51:00 +0000 (UTC) From: "Geoffrey Jacoby (JIRA)" To: dev@phoenix.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Updated] (PHOENIX-5065) Inconsistent treatment of NULL and empty string MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 [ https://issues.apache.org/jira/browse/PHOENIX-5065?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Geoffrey Jacoby updated PHOENIX-5065: ------------------------------------- Description: Phoenix doesn't handle NULLs consistently with other SQL dialects, and it doesn't handle them consistently internally either. In PHOENIX-2422, [~jamestaylor] mentioned that Phoenix's intended behavior is for empty string and NULL to be equivalent. That's inconsistent with other SQL dialects (in which NULL is never equal to anything, including itself), but if that's our documented behavior, then that's fine unless PHOENIX-2422 to change it is ever worked. But consider the following queries: {code:java} SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = ''; -- Returns 0 rows SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IS NULL; -- Returns some number of rows. Call it N SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN (''); -- Returns 0 rows SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN ('', 'FOO'); -- Returns N rows. Note that FOO does not exist, and is just a nonsense string SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = '' OR TENANT_ID = 'FOO' --Returns 0 rows, but slowly {code} was: Phoenix doesn't handle NULLs consistently with other SQL dialects, and it doesn't handle them consistently internally either. In PHOENIX-2422, [~jamestaylor] mentioned that Phoenix's intended behavior is for empty string and NULL to be equivalent. That's inconsistent with other SQL dialects (in which NULL is never equal to anything, including itself), but if that's our documented behavior, then that's fine unless PHOENIX-2422 to change it is ever worked. But consider the following queries: {code:java} SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = ''; -- Returns 0 rows SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IS NULL; -- Returns some number of rows. Call it N SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN (''); -- Returns 0 rows SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN ('', 'FOO'); -- Returns N rows. SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = '' OR TENANT_ID = 'FOO' --Returns 0 rows, but slowly {code} > Inconsistent treatment of NULL and empty string > ----------------------------------------------- > > Key: PHOENIX-5065 > URL: https://issues.apache.org/jira/browse/PHOENIX-5065 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.14.1 > Reporter: Geoffrey Jacoby > Priority: Major > > Phoenix doesn't handle NULLs consistently with other SQL dialects, and it doesn't handle them consistently internally either. > In PHOENIX-2422, [~jamestaylor] mentioned that Phoenix's intended behavior is for empty string and NULL to be equivalent. That's inconsistent with other SQL dialects (in which NULL is never equal to anything, including itself), but if that's our documented behavior, then that's fine unless PHOENIX-2422 to change it is ever worked. > But consider the following queries: > {code:java} > SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = ''; > -- Returns 0 rows > SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IS NULL; > -- Returns some number of rows. Call it N > SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN (''); > -- Returns 0 rows > SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID IN ('', 'FOO'); > -- Returns N rows. Note that FOO does not exist, and is just a nonsense string > SELECT COUNT(*) FROM SYSTEM.CATALOG WHERE TENANT_ID = '' OR TENANT_ID = 'FOO' > --Returns 0 rows, but slowly > {code} -- This message was sent by Atlassian JIRA (v7.6.3#76005)